Backup and recovery of MySQL database

I. Backing up data

The 1.mysqldump command backs up data from a database into a text file. The structure of the table and the data in the table are stored in the generated text file. The mysqldump command works very simply. It first identifies the structure of the table that needs to be backed up, and then generates a CREATE statement in the text file. Then, convert all the records in the table into an INSERT statement. These create statements and INSERT statements are all used when restoring. When you restore data, you can create a table using the CREATE statement. Use one of the INSERT statements to restore the data.
1. Back up a database
2. Backing up multiple databases
3. Back up all databases

The 2.mysqldump command backs up the data in the database. However, the backup was saved with the CREATE statement and INSERT statement in the backup file. Not only that, the mysqldump command can also export text files. Its basic grammatical form is as follows:
Mysqldump-uuser-ppassword dbname table [option] > Xx.sql;

3.Mysqldump Common parameters:
? --all-databases,-A export all databases
? --add-drop-database adding drop DATABASE statements before each database is created
? --no-data,-D does not export any data, only the database table structure is exported.
? --no-create-db,-n exports only data without adding the Create DATABASE statement.
? --no-create-info,-t exports only data without adding the Create TABLE statement.

4. Export all table data for all databases
? mysqldump-ulepus-p123456-a >/tmp/all.sql
? Export all data in the Bugfree database, not including the table structure
? mysqldump-ulepus-p123456 bugfree-n-T > A.sql
? Export all table structures in the Bugfree database, excluding data
? mysqldump-ulepus-p123456 bugfree-d > B.sql
? Export all bug information in the Bugfree database
? mysqldump-ulepus-p123456 bugfree bf_bug_info-n-T > Bug.sql

Two. Recovering data

1. Restore data from the Bugfree database that you just backed up
? mysql–ulepus–p123456 Bugfree < Bugfree.sql
? Recover data from all databases that were just backed up
? mysql–ulepus–p123456 < All.sql
? This way, in addition to recovering data can be used to execute SQL in bulk, such as a large SQL file needs to be executed, open in the visualizer and then execute it is more troublesome, can be used in this way to execute.

Three. Backing Up Database Scripts

1. While learning Linux, we have learned how to write shell scripts, now we need to automatically back up all the databases every day 1 points, the backup file is the date of the day named, put in the/usr/local/db_back directory

#!/bin/bashfile_name= ' Date +%y%m%d%h%m%s ' db_path= '/usr/local/db_back/' mysqldump-ulepus-p123456-a >${db_path}$ {File_name}_all.sqlecho "Backup OK"

Backup and recovery of MySQL database