[系统运维]关于Linux下MySql集群同步(主从、一主多从、主从从)部署及同步策略的一些笔记

写在前面

• 和小伙们分享一些Mysql集群主从同步相关的笔记
• 博文内容涉及：
  • 为什么需要mysql集群主从同步
  • 主从同步原理
  • 部署不同主从结构的Mysql集群
  • 同步使用的复制模式介绍配置
• 理解不足小伙伴帮忙指正

傍晚时分，你坐在屋檐下，看着天慢慢地黑下去，心里寂寞而凄凉，感到自己的生命被剥夺了。当时我是个年轻人，但我害怕这样生活下去，衰老下去。在我看来，这是比死亡更可怕的事。--------王小波

一些名词：

• Mysql和MariaDB在5.7之前的版本是兼容的，当前博文部署使用的是 MariaDB 5.5的版本，但是并不影响
• Mysql 集群分为 主库(master)和需要复制的 备库（replica）或者称为从库（slave）。
  • 主库（master）: 接受客户端访问连接
  • 从库（slave)：自动同步主服务器数据

一、为什么需要Mysql的主从复制

对于构建基于MySQL的大规模、高性能应用来讲，需要使用水平扩展(集群)的数据库架构方式。在MySQL内建的复制功能可以实现，通过为服务器配置一个或多个备库的方式来进行数据同步。

同时复制功能不仅有利于构建高性能的应用，也是高可用性、可扩展性、容灾、备份以及数据仓库等工作的基础。

复制解决的基本原理是让一台服务器的数据与其他服务器保持同步。一台主库的数据可以同步到多台备库上，备库本身也可以被配置成另外一台服务器的主库。主库和备库之间可以有多种不同的组合方式。

复制解决的问题

数据分布

MySQL复制通常不会对带宽造成很大的压力，但在5.1版本引入的基于行的复制会比传统的基于语句的复制模式的带宽压力更大。通过复制可以实现在不同的地理位置来分布数据备份，例如不同的数据中心。即使在不稳定的网络环境下，远程复制也可以工作。

负载均衡

通过MySQL复制可以将读操作分布到多个服务器上，实现对读密集型应用的优化，并且实现很方便，通过简单的代码修改就能实现基本的负载均衡。

对于小规模的应用，可以简单地对机器名做硬编码或使用DNS轮询（将一个机器名指向多个IP地址）。当然也可以使用更复杂的方法，例如网络负载均衡这一类的标准负载均衡解决方案，能够很好地将负载分配到不同的MySQL服务器上。Linux虚拟服务器（LinuxVirtual Server，LVS）也能够很好地工作。

备份

对于备份来说，复制是一项很有意义的技术补充，但复制既不是备份也不能够取代

高可用性和故障切换

复制能够帮助应用程序避免MySQL单点失败，一个包含复制的设计良好的故障切换系统能够显著地缩短宕机时间。

MySQL升级测试

这种做法比较普遍，使用一个更高版本的MySQL作为备库，保证在升级全部实例前，查询能够在备库按照预期执行。

复制方式

MySQL 支持两种复制方式：基于行的复制和基于语句的复制。

基于语句的复制（也称为逻辑复制）早在MySQL3.23版本中就存在，而基于行的复制方式在5.1版本中才被加进来。

这两种方式都是通过在主库上记录二进制日志、在备库重放日志的方式来实现异步的数据复制。

这意味着，在同一时间点备库上的数据可能与主库存在不一致，并且无法保证主备之间的延迟。一些大的语句可能导致备库产生几秒、几分钟甚至几个小时的延迟。

复制通常不会增加主库的开销，主要是启用二进制日志带来的开销，但出于备份或及时从崩溃中恢复的目的，这点开销也是必要的。除此之外，每个备库也会对主库增加一些负载（例如网络I/O开销），尤其当备库请求从主库读取旧的二进制日志文件时，可能会造成更高的I/O开销。另外锁竞争也可能阻碍事务的提交。最后，如果是从一个高吞吐量（例如5000或更高的TPS）的主库上复制到多个备库，唤醒多个复制线程发送事件的开销将会累加。

二、主从同步原理

MySQL实际上是如何复制数据的。总的来说，复制有三个步骤：

1. 在主库上开启记录二进制日志。在每次准备提交事务完成数据更新前，主库将数据更新的事件记录到二进制日志中。MySQL会按事务提交的顺序而非每条语句的执行顺序来记录二进制日志。在记录二进制日志后，主库会告诉存储引擎可以提交事务了。
2. 备库将主库的二进制日志复制到其本地的中继日志中。首先，备库会启动一个工作线程，称为I/O线程，I/O线程跟主库建立一个普通的客户端连接，然后在主库上启动一个特殊的二进制转储（binlog dump）线程（该线程没有对应的SQL命令），这个二进制转储线程会读取主库上二进制日志中的事件。它不会对事件进行轮询。如果该线程追赶上了主库，它将进入睡眠状态，直到主库发送信号量通知其有新的事件产生时才会被唤醒，备库I/0线程会将接收到的事件记录到中继日志中。
3. 备库的SQL线程执行最后一步，该线程从中继日志中读取事件并在备库执行，从而实现备库数据的更新。当SQL线程追赶上I/O线程时，中继日志通常已经在系统缓存中，所以中继日志的开销很低。SQL线程执行的事件也可以通过配置选项来决定是否写入其自己的二进制日志中，它对于我们稍后提到的场景非常有用。

