激情亚洲一区国产无码精品,r级免费视频在线观看

關于Linux下MySql集群(主從/一主多從/主從從)同步部署的一些筆記

1寫在前面
和小伙們分享一些MySql集群主從同步部署相關的筆記
博文內容涉及：
為什么需要MySql集群主從同步？
主從同步原理？
部署不同主從結構的MySql集群
一主一從
一主多從
主從從
主從同步使用的復制模式介紹配置
食用方式：了解Linux、MySql即可
理解不足小伙伴幫忙指正
嗯，都要努力生活下去 ^_^
一些名詞解釋：
MySql和其分支MariaDB在5.7之前的版本是兼容的，當前博文部署使用的是 MariaDB 5.5的版本，這里統(tǒng)一稱MySql
Mysql 集群分為主庫(master)和需要復制的備庫(replica)或者稱為從庫(slave)。
主庫(master): 接受客戶端訪問連接
從庫(slave)：自動同步主服務器數(shù)據(jù)
文中講主從同步、主備同步、主從復制都是一個意思。
2一、為什么需要Mysql的主從復制
對于構建基于MySQL的大規(guī)模、高性能應用來講，需要使用水平擴展(集群)的數(shù)據(jù)庫架構方式。在MySQL內建的復制功能可以實現(xiàn)，通過為服務器配置一個或多個備庫的方式來進行數(shù)據(jù)同步。

同時復制功能不僅有利于構建高性能的應用，也是高可用性、可擴展性、容災、備份以及數(shù)據(jù)倉庫等工作的基礎。

復制的基本原理是讓一臺服務器的數(shù)據(jù)與其他服務器保持同步。一臺主庫的數(shù)據(jù)可以同步到多臺備庫上，備庫本身也可以被配置成另外一臺服務器的主庫。主庫和備庫之間可以有多種不同的組合方式。

復制解決的問題
數(shù)據(jù)分布
MySQL復制通常不會對帶寬造成很大的壓力，但在5.1版本引入的基于行的復制會比傳統(tǒng)的基于語句的復制模式的帶寬壓力更大。通過復制可以實現(xiàn)在不同的地理位置來分布數(shù)據(jù)備份，例如不同的數(shù)據(jù)中心。即使在不穩(wěn)定的網絡環(huán)境下，遠程復制也可以工作。

負載均衡
通過MySQL復制可以將讀操作分布到多個服務器上，實現(xiàn)對讀密集型應用的優(yōu)化，并且實現(xiàn)很方便，通過簡單的代碼修改就能實現(xiàn)基本的負載均衡。

對于小規(guī)模的應用，可以簡單地對機器名做硬編碼或使用DNS輪詢(將一個機器名指向多個IP地址)。當然也可以使用更復雜的方法，例如網絡負載均衡這一類的標準負載均衡解決方案，能夠很好地將負載分配到不同的MySQL服務器上。Linux虛擬服務器(LinuxVirtual Server，LVS)也能夠很好地工作。

備份
對于備份來說，復制是一項很有意義的技術補充，但復制既不是備份也不能夠取代

高可用性和故障切換
復制能夠幫助應用程序避免MySQL單點失敗，一個包含復制的設計良好的故障切換系統(tǒng)能夠顯著地縮短宕機時間。

MySQL升級測試
這種做法比較普遍，使用一個更高版本的MySQL作為備庫，保證在升級全部實例前，查詢能夠在備庫按照預期執(zhí)行。

復制方式
MySQL 支持兩種復制方式：基于行的復制和基于語句的復制。

基于語句的復制(也稱為邏輯復制)早在MySQL3.23版本中就存在，而基于行的復制方式在5.1版本中才被加進來。

這兩種方式都是通過在主庫上記錄二進制日志、在備庫重放日志的方式來實現(xiàn)異步的數(shù)據(jù)復制。

這意味著，在同一時間點備庫上的數(shù)據(jù)可能與主庫存在不一致，并且無法保證主備之間的延遲。一些大的語句可能導致備庫產生幾秒、幾分鐘甚至幾個小時的延遲。

復制通常不會增加主庫的開銷，主要是啟用二進制日志帶來的開銷，但出于備份或及時從崩潰中恢復的目的，這點開銷也是必要的。除此之外，每個備庫也會對主庫增加一些負載(例如網絡I/O開銷)，尤其當備庫請求從主庫讀取舊的二進制日志文件時，可能會造成更高的I/O開銷。另外鎖競爭也可能阻礙事務的提交。最后，如果是從一個高吞吐量(例如5000或更高的TPS)的主庫上復制到多個備庫，喚醒多個復制線程發(fā)送事件的開銷將會累加。

3二、主從同步原理
MySQL實際上是如何復制數(shù)據(jù)的?？偟膩碚f，復制有三個步驟：

在主庫上開啟記錄二進制日志。在每次準備提交事務完成數(shù)據(jù)更新前，主庫將數(shù)據(jù)更新的事件記錄到二進制日志中。MySQL會按事務提交的順序而非每條語句的執(zhí)行順序來記錄二進制日志。在記錄二進制日志后，主庫會告訴存儲引擎可以提交事務了。
備庫將主庫的二進制日志復制到其本地的中繼日志中。首先，備庫會啟動一個工作線程，稱為I/O線程，I/O線程跟主庫建立一個普通的客戶端連接，然后在主庫上啟動一個特殊的二進制轉儲(binlog dump)線程(該線程沒有對應的SQL命令)，這個二進制轉儲線程會讀取主庫上二進制日志中的事件。它不會對事件進行輪詢。如果該線程追趕上了主庫，它將進入睡眠狀態(tài)，直到主庫發(fā)送信號量通知其有新的事件產生時才會被喚醒，備庫I/0線程會將接收到的事件記錄到中繼日志中。
備庫的SQL線程執(zhí)行最后一步，該線程從中繼日志中讀取事件并在備庫執(zhí)行，從而實現(xiàn)備庫數(shù)據(jù)的更新。當SQL線程追趕上I/O線程時，中繼日志通常已經在系統(tǒng)緩存中，所以中繼日志的開銷很低。SQL線程執(zhí)行的事件也可以通過配置選項來決定是否寫入其自己的二進制日志中，它對于我們稍后提到的場景非常有用。
在這個過程中，涉及兩個角色：

Master角色
啟用 binlog 日志：開啟 binlog 日志，記錄所有除查詢以外的 SQL 命令
Slave角色：
Slave_IO: 復制 master 主機 binlog 日志文件里的 SQL 命令到本機的 relay-log(中繼日志) 文件里。從服務器上的 I/O thread(讀寫線程) 負責讀取主服務器 binlog 日志中的 SQL 命令，并將其寫入到 Relay log(中繼日志中)；
Slave_SQL: 執(zhí)行本機 relay-log(中繼日志) 文件里的 SQL 語句，實現(xiàn)與 Master 數(shù)據(jù)一致。從服務器中的 SQL thread(SQL 線程)讀取中繼日志中的 SQL 命令，并將其寫入到 Slave 的數(shù)據(jù)庫中；
主從同步結構模式
主從的復制的結果模式設置需要注意幾點:

一個MySQL備庫實例只能有一個主庫。
每個備庫必須有一個唯一的服務器ID。
一個主庫可以有多個備庫(或者相應的，一個備庫可以有多個兄弟備庫)。
如果打開了log_slave_updates選項，一個備庫可以把其主庫上的數(shù)據(jù)變化傳播到其他備庫。
常見的結構模式：

單向復制：一主一從
一主多從：從 <—— 主 ——> 從,即一個主節(jié)點，多個從節(jié)點
鏈式復制：主 <—— 從<—— 從：即鏈式復制，第一個主節(jié)點，最后一個為從節(jié)點，中間的為主從節(jié)點
互為主從：主 <——> 主：也叫雙主復制或者雙向復制。需要解決沖突問題。
今天和小伙伴們分享的主要是前三，基本的配置步驟：

在主庫創(chuàng)建復制賬號。
配置主庫和備庫。
通知備庫連接到主庫并從主庫復制數(shù)據(jù)。
4三、MySQL 主從同步部署配置
配置MySQL一主一從
主服務器：192.168.26.153
從服務器：192.168.26.154
客戶端：192.168.26.152

環(huán)境安裝，為了方便部分地方使用了ansible操作。

清單文件

┌──[root@vms152.liruilongs.github.io]-[~]
└─$cat inventory
[master]
192.168.26.152
[node]
192.168.26.153
192.168.26.154
┌──[root@vms152.liruilongs.github.io]-[~]
└─$
安裝數(shù)據(jù)庫

┌──[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'

數(shù)據(jù)庫初始化操作,安裝數(shù)據(jù)庫需要操作

┌──[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日志的狀態(tài)信息
+------------------+----------+--------------+------------------+
| 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狀態(tài)

┌──[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)]>
測試主從同步
主庫添加數(shù)據(jù)

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)]>

從庫相關數(shù)據(jù)文件
存放在數(shù)據(jù)庫目錄下；刪除文件，重啟數(shù)據(jù)庫服務，可把主機恢復為獨立的數(shù)據(jù)庫服務器

