MySQL主从复制与双主复制原理与配置

忘是亡心i 2022-04-22 06:32 203阅读 0赞

# 简介 #

复制是一种允许在从库上复制主库数据的功能。

# 原理 #

原理图  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70]  
图片网上找的

MySQL复制功能使用三个线程实现，一个在主服务器上，两个在从服务器上

*  Binlog备份线程。 主设备创建一个线程，以便在从库连接时将二进制日志内容发送到从库。可以SHOW PROCESSLIST在主服务器的输出中将此线程标识为Binlog Dump线程。  
    二进制日志转储线程获取主机二进制日志上的锁，用于读取要发送到从机的每个事件。一旦读取了事件，即使在事件发送到从站之前，锁也会被释放。
 *  从库I/O线程。 在START SLAVE从属服务器上发出语句时，从属服务器会创建一个I/O线程，该线程连接到主服务器并要求它发送二进制日志中记录的更新。  
    从库I/O线程读取主库Binlog Dump线程发送的更新 并将它们复制到从库的中继日志
 *  从库SQL线程，从库创建一个SQL线程来读取由从I/O线程写入的中继日志，并执行。

大致过程：  
从库会启动2个线程，一个I/O线程，一个SQL线程，I/O线程负责连接主库，完成认证和获取主库binlog日志的内容，然后写到从库的中继日志中。再由SQL线程读取并执行中继日志中的SQL语句。

# 用途 #

*  备份
 *  可扩展性：配合各种中间件保证数据库的高可用和负载均衡
 *  数据分析：分析数据可能对主服务器产生太大影响，这可以类似地在从库上处理，而主库不受额外负载的影响
 *  … …

# 常见架构 #

*  一主多从  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 1]
 *  级联复制  
    ![在这里插入图片描述][20181103001601620.png]
 *  环复制  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 2]
 *  双主复制  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 3]
 *  … …

# 设置复制 #

必须使用唯一ID配置主库和每个从库（使用该server-id选项）以及开启binlog日志

### 一主一从 ###

拓扑图  
![在这里插入图片描述][20181103002530743.png]  
这里的MySQL已经安装好了，直接开始设置

##### 第一步：修改主库配置文件 #####

在主库配置文件/etc/my.cnf中添加下面2行

log-bin=bin.log
    server-id=1     #必须保证每个数据库的server id不同

启动主库

mysqld_safe  &

##### 第二步：备份主库所有数据库 #####

先把主库当前时间点做个备份

mysqldump -uroot -pmysql --flush-logs --quick --single-transaction --master-data=2 --all-databases > ~/alldb.sql

##### 第三步：修改从库配置文件 #####

在从库的配置文件/etc/my.cnf中添加一行

log-bin=bin.log   #打开binlog日志
    server-id=2   #确保每个数据库的server-id不同

启动从库

mysqld_safe  &

##### 第四步：还原主库的完全备份 #####

在从库上，获取主库的备份文件

scp root@192.168.253.128:/root/alldb.sql ~/

导入从库(从库的用户名密码为 root/slave )

mysql -uroot -pslave < ~/alldb.sql

##### 第五步：在主库上添加用户，允许从库获取binlog #####

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'192.168.253.158' IDENTIFIED BY  'replication';

查看主库binlog信息

mysql> SHOW MASTER  LOGS;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 4]  
如果这时候数据库是一直在往里读写数据的状态，我们可以查看刚刚完全备份的数据来确定binlog信息  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 5]  
这里完全备份的位置是120，我们数据库中查看的位置是352的原因是，在我们备份完了之后我们还创建了允许从库连接的用户所以主库又往前跑了一些距离，这里不希望从库也创建repl用户，所以采取的位置是352

##### 第六步：在从库配置主从复制 #####

mysql> CHANGE MASTER TO MASTER_HOST='192.168.253.128',MASTER_USER='repl',MASTER_PASSWORD='replication',MASTER_LOG_FILE='bin.000004',MASTER_LOG_POS=352;
    mysql> START SLAVE;

查看状态

SHOW SLAVE STATUS\G;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 6]  
说明主从配置成功

##### 第七步：测试 #####

这时候再主库创建一个新的数据库看从库能不能复制过去  
主库：

