MySQL HA高可用

MySQL HA工作原理

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

環境

四臺機器安裝數據庫服務

[root@Manager ~]#yum install -y mariadb-server
[root@Master ~]#yum install -y mariadb-server
[root@Slave-1 ~]#yum install -y mariadb-server
[root@Slave-2 ~]#yum install -y mysql-server

將Manager 設置成時間主服務器

[root@Manager ~]#vim /etc/chrony.conf
server 172.22.0.1 iburst
...
allow 192.168.0.0/16
...
local stratum 10

[root@Manager ~]#systemctl restart chronyd

其余節點向Manager 進行時間同步

[root@Master ~]#ntpdate 192.168.36.7
 9 May 19:40:23 ntpdate[7326]: step time server 192.168.36.7 offset -28798.440970 sec
....

 注：如果沒有ntpdate這個命令還需要安裝ntpdate軟件包
 [root@Master ~]#yum install -y ntpdate

主從復制搭建

Master、Slave修改配置文件并重啟服務

[root@Master ~]#cat /etc/my.cnf
[mysqld]
server_id=2
skip_name_resolve
log-bin=/data/bin/mysql-bin

[root@Slave-1 ~]#cat /etc/my.cnf
[mysqld]
server_id=3
read_only
log-bin=/data/bin/mysql-bin
relay_log_purge=0
skip_name_resolve

[root@Slave-2 ~]#cat /etc/my.cnf
[mysqld]
server_id=4
read_only
log-bin=/data/bin/mysql-bin
relay_log_purge=0
skip_name_resolve

[root@Master ~]#systemctl restart mariadb
[root@Slave-1 ~]#systemctl restart mariadb
[root@Slave-2 ~]# service mysqld restart

所有節點添加管理的用戶賬號

添加參與復制的賬號

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

添加能遠程連接的管理員賬號

MariaDB [(none)]> grant all on *.* to mhauser@'192.168.36.%' identified by 'centos';
Query OK, 0 rows affected (0.00 sec)

兩個從節點進行同步配置

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.36.17',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='centos',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.01 sec)

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.36.17
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
....
          Exec_Master_Log_Pos: 245
....
                Last_IO_Errno: 0
....
               Last_SQL_Errno: 0
....
             Master_Server_Id: 2
1 row in set (0.00 sec)

四臺機器設置key驗證

生成私鑰文件
[root@Manager ~]#ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Created directory '/root/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:jxpqYJdn4u7++CD8CLdUtv6oxLZ/0w0KawCWmiwrpE4 root@Manager
The key's randomart image is:
+---[RSA 2048]----+
|                 |
|                 |
|  .              |
|.o               |
|+o  o.  S        |
|+=+o=.o .o       |
|=EO=o*.o.o.      |
|=* B==+o. .      |
|o.=O@==.         |
+----[SHA256]-----+

生成公鑰文件
[root@Manager ~]#ssh-copy-id 192.168.36.7
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.36.7 (192.168.36.7)' can't be established.
ECDSA key fingerprint is SHA256:PpNjNgoCvgIqG8A1Cl2apgSHQWiY3auqlPdpuJuhE4Y.
ECDSA key fingerprint is MD5:35:97:a4:a0:3d:9a:67:52:bd:61:56:f1:b7:b5:01:59.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.36.7's password:

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '192.168.36.7'"
and check to make sure that only the key(s) you wanted were added.

將秘鑰傳送至遠程主機

[root@Manager ~]#scp -rp .ssh 192.168.36.17:/root/
[root@Manager ~]#scp -rp .ssh 192.168.36.27:/root/
[root@Manager ~]#scp -rp .ssh 192.168.36.37:/root/

Manager安裝MHA軟件

將事先準備好的軟件拷貝到主機中

[root@Manager ~]#ll m*
-rw-r--r-- 1 root root 87119 Nov 10  2017 mha4mysql-manager-0.56-0.el6.noarch.rpm
-rw-r--r-- 1 root root 36326 Nov 10  2017 mha4mysql-node-0.56-0.el6.noarch.rpm

使用yum軟件安裝，由于存在EPEL源的依賴包，所以安裝之前需先啟用EPEL源

[root@Manager ~]#yum repolist
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
epel                                                                            | 4.7 kB  00:00:00
(1/3): epel/group_gz                                                            |  88 kB  00:00:00
(2/3): epel/updateinfo                                                          | 994 kB  00:00:00
(3/3): epel/primary_db                                                          | 6.7 MB  00:00:01
repo id                                          repo name                                       status
CentOS                                           CentOS                                          10,019
epel                                             epel                                            13,139
repolist: 23,158

[root@Manager ~]#yum install *.rpm -y

所有主從節點安裝node包

[root@Master ~]#rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
準備中...                          ################################# [100%]
正在升級/安裝...
   1:mha4mysql-node-0.56-0.el6        ################################# [100%]

[root@Slave-1 ~]#rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
準備中...                          ################################# [100%]
正在升級/安裝...
   1:mha4mysql-node-0.56-0.el6        ################################# [100%]

[root@Slave-2 ~]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
Preparing...                ########################################### [100%]
   1:mha4mysql-node         ########################################### [100%]

Manager修改配置文件

[root@Manager ~]#mkdir /etc/mha
[root@Manager ~]#vim /etc/mha/app1.conf
[server default]
user=mhauser
password=magedu
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=repluser
repl_password=magedu
ping_interval=1
[server1]
hostname=192.168.36.17
candidate_master=1
[server2]
hostname=192.168.36.27
candidate_master=1
[server3]
hostname=192.168.36.37
candidate_master=1

檢查ssh協議是否配置成功

[root@Manager ~]#masterha_check_ssh --conf=/etc/mha/app1.conf
Thu May  9 21:03:34 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu May  9 21:03:34 2019 - [info] Reading application default configuration from /etc/mha/app1.conf..
....
Warning: Permanently added '192.168.36.27' (RSA) to the list of known hosts.
Thu May  9 21:03:36 2019 - [debug]   ok.
Thu May  9 21:03:37 2019 - [info] All SSH connection tests passed successfully.

檢查主從復制有沒有問題

[root@Manager ~]#masterha_check_repl --conf=/etc/mha/app1.conf
Thu May  9 21:04:15 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu May  9 21:04:15 2019 - [info] Reading application default configuration from /etc/mha/app1.conf..
....
MySQL Replication Health is NOT OK!

啟動執行manager

[root@Manager ~]#masterha_manager --conf=/etc/mha/app1.conf
Thu May  9 21:05:28 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu May  9 21:05:28 2019 - [info] Reading application default configuration from /etc/mha/app1.conf..
Thu May  9 21:05:28 2019 - [info] Reading server configuration from /etc/mha/app1.conf..

宕機測試

1、導入SQL函數語句，使數據處于增加狀態

2、kill -9 殺死Master中mysqld父進程，同時子進程也消失不見，實現master宕機，此時發現Master遷移至從節點

3、此時master服務器為Slave-1節點

4、管理員自動將read_only修改為0

MHA只執行一次任務，所以master宕機，更換的新的master需要刪除配置文件中的read_only

如果宕機的master修好重新啟動，建議選擇當slave從節點