MHA高可用与Atlas读写分离

一、MySQL高可用(MHA)

MHA属于中小型企业的数据库架构,是一种主备架构;下一代数据库系统架构为多活架构;

1.MHA架构

MHA必须由一主两从(不同主机)组成

Manager工具包

  • masterha_manager 启动MHA
  • masterha_check_ssh 检查MHA的SSH配置情况
  • masterha_check_repl 检查MySQL复制状况
  • masterha_master_monitor 监控Master状态
  • masterha_check_status 检查当前MHA运行状态
  • masterha_master_switch 控制故障转移(自动/手动)
  • masterha_conf_host 添加或删除配置的server信息

Node工具包

这些工具通常由MHA Manager的脚本触发,无须人为操作
  • save_binary_logs 保存和复制master的二进制日志
  • apply_diff_reply_logs 识别差异的中继日志事件(可将差异事件用于其他数据库)
  • purge relay logs 清除中继日志(不会阻塞SQL线程)

2.MHA搭建流程

step01 配置节点互信;

ssh-keygen -t rsa #创建密钥对
mv ./.ssh/id_rsa.pub ./.ssh/authorized_keys #将管理端公钥改为被管理端公钥
scp -r /root/.ssh/ 节点IP地址:/root
[root@mha_manager ~]# rm -rf .ssh/*
[root@mha_manager ~]# ssh-keygen -t rsa
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:WpLDkCk9uCaUlM9PSROkSP0r402vft1znNAOv9TB4SE root@mha_manager
The key's randomart image is:
+---[RSA 2048]----+
| oo .o.          |
|o.o+.=           |
| +=.O o      E o |
|.  = B .      + o|
|. o o * S   .  + |
| o o + =   o .. .|
|  . = o . . *... |
|   . . o . o.*   |
|    .oo     o..  |
+----[SHA256]-----+
[root@mha_manager ~]# mv .ssh/id_rsa.pub .ssh/authorized_keys
[root@mha_manager ~]# scp -r /root/.ssh/ 10.0.0.153:/root
The authenticity of host '10.0.0.153 (10.0.0.153)' can't be established.
ECDSA key fingerprint is SHA256:4O+/HRUt2Qwcz4xXk3y+Y5It07gqAUNy//ju/dZH2Vc.
ECDSA key fingerprint is MD5:5b:e2:99:8c:b6:d6:88:85:2c:4a:84:65:4a:74:78:75.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.0.0.153' (ECDSA) to the list of known hosts.
root@10.0.0.153's password: 
id_rsa                                                                  100% 1679     2.0MB/s   00:00    
authorized_keys                                                         100%  398   376.2KB/s   00:00    
known_hosts                                                             100%  172   363.0KB/s   00:00    
[root@mha_manager ~]# scp -r /root/.ssh/ 10.0.0.154:/root
The authenticity of host '10.0.0.154 (10.0.0.154)' can't be established.
ECDSA key fingerprint is SHA256:4O+/HRUt2Qwcz4xXk3y+Y5It07gqAUNy//ju/dZH2Vc.
ECDSA key fingerprint is MD5:5b:e2:99:8c:b6:d6:88:85:2c:4a:84:65:4a:74:78:75.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.0.0.154' (ECDSA) to the list of known hosts.
root@10.0.0.154's password: 
id_rsa                                                                  100% 1679     1.7MB/s   00:00    
authorized_keys                                                         100%  398   392.2KB/s   00:00    
known_hosts                                                             100%  344   472.7KB/s   00:00    
[root@mha_manager ~]# scp -r /root/.ssh/ 10.0.0.155:/root
The authenticity of host '10.0.0.155 (10.0.0.155)' can't be established.
ECDSA key fingerprint is SHA256:4O+/HRUt2Qwcz4xXk3y+Y5It07gqAUNy//ju/dZH2Vc.
ECDSA key fingerprint is MD5:5b:e2:99:8c:b6:d6:88:85:2c:4a:84:65:4a:74:78:75.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.0.0.155' (ECDSA) to the list of known hosts.
root@10.0.0.155's password: 
id_rsa                                                                  100% 1679     1.6MB/s   00:00    
authorized_keys                                                         100%  398   925.9KB/s   00:00    
known_hosts                                                             100%  516   905.3KB/s   00:00
[root@mha_manager ~]# ssh 10.0.0.153 hostname
db03
[root@mha_manager ~]# ssh 10.0.0.154 hostname
db04
[root@mha_manager ~]# ssh 10.0.0.155 hostname
db05
[root@mha_manager ~]# ssh 10.0.0.150 hostname
The authenticity of host '10.0.0.150 (10.0.0.150)' can't be established.
ECDSA key fingerprint is SHA256:4O+/HRUt2Qwcz4xXk3y+Y5It07gqAUNy//ju/dZH2Vc.
ECDSA key fingerprint is MD5:5b:e2:99:8c:b6:d6:88:85:2c:4a:84:65:4a:74:78:75.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.0.0.150' (ECDSA) to the list of known hosts.
mha_manager
[root@db03 ~/.ssh]# rm -rf ./*
[root@db03 ~]# ssh 10.0.0.150 hostname
The authenticity of host '10.0.0.150 (10.0.0.150)' can't be established.
ECDSA key fingerprint is SHA256:4O+/HRUt2Qwcz4xXk3y+Y5It07gqAUNy//ju/dZH2Vc.
ECDSA key fingerprint is MD5:5b:e2:99:8c:b6:d6:88:85:2c:4a:84:65:4a:74:78:75.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.0.0.150' (ECDSA) to the list of known hosts.
mha_manager
[root@db03 ~]# ssh 10.0.0.153 hostname
db03
[root@db03 ~]# ssh 10.0.0.154 hostname
The authenticity of host '10.0.0.154 (10.0.0.154)' can't be established.
ECDSA key fingerprint is SHA256:4O+/HRUt2Qwcz4xXk3y+Y5It07gqAUNy//ju/dZH2Vc.
ECDSA key fingerprint is MD5:5b:e2:99:8c:b6:d6:88:85:2c:4a:84:65:4a:74:78:75.
Are you sure you want to continue connecting (yes/no)? tes 
Please type 'yes' or 'no': yes
Warning: Permanently added '10.0.0.154' (ECDSA) to the list of known hosts.
db04
[root@db03 ~]# ssh 10.0.0.155 hostname
The authenticity of host '10.0.0.155 (10.0.0.155)' can't be established.
ECDSA key fingerprint is SHA256:4O+/HRUt2Qwcz4xXk3y+Y5It07gqAUNy//ju/dZH2Vc.
ECDSA key fingerprint is MD5:5b:e2:99:8c:b6:d6:88:85:2c:4a:84:65:4a:74:78:75.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.0.0.155' (ECDSA) to the list of known hosts.
db05
[root@db04 ~]# rm .ssh/* -rf
[root@db04 ~]# ssh 10.0.0.150 hostname
mha_manager
[root@db04 ~]# ssh 10.0.0.153 hostname
db03
[root@db04 ~]# ssh 10.0.0.154 hostname
db04
[root@db04 ~]# ssh 10.0.0.155 hostname
db05
[root@db05 ~/.ssh]# rm -rf ./*
[root@db05 ~]# ssh 10.0.0.150 hostname
mha_manager
[root@db05 ~]# ssh 10.0.0.153 hostname
db03
[root@db05 ~]# ssh 10.0.0.154 hostname
db04
[root@db05 ~]# ssh 10.0.0.155 hostname
db05

