Mysql 高可用(MHA)-讀寫分離(Atlas)

Friend發表於2021-02-26
MySql

Mysql 高可用(MHA)-讀寫分離(Atlas)

1. 搭建主從複製(一主兩從)

1.1 準備環境

1 主庫:10.0.0.51/db01
2 從庫:10.0.0.52/db02,10.0.0.53/db03

1.2 清理環境

 1 systemctl stop  mysqld
 2 rm -rf /data/mysql_3306/*
 3 rm -rf /binlog/
 4 mkdir /binlog/
 5 
 6 建立相關目錄與授權
 7 主庫操作:開啟binlog 和GTID
 8 mkdir -p /binlog/
 9 mkdir -p /var/log/mysql/
10 touch /var/log/mysql/mysql.err
11 chown -R mysql.mysql /var/log/mysql/
12 chown -R mysql.mysql /binlog/
13 
14 從庫操作:從庫不需要開啟binlog
15 mkdir -p /var/log/mysql/
16 touch /var/log/mysql/mysql.err
17 chown -R mysql.mysql /var/log/mysql/

1.3 準備配置⽂件

 1 a. 主庫  (開啟binlog和GTID)
 2 cat > /etc/my.cnf <<EOF
 3 [mysqld]
 4 user=mysql
 5 datadir=/data/mysql_3306
 6 basedir=/opt/mysql/
 7 socket=/tmp/mysql.sock
 8 port=3306
 9 log_error=/var/log/mysql/mysql.err
10 server_id=51
11 log_bin=/binlog/mysql-bin
12 autocommit=0
13 binlog_format=row
14 gtid-mode=on
15 enforce-gtid-consistency=true
16 log-slave-updates=1
17 [mysql]
18 socket=/tmp/mysql.sock
19 [client]
20 socket=/tmp/mysql.sock
21 EOF
22 
23 b.從庫 (開啟GTID,不用開啟binlog)
24 cat > /etc/my.cnf <<EOF
25 [mysqld]
26 user=mysql
27 datadir=/data/mysql_3306
28 basedir=/opt/mysql/
29 socket=/tmp/mysql.sock
30 port=3306
31 log_error=/var/log/mysql/mysql.err
32 server_id=52
33 autocommit=0
34 gtid-mode=on
35 enforce-gtid-consistency=true
36 log-slave-updates=1
37 [mysql]
38 socket=/tmp/mysql.sock
39 [client]
40 socket=/tmp/mysql.sock
41 EOF
42 
43 c.從庫 (開啟GTID,不用開啟binlog)
44 cat > /etc/my.cnf <<EOF
45 [mysqld]
46 user=mysql
47 datadir=/data/mysql_3306
48 basedir=/opt/mysql/
49 socket=/tmp/mysql.sock
50 port=3306
51 log_error=/var/log/mysql/mysql.err
52 server_id=53
53 autocommit=0
54 gtid-mode=on
55 enforce-gtid-consistency=true
56 log-slave-updates=1
57 [mysql]
58 socket=/tmp/mysql.sock
59 [client]
60 socket=/tmp/mysql.sock
61 EOF

1.4 初始化資料

1 mysqld --initialize-insecure --user=mysql --basedir=/opt/mysql --datadir=/data/mysql_3306/

1.5 啟動資料庫

 1 systemctl start mysql
 2 檢視是否啟動
 3 netstat -lntup |grep 3306
 4 
 5 如果是剛裝的mysql,啟動mysql需要以下方法才可以啟動
 6 方法一:cp /opt/mysql/support-files/mysql.server  /etc/init.d/mysqld    
 7       chkconfig --add mysqld
 8       systemctl start mysqld
 9       
10 方法二: /etc/init.d/mysqld start

1.6 設定資料庫密碼

1 mysqladmin password

1.7 建立遠端複製⽤戶 (db01操作)

 1 mysql> grant replication slave on *.* to repl@'10.0.0.%' identified by '123456abcd';
 2 Query OK, 0 rows affected, 1 warning (0.01 sec)
 3 
 4 mysql> select user,host from mysql.user;
 5 +---------------+-----------+
 6 | user          | host      |
 7 +---------------+-----------+
 8 | repl          | 10.0.0.%  |
 9 | mysql.session | localhost |
10 | mysql.sys     | localhost |
11 | root          | localhost |
12 +---------------+-----------+
13 4 rows in set (0.00 sec)

1.8 構建主從

 1 檢視主庫GTID 位置
 2 mysql> show master status;
 3 +------------------+----------+--------------+------------------+------------------------------------------+
 4 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
 5 +------------------+----------+--------------+------------------+------------------------------------------+
 6 | mysql-bin.000004 |      704 |              |                  | 86ef6764-6e12-11eb-ac19-000c29ee4d81:1-2 |
 7 +------------------+----------+--------------+------------------+------------------------------------------+
 8 1 row in set (0.00 sec)
 9 
10 從庫執行以下命令:
11 change master to
12 master_host='10.0.0.52',
13 master_user='repl',
14 master_password='123456abcd',
15 MASTER_AUTO_POSITION=1;
16 
17 開啟主從複製
18 start slave;
19 
20 確認啟動是否OK
21 mysql> start slave;
22 Query OK, 0 rows affected (0.00 sec)
23 
24 mysql> show slave status\G
25 *************************** 1. row ***************************
26                Slave_IO_State: Waiting for master to send event
27                   Master_Host: 10.0.0.51
28                   Master_User: repl
29                   Master_Port: 3306
30                 Connect_Retry: 60
31               Master_Log_File: mysql-bin.000004
32           Read_Master_Log_Pos: 704
33                Relay_Log_File: db02-relay-bin.000002
34                 Relay_Log_Pos: 917
35         Relay_Master_Log_File: mysql-bin.000004
36              Slave_IO_Running: Yes
37             Slave_SQL_Running: Yes
38               Replicate_Do_DB: 
39           Replicate_Ignore_DB: 
40            Replicate_Do_Table: 
41        Replicate_Ignore_Table: 
42       Replicate_Wild_Do_Table: 
43   Replicate_Wild_Ignore_Table: 
44                    Last_Errno: 0
45                    Last_Error: 
46                  Skip_Counter: 0
47           Exec_Master_Log_Pos: 704
48               Relay_Log_Space: 1123
49               Until_Condition: None
50                Until_Log_File: 
51                 Until_Log_Pos: 0
52            Master_SSL_Allowed: No
53            Master_SSL_CA_File: 
54            Master_SSL_CA_Path: 
55               Master_SSL_Cert: 
56             Master_SSL_Cipher: 
57                Master_SSL_Key: 
58         Seconds_Behind_Master: 0
59 Master_SSL_Verify_Server_Cert: No
60                 Last_IO_Errno: 0
61                 Last_IO_Error: 
62                Last_SQL_Errno: 0
63                Last_SQL_Error: 
64   Replicate_Ignore_Server_Ids: 
65              Master_Server_Id: 51
66                   Master_UUID: 86ef6764-6e12-11eb-ac19-000c29ee4d81
67              Master_Info_File: /data/mysql_3306/master.info
68                     SQL_Delay: 0
69           SQL_Remaining_Delay: NULL
70       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
71            Master_Retry_Count: 86400
72                   Master_Bind: 
73       Last_IO_Error_Timestamp: 
74      Last_SQL_Error_Timestamp: 
75                Master_SSL_Crl: 
76            Master_SSL_Crlpath: 
77            Retrieved_Gtid_Set: 86ef6764-6e12-11eb-ac19-000c29ee4d81:1-2
78             Executed_Gtid_Set: 86ef6764-6e12-11eb-ac19-000c29ee4d81:1-2
79                 Auto_Position: 1
80          Replicate_Rewrite_DB: 
81                  Channel_Name: 
82            Master_TLS_Version: 
83 1 row in set (0.00 sec)

1.9 解除從庫

1 stop slave;
2 reset slave all;

2.MHA環境準備

主庫:10.0.0.51
從庫:10.0.0.52,10.0.0.53

2.0  MHA 介紹

 1 1.Master HA，對主節點進行監控，可實現自動故障轉 移至其它從節點；通過提升某一從節點為新的主節點，基於主從複製實現，還需要客戶端配合實現，目前MHA主要支援一主多從的架構，要搭建MHA,要求一個複製叢集中必須最少有 三臺資料庫伺服器，一主二從，即一臺充當master，一臺充當備用master，另外一臺充當從庫，如果財大氣粗，也可以用一臺專門的伺服器來當MHA監控管理伺服器
 2 
 3 2.MHA工作原理
 4     1 從當機崩潰的master儲存二進位制日誌事件（binlog events） 
 5     2 識別含有最新更新的slave 
 6     3 應用差異的中繼日誌（relay log）到其他的slave 
 7     4 應用從master儲存的二進位制日誌事件（binlog events） 
 8     5 提升一個slave為新的master 
 9     6 使其他的slave連線新的master進行復制 
10     
11     注意：MHA需要基於ssh，key驗證登入方法    
12     
13     
14 3.MHA軟體由兩部分組成，Manager工具包和Node工具包，具體的說明如下。
15  1).Manager工具包主要包括以下幾個工具：
16     masterha_check_ssh              檢查MHA的SSH配置狀況
17     masterha_check_repl             檢查MySQL複製狀況
18     masterha_manger                 啟動MHA
19     masterha_check_status           檢測當前MHA執行狀態
20     masterha_master_monitor         檢測master是否當機
21     masterha_master_switch          控制故障轉移（自動或者手動）
22     masterha_conf_host              新增或刪除配置的server資訊
23     
24  2).Node工具包（這些工具通常由MHA Manager的指令碼觸發，無需人為操作）主要包括以下幾個工具：
25     save_binary_logs                儲存和複製master的二進位制日誌
26     apply_diff_relay_logs           識別差異的中繼日誌事件並將其差異的事件應用於其他的slave
27     filter_mysqlbinlog              去除不必要的ROLLBACK事件（MHA已不再使用這個工具）
28     purge_relay_logs                清除中繼日誌（不會阻塞SQL執行緒）
29     
30    自定義擴充套件:
31     secondary_check_script：通過多條網路路由檢測master的可用性；
32 　   master_ip_failover_script：更新application使用的masterip；
33 　　 report_script：傳送報告;
34 　　 init_conf_load_script：載入初始配置引數；
35 　　 master_ip_online_change_script;更新master節點ip地址。

2.1 搭建關鍵的軟連結(51,52,53都需要設定)

1 ln -s /opt/mysql-5.7.28/bin/mysqlbinlog /usr/bin/mysqlbinlog
2 ln -s /opt/mysql-5.7.28/bin/mysql /usr/bin/mysql

2.2 配置互信(不用祕鑰可以登入ssh)

 1 db01:
 2   rm -rf /root/.ssh
 3   ssh-keygen
 4   cd /root/.ssh
 5   mv id_rsa.pub authorized_keys
 6   scp -r /root/.ssh 10.0.0.52:/root
 7   scp -r /root/.ssh 10.0.0.53:/root
 8   
 9 各節點驗證:(ssh無祕鑰登入)
10 db01:
11 ssh 10.0.0.51 date           登入並檢視時間
12 ssh 10.0.0.52 date
13 ssh 10.0.0.53 date
14 db02:
15 ssh 10.0.0.51 date
16 ssh 10.0.0.52 date
17 ssh 10.0.0.53 date
18 db03:
19 ssh 10.0.0.51 date
20 ssh 10.0.0.52 date
21 ssh 10.0.0.53 date

2.3 各節點安裝 node軟體和依賴包

1 yum install perl-DBD-MySQL -y
2 rpm -ivh  mha4mysql-node-0.56-0.el6.noarch.rpm
3 
4 [root@db01 ~00:50:55]# rpm -ivh  mha4mysql-node-0.56-0.el6.noarch.rpm
5 Preparing...                          ################################# [100%]
6 Updating / installing...
7    1:mha4mysql-node-0.56-0.el6        ################################# [100%]

2.4 在主庫db01中建立mha需要的使用者

1 grant all privileges on *.* to mha@'10.0.0.%' identified by '123456abcd';
2 
3 select user,host from mysql.user;

2.5 Manager軟體安裝（db03)

1 yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
2 rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
3 
4 [root@db03 ~00:53:36]# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
5 Preparing...                          ################################# [100%]
6 Updating / installing...
7    1:mha4mysql-manager-0.56-0.el6     ################################# [100%]

2.6 配置檔案準備(db03)

 1 # 建立配置檔案目錄
 2 mkdir -p /etc/mha
 3 # 建立日誌目錄
 4 mkdir -p /var/log/mha/app1
 5 # 編輯mha配置檔案
 6 cat > /etc/mha/app1.cnf <<EOF
 7 [server default]
 8 manager_log=/var/log/mha/app1/manager 
 9 manager_workdir=/var/log/mha/app1 
10 master_binlog_dir=/binlog                                  #主庫binlog位置
11 user=mha                                                   #mha使用者
12 password=123456abcd 
13 ping_interval=2                                            #每隔2秒掃描一次
14 repl_password=123456abcd
15 repl_user=repl                                             #主從複製使用者
16 ssh_user=root                                              #無祕鑰使用使用者
17 [server1] 
18 hostname=10.0.0.51
19 port=3306 
20 [server2] 
21 hostname=10.0.0.52
22 port=3306
23 [server3]
24 hostname=10.0.0.53
25 port=3306
26 EOF

2.7 狀態檢查（db03)

  1 1. masterha_check_ssh --conf=/etc/mha/app1.cnf
  2 
  3 [root@db03 ~00:59:38]# masterha_check_ssh --conf=/etc/mha/app1.cnf
  4 Sun Feb 14 00:59:41 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
  5 Sun Feb 14 00:59:41 2021 - [info] Reading application default configuration from /etc/mha/app1.cnf..
  6 Sun Feb 14 00:59:41 2021 - [info] Reading server configuration from /etc/mha/app1.cnf..
  7 Sun Feb 14 00:59:41 2021 - [info] Starting SSH connection tests..
  8 Sun Feb 14 00:59:42 2021 - [debug] 
  9 Sun Feb 14 00:59:41 2021 - [debug]  Connecting via SSH from root@10.0.0.51(10.0.0.51:22) to root@10.0.0.52(10.0.0.52:22)..
 10 Sun Feb 14 00:59:41 2021 - [debug]   ok.
 11 Sun Feb 14 00:59:41 2021 - [debug]  Connecting via SSH from root@10.0.0.51(10.0.0.51:22) to root@10.0.0.53(10.0.0.53:22)..
 12 Sun Feb 14 00:59:41 2021 - [debug]   ok.
 13 Sun Feb 14 00:59:42 2021 - [debug] 
 14 Sun Feb 14 00:59:41 2021 - [debug]  Connecting via SSH from root@10.0.0.52(10.0.0.52:22) to root@10.0.0.51(10.0.0.51:22)..
 15 Sun Feb 14 00:59:41 2021 - [debug]   ok.
 16 Sun Feb 14 00:59:41 2021 - [debug]  Connecting via SSH from root@10.0.0.52(10.0.0.52:22) to root@10.0.0.53(10.0.0.53:22)..
 17 Sun Feb 14 00:59:42 2021 - [debug]   ok.
 18 Sun Feb 14 00:59:43 2021 - [debug] 
 19 Sun Feb 14 00:59:42 2021 - [debug]  Connecting via SSH from root@10.0.0.53(10.0.0.53:22) to root@10.0.0.51(10.0.0.51:22)..
 20 Sun Feb 14 00:59:42 2021 - [debug]   ok.
 21 Sun Feb 14 00:59:42 2021 - [debug]  Connecting via SSH from root@10.0.0.53(10.0.0.53:22) to root@10.0.0.52(10.0.0.52:22)..
 22 Sun Feb 14 00:59:42 2021 - [debug]   ok.
 23 Sun Feb 14 00:59:43 2021 - [info] All SSH connection tests passed successfully.
 24 
 25 
 26 2. masterha_check_repl --conf=/etc/mha/app1.cnf
 27 Sun Feb 14 14:16:52 2021 - [info] Checking replication health on 10.0.0.52..
 28 Sun Feb 14 14:16:52 2021 - [info]  ok.
 29 Sun Feb 14 14:16:52 2021 - [info] Checking replication health on 10.0.0.53..
 30 Sun Feb 14 14:16:52 2021 - [info]  ok.
 31 Sun Feb 14 14:16:52 2021 - [warning] master_ip_failover_script is not defined.
 32 Sun Feb 14 14:16:52 2021 - [warning] shutdown_script is not defined.
 33 Sun Feb 14 14:16:52 2021 - [info] Got exit code 0 (Not master dead).
 34 MySQL Replication Health is OK.
 35 
 36 
 37 報錯:
 38 [root@db03 ~00:59:43]# masterha_check_repl --conf=/etc/mha/app1.cnf
 39 Sun Feb 14 01:00:24 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
 40 Sun Feb 14 01:00:24 2021 - [info] Reading application default configuration from /etc/mha/app1.cnf..
 41 Sun Feb 14 01:00:24 2021 - [info] Reading server configuration from /etc/mha/app1.cnf..
 42 Sun Feb 14 01:00:24 2021 - [info] MHA::MasterMonitor version 0.56.
 43 Sun Feb 14 01:00:25 2021 - [info] GTID failover mode = 1
 44 Sun Feb 14 01:00:25 2021 - [info] Dead Servers:
 45 Sun Feb 14 01:00:25 2021 - [info] Alive Servers:
 46 Sun Feb 14 01:00:25 2021 - [info]   10.0.0.51(10.0.0.51:3306)
 47 Sun Feb 14 01:00:25 2021 - [info]   10.0.0.52(10.0.0.52:3306)
 48 Sun Feb 14 01:00:25 2021 - [info]   10.0.0.53(10.0.0.53:3306)
 49 Sun Feb 14 01:00:25 2021 - [info] Alive Slaves:
 50 Sun Feb 14 01:00:25 2021 - [info]   10.0.0.52(10.0.0.52:3306)  Version=5.7.28 (oldest major version between slaves) log-bin:disabled
 51 Sun Feb 14 01:00:25 2021 - [info]     GTID ON
 52 Sun Feb 14 01:00:25 2021 - [info]     Replicating from 10.0.0.51(10.0.0.51:3306)
 53 Sun Feb 14 01:00:25 2021 - [info]   10.0.0.53(10.0.0.53:3306)  Version=5.7.28 (oldest major version between slaves) log-bin:disabled
 54 Sun Feb 14 01:00:25 2021 - [info]     GTID ON
 55 Sun Feb 14 01:00:25 2021 - [info]     Replicating from 10.0.0.51(10.0.0.51:3306)
 56 Sun Feb 14 01:00:25 2021 - [info] Current Alive Master: 10.0.0.51(10.0.0.51:3306)
 57 Sun Feb 14 01:00:25 2021 - [info] Checking slave configurations..
 58 Sun Feb 14 01:00:25 2021 - [info]  read_only=1 is not set on slave 10.0.0.52(10.0.0.52:3306).
 59 Sun Feb 14 01:00:25 2021 - [warning]  log-bin is not set on slave 10.0.0.52(10.0.0.52:3306). This host cannot be a master.
 60 Sun Feb 14 01:00:25 2021 - [info]  read_only=1 is not set on slave 10.0.0.53(10.0.0.53:3306).
 61 Sun Feb 14 01:00:25 2021 - [warning]  log-bin is not set on slave 10.0.0.53(10.0.0.53:3306). This host cannot be a master.
 62 Sun Feb 14 01:00:25 2021 - [info] Checking replication filtering settings..
 63 Sun Feb 14 01:00:25 2021 - [info]  binlog_do_db= , binlog_ignore_db= 
 64 Sun Feb 14 01:00:25 2021 - [info]  Replication filtering check ok.
 65 Sun Feb 14 01:00:25 2021 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln361] None of slaves can be master. Check failover configuration file or log-bin settings in my.cnf
 66 Sun Feb 14 01:00:25 2021 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations.  at /usr/bin/masterha_check_repl line 48.
 67 Sun Feb 14 01:00:25 2021 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
 68 Sun Feb 14 01:00:25 2021 - [info] Got exit code 1 (Not master dead).
 69 
 70   SQL Replication Health is NOT OK!
 71   
 72   
 73 解決問題:
 74         [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln361] None of slaves can be master. Check failover configuration file or log-bin settings in my.cnf
 75         Sun Feb 14 01:00:25 2021 - [info]   10.0.0.52(10.0.0.52:3306)  Version=5.7.28 (oldest major version between slaves) log-bin:disabled
 76         Sun Feb 14 01:00:25 2021 - [info]   10.0.0.53(10.0.0.53:3306)  Version=5.7.28 (oldest major version between slaves) log-bin:disabled
 77         
 78    以上說明log-bin處於關閉的狀態,同時show variables like 'log_bin';可以看到log_bin處於OFF
 79    mysql> show variables like 'log_bin';
 80 +---------------+-------+
 81 | Variable_name | Value |
 82 +---------------+-------+
 83 | log_bin       | OFF   |
 84 +---------------+-------+
 85 1 row in set (0.00 sec)
 86      
 87 解決方法:
 88        在db02,db03的mysql 配置檔中寫入log_bin=master-bin,必須寫在server-id=[] 後面.
 89        [root@db03 ~14:16:52]# cat /etc/my.cnf
 90         [mysqld]
 91         user=mysql
 92         datadir=/data/mysql_3306
 93         basedir=/opt/mysql/
 94         socket=/tmp/mysql.sock
 95         port=3306
 96         log_error=/var/log/mysql/mysql.err
 97         server_id=53
 98         log_bin=master-bin
 99         autocommit=0
100         gti--mode=on
101         enforce-gtid-consistency=true
102         log-slave-updates=1
103         [mysql]
104         socket=/tmp/mysql.sock
105         [client]
106         socket=/tmp/mysql.sock
107  重新再測試下,問題已解決
108          [root@db03 ~14:16:52]# cat /etc/my.cnf
109         [mysqld]
110         user=mysql
111         datadir=/data/mysql_3306
112         basedir=/opt/mysql/
113         socket=/tmp/mysql.sock
114         port=3306
115         log_error=/var/log/mysql/mysql.err
116         server_id=53
117         log_bin=master-bin
118         autocommit=0
119         gti--mode=on
120         enforce-gtid-consistency=true
121         log-slave-updates=1
122         [mysql]
123         socket=/tmp/mysql.sock
124         [client]
125         socket=/tmp/mysql.sock

2.8 開啟MHA(db03)

1 nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &

2.9 檢視MHA狀態(db03)

1 masterha_check_status --conf=/etc/mha/app1.cnf
2 
3 [root@db03 ~14:30:54]# masterha_check_status --conf=/etc/mha/app1.cnf
4 app1 (pid:7808) is running(0:PING_OK), master:10.0.0.51

3.MHA 故障測試(主庫當機會自動選出新主庫)

 1 MHA Failover 過程原理:
 2    高可用最擅長的是為我們解決物理損壞,就是說主庫壞了可以很快從備份從庫中篩選出新的主庫,使資料正常執行.
 3 1.啟動Manager 
 4   呼叫masterha_manager指令碼啟動Manager程式
 5   
 6 2.監控:
 7   通過:masterha_master_monitor心跳檢測指令碼,資料庫節點,主要監控主庫.預設探測4次,每隔(ping_interval=2)秒,如果主庫還沒有心跳,認為主庫當機,進入failover過程.
 8   
 9 3. 選主:
10   [server2]
11   hostname=172.25.83.2
12   port=3306
13   candidate_master=1      #設定為候選master，如果設定該引數以後，發生主從切換以後將會將此從庫提升為主庫，即使這個主庫不是叢集中事件最新的slave
14   check_repl_delay=0      #預設情況下如果一個slave落後master 100M的relay logs的話，MHA將不會選擇該slave作為一個新的master，因為對於這個slave的恢復需要花費很長時間，通過設定check_repl_delay=0,MHA觸發切換在選擇一個新的master的時候將會忽略複製延時，這個引數對於設定了candidate_master=1的主機非常有用，因為這個候選主在切換的過程中一定是新的master
15   a. 優先順序(主觀),如果在節點配置時,加入了candidate_master=1 引數.如果備選主,日誌量落後mater太多(後master 100M 的ralay_1ogs的話,也不會被選擇成為新主master),也可以通過check_repl_delay=0,不檢查日誌落後的情景.
16   b. 日誌量最接近主庫
17   c. 日誌量一樣,按配置檔案順序來選擇主庫master.
18   
19 4. 日誌補償:
20   a. ssh 能連線上,通過save_binary_logs立即儲存缺失部分日誌到從庫(/var/tmp目錄下)並恢復
21   b. ssh 連線不了,兩個從庫進行relay_log日誌diff(apply_diff_relay_logs)差異補償.
22   
23 5. 主從身份切換,所有從庫取消和原有主庫的複製關係(stop slave;reset slave all),新主庫與剩下從庫構建主從複製.
24 
25 6. 故障庫自動被剔除叢集(通過master_conf_host 從配置資訊中去掉)
26 
27 7. MHA是一次性的高可用,Failover 後,Manager 自動退出(需要手動再次加入叢集)
28 以上是MHA的基礎環境所有具備的功能.
29 不足的地方:
30 1.應該透明(IP地址自動切換)
31 2.資料補償(資料不能丟失)
32 3.自動提醒
33 4.自愈功能(待開發)
34   思路:MHA + K8S +Operator 官方  ,   8.0 MGR + Mysqlsh

4.MHA應用透明VIP功能

1 說明:只能同機房使用,無法跨機房跨網路
2 如果想用跨機房跨網路 可以用keepalived,需要用candidate_master=1和check_repl_delay=0來配合,防止VIP和主庫不在一個節點上.

4.1配置引數(db03)

 1 將master_ip_failover_script= /usr/local/bin/master_ip_failover寫入到mha配置檔中
 2 
 3 [root@db03 ~00:27:23]# cat /etc/mha/app1.cnf 
 4 [server default]
 5 master_ip_failover_script= /usr/local/bin/master_ip_failover
 6 manager_log=/var/log/mha/app1/manager
 7 manager_workdir=/var/log/mha/app1
 8 master_binlog_dir=/binlog
 9 password=123456abcd
10 ping_interval=2
11 repl_password=123456abcd
12 repl_user=repl
13 ssh_user=root
14 user=mha
15 
16 [server1]
17 hostname=10.0.0.51
18 port=3306
19 
20 [server2]
21 hostname=10.0.0.52
22 port=3306
23 
24 [server3]
25 hostname=10.0.0.53
26 port=3306

4.2 修改指令碼

 1 vim /usr/local/bin/master_ip_failover
 2 my $vip = '10.0.0.55/24';  
 3 my $key = "1";
 4 my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
 5 my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
 6 
 7 a. 需要轉換下中文字元
 8 dos2unix /usr/local/bin/master_ip_failover
 9 
10 [root@db03 ~00:51:52]# dos2unix /usr/local/bin/master_ip_failover
11 dos2unix: converting file /usr/local/bin/master_ip_failover to Unix format ...
12 b. 賦予x許可權
13  chmod +x /usr/local/bin/master_ip_failover

4.3 手動在主庫master 中新建一個VIP.網路卡名稱一定要與配置文件中的一樣

 1 db01:10.0.0.51 操作
 2 ifconfig eth0:1 10.0.0.55/24
 3 
 4 [root@db01 ~01:02:14]# ifconfig eth0:1 10.0.0.55/24
 5 [root@db01 ~01:02:38]# ip a
 6 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
 7     link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
 8     inet 127.0.0.1/8 scope host lo
 9        valid_lft forever preferred_lft forever
10     inet6 ::1/128 scope host 
11        valid_lft forever preferred_lft forever
12 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
13     link/ether 00:0c:29:ee:4d:81 brd ff:ff:ff:ff:ff:ff
14     inet 10.0.0.51/24 brd 10.0.0.255 scope global noprefixroute eth0
15        valid_lft forever preferred_lft forever
16     inet 10.0.0.55/24 brd 10.0.0.255 scope global secondary eth0:1
17        valid_lft forever preferred_lft forever
18     inet6 fe80::f2eb:f691:47ea:8c4e/64 scope link tentative noprefixroute dadfailed 
19        valid_lft forever preferred_lft forever
20     inet6 fe80::967b:20ed:e702:f7c2/64 scope link tentative noprefixroute dadfailed 
21        valid_lft forever preferred_lft forever
22     inet6 fe80::fda8:a693:1736:211/64 scope link noprefixroute 
23        valid_lft forever preferred_lft forever

4.4 重啟MHA(db03操作)

a. 先停止
masterha_stop --conf=/etc/mha/app1.cnf   

b. 再啟動
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &

c.檢視下VIP是否還是在主庫51上或者檢視master是否為51
[root@db03 ~14:30:54]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:7808) is running(0:PING_OK), master:10.0.0.51

4.5 測試VIP漂移

 1 1.將主庫master mysql 停掉
 2   systemctl stop mysqld
 3   
 4 2.檢視mha 日誌
 5   tail -f /var/log/mha/app1/manager 
 6 
 7 3.檢視VIP IP已經漂移到52,說明已經將52做為新主庫master.
 8 
 9 4.主庫51上檢視主從複製,VIP 都已要沒有了,同時53庫上的/etc/mha/app1.cnf [server 1] 配置已經清除.
10 
11 5.將51庫的Mysql啟動來,再做主從複製,主庫為52.同時/etc/mha/app1.cnf  [server 1] 增加進去.
12     change master to
13     master_host='10.0.0.52',
14     master_user='repl',
15     master_password='123456abcd',
16     MASTER_AUTO_POSITION=1; 
17     再開啟start slave;
18     檢視show slave status\G
19     
20 6.在主庫52中執行show slave hosts;可以檢視到有幾臺從庫
21   mysql> show slave hosts;
22     +-----------+------+------+-----------+--------------------------------------+
23     | Server_id | Host | Port | Master_id | Slave_UUID                           |
24     +-----------+------+------+-----------+--------------------------------------+
25     |        53 |      | 3306 |        51 | b03f4593-6e18-11eb-a543-000c291a88d9 |
26     |        52 |      | 3306 |        51 | 6a0cfd41-6e18-11eb-a1b0-000c29d2d3c1 |
27     +-----------+------+------+-----------+--------------------------------------+
28     2 rows in set (0.00 sec)

master_ip_failover(perl)指令碼 

 1 cat /usr/local/bin/master_ip_failover
 2 #!/usr/bin/env perl
 3 use strict;
 4 use warnings FATAL =>'all';
 5 
 6 use Getopt::Long;
 7 
 8 my (
 9 $command,          $ssh_user,        $orig_master_host, $orig_master_ip,
10 $orig_master_port, $new_master_host, $new_master_ip,    $new_master_port
11 );
12 
13 my $vip = '10.0.0.55/24';  # Virtual IP
14 my $key = "1";
15 my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
16 my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
17 my $exit_code = 0;
18 
19 GetOptions(
20 'command=s'          => \$command,
21 'ssh_user=s'         => \$ssh_user,
22 'orig_master_host=s' => \$orig_master_host,
23 'orig_master_ip=s'   => \$orig_master_ip,
24 'orig_master_port=i' => \$orig_master_port,
25 'new_master_host=s'  => \$new_master_host,
26 'new_master_ip=s'    => \$new_master_ip,
27 'new_master_port=i'  => \$new_master_port,
28 );
29 
30 exit &main();
31 
32 sub main {
33 
34 #print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
35 
36 if ( $command eq "stop" || $command eq "stopssh" ) {
37 
38         # $orig_master_host, $orig_master_ip, $orig_master_port are passed.
39         # If you manage master ip address at global catalog database,
40         # invalidate orig_master_ip here.
41         my $exit_code = 1;
42         eval {
43             print "\n\n\n***************************************************************\n";
44             print "Disabling the VIP - $vip on old master: $orig_master_host\n";
45             print "***************************************************************\n\n\n\n";
46 &stop_vip();
47             $exit_code = 0;
48         };
49         if ($@) {
50             warn "Got Error: $@\n";
51             exit $exit_code;
52         }
53         exit $exit_code;
54 }
55 elsif ( $command eq "start" ) {
56 
57         # all arguments are passed.
58         # If you manage master ip address at global catalog database,
59         # activate new_master_ip here.
60         # You can also grant write access (create user, set read_only=0, etc) here.
61 my $exit_code = 10;
62         eval {
63             print "\n\n\n***************************************************************\n";
64             print "Enabling the VIP - $vip on new master: $new_master_host \n";
65             print "***************************************************************\n\n\n\n";
66 &start_vip();
67             $exit_code = 0;
68         };
69         if ($@) {
70             warn $@;
71             exit $exit_code;
72         }
73         exit $exit_code;
74 }
75 elsif ( $command eq "status" ) {
76         print "Checking the Status of the script.. OK \n";
77         `ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
78         exit 0;
79 }
80 else {
81 &usage();
82         exit 1;
83 }
84 }
85 
86 # A simple system call that enable the VIP on the new master
87 sub start_vip() {
88 `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
89 }
90 # A simple system call that disable the VIP on the old_master
91 sub stop_vip() {
92 `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
93 }
94 
95 sub usage {
96 print
97 "Usage: master_ip_failover –command=start|stop|stopssh|status –orig_master_host=host –orig_master_ip=ip –orig_master_port=po
98 rt –new_master_host=host –new_master_ip=ip –new_master_port=port\n";
99 }

5.binlog server (資料補償)

1 db03 主機操作
2 資料補償,儘可能不丟失資料
3 有專門一臺binlog server時時拉取Master 的Mysql-binlog,如果主master 當機了,從庫直接從binlog server讀取資料.
4 缺點:可能會拉低master 效能,所以盡能將binlog server裝在ssd上.

5.1 引數設定

1 vim /etc/mha/app1.cnf
2 [binlog1]
3 no_master=1            #不參與選主
4 hostname=10.0.0.53     #binlog server 主機Ip
5 master_binlog_dir=/data/mysql/binlog   #這個目錄需要與master_binlog_dir=/binlog 目錄不一樣.

5.2 建立對應目錄

1 mkdir -p /data/mysql/binlog
2 chown -R mysql.mysql /data/*

5.3 拉取主庫日誌

 1 必須先進入到binlog目錄中
 2 cd /data/mysql/binlog
 3 mysqlbinlog -R --host=10.0.0.51 --user=mha --password=123456abcd --raw --stop-never mysql-bin.000001 &
 4 mysql-bin.000001:
 5     這個日誌是為了測試用,如果生產環境中,因為有很多日誌也有可能沒有這個日誌,所以需要用show master status;來檢視下當前日誌,或者flush logs;重新重新整理一個新的日誌起點.
 6 mysql> show master status;
 7 +------------------+----------+--------------+------------------+------------------------------------------+
 8 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
 9 +------------------+----------+--------------+------------------+------------------------------------------+
10 | mysql-bin.000006 |      194 |              |                  | 86ef6764-6e12-11eb-ac19-000c29ee4d81:1-9 |
11 +------------------+----------+--------------+------------------+------------------------------------------+
12 1 row in set (0.00 sec)
13 
14 [root@db03 /data/mysql/binlog00:26:46]# ps -ef |grep mysqlbinlog
15 root      13131   7587  0 00:24 pts/0    00:00:00 mysqlbinlog -R --host=10.0.0.51 --user=mha --password=x xxxxxxxx --raw --stop-never mysql-bin.000001
16 root      13318   7587  0 00:29 pts/0    00:00:00 grep --color=auto mysqlbinlog
17 
18 [root@db03 /data/mysql/binlog00:20:15]# ll
19 total 24
20 -rw-r----- 1 root root  177 Feb 16 00:19 mysql-bin.000001
21 -rw-r----- 1 root root  177 Feb 16 00:19 mysql-bin.000002
22 -rw-r----- 1 root root  177 Feb 16 00:19 mysql-bin.000003
23 -rw-r----- 1 root root 1940 Feb 16 00:19 mysql-bin.000004
24 -rw-r----- 1 root root  217 Feb 16 00:19 mysql-bin.000005
25 -rw-r----- 1 root root  194 Feb 16 00:19 mysql-bin.000006
26 
27 [root@db01 /binlog00:20:39]# ll
28 total 28
29 -rw-r----- 1 mysql mysql  177 Feb 13 23:41 mysql-bin.000001
30 -rw-r----- 1 mysql mysql  177 Feb 13 23:45 mysql-bin.000002
31 -rw-r----- 1 mysql mysql  177 Feb 13 23:46 mysql-bin.000003
32 -rw-r----- 1 mysql mysql 1940 Feb 14 01:41 mysql-bin.000004
33 -rw-r----- 1 mysql mysql  217 Feb 14 14:13 mysql-bin.000005
34 -rw-r----- 1 mysql mysql  194 Feb 15 21:47 mysql-bin.000006
35 -rw-r----- 1 mysql mysql  150 Feb 15 21:47 mysql-bin.index
36 
37 在主庫中先檢視日誌
38 show master status;
39 再重新整理下log
40 flush logs;
41 檢視從庫53就會有一個新的binlog日誌

5.4 重啟MHA

1 a. 先停止
2 masterha_stop --conf=/etc/mha/app1.cnf   
3 
4 b. 再啟動
5 nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
6 
7 c.檢視下VIP是否還是在主庫51上或者檢視master是否為51
8 [root@db03 ~14:30:54]# masterha_check_status --conf=/etc/mha/app1.cnf
9 app1 (pid:7808) is running(0:PING_OK), master:10.0.0.51

6.傳送郵件提醒

6.1 引數設定(db03)

 1 report_script=/usr/local/bin/sendEmail-v1.56/sendemail.sh
 2 寫入配置文件中db03
 3 [root@db03 ~20:54:53]# cat /etc/mha/app1.cnf 
 4 [server default]
 5 manager_log=/var/log/mha/app1/manager
 6 manager_workdir=/var/log/mha/app1
 7 master_binlog_dir=/binlog
 8 master_ip_failover_script=/usr/local/bin/master_ip_failover
 9 report_script=/usr/local/bin/sendEmail-v1.56/sendemail.sh
10 password=123456abcd
11 ping_interval=2
12 repl_password=123456abcd
13 repl_user=repl
14 ssh_user=root
15 user=mha
16 
17 [server1]
18 hostname=10.0.0.51
19 port=3306
20 
21 [server2]
22 hostname=10.0.0.52
23 port=3306
24 
25 [server3]
26 hostname=10.0.0.53
27 port=3306
28 
29 [binlog1]
30 no_master=1
31 hostname=10.0.0.53     
32 master_binlog_dir=/data/mysql/binlog

6.2 解壓sendEmail 軟體

 1 解壓軟體到指定位置
 2 tar xf sendEmail-v1.56.tar.gz -C /usr/local/bin/
 3 
 4 [root@db03 /usr/local/bin/sendEmail-v1.5621:30:31]# ll
 5 -rw-r--r-- 1 root root 13329 Sep 30  2009 CHANGELOG
 6 -rw-r--r-- 1 root root  7949 Sep 30  2009 README
 7 -rw-r--r-- 1 root root  4701 Sep 30  2009 README-BR.txt
 8 -rwxr-xr-x 1 root root 80213 Sep 30  2009 sendEmail
 9 lrwxrwxrwx 1 root root     9 Sep 30  2009 sendEmail.pl -> sendEmail
10 -rwxr-xr-x 1 root root   280 Feb 26 19:38 sendemail.sh
11 -rw-r--r-- 1 root root  1229 Sep 30  2009 TODO
12 
13 report_script=/usr/local/bin/sendEmail-v1.56/sendemail.sh
14 解釋:MHA 最後會讀取report_script裡的指令碼,其實就是呼叫sendemail.sh指令碼,而sendemail.sh 指令碼又是呼叫sendEmail 命令來傳送郵件.

6.3 發郵件指令碼測試

 1 #1.下載一個sendEmail-v1.56.tar.gz
 2 
 3 #2. 編寫一個測試指令碼./sendemail.sh 看看測試郵件是否可以發出.確認沒有問題.
 4 vim sendemail.sh
 5 #!/bin/bash
 6 /usr/local/bin/sendEmail-v1.56/sendEmail -f "friend_lss@163.com" -t "282474670@qq.com" -s "smtp.163.com" -u "myTest" -o tls=no -o message-content-type=html -o message-charset=utf-8 -xu "friend_lss@163.com" -xp "OTUWLQOUFMHVKFRL" -m "MHA 出現問題,請及時處理"
 7 
 8 #3. 直接用命令方式
 9 sendEmail -f "friend_lss@163.com" -t "282474670@qq.com" -s "smtp.163.com" -u "myTest" -o tls=no -o message-content-type=html -o message-charset=utf-8 -xu "friend_lss@163.com" -xp "OTUWLQOUFMHVKFRL" -m "MHA 出現問題,請及時處理"  
10 
11 #4. sendEmail 引數解釋
12 -f linuxyw@163.com是發件郵箱，必須是真實的，要不發不出去的，與sendmail不一樣
13 -t 63780668@qq.com linuxyw@163.com 這二個郵件是接收郵件的郵箱地址，可以是一個，也可以是多個，用空格隔開就行，實現郵件群發
14 -s smtp.163.com 這是163郵箱的smtp地址，如果用其它企業郵箱，必須指定smtp地址，要不發不出郵件
15 -u hello  hello是郵件主題
16 -xu linuxyw 發件郵件的登陸使用者名稱，必須是真實的，否則系統登陸不上去，傳送不了郵件
17 -xp 123456pass 發件郵箱的登陸使用者密碼，必須是真實的，否則系統登陸不上去，傳送不了郵件
18 -m www.linuxyw.com  這裡的[www.linuxyw.com](http://www.linuxyw.com/)是郵件的內容，你可以輸入你所要寫的郵件內容
19 
20 #5. 執行指令碼報錯處理
21 ==================================================================================
22 執行指令碼報錯:
23 [root@db03 /usr/local/bin/sendEmail-v1.5623:20:19]# ./sendemail.sh 
24 
25 *******************************************************************
26 
27  Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
28  is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
29  possibly with SSL_ca_file|SSL_ca_path for verification.
30  If you really don't want to verify the certificate and keep the
31  connection open to Man-In-The-Middle attacks please set
32  SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
33 
34 *******************************************************************
35 
36   at /usr/local/bin/sendEmail-v1.56/sendEmail line 1906.
37 invalid SSL_version specified at /usr/share/perl5/vendor_perl/IO/Socket/SSL.pm line 444.
38 ================================================================================================
39 #處理方法:
40 在centos7中，perl版本是5.16,要降級到5.10.但是降級這個perl,依賴關係很多。經過反覆搜尋，發現有如下解決辦法：
41 a、解除安裝現有的perl版本，安裝老版本(不推薦)
42 b、增加引數-o tls=no 選項
43 [root@zabbix02 sendEmail-v1.56]# sendEmail -f  username@163.com -t username@qq.com -s smtp.163.com -u "I am zabbix" -o tls=no -o message-content-type=html -o message-charset=utf8 -xu  username@163.com -xp password -m "hello zabbix"
44 Feb 16 15:43:04 zabbix02 sendEmail[18761]: Email was sent successfully!

6.4 測試郵件是否OK

 1 a. 主庫Master 執行systemctl stop mysql, 使主庫當機
 2 
 3 b. 檢視是否有郵件提醒
 4    或者看郵件日誌
 5    
 6 c. 檢視VIP 是否轉移
 7 
 8 ==============================================================
 9 ----- Failover Report -----
10 
11 app1: MySQL Master failover 10.0.0.51(10.0.0.51:3306) to 10.0.0.52(10.0.0.52:3306) succeeded
12 
13 Master 10.0.0.51(10.0.0.51:3306) is down!
14 
15 Check MHA Manager logs at db03:/var/log/mha/app1/manager for details.
16 
17 Started automated(non-interactive) failover.
18 Invalidated master IP address on 10.0.0.51(10.0.0.51:3306)
19 Selected 10.0.0.52(10.0.0.52:3306) as a new master.
20 10.0.0.52(10.0.0.52:3306): OK: Applying all logs succeeded.
21 10.0.0.52(10.0.0.52:3306): OK: Activated master IP address.
22 10.0.0.53(10.0.0.53:3306): OK: Slave started, replicating from 10.0.0.52(10.0.0.52:3306)
23 10.0.0.52(10.0.0.52:3306): Resetting slave info succeeded.
24 Master failover to 10.0.0.52(10.0.0.52:3306) completed successfully.
25 Fri Feb 26 19:49:45 2021 - [info] Sending mail..
26 Feb 26 19:49:47 db03 sendEmail[7962]: Email was sent successfully!

7. MHA故障排除思路

 1 如果主庫當機了,思路如下
 2 1. 檢視程式
 3    ps -ef |grep manager
 4    masterha_check_status --conf=/etc/mha/app1.cnf
 5    
 6 2. 檢查配置文件節點
 7    vim /etc/mha/app1.conf
 8    如果節點已經被移除了,說明切換過程已經大部分成功
 9    如果世點還在,說明切換過程卡在中間
10 
11 3. 看日誌
12    tail -f /var/log/mha/app1/manager
13    
14 4. 修復故障庫,把節點修復好
15    systemctl start mysqld
16    /etc/init.d/mysqld start 
17 5. 修復主從
18    將故障庫修好後手工加入已有的主從中,做為從庫
19    change master to
20    master_host='10.0.0.52',
21    master_user='repl',
22    master_password='123456abcd',
23    MASTER_AUTO_POSITION=1;
24    start slave;
25    
26 6. 修復配置文件(配置文件如果還在,則省略)
27    將被移除節點配置重新寫入配置中
28     [server1]
29     hostname=10.0.0.51
30     port=3306
31     
32 7. 檢查SSH 互信和Repl 主從關係
33    masterha_check_ssh --conf=/etc/mha/app1.cnf
34    masterha_check_repl --conf=/etc/mha/app1.cnf
35    如有報錯,解決報錯後再檢查
36    
37 8. 修復binlogserver (主庫當機了,binlogserver 也會停掉)
38     [root@db03 ~14:24:23]# cd /data/mysql/binlog/
39     [root@db03 /data/mysql/binlog14:24:31]# rm -rf ./*    #刪除已有的binlog日誌
40     拉取新主庫binlog資訊 (新主庫IP地址寫正確)
41     cd /data/mysql/binlog
42     mysqlbinlog -R --host=10.0.0.52 --user=mha --password=123456abcd --raw --stop-never mysql-bin.000001 &
43    
44 9. 檢查節點VIP 的狀態
45    如果不在,再的手工生成
46    
47 10. 啟動MHA
48     nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
49     
50 11. 檢視MHA 狀態
51     [root@db03 ~14:30:54]# masterha_check_status --conf=/etc/mha/app1.cnf
52     app1 (pid:7808) is running(0:PING_OK), master:10.0.0.52

8. 讀寫分離 Atlas

8.1 Atlas 安裝(db03)

1 rpm -ivh Atlas-2.2.1.el6.x86_64.rpm

8.2 配置Atlas

 1 cd  /usr/local/mysql-proxy/conf 
 2  同有一個test.cnf 配置文件,先做個備份一份
 3  [root@db03 /usr/local/mysql-proxy/conf20:59:31]# cat test.cnf
 4 [mysql-proxy]
 5 #帶#號的為非必需的配置專案
 6 #管理介面的使用者名稱
 7 admin-username = user
 8 #管理介面的密碼
 9 admin-password = pwd
10 #Atlas後端連線的MySQL主庫的IP和埠，可設定多項，用逗號分隔
11 proxy-backend-addresses =10.0.0.55:3306
12 #Atlas後端連線的MySQL從庫的IP和埠，@後面的數字代表權重，用來作負載均衡，若省略則預設為1，可設定多項，用逗號分隔
13 proxy-read-only-backend-addresses = 10.0.0.52:3306,10.0.0.53:3306
14 #使用者名稱與其對應的加密過的MySQL密碼，密碼使用PREFIX/bin目錄下的加密程式encrypt加密，下行的user1和user2為示例，將其替換為你的MySQL的使用者名稱和加密密碼！
15 #[root@db03 /usr/local/mysql-proxy/bin22:25:52]# ./encrypt repl:123456abcd
16 #rdCkrCmLhbFMY6YuvYplDg==
17 #[root@db03 /usr/local/mysql-proxy/bin22:26:06]# ./encrypt 123456abcd
18 #tyElBOCykN3FCr2uMcG5AQ==
19 #[root@db03 /usr/local/mysql-proxy/bin22:26:28]# ./encrypt mha
20 #O2jBXONX098=
21 pwds = repl:tyElBOCykN3FCr2uMcG5AQ==, mha:tyElBOCykN3FCr2uMcG5AQ==
22 #設定Atlas的執行方式，設為true時為守護程式方式，設為false時為前臺方式，一般開發除錯時設為false，線上執行時設為true,true後面不能有空格。
23 daemon = true
24 #設定Atlas的執行方式，設為true時Atlas會啟動兩個程式，一個為monitor，一個為worker，monitor在worker意外退出後會自動將其重啟，設為false時只有worker，沒有monitor，一般開發除錯時設為false，線上執行時設為true,true後面不能有空格。
25 keepalive = true
26 #工作執行緒數，對Atlas的效能有很大影響，可根據情況適當設定
27 event-threads = 8
28 #日誌級別，分為message、warning、critical、error、debug五個級別
29 log-level = message
30 #日誌存放的路徑
31 log-path = /usr/local/mysql-proxy/log
32 #SQL日誌的開關，可設定為OFF、ON、REALTIME，OFF代表不記錄SQL日誌，ON代表記錄SQL日誌，REALTIME代表記錄SQL日誌且實時寫入磁碟，預設為OFF
33 sql-log = ON
34 #Atlas監聽的工作介面IP和埠
35 proxy-address = 0.0.0.0:33060
36 #Atlas監聽的管理介面IP和埠
37 admin-address = 0.0.0.0:2345
38 #預設字符集，設定該項後客戶端不再需要執行SET NAMES語句
39 charset = utf8

8.3. 啟動Atlas

 1 啟動:
 2 /usr/local/mysql-proxy/bin/mysql-proxyd test start
 3 重起:
 4 /usr/local/mysql-proxy/bin/mysql-proxyd test restart
 5 [root@db03 /usr/local/mysql-proxy/conf21:44:42]# /usr/local/mysql-proxy/bin/mysql-proxyd test restart
 6 OK: MySQL-Proxy of test is stopped
 7 OK: MySQL-Proxy of test is started
 8 
 9 [root@db03 /usr/local/mysql-proxy/conf21:03:27]# /usr/local/mysql-proxy/bin/mysql-proxyd test start
10 OK: MySQL-Proxy of test is started
11 
12 確認是否啟動
13 ps -ef | grep proxy
14 
15 [root@db03 /usr/local/mysql-proxy/conf21:05:02]# ps -ef | grep proxy
16 root       6281      1  0 12:54 ?        00:00:00 /usr/sbin/gssproxy -D
17 root      20514      1  0 21:05 ?        00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
18 root      20515  20514  0 21:05 ?        00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
19 root      20560   7774  0 21:06 pts/1    00:00:00 grep --color=auto proxy
20 
21 檢視埠
22 [root@db03 /usr/local/mysql-proxy/conf21:45:03]# netstat -lntup |grep proxy
23 tcp        0      0 0.0.0.0:2345            0.0.0.0:*               LISTEN      21790/mysql-proxy   
24 tcp        0      0 0.0.0.0:33060           0.0.0.0:*               LISTEN      21790/mysql-proxy

8.4 Atlas 配置說明

  1 Atlas執行需要依賴一個配置檔案（test.cnf）。在執行Atlas之前，需要對該檔案進行配置。Atlas的安裝目錄是/usr/local/mysql-proxy，進入安裝目錄下的conf目錄，可以看到已經有一個名為test.cnf的預設配置檔案，我們只需要修改裡面的某些配置項，不需要從頭寫一個配置檔案。
  2 
  3 配置範例及說明如下：
  4 [mysql-proxy]
  5 
  6 (必備，預設值即可)管理介面的使用者名稱
  7 
  8 admin-username = user
  9 
 10 (必備，預設值即可)管理介面的密碼
 11 
 12 admin-password = pwd
 13 
 14 (必備，根據實際情況配置)主庫的IP和埠
 15 
 16 proxy-backend-addresses = 192.168.0.12:3306
 17 
 18 (非必備，根據實際情況配置)從庫的IP和埠，@後面的數字代表權重，用來作負載均衡，若省略則預設為1，可設定多項，用逗號分隔。如果想讓主庫也能分擔讀請求的話，只需要將主庫資訊加入到下面的配置項中。
 19 
 20 proxy-read-only-backend-addresses = 192.168.0.13:3306,192.168.0.14:3306
 21 
 22 (必備，根據實際情況配置)使用者名稱與其對應的加密過的MySQL密碼，密碼使用PREFIX/bin目錄下的加密程式encrypt加密，使用者名稱與密碼之間用冒號分隔。主從資料庫上需要先建立該使用者並設定密碼（使用者名稱和密碼在主從資料庫上要一致）。比如使用者名稱為myuser，密碼為mypwd，執行./encrypt mypwd結果為HJBoxfRsjeI=。如果有多個使用者用逗號分隔即可。則設定如下行所示：
 23 
 24 pwds = repl: HJBoxfRsjeI=,mha:HJBoxfRsjeI=
 25 
 26 （必備，預設值即可)Atlas的執行方式，設為true時為守護程式方式，設為false時為前臺方式，一般開發除錯時設為false，線上執行時設為true
 27 
 28 daemon = true
 29 
 30 (必備，預設值即可)設定Atlas的執行方式，設為true時Atlas會啟動兩個程式，一個為monitor，一個為worker，monitor在worker意外退出後會自動將其重啟，設為false時只有worker，沒有monitor，一般開發除錯時設為false，線上執行時設為true
 31 
 32 keepalive = true
 33 
 34 (必備，根據實際情況配置)工作執行緒數，推薦設定成系統的CPU核數的2至4倍
 35 
 36 event-threads = 4
 37 
 38 (必備，預設值即可)日誌級別，分為message、warning、critical、error、debug五個級別
 39 
 40 log-level = message
 41 
 42 (必備，預設值即可)日誌存放的路徑
 43 
 44 log-path = /usr/local/mysql-proxy/log
 45 
 46 (必備，根據實際情況配置)SQL日誌的開關，可設定為OFF、ON、REALTIME，OFF代表不記錄SQL日誌，ON代表記錄SQL日誌，該模式下日誌重新整理是基於緩衝區的，當日志填滿緩衝區後，才將日誌資訊刷到磁碟。REALTIME用於除錯，代表記錄SQL日誌且實時寫入磁碟，預設為OFF
 47 
 48 sql-log = OFF
 49 
 50 (可選項，可不設定）慢日誌輸出設定。當設定了該引數時，則日誌只輸出執行時間超過sql-log-slow（單位：ms)的日誌記錄。不設定該引數則輸出全部日誌。
 51 
 52 sql-log-slow = 10
 53 
 54 (可選項，可不設定）關閉不活躍的客戶端連線設定。當設定了該引數時，Atlas會主動關閉經過'wait-timeout'時間後一直未活躍的連線。單位：秒
 55 
 56 wait-timeout = 10
 57 
 58 (必備，預設值即可)Atlas監聽的工作介面IP和埠
 59 
 60 proxy-address = 0.0.0.0:1234
 61 
 62 (必備，預設值即可)Atlas監聽的管理介面IP和埠 admin-address = 0.0.0.0:2345
 63 
 64 (可選項，可不設定)分表設定，此例中person為庫名，mt為表名，id為分表欄位，3為子表數量，可設定多項，以逗號分隔，若不分表則不需要設定該項，子表需要事先建好，子表名稱為表名_數字，數字範圍為[0,子表數-1]，如本例裡，子表名稱為mt_0、mt_1、mt_2
 65 
 66 tables = person.mt.id.3
 67 
 68 (可選項，可不設定)預設字符集，若不設定該項，則預設字符集為latin1
 69 
 70 charset = utf8
 71 
 72 (可選項，可不設定)允許連線Atlas的客戶端的IP，可以是精確IP，也可以是IP段，以逗號分隔，若不設定該項則允許所有IP連線，否則只允許列表中的IP連線
 73 
 74 client-ips = 127.0.0.1, 192.168.1
 75 
 76 (可選項，極少需要)Atlas前面掛接的LVS的物理網路卡的IP(注意不是虛IP)，若有LVS且設定了client-ips則此項必須設定，否則可以不設定
 77 
 78 lvs-ips = 192.168.1.1
 79 
 80 2.    重要配置說明
 81 以下幾項配置引數對效能和正常執行起到重要作用，需要正確設定。
 82 
 83 (1)執行緒數
 84 
 85 event-threads項設定，過小無法充分發揮多核CPU的效能，過大造成不必要的執行緒切換開銷，推薦設定為CPU的核數。
 86 
 87 (2)最小空閒連線數(2.x以上版本不需要該項，1.x版本需要)
 88 
 89 min-idle-connections項設定，過小則在高併發下會有報錯，過大雖然不報錯但在測試時不容易看出讀寫分離效果，推薦設定為比客戶端的併發峰值稍大，詳見《配置引數詳解》。上面的配置範例是針對Atlas 2.X版本，沒有該選項。對於Atlas 1.X版本的配置檔案，需要加入該配置選項。
 90 
 91 3. 可選配置說明
 92 以下幾項可以設定，也可以使用預設值，區別不大。
 93 
 94 (1)Atlas的工作埠
 95 
 96 proxy-address項配置，例如proxy-address = 0.0.0.0:1234代表客戶端應該使用1234這個埠連線Atlas來傳送SQL請求。
 97 
 98 (2)Atlas的管理埠
 99 
100 admin-address項配置，例如admin-address = 0.0.0.0:2345代表DBA應該使用2345這個埠連線Atlas來執行運維管理操作。
101 
102 (3)管理介面的使用者名稱和密碼
103 
104 admin-username項和admin-password項設定，這兩項是用來進入Atlas的管理介面的，與後端連線的MySQL沒有關係，所以可以任意設定，不需要MySQL在配置上做任何改動。
105 
106 (4)日誌級別
107 
108 以log-level項配置，分為message、warning、critical、error、debug五個級別。
109 
110 (5)日誌路徑
111 
112 以log-path項配置，如log-path = /usr/local/mysql-proxy/log。
113 
114 [mysql-proxy]
115 
116 管理介面的使用者名稱
117 admin-username = user
118 管理介面的密碼
119 admin-password = pwd
120 Atlas後端連線的MySQL主庫的IP和埠，可設定多項，用逗號分隔
121 proxy-backend-addresses = 192.168.1.1:3306
122 從庫
123 proxy-read-only-backend-addresses = 192.168.1.2:3306@1
124 使用者名稱和密碼配置項，需要和主從複製配置的使用者名稱和密碼配置一樣
125 r1:+jKsgB3YAG8=, user2:GS+tr4TPgqc=
126 後臺執行
127 daemon = true keepalive = false
128 工作執行緒數，對Atlas的效能有很大影響，可根據情況適當設定
129 event-threads = 4
130 日誌級別，分為message、warning、critical、error、debug五個級別
131 log-level = error
132 日誌存放的路徑
133 log-path = ./log
134 SQL日誌的開關，可設定為OFF、ON、REALTIME，OFF代表不記錄SQL日誌，ON代表記錄SQL日誌，REALTIME代表記錄SQL日>志且實時寫入磁碟，預設為OFF
135 sql-log = OFF
136 慢日誌輸出設定。當設定了該引數時，則日誌只輸出執行時間超過sql-log-slow（單位：ms)的日誌>記錄。不設定該引數則輸出全部日誌。
137 sql-log-slow = 1000
138 例項名稱，用於同一臺機器上多個Atlas例項間的區分
139 instance = web
140 Atlas監聽的工作介面IP和埠
141 proxy-address = 0.0.0.0:13470
142 Atlas監聽的管理介面IP和埠
143 admin-address = 0.0.0.0:23470
144 分表設定，此例中person為庫名，mt為表名，id為分表欄位，3為子表數量，可設定多項，以逗號分>隔，若不分表則不需要設定該項
145 tables = person.mt.id.3
146 預設字符集，設定該項後客戶端不再需要執行SET NAMES語句
147 charset = utf8
148 允許連線Atlas的客戶端的IP，可以是精確IP，也可以是IP段，以逗號分隔，若不設定該項則允許所>有IP連線，否則只允許列表中的IP連線
149 client-ips = 127.0.0.1, 192.168.1
150 Atlas前面掛接的LVS的物理網路卡的IP(注意不是虛IP)，若有LVS且設定了client-ips則此項必須設定>，否則可以不設定
151 lvs-ips = 192.168.1.1

8.5 測試 讀寫分離功能(那臺機器裝了Atlas就用哪臺) 

 1 db03 操作:用mha 使用者登入
 2 
 3 1. mysql -umha -p123456abcd -h 10.0.0.53 -P33060
 4 
 5 [root@db03 /usr/local/mysql-proxy/conf22:42:49]# mysql -umha -p123456abcd -h 10.0.0.53 -P33060
 6 mysql: [Warning] Using a password on the command line interface can be insecure.
 7 Welcome to the MySQL monitor.  Commands end with ; or \g.
 8 Your MySQL connection id is 1
 9 Server version: 5.0.81-log MySQL Community Server (GPL)
10 
11 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
12 
13 Oracle is a registered trademark of Oracle Corporation and/or its
14 affiliates. Other names may be trademarks of their respective
15 owners.
16 
17 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
18 
19 報錯:
20    登入密碼有問題
21 
22 解決問題:
23     Atlas 配置文件中的  pwds = repl:tyElBOCykN3FCr2uMcG5AQ==, mha:tyElBOCykN3FCr2uMcG5AQ==,這個密碼必須是加密過的,要不然是無法識別.加密方式為/usr/local/mysql-proxy/bin/下的encrypt 檔案加密
24  #使用者名稱與其對應的加密過的MySQL密碼，密碼使用PREFIX/bin目錄下的加密程式encrypt加密，下行的user1和user2為示例，將其替換為你的MySQL的使用者名稱和加密密碼！
25 #[root@db03 /usr/local/mysql-proxy/bin22:26:06]# ./encrypt 123456abcd
26 #tyElBOCykN3FCr2uMcG5AQ==
27 #[root@db03 /usr/local/mysql-proxy/bin22:26:28]# ./encrypt mha
28 #O2jBXONX098=
29 pwds = repl:tyElBOCykN3FCr2uMcG5AQ==, mha:tyElBOCykN3FCr2uMcG5AQ==
30 
31 2. 登入後測試讀寫分離:設定51,53為從庫,52為主庫
32   讀操作:
33      select @@server_id; 可以看到51,53一直輪循.
34    mysql> select @@server_id;
35 +-------------+
36 | @@server_id |
37 +-------------+
38 |          53 |
39 +-------------+
40 1 row in set (0.00 sec)
41 
42 mysql> select @@server_id;
43 +-------------+
44 | @@server_id |
45 +-------------+
46 |          51 |
47 +-------------+
48 1 row in set (0.00 sec)
49 
50 mysql> select @@server_id;
51 +-------------+
52 | @@server_id |
53 +-------------+
54 |          53 |
55 +-------------+
56 1 row in set (0.00 sec)
57 
58 mysql> select @@server_id;
59 +-------------+
60 | @@server_id |
61 +-------------+
62 |          51 |
63 +-------------+
64 1 row in set (0.00 sec)
65 
66 寫操作: 只會寫在db02 
67   begin;select @@server_id;commit;
68   
69 mysql>  begin;select @@server_id;commit;
70 Query OK, 0 rows affected (0.01 sec)
71 
72 +-------------+
73 | @@server_id |
74 +-------------+
75 |          52 |
76 +-------------+
77 1 row in set (0.00 sec)
78 
79 Query OK, 0 rows affected (0.00 sec)
80 
81 mysql>  begin;select @@server_id;commit;
82 Query OK, 0 rows affected (0.00 sec)
83 
84 +-------------+
85 | @@server_id |
86 +-------------+
87 |          52 |
88 +-------------+
89 1 row in set (0.00 sec)
90 
91 Query OK, 0 rows affected (0.01 sec)

8.6 Atlas 管理(db03)

a. 登入Atlas
登入Atlas 介面,使用者名稱: user 密碼:pwd   埠號:2345
  mysql -uuser -ppwd -h 10.0.0.53 -P2345

[root@db03 /usr/local/mysql-proxy/conf23:01:54]# mysql -uuser -ppwd -h 10.0.0.53 -P2345
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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.

b. 檢視Atlas 所有管理命令
mysql> select * from help;
+----------------------------+---------------------------------------------------------+
| command                    | description                                             |
+----------------------------+---------------------------------------------------------+
| SELECT * FROM help         | shows this help                                         |
| SELECT * FROM backends     | lists the backends and their state                      |
| SET OFFLINE $backend_id    | offline backend server, $backend_id is backend_ndx's id |
| SET ONLINE $backend_id     | online backend server, ...                              |
| ADD MASTER $backend        | example: "add master 127.0.0.1:3306", ...               |
| ADD SLAVE $backend         | example: "add slave 127.0.0.1:3306", ...                |
| REMOVE BACKEND $backend_id | example: "remove backend 1", ...                        |
| SELECT * FROM clients      | lists the clients                                       |
| ADD CLIENT $client         | example: "add client 192.168.1.2", ...                  |
| REMOVE CLIENT $client      | example: "remove client 192.168.1.2", ...               |
| SELECT * FROM pwds         | lists the pwds                                          |
| ADD PWD $pwd               | example: "add pwd user:raw_password", ...               |
| ADD ENPWD $pwd             | example: "add enpwd user:encrypted_password", ...       |
| REMOVE PWD $pwd            | example: "remove pwd user", ...                         |
| SAVE CONFIG                | save the backends to config file                        |
| SELECT VERSION             | display the version of Atlas                            |
+----------------------------+---------------------------------------------------------+

c. 管理命令
    檢視所有幫助
    SELECT * FROM help 
  
    檢視後端節點狀態
    SELECT * FROM backends

    檢視資料庫節點狀態
SELECT * FROM backends
  
  mysql> SELECT * FROM backends;
+-------------+----------------+-------+------+
| backend_ndx | address        | state | type |
+-------------+----------------+-------+------+
|           1 | 10.0.0.55:3306 | up    | rw   |
|           2 | 10.0.0.51:3306 | up    | ro   |
|           3 | 10.0.0.53:3306 | up    | ro   |
+-------------+----------------+-------+------+
3 rows in set (0.00 sec)

    上線或下線節點(對某節點需要做升級或者資料分析等操作時)
SET OFFLINE $backend_id    
SET ONLINE $backend_id

mysql> SET OFFLINE 2;
+-------------+----------------+---------+------+
| backend_ndx | address        | state   | type |
+-------------+----------------+---------+------+
|           2 | 10.0.0.51:3306 | offline | ro   |
+-------------+----------------+---------+------+
1 row in set (0.00 sec)

mysql> SET ONLINE 2;
+-------------+----------------+---------+------+
| backend_ndx | address        | state   | type |
+-------------+----------------+---------+------+
|           2 | 10.0.0.51:3306 | unknown | ro   |
+-------------+----------------+---------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM backends;
+-------------+----------------+-------+------+
| backend_ndx | address        | state | type |
+-------------+----------------+-------+------+
|           1 | 10.0.0.55:3306 | up    | rw   |
|           2 | 10.0.0.51:3306 | up    | ro   |
|           3 | 10.0.0.53:3306 | up    | ro   |
+-------------+----------------+-------+------+
3 rows in set (0.00 sec)


  新增或移除節點
  ADD MASTER $backend                 example: "add master 127.0.0.1:3306"
  ADD SLAVE $backend                  example: "add slave 127.0.0.1:3306"
  REMOVE BACKEND $backend_id 
  
mysql> REMOVE BACKEND 2;
Empty set (0.00 sec)

mysql> SELECT * FROM backends;
+-------------+----------------+-------+------+
| backend_ndx | address        | state | type |
+-------------+----------------+-------+------+
|           1 | 10.0.0.55:3306 | up    | rw   |
|           2 | 10.0.0.53:3306 | up    | ro   |
+-------------+----------------+-------+------+
2 rows in set (0.00 sec)

mysql> ADD SLAVE 10.0.0.51:3306;
Empty set (0.00 sec)

mysql> SELECT * FROM backends;
+-------------+----------------+-------+------+
| backend_ndx | address        | state | type |
+-------------+----------------+-------+------+
|           1 | 10.0.0.55:3306 | up    | rw   |
|           2 | 10.0.0.53:3306 | up    | ro   |
|           3 | 10.0.0.51:3306 | up    | ro   |
+-------------+----------------+-------+------+
3 rows in set (0.00 sec)
 1 | SELECT * FROM pwds         | lists the pwds                                          |
 2 | ADD PWD $pwd               | example: "add pwd user:raw_password", ...               |
 3 | ADD ENPWD $pwd             | example: "add enpwd user:encrypted_password", ...       |
 4 | REMOVE PWD $pwd 
 5 
 6 mysql>  SELECT * FROM pwds;
 7 +----------+--------------------------+
 8 | username | password                 |
 9 +----------+--------------------------+
10 | repl     | tyElBOCykN3FCr2uMcG5AQ== |
11 | mha      | tyElBOCykN3FCr2uMcG5AQ== |
12 +----------+--------------------------+
13 2 rows in set (0.00 sec)
14 
15 
16 企業應用案列:
17 開發人員申請一個應用使用者 app(  select  update  insert)  密碼123456abcd,要通過10網段登入
18 1. 在主庫中,建立使用者
19 grant select ,update,insert on *.* to app@'10.0.0.%' identified by '123456abcd';
20 2. 在atlas中新增生產使用者
21  方法一:  明文
22     ADD PWD $pwd     ---需要新增的使用者名稱稱,例如:ADD PWD app:123456abcd; 會自動加,並加密
23  
24  方法二:  密文
25     /usr/local/mysql-proxy/bin/encrypt  123456abcd 
26     ADD ENPWD $pwd      例: ADD ENPWD app:tyElBOCykN3FCr2uMcG5AQ== 
27  方法三:
28   /usr/local/mysql-proxy/bin/encrypt  123456abcd      ---->製作加密密碼
29   vim test.cnf
30   pwds = repl:3yb5jEku5h4=,mha:O2jBXONX098=,app:/iZxz+0GRoA=
31   /usr/local/mysql-proxy/bin/mysql-proxyd test restart
32   [root@db03 conf]# mysql -uapp -p123456abcd  -h 10.0.0.53 -P 33060

8.7  配置永久生效(以上改的任何配置機器重啟都會丟掉,相當於暫存在記憶體裡)

SAVE CONFIG;

 

相關文章