在这个过程中，涉及两个角色：

• Master角色
  • 启用 binlog 日志：开启 binlog 日志，记录所有除查询以外的 SQL 命令
• Slave角色：
  • Slave_IO: 复制 master 主机 binlog 日志文件里的 SQL 命令到本机的 relay-log（中继日志） 文件里。从服务器上的 I/O thread（读写线程） 负责读取主服务器 binlog 日志中的 SQL 命令，并将其写入到 Relay log（中继日志中）；
  • Slave_SQL: 执行本机 relay-log（中继日志） 文件里的 SQL 语句，实现与 Master 数据一致。从服务器中的 SQL thread(SQL 线程）读取中继日志中的 SQL 命令，并将其写入到 Slave 的数据库中；

主从同步结构模式

主从的复制的结果模式设置需要注意几点:

• 一个MySQL备库实例只能有一个主库。
• 每个备库必须有一个唯一的服务器ID。
• 一个主库可以有多个备库（或者相应的，一个备库可以有多个兄弟备库）。
• 如果打开了log_slave_updates选项，一个备库可以把其主库上的数据变化传播到其他备库。

常见的结构模式：

• 单向复制：一主一从
• 一主多从：从 <—— 主 ——> 从,即一个主节点，多个从节点
• 链式复制：主 <—— 从<—— 从：即链式复制，第一个位节点，最后一个为从节点，中间的为主从节点
• 互为主从：主 <——> 主：也叫双主复制或者双向复制。需要解决冲突问题。

今天和小伙伴们分享的主要是前三，基本的配置步骤：

1. 在主库创建复制账号。
2. 配置主库和备库。
3. 通知备库连接到主库并从主库复制数据。

三、MySQL 主从同步部署配置

配置MySQL一主一从

• 主服务器：192.168.26.153
• 从服务器：192.168.26.154
• 客户端：192.168.26.152

环境安装，为了方便部分地方使用了简单ansible操作。

清单文件

┌──[root@vms152.liruilongs.github.io]-[~]
└─$cat inventory
[master]
192.168.26.152
[node]
192.168.26.153
192.168.26.154
[web]
192.168.26.155
192.168.26.156
[db_node]
192.168.26.153
192.168.26.154
192.168.26.155
┌──[root@vms152.liruilongs.github.io]-[~]
└─$

安装数据库

┌──[root@vms152.liruilongs.github.io]-[~]
└─$ansible node -m yum -a 'name=mariadb,mariadb-server state=installed'

配置主服务器

主库在配置文件添加服务器id，启用binlog日志，然后重启服务

┌──[root@vms152.liruilongs.github.io]-[~]
└─$ansible 192.168.26.153 -m shell -a "sed '/\[mysqld\]/a server_id=153\nlog_bin=master153' /etc/my.cnf -i"
┌──[root@vms152.liruilongs.github.io]-[~]
└─$ansible 192.168.26.153 -m service -a 'name=mariadb state=restarted'

数据库初始化操作,安装数据库需要操作

┌──[root@vms153.liruilongs.github.io]-[~]
└─$mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!

，。。。

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
┌──[root@vms153.liruilongs.github.io]-[~]
└─$

查看主库配置文件，设置相关字符集

┌──[root@vms153.liruilongs.github.io]-[~]
└─$cat /etc/my.cnf
[mysqld]
server_id=153
log_bin=master153
# 字符集
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

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

[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

┌──[root@vms153.liruilongs.github.io]-[~]
└─$cat /etc/my.cnf.d/client.cnf
#
# These two groups are read by the client library
# Use it for options that affect all clients, but not the server
#


[client]

# This group is not read by mysql client library,
# If you use the same .cnf file for MySQL and MariaDB,
# use it for MariaDB-only client options
[client-mariadb]

其他配置文件字符编码设置

┌──[root@vms153.liruilongs.github.io]-[~]
└─$sed '/\[client\]/a default-character-set=utf8' /etc/my.cnf.d/client.cnf -i

┌──[root@vms153.liruilongs.github.io]-[~]
└─$cat /etc/my.cnf.d/mysql-clients.cnf
#
# These groups are read by MariaDB command-line tools
# Use it for options that affect only one utility
#

[mysql]

[mysql_upgrade]

[mysqladmin]

[mysqlbinlog]

[mysqlcheck]

[mysqldump]

[mysqlimport]

[mysqlshow]

[mysqlslap]

┌──[root@vms153.liruilongs.github.io]-[~]
└─$sed -i '/\[mysql\]/a default-character-set=utf8' /etc/my.cnf.d/mysql-clients.cnf

┌──[root@vms153.liruilongs.github.io]-[~]
└─$mysql -uroot -pliruilong
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
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)]> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

MariaDB [(none)]>

给从服务器授权 replication slave，授权用户为: repluser，查看主库 binlog日志信息

MariaDB [(none)]> grant replication slave on *.* to repluser@"%" identified by "repluser";
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select user,host from mysql.user
    -> ; ###在mysql库下的user表中查看用户的授权信息
+----------+-----------------------------+
| user     | host                        |
+----------+-----------------------------+
| repluser | %                           |
| root     | 127.0.0.1                   |
| root     | ::1                         |
| root     | localhost                   |
| root     | vms153.liruilongs.github.io |
+----------+-----------------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> show master status
    -> ; ###查看binlog日志的状态信息
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| master153.000004 |      391 |              |                  |
+------------------+----------+--------------+------------------+

配置从服务器：

指定 server_id

┌──[root@vms154.liruilongs.github.io]-[~]
└─$cat /etc/my.cnf
[mysqld]
server_id=154
# 字符集
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

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

[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

指定主服务器信息

┌──[root@vms154.liruilongs.github.io]-[/var/lib/mysql]
└─$mysql -uroot -pliruilong
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
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.
####指定主服务器信息
#master_host=       指定主服务器的IP地址
#master_user=       指定主服务器授权用户 
#master_password=   指定授权用户的密码
#master_log_file=   指定主服务器binlog日志文件(去153上查看)
#master_log_pos=    指定主服务器binlog日志偏移量(去153上查看)
MariaDB [(none)]>  change master to
    -> master_host="192.168.26.153",
    -> master_user="repluser",
    -> master_password="repluser",
    -> master_log_file="master153.000004",
    -> master_log_pos=391;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]>

启动slave进程，查看slave状态

┌──[root@vms154.liruilongs.github.io]-[/var/lib/mysql]
└─$systemctl restart  mariadb.service
┌──[root@vms154.liruilongs.github.io]-[/var/lib/mysql]
└─$mysql -uroot -pliruilong
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
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)]> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.26.153  ##主服务器IP地址
                  Master_User: repluser         #主服务器授权用户
                  Master_Port: 3306            #主服务器端口号
                Connect_Retry: 60
              Master_Log_File: master153.000004   #主服务器端binlog日志
          Read_Master_Log_Pos: 391       #主服务器端binlog日志偏移量
               Relay_Log_File: mariadb-relay-bin.000003
                Relay_Log_Pos: 529
        Relay_Master_Log_File: master153.000004
             Slave_IO_Running: Yes  #IO线程运行
            Slave_SQL_Running: Yes  #SQL线程运行
              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: 391
              Relay_Log_Space: 825
              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   #IO线程报错信息提示
                Last_IO_Error:
               Last_SQL_Errno: 0   #SQL线程报错信息提示
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 153
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]>

测试主从同步

主库添加数据

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| master153.000004 |      391 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> create database liruilong_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use liruilong_db;create table liruilong_db.user(id int(10),create_date datetime);
Database changed
Query OK, 0 rows affected (0.00 sec)

MariaDB [liruilong_db]> insert into user values(1,now());
Query OK, 1 row affected (0.00 sec)

MariaDB [liruilong_db]> select * from user;
+------+---------------------+
| id   | create_date         |
+------+---------------------+
|    1 | 2022-09-29 00:22:36 |
+------+---------------------+
1 row in set (0.00 sec)

MariaDB [liruilong_db]>

从库查看

┌──[root@vms154.liruilongs.github.io]-[/var/lib/mysql]
└─$mysql -uroot -pliruilong
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)]> select * from liruilong_db.user;
+------+---------------------+
| id   | create_date         |
+------+---------------------+
|    1 | 2022-09-29 00:22:36 |
+------+---------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

从库相关数据文件

存放在数据库目录下；删除文件，重启数据库服务，可把主机恢复为独立的数据库服务器

┌──[root@vms154.liruilongs.github.io]-[/var/lib/mysql]
└─$ls
aria_log.00000001  ib_logfile0   mariadb-relay-bin.000002  master.info  performance_schema
aria_log_control   ib_logfile1   mariadb-relay-bin.000003  mysql        relay-log.info
ibdata1            liruilong_db  mariadb-relay-bin.index   mysql.sock   test

• master.info ：主库信息

┌──[root@vms154.liruilongs.github.io]-[/var/lib/mysql]
└─$cat /var/lib/mysql/master.info
18
master153.000004
854
192.168.26.153
repluser
repluser
3306
60
.......

查看中继日志信息,mariadb-relay-bin.index是中继日志索引文件,mariadb-relay-bin.00000*是中继日志文件：记录从主服务器拷贝过来的sql命令

┌──[root@vms154.liruilongs.github.io]-[/var/lib/mysql]
└─$ls | grep mariadb-relay-bin
mariadb-relay-bin.000002
mariadb-relay-bin.000003
mariadb-relay-bin.index
┌──[root@vms154.liruilongs.github.io]-[/var/lib/mysql]
└─$cat mariadb-relay-bin.index
./mariadb-relay-bin.000002
./mariadb-relay-bin.000003

中继读写信息relay-log.info

