MySQL database backup, Recovery knowledge Reserve

Directory

1. Purpose of Backup

2. Backup Type

3. Backup objects

4. Backup Tools

5, the general operation of the backup process

6, the general operation process of data recovery

7. Backup Tools

1. Purpose of Backup

The purpose of the backup is to restore and restore data, and backup is just a means of restoring and recovering. Regular recovery tests are done on the backed up data to ensure the availability of the backup.

2. Backup Type

Backup type can be described from three dimensions, one is based on whether the database needs to be offline, the second is based on the scope of the backup data to divide, and the third is based on the backup data file or SQL statement file.

2.1, according to whether the database needs offline to divide:

A), Cold: Cold Backup, database service shutdown or read/write requests are not allowed, generally take the cold standby is a physical backup

b), Win Bei: Warm backup, all tables in the database are read-lock, prohibit write requests, only support read requests, the server does not need to be offline, but the upper line of the business will be affected

c), hot spare: Hot backup, back up, while the business is not affected by the function, will only be affected by the performance, whether to support hot spare and storage engine is also related, InnoDB can support hot standby, MyISAM does not support hot, it only supports Win Bei

2.2, according to the data range of backup can be divided into:

A) Complete backup: Full backup, back up all data, either a full database or a full backup of a single database

b), incremental backup: Incremental backup, which represents data that has changed since the last full backup, or represents a backup from the last incremental backup to the data that has now changed. Incremental backups cannot be taken separately for data recovery operations and are combined with full backups for data recovery.

c), differential backup: Differential backup, which represents a data backup that has changed since the last full backup and is also combined with a full backup for data recovery

2.3, according to the backup is a data file or SQL statement file, can be divided into:

A), physical backup: Direct dump (Cold copy) database data files, configuration files, log files, etc., compared to the image is directly copied data files

b), logical backup: Read the data in the table to generate SQL script files or other code data in the database, in the recovery with the backup SQL statement file to achieve database data replay, is an object-level backup scheme, so the logical backup portability is relatively high, but the efficiency of backup and recovery is relatively low.

Characteristics:

A), the advantages of logical backup: The backup of the data is an ASCII text file, the text is full of SQL statements, the backup files can be modified (such as storage engine modifications), not related to the storage engine, and can be cross-platform, cross-version recovery, through the network backup and recovery, easy to restore.

b), the disadvantage of logical backup: MySQL service must be started, backup and restore MYSQLD service process to participate, will consume additional server CPU resources, backup results are plain text type, so the database in the table is numeric data also to be converted to text type, resulting in the backup file will occupy too much storage space, However, the precision of the numeric value of the floating-point type can be lost, and the index needs to be re-established after the data is restored by compressing the backup file.

c), physical backup advantages: No need for additional backup tools, directly using the CP-like Replication tool, because it is a file-level backup, so the backup speed, the index does not need to rebuild

d), physical backup disadvantage: The backup file is larger than the actual data, because the table space like the InnoDB storage engine is the size of 10M, but the amount of data is not 10M, there will be some metadata and indexes, and MySQL need to lock the table or shut down the service before the backup

3. Backup objects

Backup is not just a database file in the data directory for the backup, but also should include: Configuration files, stored procedures, storage functions, triggers, operating system for database customization related configuration files, MySQL replication-related configuration files, binary logs and so on.

4. Backup Tools

A), mysqldump:

is a single-threaded logical Backup tool, the InnoDB table can be hot prepared, the MyISAM table can be Win Bei, backup each recovery slow, can be implemented to back up the entire server database, a single or partial database, a single or partial table, some rows in the table, stored procedures, storage functions, triggers, etc. It can automatically record the binary log files and corresponding position when the backup is made.

b), Mydumper:

is a multithreaded logical backup tool that can replace the mysqldump tool

c), Ibbackup (Open source version: Xtrabackup):

is a physical backup tool that supports INNODB engine hot backup, MyISAM engine Win Bei, fast

D), Lvm-snapshot:

is a physical backup tool that is similar to a hot spare based on an LVM snapshot, because a read lock is required when a snapshot is taken, backup and recovery is fast

e), SELECT into OUTFILE:

is a logical backup tool on MySQL interface, backup is data, not SQL statement, restore with "LOAD data INFILE" into table tb_name ", generally applicable to backup a small amount of data in a single table, faster

f), Mysqlhotcopy: is a cold standby tool that is now almost deprecated.

5, the general operation of the backup process

A), lock the tables that need to be backed up

Mysql> FLUSH TABLES with READ LOCK; #请求对所有的表施加读锁, will affect part of the online business

mysql> SHOW ENGINE INNODB STATUS; #如果是INNODB引擎, you should review some of the status of the InnoDB engine to confirm that there are write requests

b), using Backup tools for backup operations

c), mysql> UNLOCK TABLES; #备份好后要释放锁

6, the general operation process of data recovery

A), let the database offline, no longer provide business online, if necessary to close the MySQL process

b), logging service configuration and file permissions

c), copy the cold backup data file to the data directory

d), change file permissions on Demand

