-
1 Xtrabackup优点 2 （1）备份速度快，物理备份可靠 3 （2）备份过程不会打断正在执行的事务（无需锁表） 4 （3）能够基于压缩等功能节约磁盘空间和流量 5 （4）自动备份校验 6 （5）还原速度快 7 （6）可以流传将备份传输到另外一台机器上 8 （7）在不增加服务器负载的情况备份数据 9 xtrabackup的下载 10 在网页上下载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.后利用xftp传输 11 下载链接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. 12 解压 13 [root@localhost ~]# tar xf Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar 14 [root@localhost ~]# ls 15 all-201902211406.sql 16 anaconda-ks.cfg 17 Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar 18 percona-xtrabackup-24-2.4.21-1.el8.x86_64.rpm 19 percona-xtrabackup-24-debuginfo-2.4.21-1.el8.x86_64.rpm 20 percona-xtrabackup-24-debugsource-2.4.21-1.el8.x86_64.rpm 21 percona-xtrabackup-test-24-2.4.21-1.el8.x86_64.rpm 22 percona-xtrabackup-test-24-debuginfo-2.4.21-1.el8.x86_64.rpm 23 安装percona-xtrabackup-24-2.4.21-1.el8.x86_64.rpm包 24 [root@localhost ~]# yum -y install percona-xtrabackup-24-2.4.21-1.el8.x86_64.rpm 25 26 Installing: 27 percona-xtrabackup-24 28 x86_64 2.4.21-1.el8 @commandline 7.7 M 29 安装依赖关系: 30 libev x86_64 4.24-6.el8 AppStream 52 k 31 32 已安装: 33 percona-xtrabackup-24-2.4.21-1.el8.x86_64 34 libev-4.24-6.el8.x86_64 35 安装 percona*所有包 36 [root@localhost ~]# yum -y install percona* 37 已安装: 38 percona-xtrabackup-24-debuginfo-2.4.21-1.el8.x86_64 39 percona-xtrabackup-24-debugsource-2.4.21-1.el8.x86_64 40 percona-xtrabackup-test-24-2.4.21-1.el8.x86_64 41 percona-xtrabackup-test-24-debuginfo-2.4.21-1.el8.x86_64 42 备份 43 [root@localhost backups]# innobackupex --host=192.168.1.10 --user=root --password=wangming123! /backups/ 44 45 #--user=root 指定备份用户 46 #--password=123456 指定备份用户密码 47 #--host 指定客户机主机IP地址 48 #/backups 指定备份目录 49 [root@localhost backups]# ll 2020-12-29_21-41-12 #查看备份数据信息 50 total 12340 51 -rw-r-----. 1 root root 487 Dec 29 21:41 backup-my.cnf 52 -rw-r-----. 1 root root 848 Dec 29 21:41 ib_buffer_pool 53 -rw-r-----. 1 root root 12582912 Dec 29 21:41 ibdata1 54 drwxr-x---. 2 root root 58 Dec 29 21:41 information 55 drwxr-x---. 2 root root 4096 Dec 29 21:41 mysql 56 drwxr-x---. 2 root root 8192 Dec 29 21:41 performance_schema 57 drwxr-x---. 2 root root 138 Dec 29 21:41 school 58 drwxr-x---. 2 root root 8192 Dec 29 21:41 sys 59 -rw-r-----. 1 root root 21 Dec 29 21:41 xtrabackup_binlog_info #mysql 60 -rw-r-----. 1 root root 135 Dec 29 21:41 xtrabackup_checkpoints 61 -rw-r-----. 1 root root 489 Dec 29 21:41 xtrabackup_info 62 -rw-r-----. 1 root root 2560 Dec 29 21:41 xtrabackup_logfile 63 使用以下命令使相关数据性文件保持一致性状态 64 [root@localhost ~]# innobackupex --apply-log /backups/2020-12-29_21-41-12 65 删除数据库所有文件再恢复 66 先关闭MySQL服务 67 [root@localhost backups]# service mysqld stop 68 Shutting down MySQL.. SUCCESS! 69 [root@localhost backups]# innobackupex --apply-log /backups/2020-12-29_21-41-12/ #合并数据 70 [root@localhost backups]# rm -rf /opt/data/ 71 72 [root@localhost backups]# innobackupex --copy-back /backups/2020-12-29_21-41-12/ 73 恢复数据 74 [root@localhost backups]# ll /opt/data/ 75 total 122924 76 -rw-r-----. 1 root root 848 Dec 29 22:19 ib_buffer_pool 77 -rw-r-----. 1 root root 12582912 Dec 29 22:19 ibdata1 78 -rw-r-----. 1 root root 50331648 Dec 29 22:19 ib_logfile0 79 -rw-r-----. 1 root root 50331648 Dec 29 22:19 ib_logfile1 80 -rw-r-----. 1 root root 12582912 Dec 29 22:19 ibtmp1 81 drwxr-x---. 2 root root 58 Dec 29 22:19 information 82 drwxr-x---. 2 root root 4096 Dec 29 22:19 mysql 83 drwxr-x---. 2 root root 8192 Dec 29 22:19 performance_schema 84 drwxr-x---. 2 root root 138 Dec 29 22:19 school 85 drwxr-x---. 2 root root 8192 Dec 29 22:19 sys 86 -rw-r-----. 1 root root 24 Dec 29 22:19 xtrabackup_binlog_pos_innodb 87 -rw-r-----. 1 root root 489 Dec 29 22:19 xtrabackup_info 88 -rw-r-----. 1 root root 1 Dec 29 22:19 xtrabackup_master_key_id 89 开启mysql服务 90 [root@localhost backups]# service mysqld start 91 [root@localhost backups]# ss -antl 92 State Recv-Q Send-Q Local Address:Port Peer Address:Port 93 LISTEN0 128 0.0.0.0:22 0.0.0.0:* 94 LISTEN0 128 [::]:22 [::]:* 95 LISTEN0 80 *:3307 *:* 96 [root@localhost ~]# mysql -e 'show databases;' 97 mysql> show databases; 98 +--------------------+ 99 | Database | 100 +--------------------+ 101 | information_schema | 102 | mysql | 103 | performance_schema | 104 | wangming | 105 +--------------------+ 106 4 rows in set (0.02 sec) 107 使用增量数据恢复数据 108 Xtrabackup增量备份与恢复 109 增量备份仅能应用于InooDB或XtraDB表，对于MyISAM表，增量与全备相同，在上面的基础上继续创建一个opop2的库然后在opop-2的库里建一张表并插入数据 110 mysql> create database wangming ; 111 mysql> use wangming; 112 mysql> create table wangming(id int unsigned primary key auto_increment,name varchar(30)); 113 mysql> insert into wangming(name) values('zhangsan'),('lisi'),('wangwu'),('maliu'); 114 mysql> commit; 115 mysql> select * fromwangming; 116 [root@localhost backups]# mysql -e 'show databases;' 117 +--------------------+ 118 | Database | 119 +--------------------+ 120 | information_schema | 121 | mysql | 122 | performance_schema | 123 | wangming | 124 +--------------------+ 125 开始备份 126 [root@localhost backups]# innobackupex --user=root --password=' --host=192.168.24.137 /backups/ 127 增量备份 128 [root@localhost backups]# innobackupex --user=root --password='wangming123!;‘ --host=192.168.24.137 --incremental /backups/ --incremental-basedir=/backups/2020-12-29_22-33-28 129 [root@master 2018-07-30_11-01-37]# cat xtrabackup_checkpoints #查看全量备份的xtrabackup_checkpoints 130 backup_type = full-backuped #备份类型为全量备份 131 from_lsn = 0 #lsn从0开始 132 to_lsn = 3127097 #lsn到3127097结束 133 last_lsn = 3127097 134 compact = 0 135 recover_binlog_info = 0 136 137 [root@master 2018-07-30_13-51-47]# cat xtrabackup_checkpoints #查看增量备份的xtrabackup_checkpoints 138 backup_type = incremental #备份类型为增量备份 139 from_lsn = 3127097 #lsn从3127097开始 140 to_lsn = 3158741 #lsn到啊3158741结束 141 last_lsn = 3158741 142 compact = 0 143 recover_binlog_info = 0 144 145 删除一条数据来测试增量恢复 （这里删除的是增量备份的数据） 146 [root@localhost ~]#mysql 147 mysql> use wangming; 148 mysql>mysql> select * from wangming; 149 +----+-------------+ 150 | id | name | 151 +----+-------------+ 152 | 1 | zhangshan | 153 | 2 | lisi | 154 | 3 | wangwu | 155 | 4 | maliu | 156 mysql> delete from wangmng where name='lisi'; 157 mysql> select * from wangming; #确认lisi用户已经删除 158 +----+-------------+ 159 | id | name | 160 +----+-------------+ 161 | 1 | zhangshan | 162 | 3 | wangwu | 163 | 4 | maliu | 164 增量恢复操作过程如下 165 [root@localhost ~]# innobackupex --apply-log --redo-only /root/2018-10-25_08-27-43/ #第一次的全局备份数据目录 166 [root@localhost ~]# innobackupex --apply-log --redo-only /root/2018-10-25_08-27-43/ --incremental-dir=/root/2018-10-25_11-00-25/ #第二次的增量备份数据目录 167 恢复全部数据： 168 [root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back /root/2018-10-25_08-27-43/ 169 查看数据是否恢复 170 mysql> use wangming; 171 mysql>mysql> select * from wangming; 172 +----+-------------+ 173 | id | name | 174 +----+-------------+ 175 | 1 | zhangshan | 176 | 2 | lisi | 177 | 3 | wangwu | 178 | 4 | maliu |
数据以恢复成功