Basic MySQL Administration

1. How to create a user and along with his privileges automatically ?

GRANT ALL PRIVILEGES ON database.* To username@hosr or IP IDENTIFIED BY password;

flush privileges;

2. How to give only specific privileges to users ?

GRANT select,lock tables ON *.* To user@host or IP IDENTIFIED BY password;

flush privileges;

3. How to see permissions of a user ?

show grants for username@hostanme;

4. How to revoke all given privileges ?

revoke all privileges on *.* from user@host;

5. How to take backup of database ?

mysqldump --user <username> --password=<password> databasename > databasebackup.sql

6. How to take backup of two databases ?

mysqldump --user <username> --password=<password> databasename1 databasename2 > databasebackup.sql

7. How to take backup of all databases in MySQL ?

mysqldump --user <username> --password=<password> -all-databases > alldbbackup.sql

8. How to take backup of table in database ?

mysqldump --user <username> --password=<password> database_name database_table   >  databasebackup.sql 

9. How to restore one database ?

mysql --user <username> --password=<password> databasename < databasebackup.sql
mysql_upgrade -p -u root --force