┌──[root@vms154.liruilongs.github.io]-[/var/lib/mysql]
└─$cat relay-log.info
./mariadb-relay-bin.000003  ##本机正在使用的中继日志文jian
992               #中继日志记录主服务器sql命令的偏移量
master153.000004  #继日志从哪个文件中拷贝sql命令(主服务器
854  #此为主服务器最近的binlog日志的偏移量
┌──[root@vms154.liruilongs.github.io]-[/var/lib/mysql]
└─$

配置MySQL一主多从

我们在一主一从的基础上配置一主多从

• 主服务器：192.168.26.153
• 从服务器：192.168.26.154
• 从服务器：192.168.26.155
• 客户端：192.168.26.152

主服务器安装innobackupex相关软件包，做备份，然后把备份复制的新的从服务器

备份主库数据

┌──[root@vms153.liruilongs.github.io]-[/var/lib/mysql]
└─$yum -y install percona-xtrabackup

使用 innobackupex 命令备份

####完全备份：备份所有库和所有表
#--user           指定数据库的用户名: root
#--password       指定数据库的密码:   liruilong
#/alldb          指定备份数据存放的目录，不需要提前创建，会自动创建
#--no-timestamp   指定不用日期时间作为存储数据的子目录名称
#--slave-info     指备份数据时,记录sql命令的偏移量和binlog日志文件名，便于从服务器去读取
┌──[root@vms153.liruilongs.github.io]-[/var/lib/mysql]
└─$innobackupex --user root --password 'liruilong' --slave-info /alldb --no-timestamp
220929 00:53:41 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at - line 693.
BEGIN failed--compilation aborted at - line 693.
220929 00:53:41 Connecting to MySQL server host: localhost, user: root, password: set, port: 0, socket: /var/lib/mysql/mysql.sock
Using server version 5.5.68-MariaDB
innobackupex version 2.3.6 based on MySQL server 5.6.24 Linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
.......

220929 00:53:43 Executing UNLOCK TABLES
220929 00:53:43 All tables unlocked
220929 00:53:43 Backup created in directory '/alldb'
MySQL binlog position: filename 'master153.000004', position '854'
220929 00:53:43 [00] Writing backup-my.cnf
220929 00:53:43 [00]        ...done
220929 00:53:43 [00] Writing xtrabackup_info
220929 00:53:43 [00]        ...done
xtrabackup: Transaction log of lsn (1600828) to (1600828) was copied.
220929 00:53:43 completed OK!
┌──[root@vms153.liruilongs.github.io]-[/var/lib/mysql]
└─$

复制文件到新的从库，这里从库的安装略去，按照前面的方式即可

┌──[root@vms153.liruilongs.github.io]-[/]
└─$scp -r /alldb/ root@192.168.26.155:/opt/
root@192.168.26.155's password:
xtrabackup_logfile
。。。。

在从库按照备份恢复相关软件包

┌──[root@vms155.liruilongs.github.io]-[~]
└─$yum -y install percona-xtrabackup

查看拷贝过来的文件数据

┌──[root@vms155.liruilongs.github.io]-[/opt]
└─$cd alldb/
┌──[root@vms155.liruilongs.github.io]-[/opt/alldb]
└─$ls
backup-my.cnf  liruilong_db  performance_schema  xtrabackup_binlog_info  xtrabackup_info
ibdata1        mysql         test                xtrabackup_checkpoints  xtrabackup_logfile
┌──[root@vms155.liruilongs.github.io]-[/opt/alldb]
└─$cd ~

xtrabackup_binlog_info 文件记录的是binlog日志文件名和偏移量,此偏移量和主服务器的偏移量一致，从服务器同步数据时从这个偏移量开始同步

┌──[root@vms155.liruilongs.github.io]-[~]
└─$cat /opt/alldb/xtrabackup_binlog_info
master153.000004        854

新从库通过备份数据恢复数据

停调新的从库服务

┌──[root@vms155.liruilongs.github.io]-[~]
└─$systemctl stop mariadb
┌──[root@vms155.liruilongs.github.io]-[~]
└─$systemctl status mariadb
● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
   Active: inactive (dead)

删除新从库数据文件，读书备份数据的备份范围

