Backup & Restore MySQL database with compression option

If your MySQL database is very large, you may like to compress the output of mysqldump.

Use the MySQL backup command below & pipe the output to gzip, then you will get the output as gzip file.

Syntax:
$ mysqldump -u [user_name] -p [password] [database_name] | gzip -9 > [backupfile.sql.gz]

If you want to extract the .gz file, issue following command:
Syntax:
$ gunzip [backupfile.sql.gz]

To restore compressed backup files, issue following command:
Syntax:
$ gunzip < [backupfile.sql.gz] | mysql -u [user_name] -p [password] [database_name]

By above article you will easily backup & restore your MySQL database with compression option.

************************************************************************
How to backup your MySQL Database[s]
************************************************************************

Backing up your database is as essential as you breathing. So please backing up your database on daily basis OR hourly basis depends on how your database contains critical/Important data.

In this article you will find easiest way to backup & restore your MySQL database.

Backing up your MySQL database using several methods like mentioned below:

1. Simple copy method:

This method is very easy to backup MySQL database, where you need to copy only binary database files.

This method may create problem & highly not recommended. For Example The multiple ways of managing case-sensitivity between Unix & Windows means that a database copied from one system to the other may become corrupt.

2. Using mysqldump command:

This is very effective tool to backup MySQL databases, While backing up, it creates text version of databases. (*.sql file)

More precisely, It create a list of SQL statements like DROP table, CREATE table and INSERT into which would be use to restore OR recreate the original database.
Using mysqldump method, you can backup a local database & restore it on a remote database at the same time, As well as you can also backup single/multiple table[s] or single/multiple database[s].

Syntax:
$ mysqldump -u [user_name] -p[password] [database_name] > [backup.sql]

user_name : Database valid user name
password : The password of your database
database_name : Name of your database to backup
backup.sql : The filename for your database backup

Let us check some practical examples on how to use mysqldump method to backup & restore:

A. Backup of Single MySQL database:

Syntax:
$ mysqldump -u [user_name] -p[password] database_name > Sinle_database_backup.sql

B. Backup of Multiple MySQL databases:

Syntax:
$ mysqldump -u [user_name] -p[password] – -databases database-I database-II > Multiple_databases_backup.sql
–databases : Mentioned this parameter when you need to backup multiple databases.

C. Backup of all MySQL databases:

Syntax:
$ mysqldump -u root -p – -all-databases > [All_database_backup.sql]

–all-databases : Mentioned this parameter when you need to backup all MySQL databases.

D. Backup single table:

Syntax:
$ mysqldump -u [user_name] -p[password] database_name table_name > Single_table_backup.sql

E. Backup multiple tables:

Syntax:
$ mysqldump -u [user_name] -p[password] database_name table-I table-II table-III > Multiple_table_backup.sql

The mysqldump command has also some other useful options like:

–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 advantage 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.

3. Using mysqlimport command:

Use this method to import into an already existing database (i.e. to restore a database that already exists)

Syntax:
$ mysqlimport -u [user_name] -p[password] [databasename] [backup.sql]

Restoring your MySQL database:

Above we backup database into text version of SQL file. To create/restore database you need to follow two steps:
1. Create an appropriately named database on the target machine
2. Load the file using the MySQL command:

Syntax:
$ mysql -u [user_name] -p[password] [database_name_to_restore] < [backup.sql]

By above article you will easily backup & restore your MySQL database.

***********************************************************************

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: