MySQL 主从同步

发表于

为什么需要主从复制

  1. 分别读写数据库的时候，把读和写分开，能够有效的提高数据库的负载
  2. 保证数据的高可用，一旦有一台数据库服务器宕机，不会对数据产生太大的影响
  3. 可以横向扩展，实现数据库的水平扩容

主从同步的原理

主从复制的根本原理是从 master 服务器上面的数据，通过一定的方式同步到 slave 服务器上面。基本过程如下图：

主从复制

  1. 主服务器在修改的数据的时候，会产生一个 bin log
  2. 从服务器上面启动一个 I/O thread，通过配置好的用户名和密码, 连接到主服务器上面请求读取二进制日志，然后把读取到的二进制日志写到本地的一个Realy log（中继日志）里面。
  3. 从服务器上面同时开启一个 SQL thread 定时检查 Realy log(这个文件也是二进制的)，如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。

上面的 3 个过程是 MySQL 主从同步的大概流程，其中 binlog 和 relay log 的读写都是顺序 IO，性能很高。

Relay log转换成数据的过程是一个比较耗时的过程，一般出现了数据延迟的时候，基本都是这里的问题。

搭建主从同步

此处用 Linux 服务器进行演示：

  1. 安装 MySQL

  2. 修改 my.cnf

    如果找不到 my.cnf文件可以直接新建一个文件，MySQL 会自动读取。

    1
    sudo vim /etc/mysql/my.cnf

    打开后，加入如下内容：

    1
    2
    3
    4
    5
    [mysqld]# 这行一定要有
    server-id = 1  #主机可以设置为1，从机可设置为IP最后一段（保证唯一性）
    log-bin=master-a-bin  #主从服务的核心
    binlog-format=ROW  # 二进制格式，row,startement 和 mixed 三种类型
    binlog-do-db=testsync  #同步的数据库的名称

  3. 重启 MySQL 服务

    不同安装方式的重试方式不同，此处仅仅是一种可行的方式。

    1
    sudo mysql.service start

  1. 授权从服务器登录 master

    登录到 slave 服务器上面，执行下面的 sql : 

    1
    2
    3
    4
    5
    6
    7
    8
    # 新版本的SQL
    mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';          //创建账户
    mysql> GRANT ALL PRIVILEGES ON *.* TO 'slave'@'%' WITH GRANT OPTION;        //赋予权限

    mysql> flush privileges;      //刷新权限
    mysql>show master status; //查看 master 状态


    image-20200823094918305

  2. 从服务器搭建

    • 添加 /etc/mysql/my.cnf

      1
      2
      3
      4
      [mysqld]
      log-bin=mysql-bin
      server-id=2
      replicate-do-db=testsync

    • 重启 MySQL 服务

    • 执行 sql 

      1
      2
      3
      mysql> change master to master_host='192.168.101.128', master_user='slave', master_password='Abc12345++++',master_log_file='master-a-bin.000001',master_log_pos=1061;
      mysql>start slave;
      mysql>show slave status\G;//查看从服务器的状态

image-20200823180615029

从上面发现 Slave_IO 和 Slave_SQL两个线程的状态都是 No. 说明我们的同步还没有成功。

解决 Slave_SQL 线程问题

1
2
3
4
mysql> stop slave;                                                      
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;        
mysql> start slave;                                                      
mysql> show slave status\G

解决 Slave_IO 线程

在主服务器 执行 show slave status\G 看到 Read_Master_Log_Pos: 1061这个属性

image-20200823181846764

在从服务器上面执行 show slave status\G ;显示结果：

image-20200823182134135

上面说明了 master 和 slave 同步的位置不同，重新在重置下 slave 的位置就可以了。

1
2
3
mysql> stop slave;
mysql> change master to master_host='192.168.101.128', master_user='slave', master_password='123456',master_log_file='master-a-bin.000001',master_log_pos=1061;
mysql> start slave;

数据就可以完成同步了。