1. To create user “databaseuser” on host “databasehost” with password “password”:
mysql> CREATE USER 'databaseuser'@' databasehost ' IDENTIFIED BY 'password';
2. To create database “databasename”:
mysql> CREATE DATABASE datbasename;
3. To display databases:
mysql> SHOW DATABASES;
4. To display tables:
mysql> SHOW TABLES;
5. To change database;
mysql> USE datbasename;
6. To delete database:
mysql> DROP DATABASE databasename;
7. To grant permission on database “databasename”:
mysql> GRANT ALL ON databasename.* TO 'databaseuser'@' databasehost ';
8. To show grant privileges:
mysql> SHOW GRANTS FOR 'databaseuser'@' databasehost ';
9. To remove grant privileges:
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'databaseuser'@' databasehost ';
10. To make change in effect:
mysql> FLUSH PRIVILEGES;
11. To update user table:
mysql> UPDATE user SET host='newhostname' WHERE host='oldhostname';
12. To show user table:
mysql> SELECT * FROM user;
13. To reset user password:
mysql> UPDATE user SET Password=PASSWORD('password') WHERE user="'databaseuser'@' databasehost’";
14. To rename root user:
mysql> UPDATE USER SET user="newdatabaseadmin" WHERE user="root";
15. To remove entry from user table:
mysql> DELETE FROM user WHERE host="databasehost" AND user="databaseuser”;
16. To show db tables:
mysql> SHOW * FROM db;
17. To update db tables
mysql> UPDATE db SET host='newdatabasehost' WHERE db="olddatabasehost";
18. To remove entry from db table:
mysql> DELETE FROM db WHERE db="databaseuser";
19. To take database backup:
# mysqldump –h hostname –u username –p databasename > /path/to/backup/filename.sql
20. To restore database backup:
# mysql –h hostname –u username –p databasename < /path/from/restore/filename.sql