MySQL主从复制

MySQL主从复制原理及实现

• 运行环境为
  • CentOS 7
  • MySQL community 5.7.21
  • Master Server：192.168.81.129
  • Slave Server：192.168.81.128

一、前言

1.主从复制出现的原因

  传统数据库如MySQL，存在的问题就是单机部署、实例在系统上为单进程，这样就存在一些问题：

1. 资源利用不灵活。有可能磁盘io性能已经顶不住读写压力，但是cpu性能还有剩余；有可能磁盘性能还有剩余，但是cpu性能却顶不住了；也有可能cpu和磁盘都性能剩余，但是写入的数据量太大，直接撑爆磁盘上限。
2. 资源有最大上限。cpu目前最多64核，磁盘最高几百T的容量。但是用户的数据和查询是没有上限的，机器性能再高也可能支撑不住庞大的数据。
3. 连接池资源上限。业务量大，单机的连接数不足以支持。
4. 容灾问题。虽然事务可以保证重启后数据不丢，但是线上业务等不起重启。

 2. 相对于问题所产生的解决方法

1. 主从复制（replication）：解决的是容灾问题，容灾需要保证数据库切换的实时性和数据的一致性，一致性的强弱还催生了集中不同的复制模式（asynchronous异步复制, semisynchronous半同步复制, group replication组复制）
2. 读写分离（read write spliting）：是一种业务类应用解决单机无法承受读流量的方式，学名叫scale out，读写分离的业务是架设在主从复制的基础上
3. 负载均衡（load balance）：这是一个非数据库的概念，但在数据库层面，如果有一个通用的中间层，那么也适用

三者关系如图：

在图中的load balance做在了业务层，读写的路由逻辑也由业务层控制。

二、主从复制原理

工作原理步骤：

1. 主服务器（master）把数据更新记录到二进制日志（binlog）中。在每个事务更新数据完成之前，master在二进制日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。
2. 从服务器（slave）把主服务器的二进制日志复制到自己的中继日志（relay log）中。首先，slave开始一个I/O线程，在master上打开一个普通的连接，然后开始binlog dump process从master的二进制日志中读取事件，如果已经跟上了master，它会睡眠并等待master产生新的事件。I/O线程将master日志中的这些时间写入slave自己的中继日志。
3. 从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性。slave的SQL线程从中继日志读取事件，并重做其中的事件来更新slave自己的数据，使其与master中的数据一致。中继日志通常位于OS的缓存中，所以其开销很少。

工作原理其实就是，完全备份+二进制日志备份的还原。不同的是这个二进制日志的还原操作基本上实时在进行中。这里需要特别注意的是，复制不是完全实时地进行同步，而是异步实时。这中间存在主从服务器之间的执行延时，如果主服务器的压力很大，则可能导致主从服务器延时较大。

• 主要基于MySQL的二进制日志

MySQL的日志分类为：

 二进制日志（Binary Log）、中继日志（Relay Log）、错误日志、查询日志、慢查询日志、事务日志

• 三个线程

从服务器中有2个线程，一个是I/O线程，负责读取主服务器的二进制日志，并将其保存为中继日志；另一个是SQL线程，复制执行中继日志。

主服务器中有1个线程，是slave在master中打开一个连接后使得master开始的一个线程。所以当master压力很大时，大部分时间都用来执行sql而没有进行日志的传输，主从服务器延时会加大。

•       细节

1. 要实现MySQL的复制，首先必须打开master端的Binary Log（mysqlbin.xxx）功能，启用二进制日志。
2. 每个slave从master接收已经记录到二进制日志的保存的更新。当一个slave的IO线程连接上master时，通知master定位到slave在日志中读取的最后一次成功更新的位置。
3. master接收到请求后，通过负责复制的IO线程根据请求信息读取指定日志位置之后的日志信息，返回给slave的IO线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在master端的二进制日志文件的名称以及最后一行读取位置。
4. slave的IO线程接收到信息后，将接收到的日志内容依次写入到中继日志文件的最末端，并将读取到的master端二进制日志的文件名和最后一行的位置记录到master-info文件中，以便在下一次读取的时候能够清楚的把请求告诉master
5. slave的SQL线程检测到中继日志中增加了新内容后，会马上解析该文件中的内容成为在master端真实执行的query语句，并在自身执行这些query。
6. 循环复制的问题（待补充）

三、把大象装冰箱，一共分几步

1. 创建备份（复制）账户

            在备库运行的IO线程会建立一个到主库的TCP/IP连接，这意味着必须在主库创建一个用户，并赋予其合适的权限。备库IO线程以该用户名连接到主库并读取其二进制日志。创建账号sql如下：

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT, RELOAD, SUPER ON *.* TO [e-mail]'192.168.81.%' IDENTIFIED BY 'backup';

            我们在主库和备库都创建该帐号。另外我们把这个账户限制在本地网络，因为这是一个特权账号（根据给定的权限执行特定的功能）

REPLICATION SLAVE权限：允许Slave主机通过此用户连接Master以便建立主从复制关系（主从复制的最基本权限）

REPLICATION CLIENT权限：允许执行show master status, show slave status, show binary logs命令，用来监控和管理复制的账号。（针对这两种目的，使用同一个帐号同时进行复制和监控、管理更加容易；如果在主库上建立了账号，然后从主库将数据克隆到备库上时，备库也就设置好了——变成主库所需要的配置，后续也方便交换主备库的角色）

