MySQL Backup and Restore from Command Line

Assumptions

• You have access to a Unix-like terminal
• You have the password or MySQL's root user, or another user's password with access to the database

Backup

Backup of a single database

Backup a single database to a plain text file, containing the sql commands to restore the tables and their data

mysqldump -u [user] -p [database_name] > [filename].sql

Backup a single database to a gzipped version of the sql file

mysqldump -u [user] -p [database_name] | gzip > [file_name].sql.gz

Backup a single database to a bz2 compressed sql file.

mysqldump -u [user] -p [database_name] | bzip2 > [file_name].sql.bz2

You can usually upload gz, and bz2 files directly to a database using PHPMyAdmin, so is a good idea to have the outpud compressed.

Backup more than one database

You can backup more than one database at the same time.

mysqldump -u [user] -p –databases [database_name_1] [database_name_2] [database_name_n] > [filename].sql

You can use the same options as with the single database to compress the mysqldump command output.

Backup of all databases

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

This will dump all databases to a single file, and you can use that file to restore all databases at once. This is very useful to move your databases from one server to another.

Restore

Using the dump file, it is possible to restore the database with all its tables to a new MySQL server.

Create the database

mysql -u [user] -p

The [user]field in this case will usually be root. At the mysql> root.

create database [database_name];

Create a user for that database, it is actually not needed, but it is a good security measure.

grant all privileges on [database_name].* to [new_user]@[hostname] identified by [new_user_password];

exit;

Once again on the Linux command line prompt.

Restore database dump file

mysql -u [new_user] -p [database_name] < [file_name].sql

If the file was compressed, uncompressed it first.

gunzip [file_name].sql.gz

bunzip2 [file_name].sql.bz2