Tag Archives: MySQL - Page 2

Backup and restore a MySQL database

For this task I normally use the mysqldump program bundled with the mysql database package. It is a niffty little program that lets you backup multiple databases, a selection of tables or just one database to a SQL file. Here is the syntax:

Backup one database:

mysqldump --user=username --password=password --databases dbname > filename.sql

This will backup alla tables of the database dbname to the file filename.sql

Backup multiple databases:

mysqldump --user=username --password=password --databases dbname1 dbname2 dbname3 > filename.sql

This will dump database dbname1, dbname2 and dbname3 to the file filename.sql

Backup all databases:

mysqldump --user=username --password=password -A > filename.sql

The -A option tells the program to dump all databases

Backup tables:

mysqldump --user=username --password=password --databases dbname --tables tablename1 tablename2 > filename.sql

This will backup tables tablename1 and tablename2 from database dbname to file filename.sql

Restore a database:

mysql --user=username --password=password dbname < filename.sql

Yes, when restoring a dump we use the normal mysql program and not the mysqldump program. This will restore the database/tables in the file filename.sql to the dbname database