step02 配置命令软连接;

ln -s 安装目录/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s 安装目录/bin/mysql /usr/bin/mysql
[root@db03 ~]# ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
[root@db03 ~]# ln -s /application/mysql/bin/mysql /usr/bin/mysql
[root@db03 ~]# ll /usr/bin/mysql*
lrwxrwxrwx 1 root root 28 Sep  6 20:38 /usr/bin/mysql -> /application/mysql/bin/mysql
lrwxrwxrwx 1 root root 34 Sep  6 20:37 /usr/bin/mysqlbinlog -> /application/mysql/bin/mysqlbinlog
[root@db04 ~]# ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
[root@db04 ~]# ln -s /application/mysql/bin/mysql /usr/bin/mysql
[root@db04 ~]# ll /usr/bin/mysql*
lrwxrwxrwx 1 root root 28 Sep  6 20:39 /usr/bin/mysql -> /application/mysql/bin/mysql
lrwxrwxrwx 1 root root 34 Sep  6 20:39 /usr/bin/mysqlbinlog -> /application/mysql/bin/mysqlbinlog
[root@db05 ~]# ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
[root@db05 ~]# ln -s /application/mysql/bin/mysql /usr/bin/mysql
[root@db05 ~]# ll /usr/bin/mysql*
lrwxrwxrwx 1 root root 28 Sep  6 20:42 /usr/bin/mysql -> /application/mysql/bin/mysql
lrwxrwxrwx 1 root root 34 Sep  6 20:42 /usr/bin/mysqlbinlog -> /application/mysql/bin/mysqlbinlog

step03 下载MHA软件并安装
MHA官网下载地址
GITHUB下载地址

  • Node节点
yum install -y perl-DBD-MySQL #安装依赖软件包
yum install -y mha4mysql-node-0.56-0.el6.noarch.rpm #安装Node软件
  • Manager节点
yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes #安装依赖软件包
yum install -y mha4mysql-manager-0.56-0.el6.noarch.rpm #安装Manager软件
[root@mha_manager /tmp]# yum install -y perl-DBD-MySQL
......
Complete!
[root@mha_manager /tmp]# yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
......
Complete!
[root@mha_manager /tmp]# yum install -y mha4mysql-node-0.56-0.el6.noarch.rpm 
......
Complete!
[root@mha_manager /tmp]# yum install -y mha4mysql-manager-0.56-0.el6.noarch.rpm
......
Complete!
[root@db03 /tmp]# yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
......
Complete!
[root@db03 /tmp]# yum install -y mha4mysql-node-0.56-0.el6.noarch.rpm
......
Complete!
[root@db04 /tmp]# yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
......
Complete!
[root@db04 /tmp]# yum install -y mha4mysql-node-0.56-0.el6.noarch.rpm
......
Complete!
[root@db05 /tmp]# yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
......
Complete!
[root@db05 /tmp]# yum install -y mha4mysql-node-0.56-0.el6.noarch.rpm
......
Complete!

step04 主库创建同步用户

mysql -e "grant all on *.* to 用户名@'1白名单' identified by '密码';"
[root@db03 /tmp]# mysql -e "grant all on *.* to mha@'10.0.0.%' identified by 'mha';"
[root@db03 /tmp]# mysql -e "show master status;"
+-------------------+----------+--------------+------------------+------------------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+-------------------+----------+--------------+------------------+------------------------------------------+
| sql-binlog.000002 |      730 |              |                  | 64f86a3b-d079-11e9-966b-000c29b53c72:1-2 |
+-------------------+----------+--------------+------------------+------------------------------------------+

step05 管理节点创建相关路径及编写配置文件

mkdir -p /etc/mha
mkdir -p /工作目录
vim /etc/mha/配置文件
[server default]
manager_log=/工作目录/日志文件
manager_workdir=/工作目录/
master_binlog_dir=主库二进制目日志录
user=同步用户
password=同步用户密码
ping_interval=2
repl_password=主从复制用户密码
repl_user=主从复制用户
ssh_user=SSH远程连接用户
[server1]
hostname=节点IP地址-1
port=端口号
[server2]
hostname=节点IP地址-2
port=端口号
[server3]
hostname=节点IP地址-3
port=端口号
[root@mha_manager /tmp]# mkdir -p /etc/mha /var/log/mha/app1
[root@mha_manager /tmp]# cd /etc/mha/
[root@mha_manager /etc/mha]# ll
total 0
[root@mha_manager /etc/mha]# vim /etc/mha/application_01.cnf
[root@mha_manager /var/log/mha/app1/manager]# cat /etc/mha/application_01.cnf 
[server default]
manager_log=/var/log/mha/app1/manager        
manager_workdir=/var/log/mha/app1            
master_binlog_dir=/data/mysql/3306/logs      
user=mha                                   
password=mha                               
ping_interval=1
repl_user=repl
repl_password=456
ssh_user=root                               
[server1]                                   
hostname=10.0.0.153
port=3306                                  
[server2]            
hostname=10.0.0.154
port=3306
[server3]
hostname=10.0.0.155
port=3306

