20220516 percona tokudb 主从建设和xtraback问题

背景说明

1、当前 Percona Server 版本 ，且开启了GTID，后面即将添加的从库也开启了GIID

Server version: 5.7.27-30-log Percona Server (GPL), Release 30, Revision 8916819

2、当前已经支持的 Engine，主要是为了说明有 TokuDB

+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| TokuDB             | YES     | Percona TokuDB Storage Engine with Fractal Tree(tm) Technology             | YES          | YES  | YES        |
| MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+

3、在利用Percona Server自带 Xtrabackup 进行备份的时候报错

... ...
Warning: "analysis.alarmRecord" uses engine "TokuDB" and will not be backed up.
Warning: "analysis.bizMonitorConfig" uses engine "TokuDB" and will not be backed up.
... ...

Xtrabackup 进行备份

使用常规xtrabackup备份，这里采用的是流备份，即在备份的过程中直接把备份数据同步到目标主机去，可以节约最终的传输时间

innobackupex --defaults-file=/data/analysisdb_mysqldata/my.cnf --user=root -S/data/analysisdb_mysqldata/mysql.sock -pxxx  --stream=xbstream /data/temp/  | ssh root@db-backup-xxxx "xbstream -x -C  /data/xxxx_mysqldata/"

可以看到在输出的结果有下列 Warning信息

... ...
Warning: "analysis.alarmRecord" uses engine "TokuDB" and will not be backed up.
... ...

发现 TokuDB 引擎的表是没有被备份过去的，如果你的数据量不是很大的话，你可以等到同步完成然后恢复数据库之后去检查表alarmRecord 发现会报错，提示表对应的文件不存在

升级版 Xtrabackup 进行备份

Github上有人基于官方版本进行了修改，支持Tokudb的备份。 项目地址： https://github.com/XeLabs/tokudb-xtrabackup

tokudb-xtrabackup 的安装

1、安装依赖

yum install automake autoconf libtool cmake bison flex libaio-devl libncurses-dev libev-devel libcurl-devel libgcrypt-devel  vim-common

注意： 如果安装过程报如下错误等，说明没有安装对应的第三方包

CMake Error: The following variables are used in this project, but they are set to NOTFOUND.
Please set them or make sure they are set and tested correctly in the CMake files:
GCRYPT_INCLUDE_DIR
   used as include directory in directory /opt/app/tokudb-xtrabackup-tokudb-xtrabackup-2.4.7/storage/innobase/xtrabackup/src
   used as include directory in directory /opt/app/tokudb-xtrabackup-tokudb-xtrabackup-2.4.7/storage/innobase/xtrabackup/src
   used as include directory in directory /opt/app/tokudb-xtrabackup-tokudb-xtrabackup-2.4.7/storage/innobase/xtrabackup/src
   used as include directory in directory /opt/app/tokudb-xtrabackup-tokudb-xtrabackup-2.4.7/storage/innobase/xtrabackup/src
   used as include directory in directory /opt/app/tokudb-xtrabackup-tokudb-xtrabackup-2.4.7/storage/innobase/xtrabackup/src
GCRYPT_LIB

请确认正确安装了 libev-devel libcurl-devel libgcrypt-devel 这三个

2、编译安装

# 进入对应的目录
cd /opt/app/tokudb-xtrabackup-tokudb-xtrabackup-2.4.7/

# 创建独立的安装目录
mkdir /opt/app/percona-tokudb-xtrabackup

# 编译
cmake .  -DBUILD_CONFIG=xtrabackup_release  -DWITH_BOOST=extra/boost/boost_1_59_0.tar.gz  -DWITH_MAN_PAGES=OFF  -DCMAKE_INSTALL_PREFIX=/opt/app/percona-tokudb-xtrabackup

make VERSIONS=1

# 安装 
make install 

3、安装成功之后验证

[root@test-xxx-02-vm bin]# ls -l
total 202284
lrwxrwxrwx 1 root root        10 May 16 14:15 innobackupex -> xtrabackup
-rwxr-xr-x 1 root root   5282704 May 16 13:46 xbcloud
-rwxr-xr-x 1 root root      3020 May 16 12:30 xbcloud_osenv
-rwxr-xr-x 1 root root   5133024 May 16 13:46 xbcrypt
-rwxr-xr-x 1 root root   5242616 May 16 13:46 xbstream
-rwxr-xr-x 1 root root 191468872 May 16 13:59 xtrabackup
[root@test-xxx-02-vm bin]# ./xtrabackup --version
./xtrabackup version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: )

4、正确配置环境变量

我们知道环境中已经存在Percona官网自带的xtrabackup，但是这里想用刚才安装的支持tokudb的xtrabackup，最简单的就是修改环境变量PATH，将新增的路径放到PATH的最前面

# vim ~/.bashrc
export PATH=/opt/app/percona-tokudb-xtrabackup/bin:$PATH

然后在新的session中验证

[root@test-xxx-02-vm /tmp]# xtrabackup --version
xtrabackup version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: )

5、最后我们执行第二趴的流备份命令，进行数据的备份

innobackupex --defaults-file=/data/analysisdb_mysqldata/my.cnf --user=root -S/data/analysisdb_mysqldata/mysql.sock -pxxx  --stream=xbstream /data/temp/  | ssh root@db-backup-xxxx "xbstream -x -C  /data/xxxx_mysqldata/"

发现没有不支持 Tokudb 引擎的告警信息了

GTID 配置MySQL主从

在完成上面的一步的备份（我们是直接把数据备份在了目标主机的MySQL的数据目录下），然后配置 my.cnf 配置文件，启动MySQL之后检查服务是否正常。如果正常继续配置主从同步

mysql> stop slave;

mysql> set global gtid_purged = '815b83e3-af75-11e8-afeb-180373f8f6bd:1-87,c2269682-b299-11e8-8865-d4ae527816fe:1-10011892402,c85da7a3-b2f2-11e8-954f-180373f8f6bd:1-203938358,f3cd6fbf-ac55-11e8-992b-1866dae834f8:1-20389622';

mysql> change master to master_host='192.168.2.190', master_user='repl', master_password='repl_pass', master_auto_position=1;

mysql> start slave;

mysql> show slave status;

扩展

1、xtrabackup 默认支持全量热备份，不支持增量备份

开启全量热备份

# 创建备份目录，并修改权限
mkdir /data/backup/xxx_backup/ && chown -R mysql:mysql /data/backup/xxx_backup/

# 在MySQL中开启全量热备份
mysql> set tokudb_backup_dir='/data/backup/xxx_backup/';

注意 热备份的时候需要关闭异步IO

innodb_use_native_aio = 0

2、实现

参考：

https://blog.csdn.net/weixin_30621711/article/details/97964566
https://blog.csdn.net/weixin_30621711/article/details/97964566
https://blog.csdn.net/debimeng/article/details/80422959
https://www.cnblogs.com/bjx2020/p/11157700.html

https://blog.51cto.com/hcymysql/2130988