一主三从安全复制 procolaxbackup+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

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

#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

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/54(slave)

vi /mysql/data/3306/my.cnf

#slave modify parameter

bind-address=192.168.1.52/53/54

server_id=523306/533306/543306

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

#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

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

systemctl restart mysqld

mysql -uroot -proot < /soft/itpuxdb.sql

1. 主库备份并恢复到从库：

准备两个包到4个库

scp /soft/mysql-connector-python-2.1.7-1.el7.x86_64.rpm 192.168.1.52:/soft

scp /soft/percona-xtrabackup-2.4.11-Linux-x86_64.libgcrypt145.tar.gz 192.168.1.52:/soft

scp /soft/rsync-3.1.2.tar.gz 192.168.1.52:/soft

 

主从库：

cd /mysql/app

tar zxvf /opt/percona-xtrabackup-2.4.11-Linux-x86_64.libgcrypt145.tar.gz

ln -sf percona-xtrabackup-2.4.11-Linux-x86_64 xtrabackup

rpm -ivh /opt/mysql-connector-python-2.1.7-1.el7.x86_64.rpm

echo"PATH=\$PATH:/mysql/app/mysql/bin:/mysql/app/xtrabackup/bin">> /etc/profile    # 不性的话，直接把路径复制进去就好了

source /etc/profile

xtrabackup --version

主库：

/mysql/app/xtrabackup/bin/xtrabackup --defaults-file=/mysql/data/3306/my.cnf --user=root --password=root --backup --stream=tar --parallel=2 --target-dir=/mysql/backup/fullback180520 | gzip > /mysql/backup/fullback180520.tar.gz

#报错的话，加一参数   --socket=/tmp/mysql.sock

scp 192.168.0.50:/mysql/backup/fullback180520.tar.gz /mysql/backup/

scp 192.168.0.50:/mysql/backup/fullback180520.tar.gz /mysql/backup/

scp 192.168.0.50:/mysql/backup/fullback180520.tar.gz /mysql/backup/

从库 3 个：

cd /mysql/backup

mkdir -p /mysql/backup/fullback180520

tar zxvf /mysql/backup/fullback180520.tar.gz -C/mysql/backup/fullback180520

#这条命令需要rsy

/mysql/app/xtrabackup/bin/xtrabackup --defaults-file=/mysql/data/3306/my.cnf --prepare --user-memory=1G --target-dir=/mysql/backup/fullback180520 --parallel=2

 

systemctl stop mysqld

cd /mysql/data/3306/

rm -rf data_bak

mv data data_bak

mkdir data

#看07

