MySQL- Miscellaneous Examples (1)

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