级联 mysqlbackup+GTID+无损复制

 

数据库环境准备

三台

 

1. 配置启动主从复制参数

mysql -uroot -proot

create user 'repuser'@'%' identified by 'repuser123';

grant replication slave on *.* to 'repuser'@'%';

flush privileges;

select user,host from mysql.user;

 

## 192.168.1.51(master)

vi /mysql/data/3306/my.cnf

#master modify parameter

bind-address=192.168.1.51

server_id=513306

#master add parameter

log_bin=/mysql/log/3306/binlog/itpuxdb-binlog

log_bin_index=/mysql/log/3306/binlog/itpuxdb-binlog.index

binlog_format=row

binlog_rows_query_log_events=on

 

skip_name_resolve = on

expire_logs_days = 7

innodb_support_xa =1

binlog_cache_size = 1M

max_binlog_size = 2048M

log_bin_trust_function_creators = 1

innodb_flush_log_at_trx_commit =1

sync_binlog = 1

transaction-isolation = read-committed

gtid_mode = on

enforce_gtid_consistency = 1

#log-slave-updates = 1

binlog_gtid_simple_recovery=1

#--------------------------

#slave parameter

#--------------------------

#relay_log = /mysql/log/3306/relaylog/itpuxdb-relay.log

#read_only=1

#slave-parallel-type=LOGICAL_CLOCK

#slave-parallel-workers=4

#master_info_repository=TABLE

#relay_log_info_repository=TABLE

#relay_log_recovery=1

#slave_skip_errors = ddl_exist_errors

#slave_preserve_commit_order=1

#------------------------------------

#semi sync replication settings

#------------------------------------

plugin_dir=/mysql/app/mysql/lib/plugin/

plugin_load ="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

loose_rpl_semi_sync_master_enabled = 1

loose_rpl_semi_sync_slave_enabled = 1

loose_rpl_semi_sync_master_timeout = 5000

rpl_semi_sync_master_wait_point = AFTER_SYNC

rpl_semi_sync_master_wait_for_slave_count = 1

## 192.168.1.52/53(slave)

vi /mysql/data/3306/my.cnf

#slave modify parameter

bind-address=192.168.1.52

server_id=523306

#slave add parameter

log_bin=/mysql/log/3306/binlog/itpuxdb-binlog

log_bin_index=/mysql/log/3306/binlog/itpuxdb-binlog.index

binlog_format=row

binlog_rows_query_log_events=on

 

skip_name_resolve = on

expire_logs_days = 7

innodb_support_xa =1

binlog_cache_size = 1M

max_binlog_size = 2048M

log_bin_trust_function_creators = 1

innodb_flush_log_at_trx_commit =1

sync_binlog = 1

transaction-isolation = read-committed

gtid_mode = on

enforce_gtid_consistency = 1

log-slave-updates = 1

binlog_gtid_simple_recovery=1

#------------------------------------

#slave parameter

#------------------------------------

relay_log = /mysql/log/3306/relaylog/itpuxdb-relay.log

log-slave-updates = 1

read_only=1

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=4

master_info_repository=table

relay_log_info_repository=table

relay_log_recovery=1

slave_skip_errors = ddl_exist_errors

slave_preserve_commit_order=1

#------------------------------------

#semi sync replication settings

plugin_dir=/mysql/app/mysql/lib/plugin/

plugin_load ="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

loose_rpl_semi_sync_master_enabled = 1

loose_rpl_semi_sync_slave_enabled = 1

loose_rpl_semi_sync_master_timeout = 5000

rpl_semi_sync_master_wait_point = AFTER_SYNC

rpl_semi_sync_master_wait_for_slave_count = 1

1. 数据备份到51

主从 3 台：

cd /opt

scp MySQL* 192.168.0.51:/opt

scp MySQL* 192.168.0.52:/opt

unzip MySQL*.zip

tar zxvf meb-4.1.1-linux-glibc2.12-x86-64bit.tar.gz

ln -sf meb-4.1.1-linux-glibc2.12-x86-64bit mysqlbackup

cp mysqlbackup/bin/mysqlbackup /mysql/app/mysql/bin/

mysqlbackup --version

mkdir -p /mysql/backup/backup-db

mkdir -p /mysql/backup/backup-tmp

mkdir -p /mysql/backup/restore-tmp

chown -R mysql:mysql /mysql/backup

chmod -R 775 /mysql/backup

 

set global event_scheduler =1; 存储过程做下

create database itpuxdb;

drop table itpuxdb.itpux12;

create table itpuxdb.itpux12(

id int not null primary key auto_increment,

name varchar(60),

age int);

use itpuxdb;

select count(*) from itpuxdb.itpux12;

 

主库 51：

mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=root --password=root --backup-image=/mysql/backup/backup-db/fullbackup`date +%Y%m%d`.mbi --with-timestamp backup-to-image --backup-dir=/mysql/backup/backup-tmp

#验证

mysqlbackup --backup-image=/mysql/backup/backup-db/fullbackup20210418.mbi validate

从库 52：

scp 192.168.0.50:/mysql/backup/backup-db/fullbackup20210418.mbi /mysql/backup/backup-db/

systemctl stop mysqld

cd /mysql/data/3306

rm -rf data_bak

mv data data_bak

mkdir data

mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=root --password=root --datadir=/mysql/data/3306/data --backup-image=/mysql/backup/backup-db/fullbackup20210418.mbi --backup-dir=/mysql/backup/restore-tmp copy-back-and-apply-log

图解：报错，改一条下面的参数

#验证不通过改  my.cnf       参数改成：innodb_checksum_algorithm=crc32

chown -R mysql:mysql /mysql/data/3306/data

systemctl restart mysqld

mysql -uroot -proot

show databases;

select * from itpuxdb.itpux12;

1. 51做主从同步

图解：gtid的位置

mysql -uroot -proot

reset master;

reset slave;

set @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;

set @@SESSION.SQL_LOG_BIN= 0;

set @@GLOBAL.GTID_PURGED='e5cae73c-9e8a-11eb-aa29-000c294d6ea1:1-131';

set @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

stop slave;

change master to

master_host='192.168.0.50',

master_port=3306,

master_user='repuser',

master_password='repuser123',

master_auto_position=1;

start slave;

show slave status\G

select count(*) from itpuxdb.itpux12;

1. 配置从库的从库

图解：想做级联，前提是中间这个库的日志和主库一致才行，read和exec一样 Read_Master_Log_Pos: 1019023，且日志文件一样 Master_Log_File: itpuxdb-binlog.000002

从库192.168.0. 51：

mysql -uroot -proot -e "stop slave IO_THREAD;" 

备份：

mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=root --password=root --backup-image=/mysql/backup/backup-db/fullbackup51`date +%Y%m%d`.mbi --with-timestamp backup-to-image --backup-dir=/mysql/backup/backup-tmp

mysqlbackup --backup-image=/mysql/backup/backup-db/fullbackup5120210418.mbi validate

从从库 52：

scp 192.168.0.51:/mysql/backup/backup-db/fullbackup5120210418.mbi /mysql/backup/backup-db/

systemctl stop mysqld

cd /mysql/data/3306

rm -rf data_bak

mv data data_bak

mkdir data

mysqlbackup --defaults-file=/mysql/data/3306/my.cnf --user=root --password=root --datadir=/mysql/data/3306/data --backup-image=/mysql/backup/backup-db/fullbackup.mbi --backup-dir=/mysql/backup/restore-tmp copy-back-and-apply-log

#验证 my.cnf   改掉 innodb_checksum_algorithm=crc32

chown -R mysql:mysql /mysql/data/3306/data

systemctl restart mysqld

mysql -uroot -proot

show databases;

select count(*) from itpuxdb.itpux12;

主从同步

mysql -uroot -proot

reset master;

reset slave;

set @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;

set @@SESSION.SQL_LOG_BIN= 0;

set @@GLOBAL.GTID_PURGED='e5cae73c-9e8a-11eb-aa29-000c294d6ea1:1-3189';

set @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

stop slave;

change master to

master_host='192.168.0.51',

master_port=3306,

master_user='repuser',

master_password='repuser123',

master_auto_position=1;

从库 51：

mysql -uroot -proot -e "start slave IO_THREAD;" 

从库 52：

mysql -uroot -proot

start slave;

show slave status\G   #确认下 Master_Host: 

select count(*) from itpuxdb.itpux12;

1. 把主库 50 关机，对外的业务指向 51，这个时候，还是主从。

1.关闭主库，

systemctl stop mysqld

52 创建 3 个数据库和表的数据：

create database itpuxdb3;

use itpuxdb3;

create table itpuxdb3.itpuxbak31 (id int,name varchar(40));

create table itpuxdb3.itpuxbak32 (id int,name varchar(40));

insert into itpuxdb3.itpuxbak31 values(1,'itpux311'),(2,'itpux312'),(3,'itpux313'),(4,'itpux314'),(5,'itpux315');

insert into itpuxdb3.itpuxbak32 values(1,'itpux321'),(2,'itpux322'),(3,'itpux323'),(4,'itpux324'),(5,'itpux325');

commit;

select * from itpuxdb3.itpuxbak31;

select * from itpuxdb3.itpuxbak32;

备库 52 检查：

select * from itpuxdb3.itpuxbak31;

select * from itpuxdb3.itpuxbak32;