step06 MHA环境节点互信检查

mastermha_check_ssh --conf=配置文件
[root@mha_manager ~]# masterha_check_ssh --conf=/etc/mha/application_01.cnf
Fri Sep  6 22:25:47 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Sep  6 22:25:47 2019 - [info] Reading application default configuration from /etc/mha/application_01.cnf..
Fri Sep  6 22:25:47 2019 - [info] Reading server configuration from /etc/mha/application_01.cnf..
Fri Sep  6 22:25:47 2019 - [info] Starting SSH connection tests..
Fri Sep  6 22:25:47 2019 - [debug] 
Fri Sep  6 22:25:47 2019 - [debug]  Connecting via SSH from root@10.0.0.153(10.0.0.153:22) to root@10.0.0.154(10.0.0.154:22)..
Fri Sep  6 22:25:47 2019 - [debug]   ok.
Fri Sep  6 22:25:47 2019 - [debug]  Connecting via SSH from root@10.0.0.153(10.0.0.153:22) to root@10.0.0.155(10.0.0.155:22)..
Fri Sep  6 22:25:47 2019 - [debug]   ok.
Fri Sep  6 22:25:48 2019 - [debug] 
Fri Sep  6 22:25:47 2019 - [debug]  Connecting via SSH from root@10.0.0.154(10.0.0.154:22) to root@10.0.0.153(10.0.0.153:22)..
Fri Sep  6 22:25:48 2019 - [debug]   ok.
Fri Sep  6 22:25:48 2019 - [debug]  Connecting via SSH from root@10.0.0.154(10.0.0.154:22) to root@10.0.0.155(10.0.0.155:22)..
Fri Sep  6 22:25:48 2019 - [debug]   ok.
Fri Sep  6 22:25:48 2019 - [debug] 
Fri Sep  6 22:25:48 2019 - [debug]  Connecting via SSH from root@10.0.0.155(10.0.0.155:22) to root@10.0.0.153(10.0.0.153:22)..
Fri Sep  6 22:25:48 2019 - [debug]   ok.
Fri Sep  6 22:25:48 2019 - [debug]  Connecting via SSH from root@10.0.0.155(10.0.0.155:22) to root@10.0.0.154(10.0.0.154:22)..
Fri Sep  6 22:25:48 2019 - [debug]   ok.
Fri Sep  6 22:25:48 2019 - [info] All SSH connection tests passed successfully.

step07 MHA环境主从检查

masterha_check_repl --conf=配置文件
[root@mha_manager ~]# masterha_check_repl --conf=/etc/mha/application_01.cnf
Fri Sep  6 22:26:46 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Sep  6 22:26:46 2019 - [info] Reading application default configuration from /etc/mha/application_01.cnf..
Fri Sep  6 22:26:46 2019 - [info] Reading server configuration from /etc/mha/application_01.cnf..
Fri Sep  6 22:26:46 2019 - [info] MHA::MasterMonitor version 0.56.
Fri Sep  6 22:26:47 2019 - [info] GTID failover mode = 1
Fri Sep  6 22:26:47 2019 - [info] Dead Servers:
Fri Sep  6 22:26:47 2019 - [info] Alive Servers:
Fri Sep  6 22:26:47 2019 - [info]   10.0.0.153(10.0.0.153:3306)
Fri Sep  6 22:26:47 2019 - [info]   10.0.0.154(10.0.0.154:3306)
Fri Sep  6 22:26:47 2019 - [info]   10.0.0.155(10.0.0.155:3306)
Fri Sep  6 22:26:47 2019 - [info] Alive Slaves:
Fri Sep  6 22:26:47 2019 - [info]   10.0.0.154(10.0.0.154:3306)  Version=5.7.27-log (oldest major version between slaves) log-bin:enabled
Fri Sep  6 22:26:47 2019 - [info]     GTID ON
Fri Sep  6 22:26:47 2019 - [info]     Replicating from 10.0.0.153(10.0.0.153:3306)
Fri Sep  6 22:26:47 2019 - [info]   10.0.0.155(10.0.0.155:3306)  Version=5.7.27-log (oldest major version between slaves) log-bin:enabled
Fri Sep  6 22:26:47 2019 - [info]     GTID ON
Fri Sep  6 22:26:47 2019 - [info]     Replicating from 10.0.0.153(10.0.0.153:3306)
Fri Sep  6 22:26:47 2019 - [info] Current Alive Master: 10.0.0.153(10.0.0.153:3306)
Fri Sep  6 22:26:47 2019 - [info] Checking slave configurations..
Fri Sep  6 22:26:47 2019 - [info]  read_only=1 is not set on slave 10.0.0.154(10.0.0.154:3306).
Fri Sep  6 22:26:47 2019 - [info]  read_only=1 is not set on slave 10.0.0.155(10.0.0.155:3306).
Fri Sep  6 22:26:47 2019 - [info] Checking replication filtering settings..
Fri Sep  6 22:26:47 2019 - [info]  binlog_do_db= , binlog_ignore_db= 
Fri Sep  6 22:26:47 2019 - [info]  Replication filtering check ok.
Fri Sep  6 22:26:47 2019 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Fri Sep  6 22:26:47 2019 - [info] Checking SSH publickey authentication settings on the current master..
Fri Sep  6 22:26:47 2019 - [info] HealthCheck: SSH to 10.0.0.153 is reachable.
Fri Sep  6 22:26:47 2019 - [info] 
10.0.0.153(10.0.0.153:3306) (current master)
 +--10.0.0.154(10.0.0.154:3306)
 +--10.0.0.155(10.0.0.155:3306)

Fri Sep  6 22:26:47 2019 - [info] Checking replication health on 10.0.0.154..
Fri Sep  6 22:26:47 2019 - [info]  ok.
Fri Sep  6 22:26:47 2019 - [info] Checking replication health on 10.0.0.155..
Fri Sep  6 22:26:47 2019 - [info]  ok.
Fri Sep  6 22:26:47 2019 - [warning] master_ip_failover_script is not defined.
Fri Sep  6 22:26:47 2019 - [warning] shutdown_script is not defined.
Fri Sep  6 22:26:47 2019 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

