MySQL Physical backup basic operations

Ⅰ, Xtrabackup Introduction

• Xtrabackup can only back up the data of InnoDB engine, can not back up the table structure, Percona Open source, strongly recommend the latest version (old version bug many)
• Innobackupex can back up data and table structures for the MyISAM and InnoDB two engines, typically with this
• When backing up, read the MySQL configuration file (datadir) by default

Ⅱ, Xtrabackup installation using 2.1 installation

[[email protected]_0_5_centos src]# yum install perl-DBD-MySQL不安装这个备份会报错：Failed to connect to MySQL server: DBI connect[[email protected]_0_5_centos src]# cd /usr/local/src[[email protected]_0_5_centos src]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.7/binary/tarball/percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz[[email protected]_0_5_centos src]# tar zxvf percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz -C ..添加环境变量[[email protected]_0_5_centos src]# cd ..[[email protected]_0_5_centos src]# ln -s percona-xtrabackup-2.4.7-Linux-x86_64/ xtrabackup[[email protected]_0_5_centos src]# echo "PATH=/usr/local/xtrabackup/bin:$PATH" >> /etc/profile[[email protected]_0_5_centos src]# source /etc/profile

2.2 Playing a hand

[[email protected]_0_5_centos src]# innobackupex --compress --compress-threads=8 --stream=xbstream -S /tmp/mysql.sock --parallel=4 /data/backup > backup.xbstream建议用-S连接,默认走socket,不用-S可能报连不上常用参数：throttle指定备份时用到的iops是多少,限制速度

8 Compressed threads, 4 backup threads

Output content (Simplified)

180122 19:47:53 innobackupex:starting The backup operationimportant:please check that the backup run completes SUCCESSFU           Lly. At the end of a successful backup run Innobackupex prints "completed ok!". 180122 19:47:53 Version_check connecting to MySQL server with DSN ' Dbi:mysql:;mysql_read_default_group=xtrabackup;mysql _socket=/tmp/mysql.sock ' as ' root ' (using Password:yes). 180122 19:47:53 version_check Connected to MySQL server180122 1 9:47:53 Version_check executing a version check against the server ...  180122 19:47:53 version_check done.180122 19:47:53 connecting to MySQL server Host:localhost, User:root, Password:set, Port:not set, Socket:/tmp/mysql.sockusing Server version 5.7.20-loginnobackupex version 2.4.7 based on MySQL server 5.7 . Linux (x86_64) (revision ID:05F1FCF) xtrabackup:uses posix_fadvise (). # Connect to the database and do two version checks XTRABACKUP:CD to/mdata/mysql_t Est_dataxtrabackup:open files limit requested 0, set to 100001xtrabackup:using the following InnoDBConfiguration:xtrabackup:innodb_data_home_dir =. Xtrabackup:innodb_data_file_path = ibdata1:12m: Autoextendxtrabackup:innodb_log_group_home_dir =./xtrabackup:innodb_log_files_in_group = 2xtrabackup:innodb_log_ File_size = 50331648innodb:number of pools:1180122 19:47:53 >> log scanned up to (10304795) # Read the configuration file and look for the corresponding file and log location XT Rabackup:generating a list of tablespacesinnodb:allocated tablespace ID Wuyi for Dump_test/dump_inno, old maximum was 0xtr  Abackup:starting 4 threads for parallel data Files transfer180122 19:47:53 [] compressing and streaming./ibdata1180122 19:47:53 [compressing] and streaming./dump_test/dump_inno.ibd180122 19:47:53 [] ... done180122 19:47:53 [03] compressing and streaming./test/test.ibd180122 19:47:53 [Geneva] compressing and streaming./test/sbtest1.ibd180122 19:47:53 [[] ... done ... 180122 19:47:54 >> Log scanned up to (10304795) 180122 19:47:54 executing FLUSH no_write_to_binlog TABLES ... 180122 19:47:54 Executing FLUSH TABLES with READ LOCK ... 180122 19:47:54 starting to backup Non-innodb tables and files180122 19:47:54 [] compressing and streaming./dump_test/d Ump_inno.frm to <stdout>180122 19:47:54 [] ... done180122 19:47:54 [%] compressing and streaming./dump_tes T/db.opt to <stdout>180122 19:47:54 [] ... done ... 180122 19:47:55 finished backing up non-innodb tables and files# copy data 180122 19:47:55 [xx] compressing and streaming Xtraba ckup_binlog_info180122 19:47:55 [xx] ... done# get binary file log points 180122 19:47:55 executing FLUSH no_write_to_binlog ENGINE L Ogs...xtrabackup:the Latest Check Point (for incremental): ' 10304786 ' 180122 19:47:55 >> log scanned up to (10304795 ) xtrabackup:stopping log copying thread.180122 19:47:55 executing UNLOCK TABLES180122 19:47:55 All tables unlocked# stop copy, Release lock 180122 19:47:55 [xx] compressing and streaming ib_buffer_pool to <stdout>180122 19:47:55 [xx] ... done18012 2 19:47:55 Backup created in directory '/data/backup ' mysqL binlog position:filename ' bin.000006 ', position ' 154 ' 180122 19:47:55 [xx] compressing and streaming backup-my.cnf180122 19:47:55 [xx] ... done180122 19:47:55 [xx] compressing and streaming xtrabackup_info180122 19:47:55 [00]. . donextrabackup:transaction Log of LSN (10304786) to (10304795) was copied.180122 19:47:55 completed ok!180122 19:47:55 [ done180122 19:47:55 Backup created in directory '/data/backup ' MySQL binlog position:filename ' bin.000006 ', Position ' 154 ' 180122 19:47:55 [xx] compressing and streaming backup-my.cnf180122 19:47:55 [xx] ... done180122 19:47: Compressing and streaming xtrabackup_info180122 19:47:55 [xx] ... donextrabackup:transaction log of LSN (10 304786) to (10304795) is copied.180122 19:47:55 completed ok!# generate various files, end of backup

