mysql mysql5.7两个服务_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

配置主从复制 (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;