mysql 5.7 使用 xtrabackup 添加新的slave

os: ubuntu 16.04
db: mysql 5.7.25

规划如下:

192.168.56.92	node1 # mysql master
192.168.56.90	node2 # mysql slave
192.168.56.88	node3 # mysql slave

添加一个新的salve 节点 node4

192.168.56.86	node4 # mysql slave

mysql 安装

参考<<ubuntu 16.04 + mysql 5.7 + apt 安装>> 在node4 节点上安装好 mysql,参数文件 /etc/mysql/mysql.conf.d/mysqld.cnf 请参考其余节点.

xtrabackup 安装

参考<<percona xtrabackup 简介>> 在node1 node4 两个节点上安装好 xtrabackup

xtrabackup 备份和还原

在 master 上做个全量备份和增量备份

# /usr/bin/xtrabackup --defaults-file="/etc/mysql/my.cnf" --backup --datadir=/var/lib/mysql --target-dir="/mnt/mysql_3306/xtrabackup_backup/20190305/ful" --user="root" --password="rootroot" --host="localhost" --port=3306 --socket="/var/run/mysqld/mysqld.sock" --no-version-check  --parallel=4 --throttle=15000 --use-memory=2GB --slave-info --rsync --tmpdir=/tmp 

# /usr/bin/xtrabackup --defaults-file="/etc/mysql/my.cnf" --backup --datadir=/var/lib/mysql --target-dir="/mnt/mysql_3306/xtrabackup_backup/20190305/inc" --incremental-basedir="/mnt/mysql_3306/xtrabackup_backup/20190305/ful" --user="root" --password="rootroot" --host="localhost" --port=3306 --socket="/var/run/mysqld/mysqld.sock" --no-version-check  --parallel=4 --throttle=15000 --use-memory=2GB --slave-info --rsync --tmpdir=/tmp 

tar 后传输到新 slave 机器上

# cd /mnt/mysql_3306/xtrabackup_backup
# tar -zcvf ./20190305.tar.gz ./20190305
# scp ./20190305.tar.gz 192.168.56.86:/mnt/mysql_3306/xtrabackup_backup

新 slave 上执行恢复操作

# cd /mnt/mysql_3306/xtrabackup_backup
# tar -zxvf ./20190305.tar.gz 

# 先 prepare 全量备份
# /usr/bin/xtrabackup --defaults-file="/etc/mysql/my.cnf" --prepare --apply-log-only --target-dir=/mnt/mysql_3306/xtrabackup_backup/20190305/ful
# 再 prepare 增量备份
# /usr/bin/xtrabackup --defaults-file="/etc/mysql/my.cnf" --prepare --apply-log-only --target-dir=/mnt/mysql_3306/xtrabackup_backup/20190305/ful --incremental-dir=/mnt/mysql_3306/xtrabackup_backup/20190305/inc
# 再 prepare 全量备份
# /usr/bin/xtrabackup --defaults-file="/etc/mysql/my.cnf" --prepare --target-dir=/mnt/mysql_3306/xtrabackup_backup/20190305/ful
# 再 拷贝数据
# /usr/bin/xtrabackup --copy-back --datadir=/var/lib/mysql --target-dir=/mnt/mysql_3306/xtrabackup_backup/20190305/ful

# chown -R mysql:mysql /var/lib/mysql/*
# systemctl start mysql

change master to

# cd /var/lib/mysql
# cat xtrabackup_info

uuid = 66a4910f-3efb-11e9-95f7-080027c780f8
name = 
tool_name = xtrabackup
tool_command = --defaults-file=/etc/mysql/my.cnf --backup --datadir=/var/lib/mysql --target-dir=/mnt/mysql_3306/xtrabackup_backup/20190305/inc/ --incremental-basedir=/mnt/mysql_3306/xtrabackup_backup/20190305/ful --user=root --password=... --host=localhost --port=3306 --socket=/var/run/mysqld/mysqld.sock --no-version-check --parallel=4 --throttle=15000 --use-memory=2GB --slave-info --rsync --tmpdir=/tmp
tool_version = 2.4.13
ibbackup_version = 2.4.13
server_version = 5.7.25-log
start_time = 2019-03-05 12:01:47
end_time = 2019-03-05 12:01:49
lock_time = 0
binlog_pos = filename 'mysql-bin.000002', position '1284', GTID of the last change 'c74577c8-3e31-11e9-bf54-080027c780f8:1-6'
innodb_from_lsn = 2524607
innodb_to_lsn = 2524607
partial = N
incremental = Y
format = file
compact = N
compressed = N
encrypted = N

slave 节点设置

# mysql -h 127.0.0.1 -P 3306 -u root -p
mysql> show slave status\G 

如果是对 master 做的备份, 执行 show slave status\G 的结果就是为空.需要 change master to

mysql> stop slave;
mysql> 
mysql> change master to
  master_host='192.168.56.92',
  master_port=3306,
  master_user='repl',
  master_password='mysqlmysql',
  master_log_file='mysql-bin.000002',
  master_log_pos=1284;

mysql> start slave\G  
mysql> show slave status\G

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
			

基于JTID的 change master to

mysql> change master to
  master_host='192.168.56.92',
  master_port=3306,
  master_user='repl',
  master_password='mysqlmysql',
  master_auto_position=1;
  

参考:
https://www.cnblogs.com/zhoujinyi/p/5893333.html

http://www.percona.com/software/percona-xtrabackup
https://www.percona.com/downloads/
https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/
https://www.percona.com/downloads/Percona-XtraBackup-LATEST/

mysql 初始化

# which mysqld
# /usr/sbin/mysqld --defaults-file=/etc/mysql/my.cnf --datadir=/var/lib/mysql/ --user=mysql --initialize-insecure --ssl --explicit_defaults_for_timestamp

# /usr/bin/mysql_ssl_rsa_setup --user=mysql

# systemctl start mysql

# mysql -h 127.0.0.1 -P 3306 -u root -p
Enter password: 
mysql> alter user 'root'@'localhost' identified by 'rootroot';