xtrabackup

Xtrabackup 是一个对 InnoDB 做数据备份的工具,支持在线热备份(备份时不影响数据读写)


xtrabackup备份MySQL的过程:

(1)先拷贝ibdata1文件、undo文件、InnoDB/XtraDB引擎对应的表ibd等文件,不包括.frm文件

(2)flush table with read lock;

(3)拷贝MyISAM表对应的.MYD、.MYI、.frm文件,以及其他的.frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV, .par, and .opt 文件

(4)拷贝redo log,并通过show master sataus 获得binlog位置;

(5)unlock tables;

(6)exit;


还原过程:

(1)读my.cnf文件,获得datadir,innodb_data_home_dir等目录位置

(2)先拷贝MyISAM表,索引,再拷贝InnoDB表和索引,最后是日志文件

(3)保留文件属性,改变文件所有者为备份的用户


还原时间点

还原到xtrabackup结束的时候,而不是开始备份的时候。


版本使用建议

5.5 -> 2.2

5.6 -> 2.3

5.7 -> 2.4


安装xtrabackup 2.4

sht-sgmhadoopnn-01:mysqladmin:/usr/local/mysql:>sudo yum install -y  https://repo.percona.com/yum/percona-release-latest.noarch.rpm

sht-sgmhadoopnn-01:mysqladmin:/etc/yum.repos.d:>rpm -qa|grep percona

sht-sgmhadoopnn-01:mysqladmin:/etc/yum.repos.d:>sudo yum list | grep percona

sht-sgmhadoopnn-01:mysqladmin:/etc/yum.repos.d:>sudo yum install -y percona-xtrabackup-24


卸载xtrabackup

yum remove percona-xtrabackup


创建备份专用用户

mysql> CREATE USER 'bkpuser'@'%' IDENTIFIED BY 'agm43gadsg';

mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'%';


innobackupex中不建议使用的两个参数:

--flush-log 表示刷新日志,则在备份的时候,如果有大事物发生会造成备份失败;

--no-lock 表示不加FTWRL,则在备份的时候,如果有DDL操作会造成备份失败,或者无法还原;



xtrabackup全备+还原操作过程

(1)创建一个备份

sht-sgmhadoopnn-01:mysqladmin:/usr/local/mysql/data/mysql:> xtrabackup --user=bkpuser --password='agm43gadsg' --backup --target-dir=/usr/local/mysql/backup/full_backup_$(date +%Y%m%d%H%M%S)/
190411 15:57:09  version_check Connected to MySQL server
190411 15:57:09  version_check Executing a version check against the server...
190411 15:57:09  version_check Done.
190411 15:57:09 Connecting to MySQL server host: localhost, user: bkpuser, password: set, port: 3306, socket: /usr/local/mysql/data/mysql.sock
Using server version 5.7.21-log
xtrabackup version 2.4.13 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3e7ca7c)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /usr/local/mysql/data
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = /usr/local/mysql/data/
xtrabackup:   innodb_data_file_path = ibdata1:200M:autoextend
xtrabackup:   innodb_log_group_home_dir = /usr/local/mysql/arch
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 52428800
2019-04-11 15:57:09 0x7f9d712ba880 InnoDB: Using Linux native AIO
InnoDB: Number of pools: 1
190411 15:57:09 >> log scanned up to (2388451889)
InnoDB: Opened 3 undo tablespaces
InnoDB: 0 undo tablespaces made active
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 75 for employees/txt_t1, old maximum was 3
190411 15:57:09 [01] Copying /usr/local/mysql/data/ibdata1 to /usr/local/mysql/backup/full_backup_20190411155708/ibdata1
190411 15:57:10 >> log scanned up to (2388451889)
190411 15:57:11 >> log scanned up to (2388451889)
190411 15:57:12 >> log scanned up to (2388451889)
190411 15:57:13 >> log scanned up to (2388451889)
190411 15:57:13 [01]        ...done
190411 15:57:14 >> log scanned up to (2388451889)
190411 15:57:15 >> log scanned up to (2388451889)
190411 15:57:16 >> log scanned up to (2388451889)
190411 15:57:16 [01] Copying /usr/local/mysql/arch/undo001 to /usr/local/mysql/backup/full_backup_20190411155708/undo001
190411 15:57:16 [01]        ...done
190411 15:57:17 >> log scanned up to (2388451889)
190411 15:57:17 [01] Copying /usr/local/mysql/arch/undo002 to /usr/local/mysql/backup/full_backup_20190411155708/undo002
190411 15:57:17 [01]        ...done
190411 15:57:17 [01] Copying /usr/local/mysql/arch/undo003 to /usr/local/mysql/backup/full_backup_20190411155708/undo003
190411 15:57:17 [01]        ...done
190411 15:57:18 [01] Copying ./employees/txt_t1.ibd to /usr/local/mysql/backup/full_backup_20190411155708/employees/txt_t1.ibd
190411 15:57:18 [01]        ...done
190411 15:57:18 [01] Copying ./employees/departments.ibd to /usr/local/mysql/backup/full_backup_20190411155708/employees/departments.ibd
......
......
190411 15:58:28 [01] Copying ./testdb/t1.ibd to /usr/local/mysql/backup/full_backup_20190411155708/testdb/t1.ibd
190411 15:58:28 [01]        ...done
190411 15:58:28 [01] Copying ./testdb/t_org_role_user.ibd to /usr/local/mysql/backup/full_backup_20190411155708/testdb/t_org_role_user.ibd
190411 15:58:28 [01]        ...done
190411 15:58:29 >> log scanned up to (2388451889)
190411 15:58:29 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
190411 15:58:29 Executing FLUSH TABLES WITH READ LOCK...
190411 15:58:29 Starting to backup non-InnoDB tables and files
190411 15:58:29 [01] Copying ./employees/db.opt to /usr/local/mysql/backup/full_backup_20190411155708/employees/db.opt
190411 15:58:29 [01]        ...done
190411 15:58:29 [01] Copying ./employees/txt_t1.frm to /usr/local/mysql/backup/full_backup_20190411155708/employees/txt_t1.frm

