MySQL主从复制

一.案例概述

        在实际的生产环境中，如果对数据库的读和写都在同一个数据库服务器中操作，无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，般来说都是通过主从复制（Master-Slave）来同步数据，再通过读写分离来提升数据库并发负载能力的方案来进行部署与实施。

        一台主MySQL服务器带两台从MySQL服务器做数据复制，前端应用在进行数据库写操作时，对主服务器进行操作，在进行数据库读操作时，对两台从服务器进行操作，这样大量减轻了对主服务器的压力。

二.MySQL主从复制原理

        MySQL的主从复制和MySQL的读写分离两者有着紧密联系，首先要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离

1.MySQL支持的复制类型

（1）基于语句的复制：在主服务器上执行的SQL语句，在从服务器上执行同样的语句；MySQL默认采用基于语句的复制，效率比较高。

（2）基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍。

（3）混合类型的复制：默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

2.复制的过程

        （1）在每个事务更新数据完成之前，Master将这些改变记录进二进制日志。写入二进制日志完成后，Master通知存储引擎提交事务。

        （2）Slave将Master的Binarylog复制到其中继日志（Relaylog）。首先，Slave开始个工作线程——I/O线程，O线程在Master上打开一个普通的连接，然后开始Binlog dump process。Binlog dump process从Master的二进制日志中读取事件，如果已经跟上Master，它会睡眠并等待Master产生新的事件。IO线程将这些新事件写入中日志。

        （3）SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新Slave数据，使其与Master中的数据保持一致。只要该线程与IO线程保持一致，中继口志通常会位于OS的缓存中，所以中口志的开销很小。

        复制过程有一个很重要的限制，即复制在Slave上是串行化的，也就是说Master上的并行更新操作不能在Slave上并行操作。

三.主从复制案例

1.建立时间同步环境

（1）查看主从服务器时间

使用date命令查看主从服务器时间。如果时间差大则要在主服务器上搭建时间同步服务器

（2）搭建时间同步服务器

       ntp.x86_64 0:4.2.6p5-29.el7.centos.2在CentOS7.9的光盘中就有，可以通过命令下载（yum可以自动解决依赖关系）：yum -y install ntp

（3）配置NTP

修改ntp配置文件/etc/ntp.conf添加如下两行，修改完成后就可以重启服务，并设置开机自启

server 127.127.1.0
fudge 127.127.1.0 stratum 8

[root@localhost ~]# systemctl restart ntpd
[root@localhost ~]# systemctl enable ntpd
Created symlink from /etc/systemd/system/multi-user.target.wants/ntpd.service to /usr/lib/systemd/system/ntpd.service.

2.配置防火墙和SElinux

在每台服务器上配置相关的防火墙规则，虚拟机测试可直接关闭防火墙

[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# setenforce 0

3.在从节点上进行时间同步

[root@localhost ~]# yum -y install ntpdate
[root@localhost ~]# ntpdate 192.168.10.101        //搭建了时间同步服务器的主机地址

4.安装MySQL数据库

在master，slave1，slave2服务器上安装MySQL数据，先下载相关模块，然后编辑配置文件，初始化数据库。

5.配置master主服务器

（1）修改配置文件/etc/my.cnf

server-id=11
log-bin=master-bin
binlog-format=MIXED        #混合复制

log-slave-updates=true     #Slave可以是其他 Slave 的 Master，从而扩散 Master 的更新
binlog-ignore-db=test      #不记录指定的数据库的二进制日志  
replicate-ignore-db=test    #设置不需要同步的库
binlog_cache_size = 1M    #日志缓存的大小
expire_logs_days=3       #自动过期清理日志的天数

（2）修改完成后重启服务，进入MySQL服务器配置同步

grant replication slave on *.* to 'myslave'@'192.168.10.%' identified by '123456';

这条 MySQL 命令用于授予一个名为 'myslave' 的用户在所有数据库（.）上作为从服务器（Slave）的复制用户的权限，允许从指定的 IP 地址范围（192.168.10.%）连接到主服务器（Master）。该命令的具体含义如下：

• grant replication slave: 授权用户具有从服务器（Slave）的复制权限。
• on *.*: 表示授权适用于所有数据库和所有表。
• to 'myslave'@'192.168.10.%': 指定用户名为 'myslave'，允许从 IP 地址段为 192.168.10.% 的主机连接。
• identified by '123456': 指定连接时使用的密码为 '123456'。

需要注意的是，这条命令只授权 'myslave' 用户作为从服务器连接到主服务器进行复制操作，它并不授予 'myslave' 用户对主服务器中数据的其他任何操作权限。

配置完成后重新加载授权表

mysql> flush privileges;

查看主服务器（Master）的当前二进制日志文件名和日志位置（binlog coordinates）

mysql> show master status;

6.配置从服务器

在slave1，slave2服务器上面分别执行以下步骤

（1）在/etc/my.cnf中修改或添加相关内容

server-id       = 22            ##修改，值不能和其他mysql服务器重复
relay-log=relay-log-bin            ##添加（可不指定）
relay-log-index=slave-relay-bin.index    ##添加（可不指定）

备注：

relay-log=name    中继日志的文件的名字
relay-log-index=name      MySQL slave 在启动时需要检查relay log index 文件中的relay log信息，此处定义该索引文件的名字

（2）修改完成后再次重启服务

（3）进入服务器配置主从复制

change master to master_host='192.168.10.101',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=337;

备注：

这个命令是用于设置 MySQL 数据库的主从复制。让我来解释一下各个部分的含义：

• CHANGE MASTER TO: 这是 MySQL 中用于修改主从复制配置的命令。
• master_host='192.168.10.101': 指定主数据库的主机地址为 192.168.10.101。这是从数据库连接到主数据库的地址。
• master_user='myslave': 指定从数据库连接到主数据库时使用的用户名为 myslave。这个用户需要有合适的权限来进行复制操作。
• master_password='123456': 指定连接到主数据库时使用的密码为 123456。确保密码是安全的并且有足够的复杂性。
• master_log_file='master-bin.000001': 指定从数据库开始复制时使用的主数据库的二进制日志文件名。这里是 master-bin.000001，实际应根据主数据库的配置来确定。
• master_log_pos=337: 指定从数据库开始复制时读取的主数据库二进制日志文件的位置。这个位置是一个标识日志文件中特定位置的数字。

（4）启动复制进程，查看 MySQL 数据库复制状态的命令

start slave
show slave status\G