It is important to back up your databases so that you can recover your data and be up and running again in case problems occur, such as system crashes, hardware failures, or users deleting data by mistake. Backups are also essential as a safeguard before upgrading a MySQL installation, and they can be used to transfer a MySQL installation to another system or to set up replication slave servers.
Creating Backup:
The mysqldump command is used to create textfile “dumps” of databases managed by MySQL. These dumps are just files with all the SQL commands needed to recreate the database from scratch. The process is quick and easy.
First list all of databases:
# mysql -u root -p
#> show databases;
Below command will take backup of single database:
# mysqldump database_Name > database_Name.sql;
To take multiple databases backup:
# mysqldump –databases database_Name_1 database_Name_2 > database_NameS.sql;
Also we can take entire databases to be backup in the mysql server using this command:
# mysqldump –all-databases > database_NameS_ALL.sql;
To backup of specific tables from a database:
# mysqldump database_Name_1 Table_1 > TableName.sql;
Restoring Backup:
Since the dump files are just SQL commands, you can restore the database backup by telling mysql to run the commands in it and put the data into the proper database.
# mysql database_name < database_name.sql
In the code above, database_name is the name of the database you want to restore, and database_name.sql is the name of the backup file to be restored.
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
