MySQL Linux Commands

List of useful MySQL commands for Linux.

Login to MySQL

mysql -u USERNAME -p

Create a MySQL user

mysql > CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

Create a MySQL database

mysql > CREATE DATABASE 'databasename';

Grant privileges to user on a database

mysql > GRANT ALL PRIVILEGES ON DATABASENAME . TABLE TO 'user'@'localhost';

Flush privileges after setting permissions

mysql > FLUSH PRIVILEGES;

Export a MySQL database

mysqldump -u USERNAME -p DATABASENAME > EXPORTFILENAME.sql

Restore a MySQL database

mysql -u USERNAME -p DATABASENAME < DATABASEFILENAME.sql

Backup multiple databases at the same time

mysqldump --databases DATABASEONE DATABASETWO > DATABASEFILENAME.sql

Backup all databases

mysqldump --all-databases > DATABASEFILENAME.sql

Restore a single database from dump of all databases

mysql --one-database DATABASENAME < DATABASEFILENAME.sql

Dump specific tables from a database

mysqldump -u USERNAME -p DATABASENAME TABLE_NAME_1 TABLE_NAME_2 TABLE_NAME_3 > EXPORTFILENAME.sql

Set MySQL root password

mysqladmin -u root password PASSWORD

Change MySQL root password

mysqladmin -u root -pOLDPASSWORD password NEWPASSWORD