┌──[root@vms155.liruilongs.github.io]-[~]
└─$rm -rf /var/lib/mysql/*
┌──[root@vms155.liruilongs.github.io]-[~]
└─$innobackupex --apply-log /opt/alldb/
220929 10:45:50 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

innobackupex version 2.3.6 based on MySQL server 5.6.24 Linux (x86_64) (revision id: )
xtrabackup: cd to /opt/alldb/
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1600828)
。。。。。。。。
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1601046
220929 10:45:53 completed OK!

拷贝备份数据到从库数据目录下

┌──[root@vms155.liruilongs.github.io]-[~]
└─$innobackupex --copy-back /opt/alldb/
220929 10:46:23 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".

innobackupex version 2.3.6 based on MySQL server 5.6.24 Linux (x86_64) (revision id: )
220929 10:46:23 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
220929 10:46:23 [01]        ...done
220929 10:46:23 [01] Copying ib_logfile1 to /var/lib/mysql/ib_logfile1
220929 10:46:23 [01]        ...done
...........................
220929 10:46:24 [01] Copying ./xtrabackup_binlog_pos_innodb to /var/lib/mysql/xtrabackup_binlog_pos_innodb
220929 10:46:24 [01]        ...done
220929 10:46:24 completed OK!
┌──[root@vms155.liruilongs.github.io]-[~]
└─$

查看数据是否拷贝成功，修改/var/lib/mysql 下所有文件的属性,重启服务

┌──[root@vms155.liruilongs.github.io]-[~]
└─$ls /var/lib/mysql/
ibdata1  ib_logfile0  ib_logfile1  liruilong_db  mysql  performance_schema  test  xtrabackup_binlog_pos_innodb  xtrabackup_info
┌──[root@vms155.liruilongs.github.io]-[~]
└─$chown -R mysql.mysql /var/lib/mysql
┌──[root@vms155.liruilongs.github.io]-[~]
└─$systemctl start mariadb.service

查看数据是否恢复成功

┌──[root@vms155.liruilongs.github.io]-[~]
└─$mysql -uroot -pliruilong -e'select * from liruilong_db.user'
+------+---------------------+
| id   | create_date         |
+------+---------------------+
|    1 | 2022-09-29 00:22:36 |
+------+---------------------+
┌──[root@vms155.liruilongs.github.io]-[~]
└─$

新从库配置

修改从库配置文件

┌──[root@vms155.liruilongs.github.io]-[~]
└─$vim /etc/my.cnf
┌──[root@vms155.liruilongs.github.io]-[~]
└─$cat /etc/my.cnf
[mysqld]
server_id=155
# 字符集
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

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

[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
┌──[root@vms155.liruilongs.github.io]-[~]
└─$sed '/\[client\]/a default-character-set=utf8' /etc/my.cnf.d/client.cnf -i
┌──[root@vms155.liruilongs.github.io]-[~]
└─$sed -i '/\[mysql\]/a default-character-set=utf8' /etc/my.cnf.d/mysql-clients.cnf
┌──[root@vms155.liruilongs.github.io]-[~]
└─$systemctl restart mariadb.service

查看主库的二进制文件偏移量

┌──[root@vms153.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
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)]> show master status
    -> ;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| master153.000004 |      854 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

指定主服务器信息

┌──[root@vms155.liruilongs.github.io]-[~]
└─$mysql -uroot -pliruilong
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
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)]> change master to
    -> master_host="192.168.26.153",
    -> master_user="repluser",
    -> master_password="repluser",
    -> master_log_file="master153.000004",
    -> master_log_pos="854";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"854"' at line 6
MariaDB [(none)]> change master to master_host="192.168.26.153", master_user="repluser", master_password="repluser", master_log_file="master153.000004", master_log_pos=854;
Query OK, 0 rows affected (0.04 sec)

MariaDB [(none)]>

查看从库状态信息

MariaDB [(none)]> stop slave
    -> ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.26.153
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master153.000004
          Read_Master_Log_Pos: 854
               Relay_Log_File: mariadb-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master153.000004
             Slave_IO_Running: No
            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: 854
              Relay_Log_Space: 245
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1593
                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 153
1 row in set (0.00 sec)

ERROR: No query specified

我们可以看到有一个报错，说从库的Server_id设置的不对

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

重新查看155配置文件，确实有问题，所以这里修改ServerID

┌──[root@vms155.liruilongs.github.io]-[~]
└─$cat /etc/my.cnf
[mysqld]
server_id=153
# 字符集
。。。。。
!includedir /etc/my.cnf.d
┌──[root@vms155.liruilongs.github.io]-[~]
└─$vim /etc/my.cnf

之前配置文件我们直接拷贝的主库的配置，忘记修改serverID，修改后重新启动服务查看

┌──[root@vms155.liruilongs.github.io]-[~]
└─$systemctl restart mariadb.service
┌──[root@vms155.liruilongs.github.io]-[~]
└─$mysql -uroot -pliruilong -e'show slave status\G'
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.26.153
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master153.000004
          Read_Master_Log_Pos: 854
               Relay_Log_File: mariadb-relay-bin.000004
                Relay_Log_Pos: 529
        Relay_Master_Log_File: master153.000004
             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: 854
              Relay_Log_Space: 825
              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: 153
┌──[root@vms155.liruilongs.github.io]-[~]
└─$

测试一主多从

主库添加数据

┌──[root@vms153.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
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)]> insert into liruilong_db.user values(1,now());
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> select * from liruilong_db.user;
+------+---------------------+
| id   | create_date         |
+------+---------------------+
|    1 | 2022-09-29 00:22:36 |
|    1 | 2022-09-29 11:08:38 |
+------+---------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]>

查看从库155的数据是否同步

┌──[root@vms155.liruilongs.github.io]-[~]
└─$mysql -uroot -pliruilong -e' select * from liruilong_db.user'
+------+---------------------+
| id   | create_date         |
+------+---------------------+
|    1 | 2022-09-29 00:22:36 |
|    1 | 2022-09-29 11:08:38 |
+------+---------------------+
┌──[root@vms155.liruilongs.github.io]-[~]
└─$

查看从库154的数据是否同步

┌──[root@vms154.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e' select * from liruilong_db.user'
+------+---------------------+
| id   | create_date         |
+------+---------------------+
|    1 | 2022-09-29 00:22:36 |
|    1 | 2022-09-29 11:08:38 |
+------+---------------------+
┌──[root@vms154.liruilongs.github.io]-[/]
└─$

配置 MySQL 主从从

这里只是主观上的高可用，需要手动的切换IP，并不是实际上的高可用，实际的高可用实现需要借助一些其他工具

• 主服务器：192.168.26.153
• 从服务器：192.168.26.154
• 从服务器：192.168.26.155
• 客户端：192.168.26.152

主从从结构优势：

• 192.168.26.155是192.168.26.154的从服务器，192.168.26.154是192.168.26.153的从服务器；
• 当192.168.26.153宕机以后，用户可以访问从服务器192.168.26.154的数据库；
• 当192.168.26.154宕机以后，用户可以访问从服务器192.168.26.155的数据库；

篇幅有限，我们这上面一主多从的基础上修改复制模式为主从从

主从库配置

修改192.168.26.154主配置文件，log_slave_updates 开启级联复制功能，因为154同步数据是从153的binlog 日志中获取的，154并没有直接执行sql命令，所以在154 的binlog日志中并没有sql命令，那么155也就无法同步154中的数据；而开启级联复制功能，则允许155同步154从153同步过来的数据

同时需要开启binlog日志

┌──[root@vms154.liruilongs.github.io]-[/]
└─$vim /etc/my.cnf
┌──[root@vms154.liruilongs.github.io]-[/]
└─$┌──[root@vms154.liruilongs.github.io]-[/]
└─$cat /etc/my.cnf
[mysqld]
server_id=154
log_bin=master154
log_slave_updates
# 字符集
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

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

[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
┌──[root@vms154.liruilongs.github.io]-[/]
└─$systemctl restart mariadb.service
┌──[root@vms154.liruilongs.github.io]-[/]
└─$

重启服务后查看从库状态

┌──[root@vms154.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e' show slave status\G'
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.26.153
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master153.000006
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000009
                Relay_Log_Pos: 529
        Relay_Master_Log_File: master153.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: 825
              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: 153
┌──[root@vms154.liruilongs.github.io]-[/]
└─$

当153宕机后，154从库也作为主库，查看主库状态

┌──[root@vms154.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong
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 tom@"%" identified by "liruilong";
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| master154.000001 |      387 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

从库配置

修改155从库配置，由153修改为154，删除数据库相关数据，把155做做成独立库

┌──[root@vms155.liruilongs.github.io]-[~]
└─$cd /var/lib/mysql/
┌──[root@vms155.liruilongs.github.io]-[/var/lib/mysql]
└─$ls
aria_log.00000001  ib_logfile1               mariadb-relay-bin.index  master155.index  performance_schema            xtrabackup_info
aria_log_control   liruilong_db              master155.000001         master.info      relay-log.info
ibdata1            mariadb-relay-bin.000003  master155.000002         mysql            test
ib_logfile0        mariadb-relay-bin.000004  master155.000003         mysql.sock       xtrabackup_binlog_pos_innodb
┌──[root@vms155.liruilongs.github.io]-[/var/lib/mysql]
└─$rm -rf master.info
┌──[root@vms155.liruilongs.github.io]-[/var/lib/mysql]
└─$rm -rf mariadb-relay-bin.00000*
┌──[root@vms155.liruilongs.github.io]-[/var/lib/mysql]
└─$rm -rf mariadb-relay-bin.index
┌──[root@vms155.liruilongs.github.io]-[/var/lib/mysql]
└─$rm -rf relay-log.info
┌──[root@vms155.liruilongs.github.io]-[/var/lib/mysql]
└─$ls
aria_log.00000001  ib_logfile0   master155.000001  master155.index  performance_schema            xtrabackup_info
aria_log_control   ib_logfile1   master155.000002  mysql            test
ibdata1            liruilong_db  master155.000003  mysql.sock       xtrabackup_binlog_pos_innodb
┌──[root@vms155.liruilongs.github.io]-[/var/lib/mysql]
└─$

重启服务后，重新指定同步主库的相关配置

┌──[root@vms155.liruilongs.github.io]-[/var/lib/mysql]
└─$systemctl restart mariadb.service
┌──[root@vms155.liruilongs.github.io]-[/var/lib/mysql]
└─$mysql -uroot -pliruilong -e 'show slave status;'
┌──[root@vms155.liruilongs.github.io]-[/var/lib/mysql]
└─$mysql -uroot -pliruilong
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
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)]> change master to
    -> master_host="192.168.26.154",
    -> master_user="tom",
    -> master_password="liruilong",
    -> master_log_file="master154.000001",
    -> master_log_pos=387;
Query OK, 0 rows affected (0.01 sec)

查看155从库同步状态

MariaDB [(none)]> start slave
    -> ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.26.154
                  Master_User: tom
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master154.000001
          Read_Master_Log_Pos: 387
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: master154.000001
             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: 387
              Relay_Log_Space: 825
              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: 154
1 row in set (0.00 sec)

MariaDB [(none)]>

主从从同步测试

153主库新增数据

┌──[root@vms153.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e' insert into liruilong_db.user values(1,now());'
┌──[root@vms153.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'select * from liruilong_db.user'
+------+---------------------+
| id   | create_date         |
+------+---------------------+
|    1 | 2022-09-29 00:22:36 |
|    1 | 2022-09-29 11:08:38 |
|    1 | 2022-09-29 13:43:09 |
|    1 | 2022-09-29 13:51:33 |
|    1 | 2022-09-29 13:54:41 |
+------+---------------------+
┌──[root@vms153.liruilongs.github.io]-[/]
└─$

154主从库查看

┌──[root@vms154.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'select * from liruilong_db.user'
+------+---------------------+
| id   | create_date         |
+------+---------------------+
|    1 | 2022-09-29 00:22:36 |
|    1 | 2022-09-29 11:08:38 |
|    1 | 2022-09-29 13:43:09 |
|    1 | 2022-09-29 13:51:33 |
|    1 | 2022-09-29 13:54:41 |
+------+---------------------+
┌──[root@vms154.liruilongs.github.io]-[/]
└─$

155从库查看

┌──[root@vms155.liruilongs.github.io]-[/var/lib/mysql]
└─$mysql -uroot -pliruilong -e'select * from liruilong_db.user'
+------+---------------------+
| id   | create_date         |
+------+---------------------+
|    1 | 2022-09-29 13:43:09 |
|    1 | 2022-09-29 13:51:33 |
|    1 | 2022-09-29 13:54:41 |
+------+---------------------+
┌──[root@vms155.liruilongs.github.io]-[/var/lib/mysql]
└─$

四、复制模式

异步复制 (默认的复制模式)

Asynchronous replication:主服务器执行完一次事务后，立即将结果返给客户端，不关心从服务器是否已经同步数据。

• 优点：响应速度快，用户体验很好；
• 缺点：主服务器宕机后，有可能会存在从服务器数据丢失的情况；

半同步复制

Semisynchronous replication:主服务器在执行完一次事务后，等待至少一台从服务器同步数据完成，才将结果返回给客户端。

• 优点：主服务器宕机后，至少有一台从服务器拥有和主服务器相同的数据，数据安全度高；
• 缺点：响应速度下降，用户体验度下降；

关于半同步，有一些普遍的误解，下面是它不会去做的：

• 在备库提示其已经收到事件前，会阻塞主库上的事务提交。事实上在主库上已经完成事务提交，只有通知客户端被延迟了。
• 直到备库执行完事务后，才不会阻塞客户端。备库在接收到事务后发送反馈而非完成事务后发送。
• 半同步不总是能够工作。如果备库一直没有回应已收到事件，会超时并转化为正常的异步复制模式。

配置半同步复制

临时配置

马上生效，重启服务后失效

加载模块命令行配置

mysq1>install plugin rpl semi sync_master SONAME "semisync_master.so";  //加载master模块
mysq1>install plugin rpl_semi_sync_slave  SONAME "semisync_slave.so";   //加载slave模块

插件表的字段查看 information_schema.PLUGINS

┌──[root@vms153.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'desc information_schema.PLUGINS'
+------------------------+-------------+------+-----+---------+-------+
| Field                  | Type        | Null | Key | Default | Extra |
+------------------------+-------------+------+-----+---------+-------+
| PLUGIN_NAME            | varchar(64) | NO   |     |         |       |
| PLUGIN_VERSION         | varchar(20) | NO   |     |         |       |
| PLUGIN_STATUS          | varchar(10) | NO   |     |         |       |
| PLUGIN_TYPE            | varchar(80) | NO   |     |         |       |
| PLUGIN_TYPE_VERSION    | varchar(20) | NO   |     |         |       |
| PLUGIN_LIBRARY         | varchar(64) | YES  |     | NULL    |       |
| PLUGIN_LIBRARY_VERSION | varchar(20) | YES  |     | NULL    |       |
| PLUGIN_AUTHOR          | varchar(64) | YES  |     | NULL    |       |
| PLUGIN_DESCRIPTION     | longtext    | YES  |     | NULL    |       |
| PLUGIN_LICENSE         | varchar(80) | NO   |     |         |       |
| LOAD_OPTION            | varchar(64) | NO   |     |         |       |
| PLUGIN_MATURITY        | varchar(12) | NO   |     |         |       |
| PLUGIN_AUTH_VERSION    | varchar(80) | YES  |     | NULL    |       |
+------------------------+-------------+------+-----+---------+-------+

153主库配置半同步复制，并查看模块是否被加载

┌──[root@vms153.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'select PLUGIN_NAME,PLUGIN_STATUS from information_schema.PLUGINS where PLUGIN_NAME like "%semi%";'
┌──[root@vms153.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'install plugin rpl_semi_sync_master SONAME "semisync_master.so";select PLUGIN_NAME,PLUGIN_STATUS from information_schema.PLUGINS where PLUGIN_NAME like "%semi%";'
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
┌──[root@vms153.liruilongs.github.io]-[/]
└─$

154主从库配置半同步复制，需要两个模块都加载，查看模块是否被加载

┌──[root@vms154.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'select PLUGIN_NAME,PLUGIN_STATUS from information_schema.PLUGINS where PLUGIN_NAME like "%semi%";'
┌──[root@vms154.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'install plugin rpl_semi_sync_master SONAME "semisync_master.so";select PLUGIN_NAME,PLUGIN_STATUS from information_schema.PLUGINS where PLUGIN_NAME like "%semi%";'
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
┌──[root@vms154.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'install plugin rpl_semi_sync_slave SONAME "semisync_slave.so";'
┌──[root@vms154.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'select PLUGIN_NAME,PLUGIN_STATUS from information_schema.PLUGINS where PLUGIN_NAME like "%semi%";'
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
| rpl_semi_sync_slave  | ACTIVE        |
+----------------------+---------------+
┌──[root@vms154.liruilongs.github.io]-[/]
└─$

155从库配置半同步复制，并查看模块是否被加载

┌──[root@vms155.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'select PLUGIN_NAME,PLUGIN_STATUS from information_schema.PLUGINS where PLUGIN_NAME like "%semi%";'
┌──[root@vms155.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'install plugin rpl_semi_sync_slave SONAME "semisync_slave.so";'
┌──[root@vms155.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'select PLUGIN_NAME,PLUGIN_STATUS from information_schema.PLUGINS where PLUGIN_NAME like "%semi%";'
+---------------------+---------------+
| PLUGIN_NAME         | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+
┌──[root@vms155.liruilongs.github.io]-[/]
└─$

启用模块命令行配置

153设置rpl_semi_sync_master_enabled模块启用

┌──[root@vms153.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'show variables like "%semi%";'
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | OFF   |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
┌──[root@vms153.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'set global rpl_semi_sync_master_enabled=1;'
┌──[root@vms153.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'show variables like "%semi%";'
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
┌──[root@vms153.liruilongs.github.io]-[/]
└─$

154设置rpl_semi_sync_master_enabled、rpl_semi_sync_slave_enabled模块启用

┌──[root@vms154.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'set global rpl_semi_sync_master_enabled=1; set global rpl_semi_sync_slave_enabled=1;'
┌──[root@vms154.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'show variables like "%semi%";'
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
| rpl_semi_sync_slave_enabled        | ON    |
| rpl_semi_sync_slave_trace_level    | 32    |
+------------------------------------+-------+
┌──[root@vms154.liruilongs.github.io]-[/]
└─$

155设置rpl_semi_sync_slave_enabled模块启用

┌──[root@vms155.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'set global rpl_semi_sync_slave_enabled=1;'
┌──[root@vms155.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'show variables like "%semi%";'
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
┌──[root@vms155.liruilongs.github.io]-[/]
└─$

永久配置

永久配置需要把相关设置写到配置文件，然后重启服务 153 主库配置需要修改配置文件重启服务

plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1

┌──[root@vms153.liruilongs.github.io]-[/]
└─$vim /etc/my.cnf
┌──[root@vms153.liruilongs.github.io]-[/]
└─$cat /etc/my.cnf
[mysqld]

plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1


server_id=153
log_bin=master153
# 字符集
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

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

[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

┌──[root@vms153.liruilongs.github.io]-[/]
└─$systemctl restart mariadb.service
┌──[root@vms153.liruilongs.github.io]-[/]
└─$

查看配置是否启动

┌──[root@vms153.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'show variables like "%semi%";'
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
┌──[root@vms153.liruilongs.github.io]-[/]
└─$

154 主备都需要配置

┌──[root@vms154.liruilongs.github.io]-[/]
└─$vim /etc/my.cnf
┌──[root@vms154.liruilongs.github.io]-[/]
└─$cat /etc/my.cnf
[mysqld]

plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1


server_id=154
log_bin=master154
log_slave_updates
# 字符集
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

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

[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
┌──[root@vms153.liruilongs.github.io]-[/]
└─$systemctl restart mariadb.service

154查看配置是否启动

┌──[root@vms154.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'show variables like "%semi%";'
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
| rpl_semi_sync_slave_enabled        | ON    |
| rpl_semi_sync_slave_trace_level    | 32    |
+------------------------------------+-------+
┌──[root@vms154.liruilongs.github.io]-[/]
└─$

155 从库配置,重启服务

plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1

┌──[root@vms155.liruilongs.github.io]-[/]
└─$vim /etc/my.cnf
 31L, 879C 已写入
┌──[root@vms155.liruilongs.github.io]-[/]
└─$cat /etc/my.cnf
[mysqld]

plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1

server_id=155
# 字符集
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

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

[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
┌──[root@vms155.liruilongs.github.io]-[/]
└─$systemctl restart mariadb.service

查看配置是否启动

┌──[root@vms155.liruilongs.github.io]-[/]
└─$mysql -uroot -pliruilong -e'show variables like "%semi%";'
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
┌──[root@vms155.liruilongs.github.io]-[/]
└─$

关于Linux下Mysql集群复制的部署和小伙伴们分享到这里，生活加油 ^_^，之前有机会会分享一些高可用/读写分离的方案及部署

博文参考

《高性能Mysql》第三版(High Performance MySQL,Third Edition)