mysql主从复制、一主一从、一主多从、环形多主

最新推荐文章于 2024-04-26 15:58:24 发布

真不会摸鱼

最新推荐文章于 2024-04-26 15:58:24 发布

阅读量593

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/2301_79228611/article/details/132171958

版权

MySQL主从复制（原理+实操）一主一从、一主多从、环形多主

概述

MySQL 主从复制，它是构建数据库高可用集群架构的基础，我们通过为服务器配置一个或多个备库的方式来进行数据同步，复制功能不仅有利于构建高性能应用，同时也是高可用性、可扩展性、灾难恢复、备份以及数据仓库等工作的基础。复制的基本问题是解决不同服务器的数据保持同步，一台主库的数据可以同步到多台备库上，备库本身也可以被配置为另外一台服务器的主库，主库和备库之间可以有多种不同的组合方式：

单向主从
双向主从

在这里插入图片描述

级联主从

在这里插入图片描述

多主一从

在这里插入图片描述

环形多主

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-y8tDJDYr-1691487017252)(C:\Users\35306\AppData\Roaming\Typora\typora-user-images\image-20230808170237787.png)]$

用途和条件

1.mysql主从复制用途

实时灾备，用于故障切换
读写分离，提供查询服务
备份，避免影响业务

2.主从部署必要条件：

主库开启binlog日志（设置log-bin参数）
主从server-id不同
从库服务器能连通主库

原理

在这里插入图片描述

Mysql的主从复制中主要有三个线程：master（binlog dump thread）、slave（I/O thread 、SQL thread），Master一条线程和Slave中的两条线程。

master（binlog dump thread）主要负责Master库中有数据更新时，将更新的事件类型写入到主库的binlog文件中。

并且，Master会创建log dump线程通知Slave主库中存在数据更新，这就是为什么主库的binlog日志一定要开启的原因。

I/O thread线程在Slave中创建，该线程用于请求Master，Master会返回binlog的名称以及当前数据更新的位置、binlog文件位置的副本。

然后，将binlog保存在 「relay log（中继日志）」 中，中继日志也是记录数据更新的信息。

SQL线程也是在Slave中创建的，当Slave检测到中继日志有更新，就会将更新的内容同步到Slave数据库中，这样就保证了主从的数据的同步。

以上就是主从复制的过程，当然，主从复制的过程有不同的策略方式进行数据的同步，主要包含以下几种：

「同步策略」：Master会等待所有的Slave都回应后才会提交，这个主从的同步的性能会严重的影响。
「半同步策略」：Master至少会等待一个Slave回应后提交。
「异步策略」：Master不用等待Slave回应就可以提交。
「延迟策略」：Slave要落后于Master指定的时间。

对于不同的业务需求，有不同的策略方案，但是一般都会采用最终一致性，不会要求强一致性，毕竟强一致性会严重影响性能。

基于语句复制（STATEMENT）

在 MySQL5.0 以前只支持基于语句的复制。基于语句的复制模式下，主库会记录那些造成数据更改的操作，当备库读取并重放这些操作时，实际上只是把主库上的SQL执行一遍。好处是实现简单，简单的记录并执行这些语句，能让主备保持同步。

但实际上基于语句的复制方式有时会出问题。因为主库上的数据更新除了执行的语句外，可能还依赖于其他因素，例如，同一条 SQL 在主库和备库上的执行时间可能稍有不同，因此在传输带 binlog 中，还包括一些元数据信息，如当前的时间戳，还存在着一些无法被正确复制的 SQL，例如，CURRENT_USER() 函数的语句。存储过程和触发器在使用基于语句的复制模式时也可能存在问题。

基于行复制（ROW）

MySQL5.1开始支持基于行复制，这种方式会将实际的数据记录在 binlog 中，跟其他数据库的实现很像。基于行复制的模式有优点，也有缺陷。好处是可以正确的复制每一行，一些语句可以被更加有效的复制。

也有一些情况，基于行复制的代价会比较大，例如：
update tb_user set age=10;

由于这条 SQL 会更新全表，使用基于行的开销会很大，因为每一行的数据都会记录到 binlog 中，这使得 binlog 文件庞大，并且会给主库增加额外的负载。

混合模式（MIXED）

以上两种模式的混合使用，一般的复制使用 STATEMENT 模式保存 binlog，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog，MySQL 会根据执行的 SQL 语句选择日志保存方式。

因为两种模式各有优缺点以及使用的场合，所以 MySQL 支持在这两种复制模式中动态切换（MIXED模式），MySQL8.0 默认使用基于行复制的方式，理论上基于行的复制模式在整体上更优，且在实际应用中适用于大多数场景。，当然也可以使用参数 binlog_format 手动指定复制的模式。

实战

博主实战环境：

mysql Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1

一主一从

两台服务器分别部署MySQL两台服务器IP地址：

