MySQL Cheat Sheet

Backup management

Creating full database backup

$ mysqldump -u jdoe -p db_name > backupfile.sql

Creating partial database backup

$ mysqldump --add-drop-table -u jdoe -p db_name table_name1 table_name2 > backupfile.sql

Restoring database backup

$ mysql -u jdoe -p db_name < backupfile.sql

Database management

Create database

Using mysqladmin command line tool:

$ mysqladmin -u jdoe -p create 'db_name'

Using mysql command line tool:

$ mysql -u jdoe -p
mysql> CREATE database db_name;
mysql> quit;

Drop database

Using mysqladmin command line tool:

$ mysqladmin -u jdoe -p drop 'db_name'

Using mysql command line tool:

$ mysql -u jdoe -p
mysql> DROP database db_name;
mysql> quit;

User management

Create user

$ mysql -u 'root' -p
mysql> CREATE USER 'jdoe'@'localhost' IDENTIFIED BY 'some_pass';
mysql> quit;

Reset user password

Using mysqladmin command line tool:

$ mysqladmin -u 'root' -p password 'newpass'

Using mysql command line tool:

$ mysql -u 'root' -p
mysql> use mysql;
mysql> update user set password=PASSWORD('some_pass') where user='jdoe';
mysql> flush privileges;
mysql> quit;

Show permissions

$ mysql -u 'root' -p
mysql> SHOW GRANTS FOR 'admin'@'localhost';
mysql> quit;

Grant permissions

Grant limited permissions:

$ mysql -u 'root' -p
mysql> GRANT RELOAD,PROCESS ON *.* TO 'jdoe'@'localhost';
mysql> quit;

Grant all permissions:

$ mysql -u 'root' -p
mysql> GRANT ALL PRIVILEGES ON *.* TO 'jdoe'@'localhost' WITH GRANT OPTION;
mysql> quit;

See also

• SQL

References

• http://www.devshed.com/c/a/MySQL/Backing-up-and-restoring-your-MySQL-Database/
• MySQL database website

External links

• MySQL database website
• MySQL site at Sun.com