MySQL backup and restore

As we know that MySQL store the data. Data is important for any organization. since there may be chance when our MySQL server crashes or the machine in which MySQL installed will crash.

If it would  happen we lost the data. So we must keep backup of our data regularly.

MySQL client provides different utility to backup the data and restore this backup data to new MySQL server.

There are different technique and modes for creating and restoring the backup. we are not going to see all techniques this time.

Here we are using mysqldump client utility that is provided by MySQL to create and restore the data.

The mysqlump utility is used for creating logical-backup (means the SQL statement that we used to create the database and table like insert, create) that necessary SQL statements that is used to restore the database again.

Syntax for using mysqldump

Case 1. shell> mysqldump [options] db_name [tablesname...] > filename.sql

Case 2. shell> mysqldump [options] --databases db_name > db.sql

Case 3. shell> mysqldump [options] --all-databases >dbs.sql

These are the three ways given above to take backup of MySQL server.

In Case 1 we can take the backup of data base as a set of particular tables, means we can take  backup of particular tables.

In Case 2 we can take the complete backup of one or more database.

In Case 3 we can take the complete backup of all database in the MySQL server.

Now we take an example to understand all the above syntax for taking  data base backup.

Case 1  suppose we have a "college database" that contains a table called student and we want to take backup this table only

shell >  mysqldump -u {username} -p college student >student.sql //enter

suppose now we want to take full backup of the college database

shell >  mysqldump -u {username} -p college >college.sql

Case2 suppose we have 3 database in our server college, shop, rent and we want to take the back up of shop and rent

shell >  mysqldump -u {username} -p --databases shop rent  > shop-rent.sql

Case 3 we want to backup all the database
shell > mysqldump -u {username} -p --all-databases   > all_db.sql

Restore backup data to MySQL

In this section I will tell you how to restore backup data that we have created using mysqldump client.

Here we are consider some real-time scenario to store the data.

case 1. You only backup a table instead of whole database and you want to restore this table on other machine for this use the following command

mysql -u username -p database_name < table_backup.sql

case 2. Suppose you have created the backup of full database in this case if you use --databases  (as in case 2 in backup section) to take  backup then backup file contain the create database statement and use database statement you don't need to create a database  and use the following command

mysql -u username  -p  < database_backup.sql

otherwise before exporting backup  you have to create the database and then you use the following command

mysql -u username -p database_name < database_backup.sql

No comments:

Post a Comment