（详细）Mysql使用XtraBackup备份数据库

XtraBackup是比mysqldump好用很多的mysql备份方法，支持全量、增量备份，而且备份速度很快，快对于生产的大量数据备份，那简直是福音，下面介绍使用步骤。

目的

我的mysql配置：

centos7.2系统
--defaults-file=/apps/mysql/my.cnf
--port=7006
--user=root
--password=password 
--socket=/data/mysql7006/mysql.sock

计划将mysql数据备份到：/data/backup/mysql7006-20181101

备份步骤

安装XtraBackup

下载地址
https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.3/binary/tarball/percona-xtrabackup-2.4.3-Linux-x86_64.tar.gz
安装到/apps/目录，其实就是解压和创建soft link，对系统无害

cd /tmp
# 下载
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.3/binary/tarball/percona-xtrabackup-2.4.3-Linux-x86_64.tar.gz
# 解压
tar zxvf percona-xtrabackup-2.4.3-Linux-x86_64.tar.gz
mv percona-xtrabackup-2.4.3-Linux-x86_64 xtrabackup
# mv到安装目录
mv xtrabackup /apps/
# 创建连接
ln -s /apps/xtrabackup/bin/* /usr/bin/
# 测试
innobackupex -v

备份mysql

首先停止mysql的写功能：使用root登录mysql，执行FLUSH TABLES WITH READ LOCK;对mysql进行全局锁表，不允许写。
然后执行备份命令：
innobackupex --defaults-file=/apps/mysql/my.cnf --no-timestamp --user=root --password=password --socket=/data/mysql7006/mysql.sock /data/backup/mysql7006-20181101
然后等着执行完毕即可，千万级数据一般10分钟内结束。
日志如下：

181103 06:03:09 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at - line 693.
BEGIN failed--compilation aborted at - line 693.
181103 06:03:09 Connecting to MySQL server host: localhost, user: root, password: set, port: 7006, socket: /data/mysql7006/mysql.sock
Using server version 5.6.41-log
innobackupex version 2.4.3 based on MySQL server 5.7.11 Linux (x86_64) (revision id: 6a46905)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql7006/data
xtrabackup: open files limit requested 65535, set to 65535
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 4294967296
2018-11-03 06:03:09 0x7fc855260740 InnoDB: Using Linux native AIO
xtrabackup: using O_DIRECT
InnoDB: Number of pools: 1
181103 06:03:09 >> log scanned up to (1808750)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 1 for mysql/innodb_table_stats, old maximum was 0
181103 06:03:09 [01] Copying ./ibdata1 to /data/backup/mysql7006-20181101/ibdata1
181103 06:03:09 [01]        ...done
181103 06:03:09 [01] Copying ./mysql/innodb_table_stats.ibd to /data/backup/mysql7006-20181101/mysql/innodb_table_stats.ibd
181103 06:03:09 [01]        ...done
。。。。。。
181103 06:03:10 Finished backing up non-InnoDB tables and files
181103 06:03:10 [00] Writing xtrabackup_binlog_info
181103 06:03:10 [00]        ...done
181103 06:03:10 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1808750'
xtrabackup: Stopping log copying thread.
.181103 06:03:10 >> log scanned up to (1808750)

181103 06:03:11 Executing UNLOCK TABLES
181103 06:03:11 All tables unlocked
181103 06:03:11 Backup created in directory '/data/backup/mysql7006-20181101'
MySQL binlog position: filename 'bin-log.000025', position '120'
181103 06:03:11 [00] Writing backup-my.cnf
181103 06:03:11 [00]        ...done
181103 06:03:11 [00] Writing xtrabackup_info
181103 06:03:11 [00]        ...done
xtrabackup: Transaction log of lsn (1808750) to (1808750) was copied.
181103 06:03:11 completed OK!

当出现completed OK!就是成功备份成功了。
最后恢复写功能：root登录数据库执行unlock tables;
说明：locktable是保证数据一致性的操作，其实xtrabackup可以实现不锁表备份，稍微复杂些，见：https://segmentfault.com/a/1190000002575399

还原mysql

如果本库还原以前的数据，只需要执行：
innobackupex --defaults-file=/apps/mysql/my.cnf --user=root --password=password --use-memory=2G --port=7006 --apply-log /data/backup/mysql7006-20181101

--use-memory=2G默认是100M，增大内存能加快速度。

如果用备份的数据制作从库，需要执行：

# 停止从库运行
mysql -u root --socket=*.sock --port=7006 -p shutdown
# 备份数据传输到从库服务器
scp
# 用主库数据恢复从库
innobackupex --defaults-file=/apps/mysql/my.cnf --user=root --password=password --use-memory=2G --port=7006 --apply-log /data/backup/mysql7006-20181101
mv /path/to/old/data /path/to/old/data.bak
mv /data/backup/mysql7006-20181101 /path/to/old/data
# 从库启动
bin/mysqld_safe --defaults-file=/apps/mysql/my.cnf &

一些注意：

• 如果数据库被程序锁表锁库，连select都不能操作，那么xtrabackup会备份失败，一直显示log scanned up to (log id)，但是log id不变。此时请先解决锁库锁表问题。