Mysql主从复制详解及配置--异步复制

约定不等于承诺〃 2023-07-11 08:41 2阅读 0赞

### Mysql主从复制详解 ###

*  一、MySQL Replication
 *   *  MySQL中主从复制的优点包括：
     *  Replication 的原理
     *  一主多从
     *  关于二进制日志
 *  二、rhel7.3下mysql的主从复制配置
 *   *  1.主数据库（server1）配置
     *  2.从数据库的配置
     *  3.主从测试：

最常见的集群方案  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70]

# 一、MySQL Replication #

主从复制（也称 AB 复制）允许将来自一个MySQL数据库服务器（主服务器）的数据复制到一个或多个MySQL数据库服务器（从服务器）。

复制是异步的 从站不需要永久连接以接收来自主站的更新。

根据配置，可以复制数据库中的所有数据库，所选数据库甚至选定的表。

主从复制的机中模式可以参考：[https://blog.csdn.net/qq\_35887546/article/details/104757085][https_blog.csdn.net_qq_35887546_article_details_104757085]

## MySQL中主从复制的优点包括： ##

*  **横向扩展解决方案** \-在多个从站之间分配负载以提高性能。在此环境中，所有写入和更新都必须在主服务器上进行。但是，读取可以在一个或多个从设备上进行。该模型可以提高写入性能（因为主设备专用于更新），同时显着提高了越来越多的从设备的读取速度。
 *  **数据安全性** \- 因为数据被复制到从站，并且从站可以暂停复制过程，所以可以在从站上运行备份服务而不会破坏相应的主数据。
 *  **分析** \- 可以在主服务器上创建实时数据，而信息分析可以在从服务器上进行，而不会影响主服务器的性能。
 *  **远程数据分发** \- 可以使用复制为远程站点创建数据的本地副本，而无需永久访问主服务器。

## Replication 的原理 ##

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

> 前提是作为主服务器角色的数据库服务器必须开启二进制日志

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

需要注意的是：

*  每个从服务器都会收到主服务器二进制日志的全部内容的副本。
 *  从服务器设备负责决定应该执行二进制日志中的哪些语句。
 *  除非另行指定，否则主从二进制日志中的所有事件都在从站上执行。
 *  如果需要，可以将从服务器配置为仅处理一些特定数据库或表的事件。

**重要: 无法将主服务器配置为仅记录特定事件。**

每个从站(从服务器)都会记录二进制日志坐标：

文件名
    文件中它已经从主站读取和处理的位置。

由于每个从服务器都分别记录了自己当前处理二进制日志中的位置，因此可以断开从服务器的连接，重新连接然后恢复继续处理。

## 一主多从 ##

如果一主多从的话，这时主库既要负责写又要负责为几个从库提供二进制日志。此时可以稍做调整，将二进制日志只给某一从，这一从再开启二进制日志并将自己的二进制日志再发给其它从。或者是干脆这个从不记录只负责将二进制日志转发给其它从，这样架构起来性能可能要好得多，而且数据之间的延时应该也稍微要好一些。工作原理图如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 2]

## 关于二进制日志 ##

`mysqld`将数字扩展名附加到二进制日志基本名称以生成二进制日志文件名。每次服务器创建新日志文件时，该数字都会增加，从而创建一系列有序的文件。每次启动或刷新日志时，服务器都会在系列中创建一个新文件。服务器还会在当前日志大小达到`max_binlog_size`参数设置的大小后自动创建新的二进制日志文件 。二进制日志文件可能会比`max_binlog_size`使用大型事务时更大， 因为事务是以一个部分写入文件，而不是在文件之间分割。

为了跟踪已使用的二进制日志文件， `mysqld`还创建了一个二进制日志索引文件，其中包含所有使用的二进制日志文件的名称。默认情况下，它具有与二进制日志文件相同的基本名称，并带有扩展名`.index`。在`mysqld`运行时，不应手动编辑此文件。

`二进制日志文件`通常表示包含数据库事件的单个编号文件。

`二进制日志` 表示含编号的二进制日志文件集加上索引文件。

`SUPER` 权限的用户可以使用`SET sql_log_bin=0`语句禁用其当前环境下自己的语句的二进制日志记录

# 二、rhel7.3下mysql的主从复制配置 #

实现Mysql数据库主从复制需要进行的配置：

主服务器：
        开启二进制日志
        配置唯一的server-id
        获得master二进制日志文件名及位置
        创建一个用于slave和master通信的用户账号
    从服务器：
    	 配置唯一的server-id
         使用master分配的用户账号读取master二进制日志
    	 启用slave服务

此实验需要两台虚拟机：