step08 启动MHA的manager进程

MHA通过指定不同的配置文件启动,管理不同的一主两从节点
nohup masterha_manager --conf=配置文件 --remove_dead_master_conf --ignore_last_failover < /dev/null > /工作目录/manager.log 2>&1 &
[root@mha_manager /var/log/mha/app1]# nohup masterha_manager --conf=/etc/mha/application_01.cnf --remove_dead_master_conf --ignore_last_failover  < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
[1] 10143

step09 MHA状态检查

masterha_check_status --conf=配置文件
[root@mha_manager ~]# masterha_check_status --conf=/etc/mha/application_01.cnf 
application_01 (pid:11529) is running(0:PING_OK), master:10.0.0.153

step10 MHA验证(宕机测试)

[root@db03 ~]# systemctl stop mysqld.service 
[root@mha_manager ~]# masterha_check_status --conf=/etc/mha/application_01.cnf 
application_01 is stopped(2:NOT_RUNNING).
[1]+  Done                    nohup masterha_manager --conf=/etc/mha/application_01.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1
[root@mha_manager ~]# cat /etc/mha/application_01.cnf 
[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/mysql/3306/logs
password=mha
ping_interval=1
repl_password=456
repl_user=repl
ssh_user=root
user=mha

[server2]
hostname=10.0.0.154
port=3306

[server3]
hostname=10.0.0.155
port=3306
mysql> show slave status\G;
Empty set (0.00 sec)

ERROR: 
No query specified

mysql> show master status\G;
*************************** 1. row ***************************
             File: sql-binlog.000002
         Position: 905
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 64f86a3b-d079-11e9-966b-000c29b53c72:1-3
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         154 |
+-------------+
1 row in set (0.00 sec)
[root@db05 ~]# mysql -e "show slave status\G;"| grep -E '^[ ]+Master' |head -4
                  Master_Host: 10.0.0.154
                  Master_User: repl
                  Master_Port: 3306
              Master_Log_File: sql-binlog.000002

3.MHA Faileover工作流程

step01
Manager对节点进行监控,包括目标主机的系统、网络、SSH连接性等方面;重点对主库进行探测,探测频率2s/次,连续3次探测失败则MHA认为该Node故障主库进行探测,探测频率2s/次,连续3次探测失败则MHA认为该Node故障。
step02
当检测到主节点故障时,若主库可以SSH连接,从库立即进行数据补偿。从库对比主库的GTID或Position号,立即将二进制日志保存至各个从节点/var/tmp目录下并应用(save_binary_logs);
step03
MHA进行重新选取主节点。默认主节点选举策略为日志量大的成为主节点,若从库日志量一致,则按照配置文件中的节点顺序依次选取。

可以通过参数配置节点权重(Candidate_master),影响MHA的主库选举。

默认情况下,当Slave的relaylog日志量落后主库100M以上,则权重参数失效;
可通过check_repl_delay=0参数配置,忽略日志量差异;


选举策略执行优先级:权重>日志量>配置文件顺序

step04
若主库不能进行SSH连接,则对比从库间relaylog差异(apply_diff_reply_logs),进行数据补偿。
step05
备选主库进行身份切换,对外提供服务;
其余从库自动和主库建立主从关系。

4.MHA优化

脚本工具下载链接

  • 应用透明(VIP)
使用KEEPALIVE配合MHA进行VIP漂移时,必须通过candidate_master=1参数强制指定候选主库,让KEEPALIVE和MHA进行一致性漂移。

step01 获取脚本

[root@mha_manager ~]# cd /etc/mha/
[root@mha_manager /etc/mha]# rz -E
rz waiting to receive.
[root@mha_manager /etc/mha]# mv master_ip_failover.txt ./master_ip_failover
[root@mha_manager /etc/mha]# chmod +x master_ip_failover
[root@mha_manager /etc/mha]# ll 
total 8
-rw-r--r-- 1 root root  337 Sep  8 09:42 application_01.cnf
-rwxr-xr-x 1 root root 2248 Jun 28 11:36 master_ip_failover

step02 安装字符转换工具(处理脚本)

yum install -y dos2unix
[root@mha_manager /etc/mha]# yum install -y dos2unix.x86_64 
.....
Complete!

step03 处理脚本

dos2unix 脚本名
[root@mha_manager /etc/mha]# dos2unix master_ip_failover
dos2unix: converting file master_ip_failover.sh to Unix format ...

step04 编辑脚本

my $vip = '虚拟IP地址/掩码';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig 网卡名:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig 网卡名:$key down";
[root@mha_manager /etc/mha]# grep -E '^my' master_ip_failover
my (
my $vip = '10.0.0.156/24';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";

step05 修改MHA配置文件,调用脚本

[server default]
master_ip_failover_script=脚本
[root@mha_manager /etc/mha]# cat application_01.cnf 
[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/mysql/3306/logs
master_ip_failover_script=/etc/mha/master_ip_failover
user=mha
password=mha
ping_interval=1
repl_password=456
repl_user=repl
ssh_user=root

[server1]
hostname=10.0.0.153
port=3306

[server2]
hostname=10.0.0.154
port=3306

[server3]
hostname=10.0.0.155
port=3306
candidate_master=1
check_repl_delay=0

step06 首次使用MHA,手动为主库添加VIP

ifconfig 网卡名:1 VIP/掩码
[root@db03 ~]# ifconfig eth0:1 10.0.0.156/24
[root@db03 ~]# ifconfig eth0
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 10.0.0.153  netmask 255.255.255.0  broadcast 10.0.0.255
        inet6 fe80::20c:29ff:feb5:3c72  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:b5:3c:72  txqueuelen 1000  (Ethernet)
        RX packets 26361  bytes 13949641 (13.3 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 15379  bytes 1634740 (1.5 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@db03 ~]# ifconfig eth0:1
eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 10.0.0.156  netmask 255.255.255.0  broadcast 10.0.0.255
        ether 00:0c:29:b5:3c:72  txqueuelen 1000  (Ethernet)
[root@db03 ~]# ping 10.0.0.156 
PING 10.0.0.156 (10.0.0.156) 56(84) bytes of data.
64 bytes from 10.0.0.156: icmp_seq=1 ttl=64 time=0.044 ms
^C
--- 10.0.0.156 ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 0.044/0.044/0.044/0.000 ms

step07 重启MHA

masterha_stop --conf=配置文件 #停止MHA
nohup masterha_manager --conf=配置文件 --remove_dead_master_conf --ignore_last_failover < /dev/null > 日志文件.log 2>&1 & #启用MHA
[root@mha_manager /etc/mha]# masterha_stop --conf=/etc/mha/application_01.cnf 
Stopped application_01 successfully.
[1]+  Exit 1                  nohup masterha_manager --conf=/etc/mha/application_01.cnf remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1  (wd: ~)
(wd now: /etc/mha)
[root@mha_manager /etc/mha]# nohup masterha_manager --conf=/etc/mha/application_01.cnf --remove_dead_master_conf --ignore_last_failover </dev/null >/var/log/mha/manager.log 2&>1 &
[1] 27220
[root@mha_manager /etc/mha]# masterha_check_status --conf /etc/mha/application_01.cnf
application_01 (pid:27220) is running(0:PING_OK), master:10.0.0.154
  • 二次数据补偿(Binlog Server)

step01 准备Binlog_server
Binlog_Server要求:具备MySQL 5.6及以上版本环境,支持GTID,并开启;

[root@mha_manager /etc/mha]# mysql -e "show variables like '%version%'"
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.27                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.27-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux-glibc2.12              |
+-------------------------+------------------------------+
[root@mha_manager /etc/mha]# grep -i 'GTID' /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true

step02 编辑manager配置文件

[binlog1]
no_master=1 #不参与主库选举
hostname=日志服务器地址
master_binlog_dir=日志目录 #指定保存日志路径
[root@mha_manager /etc/mha]# tail -4 application_01.cnf 
[binlog1]
no_master=1
hostname=10.0.0.150
master_binlog_dir=/data/logs/mysql-bin

step03 创建日志保存目录并授权

mkdir -p 日志目录
chown -R mysql.mysql 日志目录
[root@mha_manager /etc/mha]# mkdir /data/logs/mysql-bin -p
[root@mha_manager /etc/mha]# chown mysql. -R /data/logs/mysql-bin/
[root@mha_manager /etc/mha]# id mysql
uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)
[root@mha_manager /etc/mha]# ll /data/logs/mysql-bin/ -d
drwxr-xr-x 2 mysql mysql 6 Sep  8 16:23 /data/logs/mysql-bin/

step04 binlog_server 从主库拉取日志

cd 日志目录
mysqlbinlog -R --host=主库IP地址 --user=MHA用户 --password=MHA用户密码 --raw --stop-nerver 日志文件 & #从指定文件开始拉取所有二进制日志文件
拉取日志文件的起点应为主库当前使用的二进制日志文件;
[root@mha_manager /data/logs/mysql-bin]# ssh 10.0.0.154 'mysql -e "show master status\G"'
*************************** 1. row ***************************
             File: sql-binlog.000002
         Position: 905
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 64f86a3b-d079-11e9-966b-000c29b53c72:1-3
[root@mha_manager /data/logs/mysql-bin]# mysqlbinlog -R --user=mha --password=mha --host=10.0.0.154 --port=3306 --raw --stop-never sql-binlog.000002 &
[2] 38283
[root@mha_manager /data/logs/mysql-bin]# mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.

[root@mha_manager /data/logs/mysql-bin]# ls
sql-binlog.000002

step05 创建软链接

ln -s 安装目录/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s 安装目录/bin/mysql /usr/bin/mysql
[root@mha_manager ~]# ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
[root@mha_manager ~]# ln -s /application/mysql/bin/mysql /usr/bin/mysql
[root@db03 ~]# ll /usr/bin/mysql*
[root@mha_manager ~]# ll /usr/bin/mysql*
lrwxrwxrwx 1 root root 28 Sep  9 19:21 /usr/bin/mysql -> /application/mysql/bin/mysql
lrwxrwxrwx 1 root root 34 Sep  9 19:21 /usr/bin/mysqlbinlog -> /application/mysql/bin/mysqlbinlog

step06 重启MHA

masterha_stop --conf=配置文件 #停止MHA
nohup masterha_manager --conf=配置文件 --remove_dead_master_conf --ignore_last_failover < /dev/null > 日志文件.log 2>&1 & #启用MHA
[root@mha_manager /data/logs/mysql-bin]# masterha_stop --conf=/etc/mha/application_01.cnf
Stopped application_01 successfully.
[1]-  Exit 1                  nohup masterha_manager --conf=/etc/mha/application_01.cnf --remove_dead_master_conf --ignore_last_failover 2 < /dev/null > /var/log/mha/manager.log &>1  (wd: /etc/mha)
(wd now: /data/logs/mysql-bin)
[root@mha_manager /data/logs/mysql-bin]# nohup masterha_manager --conf=/etc/mha/application_01.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null >/var/log/mha/manager.log 2>&1 &
[3] 38519
[root@mha_manager /data/logs/mysql-bin]# masterha_check_status --conf=/etc/mha/application_01.cnf
application_01 (pid:38519) is running(0:PING_OK), master:10.0.0.154
  • 故障切换通知(Send_Report)

step01获取并编写邮件脚本

[root@mha_manager ~]# cd /etc/mha
[root@mha_manager ~]# mkdir -p mail
[root@mha_manager ~]# cd mail
[root@mha_manager /etc/mha/mail]# rz -E
rz waiting to receive.
[root@mha_manager /etc/mha/mail]# unzip MHA_Email.zip 
Archive:  MHA_Email.zip
  inflating: send                    
  inflating: sendEmail               
  inflating: testpl
[root@mha_manager /etc/mha/mail]# rm -rf MHA_Email.zip
[root@mha_manager /etc/mha/mail]# ls
send  sendEmail  testpl
[root@mha_manager /etc/mha/mail]# cat testpl 
#!/bin/bash
/usr/local/bin/sendEmail -o tls=no -f lin****_***@163.com -t aspen_han@******.com -s smtp.163.com:25 -xu li****_*** -xp ******* -u "MHA Waring" -m "YOUR MHA MAY BE FAILOVER" &>/tmp/sendmail.log
[root@mha_manager /etc/mha/mail]# chmod +x *
[root@mha_manager /etc/mha/mail]# ll
total 88
-rwxr-xr-x 1 root root    35 Dec 27  2017 send
-rwxr-xr-x 1 root root 80213 Sep 30  2009 sendEmail
-rwxr-xr-x 1 root root   207 Sep  8 17:01 testpl

step02测试脚本
sendEmail官网下载链接

[root@mha_manager ~]# cd /etc/mha/mail/
[root@mha_manager /etc/mha/mail]# rz -E
rz waiting to receive.
[root@mha_manager /etc/mha/mail]# tar xf ./sendEmail-v1.56.tar.gz 
[root@mha_manager /etc/mha/mail]# ls
send  sendEmail  sendEmail-v1.56  sendEmail-v1.56.tar.gz  testpl
[root@mha_manager /etc/mha/mail]# cd sendEmail-v1.56/
[root@mha_manager /etc/mha/mail/sendEmail-v1.56]# ls
CHANGELOG  README  README-BR.txt  sendEmail  sendEmail.pl  TODO
[root@mha_manager /etc/mha/mail/sendEmail-v1.56]# cp -a sendEmail /usr/local/bin/
[root@mha_manager /etc/mha/mail]# ./testpl 
[root@mha_manager /etc/mha/mail]# cat /tmp/sendmail.log 
Sep 08 17:39:34 mha_manager sendEmail[40926]: Email was sent successfully!

step03调用脚本

[server default]
report_script=通知脚本
[root@mha_manager /etc/mha/mail]# ln -s /etc/mha/mail/testpl /usr/local/bin/
[root@mha_manager /etc/mha/mail]# ll /usr/local/bin/testpl 
lrwxrwxrwx 1 root root 20 Sep  8 22:14 /usr/local/bin/testpl -> /etc/mha/mail/testpl
[root@mha_manager /var/log/mha]# grep 'report_script' /etc/mha/application_01.cnf 
report_script=/etc/mha/mail/send
send脚本调用的是testpl脚本,需要将testpl脚本创建软连接到/usr/local/bin/目录下

step04 重启MHA

masterha_stop --conf=配置文件 #停止MHA
nohup masterha_manager --conf=配置文件 --remove_dead_master_conf --ignore_last_failover < /dev/null> 日志文件.log 2>&1 & #启用MHA
[root@mha_manager /etc/mha/mail]# masterha_stop --conf=/etc/mha/application_01.cnf
Stopped application_01 successfully.
[3]+  Exit 1                  nohup masterha_manager --conf=/etc/mha/application_01.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/manager.log 2>&1
[root@mha_manager /etc/mha/mail]# nohup masterha_manager --conf=/etc/mha/application_01.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null >/var/log/mha/manager.log 2>&1 &
[3] 41126
[root@mha_manager /etc/mha/mail]# masterha_check_status --conf=/etc/mha/application_01.cnf 
application_01 (pid:41126) is running(0:PING_OK), master:10.0.0.154
  • 自愈自知(待开发)
MHA为一次性高可用,当Node发生故障时,因不满足一主两从的环境,manager进程会自动停止;若主库Node故障,则manager完成主库转移工作后,会自动停止运行,同时还会停止Binlog Server

5.MHA故障修复

step0 模拟主库故障

[root@db04 ~]# hostname -I
10.0.0.154 10.0.0.156 172.16.1.154 
[root@db04 ~]# systemctl stop mysqld
[root@db04 ~]# hostname -I
10.0.0.154 172.16.1.154
[root@mha_manager /data/logs/mysql-bin]# masterha_check_status --conf=/etc/mha/application_01.cnf
application_01 is stopped(2:NOT_RUNNING).
[1]-  Done                    mysqlbinlog -R --host=10.0.0.154 --port=3306 --user=mha --password=mha --raw --stop-never sql-binlog.000018
[2]+  Done                    nohup masterha_manager --conf=/etc/mha/application_01.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1

step1 恢复故障节点业务

[root@db04 ~]# systemctl start mysqld
[root@db04 ~]# mysql -e "select @@server_id;"
+-------------+
| @@server_id |
+-------------+
|         154 |
+-------------+

step2 重构主从关系

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> reset slave all;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.155',MASTER_USER='repl',MASTER_PASSWORD='456',MASTER_PORT=3306,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> ^DBye
[root@db04 ~]# mysql  -e "show slave status\G;"|grep -E '(^[ ]+Slave_(IO|SQL)_Running:)|(^[ ]+Last_(IO|SQL)_Err(or|no):)'
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error:

step3 修复manager配置文件

[root@mha_manager /data/logs/mysql-bin]# vim /etc/mha/application_01.cnf 
[binlog1]
hostname=10.0.0.150
master_binlog_dir=/data/logs/mysql-bin
no_master=1

[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/mysql/3306/logs
master_ip_failover_script=/etc/mha/master_ip_failover
password=mha
ping_interval=1
repl_password=456
repl_user=repl
report_script=/etc/mha/mail/send
ssh_user=root
user=mha

[server1]
hostname=10.0.0.153
port=3306
[server2]
hostname=10.0.0.154
port=3306
[server3]
candidate_master=1
check_repl_delay=0
hostname=10.0.0.155
port=3306

step4 重新拉取Binlog Server

[root@mha_manager /data/logs/mysql-bin]# ls
sql-binlog.000018  sql-binlog.000019  sql-binlog.000020  sql-binlog.000021
[root@mha_manager /data/logs/mysql-bin]# rm -rf *
[root@mha_manager /data/logs/mysql-bin]# mysqlbinlog -R --host=10.0.0.155 --port=3306 --user=mha --password=mha   --raw --stop-never sql-binlog.000001 &
[1] 23165
[root@mha_manager /data/logs/mysql-bin]# mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.

[root@mha_manager /data/logs/mysql-bin]# ls
sql-binlog.000001  sql-binlog.000003  sql-binlog.000005
sql-binlog.000002  sql-binlog.000004  sql-binlog.000006

step5 启动manager进程

[root@mha_manager /data/logs/mysql-bin]# nohup masterha_manager --conf=/etc/mha/application_01.cnf --remove_dead_master_conf --ignore_last_failover  < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
[2] 23167

step6 验证manager进程状态

[root@mha_manager /data/logs/mysql-bin]# masterha_check_status --conf=/etc/mha/application_01.cnf
application_01 (pid:23167) is running(0:PING_OK), master:10.0.0.155

二、基于Atlas实现MHA的读写分离架构

Atlas下载地址

注意:
1、Atlas只能安装运行在64位的系统上
2、Centos 5.X安装 Atlas-XX.el5.x86_64.rpm,Centos 6.X安装Atlas-XX.el6.x86_64.rpm
3、后端mysql版本应大于5.1,建议使用Mysql 5.6以上

1 Atlas部署流程

step01 获取Atlas软件,并安装

[root@mha_manager ~]# 
[root@mha_manager ~]# cd /application/
[root@mha_manager /application]# ls
mysql
[root@mha_manager /application]# mkdir Atlas -p
[root@mha_manager /application]# cd Atlas/
[root@mha_manager /application/Atlas]# mkdir install_packet
[root@mha_manager /application/Atlas]# cd install_packet/
[root@mha_manager /application/Atlas/install_packet]# rz -E
rz waiting to receive.
[root@mha_manager /application/Atlas/install_packet]# ls
Atlas-2.2.1.el6.x86_64.rpm
[root@mha_manager /application/Atlas/install_packet]# yum install -y Atlas-2.2.1.el6.x86_64.rpm
......
Installed:
  Atlas.x86_64 0:2.2.1-1                                                 

Complete!

step02 进入安装目录

cd /usr/local/mysql-proxy/
[root@mha_manager /application/Atlas/install_packet]# cd /usr/local/mysql-proxy/
[root@mha_manager /usr/local/mysql-proxy]# ls
bin  conf  lib  log

step03 编辑配置文件

vim /usr/local/mysql-proxy/conf/配置文件
[mysql-proxy]
admin-username = Atlas管理用户
admin-password = Atlas管理用户密码
proxy-backend-addresses = IP地址:端口号 #指定写节点IP地址
proxy-read-only-backend-addresses = IP地址1:端口,IP地址2:端口 #指定读节点IP地址(默认两个读节点进行负载均衡)
pwds = 用户1:加密密码,用户2:加密密码,...... #指定业务连接用户和密码
daemon = true #开启守护进程模式
keepalive = true #开启结点状态监控
event-threads = n #开启指定数量的事件线程
log-level = message #指定日志记录级别
log-path = 日志路径 #指定日志存放位置
sql-log=ON #开启Atlas日志记录
proxy-address = 0.0.0.0:端口 #指定Atlas业务端口
admin-address = 0.0.0.0:端口 #指定Atlas管理端口
charset=utf8 #指定字符集(一般与后端数据库所使用字符集一致)
[root@mha_manager /usr/local/mysql-proxy]# cd conf/
[root@mha_manager /usr/local/mysql-proxy/conf]# ls
test.cnf
[root@mha_manager /usr/local/mysql-proxy/conf]# cp test.cnf{,.bak}
[root@mha_manager /usr/local/mysql-proxy/conf]# >test.cnf
[root@mha_manager /usr/local/mysql-proxy/conf]# ls
test.cnf  test.cnf.bak
[root@mha_manager /usr/local/mysql-proxy/conf]# /usr/local/mysql-proxy/bin/encrypt mha
O2jBXONX098=
[root@mha_manager /usr/local/mysql-proxy/conf]# cat test.cnf
[mysql-proxy]
admin-username = proxy_manager
admin-password = aspen_han
proxy-backend-addresses = 10.0.0.156:3306
proxy-read-only-backend-addresses = 10.0.0.153:3306,10.0.0.154:3306
pwds = mha:O2jBXONX098=
daemon = true
keepalive = true
event-threads = 8
log-level = message
log-path = /usr/local/mysql-proxy/log
sql-log = ON
proxy-address = 0.0.0.0:3305
admin-address = 0.0.0.0:3304
charset = utf8mb4

step04 启动Atlas

Atlas可以通过不同配置文件,同时启动多个Atlas代理多套MHA节点;
/usr/local/mysql-proxy/bin/mysql-proxyd 配置文件 start;
[root@mha_manager /usr/local/mysql-proxy/conf]# /usr/local/mysql-proxy/bin/mysql-proxyd test start
OK: MySQL-Proxy of test is started
[root@mha_manager /usr/local/mysql-proxy/conf]# ps -ef | grep proxy
root      27873      1  0 09:25 ?        00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root      27874  27873  0 09:25 ?        00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root      27891  24792  0 09:26 pts/1    00:00:00 grep --color=auto proxy

step05 验证Atlas读写分离

[root@mha_manager /usr/local/mysql-proxy/conf]# mysql -umha -pmha -h 10.0.0.150 -P3305
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.81-log

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.

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         153 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         154 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         153 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         154 |
+-------------+
1 row in set (0.00 sec)
mysql> begin;select @@server_id;commit;
Query OK, 0 rows affected (0.01 sec)

+-------------+
| @@server_id |
+-------------+
|         155 |
+-------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.09 sec)

mysql> begin;select @@server_id;commit;
Query OK, 0 rows affected (0.00 sec)

+-------------+
| @@server_id |
+-------------+
|         155 |
+-------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> begin;select @@server_id;commit;
Query OK, 0 rows affected (0.00 sec)

2、Atlas管理

  • 创建用户

step 01 MySQL节点添加用户

[root@db05 ~]# mysql -e " grant all on *.* to aspen@'10.0.0.%' identified by '123';"
[root@db05 ~]# mysql -e "select user,host from mysql.user;"
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| aspen         | 10.0.0.%  |
| mha           | 10.0.0.%  |
| repl          | 10.0.0.%  |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+

step 02 Atlas用户密码加密

/usr/local/mysql-proxy/bin/encrypt 明文密码
[root@mha_manager /usr/local/mysql-proxy/conf]# /usr/local/mysql-proxy/bin/encrypt 123
3yb5jEku5h4=

step 03 Atlas添加用户

修改配置文件pwds
[root@mha_manager /usr/local/mysql-proxy/conf]# grep 'pwds' test.cnf
pwds = mha:O2jBXONX098=,aspen:3yb5jEku5h4=

step 04 重启Atlas

/usr/local/mysql-proxy/bin/mysql-proxyd 配置文件 restart;
[root@mha_manager /usr/local/mysql-proxy/conf]# /usr/local/mysql-proxy/bin/mysql-proxyd test restart
OK: MySQL-Proxy of test is stopped
OK: MySQL-Proxy of test is started

step 05 连接验证

[root@mha_manager /usr/local/mysql-proxy/conf]# mysql -uaspen -p123 -h 10.0.0.150 -P3305
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.81-log MySQL Community Server (GPL)

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.

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         154 |
+-------------+
1 row in set (0.01 sec)
  • 内部管理

step1 连接进入管理端

mysql -u用户 -p密码 -h IP地址 -P端口 #用Atlas管理用户登录Atlas管理端
用户-admin-username
密码-admin-password
IP地址-admin-address
端口号-admin-address
[root@mha_manager /usr/local/mysql-proxy/conf]# head -3 test.cnf
[mysql-proxy]
admin-username = proxy_manager
admin-password = aspen_han
[root@mha_manager /usr/local/mysql-proxy/conf]# mysql -uproxy_manager -paspen_han -h 10.0.0.150 -P3304
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.

mysql> 

step2 查看管理端帮助信息

select * from help;
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                            |
+----------------------------+---------------------------------------------------------+
16 rows in set (0.00 sec)

2、Atlas常用管理端操作

  • 查看后端节点
SELECT * FROM backends;
mysql> select * from backends;
+-------------+-----------------+-------+------+
| backend_ndx | address         | state | type |
+-------------+-----------------+-------+------+
|           1 | 10.0.0.156:3306 | up    | rw   |
|           2 | 10.0.0.153:3306 | up    | ro   |
|           3 | 10.0.0.154:3306 | up    | ro   |
+-------------+-----------------+-------+------+
3 rows in set (0.00 sec)
  • 下线后端节点
    SET OFFLINE backend_ndx;
mysql> set offline 3;
+-------------+-----------------+---------+------+
| backend_ndx | address         | state   | type |
+-------------+-----------------+---------+------+
|           3 | 10.0.0.154:3306 | offline | ro   |
+-------------+-----------------+---------+------+
1 row in set (0.00 sec)

mysql> select * from backends;
+-------------+-----------------+---------+------+
| backend_ndx | address         | state   | type |
+-------------+-----------------+---------+------+
|           1 | 10.0.0.156:3306 | up      | rw   |
|           2 | 10.0.0.153:3306 | up      | ro   |
|           3 | 10.0.0.154:3306 | offline | ro   |
+-------------+-----------------+---------+------+
3 rows in set (0.00 sec)
  • 上线后端节点
SET ONLINE backend_ndx;
mysql> set online 3;
+-------------+-----------------+---------+------+
| backend_ndx | address         | state   | type |
+-------------+-----------------+---------+------+
|           3 | 10.0.0.154:3306 | unknown | ro   |
+-------------+-----------------+---------+------+
1 row in set (0.00 sec)

mysql> select * from backends;
+-------------+-----------------+-------+------+
| backend_ndx | address         | state | type |
+-------------+-----------------+-------+------+
|           1 | 10.0.0.156:3306 | up    | rw   |
|           2 | 10.0.0.153:3306 | up    | ro   |
|           3 | 10.0.0.154:3306 | up    | ro   |
+-------------+-----------------+-------+------+
3 rows in set (0.00 sec)
  • 删除从节点
REMOVE BACKEND backend_ndx;>
mysql> select * from backends;
+-------------+-----------------+-------+------+
| backend_ndx | address         | state | type |
+-------------+-----------------+-------+------+
|           1 | 10.0.0.156:3306 | up    | rw   |
|           2 | 10.0.0.154:3306 | up    | ro   |
+-------------+-----------------+-------+------+
2 rows in set (0.00 sec)
  • 添加从节点
ADD SLAVE 节点IP地址:端口号;
mysql> add slave 10.0.0.153:3306;
Empty set (0.00 sec)

mysql> select * from backends;
+-------------+-----------------+-------+------+
| backend_ndx | address         | state | type |
+-------------+-----------------+-------+------+
|           1 | 10.0.0.156:3306 | up    | rw   |
|           2 | 10.0.0.154:3306 | up    | ro   |
|           3 | 10.0.0.153:3306 | up    | ro   |
+-------------+-----------------+-------+------+
3 rows in set (0.00 sec)
  • 查询管理用户和密码
SELECT * FROM pwds;
mysql> select * from pwds;
+----------+--------------+
| username | password     |
+----------+--------------+
| mha      | O2jBXONX098= |
| aspen    | 3yb5jEku5h4= |
+----------+--------------+
2 rows in set (0.00 sec)
  • 删除管理用户
REMOVE PWD 用户名;
mysql> remove pwd aspen;
Empty set (0.00 sec)

mysql> select * from pwds;
+----------+--------------+
| username | password     |
+----------+--------------+
| mha      | O2jBXONX098= |
+----------+--------------+
1 row in set (0.00 sec)
  • 添加管理用户
ADD PWD 用户名:明文密码;
ADD ENPWD 用户名:密文密码;
mysql> select * from pwds;
+----------+--------------+
| username | password     |
+----------+--------------+
| mha      | O2jBXONX098= |
| aspen    | 3yb5jEku5h4= |
+----------+--------------+
2 rows in set (0.00 sec)
以上管理端操作默认不保存到配置文件,重启Atlas配置失效;
  • 将管理端修改保存到配置文件
save config;
mysql> add pwd test:1234;
Empty set (0.00 sec)

mysql> save config;
Empty set (0.00 sec)
[root@mha_manager /usr/local/mysql-proxy/conf]# grep 'pwds' test.cnf
pwds=mha:O2jBXONX098=,aspen:3yb5jEku5h4=,test:T+sRVvDh0JA=

附:思维导图

发表评论

您的电子邮箱地址不会被公开。