MySQL Cheat Sheet

From BindleBinariesWiki

Backup management

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

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

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

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;

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;

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

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;

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

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;