MySQL主主复制（双主复制）配置过程介绍
发表于：2026-11-01 作者：千家信息网编辑
千家信息网最后更新 2026年11月01日，一、修改配置文件my.cnf服务器A（172.16.16.70）配置如下server_id = 70socket = /tmp/mysql.sockinnodb_buffer_pool_size =
千家信息网最后更新 2026年11月01日MySQL主主复制（双主复制）配置过程介绍一、修改配置文件my.cnf
服务器A（172.16.16.70）配置如下
server_id = 70
socket = /tmp/mysql.sock
innodb_buffer_pool_size = 10G
character-set-server=utf8
log_bin=mysql-bin
expire_logs_days=3
replicate-do-db=ixinnuo_sjcj
binlog-ignore-db=mysql,information_schema
auto-increment-increment = 2
auto-increment-offset = 1
服务器B（172.16.16.71）配置如下:
server_id = 71
socket = /tmp/mysql.sock
innodb_buffer_pool_size = 10G
character-set-server=utf8
log_bin=mysql-bin
expire_logs_days=3
replicate-do-db=ixinnuo_sjcj
replicate-ignore-db = mysql,information_schema
auto-increment-increment = 2
auto-increment-offset = 2
两台服务器都重启，使配置生效:
# service mysqld restart
说明:auto-increment-offset是用来设定数据库中自动增长的起点的，回为这两台服务器都设定了一次自动增长值2，所以它们的起点必须要不同，这样才能避免两台服务器数据同步时出现主键冲突.
replicate-do-db 指定同步的数据库，本例为ixinnuo_sjcj库，另外，建议两台服务器的硬件配置也都一样。
二、同步数据,建立复制账号:
在服务器A（172.16.16.70）上:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'172.16.16.71' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
在服务器B（172.16.16.71）上:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'172.16.16.70' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
三、执行change master命令同步:
在服务器A（172.16.16.70）上:
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000047 | 411 | | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
在服务器B（172.16.16.71）上:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 618 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在服务器A上执行:
mysql> change master to master_host='172.16.16.71',master_user='slave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=618;
Query OK, 0 rows affected, 2 warnings (0.29 sec)
在服务器B上执行:
mysql> change master to master_host='172.16.16.70',master_user='slave',master_password='123456',master_log_file='mysql-bin.000047',master_log_pos=411;
Query OK, 0 rows affected, 2 warnings (0.34 sec)
在两服务器都执行以下命令:
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
四.查看状态:
A服务器（172.16.16.70）状态如下：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.16.71
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 618
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: ixinnuo_sjcj
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 618
Relay_Log_Space: 460
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 71
Master_UUID: 32197cd9-2957-11e7-a5c4-525400e493a4
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
B服务器（172.16.16.71）状态如下：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.16.70
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000047
Read_Master_Log_Pos: 411
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000047
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: ixinnuo_sjcj
Replicate_Ignore_DB: mysql,information_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 411
Relay_Log_Space: 460
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 70
Master_UUID: f1366940-266c-11e7-92c2-525400f39f79
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
说明已经配置成功了
测试:
在服务器A(172.16.16.70)上ixinnuo_sjcj库里创建表chenfeng:
mysql> use ixinnuo_sjcj
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table chenfeng (name varchar(10));
Query OK, 0 rows affected (0.23 sec)
mysql> insert into chenfeng values('duansf');
Query OK, 1 row affected (0.13 sec)
mysql> insert into chenfeng values('liuyb');
Query OK, 1 row affected (0.02 sec)
在服务器B上查看服务器A上创建的表chenfeng
mysql> select * from chenfeng;
+--------+
| name |
+--------+
| duansf |
| liuyb |
+--------+
2 rows in set (0.00 sec)
mysql> desc chenfeng;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
在服务器B(172.16.16.71)上ixinnuo_sjcj库里创建表duansf:
mysql> create table duansf (name varchar(20));
Query OK, 0 rows affected (0.18 sec)
mysql> insert into duansf values('duansf');
Query OK, 1 row affected (0.02 sec)
mysql> insert into duansf values('liuky');
Query OK, 1 row affected (0.03 sec)
在服务器上A上查看服务器B上创建的表duansf
mysql> select * from duansf;
+--------+
| name |
+--------+
| duansf |
| liuky |
+--------+
2 rows in set (0.00 sec)
mysql> desc duansf;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
测试成功，说明MySQL主主复制配置成功.
服务器A（172.16.16.70）配置如下
server_id = 70
socket = /tmp/mysql.sock
innodb_buffer_pool_size = 10G
character-set-server=utf8
log_bin=mysql-bin
expire_logs_days=3
replicate-do-db=ixinnuo_sjcj
binlog-ignore-db=mysql,information_schema
auto-increment-increment = 2
auto-increment-offset = 1
服务器B（172.16.16.71）配置如下:
server_id = 71
socket = /tmp/mysql.sock
innodb_buffer_pool_size = 10G
character-set-server=utf8
log_bin=mysql-bin
expire_logs_days=3
replicate-do-db=ixinnuo_sjcj
replicate-ignore-db = mysql,information_schema
auto-increment-increment = 2
auto-increment-offset = 2
两台服务器都重启，使配置生效:
# service mysqld restart
说明:auto-increment-offset是用来设定数据库中自动增长的起点的，回为这两台服务器都设定了一次自动增长值2，所以它们的起点必须要不同，这样才能避免两台服务器数据同步时出现主键冲突.
replicate-do-db 指定同步的数据库，本例为ixinnuo_sjcj库，另外，建议两台服务器的硬件配置也都一样。
二、同步数据,建立复制账号:
在服务器A（172.16.16.70）上:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'172.16.16.71' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
在服务器B（172.16.16.71）上:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'172.16.16.70' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
三、执行change master命令同步:
在服务器A（172.16.16.70）上:
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000047 | 411 | | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
在服务器B（172.16.16.71）上:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 618 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在服务器A上执行:
mysql> change master to master_host='172.16.16.71',master_user='slave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=618;
Query OK, 0 rows affected, 2 warnings (0.29 sec)
在服务器B上执行:
mysql> change master to master_host='172.16.16.70',master_user='slave',master_password='123456',master_log_file='mysql-bin.000047',master_log_pos=411;
Query OK, 0 rows affected, 2 warnings (0.34 sec)
在两服务器都执行以下命令:
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
四.查看状态:
A服务器（172.16.16.70）状态如下：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.16.71
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 618
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: ixinnuo_sjcj
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 618
Relay_Log_Space: 460
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 71
Master_UUID: 32197cd9-2957-11e7-a5c4-525400e493a4
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
B服务器（172.16.16.71）状态如下：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.16.70
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000047
Read_Master_Log_Pos: 411
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000047
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: ixinnuo_sjcj
Replicate_Ignore_DB: mysql,information_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 411
Relay_Log_Space: 460
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 70
Master_UUID: f1366940-266c-11e7-92c2-525400f39f79
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
说明已经配置成功了
测试:
在服务器A(172.16.16.70)上ixinnuo_sjcj库里创建表chenfeng:
mysql> use ixinnuo_sjcj
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table chenfeng (name varchar(10));
Query OK, 0 rows affected (0.23 sec)
mysql> insert into chenfeng values('duansf');
Query OK, 1 row affected (0.13 sec)
mysql> insert into chenfeng values('liuyb');
Query OK, 1 row affected (0.02 sec)
在服务器B上查看服务器A上创建的表chenfeng
mysql> select * from chenfeng;
+--------+
| name |
+--------+
| duansf |
| liuyb |
+--------+
2 rows in set (0.00 sec)
mysql> desc chenfeng;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
在服务器B(172.16.16.71)上ixinnuo_sjcj库里创建表duansf:
mysql> create table duansf (name varchar(20));
Query OK, 0 rows affected (0.18 sec)
mysql> insert into duansf values('duansf');
Query OK, 1 row affected (0.02 sec)
mysql> insert into duansf values('liuky');
Query OK, 1 row affected (0.03 sec)
在服务器上A上查看服务器B上创建的表duansf
mysql> select * from duansf;
+--------+
| name |
+--------+
| duansf |
| liuky |
+--------+
2 rows in set (0.00 sec)
mysql> desc duansf;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
测试成功，说明MySQL主主复制配置成功.
服务器
服务
配置
数据
同步
成功
状态
命令
数据库
起点
库里
增长
测试
不同
增长值
建议
文件
硬件
账号
时出
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
伟宏互联网科技
苏州找大状互联网科技有限公司
科技赋能下的互联网金融
深圳恒合互联网络科技有限公司
科技与互联网
金融科技还是互联网
互联网 卓越科技
小马过河互联网科技
美国互联网黑科技
互联网金融 科技创新
互联网科技博主是什么意思
互联网科技与管理
崇明区互联网教育科技诚信经营
互联网行业科技股
四川互联网展示科技馆设计
厦门江牛科技互联网
东营紫格互联网科技
互联网界的黑科技
广州三三互联网科技有限公司
互联网科技创新创业项目
互联网科技公司排名
河南云网互联网络科技有限公司
科技与互联网
厦门中拓互联网络科技有限公司
互联网金融科技平台
丰台科技园 互联网公司
互联网科技革命什么开始
北京天晋互联网科技有限公司
平安好医生打造互联网科技医疗
广东猫王互联网科技有限公司电话
