MySQL backup and restore from command line

Backup

Simple backup

mysqldump --opt -u[user] -p[pass] [database] > [filename].sql

• [user] Your database username
• [pass] The password for your database (note there is no space between -p and the password)
• [database] The name of your database
• [filename].sql The filename for your database backup
• [--opt] The mysqldump option

Backup just few tables

mysqldump -u[user] -p [database] [table_a] [table_b] > [filename].sql

Backup more databases

mysqldump -u[user] -p --databases [db_a] [db_b] [db_c] > [filename].sql

Backup all database from mysql

mysqldump -u[user] -p --all-databases > [filename].sql

The mysqldump command has also some other useful options:

• Tells MySQL to add a DROP TABLE statement before each CREATE TABLE in the dump.
  
  --add-drop-table
• Dumps only the database structure, not the contents.
  
  --no-data
• Adds the LOCK TABLES and UNLOCK TABLES statements you can see in the dump file.
  
  --add-locks

Simple backup with compression

mysqldump -u[user] -p [database] | gzip -9 > [filename].sql.gz

Extract

gunzip [filename].sql.gz

Restore

Simple restore

mysql -u[user] -p [database] < [filename].sql

Restore compressed

gunzip < [filename].sql.gz | mysql -u[user] -p [database]

Import to existing database

mysqlimport -u[user] -p [database] [filename].sql