Back up and Restore your MySQL database

If you have shell or telnet access to your web server, you can backup your MySQL data by using the mysqldump command. This command connects to the MySQL server and creates an SQL dump file. The dump file contains the SQL statements necessary to re-create the database. Here is the proper syntax:


$ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]


* [uname] Your database username
* [pass] The password for your database (note there is no space between -p and the password)
* [dbname] The name of your database
* [backupfile.sql] The filename for your database backup
* [--opt] The mysqldump option


For example, to backup a database named 'Tutorials' with the username 'root' and with no password to a file tut_backup.sql, you should accomplish this command:


$ mysqldump -u root -p Tutorials > tut_backup.sql


This command will backup the 'Tutorials' database into a file called tut_backup.sql which will contain all the SQL statements needed to re-create the database.


Sometimes it is necessary to back up more that one database at once. In this case you can use the --database option followed by the list of databases you would like to backup. Each database name has to be separated by space.


$ mysqldump -u root -p --databases Tutorials Articles Comments > content_backup.sql


Back up your MySQL Database with Compress


If your mysql database is very big, you might want to compress the output of mysqldump. Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file.


$ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]


If you want to extract the .gz file, use the command below:
$ gunzip [backupfile.sql.gz]


Restoring your MySQL Database


Above we backup the Tutorials database into tut_backup.sql file. To re-create the Tutorials database you should follow two steps:


* Create an appropriately named database on the target machine
* Load the file using the mysql command:


$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]


Have a look how you can restore your tut_backup.sql file to the Tutorials database.


$ mysql -u root -p Tutorials < tut_backup.sql


To restore compressed backup files you can use following command
gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]