MySQL主主数据同步

矫情吗；* 2022-06-04 10:10 306阅读 0赞

# 环境 #

操作系统版本：CentOS7 64位   
MySQL版本：mysql5.6.33   
节点1IP：192.168.1.205 主机名：edu-mysql-01   
节点2IP：192.168.1.206 主机名：edu-mysql-02

MySQL 主从复制官方文档：[http://dev.mysql.com/doc/refman/5.6/en/replication.html][http_dev.mysql.com_doc_refman_5.6_en_replication.html]

注意：   
1> 主从服务器操作系统版本和位数要保持一致   
2> Master和Slave数据库的版本要一致   
3> Master和Slave数据库中的数据要一致

# 配置 #

配置之前先参考[《MySQL5.7安装与配置（YUM）》][MySQL5.7_YUM]安装好MySQL（注意本文演示的是5.6版本，需要修改文章中的yum源为5.6）

## 1、安全配置 ##

1> 防火墙   
添加mysql通信端口（默认为3306）

shell> vim /etc/sysconfig/iptables
    -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
    shell> service  iptables restart

*  1
 *  2
 *  3

或关闭防火墙

shell> service iptables stop

*  1

2> 关闭selinux

shell> vi /etc/selinux/config SELINUX=disabled

*  1
 *  2

将SELINUX的值修改为disabled

## 2. 节点1配置（192.168.1.205） ##

### 2.1 添加数据同步配置 ###

shell> vim /etc/my.cnf

*  1

在\[mysqld\]中增加以下配置项：

# 服务器的ID,必须唯一，一般设置自己的IP
    server_id=205
    # 复制过滤：不需要备份的数据库（MySQL库一般不同步）
    binlog-ignore-db=mysql
    # 开启二进制日志功能，名字可以随便取，最好有含义（比如项目名）
    log-bin=edu-mysql-bin
    # 为每个 session 分配的内存,在事务过程中用来存储二进制日志的缓存
    binlog_cache_size=1M
    # 主从复制的格式(mixed,statement,row,默认格式是 statement)
    binlog_format=mixed
    # 二进制日志自动删除/过期的天数。默认值为 0,表示不自动删除。
    expire_logs_days=7
    ## 跳过主从复制中遇到的所有错误或指定类型的错误,避免 slave 端复制中断。 
    ## 如:1062 错误是指一些主键重复,1032 错误是因为主从数据库数据不一致
    slave_skip_errors=1062
    # 作为从服务器时的中继日志
    relay_log=edu-mysql-relay-bin
    # log_slave_updates 表示 slave 将复制事件写进自己的二进制日志
    log_slave_updates=1
    # 主键自增规则，避免主从同步ID重复的问题
    auto_increment_increment=2  # 自增因子（每次加2）
    auto_increment_offset=1     # 自增偏移（从1开始），单数

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18
 *  19
 *  20
 *  21
 *  22

### 2.2 Master配置 ###

# 先重启一下服务
    shell> service mysqld restart  
    # 登录到mysql
    shell> mysql -uroot -p 
    # 创建数据库同步用户，并授予相应的权限
    mysql> grant replication slave, replication client on *.* to 'repl'@'192.168.1.206' identified by 'root123456';
    # 刷新授权表信息
    mysql> flush privileges;
    # 查看binlog文件的position（偏移）和File（日志文件）的值，从机上需要用到
    mysql> show master status; +----------------------+----------+--------------+------------------+-------------------+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------+----------+--------------+------------------+-------------------+
    | edu-mysql-bin.000001 | 120 | | mysql | | +----------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16

### 2.3 Slave配置 ###

# master_user和master_password：在206上执行grant replication slave...创建的用户和密码
    # master_log_file和master_log_pos：在206上运行show master status;命令执行结果对应File和Position字段的值
    mysql> change master to master_host='192.168.1.206',master_user='repl', master_password='root123456', master_port=3306, master_log_file='edu-mysql-bin.000001', master_log_pos=439, master_connect_retry=30;
    # 查看作为从节点的状态信息
    mysql> show slave status\G;
    *************************** 1. row ***************************
     Slave_IO_State:
     Master_Host: 192.168.1.206
     Master_User: repl
     Master_Port: 3306
     Connect_Retry: 30
     Master_Log_File: edu-mysql-bin.000001
     Read_Master_Log_Pos: 439
     Relay_Log_File: edu-mysql-relay-bin.000001
     Relay_Log_Pos: 4
     Relay_Master_Log_File: edu-mysql-bin.000001
     Slave_IO_Running: No
     Slave_SQL_Running: No
     # 省略其它配置。。。

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18
 *  19

由于此时从节点还没有启动，Slave\_IO\_State的值为空，Slave\_IO\_Running和Slave\_SQL\_Running线程为No表示也没有运行。

### 2.4 启动Slave ###

注意：要在节点2上创建同步帐户后再启动，否则会报连不上master错误

# 启动从节点，开始工作接收主节点发送事件（数据库数据变更的所有事件）
    mysql> start slave;
    # 此时再查看slave节点的状态
    mysql> show slave status\G;
    *************************** 1. row ***************************
     Slave_IO_State: Waiting for master to send event
     Master_Host: 192.168.1.206
     Master_User: repl
     Master_Port: 3306
     Connect_Retry: 30
     Master_Log_File: edu-mysql-bin.000001
     Read_Master_Log_Pos: 439
     Relay_Log_File: edu-mysql-relay-bin.000002
     Relay_Log_Pos: 287
     Relay_Master_Log_File: edu-mysql-bin.000001
     Slave_IO_Running: Yes
     Slave_SQL_Running: Yes
     Replicate_Do_DB:
     # ...省略其它配置

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18
 *  19

## 3. 节点2配置（192.168.1.206） ##

### 3.1 添加数据同步配置 ###

shell> vim /etc/my.cnf

*  1

在\[mysqld\]中增加以下配置项：

server_id=206
    binlog-ignore-db=mysql
    log-bin=edu-mysql-bin
    binlog_cache_size=1M
    binlog_format=mixed
    expire_logs_days=7
    slave_skip_errors=1062
    relay_log=edu-mysql-relay-bin
    log_slave_updates=1
    #ID自增从2开始，双数
    auto_increment_increment=2
    auto_increment_offset=2

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12

### 3.2 Master配置 ###

# 先重启一下服务
    shell> service mysqld restart  
    # 登录到mysql
    shell> mysql -uroot -p 
    # 创建数据库同步用户，并授予相应的权限（只允许repl用户从192.168.1.205上登录）
    mysql> grant replication slave, replication client on *.* to 'repl'@'192.168.1.205' identified by 'root123456';
    # 刷新授权表信息
    mysql> flush privileges;
    # 查看binlog文件的position（偏移）和File（日志文件）的值，从机上需要用到
    mysql> show master status; +----------------------+----------+--------------+------------------+-------------------+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------+----------+--------------+------------------+-------------------+
    | edu-mysql-bin.000001 | 439 | | mysql | | +----------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16

这时可以启动节点1(205)的slave服务

### 3.3 Slave配置 ###

# master_log_file和master_log_pos：205节点上执行show master status;对应File和position的值
    mysql> change master to master_host='192.168.1.205',master_user='repl', master_password='root123456', master_port=3306, master_log_file='edu-mysql-bin.000001', master_log_pos=120, master_connect_retry=30;
    Query OK, 0 rows affected, 2 warnings (0.02 sec)
    mysql> show slave status\G;
    *************************** 1. row ***************************
     Slave_IO_State:
     Master_Host: 192.168.1.205
     Master_User: repl
     Master_Port: 3306
     Connect_Retry: 30
     Master_Log_File: edu-mysql-bin.000001
     Read_Master_Log_Pos: 120
     Relay_Log_File: edu-mysql-relay-bin.000001
     Relay_Log_Pos: 4
     Relay_Master_Log_File: edu-mysql-bin.000001
     Slave_IO_Running: No
     Slave_SQL_Running: No
     Replicate_Do_DB:
     #...省略其它配置

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18
 *  19

### 3.4、启动Slave ###

shell> start slave;
    Query OK, 0 rows affected (0.01 sec)
    mysql> show slave status\G;
    *************************** 1. row ***************************
     Slave_IO_State: Waiting for master to send event
     Master_Host: 192.168.1.205
     Master_User: repl
     Master_Port: 3306
     Connect_Retry: 30
     Master_Log_File: edu-mysql-bin.000001
     Read_Master_Log_Pos: 439
     Relay_Log_File: edu-mysql-relay-bin.000002
     Relay_Log_Pos: 287
     Relay_Master_Log_File: edu-mysql-bin.000001
     Slave_IO_Running: Yes
     Slave_SQL_Running: Yes
     Replicate_Do_DB:
     ...省略其它配置

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18

## 4、验证 ##

# 登录205创建一个数据库
    shell> mysql -u root -p
    mysql> create database if not exists mydb default character set utf8 collate utf8_general_ci;
    mysql> create table user (id int, username varchar(30), password varchar(30));
    mysql> insert into user values (1, 'yangxin', '123456');
    # 下面是在206节点上的操作
    #1、登录206查询所有库，是否包含mydb数据库
    #2、切换到mydb库，是否包含user表，并有一条数据
    #3、在206的mydb.user表插入一条数据，查看205是否同步过去
    mysql> insert into user values (2,'yangxin2','123456')

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10

详细过程如下图所示：

![数据同步操作过程][20160917175250676]

[http_dev.mysql.com_doc_refman_5.6_en_replication.html]: http://dev.mysql.com/doc/refman/5.6/en/replication.html
[MySQL5.7_YUM]: http://blog.csdn.net/xyang81/article/details/51759200
[20160917175250676]: /images/20220604/fb8ff002963b4b46bf7dcd8987d1b5ff.png