<table> 
 <thead> 
  <tr> 
   <th>虚拟机名称</th> 
   <th>ip</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>server1</td> 
   <td>172.25.63.1</td> 
   <td>主数据库服务器</td> 
  </tr> 
  <tr> 
   <td>server2</td> 
   <td>172.25.63.2</td> 
   <td>从数据库服务器</td> 
  </tr> 
 </tbody> 
</table>

## 1.主数据库（server1）配置 ##

**<1>安装mysql**

tar xf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
    yum install mysql-community-client-5.7.28-1.el7.x86_64.rpm mysql-community-common-5.7.28-1.el7.x86_64.rpm mysql-community-libs-5.7.28-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm mysql-community-server-5.7.28-1.el7.x86_64.rpm -y

**<2>编辑mysql的主配置文件/etc/my.cnf**  
![在这里插入图片描述][20200305104509742.png]  
其中log-bin对应的日志名随便给；server-id对应的数字也随便给，只是一般从1开始。

\#在最后写入

log-bin=mysql-bin		#启动mysql二进制日志,即数据同步语句,从数据库会一条一条的执行这些语句
    server-id=1		#服务器唯一标识

<3>开启数据库，查看原始密码

![在这里插入图片描述][20200309161035859.png]  
![在这里插入图片描述][20200309161046965.png]<4>进行数据库安全初始化(登陆密码为查看到的原始密码)——如果不进行初始化，进入数据库是不能进行任何操作的，所以要进行安全初始化。

\#修改密码，我的秘密为Ding123@westos.com

\#注意这里设置新密码时,密码是有要求的,要求为:数字+大小写字母+特殊字符,且超过8位。这是因为该数据库版本开启了密码检测插件,密码太过简单会报错。

\#第一个选项敲回车,其余选项全都敲y

<5>以root用户及刚刚安全初始化设置的密码，登陆数据库

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 3]  
<4>创建有复制权限的帐号，并查看该帐号是否创建成功。

\#可创建从数据库的masterbackup用户和权限

\#\#\*.\*表示所有数据库的所有表。

\#\#172.25.8.%表示172.25.8网段,即0-255的IP都可以访问主服务器,正式环境请配置指定从服务器IP。

\#\#若将172.25.8.%该为%,则表示任何IP均可作为从数据库来访问主服务器。  
![在这里插入图片描述][20200305104637777.png]![在这里插入图片描述][2020030510464777.png]

<5>查看主库状态：可以看到当前记录日志的日志文件名和Position号。

也可以查看主机的日志，主机的server-id，主机的二进制日志是否开启

\#查看主库的状态  
![在这里插入图片描述][2020030510473672.png]  
\#查看主机的日志  
![在这里插入图片描述][20200305104750390.png]  
\#查看本机的server-id  
![在这里插入图片描述][20200305104758150.png]  
\#查看本机的二进制日志是否开启  
![在这里插入图片描述][20200305104809104.png]

![在这里插入图片描述][20200305104818772.png]  
其中mysql-bin.0000001日志位于/var/lib/mysql目录下，  
![在这里插入图片描述][20200305104838736.png]  
且文件类型为日志，所以要利用mysqlbinlog命令来查看。

命令有三种，看到的内容都是一样的。第一种：mysqlbinlog binlog.000001；第二种： mysqlbinlog binlog.000001 -v v；

第三种：mysqlbinlog binlog.00001 --base64-output=DECODE-ROWS（其中–base64-output参数是通过mysqlbinlog --help | grep out 得到的）  
![在这里插入图片描述][20200305104848532.png]

## 2.从数据库的配置 ##

<1>安装mysql并配置

<2>编辑/etc/my.cnf文件

![在这里插入图片描述][20200305105046804.png]  
\#服务器唯一标识,如果有多个从服务器,每个服务器的server-id不能重复,跟IP一样是唯一标识,如果你没设置server-id或者设置为0,则从服务器不会连接到主服务器。因为主库是1,所以这里不能写1,要和主库不同,习惯上顺序是递增的,所以我这里写2

在最后一行写入:

server-id=2

<3>开启数据库，查看原始密码，并进行数据库安全初始化(登陆密码为查看到的原始密码)

![在这里插入图片描述][20200309161357214.png]  
修改完密码后,第一个选项敲直接回车,后面的选项全都敲y  
![在这里插入图片描述][20200309161408736.png]  
<4>查看主库的授权用户是否设置成功

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 4]  
**切记：此处查看之后，必须退出来，否则就是在主库操作了。**

<5>设定从库，将主库与从库连接起来，并开启从库  
![在这里插入图片描述][20200305105153710.png]  
log\_file 和 log\_pos 在master 中用 show master status; 查看  
![在这里插入图片描述][20200305105204776.png]  
<6>查看从库状态