190411 15:58:33 [01] Copying ./sys/x@0024waits_by_host_by_latency.frm to /usr/local/mysql/backup/full_backup_20190411155708/sys/x@0024waits_by_host_by_latency.frm
190411 15:58:33 [01]        ...done
190411 15:58:33 [01] Copying ./sys/x@0024waits_by_user_by_latency.frm to /usr/local/mysql/backup/full_backup_20190411155708/sys/x@0024waits_by_user_by_latency.frm
190411 15:58:33 [01]        ...done
190411 15:58:33 [01] Copying ./sys/x@0024waits_global_by_latency.frm to /usr/local/mysql/backup/full_backup_20190411155708/sys/x@0024waits_global_by_latency.frm
190411 15:58:33 [01]        ...done
190411 15:58:33 [01] Copying ./testdb/db.opt to /usr/local/mysql/backup/full_backup_20190411155708/testdb/db.opt
190411 15:58:33 [01]        ...done
190411 15:58:33 [01] Copying ./testdb/geom.frm to /usr/local/mysql/backup/full_backup_20190411155708/testdb/geom.frm
190411 15:58:33 [01]        ...done
190411 15:58:33 [01] Copying ./testdb/t1.frm to /usr/local/mysql/backup/full_backup_20190411155708/testdb/t1.frm
190411 15:58:33 [01]        ...done
190411 15:58:33 [01] Copying ./testdb/t2.MYD to /usr/local/mysql/backup/full_backup_20190411155708/testdb/t2.MYD
190411 15:58:33 [01]        ...done
190411 15:58:33 [01] Copying ./testdb/t2.MYI to /usr/local/mysql/backup/full_backup_20190411155708/testdb/t2.MYI
190411 15:58:33 [01]        ...done
190411 15:58:33 [01] Copying ./testdb/t2.frm to /usr/local/mysql/backup/full_backup_20190411155708/testdb/t2.frm
190411 15:58:33 [01]        ...done
190411 15:58:33 [01] Copying ./testdb/t_org_role_user.frm to /usr/local/mysql/backup/full_backup_20190411155708/testdb/t_org_role_user.frm
190411 15:58:33 [01]        ...done
190411 15:58:33 Finished backing up non-InnoDB tables and files
190411 15:58:33 [00] Writing /usr/local/mysql/backup/full_backup_20190411155708/xtrabackup_binlog_info
190411 15:58:33 [00]        ...done
190411 15:58:33 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2388451880'
xtrabackup: Stopping log copying thread.
.190411 15:58:33 >> log scanned up to (2388451889)
......
......
190411 15:58:33 Executing UNLOCK TABLES
190411 15:58:33 All tables unlocked
190411 15:58:33 [00] Copying ib_buffer_pool to /usr/local/mysql/backup/full_backup_20190411155708/ib_buffer_pool
190411 15:58:33 [00]        ...done
190411 15:58:33 Backup created in directory '/usr/local/mysql/backup/full_backup_20190411155708/'
MySQL binlog position: filename 'mysql-bin.000002', position '722', GTID of the last change '1a598743-53aa-11e9-9c46-00505682501e:1-3'
190411 15:58:33 [00] Writing /usr/local/mysql/backup/full_backup_20190411155708/backup-my.cnf
190411 15:58:33 [00]        ...done
190411 15:58:33 [00] Writing /usr/local/mysql/backup/full_backup_20190411155708/xtrabackup_info
190411 15:58:33 [00]        ...done
xtrabackup: Transaction log of lsn (2388451880) to (2388451889) was copied.
190411 15:58:34 completed OK!


