MySQL高级性能优化---主从复制

不念不忘少年蓝@ 2021-12-12 15:53 549阅读 0赞

# 1. 主从复制的原理 #

数据库有个bin-log二进制文件，记录了所有sql语句。我们的目标就是把主数据库的bin-log文件的sql语句复制过来。让其在从数据的relay-log重做日志文件中再执行一次这些sql语句即可。下面的主从配置就是围绕着这个原理配置。具体需要三个线程来操作。

具体需要三个线程来操作  
\*\* **去请求主库的binlog，并将得到的binlog日志写到relay-log（中继日志）文件中。  
主库会生成一个**log dump线程\*\*，用来给从库**i/o线程**的binlog。  
**SQL线程**，会读取relaylog文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70]

# 2. 主机配置 #

## 2.1 首先修改mysql的配置文件my.cnf，使其支持二进制日志功能。 ##

修改my.cnf文件，增加以下内容

log-bin=mysql-bin
    binlog_format=mixed
    server-id=101

参数解释：  
log-bin=mysql-bin //将mysql二进制日志取名为mysql-bin  
binlog\_format=mixed //二进制日志的格式，有三种：statement/row/mixed,具体分别不多做解释，这里使用mixed  
server-id=101 //为服务器设置一个独一无二的id便于区分，这里使用ip地址的最后一位充当server-id

修改完之后重启mysql

service mysqld restart

## 2.2 在主服务器上为从服务器分配一个账号，就像一把钥匙，从服务器拿着这个钥匙，才能到主服务器上来共享主服务器的日志文件 ##

GRANT replication slave ON *.* TO 'mysql105'@'192.168.1.104' IDENTIFIED BY '123456';
    FLUSH PRIVILEGES;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 1]

## 2.3 查看主服务器BIN日志的信息（执行完之后记录下这两值，然后在配置完从服务器之前不要对主服务器进行任何操作，因为每次操作数据库时这两值会发生改变） ##

show master status;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 2]  
记录mysql-bin.000001和593这个值

# 3. 从机配置 #

## 3.1 首先修改mysql的配置文件my.cnf，使其支持二进制日志功能。 ##

log-bin=mysql-bin
    binlog_format=mixed
    server-id=105

唯一的区别是，server-id要和主mysql的server-id不一样，即server-id=105;其他两项是一样的，保存，并重启mySQL；

修改完之后重启mysql

service mysqld restart

## 3.2 设置从服务器配置 ##

进入从服务器mysql  
命令： \# mysql -u root -p

**关闭slave（如果你以前配置过主从的话，一定要先关闭）  
命令：stop slave;**

开始配置：  
输入下面代码即可：

CHANGE MASTER TO MASTER_HOST="192.168.1.103",
    MASTER_USER="mysql105",
    MASTER_PASSWORD="123456",
    MASTER_PORT=3306,
    MASTER_LOG_FILE="mysql-bin.000001",
    MASTER_LOG_POS=593;

参数解释：MASTER\_HOST : 设置要连接的主服务器的ip地址

MASTER\_USER : 设置要连接的主服务器的用户名

MASTER\_PASSWORD : 设置要连接的主服务器的密码

MASTER\_LOG\_FILE : 设置要连接的主服务器的bin日志的日志名称，即第3步得到的信息

MASTER\_LOG\_POS : 设置要连接的主服务器的bin日志的记录位置，即第3步得到的信息，（这里注意，最后一项不需要加引号。否则配置失败）  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 3]

## 3.3 启动从服务器 ##

start slave;

## 3.4 查看是否配置成功 ##

show slave status;

或者

show slave status\G;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 4]

## 3.5 停止服务 ##

stop slave;

# 4. 测试 #

## 4.1 在主机新建一个数据库testdb，新建一张表并插入几条测试数据 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 5]

## 4.2 查看从数据库是否同步数据 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 6]

# 5. 使用binlog主从复制的问题 #

*  之前在网上看到有人分享面试经验：binlog复制延迟太多怎么办，对于这个问题在工作当中也是很常见的一个问题。
 *  之前分析过，MySQL基于二进制日志binlog实现的主从复制是一种**异步复制**，即主库对于数据库修改操作，首先记录到binlog然后在修改数据库文件。主库的复制线程读取binlog然后传输给从库，从库的复制线程接收保存为relay log，然后再由SQL线程读取relay log并在从库执行该SQL。**故整个过程是存在延迟的，实现的是最终一致性**。所以如果项目需要使用主从复制，则前提是从库的数据存在延迟能满足业务需求。

数据库的一致性：

*  强一致性：系统中的某个数据被成功更新后，后续任何对该数据的读取操作都将得到更新后的值；
 *  弱一致性：系统中的某个数据被更新后，后续对该数据的读取操作可能得到更新后的值，也可能是更改前的值。但经过“不一致时间窗口”这段时间后，后续对该数据的读取都是更新后的值；
 *  最终一致性：是弱一致性的特殊形式，存储系统保证在没有新的更新的条件下，最终所有的访问都是最后更新的值。

由MySQL主从复制的过程分析可知，主从复制过程其实就是二进制日志读取，传输，接收，解析执行的过程，故整个过程涉及到的主要资源包括：CPU，内存，磁盘IO，网络IO。所以在发生延迟太多时，可以观察对应时刻的以上硬件资源的负载和使用情况。

## 主库 ##

*  主库主要是读取binlog文件然后传输出去，所以主要可能成为性能瓶颈的就是磁盘IO，内存，网络IO。
 *  磁盘IO：如果主库所在主机上面，如果还存在其他磁盘IO繁忙的进程，如kafka进程，则可能导致主库复制线程在读取binlog时存在竞争而导致读取缓慢，所以可以使用如iostat命令来监控。
 *  内存：复制线程首先需要将binlog读取到内存，然后发送出去，故如果主库主机的内存使用太大，也会影响数据的复制，具体可以通过top，free命令来监控；
 *  网络IO：复制线程最终要通过网络将数据传输出去，故如果主库主机的网络带宽太小或者主机上存在其他需要大量消耗网络带宽的进程，则网络带宽可能成为瓶颈。具体可以通过vmstat，nethogs等查看网络带宽消耗。

## 从库 ##

*  从库需要从主库接收binlog并写到relay log中，所以也存在与主库类似的问题。同时从库的SQL线程还需要读取relay log并执行对应的SQL，故除此之外，还可能由于CPU繁忙，导致SQL执行缓慢。

**在后续的博客中会使用pcx集群方案解决数据同步延迟最终一致性的致命问题**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70]: /images/20211210/9f7f942068844ee499a934f28709806d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 1]: /images/20211210/8dff730deeb04ea4bfd132b33efa1903.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 2]: /images/20211210/ebc34e96a704455fb3dfda76aedb88a4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 3]: /images/20211210/03d9226bc88548e3bd2d2ae25f68abef.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 4]: /images/20211210/d0d59960219845eb91ed3ec7f191fe83.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 5]: /images/20211210/a98b3721fbe74a8a8f933a61e6518bdb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 6]: /images/20211210/2e7196c1c40f460881c04f0a79397287.png