e), try to start the service, should limit network function

f), turn off the binary log so that the recovery operation is not logged into the binary log file

g), Load logical backup

h), check and replay the binary log

i), confirm that the data restore is complete normally, check tables checklist

j), restart the server with full permissions

7. Backup Tools

A), mysqldump tools

-b,--databases db_name: Specifies the database to be backed up, using this option to automatically generate a "CREATE DATABASE" statement in the backed-up file, if you do not use this option to manually create the database when you restore the database

-X,--lock-all-tables: All tables are locked, and the tables in the INNODB engine may not be immediately effective, requiring "mysql> SHOW engine INNODB STATUS; "To observe, so if all the tables in the library are InnoDB engines, this option is not recommended

-L,--lock-tables: Use this option when backing up a single table

--master-data[=#]: A value of 1, 2, indicates that "change master to master_log_file= ' Binlog_name ' Master_log_pos=position" is generated in the backed up file, Just a value of 2 o'clock, this statement will be annotated, when using the mysqldump command backup, it is recommended to add this option, in the backup of the file can be learned in the backup of the binary log file and the position point where, easy to use binary log files to restore data positioning.

--single-transaction: This option enables the hot standby InnoDB engine table, does not require "--lock-all-tables" to lock all tables, creates a large transaction for the mysqldump command, creates a snapshot of all tables, The snapshot is then backed up

-A,--all-databases: backing Up all libraries

-C,--compress:mysqldump compress data for remote backup and transfer

-E,--events: Backup event altimeter code while backing up data

-R,--routines: backup stored procedures and storage functions

--OPT: Enable multiple advanced options at the same time, check the Help information

b), LVM-based almost hot-standby tool

The premise: the transaction log file and the data file must be on the same volume, because the snapshot is a snapshot of a volume, to ensure that the transaction log and data at the same time when the snapshot is taken, this is to ensure data consistency.

Adjust "Sync_binlog = 1" Before the backup, this parameter is when the data changes, the SQL statement is written into "binlog_cache_size" the size of the memory space, and then automatically refreshed to disk by the system, set this value to "1" is to indicate that each time the statement is written to the binlog memory space, it is flushed directly to the disk, which can maintain the data's accessibility.

The backup process is roughly as follows:

1, mysql> FLUSH TABLES with READ LOCK; #获取全局读锁, if MySQL is not offline, it will affect some of the business

mysql> SHOW MASTER STATUS; #记录当前的二进制日志文件和position

Mysql> FLUSH LOGS;

2. Create a snapshot of the volume where the data resides

]# lvcreate-l 100m-n mydata-snap-p r-s/dev/vg_name/lv_name #快照大小请根据实际情况而定

3, mysql> UNLOCK TABLES;

4. Backup Data

Use similar tools such as CP and tar to dump the data in a snapshot directly.

In order to simplify the complex process of backup, someone specially made a tool called Mylvmback, official website: http://www.lenzg.net/mylvmbackup/

c), Xtrabackup

The InnoDB table can be hot prepared, the MyISAM table can only be Win Bei, this tool has the following characteristics:

1, reliable and fast backup;

2. The backup process does not interrupt the executing transaction;

3. Save disk space and bandwidth based on flow control and compression

4. Fast Restore Speed

Xtrabackup files created in the backup directory:

1, Xtrabackup_checkpoints

Record backup type (such as full or incremental), backup status (such as whether it is already prepared status), and LSN (log sequence number) range information

2, Xtrabackup_binlog_info

Record the binary log files currently in use by the MySQL server and the location of the binary log event so far as the backup is made

3, Xtrabackup_info

Records the Innobackupex used at backup, the version of the Xtrabackup tool, the binary log file name that is currently used at backup, and the location of the event.

4, Xtrabackup_logfile

This is a non-text file, which is recorded in the backup InnoDB engine management of the data changes of events, data, etc., in the preparation phase of the recovery needs to be committed to the file and not synchronized to the disk of the transaction to do redo, and the uncommitted transaction to undo.

Depending on the version of Xtrabackup, the files created under the backup directory are slightly different.

Features of the Percona-xtrabackup software package:

Xtrabackup software is provided by the Percona Company's "Percona-xtrabackup" package, which is actually composed of two software, one is Innobackupex, the other is xtrabackup. Innobackupex is a per-script encapsulated by the per script, which automatically calls the Xtrabackup program to implement a hot spare for InnoDB or xtradb on the InnoDB table, whereas InnoDB is only possible for non-xtradb or Win Bei tables. If the tables in the backed-up library have mixed scenarios such as InnoDB and MyISAM, Innobackupex first spares the InnoDB table, locks the entire table, and then backs up the non-InnoDB table (Non-innodb table). The time to get a table lock in a busy MySQL is indeterminate and blocks write requests, so it is not recommended to use Innobackupex as a backup without affecting the online business mix scenario.

This article is from the "knowledge needs summary and records" blog, please be sure to keep this source http://zhaochj.blog.51cto.com/368705/1632106

MySQL database backup, Recovery knowledge Reserve