Create MySQL Dump and Restore It

To Dump

A Single Database:

$ mysqldump -u root -p database_name > dumpfile.sql
 - OR -
$ mysqldump -u root -p --databases database_name > dumpfile.sql

All Databases:

$ mysqldump -u root -p -A > dumpfile.sql
 - OR -
$ mysqldump -u root -p --all-databases > dumpfile.sql

Selected Databases:

$ mysqldump -u root -p -D database_1 database_2 database_3 > dumpfile.sql
 - OR -
$ mysqldump -u root -p --databases database_1 database_2 database_3 > dumpfile.sql

A Table in a Database:

$ mysqldump -u root -p --databases database_name --tables table_name > dumpfile.sql

Zip it at the same time:

$ mysqldump -u root -p --opt database_name | gzip > dumpfile.sql.gz

See here for explanations of "--opt" option.

Create and zip remotely, pipe it and unzip
(create .my.cnf in user's home directory for password)

$ ssh user@host "mysqldump database_name | gzip -9" | gzip -d > dumpfile.sql

To Restore

$ mysql -u root -p database_name < dumpfile.sql

# For compressed dump
$ gunzip < dumpfile.sql.gz | mysql -u root -p database_name

Setting Up Users on MySQL

Create a New User:

mysql> CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password';
mysql> CREATE USER 'user1'@'%' IDENTIFIED BY 'password';

If you know the password hash, you can add a PASSWORD prefix to your call like this:

mysql> CREATE USER 'user1'@'%' IDENTIFIED BY PASSWORD '*93840205958...';

Make sure you know what is the default password encoding in use:

mysql> SHOW VARIABLES LIKE 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

Ref: http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_password

Grant Privileges to a User:

-- Grant everything to user1 from any host
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%' WITH GRANT OPTION;
-- You can shorten ALL PRIVILEGES with ALL
mysql> GRANT ALL ON *.* TO 'user1'@'localhost';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'user1'@'localhost';
mysql> FLUSH PRIVILEGES;

Create a New User and Grant Privileges in One Line:

mysql> GRANT ALL ON *.* TO 'user1'@'localhost' IDENTIFIED BY 'password';

To check privileges for an account:

mysql> SHOW GRANTS;
mysql> SHOW GRANTS FOR 'user1'@'localhost';
+--------------------------------------------------------------------+
| Grants for user1@localhost                                         |
+--------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'user1'@'localhost' |
+--------------------------------------------------------------------+

To reset password:

mysql> SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('mypassword');

mysql> SET PASSWORD FOR 'user1'@'localhost' = OLD_PASSWORD('mypassword');

mysql> SET PASSWORD FOR 'user1'@'localhost' = 'password-hash-string';

Remove User's Privilege

mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'user1'@'localhost';