MySQL common commands

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;

create a database:

create database my_db;

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;