也可以查看主机的server-id，主机的二进制日志是否开启  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 5]  
如果Slave\_IO\_Running和Slave\_SQL\_Running都为yes，则表示正常

## 3.主从测试： ##

1.在主库端建立库westos和表usertb,并插入信息  
![在这里插入图片描述][20200305105521186.png]  
\#创建数据库westos  
![在这里插入图片描述][20200305105528247.png]  
![在这里插入图片描述][20200305105541924.png]  
\#创建表usertb  
![在这里插入图片描述][20200305105550132.png]  
\#查看表usertb的结构  
![在这里插入图片描述][20200305105558155.png]  
\#向表usertb中插入信息  
![在这里插入图片描述][20200305105606916.png]  
\#查看表usertb的信息  
![在这里插入图片描述][20200305105618845.png]  
值的注意的是：

（1）主库的Position号是会发生改变的  
![在这里插入图片描述][20200305105631882.png]  
（2）server1（主库）重启一次mysqld服务，二进制日志的名字会变一次

2.从库端查看是否存在在主库中创建的内容  
![在这里插入图片描述][20200305105641533.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 6]  
**结论：**

从库看到的内容同主库一样，表示主从数据库复制搭建成功。  
值的注意的是：主库编写的内容会同步到从库，当然从库也可以编写内容，但是不会同步到主库。

server2（从库）：

![在这里插入图片描述][20200305105716681.png]  
server1（主库select \* from usertb;）：

![在这里插入图片描述][20200305105732627.png]  
为了不影响后面的实验，将从库上插入的数据删除，保证主库和从库数据是一致的

![在这里插入图片描述][20200305105737950.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200304203631784.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2,size_16,color_FFFFFF,t_70
[https_blog.csdn.net_qq_35887546_article_details_104757085]: https://blog.csdn.net/qq_35887546/article/details/104757085
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200304204115488.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200304204824157.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2,size_16,color_FFFFFF,t_70
[20200305104509742.png]: https://img-blog.csdnimg.cn/20200305104509742.png
[20200309161035859.png]: https://img-blog.csdnimg.cn/20200309161035859.png
[20200309161046965.png]: https://img-blog.csdnimg.cn/20200309161046965.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200305104621939.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2,size_16,color_FFFFFF,t_70
[20200305104637777.png]: https://img-blog.csdnimg.cn/20200305104637777.png
[2020030510464777.png]: https://img-blog.csdnimg.cn/2020030510464777.png
[2020030510473672.png]: https://img-blog.csdnimg.cn/2020030510473672.png
[20200305104750390.png]: https://img-blog.csdnimg.cn/20200305104750390.png
[20200305104758150.png]: https://img-blog.csdnimg.cn/20200305104758150.png
[20200305104809104.png]: https://img-blog.csdnimg.cn/20200305104809104.png
[20200305104818772.png]: https://img-blog.csdnimg.cn/20200305104818772.png
[20200305104838736.png]: https://img-blog.csdnimg.cn/20200305104838736.png
[20200305104848532.png]: https://img-blog.csdnimg.cn/20200305104848532.png
[20200305105046804.png]: https://img-blog.csdnimg.cn/20200305105046804.png
[20200309161357214.png]: https://img-blog.csdnimg.cn/20200309161357214.png
[20200309161408736.png]: https://img-blog.csdnimg.cn/20200309161408736.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200305105135356.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2,size_16,color_FFFFFF,t_70
[20200305105153710.png]: https://img-blog.csdnimg.cn/20200305105153710.png
[20200305105204776.png]: https://img-blog.csdnimg.cn/20200305105204776.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200305105217714.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2,size_16,color_FFFFFF,t_70
[20200305105521186.png]: https://img-blog.csdnimg.cn/20200305105521186.png
[20200305105528247.png]: https://img-blog.csdnimg.cn/20200305105528247.png
[20200305105541924.png]: https://img-blog.csdnimg.cn/20200305105541924.png
[20200305105550132.png]: https://img-blog.csdnimg.cn/20200305105550132.png
[20200305105558155.png]: https://img-blog.csdnimg.cn/20200305105558155.png
[20200305105606916.png]: https://img-blog.csdnimg.cn/20200305105606916.png
[20200305105618845.png]: https://img-blog.csdnimg.cn/20200305105618845.png
[20200305105631882.png]: https://img-blog.csdnimg.cn/20200305105631882.png
[20200305105641533.png]: https://img-blog.csdnimg.cn/20200305105641533.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200305105705983.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2,size_16,color_FFFFFF,t_70
[20200305105716681.png]: https://img-blog.csdnimg.cn/20200305105716681.png
[20200305105732627.png]: https://img-blog.csdnimg.cn/20200305105732627.png
[20200305105737950.png]: https://img-blog.csdnimg.cn/20200305105737950.png