rsync -avrP /mysql/backup/fullback180520/* --exclude='xtrabackup_*' /mysql/data/3306/data/

chown -R mysql:mysql data

systemctl start mysqld

mysql -uroot -proot

show databases;

 

select * from itpuxdb.itpux12;

mysql -uroot -proot

stop slave;

reset master;

reset slave;

设置已执行过的 gtid                看fullback180520文件夹里面的xtrabackup_info   替换掉下面的GLOBAL.GTID_PURGED

mysql -uroot -proot

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-4617';

SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

mysql> show master status\G;

*************************** 1. row ***************************

File: itpuxdb-binlog.000001

Position: 154

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: e5de0648-5bcf-11e8-9806-000c292834b0:1-8

1 row in set (0.00 sec)

change master to

master_host='192.168.0.50',

master_port=3306,

master_user='repuser',

master_password='repuser123',

master_auto_position=1;

#master_use_gtid=current_pos

start slave;

show slave status \G

show variables like '%semi_sync%';

SHOW GLOBAL STATUS LIKE 'rpl_semi%';

show status like "%semi%";

select * from itpuxdb.itpux12;

show global variables like '%gtid%';

1.  主从数据测试。

create database itpuxdb2;

create table itpuxdb2.itpuxbak21 (id int,name varchar(40));

insert into itpuxdb2.itpuxbak21 values(1,'itpux211'),(2,'itpux212'),(3,'itpux213'),(4,'itpux214'),(5,'itpux215');

commit;

select * from itpuxdb2.itpuxbak21;

备库检查：

select * from itpuxdb2.itpuxbak21;

主从都检查：

mysql -uroot -proot

show slave status \G

show

 

1. 配置 SSL 安全复制

主服务器

cd /mysql/data/3306/data

mysql_ssl_rsa_setup --datadir=/mysql/data/3306/data --user=mysql --uid=mysql

vi /mysql/data/3306/my.cnf

ssl-ca=/mysql/data/3306/data/ca.pem

ssl-cert=/mysql/data/3306/data/server-cert.pem

ssl-key=/mysql/data/3306/data/server-key.pem

systemctl restart mysqld

tail -100f /mysql/log/3306/itpuxdb-error.err

mysql -uroot -proot

show global variables like '%ssl%';

show global variables like 'tls_version';

create user 'repssluser'@'%' identified by 'repssluser123';

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

flush privileges;

把生成的证书传给从服务器:

cd /mysql/data/3306/data

scp ca.pem client-cert.pem client-key.pem root@192.168.0.51:/mysql/data/3306/data/

scp ca.pem client-cert.pem client-key.pem root@192.168.0.52:/mysql/data/3306/data/

scp ca.pem client-cert.pem client-key.pem root@192.168.0.53:/mysql/data/3306/data/

chown mysql:mysql /mysql/data/3306/data/*.pem

echo "ssl-ca = /mysql/data/3306/data/ca.pem" >> /mysql/data/3306/my.cnf

echo "ssl-cert = /mysql/data/3306/data/client-cert.pem" >> /mysql/data/3306/my.cnf

echo "ssl-key = /mysql/data/3306/data/client-key.pem" >> /mysql/data/3306/my.cnf

systemctl restart mysqld

mysql -uroot -proot -e "show variables like '%ssl%';"

05.连接主从复制

开启主从复制之前，可以ssl连接下主服务器

mysql -urepssluser -prepssluser123 -h 192.168.0.50 --ssl-ca=/mysql/data/3306/data/ca.pem --ssl-cert=/mysql/data/3306/data/client-cert.pem --ssl-key=/mysql/data/3306/data/client-key.pem

exit;

重新配置从库，因为之前配置过

mysql -uroot -proot

stop slave;

change master to

master_host='192.168.0.50',

master_port=3306,

master_user='repssluser',

master_password='repssluser123',

master_auto_position=1,

master_ssl=1,

master_ssl_ca='/mysql/data/3306/data/ca.pem',

master_ssl_cert='/mysql/data/3306/data/client-cert.pem',

master_ssl_key='/mysql/data/3306/data/client-key.pem';

start slave;

show slave status\G

show processlist;

06.测试 SSL 复制及数据验证

主库抓包：tcpdump -i ens33 -nn -XX ip dst host 192.168.0.50 and tcp dst port 3306

主库

mysql -uroot -proot

create database itpuxdb3;

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

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

commit;

select * from itpuxdb3.itpuxbak31;

图解：乱码就是安全复制成功了

备库检查：

mysql -uroot -proot

show slave status \G

show processlist;

select * from itpuxdb3.itpuxbak31;

1. 主库异常宕机的主从故障修复（宕机切换）

主库直接关机

shutdown -h now

12.5.3 检查从库状态

mysql -uroot -proot

show processlist;

show slave status\G

然后都有报错：

Last_IO_Errno: 2003

Last_IO_Error: error reconnecting to master 'repssluser@192.168.1.51:3306' - retry-time: 60 retries: 2 

Last_SQL_Errno: 0

 

12.5.4 切换步骤

在每个从库上执行：

stop slave io_thread;

show processlist;

show slave status\G

02. 选择新的主库

登陆 192.168.0.51，执行，（这时选哪台，要看master执行的日志哪个数大）如下图：

stop slave;

reset master;

reset slave;   #把参数都清理掉

处理 my.cnf 文件，关掉从库的参数

systemctl restart mysqld;

05.配置另外 2 台同步

mysql -uroot -proot

stop slave;

reset slave;

reset master;

change master to

master_host='192.168.0.51',

master_port=3306,

master_user='repuser',

master_password='repuser123',

master_auto_position=1;

start slave;

show slave status\G

show processlist;

06.测试数据：

mysql -uroot -proot

create database itpuxdb4;

create table itpuxdb4.itpuxbak41 (id int,name varchar(40));

insert into itpuxdb4.itpuxbak41 values(1,'itpux411'),(2,'itpux412'),(3,'itpux413'),(4,'itpux414'),(5,'itpux415');

commit;

select * from itpuxdb4.itpuxbak41;

备库检查：

mysql -uroot -proot

show slave status \G

show processlist;

select * from itpuxdb4.itpuxbak41;