一、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
db05step02 配置命令软连接；
|
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/mysqlbinlogstep03 下载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=3306step06 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] 10143step09 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.153step10 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=3306mysql> 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.0000023.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_failoverstep02 安装字符转换工具(处理脚本)
| 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=0step06 首次使用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 msstep07 重启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=truestep02 编辑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-binstep03 创建日志保存目录并授权
|
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.000002step05 创建软链接
|
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/mysqlbinlogstep06 重启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 testplstep02测试脚本
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>&1step1 恢复故障节点业务
[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=3306step4 重新拉取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.000006step5 启动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] 23167step6 验证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的读写分离架构
|
注意： 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 logstep03 编辑配置文件
| 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 = utf8mb4step04 启动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 proxystep05 验证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 startedstep 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=附：思维导图