(2)准备一个备份:

原理是在还原备份之前需要做一次crash recovery,应用redolog,undolog

sht-sgmhadoopnn-01:mysqladmin:/usr/local/mysql/data/mysql:> xtrabackup --user=bkpuser --password='agm43gadsg' --prepare --target-dir=/usr/local/mysql/backup/full_backup_20190411155708/
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2388451880)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:200M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 8388608
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:200M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
InnoDB: Opened 3 undo tablespaces
InnoDB: 3 undo tablespaces made active
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 2388451880
InnoDB: Doing recovery: scanned up to log sequence number 2388451889 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 722, file name mysql-bin.000002
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.19 started; log sequence number 2388451889
InnoDB: xtrabackup: Last MySQL binlog file position 722, file name mysql-bin.000002


xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2388452025
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:200M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 52428800
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
InnoDB: Setting log file ./ib_logfile101 size to 50 MB
InnoDB: Setting log file ./ib_logfile1 size to 50 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=2388452025
InnoDB: Opened 3 undo tablespaces
InnoDB: 3 undo tablespaces made active
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 2388452364
InnoDB: Doing recovery: scanned up to log sequence number 2388452373 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 722, file name mysql-bin.000002
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: page_cleaner: 1000ms intended loop took 4420ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: 5.7.19 started; log sequence number 2388452373
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2388452392
190411 16:05:22 completed OK!


(3)还原一个备份

这次还原过程是在另外一台主机上,为了不破坏源主机数据,需要在还原主机上安装好相同版本的MySQL软件和配置文件my.cnf

为了保持数据库配置一致性,这里测试直接把源数据库软件拷贝到新的主机sht-sgmhadoopnn-02上