Ⅲ, Xtrabackup principle Analysis xtrabackup fully prepared steps

-	Operation	parsing
Step1	Connecting to MySQL Server host	Connect Login
Step2	Using the following InnoDB configuration	Read related configuration file
Step3	Start Xtrabackup_log	Enable log files, record redo LSN, and continuously scan redo log to copy the newly generated redo to Xtrabackup_logfile
Step4	Copy innodb tables. IBD,. Ibdata1, Undo logs	Copy InnoDB table space, shared tablespace, undo log
Step5	Flush No_write_to_binlog tables, flush tables with read lock	Force the commit log to redo to prevent data loss (not before 5.6), lock table
Step6	Copy non-innodb tables. MYD,. MYI,. Opt, misc files and InnoDB tables. frm,. Opt, misc files	Copy MyISAM table-related content and table structure files for InnoDB tables
Step7	Get binary Log position	Gets the binary log location point that is written to the Xtrabackup_binlog_info file
Step8	Flush No_write_to_binlog Engine Logs	The Redo Brush Disc
Step9	Stopping log copying thread	Stop copying
Step10	Unlock tables	Release lock
Step11	Completed OK	Generate various files, end of backup

Tips

① simple point: A thread backup redo, throughout the entire process always, another thread back up the tablespace file until completed OK, the backup succeeds

②5.6 prior to Xtrabackup has the risk of losing data, it is highly recommended to use the latest version

③ and mysqldump, Mydumper compared to the xtrabackup backup is the end point of the data (binary file location point is not the same), so physical backup in addition to its own recovery block, synchronization is also fast, because do not pull the data, do an hour of backup, Logical backups require one hours of data synchronization, and physical backups do not require

④ MyISAM or blocking during backup, data consistency requirements

Ⅳ, xtrabackup Backup Recovery 4.1 View backup files

Since I'm using a stream file to back it up, I'm going to open the stream file first.

