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