企業化運維(5)_mysql數據庫

###1.源碼編譯mysql###?

對壓縮包進行解壓，并對mysql進行源碼編譯，其中需要下載依賴才能編譯成功。?

官網： www.mysql.com解壓并進入目錄
[root@server1 ~]# tar xf mysql-boost-5.7.40.tar.gz
[root@server1 ~]# cd mysql-5.7.40/安裝依賴性
[root@server1 mysql-5.7.40]# yum install -y cmake gcc-c++ bsioncmake
[root@server1 mysql-5.7.40]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci -DWITH_BOOST=/root/mysql-5.7.40/boost/boost_1_59_0[root@server1 mysql-5.7.40]# make
[root@server1 mysql-5.7.40]# make install

[注意] 如果沒有一次性cmake成功，則刪除CMakeCache.txt，重新cmake?

參數含義
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql				##指定軟件的安裝路徑
-DMYSQL_DATADIR=/data/mysql							##指定MySQL數據庫數據文件的存儲路徑，這一選項在MySQL服務啟動時可以通過datadir參數進行設置
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock			##指定套接字文件的存儲路徑，默認是在/tmp/mysql.sock目錄下，這一選項可以在MySQL服務啟動時通過socket參數進行設置
-DWITH_INNOBASE_STORAGE_ENGINE=1					##靜態編譯存儲引擎
-DSYSCONFDIR=/etc									##指定MySQL參數文件的默認路徑，這一選項可以在MySQL服務啟動時通過defaults-file參數進行設置
-DENABLED_LOCAL_INFILE=1							##是否允許從客戶端本地加載數據到MySQL服務端，專用于load data infile語句，默認是不允許的
-DWITH_EXTRA_CHARSETS=all							##指定附加支持的字符集，默認是all
-DDEFAULT_CHARSET=utf8mb4							##指定MySQL服務的默認字符集
-DDEFAULT_COLLATION=utf8mb4_unicode_ci				##指定MySQL服務的默認校對規則
-DWITH_BOOST=/root/mysql-5.7.31/boost/boost_1_59_0	##安裝位置與數據位置

###2.初始化配置mysql###

創建mysql的數據目錄，并設定為某一用戶的所屬人、所屬組以保證其執行權限，因此建立該用戶。同時拷貝啟動腳本，使mysql可全局使用，然后開啟服務。??

拷貝啟動腳本
[root@server1 mysql-5.7.40]# cd /usr/local/mysql/
[root@server1 mysql]# cd support-files/
[root@server1 support-files]# cp mysql.server /etc/init.d/mysqld

修改mysql配置文件
[root@server1 etc]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/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

創建mysql的數據目錄，并設定為mysql用戶的所屬人、所屬組以保證其執行權限，因此建立mysql用戶。
[root@server1 ~]# mkdir /data/mysql -p
[root@server1 ~]# useradd -M -d /data/mysql/ -s /sbin/nologin mysql
[root@server1 ~]# chown mysql.mysql /data/mysql/

修改環境變量使其生效
[root@server1 ~]# vim .bash_profile
# .bash_profile# Get the aliases and functions
if [ -f ~/.bashrc ]; then. ~/.bashrc
fi# User specific environment and startup programsPATH=$PATH:$HOME/bin:/usr/local/php/bin:/usr/local/mysql/binexport PATH[root@server1 ~]# source .bash_profile

初始化觸發
[root@server1 ~]# mysqld --initialize --user=mysql
啟動服務
[root@server1 ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/server1.err'.SUCCESS!
[root@server1 ~]# netstat -antlp|grep :3306
tcp6       0      0 :::3306                 :::*                    LISTEN      13856/mysqld

安全初始化
[root@server1 ~]# mysql_secure_installation
Securing the MySQL server deployment.Enter password for user root: #輸入上面生成的臨時密碼The existing password for the user account root has expired. Please set a new password.New password:	#輸入兩遍新密碼Re-enter new password:VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?Press y|Y for Yes, any other key for No:		#是否激活密碼插件，直接回車
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) :
#是否再次修改管理員密碼，直接回車... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
#是否刪除匿名用戶，輸入 yNormally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
#是否禁用root遠程登錄，輸入 y
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y- Dropping test database...
Success.
#是否刪除test數據庫，輸入 y- Removing privileges on test database...
Success.Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
#刷新授權表
All done!

登錄數據庫
[root@server1 ~]# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.40 Source distributionCopyright (c) 2000, 2022, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

###3.主從復制###

（1）master配置

修改配置文件，設定啟用binlog和主庫id號，重啟服務。在數據目錄下，會生成數據庫的二進制操作日志和二進制日志的索引文件。

