MySQL 5.7.15 两台服务器双实例相互复制

最新推荐文章于 2021-05-04 15:38:51 发布

原创最新推荐文章于 2021-05-04 15:38:51 发布 · 1.6k 阅读

0 ·

CC 4.0 BY-SA版权

MYSQL 同时被 2 个专栏收录

61 篇文章

订阅专栏

MYSQL 高可用性

16 篇文章

订阅专栏

本文介绍如何在两台服务器上各安装两个MySQL实例，并配置这些实例进行互为主从复制。通过详细步骤展示了如何创建复制账户、进行数据备份与恢复、设置复制参数等关键操作。

两台服务器，每台服务器安装了两个实例，现要配置相互复制，复制某个数据库。此测试的的数据库名称和表结构都一样。

单服务器安装多mysql实例，参考：MySQL 5.7.15 多实例安装（二进制安装模式）

测试架构如下：

192.168.1.110 的配置文件 /etc/my.cnf 内容如下：（两个实例配置都在一个文件中）

[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user       = root
password   = rootpwd

####### master 3306 （slave 3308） #######
[mysqld3306]
port = 3306
server_id = 3306
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data3306
log-bin=/usr/local/mysql/binlog3306/mysql-bin
socket =/tmp/mysql3306.sock  
log-error =/var/log/mysqld3306.log
pid-file =/var/run/mysqld/mysqld3306.pid
autocommit = 1
character_set_server=utf8
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
event_scheduler = 1
lower_case_table_names=1
max_allowed_packet = 16777216
explicit_defaults_for_timestamp = 1
binlog_format = row 
skip-external-locking

#connection#
max_connections = 512
max_connect_errors = 100
wait_timeout = 1800
skip-name-resolve = 1

#replication#
#gtid-mode=1 #GTID only 
#enforce_gtid_consistency = 1 #GTID only 
#binlog_rows_query_log_events = 1
sync_binlog = 1


####### slave 3307 （master 3309） #######
[mysqld3307]
port = 3307
server_id = 3307
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data3307
log-bin=/usr/local/mysql/binlog3307/mysql-bin
socket =/tmp/mysql3307.sock  
log-error =/var/log/mysqld3307.log
pid-file =/var/run/mysqld/mysqld3307.pid
autocommit = 1
character_set_server=utf8
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
event_scheduler = 1
lower_case_table_names=1
max_allowed_packet = 16777216
log_slave_updates = 1
explicit_defaults_for_timestamp = 1
binlog_format = row 
skip-external-locking

#connection#
max_connections = 512
max_connect_errors = 100
wait_timeout = 1800
skip-name-resolve = 1

#replication#
#log-slave-updates
#read_only=1  
relay_log=mysql-relay-bin
relay_log_recovery = 1
relay_log_info_repository = TABLE
master_info_repository = TABLE
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info =1
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 2
slave_preserve_commit_order=1
slave_transaction_retries=128
slave_skip_errors = ddl_exist_errors
replicate_wild_do_table=zaesd.%  
replicate_wild_ignore_table=mysql.%

192.168.1.111 的配置文件 /etc/my.cnf ，参考上面，把 3306 替换为 3308 ，3307 替换为 3309 。

重启实例 ( 以服务器 10.18.192.110为例 )

shell> mysqladmin -uroot -p -S /tmp/mysql3306.sock shutdown
shell> mysqladmin -uroot -p -S /tmp/mysql3307.sock shutdown

shell> mysqld_multi report
shell> mysqld_multi start 3306,3307

添加复制账户（每个实例）

create user repluser@'192.168.1.%' IDENTIFIED by 'replpassword';
grant replication slave on *.* to repluser@'192.168.1.%';
flush privileges;

配置主从复制 (192.168.1.110:3306 --> 192.168.1.111:3309)

# 登录服务器 192.168.1.110 (3306 为master)
shell> mysqldump -h192.168.1.110 -uroot -p -P3306 --single-transaction --events --triggers --routines --flush-logs --master-data=2 --databases testdb > /tmp/testdb3006.sql  

# 备份远程传输到备用服务器 111
shell> scp -r /tmp/testdb3006.sql root@192.168.1.111:/tmp   

# 登录服务器 192.168.1.111 , slave(3309) 备库还原  
shell> mysql -h192.168.1.111 -uroot -p -P3309 testdb < /tmp/testdb3006.sql  

# 查看备份时的日志位置  
shell> grep -i "change master" /tmp/testdb3006.sql 
	-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=154;
	
# 连接数据库设置slave
shell> mysql -h192.168.1.111 -uroot -p -P3309
mysql>
CHANGE MASTER TO     
    MASTER_HOST='192.168.1.110', 
    MASTER_PORT =3306,    
    MASTER_USER='repluser',     
    MASTER_PASSWORD='replpassword',     
    MASTER_LOG_FILE='mysql-bin.000007',     
    MASTER_LOG_POS=154;    
mysql> START SLAVE;

# 查看状态
mysql> show master status \G;  
mysql> show slave status \G;

配置主从复制 (192.168.1.111:3308 --> 192.168.1.110:3307)

# 登录服务器 192.168.1.111 (3308 为master)
shell> mysqldump -h192.168.1.111 -uroot -p -P3308 --single-transaction --events --triggers --routines --flush-logs --master-data=2 --databases testdb > /tmp/testdb3008.sql  

# 备份远程传输到备用服务器 111
shell> scp -r /tmp/testdb3008.sql root@192.168.1.110:/tmp   

# 登录服务器 192.168.1.110,slave(3307) 备库还原  
shell> mysql -h192.168.1.110 -uroot -p -P3307 testdb < /tmp/testdb3008.sql  

# 查看备份时的日志位置  
shell> grep -i "change master" /tmp/testdb3008.sql 
	-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;
	
# 连接数据库设置slave
shell> mysql -h192.168.1.110 -uroot -p -P3307
mysql>
CHANGE MASTER TO     
    MASTER_HOST='192.168.1.111', 
    MASTER_PORT =3308,    
    MASTER_USER='repluser',     
    MASTER_PASSWORD='replpassword',     
    MASTER_LOG_FILE='mysql-bin.000003',     
    MASTER_LOG_POS=154;    
mysql> START SLAVE;

# 查看状态
mysql> show master status \G;  
mysql> show slave status \G;

测试

# 192.168.1.110:3306 插入数据 , 192.168.1.111:3309 查看数据
# 192.168.1.111:3308 插入数据 , 192.168.1.110:3307 查看数据

shell> mysql -h192.168.1.110 -uroot -p -P3306

mysql> use testdb;
mysql> 
create table tab (  
    id char(36) not null,  
    name varchar(50) not null default '',  
    addtime datetime not null default '1900-01-01 00:00:00',  
    primary key (id)  
)engine=innodb default charset=utf8;  
mysql> 
mysql> use testdb;
mysql> insert into tab values(1,'kk',now());
mysql> 
mysql> use mysql;
mysql> delete from testdb.tab;
mysql> 
mysql> select * from testdb.tab;