MySQL主从复制的实现

朴灿烈づ我的快乐病毒、 2023-06-19 02:49 10阅读 0赞

# MySQL主从复制的理解图： #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2Njg4MTQz_size_16_color_FFFFFF_t_70][]

# MySQL Replication原理 #

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

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

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

MySQL中复制的优点包括：

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

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

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

**每个从服务器都会收到主服务器二进制日志的全部内容的副本。**

**从服务器设备负责决定应该执行二进制日志中的哪些语句。**

**除非另行指定，否则主从二进制日志中的所有事件都在从站上执行。**

**如果需要，您可以将从服务器配置为仅处理一些特定数据库或表的事件。**

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

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

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

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

### 一主多从 ###

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2Njg4MTQz_size_16_color_FFFFFF_t_70 1][]  
  
（上面的都来自于[Mysql 主从复制][Mysql]，这种原理类的内容，懒得自己再敲一遍了）

# **实现篇** #

首先需要创建MySQL多实例，这里我创建的是一主一从，如果需要多从，上面写有思路。  
**1、复制第一个安装的MySQL Server**，直接整个文件夹复制，如下图。

![20191204143205339.png][]

在别的路径下粘贴之后，记得删除data文件夹，否则新的MySQL实例的配置文件会一直找第一个MySQL服务，然后新的MySQL实例的服务就会一直无法启动！我的主库为MySQL2，从库为MySQL3。因为我不想操作我第一个安装的MySQL服务。如下图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2Njg4MTQz_size_16_color_FFFFFF_t_70 2][]

**2、修改配置文件my.ini**：两个端口、两个dir路径、server-id和主库的二进制日志位置

关于server-id：

①mysql的同步的数据中是包含server-id的，用于标识该语句最初是从哪个server写入的，所以server-id一定不同

② 每一个同步中的slave在master上都对应一个master线程，该线程就是通过slave的server-id来标识的；每个slave在master端最多有一个master线程，如果两个slave的server-id 相同，则后一个连接成功时，前一个将被踢掉。 这里至少有这么一种考虑：

slave主动连接master之后，如果slave上面执行了slave stop；则连接断开，但是master上对应的线程并没有退出；当slave start之后，master不能再创建一个线程而保留原来的线程，那样同步就可能有问题；

③ 在mysql做主主同步时，多个主需要构成一个环状，但是同步的时候有要保证一条数据不会陷入死循环，这里就是靠server-id来实现的

[client]
    
    port=3307
    
    [mysql]
    no-beep
    
    default-character-set=utf8
    
    
    # server_type=3
    [mysqld]
    
    
    
    # The TCP/IP Port the MySQL Server will listen on
    port=3307
    
    # 设置mysql的安装目录
    
    basedir="C:/MySQL2"
    
    # 设置mysql数据库的数据的存放目录，这个data目录如果不存在，需要自己手动在创建
    
    datadir="C:/MySQL2/data/"
    
    
    # Server Id.
    server-id=2
    #主库二进制日志的位置
    log-bin=C:/MySQL2/mysql-bin.log

### 以下所有命令建议在管理员权限下执行 ###

**3、初始化**：打开CMD窗口，跳转到MySQL2/bin路径下，执行命令初始化MySQL，这里初始化成功，就会自动生成data文件夹，而且CMD也会显示一个随机生成的MySQL密码，这里可以记住它，也可以在MySQL2/data/xxx.err这个err文件中查看。MySQL5.7以前的版本在安装时不需要执行这个初始化命令也可以。

mysqld --initialize --user=mysql --console

**4、安装mysql**：指定服务的名称MySQL2和配置文件my.ini的路径

mysqld install MySQL2 --defaults-file="C:\MySQL2\my.ini"

**5、修改服务：**win+r 输入regedit命令打开注册表，找到HKEY\_LOCAL\_MACHINE -> SYSTEM -> CurrentControlSet -> services -> MySQL2，可以看到imagePath参数值