[root@server1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/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/Systemdlog-bin=mysql-bin##表示啟用binlog,log-bin決定了msyql 的binlog的名字，?成的binlog名字為mysql-bin.000001
server_id=1			##正整數，可以不是遞增，但必須為正#[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@server1 ~]# /etc/init.d/mysqld restart

?進入數據庫，創建復制用戶，并且進行授權。slave端可以通過該授權用戶進入master端的數據庫。

[root@server1 ~]# mysql -pwestos
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'westos';
Query OK, 0 rows affected (0.00 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      595 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

（2）slave配置

在server1主機master主庫中將mysql的數據目錄以及配置文件復制到server2主機，即slave從庫。

新建數據庫實例server2
[root@server1 ~]# scp -r /usr/local/mysql/ server2:/usr/local/
[root@server1 ~]# scp /etc/my.cnf server2:/etc/修改配置文件
[root@server2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/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/Systemdserver-id=2#[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

?修改環境變量，在從庫中拷貝啟動腳本，使mysql可全局使用，添加變量，創建用戶，數據目錄，初始化，然后開啟服務，修改密碼。?

修改環境變量
[root@server2 ~]# vim .bash_profile
[root@server2 ~]# source .bash_profile[root@server2 ~]# cd /usr/local/mysql
[root@server2 mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@server2 mysql]# useradd -M -d /data/mysql/ -s /sbin/nologin mysql
[root@server2 mysql]# mkdir -p /data/mysql
[root@server2 mysql]# chown mysql.mysql /data/mysql/

用mysql用戶初始化觸發
[root@server2 mysql]# mysqld --initialize --user=mysql
啟動服務
[root@server2 mysql]# /etc/init.d/mysqld start
安全初始化
[root@server2 mysql]# mysql_secure_installation

配置主從同步?

登錄
[root@server2 mysql]# mysql -pwestos
mysql> CHANGE MASTER TO MASTER_HOST='192.168.76.11', MASTER_USER='repl', MASTER_PASSWORD='westos', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=595;
Query OK, 0 rows affected, 2 warnings (0.01 sec)###########參數#########################
MASTER_HOST='192.168.76.11'          #訪問的master端地址
MASTER_USER='repl',                  #訪問的用戶
MASTER_PASSWORD='westos',            #輸入密碼
MASTER_LOG_FILE='mysql-bin.000001',  #要從master中復制的文件
MASTER_LOG_POS=595;                  #文件Position號
########################################開啟slave
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)查看slave狀態
mysql> show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.56.11Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 595Relay_Log_File: server2-relay-bin.000002Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: Yes   ##兩行yes即為成功
...

（3）測試server12數據同步

master創建測試數據庫

[root@server1 ~]# mysql -pwestos
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)mysql> create database westos;
Query OK, 1 row affected (0.00 sec)mysql> use westos；mysql> create table user_tb (-> username varchar(25) not null,->  password varchar(50) not null);
Query OK, 0 rows affected (0.00 sec)mysql> desc user_tb;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(25) | NO   |     | NULL    |       |
| password | varchar(50) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)mysql> insert into user_tb values ('user1','123');
Query OK, 1 row affected (0.00 sec)mysql> insert into user_tb values ('user2','456');
Query OK, 1 row affected (0.00 sec)

slave server2：查看測試數據庫是否同步

[root@server2 mysql]# mysql -pwestos
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| westos             |
+--------------------+
5 rows in set (0.00 sec)
mysql> use westos
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| user_tb          |
+------------------+
1 row in set (0.00 sec)mysql> select * from user_tb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 456      |
+----------+----------+
2 rows in set (0.01 sec)

（4）添加一臺新的slave：server3?

[root@server1 ~]# scp -r /usr/local/mysql/ server3:/usr/local/
[root@server1 ~]# scp /etc/my.cnf server3:/etc/
[root@server3 ~]# vim .bash_profile
[root@server3 ~]# source .bash_profile
[root@server3 ~]# mkdir -p /data/mysql
[root@server3 ~]# useradd -M -d /data/mysql/ -s /sbin/nologin mysql
[root@server3 ~]# chown mysql.mysql /data/mysql/
[root@server3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/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/Systemdserver-id=3#[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@server3 ~]# mysqld --initialize --user=mysql
[root@server3 ~]# cd /usr/local/mysql/
[root@server3 mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@server3 mysql]# /etc/init.d/mysqld start
[root@server3 mysql]# mysql_secure_installation

（5）配置slave3能同步master改變前后的所有數據

新建的slave只能同步從新建這一刻起master生成的新數據，之前的數據會丟失。需要先將master已有的數據備份，傳輸給新的slave，在建立新的主從復制。

master備份數據庫?

[root@server1 ~]# mysqldump -pwestos westos > dump.sql   #mysqldump用來做備份
[root@server1 ~]# scp dump.sql server3:

注意：
生產環境中備份時需要鎖表，保證備份前后的數據一致
mysql> FLUSH TABLES WITH READ LOCK;
備份后再解鎖
mysql> UNLOCK TABLES;?

注意：

mysqldump命令備份的數據文件，在還原時先DROP TABLE，需要合并數據時需要刪除此語句

?

salve server3還原數據庫?

[root@server3 ~]# mysqladmin -pwestos create westos #新建一個庫
[root@server3 ~]# mysql -pwestos westos < dump.sql   #將備份數據傳給庫

測試是否舊數據同步?

[root@server3 ~]# mysql -pwestos
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| westos             |
+--------------------+
5 rows in set (0.00 sec)mysql> use westos
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> select * from user_tb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 456      |
+----------+----------+
2 rows in set (0.00 sec)

設置主從同步

mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.11', MASTER_USER='repl', MASTER_PASSWORD='westos', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1534;
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> start slave;
Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.56.11Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 1534Relay_Log_File: server3-relay-bin.000002Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: Yes
...

（6）測試server123數據同步

master server1創建測試文件

[root@server1 ~]# mysql -pwestos
mysql> insert into westos.user_tb values ('user3','666');
Query OK, 1 row affected (0.00 sec)

slave server23查看是否同步

[root@server2 mysql]# mysql -pwestos
mysql> select * from westos.user_tb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 456      |
| user3    | 666      |
+----------+----------+
3 rows in set (0.00 sec)[root@server3 mysql]# mysql -pwestos
mysql> select * from westos.user_tb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 456      |
| user3    | 666      |
+----------+----------+
3 rows in set (0.00 sec)

###4.主-從（主）-從復制+GTID###?

GTID即全局事務ID (global transaction identifier), 其保證為每一個在主上提交的事務在復制集群中可以生成一個唯一的ID。mysql主從結構在一主一從情況下對于GTID來說就沒有優勢了，而對于2臺主以上的結構優勢異常明顯，可以在數據不丟失的情況下切換新主。

（1）master設置?

在server1主機master端mysql配置文件中開啟gtid，并重啟服務。

master配置：
[root@server1 mysql]# vim /etc/my.cnf
...
log-bin=mysql-bin
server-id=1
gtid_mode=ON
enforce-gtid-consistency=ON重啟服務
[root@server1 mysql]# /etc/init.d/mysqld restart

?（2）slave設置

在server2主機slave端mysql配置文件中開啟gtid，并重啟服務。停止slave端，設定以repl用戶自動獲取master端的事務。開啟slave，查看狀態，可以看到，IO線程和SQL線程為running狀態。?其它slave節點以此類推?

slave配置：
[root@server2 ~]# vim /etc/my.cnf
...
server-id=2
gtid_mode=ON
enforce-gtid-consistency=ON重啟服務
[root@server2 ~]# /etc/init.d/mysqld restart[root@server2 ~]# mysql -pwestos
#首先停止slave
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)#重新配置
mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.11', MASTER_USER='repl', MASTER_PASSWORD='westos', MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)#再次啟動
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.56.11Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos: 154Relay_Log_File: server2-relay-bin.000002Relay_Log_Pos: 367Relay_Master_Log_File: mysql-bin.000002Slave_IO_Running: YesSlave_SQL_Running: Yes

