MySQL 主从同步

为什么需要主从复制

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

主从同步的原理

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

主从复制

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

上面的 3 个过程是 MySQL 主从同步的大概流程,其中 binlogrelay 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_IOSlave_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;

数据就可以完成同步了。