RELOAD权限：允许执行flush命令，指明重新加载权限表到系统内存中；refresh命令代表关闭和重新开启日志文件并刷新所有的表；reload命令

SUPER权限：允许执行一系列数据库管理命令，包括kill强制关闭某个连接命令，change master to创建复制关系命令，以及create/alter/drop server等命令

    2. 配置主库和备库

    在my.cnf中的[mysqld]下添加如下内容：

server-id=1               #数据库唯一ID，主从的标识号绝对不能重复。
log-bin=mysql-bin         #开启bin-log，并指定文件目录和文件名前缀
binlog-do-db=replication  #需要同步的数据库。如果是多个同步库，就以此格式另写几行即可。如果不指明对某个具体库同步，就去掉此行，表示同步所有库（除了ignore忽略的库）。
binlog-ignore-db=mysql    #不同步mysql系统数据库。如果是多个不同步库，就以此格式另写几行；也可以在一行，中间逗号隔开。
sync_binlog = 1           #确保binlog日志写入后与硬盘同步
binlog_checksum = none    #跳过现有的采用checksum的事件，mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none
binlog_format = mixed     #bin-log日志文件格式，设置为MIXED可以防止主键重复。

    唯一的数据库服务器ID必须明确指定，默认通常为1（和版本相关，一些版本不允许使用这个值）。使用默认值可能会导致和其他服务器的ID冲突，一种通用的做法是使用服务器IP地址的末8位，但要保证它是不变且唯一的

    重新启动数据库后，检查Master的二进制日志文件是否已经创建成功（MySQL会为文件名增加一些数字，因为我之前失败过两次，所以这里是3）。使用命令如下：

mysql> show master status;

    备库上也需要在my.cnf中增加类似配置，并且同样需要重启服务器

[mysqld]
server-id=2                      #设置从服务器id，必须于主服务器不同
log-bin=mysql-bin                #启动MySQ二进制日志系统
replicate-do-db=replication      #需要同步的数据库名。如果不指明同步哪些库，就去掉这行，表示所有库的同步（除了ignore忽略的库）。
replicate-ignore-db=mysql        #不同步mysql系统数据库
slave-skip-errors = all          #跳过所有的错误错误，继续执行复制操作
relay_log=/var/lib/mysql/mysql-relay-bin    #指定中继日志的位置和命名
log_slave_updates=1              #允许备库将其重做的事件也记录到自身的二进制日志中
read_only=1                      #阻止任何没有特权权限的线程修改数据（对于需要在备库建表的应用来说不太实用）

    3. 启动复制

            在这一步告诉备库如何连接到主库并重放其二进制日志。这一步不要通过修改my.cnf的master_port或master_host这些选项，而是使用change master to语句，该语句完全替代了my.cnf中相应的设置，并且允许以后指向别的主库时无需重启备库。命令如下：

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.81.129',
    -> MASTER_USER='backup',
    -> MASTER_PASSWORD='backup',
    -> MASTER_LOG_FILE='MYSQL-BIN.0.00003',
    -> MASTER_LOG_POS=0;

    MASTER_LOG_POS参数被设置为0，因为要从日志的开头读起。当执行完这条语句后，可以通过SHOW SLAVE STATUS\G;来检查复制是否正确执行。（此图中的Read_Master）

                           。。。
        Seconds_Behind_Master:NULL

    运行下面的命令开始复制：

mysql> START SLAVE;

    执行该命令没有显示错误，再用SHOW SLAVE STATUS命令检查：

                ...

                

    从输出可以看出IO线程和SQL线程都已经成功连接到Master，并读取了主库所有的事件。日志位置发生变化，表明已经从主库获取和执行了一些事件。如果在主库上做一些数据更新，就会看到备库的文件或者日志位置都可能会增加，同时备库中的数据也会随之更新。

    注：当Slave_IO_Running为Connecting时，在确定连接主库命令无误下，检查服务器防火墙是否关闭。相关命令如下：

#停止firewall服务
systemctl stop firewalld.service
#禁止firewall服务开机启动
systemctl disable firewalld.service
#检查防火墙状态（开启显示running，关闭显示notrunning）
firewall-cmd --state

    我们还可以在Master的线程列表中看到复制线程。

    同样，在备库中也可以看到两个线程，一个IO线程，一个SQL线程：

    这些线程总是运行在“system user”账号下，和其它的线程不同。

四、测试主从复制

    在Master中登录

$ mysql -uroot -p

    创建测试库

mysql> CREATE DATABASE `replication`;
mysql> USE `replication`;
mysql> CREATE TABLE `hello`(
        `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
        `name` varchar(255) NOT NULL);

    在Slave查看是否同步过来了

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| replication        |
| sys                |
+--------------------+
mysql> USE replication;
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_replication |
+-----------------------+
| hello                 |
+-----------------------+

五、一些命令

    查看从服务器主机列表（1从对1主，1主可对多从）

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         2 |      | 3306 |         1 | 180f5c89-0be5-11e8-a1d5-00505634c3fc |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

    获取binlog文件列表

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       169 |
| mysql-bin.000002 |       169 |
| mysql-bin.000003 |      1992 |
+------------------+-----------+

    查看binlog文件内容

show binlog events; # 默认查看第一个binlog的文件内容
show binlog events in 'mysql-bin.000003'; # 查看指定binlog文件内容

以上就是主从复制的相关内容，如有不足欢迎指正~