xtrabackup 8备份Mysql 8.0.18（全备全过程）身临其境乐动体育版

1，首先在mysql8里先建了一个稍微大一点的乐动体育测试表在LD90.VIP可看到更多，脚本：

CREATE TABLE user100w( id INT NOT NULL AUTO_INCREMENT, first_name VARCHAR(10) NOT NULL, last_name VARCHAR(10) NOT NULL, sex VARCHAR(5) NOT NULL, score INT NOT NULL, copy_id INT NOT NULL, PRIMARY KEY (id));
DROP PROCEDURE IF EXISTS add_user; DELIMITER // create PROCEDURE add_user(in num INT) BEGIN DECLARE rowid INT DEFAULT 0; DECLARE firstname CHAR(1); DECLARE name1 CHAR(1); DECLARE name2 CHAR(1); DECLARE lastname VARCHAR(3) DEFAULT ‘’; DECLARE sex CHAR(1); DECLARE score CHAR(2); WHILE rowid < num DO SET firstname = SUBSTRING(‘赵钱孙李周吴郑王林杨柳刘孙陈江阮侯邹高彭徐’,FLOOR(1+21RAND()),1); SET name1 = SUBSTRING(‘一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励’,ROUND(1+43RAND()),1); SET name2 = SUBSTRING(‘一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励’,ROUND(1+43*RAND()),1); SET sex=FLOOR(0 + (RAND() * 2)); SET score= FLOOR(40 + (RAND() *60)); SET rowid = rowid + 1; IF ROUND(RAND())=0 THEN SET lastname =name1; END IF; IF ROUND(RAND())=1 THEN SET lastname = CONCAT(name1,name2); END IF; insert INTO user100w (first_name,last_name,sex,score,copy_id) VALUES (firstname,lastname,sex,score,rowid); END WHILE; END //DELIMITER ;
call add_user(10000);
2，yum安装依赖

[root@centos1 ~]# yum install libev
3，下载xtrabackup后，直接用yum安装

[root@centos1 opt]# wget
https://www.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0-7/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.7-1.el7.x86_64.rpm

Saving to: ‘percona-xtrabackup-80-8.0.7-1.el7.x86_64.rpm’
100%[+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++=======================>] 12,177,712 22.2KB/s in 2m 20s 2019-11-08 17:39:37 (21.5 KB/s) - ‘percona-xtrabackup-80-8.0.7-1.el7.x86_64.rpm’ saved [12177712/12177712]
[root@centos1 opt]# yum localinstall percona-xtrabackup-80-8.0.7-1.el7.x86_64.rpm
Installed: percona-xtrabackup-80.x86_64 0:8.0.7-1.el7
Dependency Installed: 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
Complete!
[root@centos1 opt]# xtrabackup -vxtrabackup: recognized server arguments: --datadir=/var/lib/mysql xtrabackup version 8.0.7 based on MySQL server 8.0.16 Linux (x86_64) (revision id: 069e0e6)

问题来了我的mysql是8.0.18的（装好之后发现并没有什么问题）

4，开始备份

一些参数：

根据您的系统，您可能需要指定以下一个或多个选项才能连接到服务器：

选项 描述
-港口 使用TCP / IP连接到数据库服务器时使用的端口。
-插座 连接到本地数据库时使用的套接字。
-主办 使用TCP / IP连接到数据库服务器时要使用的主机。
备份（如果不是localhost的远程备份的话，mysql需要更新User表可以远程登录）：

[mysql@centos1 ~]$ xtrabackup --defaults-file=/data/mysql/my.cnf --host=localhost --user=root --password=mysqlWAI --port=3306 --socket=/data/mysql/run/mysql.sock --backup --target-dir=/home/mysql/xbackup/
备份时插入一条数据做测试

mysql> select * from test1 ;±-----±-----+| id | code |±-----±-----+| 1 | 3009 || 2 | 3010 || 3 | 3011 |±-----±-----+3 rows in set (0.00 sec)
mysql> insert into test1 values(4,3099);
备份完成会提示：

191111 18:09:24 All tables unlocked
191111 18:09:24 Backup created in directory ‘/home/mysql/xbackup/’
MySQL binlog position: filename ‘mysql-bin.000004’, position ‘155’
191111 18:09:24 [00] Writing /home/mysql/xbackup/backup-my.cnf
191111 18:09:24 [00] …done
191111 18:09:24 [00] Writing /home/mysql/xbackup/xtrabackup_info
191111 18:09:24 [00] …done
xtrabackup: Transaction log of lsn (23556373) to (23556739) was copied.
191111 18:09:24 completed OK!
查看一下备份的情况

[mysql@centos1 xbackup]$ ll
total 2142252
-rw-r----- 1 mysql mysql 499 Nov 11 18:09 backup-my.cnf
-rw-r----- 1 mysql mysql 2147483648 Nov 11 18:09 ibdata1
drwxr-x— 2 mysql mysql 157 Nov 11 18:09 mysql
-rw-r----- 1 mysql mysql 155 Nov 11 18:09 mysql-bin.000004
-rw-r----- 1 mysql mysql 36 Nov 11 18:09 mysql-bin.index
-rw-r----- 1 mysql my