Linux-xtrabackup实现全量+增量+binlog恢复库
备份环境
centos7 ，地址10.0.0.7 数据库： MySQL5.7 数据库 备份库
centos7 地址10.0.0.77 数据库： MySQL5.7 数据库 还原库
xtrabackup工具2.4
centos7：10.0.0.7和10.0.0.77 都操作
创建备份目录
上传工具包xtrabackup
[root@centos7-liyj ~]#mkdir /backup/ [root@centos7-liyj ~]#rz -E rz waiting to receive. [root@centos7-liyj ~]#ls anaconda-ks.cfg boost_1_59_0.tar.gz percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm
安装工具包
[root@centos7-liyj ~]#yum -y install percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm
[root@centos7-liyj ~]#yum -y install percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm Loaded plugins: fastestmirror Examining percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm: percona-xtrabackup-24-2.4.20-1.el7.x86_64 Marking percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package percona-xtrabackup-24.x86_64 0:2.4.20-1.el7 will be installed --> Processing Dependency: perl(DBD::mysql) for package: percona-xtrabackup-24-2.4.20-1.el7.x86_64 Loading mirror speeds from cached hostfile --> Processing Dependency: perl(Digest::MD5) for package: percona-xtrabackup-24-2.4.20-1.el7.x86_64 --> Processing Dependency: rsync for package: percona-xtrabackup-24-2.4.20-1.el7.x86_64 --> Processing Dependency: libev.so.4()(64bit) for package: percona-xtrabackup-24-2.4.20-1.el7.x86_64 --> Running transaction check ---> Package libev.x86_64 0:4.15-7.el7 will be installed ---> Package perl-DBD-MySQL.x86_64 0:4.023-6.el7 will be installed --> Processing Dependency: perl(DBI::Const::GetInfoType) for package: perl-DBD-MySQL-4.023-6.el7.x86_64 --> Processing Dependency: perl(DBI) for package: perl-DBD-MySQL-4.023-6.el7.x86_64 ---> Package perl-Digest-MD5.x86_64 0:2.52-3.el7 will be installed --> Processing Dependency: perl(Digest::base) >= 1.00 for package: perl-Digest-MD5-2.52-3.el7.x86_64 ---> Package rsync.x86_64 0:3.1.2-10.el7 will be installed --> Running transaction check ---> Package perl-DBI.x86_64 0:1.627-4.el7 will be installed --> Processing Dependency: perl(RPC::PlServer) >= 0.2001 for package: perl-DBI-1.627-4.el7.x86_64 --> Processing Dependency: perl(RPC::PlClient) >= 0.2000 for package: perl-DBI-1.627-4.el7.x86_64 ---> Package perl-Digest.noarch 0:1.17-245.el7 will be installed --> Running transaction check ---> Package perl-PlRPC.noarch 0:0.2020-14.el7 will be installed --> Processing Dependency: perl(Net::Daemon) >= 0.13 for package: perl-PlRPC-0.2020-14.el7.noarch --> Processing Dependency: perl(Net::Daemon::Test) for package: perl-PlRPC-0.2020-14.el7.noarch --> Processing Dependency: perl(Net::Daemon::Log) for package: perl-PlRPC-0.2020-14.el7.noarch --> Processing Dependency: perl(Compress::Zlib) for package: perl-PlRPC-0.2020-14.el7.noarch --> Running transaction check ---> Package perl-IO-Compress.noarch 0:2.061-2.el7 will be installed --> Processing Dependency: perl(Compress::Raw::Zlib) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch --> Processing Dependency: perl(Compress::Raw::Bzip2) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch ---> Package perl-Net-Daemon.noarch 0:0.48-5.el7 will be installed --> Running transaction check ---> Package perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 will be installed ---> Package perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ====================================================================================================================== Package Arch Version Repository Size ====================================================================================================================== Installing: percona-xtrabackup-24 x86_64 2.4.20-1.el7 /percona-xtrabackup-24-2.4.20-1.el7.x86_64 32 M Installing for dependencies: libev x86_64 4.15-7.el7 extras 44 k perl-Compress-Raw-Bzip2 x86_64 2.061-3.el7 base 32 k perl-Compress-Raw-Zlib x86_64 1:2.061-4.el7 base 57 k perl-DBD-MySQL x86_64 4.023-6.el7 base 140 k perl-DBI x86_64 1.627-4.el7 base 802 k perl-Digest noarch 1.17-245.el7 base 23 k perl-Digest-MD5 x86_64 2.52-3.el7 base 30 k perl-IO-Compress noarch 2.061-2.el7 base 260 k perl-Net-Daemon noarch 0.48-5.el7 base 51 k perl-PlRPC noarch 0.2020-14.el7 base 36 k rsync x86_64 3.1.2-10.el7 base 404 k Transaction Summary ====================================================================================================================== Install 1 Package (+11 Dependent packages) Total size: 34 M Total download size: 1.8 M Installed size: 36 M Downloading packages: (1/11): libev-4.15-7.el7.x86_64.rpm | 44 kB 00:00:00 (2/11): perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm | 32 kB 00:00:00 (3/11): perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm | 57 kB 00:00:00 (4/11): perl-DBD-MySQL-4.023-6.el7.x86_64.rpm | 140 kB 00:00:00 (5/11): perl-Digest-1.17-245.el7.noarch.rpm | 23 kB 00:00:00 (6/11): perl-DBI-1.627-4.el7.x86_64.rpm | 802 kB 00:00:00 (7/11): perl-IO-Compress-2.061-2.el7.noarch.rpm | 260 kB 00:00:00 (8/11): perl-Digest-MD5-2.52-3.el7.x86_64.rpm | 30 kB 00:00:00 (9/11): perl-Net-Daemon-0.48-5.el7.noarch.rpm | 51 kB 00:00:00 (10/11): perl-PlRPC-0.2020-14.el7.noarch.rpm | 36 kB 00:00:00 (11/11): rsync-3.1.2-10.el7.x86_64.rpm | 404 kB 00:00:00 ---------------------------------------------------------------------------------------------------------------------- Total 1.1 MB/s | 1.8 MB 00:00:01 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : perl-Digest-1.17-245.el7.noarch 1/12 Installing : perl-Digest-MD5-2.52-3.el7.x86_64 2/12 Installing : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 3/12 Installing : libev-4.15-7.el7.x86_64 4/12 Installing : rsync-3.1.2-10.el7.x86_64 5/12 Installing : perl-Net-Daemon-0.48-5.el7.noarch 6/12 Installing : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 7/12 Installing : perl-IO-Compress-2.061-2.el7.noarch 8/12 Installing : perl-PlRPC-0.2020-14.el7.noarch 9/12 Installing : perl-DBI-1.627-4.el7.x86_64 10/12 Installing : perl-DBD-MySQL-4.023-6.el7.x86_64 11/12 Installing : percona-xtrabackup-24-2.4.20-1.el7.x86_64 12/12 Verifying : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 1/12 Verifying : perl-Net-Daemon-0.48-5.el7.noarch 2/12 Verifying : percona-xtrabackup-24-2.4.20-1.el7.x86_64 3/12 Verifying : perl-Digest-MD5-2.52-3.el7.x86_64 4/12 Verifying : perl-DBD-MySQL-4.023-6.el7.x86_64 5/12 Verifying : rsync-3.1.2-10.el7.x86_64 6/12 Verifying : libev-4.15-7.el7.x86_64 7/12 Verifying : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 8/12 Verifying : perl-Digest-1.17-245.el7.noarch 9/12 Verifying : perl-DBI-1.627-4.el7.x86_64 10/12 Verifying : perl-IO-Compress-2.061-2.el7.noarch 11/12 Verifying : perl-PlRPC-0.2020-14.el7.noarch 12/12 Installed: percona-xtrabackup-24.x86_64 0:2.4.20-1.el7 Dependency Installed: libev.x86_64 0:4.15-7.el7 perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 perl-DBD-MySQL.x86_64 0:4.023-6.el7 perl-DBI.x86_64 0:1.627-4.el7 perl-Digest.noarch 0:1.17-245.el7 perl-Digest-MD5.x86_64 0:2.52-3.el7 perl-IO-Compress.noarch 0:2.061-2.el7 perl-Net-Daemon.noarch 0:0.48-5.el7 perl-PlRPC.noarch 0:0.2020-14.el7 rsync.x86_64 0:3.1.2-10.el7 Complete!xtrabackup安装
centos7：10.0.0.7 备份操作
1、完全备份
[root@centos7-liyj ~]#xtrabackup -uroot -patech123456 --backup --target-dir=/backup/base
备份的目录文件
[root@centos7-liyj ~]#ll /backup/base/ total 12336 -rw-r----- 1 root root 487 May 14 20:00 backup-my.cnf -rw-r----- 1 root root 301 May 14 20:00 ib_buffer_pool -rw-r----- 1 root root 12582912 May 14 20:00 ibdata1 drwxr-x--- 2 root root 4096 May 14 20:00 mysql drwxr-x--- 2 root root 8192 May 14 20:00 performance_schema drwxr-x--- 2 root root 8192 May 14 20:00 sys -rw-r----- 1 root root 135 May 14 20:00 xtrabackup_checkpoints -rw-r----- 1 root root 437 May 14 20:00 xtrabackup_info -rw-r----- 1 root root 2560 May 14 20:00 xtrabackup_logfile
2、第一次修改数据
修改数据库文件
students表中增加一名学员
mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 9925 | +------------------+-----------+ 1 row in set (0.00 sec) mysql> insert students(name,age,gender,classid)values('Dugu qiubai',40,'M',1); Query OK, 1 row affected (0.01 sec) mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 10214 | +------------------+-----------+ 1 row in set (0.00 sec) 3、第一次增量备份
xtrabackup -uroot -patech123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
4、第二次修改数据
修改数据库文件
terchers 表中增加一名教师
mysql> insert teachers(name,age,gender)values('Yuan Tiangang',80,'M'); Query OK, 1 row affected (0.00 sec) mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 10501 | +------------------+-----------+ 1 row in set (0.00 sec) 5、第二次增量备份
[root@centos7-liyj ~]#xtrabackup -uroot -patech123456 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
6、把备份的文件复制到 10.0.0.77 机器上换源
[root@centos7-liyj ~]#scp -r /backup/* 10.0.0.77:/backup/
centos7：10.0.0.77 还原操作
还原过程
1、预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
[root@centos7-liyj ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base/
[root@centos7-liyj ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base/ xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=7 --redo-log-version=1 xtrabackup: recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/backup/base/ xtrabackup version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056) xtrabackup: cd to /backup/base/ xtrabackup: This target seems to be not prepared yet. InnoDB: Number of pools: 1 xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2808169) xtrabackup: using the following InnoDB configuration for recovery: 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 = 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:12M: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: page_cleaner coordinator priority: -20 InnoDB: Highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 2808169 InnoDB: Doing recovery: scanned up to log sequence number 2808178 (0%) InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: xtrabackup: Last MySQL binlog file position 8801, file name mysql-bin.000001 InnoDB: xtrabackup: Last MySQL binlog file position 8801, file name mysql-bin.000001 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 2808187 InnoDB: Number of pools: 1 220514 22:13:40 completed OK!命令执行详情
2、合并第一次增量备份到完全备份
[root@centos7-liyj ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
3、合并第二次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
[root@centos7-liyj ~]#xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
4、复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
关闭数据库服务
[root@centos7-liyj ~]#systemctl stop mysqld
确认数据库目录为空
[root@centos7-liyj /backup/base]#rm -rf /data/mysql/* [root@centos7-liyj /backup/base]#cd /data/mysql/ [root@centos7-liyj /data/mysql]#ll total 0
还原好的数据库文件
[root@centos7-liyj /backup/base]#cd /data/mysql/ [root@centos7-liyj /data/mysql]#ll total 122924 drwxr-x--- 2 root root 272 May 14 22:39 hellodb -rw-r----- 1 root root 290 May 14 22:39 ib_buffer_pool -rw-r----- 1 root root 12582912 May 14 22:39 ibdata1 -rw-r----- 1 root root 50331648 May 14 22:39 ib_logfile0 -rw-r----- 1 root root 50331648 May 14 22:39 ib_logfile1 -rw-r----- 1 root root 12582912 May 14 22:39 ibtmp1 drwxr-x--- 2 root root 4096 May 14 22:39 mysql drwxr-x--- 2 root root 8192 May 14 22:39 performance_schema drwxr-x--- 2 root root 8192 May 14 22:39 sys -rw-r----- 1 root root 23 May 14 22:39 xtrabackup_binlog_pos_innodb -rw-r----- 1 root root 527 May 14 22:39 xtrabackup_info -rw-r----- 1 root root 1 May 14 22:39 xtrabackup_master_key_id
5、还原属性
[root@centos7-liyj /data/mysql]#chown -R mysql.mysql /data/mysql [root@centos7-liyj /data/mysql]#ll total 122924 drwxr-x--- 2 mysql mysql 272 May 14 22:39 hellodb -rw-r----- 1 mysql mysql 290 May 14 22:39 ib_buffer_pool -rw-r----- 1 mysql mysql 12582912 May 14 22:39 ibdata1 -rw-r----- 1 mysql mysql 50331648 May 14 22:39 ib_logfile0 -rw-r----- 1 mysql mysql 50331648 May 14 22:39 ib_logfile1 -rw-r----- 1 mysql mysql 12582912 May 14 22:39 ibtmp1 drwxr-x--- 2 mysql mysql 4096 May 14 22:39 mysql drwxr-x--- 2 mysql mysql 8192 May 14 22:39 performance_schema drwxr-x--- 2 mysql mysql 8192 May 14 22:39 sys -rw-r----- 1 mysql mysql 23 May 14 22:39 xtrabackup_binlog_pos_innodb -rw-r----- 1 mysql mysql 527 May 14 22:39 xtrabackup_info -rw-r----- 1 mysql mysql 1 May 14 22:39 xtrabackup_master_key_id
6、启动数据服务
启动报错
[root@centos7-liyj /data/mysql]#systemctl start mysqld Job for mysqld.service failed because the control process exited with error code. See systemctl status mysqld.service and journalctl -xe for details.
原因备份库开启了二进制日志，还原库完成后没有二进制日志目录
（1）修改server-id
[root@centos7-liyj /data/mysql]#cat /etc/my.cnf [mysqld] server-id=77 #修改server-id log-bin=/data/mysql/logbin/mysql-bin
（2）建立二进制日志目录路径
[root@centos7-liyj /data/mysql]#mkdir /data/mysql/logbin [root@centos7-liyj /data/mysql]#chown -R mysql.mysql /data/mysql
重启数据库服务成功
[root@centos7-liyj /data/mysql]#systemctl start mysqld
登录检查数据库
[root@centos7-liyj ~]#mysql -uroot -patech123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.37-log MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | logbin | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.01 sec) mysql> use hellodb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from students ; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | | 26 | Dugu qiubai | 40 | M | 1 | NULL | +-------+---------------+-----+--------+---------+-----------+ 26 rows in set (0.00 sec) mysql> select * from teachers ; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | | 5 | Yuan Tiangang | 80 | M | +-----+---------------+-----+--------+ 5 rows in set (0.00 sec) mysql>详情
注意：生产中禁止 使用 select * from students；查询表全部内容
生产中数据庞大，查看所有内容造成大量的I/O读取，机器直接卡死崩溃，严重影响业务