linux , mysql互为主从复制

曾经终败给现在 2023-07-01 06:28 29阅读 0赞

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:

![20200119163809113.png][]

Master-2:

![20200119163849248.png][]

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;

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9xaWFuZ3lvbmdodQ_size_16_color_FFFFFF_t_70][]

--------------------

参考 [https://www.jianshu.com/p/58dc118c36ef][https_www.jianshu.com_p_58dc118c36ef]

[20200119163809113.png]: https://img-blog.csdnimg.cn/20200119163809113.png
[20200119163849248.png]: https://img-blog.csdnimg.cn/20200119163849248.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9xaWFuZ3lvbmdodQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200119164034452.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9xaWFuZ3lvbmdodQ==,size_16,color_FFFFFF,t_70
[https_www.jianshu.com_p_58dc118c36ef]: https://www.jianshu.com/p/58dc118c36ef