linux , mysql互为主从复制

 

Master-1：192.169.7.242
Master-2：192.169.0.197
MySQL版本：mysql-5.7

---

1.mysql配置文件修改及说明

分别修改两个节点的mysql，此处修改配置文件为：/etc/my.cnf
修改Master-1如下：

 

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 



server-id=1 #server的唯一标识
auto_increment_offset=1 #自增id起始值
auto_increment_increment=2 #每次自增数字

log-bin = mysql-bin #打开二进制功能,MASTER主服务器必须打开此项
max_binlog_size=1024M #binlog单文件最大值
binlog_format=mixed #指定mysql的binlog日志的格式，mixed是混合模式
relay-log=relay-bin #开启中继日志功能
relay-log-index=slave-relay-bin.index #中继日志清单

replicate-ignore-db = mysql #忽略不同步主从的数据库
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test

修改Master-2如下：

 

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 


server-id=2 #server的唯一标识
auto_increment_offset=2 #自增id起始值
auto_increment_increment=2 #每次自增数字

log-bin = mysql-bin #打开二进制功能,MASTER主服务器必须打开此项
max_binlog_size=1024M #binlog单文件最大值
binlog_format=mixed #指定mysql的binlog日志的格式，mixed是混合模式
relay-log=relay-bin #开启中继日志功能
relay-log-index=slave-relay-bin.index #中继日志清单

replicate-ignore-db = mysql #忽略不同步主从的数据库
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test

注意：

• Master-1和Master-2的server-id和auto-increment-offset配置不同,其他相同。
• server-id：是服务器编号，所以，两台服务器上的值要设置的不一样，此处设置为1和2
• auto-increment-offset：用来设定数据库中自动增长的值，为了在两台同时对外提供服务时，防止子增长的字段重复
• 在my.cnf配置文件中，添加“binlog_do_db=数据库名”配置项（可以添加多个）来指定要同步的数据库。如果配置了这个配置项，却没添加在该配置项后面的数据库，则binlog不记录它的事件

2.重启mysql使配置文件生效

分别在两个节点上执行一下命令：

 

service mysql stop
service mysql start

3.添加主从同步账号

Master-1操作如下：

 

grant replication slave on *.* to ’m1slave’@‘172.16.160.37’ identified by ‘password’;

Master-2操作如下：

 

grant replication slave on *.* to ’m2slave’@‘172.16.160.35’ identified by ‘password’;

注意：

• 上面SQL语句中的IP地址是对谁访问本机的设置，而非本机IP
• 若允许任何IP使用此用户都可以访问时，只需将IP地址修改为百分号（%）即可

4.查看Master库的状态

分别在两个节点上执行一下命令：

 

show master status;

查看结果如下:
Master-1:

 

Master-2:

 

5.配置同步数据

Master-1操作如下：

 

change master to master_host='192.169.7.242', master_port=3306, master_user='root', master_password='123456', master_log_file='mysql-bin.000003', master_log_pos=154;

Master-2操作如下：

 

change master to master_host='192.169.0.197', master_port=3306, master_user='root', master_password='123456', master_log_file='mysql-bin.000003', master_log_pos=466;

注意：

• master_log_file和master_log_pos的配置是通过（第4步）中查询到的数据
• master_user和master_password的配置是通过（第3步）中添加角色的账号、密码

6.运行Slave库

分别在两个节点上执行一下命令：

 

start slave;

7.查看Slave库的状态

分别在两个节点上执行一下命令：

 

show slave status\G;

如果查看结果如下图，说明正常

 

---

参考 https://www.jianshu.com/p/58dc118c36ef