主：10.10.10.128
从：10.10.10.130

master端配置：

1.开启mysql数据库的二进制日志

[root@matser ~]# vim /etc/my.cnf   #只添加server-id和log-bin其它不要更改

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
server-id=128                     #一般指IP地址，不能重复
log-bin=binlog					  #开启二进制日志
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

2.添加slave线程账号

[root@matser ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant replication slave on *.* to "slave"@"%" identified by "1";        #添加账号
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> flush privileges;                                                       #刷新
Query OK, 0 rows affected (0.00 sec)

注：
replication slave：
   拥有此权限可以查看从服务器，从主服务器读取二进制日志，一般只授予这个权限，不会授予更多！
                
　　replication client ： 
   拥有此权限，可以复制客户端数据信息

3.重启数据库

MariaDB [(none)]> exit
Bye
[root@matser ~]# systemctl restart mariadb.service

4.备份主端数据库

[root@matser ~]#  mysqldump -p --all-databases --single-transaction --master-data=1 --flush-logs > /all.sql
Enter password: 
[root@matser ~]# scp /all.sql 10.10.10.130:/							#传输到备端
The authenticity of host '10.10.10.130 (10.10.10.130)' can't be established.
ECDSA key fingerprint is SHA256:zy0aDLr/iy4VZO3pDZkPkEJFnl7u5NqvWXD3pod2uxc.
ECDSA key fingerprint is MD5:f2:8d:f0:e8:64:7c:62:91:b4:09:87:4b:fb:0d:23:6c.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.10.10.130' (ECDSA) to the list of known hosts.
root@10.10.10.130's password: 
all.sql                                                                100%  505KB  36.8MB/s   00:00    

注：
--all-databases        如果要一次备份多个库，添加该参数
--master-data=1        表示在dump过程中记录主库的binlog和pos点，并在dump文件中不注释掉这一行，即恢复时会执行；
--single-transaction   该参数通过在一个事务中导出所有表从而创建一个一致性的快照，当前版本的MySQL只可以对innodb 引擎保证一致性，导出过程中不会锁表其他引擎

slave端配置

1.修改配置文件

1 # vim /etc/my.cnf（在配置文件中添加）
2 server-id=2　　　　　　　　　　　　　　## 必须给予id编号，不可重复，一般是本机的IP地址；

2.进入数据库，将备份数据导入

进入mysql，然后用source /all.sql

mysql> source /all.sql (要注意文件传输后的存放路径)
           ……
           ……
            Query OK, 0 rows affected (0.00 sec)                     (如果出现这样的瀑布流的信息提示，这说明数据导入成功)

            Query OK, 0 rows affected (0.00 sec)

            Query OK, 0 rows affected (0.00 sec)

            Query OK, 0 rows affected (0.00 sec)

           mysql>

3.指定master端

1、mysql> help change master to　　　　　　　　## 查看格式，直接复制粘贴……也可以使用命令行，但是个人更喜欢这样粘贴

CHANGE MASTER TO
  MASTER_HOST='master2.example.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;

2、mysql> \e　　　　　　　　　　　　　　　　　　　　## 开始按照格式，写入Master端的相关信息
CHANGE MASTER TO
MASTER_HOST='10.10.10.128',
MASTER_USER='slave',
MASTER_PASSWORD='1',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000009',
MASTER_LOG_POS=335,
MASTER_CONNECT_RETRY=10

4.查看信息


MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.10.128
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: binlog.000005
          Read_Master_Log_Pos: 335
               Relay_Log_File: mariadb-relay-bin.000009
                Relay_Log_Pos: 616
        Relay_Master_Log_File: binlog.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 335
              Relay_Log_Space: 912
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)
如果看到两个YES，则说明OK ！

Slave_IO_Running: Yes 
Slave_SQL_Running: Yes

如果发现---Slave_IO_Running不为yes
可以依照一下思路拍错——
1、主从配置文件格式，是否正确
2、slave是否创建，权限是否授予
3、网络是否联通
4、防火墙是否放行
5、从端链接主动，语法格式是否正确。
6、slave是否开启

单主多从

上述主从复制已经配置完成，那单主多从该怎么做呢，相信大家已经想到了：只需在一主一从的基础上，再配置一台slave2就可以了，既将上述slave端配置再做一遍即可.

新slaveIP地址：10.10.10.133

同样先在master端，把数据库备份文件scp到新slave端

1.修改配置文件

1 # vim /etc/my.cnf（在配置文件中添加）
2 server-id=3　　　　　　　　　　　　　## 必须给予id编号，不可重复，一般是本机的IP地址；

2.进入数据库，将备份数据导入

进入mysql，然后用source /all.sql

