MySQL 主从复制

置顶后端开发/ MySQL 后端开发 MySQL

创建时间:2017-09-13 09:24

字数:1.3k

数据库复制 replication 实现原理
架构图
流程图
MySql常用相关指令
主从复制配置步骤
主主复制

MySQL 复制原理及操作

数据库复制 replication 实现原理

mysql 的主从复制功能是基于 binlog 操作日志的，其过程如下：

从数据库执行 start salve ，开启主从复制开关
从数据库的 io 线程会使用主数据上授权的用户请求连接主数据库，并请求指定的 binlog 日志。
主数据接收到来自从数据库的 io 线程的请求后，主数据库上负责复制的 io 线程根据从数据库的请求信息，读取指定的 binlog 文件的指定位置，返回给从数据库的io线程，返回的信息除了本次请求的日志内容外，还是有本次返回的日志内容后在主数据上新的 binlog 文件名称及在 binlog 中的位置（供从数据库下次请求 binlog 使用）。
从数据的 io 线程获取到来自主数据上的 io 线程发送的 binlog 后，将 binlog 中的内容依次写入到从数据库自身的 relaylog（中继日志）文件（Mysql-info-realy-bin.XXXX) 的最末端，并将新的 binlog 文件名和位置记录到 Master-info 文件中，以便下一次读取主数据库的新 binlog 日志时，能够告诉 master 服务器需要从新 binlog 日志的那个文件那个位置，开始返回给从数据库。
从数据库的 sql 线程会实时的检测本地 relay log 中新增的日志内容，然后及时把 log 文件中的内容解析成在主数据库曾经执行的 sql 语句，并在自身从数据库上按顺序执行这些 sql 语句。
至此，正常情况下，主从数据库，就可以实现同步。

架构图

流程图

MySql常用相关指令

show master status;     //查看master的状态，尤其是当前的日志及位置
show slave status;     //查看slave的状态
reset slave;            //重置slave状态
start slave;            //启动slave状态（开启监听master的变化）
stop slave;             //暂停salve状态

主从复制配置步骤

服务器说明

master	172.17.0.2
slave1	172.17.0.3
slave2	172.17.0.4

配置主服务器

创建用户

1 2	MySQL [(none)]> create user 'zj-replication'@'172.17.0.%' identified by '000000'; Query OK, 0 rows affected (0.08 sec)

授权

1 2	MySQL [(none)]> grant replication slave on . to 'zj-replication'@'172.17.0.%' identified by '000000'; Query OK, 0 rows affected, 1 warning (0.00 sec)

开启 binlog 日志

修改 mysql 配置文件:

# 开启二进制日志
log-bin=/var/mysql/mysql-bin
# 给服务器起一个唯一的id
server-id=1

修改完成后,重启MySQL服务器,用客户端登录,查看 binlog状态

MySQL [mysql]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置从服务器

修改配置文件

修改配置msyql配置文件

# server id 不能和其他mysql服务器重复
server-id=2
# 定义relay_log的位置和名称，如果值为空，则默认位置在数据文件的目录，文件名为host_name-relay-bin.nnnnnn
relay-log=slave-relay-bin

连接 master

mysql> change master to 
    -> master_host='172.17.0.2',
    -> master_port=3306,
    -> master_user='zj-replication',
    -> master_password='000000',
    -> master_log_file='master-bin.000001',                      
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

启动

1	start slave;

查看状态

MySQL [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.2
                  Master_User: zj-replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-relay-bin.000001
                Relay_Log_Pos: 0
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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: 154
              Relay_Log_Space: 527
              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: 1
                  Master_UUID: 034b7e45-4c16-11e8-9386-0242ac110002
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

要保证 Slave_IO_Running 和 Slave_SQL_Running 的值都是 Yes 才行.

主主复制

mysql 的主主复制就是两台 mysql 节点互为主从。搭建起来 mysql 主从,再来搭建主主复
制就非常简单了。
在原来主从的基础上做如下操作即可:

1.开启原从节点的 binlog 日志
2.原从节点创建读取副本的用户
3.在原主节点中让 master 指向从节点
4.在原主节点执行 start slave 命令

在MySQL5.7以前,对于主主复制,如果向两个节点中插入数据,一定会导致主键的重复,这里需要一个小的技巧让第一台节点主键采用 1,3,5,7…的方式自增第二台节点采用 2,4,6,8…. 的方式递增

修改配置文件:

# 服务器172.17.0.2：
auto_increment_increment = 2;
auto_increment_offset = 1; 

# 服务器172.17.0.3：
auto_increment_increment = 2;
auto_increment_offset = 2;

这种方法有一个弊端:当需要加入新的服务器时，这种方法难以扩展。

也可以在新增行时,计算好id.比如oracle数据库的主键id，可以使用序列sequence，每次新增生成递增的id。 mysql可以使用redis设置一个自增id,每次新增取出id。

但这一问题,在5.7版本得到了解决,不需要配置主键自增策略,每个库的操作都会让另外一个库递增.