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