mysql> source /all.sql (要注意文件传输后的存放路径)
           ……
           ……
            Query OK, 0 rows affected (0.00 sec)                     (如果出现这样的瀑布流的信息提示，这说明数据导入成功)

            Query OK, 0 rows affected (0.00 sec)

            Query OK, 0 rows affected (0.00 sec)

            Query OK, 0 rows affected (0.00 sec)

           mysql>

3.指定master端

1、mysql> help change master to　　　　　　　　## 查看格式，直接复制粘贴……也可以使用命令行，但是个人更喜欢这样粘贴

CHANGE MASTER TO
  MASTER_HOST='master2.example.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;

2、mysql> \e　　　　　　　　　　　　　　　　　　　　## 开始按照格式，写入Master端的相关信息
CHANGE MASTER TO
MASTER_HOST='10.10.10.128',
MASTER_USER='slave',
MASTER_PASSWORD='1',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000009',
MASTER_LOG_POS=335,
MASTER_CONNECT_RETRY=10

4.查看信息


MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.10.128
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: binlog.000005
          Read_Master_Log_Pos: 335
               Relay_Log_File: mariadb-relay-bin.000009
                Relay_Log_Pos: 616
        Relay_Master_Log_File: binlog.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 335
              Relay_Log_Space: 912
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)
如果看到两个YES，则说明OK ！

Slave_IO_Running: Yes 
Slave_SQL_Running: Yes

环形多主

下面到了稍微烧脑的环形了，初看感觉很简单，其实真正上手做了才会发现会遇到很多问题，比如我，刚开始单纯认为，三台机器之间每两台之间互相主从不就好了吗。后面发现失败了，一是细节没有注意，有些配置文件需要写入，跳过错误日志 更新新的二进制文件 ，之前想的不够简练，直接：

A为B的主，B为C的主，C为A的主就可以了，然后在配置文件里加入

log-slave-updates
slave-skip-errors=all

原理：

MySQL的Replication是一种多个MySQL的数据库做主从同步的方案，特点是异步，广泛用在各种对MySQL有更高性能，更高可靠性要求的场合。与之对应的另一个技术是同步的MySQL Cluster，但因为比较复杂，使用者较少。

下面是MySQL官方给出了使用Replication的场景：

Replication原理

Mysql的 Replication 是一个异步的复制过程，从一个MySQL节点（称之为Master）复制到另一个MySQL节点（称之Slave）。在Master 与 Slave 之间的实现整个复制过程主要由三个线程来完成，其中两个线程（SQL 线程和 I/O 线程）在 Slave 端，另外一个线（I/O 线程）在 Master 端。

要实现 MySQL 的 Replication ，首先必须打开 Master 端的 Binary Log，因为整个复制过程实际上就是 Slave 从 Master 端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。

看上去MySQL的Replication原理非常简单，总结一下：

每个从仅可以设置一个主。
主在执行sql之后，记录二进制log文件（bin-log）。
从连接主，并从主获取binlog，存于本地relay-log，并从上次记住的位置起执行sql，一旦遇到错误则停止同步。

从这几条Replication原理来看，可以有这些推论：
* 主从间的数据库不是实时同步，就算网络连接正常，也存在瞬间，主从数据不一致。
* 如果主从的网络断开，从会在网络正常后，批量同步。

如果对从进行修改数据，那么很可能从在执行主的bin-log时出现错误而停止同步，这个是很危险的操作。所以一般情况下，非常小心的修改从上的数据。
一个衍生的配置是双主，互为主从配置，只要双方的修改不冲突，可以工作良好。

如果需要多主的话，可以用环形配置，这样任意一个节点的修改都可以同步到所有节点

下面开始我的方法，配置三台mysql数据库的环形多主 ：

首先三台服务器按上述的主从配置为：A为B的主，B为C的主，C为A的主。我相信大家都没问题

下面只展示我的三台服务器配置完成后的效果：

MariaDB [(none)]> show slave status\Gq
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.10.131
                  Master_User: slave3
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: binlog.000006
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000011
                Relay_Log_Pos: 526
        Relay_Master_Log_File: binlog.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 245
              Relay_Log_Space: 1103
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 3

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.10.128
                  Master_User: slave1
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: binlog.000005
          Read_Master_Log_Pos: 335
               Relay_Log_File: mariadb-relay-bin.000009
                Relay_Log_Pos: 616
        Relay_Master_Log_File: binlog.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 335
              Relay_Log_Space: 912
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.10.130
                  Master_User: slave2
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: binlog.000006
          Read_Master_Log_Pos: 335
               Relay_Log_File: mariadb-relay-bin.000012
                Relay_Log_Pos: 616
        Relay_Master_Log_File: binlog.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 335
              Relay_Log_Space: 912
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
1 row in set (0.00 sec)

之后在配置文件里加入两行

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
server-id=2
log-bin=binlog
auto_increment_offset=2
auto_increment_increment=3
log-slave-updates                                    <---------------- 加入这两行
slave-skip-errors=all                                <----------------
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d