How to backup and restore your data with MysqlDump

• March 5, 2009
• mysql  
• backup  
• dump  
• mysql  
• mysqldump  
• restore  

Simple backup:

From one database, to a simple sql file.

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]

Backup specific tables

One database, several specified tables, to a single sql file.

mysqldump -u [username] -p [password] [databasename] [table1 table2 ....] > [backupfile.sql]

Backup multiple databases

Multiple databases, to a single sql file.

mysqldump -u [username] -p [password] --databases [database1 database2...] > [backupfile.sql]

Backup all databases

All databases found in connection, to a single file.

mysqldump -u [username] -p [password] --all-databases > [backupfile.sql]

Other options:

Backup database structure, add option –no-data:

mysqldump --no-data -u [username] -p [password] [databasename] > [backupfile.sql]

Drop table command in backup, add option:

mysqldump --add-drop-table -u [username] -p [password] [databasename] > [backupfile.sql]

Restore

Runs the script contained in the backup file, to the specified connection

mysql -u [username] -p [password] [databasename] < [backupfile.sql]

Moving database

From the current database, execute a mysqldump, and transfert result to the remote mysql database.

mysqldump -u  [username] -p [password] [databasename] | mysql --host=[host.ip.new.db] -C [database]