基于GTID的异步复制

版本5.7.6+  才行

 

下载   mysql-utilities-1.6.1.tar.gz   mysql的套件，在企业版板块才有，用Python写的，用得很多

 

进入解压文件

python setup.py build

python setup.py install

 

 

主备库都操作

mysql -uroot -proot create user 'repuser'@'%' identified by 'repuser123'; 

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

flush privileges; 

select user,host from mysql.user; 

exit;

 

 

## 192.168.1.50(master)

vi /mysql/data/3306/my.cnf

#master add parameter

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

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

binlog_format=row

binlog_rows_query_log_events=on

#master modify parameter

bind-address=0.0.0.0

server_id=503306

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

 

 

## 192.168.1.51(slave)

vi /mysql/data/3306/my.cnf

#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

#slave modify parameter

bind-address=0.0.0.0

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

 

gtid_mode = on

enforce_gtid_consistency = 1

log-slave-updates = 1

binlog_gtid_simple_recovery=1        #控制算法，不配也行

 

#slave parameter

relay_log = /mysql/log/3306/relaylog/mysqldb-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

systemctl restart mysqld

mysql -uroot -proot

show master status;

 

exit;

 

主库操作

mysqlreplicate --master=root:root@192.168.1.50:3306 --slave=root:root@192.168.1.51:3306 --rpl-user=repuser:repuser123 -b

#主库操作用test测试

mysqlreplicate --master=root:root@192.168.1.50:3306 --slave=root:root@192.168.1.51:3306 --rpl-user=repuser:repuser123 --test-db=testdb

 #主库操作测试

mysqlrplcheck --master=root:root@192.168.1.50:3306 --slave=root:root@192.168.1.51:3306 -s

 #一致性检查

mysqlrplsync --master=root:root@192.168.1.50:3306 --slave=root:root@192.168.1.51:3306

 

 

 

mysql -uroot -proot

create database itpuxdb1;

use itpuxdb1;

create table itpuxbak11 (id int,name varchar(40));

insert into itpuxbak11 values

(1,'itpux111'),(2,'itpux112'),(3,'itpux113'),(4,'itpux114'),(5,'itpux115');

commit;

select * from itpuxbak11;

create user 'itpux01'@'%' identified by 'itpux01';

grant all privileges on itpuxdb1.* to 'itpux01'@'%';

flush privileges;

exit;

#从库检查

mysql -uitpux01 -pitpux01

select * from itpuxdb1.itpuxbak11;

exit;