（3）測試?

在master端插入新內容，slave端的mysql表中的gtid_executed里會生成gtid的標識號。

?###5.半同步復制###

異步復制：主庫只負責發送，從庫只負責接受，主庫沒有確認復制的過程，如果網絡延遲或者設備中斷，會導致數據不同步。
show processlist; 可以查看io和sql線程
io延遲：網絡延遲
sql延遲：因為主庫上多個線程對數據庫進行寫入，但從庫只有一個線程進行回放，不可避免會產生延遲。
當讀和寫的同步需求很高，異步復制就不能夠穩定復制。

AFTER_COMMIT
原理：master將binlog發送給slave端，同時保存本地，進行引擎提交，等待slave端完成操作，返回給master端ack信息，同時返回用戶ok信息。
但問題是，進行引擎提交后，salve端沒有完成操作，但此時同時登陸的另一個用戶已經可以看到提交的信息，如果此時master端掛掉，slave端沒有保存信息，而另一用戶已經看到的信息也會消失。

AFTER_SYNC(5.7版本后默認使用)

原理：master將binlog發送給slave端，同時保存本地，等待slave端完成操作，返回給master端ack信息，master端才會進行引擎提交，同時返回用戶ok信息。

（1）master配置

#安裝半同步模塊
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)#查看節點上是否已經安裝了模塊
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS->        FROM INFORMATION_SCHEMA.PLUGINS->        WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)#激活master半同步模式
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)#查看半同步參數
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+-------------------------------------------+------------+
| 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_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)#查看半同步狀態
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 2     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)半同步參數寫入配置文件，確保重啟后依然生效
[root@server1 mysql]# vim /etc/my.cnf
...
log-bin=mysql-bin
server-id=1
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1

（2）slave配置

#安裝半同步模塊
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)#激活半同步模式
mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
Query OK, 0 rows affected (0.00 sec)#需要重啟IO線程，slave端的半同步才能生效
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.00 sec)mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)#半同步參數寫入配置文件
[root@server2 ~]# vim /etc/my.cnf
server-id=2
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_slave_enabled=1

（3）測試

master寫入數據：
mysql> insert into westos.user_tb values ('user6','666');
Query OK, 1 row affected (0.01 sec)mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 2     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 2     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 2096  |
| Rpl_semi_sync_master_tx_wait_time          | 2096  |
| Rpl_semi_sync_master_tx_waits              | 1     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 1     |		#顯示通過半同步復制的數據
+--------------------------------------------+-------+
14 rows in set (0.00 sec)停止所有slave節點的IO線程：(不會再有任何slave線程回應master)
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)master節點再次寫入數據：
mysql> insert into westos.user_tb values ('user7','666');
Query OK, 1 row affected (10.00 sec)
#等待默認超時時間10秒后，mysql自動切換為異步模式mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 2     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 4     |
| Rpl_semi_sync_master_no_times              | 1     |
| Rpl_semi_sync_master_no_tx                 | 1     |	#顯示未通過半同步模式復制的數據，即異步模式數據
| Rpl_semi_sync_master_status                | OFF   |	#半同步模式關閉
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 2096  |
| Rpl_semi_sync_master_tx_wait_time          | 2096  |
| Rpl_semi_sync_master_tx_waits              | 1     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 1     |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)所有slave節點再次啟動IO線程，mysql會自動切回半同步模式
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