┌──[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相關軟件包，做備份，然后把備份復制到新的從服務器

備份主庫數(shù)據(jù)
┌──[root@vms153.liruilongs.github.io]-[/var/lib/mysql]
└─$yum -y install percona-xtrabackup
使用 innobackupex 命令備份

####完全備份：備份所有庫和所有表
#--user           指定數(shù)據(jù)庫的用戶名: root
#--password       指定數(shù)據(jù)庫的密碼:   liruilong
#/alldb          指定備份數(shù)據(jù)存放的目錄，不需要提前創(chuàng)建，會自動創(chuàng)建
#--no-timestamp   指定不用日期時間作為存儲數(shù)據(jù)的子目錄名稱
#--slave-info     指備份數(shù)據(jù)時,記錄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

查看拷貝過來的文件數(shù)據(jù)

┌──[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日志文件名和偏移量,此偏移量和主服務器的偏移量一致，從服務器同步數(shù)據(jù)時從這個偏移量開始同步

┌──[root@vms155.liruilongs.github.io]-[~]
└─$cat /opt/alldb/xtrabackup_binlog_info
master153.000004        854
新從庫通過備份數(shù)據(jù)恢復數(shù)據(jù)
停調新的從庫服務

┌──[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)
刪除新從庫數(shù)據(jù)文件，讀書備份數(shù)據(jù)的備份范圍

┌──[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!
拷貝備份數(shù)據(jù)到從庫數(shù)據(jù)目錄下

┌──[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]-[~]
└─$
查看數(shù)據(jù)是否拷貝成功，修改/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
查看數(shù)據(jù)是否恢復成功

┌──[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)]>
查看從庫狀態(tài)信息

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]-[~]
└─$
測試一主多從
主庫添加數(shù)據(jù)

┌──[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的數(shù)據(jù)是否同步

┌──[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的數(shù)據(jù)是否同步

┌──[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，并不是實際上的高可用，實際的高可用實現(xiàn)需要借助一些其他工具

主服務器：192.168.26.153
從主服務器：192.168.26.154
從服務器：192.168.26.155
客戶端：192.168.26.152

主從從結構優(yōu)勢：

192.168.26.155是192.168.26.154的從服務器，192.168.26.154是192.168.26.153的從服務器；
當192.168.26.153宕機以后，用戶可以訪問從服務器192.168.26.154的數(shù)據(jù)庫；
當192.168.26.154宕機以后，用戶可以訪問從服務器192.168.26.155的數(shù)據(jù)庫；

篇幅有限，我們在上面一主多從的基礎上修改同步架構模式為主從從

主從庫配置
修改192.168.26.154主配置文件，log_slave_updates 開啟級聯(lián)復制功能，因為154同步數(shù)據(jù)是從153的binlog 日志中獲取的，154并沒有直接執(zhí)行sql命令，所以在154 的binlog日志中并沒有sql命令，那么155也就無法同步154中的數(shù)據(jù)；而開啟級聯(lián)復制功能，則允許155同步154從153同步過來的數(shù)據(jù)

同時需要開啟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]-[/]
└─$
重啟服務后查看從庫狀態(tài)

┌──[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從庫也作為主庫，查看主庫狀態(tài)

┌──[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，刪除數(shù)據(jù)庫相關數(shù)據(jù)，把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從庫同步狀態(tài)

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主庫新增數(shù)據(jù)

┌──[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]
└─$
5四、復制模式
異步復制 (默認的復制模式)
Asynchronous replication:主服務器執(zhí)行完一次事務后，立即將結果返給客戶端，不關心從服務器是否已經同步數(shù)據(jù)。

優(yōu)點：響應速度快，用戶體驗很好；
缺點：主服務器宕機后，有可能會存在從服務器數(shù)據(jù)丟失的情況；

半同步復制
Semisynchronous replication:主服務器在執(zhí)行完一次事務后，等待至少一臺從服務器同步數(shù)據(jù)完成，才將結果返回給客戶端。

優(yōu)點：主服務器宕機后，至少有一臺從服務器擁有和主服務器相同的數(shù)據(jù)，數(shù)據(jù)安全度高；
缺點：響應速度下降，用戶體驗度下降；
關于半同步，有一些普遍的誤解，下面是它不會去做的：

在備庫提示其已經收到事件前，會阻塞主庫上的事務提交。事實上在主庫上已經完成事務提交，只有通知客戶端被延遲了。
直到備庫執(zhí)行完事務后，才不會阻塞客戶端。備庫在接收到事務后發(fā)送反饋而非完成事務后發(fā)送。
半同步不總是能夠工作。如果備庫一直沒有回應已收到事件，會超時并轉化為正常的異步復制模式。
配置半同步復制
臨時配置
馬上生效，重啟服務后失效

加載模塊命令行配置
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集群復制的部署和小伙伴們分享到這里，生活加油 ^_^，之后有機會會分享一些高可用/讀寫分離的方案及部署
6博文參考
《高性能Mysql》第三版(High Performance MySQL,Third Edition)

作者：山河已無恙

歡迎關注微信公眾號：山河已無恙

在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

運維