##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