mysql实现主从复制（实操及原理）

我不是女神ヾ 2023-06-18 03:00 4阅读 0赞

### 文章目录 ###

*  安装mysql
 *  主库配置
 *  从库配置
 *  验证
 *  原理

这里我们配置mysql的一主一从。机器使用如下：

<table> 
 <thead> 
  <tr> 
   <th>ip</th> 
   <th>role</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>101.132.138.185</td> 
   <td>master</td> 
  </tr> 
  <tr> 
   <td>122.51.214.240</td> 
   <td>slave</td> 
  </tr> 
 </tbody> 
</table>

# 安装mysql #

首先将本地下载好的mysql的rpm安装包scp到目标服务器上。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]  
首先将rpm安装包通过scp传送到服务器，然后安装配置mysql的yum源的rpm包。

rpm -Uvh mysql80-community-release-el7-1.noarch.rpm

安装mysql

yum install mysql-community-server

开启mysql服务

service mysqld start

通过ps指令可以发现mysql已经启动成功  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]  
mysql安装成功后创建的超级用户`root@localhost`的密码会被存储在`/var/log/mysqld.log`，可以使用如下命令查看密码  
`grep 'temporary' password /var/log/mysqld.log`  
使用mysql生成的root@localhost用户和密码登录数据库，并修改其密码，具体命令：

mysql -uroot -p
    ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

这样数据库就配置好了，具体有问题可以参考博主之前部署阿里云的[博客][Link 1]。

如果设置密码时提示密码不符合当前策略可以参考这篇[博客][Link 2]进行更改。

两台服务器的数据库都安装成功且能在本地远程连接后，开始进行主从的配置。

# 主库配置 #

首先创建一个用于复制的用户

mysql> GRANT REPLICATION SLAVE,FILE ON *.* TO 'yourusername'@'122.51.214.240' IDENTIFIED BY 'yourpassword';

122.51.214.240：是运行使用该用户的ip地址（这里是从库信息）  
yourusername：是新创建的自定义的用户名  
yourpassword：是新创建的用户名的密码

接下来修改mysql的配置文件，my.cnf（window系统时my.ini）  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2]  
重启MySQL服务：`systemctl restart mysqld`；  
重新登录mysql客户端，输入命令`show master status`查看一下是否成功。如下master配置成功。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 3]

# 从库配置 #

编辑从库的数据库配置信息添加一个唯一的server-id如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 4]  
重启从库数据库并登陆，配置主库信息。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 5]  
配置好主库信息后，通过`start slave;`开启从库同步，可以通过`show slave status\G;`查看同步状态，如下图，从库开启成功。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 6]

# 验证 #

这里我们首先导出主库的数据库信息在从库执行，此时主从数据库的数据量是一致的。如图为从库当前信息。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 7]  
接下来我们去主库中添加一条测试数据：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 8]  
回到主库中可以发现该记录已经被同步过来了。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 9]  
这里注意在实际操作的过程中因为笔者是先开启主从，后在从库中导入数据库的，所以复制失败了，通过`show slave status\G;`查看发现错误提示是没有该数据库。这里再次开启从库`start slave;`就可以了，不过还是建议先导入数据库，再开启从库复制。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 10]

# 原理 #

Mysql通过3个线程来完成主从库之间的数据复制：其中BinLog Dump线程跑在主库上，I/O线程和SQl线程跑在从库上。当从库启动复制（start slave）时，首先创建I/O线程连接主库，主库随后创建Binlog Dump线程读取数据库事件并发给I/O线程，I/O线程获取到数据库事件更新到从库的中继日志Realy log中去，之后从库上的SQl线程读取中继日志relay log 中更新的数据库事件并应用。

主从同步原理图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 11]  
**master记录数据更改操作**

*  启用binlog日志
 *  设置binlog日志格式
 *  设置server\_id

**slave运行2个线程**

*  Slave\_io：复制master主机binlog日志文件里的sql到本机的relay-log文件里
 *  Slave\_sql:执行本机relay\_log文件里的sql语句，重现master的数据操作

**基本思路**

*  确保数据相同：从库必须要有主库上的数据
 *  配置主服务器：启用binlog日志，授权用户，查看当前正在使用的日志
 *  配置从服务器：设置server\_id（唯一），指定主库信息
 *  测试配置：客户端连接主库写入数据，在从库上也能查询到

**确保数据一致**

*  Master服务器
 *  备份所有库
 *  Slave服务器
 *  清空同名库（如果有的话）
 *  离线导入由master提供的备份

这里我们再验证一下确保数据一致的功能：  
首先我们将从库中的表全部删除  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 12]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 13]  
如上图，没有对应表的话还是没办法同步成功。

笔者认为，主库的binlog日志记载的是在开启binlog线程之后的数据操作记录，即开启之前数据库中的记录binlog中是没有的，也就没有办法完成与从库的同步，所以一般建议是先在从库中导入主库实现最开始的全量复制，再开启主从配置，开始增量复制。  
这里做一下验证，我们这次再从库中只导入ssm\_shop数据的表结构，不引入表中的数据。如下图，表中记录是空的：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 14]  
然后在开启从库的`start slavel;`后在主库中插入一条测试数据。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 15]  
查看从库：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 16]  
只有一条新增的记录，所以上面的假设应该是正确的。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191129205137389.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191130193738678.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[Link 1]: https://blog.csdn.net/qq_24095055/article/details/86712540#Mysql_65
[Link 2]: https://blog.csdn.net/hello_world_qwp/article/details/79551789
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191201150333413.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20191201150645366.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20191201152103235.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/2019120115254513.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/2019120115461427.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20191201155151958.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20191201155233678.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20191201155630965.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/20191201160035554.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 11]: https://img-blog.csdnimg.cn/20191201160513756.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 12]: https://img-blog.csdnimg.cn/20191201161411815.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 13]: https://img-blog.csdnimg.cn/20191201161656141.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 14]: https://img-blog.csdnimg.cn/20191201162313762.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 15]: https://img-blog.csdnimg.cn/20191201162447711.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 16]: https://img-blog.csdnimg.cn/20191201162517906.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9oZXJvYmluLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70