MySQL Management and optimization (20): Backup and Recovery

Backup and Recovery:

• backups make the data in the database more efficient and secure .

Backup/Restore Policy:

Some factors to consider when making a backup or recovery:

• determining whether the storage engine for the tables to be backed up is transactional or non-transactional, the two different storage engine backups are not quite the same in terms of handling data consistency.
• Do you use full or incremental backups? The advantage of full backup is that backups keep up-to-date backups, which can take less time to recover, and the drawback is that if the amount of data is too large, it will take a lot of time and cause long-time pressure on the system. Incremental backup is the opposite, just back up the daily incremental log, backup time is low, under load pressure, the disadvantage is that when the recovery requires full backup plus the backup to all the logs before the failure, the recovery time will grow.
• you can consider a copy of the method to do offsite backup , but replication can not replace the backup, it is the wrong operation of the database is powerless.
• to do backups on a regular basis , the cycle of backups takes into account the recovery time that the system can afford. The backup should take place when the system load is low.
• make sure that MySQL turns on the log-bin option, with Binlog,mysql to do a full recovery when necessary, or point-in-time recovery, or location-based recovery.
• always do backup recovery testing to make sure that the backups are valid and can be recovered.

Logical Backup and Recovery:

• Logical Backups can be backed up against different storage engines using the same method, while physical backups have different methods for different storage engines.

Backup:

• A logical backup in MySQL backs up the data in the database into a text file that can be viewed and edited by the backed up file.
• we can use the mysqldump tool to implement backups such as:

--Back up all databases mysqldump-uroot-p--all-database > all.sql--Backup Database testmysqldump-uroot-p Test > test.sql--Backup Database Test table Empmysqldump-uroot-p Test emp > test_emp.sql--Backup Database Test under Table EMP, deptmysqldump-uroot-p Test EMP Dept > test_emp_d ept.sql--mysqldump--help to see more options

• to ensure consistent data backup , the- L parameter is required for MyISAM table backups, and the --single-transaction option is available for InnoDB.

Full recovery:

• again, we can use mysqldump to achieve a simple recovery:

--Recover a database mysql-uroot-p db_name < bakfile--The above recovery may not be complete, and you will need to redo the log that was executed after the backup Mysqlbinlog binlog-file | Mysql-uroot-p db_name

Point-in-time recovery:

• MySQL recovery is divided into full recovery and non-full recovery, and non-full recovery is divided into point-in- time Recovery and location-based recovery.
• Point-in-time recovery:

--If the error occurred 10 o'clock in the morning, you can use the following statement to recover Mysqlbinlog--stop-date= "2014-10-06 9:59:59" Bin_log_file | mysql-uroot-p****--Skip 10-point error, then restore Mysqlbinlog--start-date= "2014-10-06 10:00:01" Bin_log_file | mysql-uroot-p****

Location-based recovery:

--Save the log for a time period mysqlbinlog--start-date= "2014-10-06 12:10:20"--stop-date= "2014-10-06 12:15:00" Bin_log_file > Temp_ file--over logs from certain locations, such as skipping the 1000~2000 location log mysqlbinlog--stop-position= "$" bin_log_file | Mysql-uroot-p****mysqlbinlog--start-position= "Bin_log_file" | mysql-uroot-p****

Physical Backup and Recovery:

• physical backup is divided into two kinds of cold and hot backup, compared with logical backup, the advantage is faster backup and recovery.

Cold backup:

• Stop the MySQL service, restore the MySQL data file at the operating system level, restart the MySQL service, and use the Mysqlbinlog tool to restore all binlog since the backup.

Hot backup:

• hot backups are required for different storage engines, primarily MyISAM and InnoDB.
• MyISAM Storage Engine:

--1. Use the Mysqlhotcopy tool mysqlhotcopy-u root-p * * * * db_name/path/to/new_directory--2. Manual lock table Copy flush tables for read;--copy data file to backup directory

• InnoDB Storage Engine:

You can refer to the toll tool ibbackup,http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/ihb-meb-compatibility.html

Import and export of tables: export:

• sometimes we need to export the database tables to:

1. Display as Excel ;

2. In order to save backup space ;

3. Load data is loaded more than 20 times times faster than normal SQL loading for fast loading.

• There are two ways to achieve this:

--Use Select ... Into OUTFILE ... SELECT * FROM table_name to OUTFILE ' file_name ' [option];

where option is selected:

Note:SELECT ... Into OUTFILE ... The resulting output file, if it already exists, will fail to be created and will not overwrite the original file.

The 2nd method is to export with mysqldump :

Mysqldump-u username-t target_dir db_name table_name [option]

Where option is selected:

Import:

• using LOAD DATA INFILE:

LOAD DATA [LOCAL] INFILE ' file_name ' into TABLE table_name [option]

The option options are as follows:

• using mysqlimport:

Mysqlimport-u root-p*** [--local] db_name file_name [option]

where option:

Note: If import and export are cross-platform (Windows and Linux), be aware that setting the parameter line-terminated-byis set to line-terminated-by on Windows = ' \ r \ n ', set line-terminated-by= ' \ n ' on Linux.

Do not hesitate to correct me.

MySQL Management and optimization (20): Backup and Recovery