"C:\MySQL Server 5.7\bin\mysqld" --defaults-file=C:\MySQL2\my.ini MySQL2
    
    第一个mysqld的位置需要修改成你的新MySQL2服务的mysqld位置，如下
    
    "C:\MySQL2\bin\mysqld" --defaults-file=C:\MySQL2\my.ini MySQL2
    
    这里如果不修改，你的新MySQL服务也会一直无法启动！打开data文件夹下的xxx-slow.log文件
    可以看到执行的还是原来的MySQL的mysqld这个配置文件

**6、启动主库服务：**

net start MySQL2

这时候如果启动MySQL服务失败，请先删除已安装的MySQL2服务：**sc delete MySQL2（服务名称）。**然后再看看上面的步骤，是哪一个配置没有按照步骤来修改。启动成功，就算是生成MySQL多实例了。这时候包括原来的MySQL Server服务，已经有两个了，端口号分别为3306、3307。然后我们就需要修改密码，这时候MySQL2的密码有3种可能：  
1、密码丢失：使用工具连接mysql服务的时候账号名是root，密码就不用填。如果要新密码就先登录mysql，然后执行命令

set password=password(“新密码”);

2、随机生成了新密码：在MySQL安装目录下的data文件夹的err文件最后几行就可以看到新密码，这是在前面执行MySQL初始化命令的时候随机生成的，如果你没有清空cmd，也可以在CMD记录中看到，然后修改密码即可  
3、密码过期：在MySQL安装目录下的data文件夹的err文件最后几行可以看到密码过期的信息，这时候只能重新设置密码  
重设密码原理：跳过数据库的用户验证，直接Update更新mysql数据库的user表里的密码字段，具体步骤：  
①管理员模式打开cmd窗口到MySQL2/bin目录下先停止服务net stop MySQL2  
②输入命令  mysqld --skip-grant-tables  回车，此时就跳过了mysql的用户验证。注意输入此命令之后命令行就无法操作了，此时可以再打开一个新的cmd窗口（记为cmd2）  
③cmd2切换到MySQL2/bin目录下，然后直接输入mysql，不需要带任何登录参数直接回车就可以登陆上数据库  
④输入 use mysql;   选择mysql数据库。  
⑤更改root密码  
MySQL5.7版本以前：输入

update user set password=password('你的新密码') where user='root' and host='localhost';

MySQL5.7版本以后：输入

update mysql.user set authentication_string=PASSWORD("你的新密码") where user="root" and host="localhost"；

因为MySQL新版本用于存用户密码的字段名为authentication\_string而不是 password，且新密码必须使用PASSWORD("xxxx")函数进行加密。以上所有请在管理员模式下执行

**7、配置从库，记为MySQL3**，它的my.ini配置如下：

[client]
    
    port=3308
    
    [mysql]
    no-beep
    
    default-character-set=utf8
    
    
    # server_type=3
    [mysqld]
    
    
    
    # The TCP/IP Port the MySQL Server will listen on
    port=3308
    
    # 设置mysql的安装目录
    
    basedir="C:/MySQL3"
    
    # 设置mysql数据库的数据的存放目录，这个data目录如果不存在，需要自己手动在创建
    
    datadir="C:/MySQL3/data/"
    
    
    # Server Id.
    server-id=2
    #从库不需要这个配置文件
    #log-bin=C:/MySQL2/mysql-bin.log

然后其他安装步骤跟主库一样。

**8、连接MySQL主从库**：通过Navicat Premium等工具连接了MySQL2、MySQL3，3307端口的记为3307master，3308的记为3308slave，test是我用来测试主从复制功能是否正常的测试数据库。如下图：

![20191204150246617.png][]

要想主从复制，就要数据库同构，即主从数据库3307master、3308slave所有的数据库名称一致。如果你发现从库中不包含主库同名数据库，这时候就直接新建同名数据库就可以了。