[[Email protected]_0_5_centos backup]# Xbstream-x < Backup.xbstream[[email protected]_0_5_centos backup]# Lltotal 2792drwxr-x---2 root root 4096 Jan 15:52 abc-rw-r-----1 root root 417 Jan 15:52 Backup-my. cnf.qp-rw-r--r--1 root root 1822257 Jan 15:51 backup.xbstreamdrwxr-x---2 root root 4096 Jan 15:52 dump_test-rw- R-----1 root root 370 Jan 15:52 ib_buffer_pool.qp-rw-r-----1 root root 969374 Jan 15:52 ibdata1.qpdrwxr-x---    2 root root 4096 Jan 15:52 mysqldrwxr-x---2 root root 4096 Jan 15:52 performance_schemadrwxr-x---2 root root 12288 Jan 15:52 sysdrwxr-x---2 root root 4096 Jan 15:52 test-rw-r-----1 root root 102 Jan 15:52 Xtrab Ackup_binlog_info.qp-rw-r-----1 root root 15:52 xtrabackup_checkpoints-rw-r-----1 root root 494 Jan 2 3 15:52 xtrabackup_info.qp-rw-r-----1 root root 391 Jan 15:52 xtrabackup_logfile.qp See many QP files because the backup was compressed and we need to unzip it [[E Mail protected]_0_5_centos BACkup]# for F in ' Find./-iname "*\.QP"; Do QPRESS-DT4 $f $ (dirname $f) && rm-f $f; Done[[email protected]_0_5_centos backup]# lltotal 14152drwxr-x---2 root root 4096 Jan 15:55 abc-rw-r--r--1  Root root 427 Jan 15:55 backup-my.cnf-rw-r--r--1 root root 1822257 Jan 15:51 backup.xbstreamdrwxr-x---2 root Root 4096 Jan 15:55 dump_test-rw-r--r--1 root root 413 Jan 15:55 ib_buffer_pool-rw-r--r--1 root root 125 82912 Jan 15:55 ibdata1drwxr-x---2 root root 4096 Jan 15:55 mysqldrwxr-x---2 root root 12288 Jan 15:55 p Erformance_schemadrwxr-x---2 root root 12288 Jan 15:55 sysdrwxr-x---2 root root 4096 Jan 15:55 Test-rw-r--r --1 root root 15:55 xtrabackup_binlog_info-rw-r-----1 root root + Jan 15:52 Xtrabackup_checkpo ints-rw-r--r--1 root root 521 Jan 15:55 xtrabackup_info-rw-r--r--1 root root 2560 Jan 15:55 Xtrabackup_lo Gfile can see that, in addition to the backup table space, there are 4 of files generated

See the next 4 files

[[Email protected]_0_5_centos backup]# cat xtrabackup_binlog_info           # Record Binlog file name and positionbin.000006 154------[[Email protected]_0_5_centos backup]# Cat Xtrabackup_checkpoints           # record checkpoint, LSN information backup_type = FULL-BACKUPEDFROM_LSN = 0TO_LSN = 10304786last_l during backup sn = 10304795compact = 0recover_binlog_info = 0------[[Email protected]_0_5_centos backup]# cat Xtrabackup_info & nbsp      # information UUID in the entire backup process = 48febc78-0012-11e8-b724-525400a4dac1name =tool_name = Innobackupextool_command =--compress--compress-threads=8--stream=xbstream-s/tmp/mysql.sock--parallel=4./tool_version = 2.4.7ibbackup_ Version = 2.4.7server_version = 5.7.20-logstart_time = 2018-01-23 15:51:51end_time = 2018-01-23 15:51:56lock_time = 0binlo  G_pos = filename ' bin.000006 ', position ' 154 ' innodb_from_lsn = 0INNODB_TO_LSN = 10304786partial = Nincremental = Nformat = Xbstreamcompact = ncompressed = compressedencrypted = N------xtrabackup_logfile     # Continuous backup of redo, not directly see 

4.2 Recovery

step1: 应用日志,将backup恢复[[email protected]_0_5_centos mdata]# innobackupex --apply-log backupstep2：将恢复好的数据拷贝到datadir,直接move也行[[email protected]_0_5_centos mdata]# innobackupex --copy-back backupstep3：修改文件属主[[email protected]_0_5_centos mdata]# chown -R mysql:mysql mysql_test_datastep4：启动数据库/etc/init.d/mysql.server startStarting MySQL. SUCCESS!

Tips

• After the log application is complete, a file is added to the backup file: Xtrabackup_binlog_pos_innodb, which records the current position of Binlog for InnoDB, and Xtrabackup_binlog_ Info records the current binlog of the entire instance position

• Scenario, the two locations are the same, but when both engines are present at the time of the backup, Xtrabackup_binlog_info.pos > Xtrabackup_binlog_pos_innodb.pos may appear.

• So we usually use the Binlog in xtrabackup_binlog_info position

Ⅴ, other related issues 5.1 incremental backups

--incremental-history-name=name can be used to make incremental backups with parameter changes

However, this incremental backup feature is not recommended, and performance is particularly poor

If yesterday full 100G, updated 30G today, do incremental to scan 100G files to know which pages have changed, then to back up, online difficult to accept

Percona has a parameter to monitor which pages have been changed, so you don't have to sweep all of the previous backup tablespace, but less

To do increments, use the binary log mechanism to do it

5.2 Specifying a library table backup

This is also not recommended, and it is strongly recommended that full backups

Backup principle is to back up all tablespace (IBD), incomplete backup, you may encounter various problems

For example, back up a library, did not back up B library, with this backup after the B library to create a table with the same name can not be created

5.3 Remote Backup

innobackupex --compress --compress-threads=8 --stream=xbstream --user=root --parallel=4 ./ | ssh [email protected] "xbstream -x -C /data/www/mysql/backup"

MySQL Physical backup basic operations