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.

***********************************************************************
Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Leave a Reply