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 会自动读取。

   sudo vim /etc/mysql/my.cnf

   打开后，加入如下内容：

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

3. 重启 MySQL 服务

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

   sudo mysql.service start  

1. 授权从服务器登录 master

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

   # 新版本的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 状态

   

2. 从服务器搭建

   • 添加 /etc/mysql/my.cnf

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

   • 重启 MySQL 服务

   • 执行 sql

     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;//查看从服务器的状态

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

解决 Slave_SQL 线程问题

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这个属性

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

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

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;

数据就可以完成同步了。