###6.并行復制（MTS）###?

適用于多線程并發。基于組的并發復制，可以支持在一個database中，并發執行relaylog中的事務。相同的二進制日志組在master上提交并行應用到slave節點上，沒有跨數據庫的限制，并且不需要把數據分割到多個數據庫。

（1）master配置?

在master主機中修改配置文件，設定在mysql中創建表，數據會存在數據庫中，打開半同步復制。?

vim /etc/my.cnf
///
log-bin=mysql-bin
server-id=1
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1		##控制打開半同步復制
master_info_repository=TABLE		##在mysql中創建表，數據會存在數據庫中
///

（2）slave配置?

server2主機

默認slave節點sql單線程回放，會造成數據同步延時較高
slave節點添加以下參數
[root@server2 ~]# vim /etc/my.cnf
...
slave-parallel-type=LOGICAL_CLOCK    ##基于組提交的并行復制方式
slave-parallel-workers=16     ##可有16個SQL Thread(coordinator線程)來進行并行復制，不建議設置為1，單線程復制，但是因為要做coordinator協調線程的轉發，性能比0還差，效率會更低
master_info_repository=TABLE  ##有兩個值，分別是file和table，該參數決定了slave記錄master的狀態，如果參數是file，就會創建master.info文件，磁盤頻繁讀取文件，會降低執行效率，如果參數值是table，就在mysql中創建slave_master_info的表，數據會存在數據庫中，避免影響內存。
relay_log_info_repository=TABLE   ##明文存儲不安全,把relay.info中的信息記錄在table中相對安全
relay_log_recovery=ON           ##從庫意外宕機后，同時從庫的relay log也一起損壞了，而主庫的日志已經傳到了從庫，只是從庫還沒有來得及應用這些日志時，從庫會自動放棄所有未執行的relay log，重新生成一個relay log，并將從庫的io線程的position重新指向新的relay log。并將sql線程的position退回到跟io線程的position保持一致，重新開始同步，這樣在從庫中事務不會丟失。重啟服務
[root@server2 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

（3）測試

進入數據庫，查看設定是否生效

[root@server2 ~]# mysql -pwestos
> show variables like 'relay_log%';	##查看上面的設定是否成功
> use mysql
> show tables;						##slave_master_info
> select * from slave_relay_log_info;
> show processlist;

###7.延遲復制###?

1、mysql本身有延遲，測驗延遲多少會對實驗產生影響
2、如果主庫出現問題，有延遲內的時間從從庫恢復數據
并不記錄IO延遲，只記錄SQL的延遲

（1）slave端設置

在server2進入數據庫，查看從庫狀態，當前主從延遲為0，停止從庫服務。設定主從復制延遲為60s，重新開啟從庫服務。查看狀態，設定成功。?

###server2
mysql -p
> show slave status\G;					##sql_delay=0
> STOP SLAVE SQL_THREAD;                ##停止服務
> CHANGE MASTER TO MASTER_DELAY=60;     ##設定延遲60s復制
> START SLAVE SQL_THREAD;
> show slave status\G;                  ##sql_delay=30

（2）測試

此時在server1插入數據。

###server1
mysql -pwestos
> use westos;
> insert into linux values ('user9','999');

?在server2查看，seconds_behind_master會從60遞減到0，60s后，數據會被同步。

###server2
> show slave status\G;			##seconds_behind_master:30 ～ 0
> select * from westos.linux;	##重復執行，直到30s后才會出現user10
> show slave status\G;			##seconds_behind_master:0

###8.慢查詢###

程序中定位一個執行慢的SQL可以根據慢查詢日志，默認情況下，慢查詢日志禁用，因為開啟慢查詢日志或多或少的會對mysql的性能產生一些影響。在慢查詢日志功能開啟時，只有SQL執行時間超過long_query_time參數值的的語句才會在慢查詢日志中記錄。long_query_time參數，最小值和默認值分別為0 10，單位為秒。

在server1主機中，當前慢查詢為關閉狀態。設定開啟慢查詢，慢查詢記錄會存放在數據目錄server1-slow.log文件中。慢查詢默認值為10s，設定為5s，執行select sleep(N)語句。?

###server1
> show variables like 'slow%';	##slow_query_log為OFF
> set global slow_query_log=1;	##開啟慢查詢日志
> show variables like 'slow%';	##slow_query_log為ON，慢查詢記錄會存放在數據目錄server1-slow.log文件中
> show variables like 'long%';	##默認10s
> set long_query_time=5;
> show variables like 'long%';	##5s
> select sleep(10);				##執行select sleep(N)可以讓此語句運行N秒鐘
> exit

進入mysql數據目錄，查看慢查詢日志。?

cd /data/mysql
ls 
cat server1-slow.log
///
query_time:10.000515
select sleep(10);

###9.mysql組復制（多主模式）###

底層邏輯：基于二進制日志的復制
在任何一個節點寫入，都會將數據發送到其他所有節點進行校驗，當任何一個節點校驗失敗，其他節點不會執行該操作，只有所有節點都校驗成功，才會執行該操作。

容錯：最起碼需要有3個節點，允許一個節點出現即時故障。

組復制（group commit）：通過對事務進行分組，優化減少了生成二進制日志所需的操作數。當事務同時提交時，它們將在單個操作中寫入到二進制日志中。如果事務能同時提交成功，那么它們就不會共享任何鎖，這意味著它們沒有沖突，因此可以在Slave上并行執行。所以通過在主機上的二進制日志中添加組提交信息，這些Slave可以并行地安全地運行事務。

（1）server1配置

首先停止數據庫
[root@server1 mysql]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!清除數據
[root@server1 ~]# cd /data/mysql 
[root@server1 mysql]# rm -fr *[root@server1 mysql]# vim /etc/my.cnf
...
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"   ##不需要的引擎server_id=1
gtid_mode=ON                   ##開啟gitd復制
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE           ##不支持binlog校驗
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW              ##默認日志格式plugin_load_add='group_replication.so'  ##加載組復制插件
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"   ##UUID號，集群中要一致
group_replication_start_on_boot=off   ##不需開機自啟
group_replication_local_address= "server1:33061"
group_replication_group_seeds= "server1:33061,server2:33061,server3:33061"  ##解析
group_replication_bootstrap_group=off   ##默認關閉, 防止自動創建新的Group
group_replication_ip_whitelist="192.168.76.0/24,127.0.0.1/8"  ##安全白名單，默認只允許localhost，加入虛擬機網段
group_replication_single_primary_mode=OFF    ##單主模式
group_replication_enforce_update_everywhere_checks=ON   ##復制
group_replication_allow_local_disjoint_gtids_join=1
#根據實際情況修改主機名和網段初始化
[root@server1 mysql]# mysqld --initialize --user=mysql啟動服務
[root@server1 mysql]# /etc/init.d/mysqld start[root@server1 mysql]# mysql -p
#首先修改臨時密碼
mysql> alter user root@localhost identified by 'westos';
Query OK, 0 rows affected (0.01 sec)mysql> SET SQL_LOG_BIN=0;     ##設為0后，在Master數據庫上執行的語句都不記錄binlog(想在主庫上執行一些操作，但不復制到slave庫上，可以通過修改參數sql_log_bin來實現)
Query OK, 0 rows affected (0.00 sec)mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';  ##創建用于組復制的用戶，當前語句不紀錄binlog
Query OK, 0 rows affected (0.00 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';   ##授權
Query OK, 0 rows affected (0.00 sec)mysql> FLUSH PRIVILEGES;    ##刷新權限表
Query OK, 0 rows affected (0.00 sec)mysql> SET SQL_LOG_BIN=1;   打開binlog記錄，以下內容需要記錄
Query OK, 0 rows affected (0.00 sec)mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';      ##change master to配置和改變slave服務器用于連接master服務器的參數，以便slave服務器讀取master服務器的binlog及slave服務器的relay log。同時也更新master info及relay log info信息庫。執行該語句前如果從機上slave io及sql線程已經啟動，需要先停止(執行stop slave)
Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql> SET GLOBAL group_replication_bootstrap_group=ON;	#只在server1上執行，標示以后加入集群的服務器以這臺服務器為基準,以后加入的就不需要設置
Query OK, 0 rows affected (0.00 sec)mysql>  START GROUP_REPLICATION;    ##開啟組復制
Query OK, 0 rows affected, 1 warning (2.19 sec)mysql>  SET GLOBAL group_replication_bootstrap_group=OFF;	 #只在server1上執行
Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM performance_schema.replication_group_members;  ##查看組復制成員
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 21262bc0-b4e3-11ed-a6a0-000c298f4b2a | server1     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

（2）server2配置

首先停止數據庫
[root@server2 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!清除數據
[root@server2 ~]# cd /data/mysql 
[root@server2 mysql]# rm -fr *[root@server2 mysql]# vim /etc/my.cnf
...
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROWplugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "server2:33061"
group_replication_group_seeds= "server1:33061,server2:33061,server3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="192.168.76.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=1
#根據實際情況修改主機名和網段初始化
[root@server2 mysql]# mysqld --initialize --user=mysql啟動服務
[root@server2 mysql]# /etc/init.d/mysqld start[root@server2 mysql]# mysql -p
#首先修改臨時密碼
mysql> alter user root@localhost identified by 'westos';
Query OK, 0 rows affected (0.01 sec)mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
Query OK, 0 rows affected (0.00 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql>  START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.19 sec)mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 21262bc0-b4e3-11ed-a6a0-000c298f4b2a | server1     |        3306 | ONLINE       |
| group_replication_applier | e8be5058-b4e3-11ed-8788-000c29f15153 | server2     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

（3）server3配置

首先停止數據庫
[root@server3 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!清楚數據
[root@server3 ~]# cd /data/mysql 
[root@server3 mysql]# rm -fr *[root@server3 mysql]# vim /etc/my.cnf
...
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROWplugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "server3:33061"
group_replication_group_seeds= "server1:33061,server2:33061,server3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="192.168.76.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=1
#根據實際情況修改主機名和網段初始化
[root@server3 mysql]# mysqld --initialize --user=mysql啟動服務
[root@server3 mysql]# /etc/init.d/mysqld start[root@server3 mysql]# mysql -p
#首先修改臨時密碼
mysql> alter user root@localhost identified by 'westos';
Query OK, 0 rows affected (0.01 sec)mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
Query OK, 0 rows affected (0.00 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql>  START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.19 sec)mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 21262bc0-b4e3-11ed-a6a0-000c298f4b2a | server1     |        3306 | ONLINE       |
| group_replication_applier | 4c655909-b4e4-11ed-8dcc-000c29259218 | server3     |        3306 | ONLINE       |
| group_replication_applier | e8be5058-b4e3-11ed-8788-000c29f15153 | server2     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

（4）測試

所有節點都可以讀寫數據

server1：
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)mysql> USE test;
Database changed
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);   ##組復制要求創建表中必須有主鍵
Query OK, 0 rows affected (0.01 sec)mysql> INSERT INTO t1 VALUES (1, 'Luis');
Query OK, 1 row affected (0.03 sec)mysql>  SELECT * FROM t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
+----+------+
1 row in set (0.00 sec)

server2：
mysql> select * from test.t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
+----+------+
1 row in set (0.00 sec)mysql> INSERT INTO test.t1 VALUES (2, 'wxh');
Query OK, 1 row affected (0.00 sec)

server3：
mysql>  select * from test.t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
|  2 | wxh  |
+----+------+
2 rows in set (0.00 sec)mysql> INSERT INTO test.t1 VALUES (3, 'westos');
Query OK, 1 row affected (0.01 sec)

?###10.mysql路由器（讀寫分離）###

訪問mysql路由器，然后由mysql路由器訪問后端服務器server主機，確保當某一臺主機宕掉后，可以迅速啟用其他主機。相當于負載均衡。

基于主從復制或者組復制（多主模式）的集群，通過訪問不同端口實現讀寫分離。

前提：組復制，多主模式三臺主機均online,再準備一個server4主機做路由器

?（1）路由器server4配置

安裝mysql讀寫分離組件。編輯配置文件，設定只讀端口和讀寫端口，并開啟服務。

安裝軟件
[root@server4 ~]# rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm配置服務
[root@server4 ~]# cd /etc/mysqlrouter/
[root@server4 mysqlrouter]# vim mysqlrouter.conf
...
[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 192.168.56.11:3306,192.168.56.12:3306,192.168.56.13:3306
routing_strategy = round-robin[routing:rw]
bind_address = 0.0.0.0
bind_port = 7002
destinations = 192.168.56.13:3306,192.168.56.12:3306,192.168.56.11:3306
routing_strategy = first-available啟動服務
[root@server4 mysqlrouter]# systemctl  enable --now  mysqlrouter.service安裝mysql客戶端工具
[root@server4 ~]# yum install -y mariadb在mysql集群中創建遠程測試用戶
mysql> grant all on test.* to 'wxh'@'%' identified by 'westos';

（2）測試

路由器連接遠程測試用戶，訪問7001端口，按照配置文件的設置，第一個連接的應該是server1.

連接mysql路由器
[root@server4 ~]# mysql -h 192.168.56.14 -P 7001 -u wxh -pwestosMySQL [(none)]> select * from test.t1;
+----+--------+
| c1 | c2     |
+----+--------+
|  1 | Luis   |
|  2 | wxh    |
|  3 | westos |
+----+--------+
3 rows in set (0.00 sec)在server1上查看網絡連接
[root@server1 mysql]# yum install -y lsof
[root@server1 mysql]# lsof -i :3306
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  15184 mysql   22u  IPv6  87530      0t0  TCP *:mysql (LISTEN)
mysqld  15184 mysql   83u  IPv6  93365      0t0  TCP server1:mysql->server4:55504 (ESTABLISHED)

路由器連接遠程測試用戶，訪問7002端口，按照配置文件的設置，第一個連接的應該是server3.

[root@server4 ~]# mysql -h 192.168.56.14 -P 7002 -u wxh -pwestos[root@server3 mysql]# lsof -i :3306
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  30811 mysql   22u  IPv6  73360      0t0  TCP *:mysql (LISTEN)
mysqld  30811 mysql   81u  IPv6  78061      0t0  TCP server3:mysql->server4:38594 (ESTABLISHED)

###11.mysql MHA高可用###?

Master HA，對主節點進行監控，可實現自動故障轉移至其它從節點；通過提升某一從節點為新的主節點，基于主從復制實現，還需要客戶端配合實現，目前MHA主要支持一主多從的架構，要搭建MHA,要求一個復制集群中必須最少有三臺數據庫服務器，一主二從，即一臺充當master，一臺充當備用master，另外一臺充當從庫。

MHA工作原理
1 從宕機崩潰的master保存二進制日志事件（binlog events）
2 識別含有最新更新的slave
3 應用差異的中繼日志（relay log）到其他的slave
4 應用從master保存的二進制日志事件（binlog events）
5 提升一個slave為新的master
6 使其他的slave連接新的master進行復制

（1）創建一主兩從集群

①master配置

master配置：
清理數據
[root@server1 ~]# cd /data/mysql
[root@server1 mysql]# rm -fr *修改配置
[root@server1 mysql]# vim /etc/my.cnf
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
log_bin=binlog初始化
[root@server1 mysql]# mysqld --initialize --user=mysql啟動服務
[root@server1 mysql]# /etc/init.d/mysqld start配置master
[root@server1 mysql]# mysql -p
mysql> alter user root@localhost identified by 'westos';
Query OK, 0 rows affected (0.01 sec)mysql> grant replication slave on *.* to repl@'%' identified by 'westos';
Query OK, 0 rows affected, 1 warning (0.01 sec)

②slave配置

slave配置：
[root@server2 ~]# cd /data/mysql/
[root@server2 mysql]# rm -fr *[root@server2 mysql]# vim /etc/my.cnf
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
log_bin=binlog[root@server2 mysql]# mysqld --initialize --user=mysql[root@server2 mysql]# /etc/init.d/mysqld start[root@server2 mysql]# mysql -p
mysql>  alter user root@localhost identified by 'westos';
Query OK, 0 rows affected (0.01 sec)mysql> change master to master_host='192.168.56.11', master_user='repl', master_password='westos', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)mysql> start slave;
Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.56.11Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000002Read_Master_Log_Pos: 681Relay_Log_File: server2-relay-bin.000002Relay_Log_Pos: 888Relay_Master_Log_File: binlog.000002Slave_IO_Running: YesSlave_SQL_Running: Yesserver3節點的配置以此類推

③測試

master端新建內容，slave端同步

master：
mysql> create database westos;
Query OK, 1 row affected (0.00 sec)mysql> use westos
Database changedmysql> create table user_tb (-> username varchar(25) not null,-> password varchar(50) not null);
Query OK, 0 rows affected (0.01 sec)mysql> insert into user_tb values ('user1','111');
Query OK, 1 row affected (0.03 sec)mysql> insert into user_tb values ('user2','222');
Query OK, 1 row affected (0.00 sec)

slave:
mysql> select * from westos.user_tb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
+----------+----------+
2 rows in set (0.00 sec)

（2）MHA環境部署

①四臺主機安裝軟件

? Manager 工具包主要包括以下幾個工具：

masterha_check_ssh // 檢查 MHA 的 SSH 配置狀況

masterha_check_repl // 檢查 MySQL 復制狀況

masterha_manger //啟動 MHA

masterha_check_status // 檢測當前 MHA 運行狀態

masterha_master_monitor //檢測 master 是否宕機

masterha_master_switch // 控制故障轉移（自動或者手動）

masterha_conf_host // 添加或刪除配置的 server 信息

? Node 工具包（由 MHA Manager 的腳本觸發，無需人為操作）主要包括以下幾個工具：

save_binary_logs // 保存和復制 master 的二進制日志

apply_diff_relay_logs // 識別差異的中繼日志事件并將其差異的事件應用于其他的 slave

filter_mysqlbinlog // 去除不必要的 ROLLBACK 事件（ MHA 已不再使用這個工具）

purge_relay_logs // 清除中繼日志（不會阻塞 SQL 線程）

在server4主機，關閉mysqlrouter服務。安裝MHA高可用的masterrpm包及其依賴性。并將MHAnode節點rpm包復制至其他三臺主機。在其他三臺主機上安裝rpm包。

server4上安裝管理端軟件
[root@server4 ~]# cd MHA-7/
[root@server4 MHA-7]# yum install -y *.rpm管理端配置ssh免密
[root@server4 MHA-7]# ssh-keygen
[root@server4 MHA-7]# ssh-copy-id server4把密鑰復制到各節點
[root@server4 ~]# scp -r .ssh/ server1:
[root@server4 ~]# scp -r .ssh/ server2:
[root@server4 ~]# scp -r .ssh/ server3:復制客戶端軟件
[root@server4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server1:
[root@server4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server2:
[root@server4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server3:server1、server2、server3上安裝客戶端軟件
[root@server1 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm

②MHA環境配置

在server4主機建立高可用目錄，編輯配置文件。

MHA配置
[root@server4 ~]# mkdir /etc/masterha  ##建立高可用目錄
[root@server4 ~]# vim /etc/masterha/app1.cnf
[server default]
user=root				#mysql管理員
password=westos		#mysql管理員密碼
ssh_user=root			#ssh免密用戶
repl_user=repl			#mysql主從復制用戶
repl_password=westos
master_binlog_dir=/data/mysql	#mysql數據庫目錄
remote_workdir=/tmp     #遠端mysql在發生切換時binlog的保存位置
secondary_check_script= masterha_secondary_check -s 192.168.76.12 -s 192.168.76.13
ping_interval=3         #發送ping包的時間間隔，默認是3秒，嘗試三次沒有回應的時候自動進行failover
# master_ip_failover_script= /script/masterha/master_ip_failover
# shutdown_script= /script/masterha/power_manager
# report_script= /script/masterha/send_report
# master_ip_online_change_script= /script/masterha/master_ip_online_change
manager_workdir=/etc/masterha
manager_log=/etc/masterha/manager.log[server1]
hostname=192.168.76.11
candidate_master=1
check_repl_delay=0[server2]
hostname=192.168.76.12
candidate_master=1    #指定failover時此slave會接管master，即使數據不是最新的。
check_repl_delay=0[server3]
hostname=192.168.76.13
no_master=1    #始終不作為master

?在server1主機進入數據庫，設置mysql管理員權限，slave節點會自動同步

在server1master上設置mysql管理員權限，slave節點會自動同步
mysql> grant all on *.* to root@'%' identified by 'westos';
Query OK, 0 rows affected, 1 warning (0.00 sec)

③測試

檢測各節點ssh免密連接
[root@server4 ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnf
檢測主從復制集群狀態
[root@server4 ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf

（3）故障切換

MHA 的故障切換過程，共包括以下的步驟：

1. 配置文件檢查階段，這個階段會檢查整個集群配置文件配置

2. 宕機的 master 處理，這個階段包括虛擬 ip 摘除操作，主機關機操作

3. 復制 dead maste 和最新 slave 相差的 relay log ，并保存到 MHA Manger 具體的目錄下

4. 識別含有最新更新的 slave

5. 應用從 master 保存的二進制日志事件（ binlog events ）

6. 提升一個 slave 為新的 master 進行復制

7. 使其他的 slave 連接新的 master 進行復制

MHA 在線切換的大概過程：

1. 檢測復制設置和確定當前主服務器

2. 確定新的主服務器

3. 阻塞寫入到當前主服務器

4. 等待所有從服務器趕上復制

5. 授予寫入到新的主服務器

6. 重新設置從服務器

為了保證數據完全一致性，在最快的時間內完成切換， MHA 的在線切換必須滿足以下條

件才會切換成功，否則會切換失敗。

1. 所有 slave 的 IO 線程都在運行

2. 所有 slave 的 SQL 線程都在運行

3. 所有的 show slave status 的輸出中 Seconds_Behind_Master 參數小于或者等于

running_updates_limit 秒，如果在切換過程中不指定 running_updates_limit, 那么默認

情況下 running_updates_limit 為 1 秒。

4. 在 master 端，通過 show processlist 輸出，沒有一個更新花費的時間大于 running_updates_limit秒

①手動切換

master正常

手動切換（master正常）
[root@server4 ~]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=192.168.56.12 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

master故障

手動切換（master故障）
手動停止master節點上的msql服務
[root@server2 ~]# /etc/init.d/mysqld stop[root@server4 masterha]#  masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=192.168.56.12 --dead_master_port=3306 --new_master_host=192.168.56.11 --new_master_port=3306 --ignore_last_failover故障節點需要手動修復，重新加入主從集群
[root@server2 ~]# /etc/init.d/mysqld start
[root@server2 ~]# mysql -pwestosmysql> change master to master_host='192.168.56.11', master_user='repl', master_password='westos', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

②自動切換

自動切換
故障切換后會生成lock文件，需要手動刪除
[root@server4 masterha]# rm -f app1.failover.complete啟動manger程序，并打入后臺運行，完成切換任務后進程會自動退出
[root@server4 masterha]# masterha_manager --conf=/etc/masterha/app1.cnf &在進行自動切換之前，必須保證主從集群正常，一主兩從加入故障切換腳本，需要有可執行權限[root@server4 ~]# mv master_ip_failover master_ip_online_change /usr/local/bin/
[root@server4 ~]# chmod +x /usr/local/bin/master_ip_*
[root@server4 ~]# ll /usr/local/bin/
total 8
-rwxr-xr-x 1 root root 2158 Feb 26 11:20 master_ip_failover
-rwxr-xr-x 1 root root 3815 Feb 26 11:21 master_ip_online_change修改故障切換腳本中的vip
[root@server4 ~]# vim /usr/local/bin/master_ip_failover
[root@server4 ~]# vim /usr/local/bin/master_ip_online_change
...
my $vip = '192.168.76.100/24';
...修改配置文件加上故障切換腳本的訪問路徑
[root@server4 ~]# vim  /etc/masterha/app1.cnf
[server default]
user=root
password=westos
ssh_user=root
repl_user=repl
repl_password=westos
master_binlog_dir=/data/mysql
remote_workdir=/tmp
secondary_check_script= masterha_secondary_check -s 192.168.56.12 -s 192.168.56.13
ping_interval=3
master_ip_failover_script= /usr/local/bin/master_ip_failover
# shutdown_script= /script/masterha/power_manager
# report_script= /script/masterha/send_report
master_ip_online_change_script= /usr/local/bin/master_ip_online_change
manager_workdir=/etc/masterha
manager_log=/etc/masterha/manager.log[server1]
hostname=192.168.56.11
candidate_master=1
check_repl_delay=0[server2]
hostname=192.168.56.12
candidate_master=1
check_repl_delay=0[server3]
hostname=192.168.56.13
no_master=1測試
給master端添加一個vip，然后停掉master服務
[root@server1 ~]# ip a a 192.168.76.100/24 dev eth0
[root@server1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!server4的日志里顯示server1master掛掉，server2變為master
[root@server4 masterha]# ls
app1.cnf  app1.failover.complete  manager.log
[root@server4 masterha]# cat manager.log
...
From:
192.168.76.11(192.168.76.11:3306) (current master)+--192.168.76.12(192.168.76.12:3306)+--192.168.76.13(192.168.76.13:3306)To:
192.168.76.12(192.168.76.12:3306) (new master)+--192.168.76.13(192.168.76.13:3306)
...VIP被加到新的master上
[root@server2 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope hostvalid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:61:99:04 brd ff:ff:ff:ff:ff:ffinet 192.168.76.12/24 brd 192.168.76.255 scope global eth0valid_lft forever preferred_lft foreverinet 192.168.76.100/24 scope global secondary eth0valid_lft forever preferred_lft foreverinet6 fe80::20c:29ff:fe61:9904/64 scope linkvalid_lft forever preferred_lft forever