6. 應(yīng)用透明---VIP
vip :? 10.0.0.55/24
6.1 vip 故障轉(zhuǎn)移腳本
上傳mha_script.tar文件到/usr/local/bin 解壓
6.2 修改權(quán)限
[root@db03 bin]# chmod +x /usr/local/bin/*
=============================================
插曲：將腳本修改成這個(gè)版本得因?yàn)槟_本里有中文#
[root@db03 bin]yum install -y dos2unix? ? ? #
[root@db03 bin]dos2unix *? ? ? ? ? ? ? ? ? #
=============================================
6.3 修改內(nèi)容
[root@db03 bin]# cp master_ip_failover master_ip_failover.bak
my $vip = '10.0.0.55/24';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
my $ssh_Bcast_arp= "/sbin/arping -I eth0 -c 3 -A 10.0.0.55";
6.4 修改Manager 配置文件
vim /etc/mha/app1.cnf
master_ip_failover_script=/usr/local/bin/master_ip_failover
6.5 重啟MHA
[root@db03 bin]# masterha_stop? --conf=/etc/mha/app1.cnf
[root@db03 bin]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover? < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
6.6 手工在主庫(kù)添加VIP
[root@db02 ~]# ifconfig eth0:1 10.0.0.55/24
6.7 效果測(cè)試
使用navicat 連接測(cè)試MHA vip功能。
7. 故障提醒功能
7.1 準(zhǔn)備腳本
[root@db03 bin]# cp send_report send_report.bak1
my $smtp='smtp.qq.com';? ? ? ? ? ? # smtp服務(wù)器
my $mail_from='22654481@qq.com';? ? # 發(fā)件箱
my $mail_user='22654481';? ? ? ? ? # 用戶名 QQ號(hào)
my $mail_pass='gemghsvgkeyzcagh';? # 授權(quán)碼
my $mail_to=['22654481@qq.com'];? ? # 收件箱
#my $mail_to=['to1@qq.com','to2@qq.com'];
7.2 修改配置文件
vim /etc/mha/app1.cnf
# 添加一行：
report_script=/usr/local/bin/send_report
7.3 重啟MHA
[root@db03 bin]# masterha_stop? --conf=/etc/mha/app1.cnf
[root@db03 bin]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover? < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
7.4 模擬主庫(kù)宕機(jī)?
7.4.1 確認(rèn)主庫(kù)
[root@db03 bin]# masterha_check_status? --conf=/etc/mha/app1.cnf
app1 (pid:27096) is running(0:PING_OK), master:10.0.0.52
7.4.2 宕主庫(kù)
[root@db02 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!
7.4.3 觀察 vip 漂移?
7.4.4 觀察 郵件
7.5? 修復(fù)MHA 架構(gòu)1主2從
略
8. 日志補(bǔ)償?shù)娜哂喾桨?-binlog_server
8.1 創(chuàng)建必要目錄(db03)
mkdir -p /data/binlog_server/
chown -R mysql.mysql /data/*
cd? /data/binlog_server/
[root@db03 ~]# mysql -e "show slave status \G"|grep "Master_Log"
? ? ? ? ? ? ? Master_Log_File: mysql-bin.000008
? ? ? ? ? Read_Master_Log_Pos: 194
? ? ? ? Relay_Master_Log_File: mysql-bin.000008
? ? ? ? ? Exec_Master_Log_Pos: 194
[root@db03 ~]#
[root@db03 binlog_server]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:7057) is running(0:PING_OK), master:10.0.0.52
mysqlbinlog? -R --host=10.0.0.52 --user=mha --password=mha --raw? --stop-never mysql-bin.000004 &
注意：
拉取日志的起點(diǎn),需要按照目前從庫(kù)的已經(jīng)獲取到的二進(jìn)制日志點(diǎn)為起點(diǎn)
8.2 配置文件設(shè)置
vim /etc/mha/app1.cnf
[binlog1]
no_master=1
hostname=10.0.0.53
master_binlog_dir=/data/binlog_server/
8.3 重啟MHA
[root@db03 bin]# masterha_stop? --conf=/etc/mha/app1.cnf
[root@db03 bin]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover? < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
8.4 故障演練及修復(fù)
額外修復(fù)binlog server
[root@db03 binlog_server]# cd /data/binlog_server/
[root@db03 binlog_server]#
[root@db03 binlog_server]# ll
total 4
-rw-r----- 1 root root 485 Jul 15 12:09 mysql-bin.000004
[root@db03 binlog_server]# rm -rf *
[root@db03 binlog_server]#? mysql -e "show slave status \G"|grep "Master_Log"
? ? ? ? ? ? ? Master_Log_File: mysql-bin.000004
? ? ? ? ? Read_Master_Log_Pos: 234
? ? ? ? Relay_Master_Log_File: mysql-bin.000004
? ? ? ? ? Exec_Master_Log_Pos: 234
[root@db03 binlog_server]# mysqlbinlog? -R --host=10.0.0.51 --user=mha --password=mha --raw? --stop-never mysql-bin.000004 &
[root@db03 binlog_server]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover? < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
[2] 8375
[root@db03 binlog_server]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:8375) is running(0:PING_OK), master:10.0.0.51
9. MHA的維護(hù)操作 - 在線切換功能
9.1 只切換角色
masterha_master_switch? --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=10.0.0.52 --orig_master_is_new_slave --running_updates_limit=10000
注意：
master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes
1. 此種方法 切換，要注意將原主庫(kù)，F(xiàn)TWRL，否則會(huì)造成主從不一致。
2. 手工切換vip
3. 重新拉去新主庫(kù)的binlog
4. 發(fā)郵件功能
9.2 master_ip_online_change_script功能實(shí)現(xiàn)
功能: 在線切換時(shí)，自動(dòng)鎖原主庫(kù)，VIP自動(dòng)切換
9.2.1 準(zhǔn)備切換腳本
vim /usr/local/bin/master_ip_online_change
my $vip = "10.0.0.55/24";
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key $vip down";
my $ssh_Bcast_arp= "/sbin/arping -I ens33 -c 3 -A 10.0.0.55";
9.2.2 修改MHA配置文件
vim /etc/mha/app1.cnf
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
9.2.3 停 MHA
[root@db03 bin]# masterha_stop? --conf=/etc/mha/app1.cnf
9.2.4 檢查repl
[root@db03 bin]# masterha_check_repl? --conf=/etc/mha/app1.cnf
9.2.5 在線切換
masterha_master_switch? --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=10.0.0.51 --orig_master_is_new_slave --running_updates_limit=10000
9.2.6 重構(gòu)binlogserver
[root@db03 bin]# ps -ef |grep mysqlbinlog
root? ? ? 28144? 16272? 0 17:50 pts/1? ? 00:00:00 mysqlbinlog -R --host=10.0.0.52 --user=mha --password=x x --raw --stop-never mysql-bin.000005
root? ? ? 28529? 16272? 0 18:03 pts/1? ? 00:00:00 grep --color=auto mysqlbinlog
[root@db03 bin]# kill -9 28144
[root@db03 bin]# cd /data/binlog_server/
[root@db03 binlog_server]# ll
total 4
-rw-r----- 1 root root 194 Apr? 1 17:50 mysql-bin.000005
[root@db03 binlog_server]# rm -rf *
[root@db03 binlog_server]# mysqlbinlog? -R --host=10.0.0.51 --user=mha --password=mha --raw? --stop-never mysql-bin.000009 &
[1] 28534
9.2.7 啟動(dòng)MHA
[root@db03 bin]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover? < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
[root@db03 binlog_server]# masterha_check_status? --conf=/etc/mha/app1.cnf
app1 (pid:28535) is running(0:PING_OK), master:10.0.0.51
================================
Atlas 讀寫分離技術(shù)
1. 介紹
Atlas是由 Qihoo 360, Web平臺(tái)部基礎(chǔ)架構(gòu)團(tuán)隊(duì)開發(fā)維護(hù)的一個(gè)基于MySQL協(xié)議的數(shù)據(jù)中間層項(xiàng)目。
它是在mysql-proxy 0.8.2版本的基礎(chǔ)上，對(duì)其進(jìn)行了優(yōu)化，增加了一些新的功能特性。
360內(nèi)部使用Atlas運(yùn)行的mysql業(yè)務(wù)，每天承載的讀寫請(qǐng)求數(shù)達(dá)幾十億條。
下載地址
https://github.com/Qihoo360/Atlas/releases
2.安裝配置
yum install -y Atlas*
cd /usr/local/mysql-proxy/conf
mv test.cnf test.cnf.bak
vi test.cnf
[mysql-proxy]
admin-username = user
admin-password = pwd
proxy-backend-addresses = 10.0.0.55:3306
proxy-read-only-backend-addresses = 10.0.0.52:3306,10.0.0.53:3306
pwds = repl:3yb5jEku5h4=,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:33060
admin-address = 0.0.0.0:2345
charset=utf8
啟動(dòng)atlas
/usr/local/mysql-proxy/bin/mysql-proxyd test start
ps -ef |grep proxy
3. Atlas功能測(cè)試
測(cè)試讀操作：
mysql -umha -pmha? -h 10.0.0.53 -P 33060
db03 [(none)]>select @@server_id;
測(cè)試寫操作：
mysql> begin;select @@server_id;commit;
注意:?
DDL建議不要再Atlas觸發(fā)，最好是到主庫(kù)觸發(fā)（Online DDL或者PT-OSC）。
DML建議begin; DML;? commit;
4. Atlas 的管理操作
[root@db03 conf]# mysql -uuser -ppwd -h 10.0.0.53 -P2345
db03 [(none)]>select * from help;
4.1 查看所有節(jié)點(diǎn)
db03 [(none)]>SELECT * FROM backends;
+-------------+----------------+-------+------+
| backend_ndx | address? ? ? ? | state | type |
+-------------+----------------+-------+------+
|? ? ? ? ? 1 | 10.0.0.55:3306 | up? ? | rw? |
|? ? ? ? ? 2 | 10.0.0.52:3306 | up? ? | ro? |
|? ? ? ? ? 3 | 10.0.0.53:3306 | up? ? | ro? |
+-------------+----------------+-------+------+
3 rows in set (0.00 sec)
4.2 節(jié)點(diǎn)的上線和下線
db03 [(none)]>SET OFFLINE 1;
+-------------+----------------+---------+------+
| backend_ndx | address? ? ? ? | state? | type |
+-------------+----------------+---------+------+
|? ? ? ? ? 1 | 10.0.0.55:3306 | offline | rw? |
+-------------+----------------+---------+------+
1 row in set (0.01 sec)
db03 [(none)]>SELECT * FROM backends;
+-------------+----------------+---------+------+
| backend_ndx | address? ? ? ? | state? | type |
+-------------+----------------+---------+------+
|? ? ? ? ? 1 | 10.0.0.55:3306 | offline | rw? |
|? ? ? ? ? 2 | 10.0.0.52:3306 | up? ? ? | ro? |
|? ? ? ? ? 3 | 10.0.0.53:3306 | up? ? ? | ro? |
+-------------+----------------+---------+------+
db03 [(none)]>SET ONLINE 1;
+-------------+----------------+---------+------+
| backend_ndx | address? ? ? ? | state? | type |
+-------------+----------------+---------+------+
|? ? ? ? ? 1 | 10.0.0.55:3306 | unknown | rw? |
+-------------+----------------+---------+------+
4.3 刪除和添加節(jié)點(diǎn)
db03 [(none)]>REMOVE BACKEND? 3;
db03 [(none)]>ADD SLAVE? 10.0.0.53:3306;
4.4 用戶管理
db01 [(none)]>grant all on *.* to oldguo@'10.0.0.%' identified by '123';
db03 [(none)]>SELECT * FROM pwds;
db03 [(none)]>add pwd oldguo:123;
4.5 持久化配置文件
db03 [(none)]>save config;
