Some mySQL commands you want to keep under you hand.
Connecting to a mySQL server:
mysql -h host_address -u user -p
Viewing all the databases:
show databases;
Select a database to use:
use mydatabase;
show db tables (db must be selected):
show tables;
describe the table content:
describe new_table;
Selecting all the users in the system:
select host, user, password from mysql.user;
Creating a user:
CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
where you can omit theIDENTIFIED BY if password is not required (but it usually is).
Deleting a user:
DROP USER user;
Assigning a password:
UPDATE mysql.user SET Password=PASSWORD('newpass')
WHERE User='bob' AND Host='%.loc.gov';
FLUSH PRIVILEGES;
or
GRANT USAGE ON *.* TO 'bob'@'%.loc.gov' IDENTIFIED BY 'newpass';
grant privileges:
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
where you can replace ALL PRIVILEGES with any privilege that you like.
Revoking privileges:
revoke all privileges on databasename.* from user;
again, you can replace ALL PRIVILEGES with any privilege that you like.
Show user privileges:
show grants for 'user'@'host';
Clean table – this will remove all the records in the table. unlike the Delete command, this will also reset any counters on the table.
TRUNCATE TABLE tablename;