//下载xtrabackup并安装 [root@localhost ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/redhat/8/x86_64/Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar [root@localhost ~]# ls anaconda-ks.cfg Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar [root@localhost ~]# tar xf Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar [root@localhost ~]# ls anaconda-ks.cfg Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar percona-xtrabackup-24-2.4.21-1.el8.x86_64.rpm percona-xtrabackup-24-debuginfo-2.4.21-1.el8.x86_64.rpm percona-xtrabackup-24-debugsource-2.4.21-1.el8.x86_64.rpm percona-xtrabackup-test-24-2.4.21-1.el8.x86_64.rpm percona-xtrabackup-test-24-debuginfo-2.4.21-1.el8.x86_64.rpm [root@localhost ~]# yum -y install percona* xtrabackup全量备份与恢复 总结全库备份与恢复三步曲： a. innobackupex全量备份，并指定备份目录路径； b. 在恢复前，需要使用--apply-log参数先进行合并数据文件，确保数据的一致性要求； c. 恢复时，直接使用--copy-back参数进行恢复，需要注意的是，在my.cnf中要指定数据文件目录的路径。 备份——语法： innobackupex --user=DBUSER --password=DBUSERPASS --defaults-file=/etc/my.cnf /path/to/BACKUP-DIR/ 恢复——语法： innobackupex --apply-log /backups/20XX-XX-XX_XX-XX-XX/ innobackupex --copy-back --defaults-file=/etc/my.cnf /backups/20XX-XX-XX_XX-XX-XX/ 全量备份 //授权root用户在所有位置上远程登录访问root数据库 [root@localhost ~]# mysql -uroot -p123 mysql> grant all on *.* to 'root'@'%' identified by '123'; mysql> flush privileges; mysql> quit Bye [root@localhost ~]# innobackupex --user=root --password=123 --host=127.0.0.1 /backups/ 201229 19:23:54 completed OK! [root@localhost ~]# ll /backups/ total 0 drwxr-x---. 6 root root 207 Dec 29 19:23 2020-12-29_19-23-50 [root@localhost ~]# ll /backups/2020-12-29_19-23-50/ total 12336 -rw-r-----. 1 root root 487 Dec 29 19:23 backup-my.cnf -rw-r-----. 1 root root 388 Dec 29 19:23 ib_buffer_pool -rw-r-----. 1 root root 12582912 Dec 29 19:23 ibdata1 drwxr-x---. 2 root root 4096 Dec 29 19:23 mysql drwxr-x---. 2 root root 8192 Dec 29 19:23 performance_schema drwxr-x---. 2 root root 58 Dec 29 19:23 school drwxr-x---. 2 root root 8192 Dec 29 19:23 sys -rw-r-----. 1 root root 135 Dec 29 19:23 xtrabackup_checkpoints -rw-r-----. 1 root root 437 Dec 29 19:23 xtrabackup_info -rw-r-----. 1 root root 2560 Dec 29 19:23 xtrabackup_logfile 恢复 [root@localhost ~]# service mysqld stop Shutting down MySQL.. SUCCESS! //合并数据，使数据文件处于一致性的状态 [root@localhost ~]# innobackupex --apply-log /backups/2020-12-29_19-23-50/ 201229 19:29:05 completed OK! //删除原有的数据 [root@localhost ~]# rm -rf /opt/data/ [root@localhost ~]# vim /etc/my.cnf [mysqld] datadir=/opt/data //数据恢复 [root@localhost ~]# innobackupex --copy-back /backups/2020-12-29_19-23-50/ 201229 19:34:26 completed OK! //修改属主属组 [root@localhost ~]# ll /opt/data/ total 122920 -rw-r-----. 1 root root 388 Dec 29 19:34 ib_buffer_pool -rw-r-----. 1 root root 12582912 Dec 29 19:34 ibdata1 -rw-r-----. 1 root root 50331648 Dec 29 19:34 ib_logfile0 -rw-r-----. 1 root root 50331648 Dec 29 19:34 ib_logfile1 -rw-r-----. 1 root root 12582912 Dec 29 19:34 ibtmp1 drwxr-x---. 2 root root 4096 Dec 29 19:34 mysql drwxr-x---. 2 root root 8192 Dec 29 19:34 performance_schema drwxr-x---. 2 root root 58 Dec 29 19:34 school drwxr-x---. 2 root root 8192 Dec 29 19:34 sys -rw-r-----. 1 root root 437 Dec 29 19:34 xtrabackup_info -rw-r-----. 1 root root 1 Dec 29 19:34 xtrabackup_master_key_id [root@localhost ~]# chown -R mysql.mysql /opt/data/ //启动mysql [root@localhost ~]# service mysqld start Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'. SUCCESS! //查看数据，是否恢复 [root@localhost ~]# mysql -uroot -p123 -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | sys | +--------------------+ xtrabackup增量备份与恢复 增量备份 //全备数据 [root@localhost ~]# innobackupex --user=root --password=123 --host=127.0.0.1 /backups/ 201229 19:50:12 completed OK! mysql> create database student; Query OK, 1 row affected (0.00 sec) //创建student库并创建testtb表插入若干数据 [root@localhost ~]# mysql -uroot -p123456 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 15 Server version: 5.7.31 MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 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> use student; Database changed mysql> create table testtb(id int); Query OK, 0 rows affected (0.01 sec) mysql> insert into testtb values(1),(10),(99); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from testtb; +------+ | id | +------+ | 1 | | 10 | | 99 | +------+ 3 rows in set (0.00 sec) mysql> quit; Bye //使用innobackupex进行增量备份 [root@localhost ~]# innobackupex --user=root --password=123 --host=127.0.0.1 --incremental /backups/ --incremental-basedir=/backups/2020-12-29_19-50-10/ xtrabackup: Transaction log of lsn (4247071) to (4247080) was copied. 201229 19:57:26 completed OK! [root@localhost ~]# ll /backups/ total 0 drwxr-x---. 6 root root 207 Dec 29 19:50 2020-12-29_19-50-10 //全量备份数据目录 drwxr-x---. 7 root root 248 Dec 29 19:57 2020-12-29_19-57-24 //增量备份数据目录 //查看全量备份的xtrabackup_checkpoints [root@localhost ~]# cd /backups/2020-12-29_19-50-10/ [root@localhost 2020-12-29_19-50-10]# cat xtrabackup_checkpoints backup_type = full-backuped //备份类型为全量备份 from_lsn = 0 to_lsn = 4240955 last_lsn = 4240964 compact = 0 recover_binlog_info = 0 flushed_lsn = 4240964 //查看增量备份的xtrabackup_checkpoints [root@localhost 2020-12-29_19-50-10]# cd /backups/2020-12-29_19-57-24/ [root@localhost 2020-12-29_19-57-24]# cat xtrabackup_checkpoints backup_type = incremental //备份类型为增量备份 from_lsn = 4240955 to_lsn = 4247071 last_lsn = 4247080 compact = 0 recover_binlog_info = 0 flushed_lsn = 4247080 增量备份后数据恢复 //模拟mysql故障，删除数据目录所有数据 [root@localhost ~]# service mysqld stop Shutting down MySQL.. SUCCESS! [root@localhost ~]# rm -rf /opt/data/* //合并全备数据目录，确保数据的一致性 [root@localhost ~]# innobackupex --apply-log --redo-only /backups/2020-12-29_19-50-10/ xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 4240973 InnoDB: Number of pools: 1 201229 21:15:41 completed OK! //将增量备份数据合并到全备数据目录当中 [root@localhost ~]# innobackupex --apply-log --redo-only /backups/2020-12-29_19-50-10/ --incremental-dir=/backups/2020-12-29_19-57-24 201229 21:21:24 [00] ...done 201229 21:21:24 completed OK! //查看类型 [root@localhost ~]# cat /backups/2020-12-29_19-50-10/xtrabackup_checkpoints backup_type = log-applied //查看到数据备份类型是增加 from_lsn = 0 to_lsn = 4247071 last_lsn = 4247080 compact = 0 recover_binlog_info = 0 flushed_lsn = 4247080 //恢复数据 [root@localhost ~]# innobackupex --copy-back /backups/2020-12-29_19-50-10/ 201229 21:23:13 [01] ...done 201229 21:23:13 completed OK! //更改数据的属主属组 [root@localhost ~]# ll /opt/data/ total 110632 -rw-r-----. 1 root root 388 Dec 29 21:23 ib_buffer_pool -rw-r-----. 1 root root 12582912 Dec 29 21:23 ibdata1 -rw-r-----. 1 root root 50331648 Dec 29 21:23 ib_logfile0 -rw-r-----. 1 root root 50331648 Dec 29 21:23 ib_logfile1 drwxr-x---. 2 root root 4096 Dec 29 21:23 mysql drwxr-x---. 2 root root 8192 Dec 29 21:23 performance_schema drwxr-x---. 2 root root 58 Dec 29 21:23 school drwxr-x---. 2 root root 56 Dec 29 21:23 student drwxr-x---. 2 root root 8192 Dec 29 21:23 sys -rw-r-----. 1 root root 509 Dec 29 21:23 xtrabackup_info -rw-r-----. 1 root root 1 Dec 29 21:23 xtrabackup_master_key_id [root@localhost ~]# chown -R mysql.mysql /opt/data/ //启动mysql [root@localhost ~]# service mysqld start Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'. . SUCCESS! //查看数据是否恢复 [root@localhost ~]# mysql -uroot -p123 -e "show databases;" mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | student | | sys |