版本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;