关于在主库中配置**binlog-do-db=dbname（要同步的数据库名字）** ：我并没有在主库的my.ini中配置这一项，只是手动在主从库创建了相同的数据库（即我要让它主从复制的数据库），然后MySQL也可以正确执行主从复制，它默认应该是从库全部复制（未验证）

**9、设置主库**：3307master主库中test数据库（第8步创建的测试库），新建查询，完成对从库的授权功能，SQL语句如下

#REPLICATION复制的权限  给  localhost（IP），@左边的是用户名，右边是密码
    GRANT REPLICATION SLAVE ON *.* TO 'root'@'localhost' IDENTIFIED BY 'root'

查看主库的状态

show MASTER STATUS

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2Njg4MTQz_size_16_color_FFFFFF_t_70 3][]

**10、设置从库**：3308slave从库中test数据库（第8步说了这个是测试库），新建查询，完成对主库的跟从功能，SQL语句如下  
配置主库的相关信息，log\_file和log\_pos是show master status（第9步图中标有）显示出来的

CHANGE MASTER TO
    master_host='localhost',master_port=3307,master_user='root',master_password='root',
    master_log_file='mysql-bin.000004',master_log_pos=5475

启动从库：

start slave

查看从库的状态

show slave status

主要看几点：slave\_sql\_running是否YES，slave\_io\_running是否YES，log\_file、log\_pos、master\_port是否和主库对应

**11、查看主库3307master目前拥有的从库**

show slave hosts;

主要看显示出来的数据是否和从库对应

**12、如果这时候全部正常，那说明主从复制成功了**

但是，因为每个人机器环境不一样，可能主从复制仍然失败。即主库新建表更新数据之后，从库没有实时跟着更新数据。经测试，等待几分钟，从库有概率跟着更新部分数据，具体我也不知道为什么。  
解决办法：**电脑重启**。讲道理只要重启MySQL2、MySQL3服务（主从服务）就可以了。可是经过测试，我重启电脑才解决问题。

**13、切记不要在从库进行写操作**，会导致slave\_sql\_running变成了NO，主从复制失败。原因：主库的postion变了，从库没有跟着变

解决办法：  
（1）、3308slave执行以下命令

slave stop; 
    set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
    slave start;

通过show slave status命令发现，slave\_sql\_Runing=NO,还是没有解决，这时候就需要重新连接配置从服务。  
（2）、重新配置：  
3308slave从库中执行：**stop slave;**  
3307master主库中执行：**show MASTER STATUS**，记录postion和log-file  
最后3308slave从库中执行：

CHANGE MASTER TO
    master_host='localhost',master_port=3307,master_user='root',master_password='root',
    master_log_file='主库的文件名',master_log_pos=主库显示的数字

再次通过**show slave status;**命令发现，slave\_sql\_Runing=YES,这时候主从复制应该成功了，但还是有概率失败，这时候请看第三步  
（3）、再次配置：第一次主从复制，需要主从库达到同构状态（即所拥有的数据库和数据都一样），所以先**stop slave;**然后手动同步主库数据到从库。再次执行配置2，即可成功。

**建议：在确保主从复制成功之后，断开从库数据库连接，这样子就不会不小心操作到了，而且从库也会自动更新数据。**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2Njg4MTQz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2019120414223310.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2Njg4MTQz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2Njg4MTQz_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191204142327454.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2Njg4MTQz,size_16,color_FFFFFF,t_70
[Mysql]: https://www.jianshu.com/p/faf0127f1cb2
[20191204143205339.png]: https://img-blog.csdnimg.cn/20191204143205339.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2Njg4MTQz_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191204143349704.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2Njg4MTQz,size_16,color_FFFFFF,t_70
[20191204150246617.png]: https://img-blog.csdnimg.cn/20191204150246617.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2Njg4MTQz_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20191204151536930.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2Njg4MTQz,size_16,color_FFFFFF,t_70