一、主库优先级切换测试
1.配置预选提升主库配置
[server3]
candidate_master=1
check_repl_delay=0
hostname=172.16.1.53
port=3306
2.数据量切换优先级测试
1.启动mha
nohup masterha_manager --conf=/service/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /service/mha/manager.log 2>&1 &
2.主库创建表
use test
mysql> create table linux7(id int not null primary key auto_increment,name varchar(10));
3.写一个脚本一致插入数据
[root@db03 ~]
while true;do
mysql -e "use test;insert into linux7(name) values('lhd')"
sleep 1
done
4.执行脚本
5.停止db01的IO线程
stop slave io_thread;
6.停止主库，查看切换
3.如果断电，binog如何保存
1.配置MHA实时备份binlog配置
[root@db03 ~]
[binlog1]
no_master=1
hostname=172.16.1.53
master_binlog_dir=/data/mysql/binlog/
2.创建binlog存放目录
[root@db03 ~]
3.手动备份binlog
[root@db03 ~]
[root@db03 binlog]
4.主库添加数据
5.MHA服务器查看binlog
[root@db03 binlog]
total 36
-rw-rw---- 1 root root 554 May 7 09:37 mysql-bin.000001
-rw-rw---- 1 root root 25678 May 7 09:40 mysql-bin.000002
-rw-rw---- 1 root root 120 May 7 09:40 mysql-bin.000003
二、VIP漂移
1.配置MHA读取脚本
[root@db03 ~]
[server default]
master_ip_failover_script=/service/mha/master_ip_failover
2.编写脚本
1.上传脚本
[root@db03 mha]
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
my $vip = '172.16.1.55/24';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig eth1:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth1:$key down";
GetOptions(
2.授权脚本
[root@db03 mha]
dos2unix: converting file master_ip_failover to Unix format ...
[root@db03 mha]
3.手动绑定VIP
[root@db01 ~]
4.重启MHA
三、Atlas 读写分离
1.Atlas介绍
1.读写分离
2.从库负载均衡
3.IP过滤
4.自动分表
5.DBA可平滑上下线DB
6.自动摘除宕机的DB
1.将主流程中所有Lua代码用C重写，Lua仅用于管理接口
2.重写网络模型、线程模型
3.实现了真正意义上的连接池
4.优化了锁机制，性能提高数十倍
2.安装Atlas
1.上传包
[root@db03 ~]
2.安装rpm包
[root@db03 ~]
3.安装后查看文件
[root@db03 ~]
total 0
drwxr-xr-x 2 root root 75 May 7 11:25 bin
drwxr-xr-x 2 root root 22 May 7 11:25 conf
drwxr-xr-x 3 root root 331 May 7 11:25 lib
drwxr-xr-x 2 root root 6 Dec 17 2014 log
3.配置文件
[root@db03 conf]
[mysql-proxy]
admin-username = user
admin-password = pwd
proxy-backend-addresses = 172.16.1.55:3306
proxy-read-only-backend-addresses = 172.16.1.51:3306,172.16.1.53:3306
pwds = rep:3yb5jEku5h4=,mha:O2jBXONX098=
daemon = true
keepalive = true
event-threads = 8
log-level = error
log-path = /usr/local/mysql-proxy/log
sql-log = ON
sql-log-slow = 10
instance = test
proxy-address = 0.0.0.0:1234
admin-address = 0.0.0.0:2345
client-ips = 127.0.0.1
4.启动Atlas
[root@db03 conf]
OK: MySQL-Proxy of test is started
[root@db03 conf]
root 32806 1 0 11:53 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root 32807 32806 0 11:53 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root 32844 25499 0 11:54 pts/4 00:00:00 grep --color=auto mysql-proxy
[root@db03 conf]
tcp 0 0 0.0.0.0:2345 0.0.0.0:* LISTEN 32807/mysql-proxy
tcp 0 0 0.0.0.0:1234 0.0.0.0:* LISTEN 32807/mysql-proxy
5.Atlas使用
1.连接数据库
[root@db03 conf]
2.Atlas管理数据库使用方法
mysql> SELECT * FROM help;
+----------------------------+---------------------------------------------------------+
| command | description |
+----------------------------+---------------------------------------------------------+
| SELECT * FROM help | 查看帮助 |
| SELECT * FROM backends | 查看后端数据库列表 |
| SET OFFLINE $backend_id | DBA平滑下线数据库（逻辑下线） |
| SET ONLINE $backend_id | DBA平滑上线数据库（逻辑上线） |
| ADD MASTER $backend | 添加主库：add master 172.16.1.59:3306; |
| ADD SLAVE $backend | 添加从库：ADD SLAVE 172.16.1.58:3306; |
| REMOVE BACKEND $backend_id | 移除后端数据库（逻辑移除） |
| SELECT * FROM clients | 查看可以连接的客户端 |
| ADD CLIENT $client | 添加可以连接的客户端 |
| REMOVE CLIENT $client | 移除可以连接的客户端 |
| SELECT * FROM pwds | 查看用户名和加密的密码 |
| ADD PWD $pwd | 添加用户:没加密的密码 |
| ADD ENPWD $pwd | 添加用户:加密的密码 |
| REMOVE PWD $pwd | 移除用户，不用加密码 |
| SAVE CONFIG | 保存到配置文件 |
| SELECT VERSION | 查看版本 |
+----------------------------+---------------------------------------------------------+
16 rows in set (0.00 sec)
四、Atlas结合MHA故障恢复
1.思路
1.找到出故障的主库，在Atlas中对应的backend_id
2.移除down掉的主库
3.找到提升为主库的从库
4.先移除该从库，再把该从库当成主库添加
5.保存到配置文件
2.写脚本
[root@db03 ~]
#!/bin/bash
down_server=$(grep 'Master .* is down' /service/mha/manager.log | awk -F "[ (]" '{print $2}') &> /dev/null
add_slave_server=$(grep 'Master .* is down' /service/mha/manager.log | awk -F "[ ()]" '{print $3}') &> /dev/null
down_backend_id=$(mysql -uuser -ppwd -h127.0.0.1 -P2345 -e "SELECT * FROM backends" | grep $down_server | awk '{print $1}') &> /dev/null
mysql -uuser -ppwd -h127.0.0.1 -P2345 -e "remove backend $down_backend_id" &> /dev/null
new_master=$(grep 'as a new master' /service/mha/manager.log | awk -F "[ ()]" '{print $3}') &> /dev/null
new_master_id=$(mysql -uuser -ppwd -h127.0.0.1 -P2345 -e "SELECT * FROM backends" | grep $new_master | awk '{print $1}') &> /dev/null
mysql -uuser -ppwd -h127.0.0.1 -P2345 -e "remove backend $new_master_id" &> /dev/null
mysql -uuser -ppwd -h127.0.0.1 -P2345 -e "add master $new_master;save config" &> /dev/null
ssh $down_server "/bin/bash /root/recover.sh" &> /dev/null
mysql -uuser -ppwd -h127.0.0.1 -P2345 -e "add slave $add_slave_server;save config" &> /dev/null
[root@db01 ~]
#!/bin/bash
systemctl start mysql
sleep 3
change=$(ssh 172.16.1.53 "grep -i 'change master to' /service/mha/manager.log | cut -d ':' -f 4 | sed 's#xxx#123#g'")
mysql -uroot -p123456 -e "$change;start slave"
ssh 172.16.1.53 "\cp /service/mha/app11.cnf /service/mha/app1.cnf"
ssh 172.16.1.53 "/bin/bash /root/start_mha.sh"
[root@db03 ~]
#!/bin/bash
new_master=$(grep 'a new master' /service/mha/manager.log | awk -F "[ ,(]" '{print $2}')
mysqlbinlog -R --host="$new_master" --user=mha --password=mha --raw --stop-never mysql-bin.000001 &
nohup masterha_manager --conf=/service/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /service/mha/manager.log 2>&1 &