mysql> create database master;

在从库查看一下是否有这个数据库  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 7]

### 双主复制 ###

拓扑图  
![在这里插入图片描述][20181103012247525.png]  
简单地说就是互为主从，我们再刚才一主一从的基础上继续做，刚才已经设置好了一主一从，这时候只需要在192.168.253.158上创建个账户允许192.168.253.128连接过来复制数据，然后再192.168.253.158上配置向相关信息即可。

注意：互为主从时，自动增长id可能会冲突，所以需要设置一下
       
    定义一个节点使用奇数id，下面配置表示从1开始每次增2
        auto_increment_offset=1    
        auto_increment_increment=2
    另一个节点使用偶数id，下面配置表示从2开始没次递增2
        auto_increment_offset=2
        auto_increment_increment=2

环境：  
基于刚才一主一从配置

### 第一步：修改配置文件保证自动增长id不冲突 ###

192.168.253.128配置文件的几个关键项

log-bin=bin.log
    server-id=1
    auto_increment_offset=2
    auto_increment_increment=2

192.168.253.158配置文件的几个关键项

log-bin=bin.log
    server-id=2
    auto_increment_offset=1
    auto_increment_increment=2

### 第二步：在192.168.253.158上添加用户 ###

在192.168.253.158上创建用户，允许192.168.253.128连接获取binlog

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'192.168.253.128' IDENTIFIED BY  'replication';

查看192.168.253.158的binlog信息  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 8]

### 第三步：在192.168.253.128上配置主从复制 ###

在192.168.253.128上配置主从复制

mysql> CHANGE MASTER TO MASTER_HOST='192.168.253.158',MASTER_USER='repl',MASTER_PASSWORD='replication',MASTER_LOG_FILE='bin.000004',MASTER_LOG_POS=669051;
    START SLAVE;

查看状态

SHOW SLAVE STATUS\G

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 9]

### 第四步：测试 ###

现在在192.168.253.158上再创建个数据库，看192.168.253.128能不能获取到数据

create database slave;

在192.168.253.128上看一下是否成功了  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 10]

# 仅复制有限个数据库/表相关的数据 #

这个实现方式有2个

*  第一种方式：主库配置仅向二进制日志中记录有关特定数据库相关的写操作；

#通过这两个变量设置
    binlog_do_db=     #记录某数据库
    binlog_ignore_db=  #不记录某数据库

*  第二种方式：从库配置仅读取/忽略某数据库/表的相关操作

#通过下面几个变量实现
    Replicate_Do_DB=    #表示复制指定数据库
    Replicate_Ignore_DB= # 表示不复制指定数据库
    Replicate_Do_Table=  # 表示复制指定表
    Replicate_Ignore_Table= #表示忽略指定表
    Replicate_Wild_Do_Table=  #从库被限制为复制与指定通配符模式匹配的表
    Replicate_Wild_Ignore_Table=#从库将忽略复制流中与指定通配符模式匹配的表的任何更改

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70]: /images/20220422/2fa01cd603a24193900e3e1aeccd69f0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 1]: /images/20220422/b998b6ce43a34fa2b862b0453839c4b4.png
[20181103001601620.png]: /images/20220422/16fe9b3776a74e9196f93248ad829cd1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 2]: /images/20220422/44e62e5d72154394a260b00393646523.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 3]: /images/20220422/01a544aa49324dde8eb26367eb0dca96.png
[20181103002530743.png]: /images/20220422/39701110c86a4bd392506692cc319328.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 4]: /images/20220422/dc6f33dcc09b4697838dbfa50045c03c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 5]: /images/20220422/1375673b6f45450eb306b74439de303c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 6]: /images/20220422/e070f2eefa664b248432eb308a345ebf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 7]: /images/20220422/852cef94160c42c290d0a77f368044ef.png
[20181103012247525.png]: /images/20220422/4e9abce4f8c948c5a3a3bb12c0171a39.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 8]: /images/20220422/f8cc74499fee49c7a0f5f1a482f62e7e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 9]: /images/20220422/37b62d0c7b9543719f932a4bea2535bb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 10]: /images/20220422/43e541ea4a444555b47451028a458d3d.png