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

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre lang="" line="" escaped="" cssfile="">

This site uses Akismet to reduce spam. Learn how your comment data is processed.