sht-sgmhadoopnn-01:mysqladmin:/usr/local/mysql/data:>rsync -avz --progress /usr/local/mysql/* sht-sgmhadoopnn-02:/usr/local/mysql/

还原之前确保还原数据库sht-sgmhadoopnn-02 mysql shutdown,并且datadir为空,这一步可以通过rsync或cp直接拷贝备份集文件到指定data目录,也可以用--copy-back参数

sht-sgmhadoopnn-02:mysqladmin:/usr/local/mysql:>rm -rf /usr/local/mysql/data/*  /usr/local/mysql/arch/*

sht-sgmhadoopnn-02:mysqladmin:/usr/local/mysql:> xtrabackup --user=bkpuser --password='agm43gadsg' --host=sht-sgmhadoopnn-02 --copy-back --target-dir=/usr/local/mysql/backup/full_backup_20190411155708
xtrabackup version 2.4.13 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3e7ca7c)
190331 19:34:43 [01] Copying undo001 to /usr/local/mysql/arch/undo001
190331 19:34:43 [01]        ...done
190331 19:34:43 [01] Copying undo002 to /usr/local/mysql/arch/undo002
190331 19:34:43 [01]        ...done
190331 19:34:44 [01] Copying undo003 to /usr/local/mysql/arch/undo003
190331 19:34:44 [01]        ...done
190331 19:34:44 [01] Copying ib_logfile0 to /usr/local/mysql/arch/ib_logfile0
190331 19:34:45 [01]        ...done
190331 19:34:46 [01] Copying ib_logfile1 to /usr/local/mysql/arch/ib_logfile1
190331 19:34:47 [01]        ...done
190331 19:34:48 [01] Copying ibdata1 to /usr/local/mysql/data/ibdata1
190331 19:34:52 [01]        ...done
190331 19:34:55 [01] Copying ./employees/db.opt to /usr/local/mysql/data/employees/db.opt
190331 19:34:55 [01]        ...done
190331 19:34:55 [01] Copying ./employees/txt_t1.frm to /usr/local/mysql/data/employees/txt_t1.frm
190331 19:34:55 [01]        ...done
190331 19:34:55 [01] Copying ./employees/departments.frm to /usr/local/mysql/data/employees/departments.frm
190331 19:34:55 [01]        ...done
......
......
190331 19:36:10 [01] Copying ./testdb/t_org_role_user.frm to /usr/local/mysql/data/testdb/t_org_role_user.frm
190331 19:36:10 [01]        ...done
190331 19:36:10 [01] Copying ./testdb/t_org_role_user.ibd to /usr/local/mysql/data/testdb/t_org_role_user.ibd
190331 19:36:10 [01]        ...done
190331 19:36:10 [01] Copying ./ib_buffer_pool to /usr/local/mysql/data/ib_buffer_pool
190331 19:36:10 [01]        ...done
190331 19:36:10 [01] Copying ./ibtmp1 to /usr/local/mysql/data/ibtmp1
190331 19:36:10 [01]        ...done
190331 19:36:11 [01] Copying ./xtrabackup_binlog_pos_innodb to /usr/local/mysql/data/xtrabackup_binlog_pos_innodb
190331 19:36:11 [01]        ...done
190331 19:36:11 [01] Copying ./xtrabackup_info to /usr/local/mysql/data/xtrabackup_info
190331 19:36:11 [01]        ...done
190331 19:36:11 [01] Copying ./xtrabackup_master_key_id to /usr/local/mysql/data/xtrabackup_master_key_id
190331 19:36:11 [01]        ...done
190331 19:36:11 completed OK!


启动数据库

sht-sgmhadoopnn-02:mysqladmin:/usr/local/mysql:>mysqld &
[1] 6183

xtrabackup增量备份+还原操作过程

(1)先做个全备

sht-sgmhadoopnn-01:mysqladmin:/usr/local/mysql:>xtrabackup --backup --target-dir=/usr/local/mysql/backup/full_backup_$(date +%Y%m%d%H%M%S)

sht-sgmhadoopnn-01:mysqladmin:/usr/local/mysql:>ll /usr/local/mysql/backup/
drwxr-x--- 7 mysqladmin dba  299 Apr 11 16:50 full_backup_20190411164929
插入测试数据:
mysql> create table testdb.t3(c1 int,c2 varchar(20));
mysql> insert into t1 values(1,'aaa');

(2)增量备份1+增量备份2

sht-sgmhadoopnn-01:mysqladmin:/usr/local/mysql:> xtrabackup --backup --target-dir=/usr/local/mysql/backup/inc1_backup_$(date +%Y%m%d%H%M%S) --incremental-basedir=/usr/local/mysql/backup/full_backup_20190411164929

sht-sgmhadoopnn-01:mysqladmin:/usr/local/mysql:>ll backup/
total 4
drwxr-x--- 7 mysqladmin dba  299 Apr 11 16:50 full_backup_20190411164929
drwxr-x--- 7 mysqladmin dba 4096 Apr 11 17:02 inc1_backup_20190411170239
mysql> insert into t1 values(2,'bbb');
sht-sgmhadoopnn-01:mysqladmin:/usr/local/mysql:> xtrabackup --backup --target-dir=/usr/local/mysql/backup/inc2_backup_$(date +%Y%m%d%H%M%S) --incremental-basedir=/usr/local/mysql/backup/inc1_backup_20190411170239
sht-sgmhadoopnn-01:mysqladmin:/usr/local/mysql:>ll backup/
total 8
drwxr-x--- 7 mysqladmin dba  299 Apr 11 16:50 full_backup_20190411164929
drwxr-x--- 7 mysqladmin dba 4096 Apr 11 17:02 inc1_backup_20190411170239
drwxr-x--- 7 mysqladmin dba 4096 Apr 12 15:58 inc2_backup_20190412155845

(3)准备一个增量备份

sht-sgmhadoopnn-01:mysqladmin:/usr/local/mysql:> xtrabackup --prepare --apply-log-only --target-dir=/usr/local/mysql/backup/full_backup_20190411164929
sht-sgmhadoopnn-01:mysqladmin:/usr/local/mysql:> xtrabackup --prepare --apply-log-only --target-dir=/usr/local/mysql/backup/full_backup_20190411164929 --incremental-dir=/usr/local/mysql/backup/inc1_backup_20190411170239
sht-sgmhadoopnn-01:mysqladmin:/usr/local/mysql:> xtrabackup --prepare --target-dir=/usr/local/mysql/backup/full_backup_20190411164929 --incremental-dir=/usr/local/mysql/backup/inc2_backup_20190412155845

(4)还原一个备份

这次还原过程是在另外一台主机上,为了不破坏源主机数据,需要在还原主机上安装好相同版本的MySQL软件和配置文件my.cnf

为了保持数据库配置一致性,这里测试直接把备份文件贝到新的主机sht-sgmhadoopnn-02上

sht-sgmhadoopnn-02:mysqladmin:/usr/local/mysql:> rm -rf /usr/local/mysql/data/* /usr/local/mysql/arch/*
sht-sgmhadoopnn-02:mysqladmin:/usr/local/mysql:> rsync -avz --progress /usr/local/mysql/backup/full_backup_20190411164929  sht-sgmhadoopnn-02:/usr/local/mysql/backup/
sht-sgmhadoopnn-02:mysqladmin:/usr/local/mysql:> xtrabackup --copy-back --target-dir=/usr/local/mysql/backup/full_backup_20190411164929
sht-sgmhadoopnn-02:mysqladmin:/usr/local/mysql:>mysqld &
检查数据:
mysql> select * from t1;
+------+------+
| c1   | c2   |
+------+------+
|    1 | aaa  |
|    2 | bbb  |
+------+------+
12 rows in set (0.00 sec)



xtrabackup备份可能遇到的问题:

MySQL数据库只有50GB,测试的时候只要半小时就能备份完成,但是突然一次xtrabackup运行从1点到6点才完成

查看备份日志,发现从执行flush table with read lock,到unlock用了5个多小时,这个过程是copy一些non-InnoDB文件,正常情况非常快,但是这里却用了这么久时间

可能的原因:

执行FTWRL之前有慢select或耗时的DDL,DML操作,堵塞了FTWRL操作,然后再向数据库做一些DDL,DML都要等待FTWRL动作完成并unlock;


在三个不同的会话上依次执行下面三条SQL

session1:一条慢SQL
mysql> select count(*) from employees e1 join dept_emp;

session2:被s1阻塞
mysql> flush table with read lock;

session3:被s2阻塞
mysql> insert into t1 values(1,'ccc');
mysql> show full processlist;
+----+------+-----------+-----------+---------+------+------------------------------+-------------------------------------------------+
| Id | User | Host      | db        | Command | Time | State                        | Info                                            |
+----+------+-----------+-----------+---------+------+------------------------------+-------------------------------------------------+
| 39 | root | localhost | employees | Query   |   48 | Sending data                 | select count(*) from employees e1 join dept_emp |
| 40 | root | localhost | employees | Query   |   32 | Waiting for table flush      | flush table with read lock                      |
| 41 | root | localhost | employees | Query   |   21 | Waiting for global read lock | insert into t1 values(1,'ccc')                  |
| 42 | root | localhost | NULL      | Query   |    0 | starting                     | show full processlist                           |
+----+------+-----------+-----------+---------+------+------------------------------+-------------------------------------------------+
4 rows in set (0.00 sec)


解决方法:

在业务不繁忙的时候做xtrabackup;

在从库上面备份;

指定参数--ftwrl-wait-timeout=10  #FTWRL等待超过10s,则备份报错,自动退出

xtrabackup --backup --target-dir=/usr/local/mysql/backup/full_backup_$(date +%Y%m%d%H%M%S)  --ftwrl-wait-timeout=10
190413 19:36:26 Waiting for query 56 (duration 132 sec): select count(*) from employees e1 join dept_emp190413 19:36:27 >> log scanned up to (2388459962)
190413 19:36:27 Waiting for query 56 (duration 133 sec): select count(*) from employees e1 join dept_emp190413 19:36:28 >> log scanned up to (2388459962)
190413 19:36:28 Waiting for query 56 (duration 134 sec): select count(*) from employees e1 join dept_emp190413 19:36:29 >> log scanned up to (2388459962)
190413 19:36:29 Unable to obtain lock. Please try again later.

 --kill-long-queries-timeout=10  #FTWRL等待超过10s后,kill掉慢SQL,备份完成

mysql> select count(*) from employees e1 join dept_emp;
xtrabackup --backup --target-dir=/usr/local/mysql/backup/full_backup_$(date +%Y%m%d%H%M%S) --kill-long-queries-timeout=10
......
190413 19:31:01 Executing FLUSH TABLES WITH READ LOCK...
190413 19:31:01 Kill query timeout 10 seconds.
190413 19:31:11 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /usr/local/mysql/data/mysql.sock
190413 19:31:11 Killing query 39 (duration 980 sec): select count(*) from employees e1 join dept_emp
190413 19:31:11 Killing query 39 (duration 980 sec): select count(*) from employees e1 join dept_emp