Mysql database backup and restore

If you’re storing anything in MySQL databases that you do not want to lose, it is very important to make regular backups of your data to protect it from loss. It is always good to have backup copy of everything. This tutorial will show you easy steps to backup and restore the data in your MySQL database.

Creating A Backup

The mysqldump command is used to create textfile “dumps” of databases managed by MySQL. These dumps are just files with .sql format which are used to restore the database in case of any database loss.

If you want to back up a single database, you can create the dump and send the output into a file by

# mysqldump database_name > database_name.sql

Multiple databases can be backed up at the same time

# mysqldump –databases database_one database_two > two_databases.sql

It is also simple to back up all of the databases on a server

# mysqldump –all-databases > all_databases.sql

Restoring a Backup

Since the dump files are just .sql files, you can restore the database backup by

# mysql database_name < database_name.sql

If you are trying to restore a single database from dump of all the databases, you have to let mysql know like this

# mysql –one-database database_name < all_databases.sql

Here is the proper syntax if there is password for mysql for root user.

# 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 ‘database_name’ with the username ‘root’ and with no password to a file database_name.sql ,

# mysqldump -u root -p database_name > database_name.sql

This command will backup the ‘database_name’ database into a file called database_name.sql which will contain all the SQL statements needed to re-create the database.

With mysqldump command you can specify certain tables of your database you want to backup. For example, to back up only “tableone” and “tabletwo” tables from the ‘database_name’ database use the command below. Each table name has to be separated by space.

# mysqldump -u root -p database_name tableone tabletwo > tables.sql

Sometimes it is necessary to back up more than 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 database_name database_name2 > databases_backup.sql

If you want to back up all the databases in the server at one time you should use the –all-databases option. It tells MySQL to dump all the databases it has in storage.

# mysqldump -u root -p –all-databases > alldb_backup.sql

The mysqldump command has also some other useful options:

–add-drop-table: Tells MySQL to add a DROP TABLE statement before each CREATE TABLE in the dump.

–no-data: Dumps only the database structure, not the contents.

–add-locks: Adds the LOCK TABLES and UNLOCK TABLES statements you can see in the dump file.

The mysqldump command has advantages and disadvantages. The advantages of using mysqldump are that it is simple to use and it takes care of table locking issues for you. The disadvantage is that the command locks tables. If the size of your tables is very big mysqldump can lock out users for a long period of time.

Back up your MySQL Database with Compress

If your mysql database is too big, its good 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 database_name database into database_name.sql file. To re-create the database_name database, you can use following commands

Create an empty database on the serverLoad the file using the mysql command

# mysql -u [uname] -p[pass] [database_name] < [database_name.sql]

To restore compressed backup files you can do the following

# gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

If you want to restore a database that already exists, you’ll have to use mysqlimport command. The syntax for mysqlimport is as below

# mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]

Backing up database by using PHPMYADMIN

phpMyAdmin is the most popular web-based interface to MySQL and is usually preinstalled in most shared hosting accounts. Through it, you can perform any action on the database including backups and restoration. You can use phpMyAdmin to backup/restore your database if it is relatively small ie upto 8 to 10 MB. For bigger databases, you can use above method.

To back up your database, open your phpMyAdmin page in your web browser and select your database from the dropdown on the left. From the main database page, click on Export tab on the top button bar.

1. Select the database you’d like to back up from the column on the left side of phpMyAdmin.

2. Click on Export tab on the top button bar.

3. Select the tables from the list that you would like to backup. If you want to backup the entire database, check Select All.

4. Select Structure and data from the bullet list.

5. Check the Add ‘drop table box if you are moving the database to a new location and don’t want to merge the old table with an existing one.

6. Click the Save as file box.

7. Use the Save as file zipped if you want to compress the backup before downloading it from the server.

8. Click the Go button, when prompted save the file to your local computer.

Restoring via PHPMYADMIN

1. From the column on the left select the database that you want to restore to. If one doesn’t exist you must first create it.

2. Click on Import from the top set of tabs.

3. Click on the Browse button next to Location of the textfile near the bottom.

4. Browse to the local backup and click Open.

5. If you have the local backup in a non-text file format, e.g. you selected save as file: zipped when you backed up the database, you’ll have to unzip the file on your local computer before you can select it during this step.

6. Click the Go button.

7. Done…Your SQL-query has been executed successfully.

Tagged , , . Bookmark the permalink.

Leave a Reply