MySQL高可用及讀寫分離

為什么要使用高可用？

什么是高可用?
企業(yè)高可用標(biāo)準(zhǔn)：全年無(wú)故障時(shí)間
無(wú)故障時(shí)間故障時(shí)間
99.9% 0.1% = 525.6 min KA+雙主：人為干預(yù)
99.99% 0.01% = 52.56 min MHA ORCH TMHA ：半自動(dòng)化
99.999% 0.001% = 5.256 min PXC 、 MGR 、MGC
99.9999% 0.0001% = 0.5256 min 自動(dòng)化、云化、平臺(tái)化

MHA的軟件結(jié)構(gòu)介紹及搭建過(guò)程
一堆perl寫的腳本。
2.1 manager 組件
masterha_manger 啟動(dòng)MHA
masterha_check_ssh 檢查MHA的SSH配置狀況
masterha_check_repl 檢查MySQL復(fù)制狀況
masterha_master_monitor 檢測(cè)master是否宕機(jī)
masterha_check_status 檢測(cè)當(dāng)前MHA運(yùn)行狀態(tài)
masterha_master_switch 控制故障轉(zhuǎn)移（自動(dòng)或者手動(dòng)）
masterha_conf_host 添加或刪除配置的server信息

2.2 node 組件
save_binary_logs 保存和復(fù)制master的二進(jìn)制日志
apply_diff_relay_logs 識(shí)別差異的中繼日志事件并將其差異的事件應(yīng)用于其他的
purge_relay_logs 清除中繼日志（不會(huì)阻塞SQL線程）

2.3 MHA部署

2.3.1 GTID 復(fù)制環(huán)境準(zhǔn)備

清理環(huán)境

pkill mysqld
rm -rf /data/3306/*
mv /etc/my.cnf /tmp

創(chuàng)建相關(guān)目錄

mkdir -p /data/3306/data /data/3306/binlog
chown -R mysql.mysql /data

創(chuàng)建配置文件

db01

cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3306/data
server_id=6
socket=/tmp/mysql.sock
log_bin=/data/3306/binlog/mysql-bin
gtid_mode=ON
enforce_gtid_consistency
log-slave-updates=1
secure-file-priv=/tmp
innodb_data_file_path=ibdata1:128M;ibdata2:128M:autoextend
innodb_temp_data_file_path=ibtmp1:128M;ibtmp2:128M:autoextend:max:500M
innodb_undo_tablespaces=3
innodb_max_undo_log_size=128M
innodb_undo_log_truncate=ON
innodb_purge_rseg_truncate_frequency=32
autocommit=0
innodb_flush_method=O_DIRECT
slow_query_log=ON
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes
[client]
socket=/tmp/mysql.sock
[mysql]
prompt=db01 [\d]>
socket=/tmp/mysql.sock
EOF

db02

cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3306/data
server_id=7
socket=/tmp/mysql.sock
log_bin=/data/3306/binlog/mysql-bin
gtid_mode=ON
enforce_gtid_consistency
log-slave-updates=1
secure-file-priv=/tmp
innodb_data_file_path=ibdata1:128M;ibdata2:128M:autoextend
innodb_temp_data_file_path=ibtmp1:128M;ibtmp2:128M:autoextend:max:500M
innodb_undo_tablespaces=3
innodb_max_undo_log_size=128M
innodb_undo_log_truncate=ON
innodb_purge_rseg_truncate_frequency=32
autocommit=0
innodb_flush_method=O_DIRECT
slow_query_log=ON
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes
[client]
socket=/tmp/mysql.sock
[mysql]
prompt=db02 [\d]>
socket=/tmp/mysql.sock
EOF

db03

cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3306/data
server_id=8
socket=/tmp/mysql.sock
log_bin=/data/3306/binlog/mysql-bin
gtid_mode=ON
enforce_gtid_consistency
log-slave-updates=1
secure-file-priv=/tmp
innodb_data_file_path=ibdata1:128M;ibdata2:128M:autoextend
innodb_temp_data_file_path=ibtmp1:128M;ibtmp2:128M:autoextend:max:500M
innodb_undo_tablespaces=3
innodb_max_undo_log_size=128M
innodb_undo_log_truncate=ON
innodb_purge_rseg_truncate_frequency=32
autocommit=0
innodb_flush_method=O_DIRECT
slow_query_log=ON
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes
[client]
socket=/tmp/mysql.sock
[mysql]
prompt=db03 [\d]>
socket=/tmp/mysql.sock
EOF

初始化數(shù)據(jù)

mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3306/data

啟動(dòng)數(shù)據(jù)庫(kù)

/etc/init.d/mysqld start

構(gòu)建主從

db01 創(chuàng)建復(fù)制用戶

db01 [(none)]>grant replication slave on . to repl@'10.0.0.%' identified by '123';

db02、db03 構(gòu)建主從

change master to
master_host='10.0.0.51',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
start slave;

2.3.2 MHA軟件安裝配置
2.1 規(guī)劃:
主庫(kù):
51 node
從庫(kù):
52 node
53 node manager

2.2 準(zhǔn)備環(huán)境（略。1主2從GTID）

2.3 配置關(guān)鍵程序軟連接（所有節(jié)點(diǎn)）
ln -s /data/app/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /data/app/mysql/bin/mysql /usr/bin/mysql

2.4 配置各節(jié)點(diǎn)互信（密鑰對(duì)）
db01：
rm -rf /root/.ssh
ssh-keygen
cd /root/.ssh
mv id_rsa.pub authorized_keys
scp -r /root/.ssh 10.0.0.52:/root
scp -r /root/.ssh 10.0.0.53:/root
各節(jié)點(diǎn)驗(yàn)證
db01:
ssh 10.0.0.51 date
ssh 10.0.0.52 date
ssh 10.0.0.53 date
db02:
ssh 10.0.0.51 date
ssh 10.0.0.52 date
ssh 10.0.0.53 date
db03:
ssh 10.0.0.51 date
ssh 10.0.0.52 date
ssh 10.0.0.53 date

2.5 安裝軟件

下載mha軟件

mha官網(wǎng)：https://code.google.com/archive/p/mysql-master-ha/
github下載地址：https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads

說(shuō)明：

8.0 的版本:

密碼加密模式 sha2 ---> native
使用0.58 版本MHA軟件

所有節(jié)點(diǎn)安裝Node軟件依賴包

yum install perl-DBD-MySQL -y
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

Manager軟件安裝（db03）

yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

在db01主庫(kù)中創(chuàng)建mha需要的用戶

grant all privileges on . to mha@'10.0.0.%' identified by 'mha';

2.6 Manager配置文件準(zhǔn)備(db03)

創(chuàng)建配置文件目錄

mkdir -p /etc/mha

創(chuàng)建日志目錄

mkdir -p /var/log/mha/app1

編輯mha配置文件

cat > /etc/mha/app1.cnf <<EOF
[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/binlog
user=mha
password=mha
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root
[server1]
hostname=10.0.0.51
port=3306
[server2]
hostname=10.0.0.52
port=3306
[server3]
hostname=10.0.0.53
port=3306
EOF
2.7 狀態(tài)檢查（db03）
masterha_check_ssh --conf=/etc/mha/app1.cnf
masterha_check_repl --conf=/etc/mha/app1.cnf

2.8 開(kāi)啟MHA-manager
開(kāi)啟MHA(db03)：
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &

2.9 查看MHA狀態(tài)
[root@db03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:4719) is running(0:PING_OK), master:10.0.0.51

站在產(chǎn)品經(jīng)理角度，評(píng)估高可用軟件設(shè)計(jì)
3.1 監(jiān)控
3.2 選主
3.3 數(shù)據(jù)補(bǔ)償
3.4 故障轉(zhuǎn)移
3.5 應(yīng)用透明
3.6 自動(dòng)提醒
3.7 自愈

image.png

MHA FailOver 原理
4.1 監(jiān)控：
通過(guò) masterha_master_monitor ，每隔ping_interval秒特測(cè)一此Master 心跳。
監(jiān)測(cè)不到心跳，一共給4次機(jī)會(huì)。

4.2 選主
4.2.1 備選主
candidate_master=1 強(qiáng)制某個(gè)節(jié)點(diǎn)為備選主。如果日志量超過(guò)100M差異，放棄掉他。
check_repl_delay=0 不檢查日志量的差異。

4.2.2 日志量
各個(gè)從庫(kù)回放到的日志量。
無(wú)GTID：
[root@db02 ~]# mysql -e "show slave status\G" |grep "Master_Log"
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 194
Relay_Master_Log_File: mysql-bin.000003
Exec_Master_Log_Pos: 194

有GTID：
[root@db02 ~]# mysql -e "show slave status\G" |grep "Executed_Gtid_Set"
Executed_Gtid_Set: 1c35b73a-7321-11ea-8974-000c29248f69:1-6
[root@db02 ~]#

4.2.3 如果沒(méi)有權(quán)重，從庫(kù)日志量一樣
根據(jù)配置文件的先后順序選擇新主。

4.3 日志補(bǔ)償

4.3.1 if 主庫(kù)ssh 能連接
各個(gè)從節(jié)點(diǎn)，通過(guò)save_binary_logs 立即保存缺失部分的binlog到/var/tmp/xxxxx
怎么判斷缺失日志？
有GTID？
[root@db01 ~]# mysql -e "show master status;"
[root@db02 ~]# mysql -e "show slave status\G" |grep "Retrieved_Gtid_Set"

4.3.2 eles 主庫(kù) ssh 不能連接
從節(jié)點(diǎn)調(diào)用apply_diff_relay_logs，計(jì)算兩個(gè)從節(jié)點(diǎn)的relay-log日志差異。

4.4 故障轉(zhuǎn)移

取消所有節(jié)點(diǎn)的從庫(kù)狀態(tài)
構(gòu)建新的主從關(guān)系

4.5 自動(dòng)將故障節(jié)點(diǎn)，從配置文件剔除
--remove_dead_master_conf

4.6 自殺
manager自動(dòng)退出。

4.7 應(yīng)用透明: vip
4.8 數(shù)據(jù)補(bǔ)償補(bǔ)充方案：binlog_server
4.9 切換提醒：send_report

模擬故障并恢復(fù)
5.0 工作狀態(tài)查看
[root@db03 app1]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:17501) is running(0:PING_OK), master:10.0.0.51

5.1 宕主庫(kù)測(cè)試
[root@db01 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!
[root@db01 ~]#

5.2 看日志
[root@db03 app1]# vim /var/log/mha/app1/manager

5.3 恢復(fù)
5.3.1 修復(fù)故障節(jié)點(diǎn)
[root@db01 ~]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS!

如果生產(chǎn)怎么辦？
按實(shí)際情況。

5.3.2 恢復(fù)主從
change master to
master_host='10.0.0.52',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
start slave;

5.3.3 修復(fù)配置文件

方法一：
vim /etc/mha/app1.cnf
[server1]
hostname=10.0.0.51
port=3306

方法二：
[root@db03 ~]# masterha_conf_host --command=add --conf=/etc/mha/app1.cnf --hostname=10.0.0.51 --block=server10 --params="port=3306"
masterha_conf_host --command=delete --conf=/etc/mha/app1.cnf --block=server1

5.3.4 預(yù)檢測(cè)腳本
[root@db03 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
[root@db03 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf

5.3.5 啟動(dòng)MHA
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 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:24316) is running(0:PING_OK), master:10.0.0.52
[root@db03 ~]#

應(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/*

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 $if = 'ens33'; my$ ssh_start_vip = "/sbin/ifconfig $if:$ key $vip"; my$ ssh_stop_vip = "/sbin/ifconfig $if:$ key down";
my $ssh_Bcast_arp= "/sbin/arping -I$ if -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 ens33:1 10.0.0.55/24

故障提醒功能
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 觀察郵件

image.png

7.5 修復(fù)MHA 架構(gòu)1主2從
略

日志補(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 ~]#

mysqlbinlog -R --host=10.0.0.52 --user=mha --password=mha --raw --stop-never mysql-bin.000003 &

注意：
拉取日志的起點(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 &

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

此種方法切換，要注意將原主庫(kù)，F(xiàn)TWRL（Flush table with read lock），否則會(huì)造成主從不一致。
手工切換vip
重新拉去新主庫(kù)的binlog

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.4 在線切換
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.5 重構(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.6 啟動(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

===================
Altas 讀寫分離

介紹
Atlas是由 Qihoo 360, Web平臺(tái)部基礎(chǔ)架構(gòu)團(tuán)隊(duì)開(kāi)發(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
注意：
1、Atlas只能安裝運(yùn)行在64位的系統(tǒng)上
2、Centos 5.X安裝 Atlas-XX.el5.x86_64.rpm，Centos 6.X安裝Atlas-XX.el6.x86_64.rpm。
3、后端mysql版本應(yīng)大于5.1，建議使用Mysql 5.6以上

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.51: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

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;

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)

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;

==================================================

MySQL PT（percona-toolkit）工具使用
[root@db01 ~]# yum install -y percona-toolkit-3.1.0-2.el7.x86_64.rpm

5.1. 系統(tǒng)配置總覽

pt-summary

作用：系統(tǒng)狀態(tài)總覽。
應(yīng)用場(chǎng)景：系統(tǒng)巡檢工作。

表歸檔：pt-archiver

重要參數(shù)

--limit 100 每次取100行數(shù)據(jù)用pt-archive處理
--txn-size 100 設(shè)置100行為一個(gè)事務(wù)提交一次
--where 'id<3000' 設(shè)置操作條件
--progress 5000 每處理5000行輸出一次處理信息
--statistics 輸出執(zhí)行過(guò)程及最后的操作統(tǒng)計(jì)。
--charset=UTF8 指定字符集為UTF8—這個(gè)最后加上不然可能出現(xiàn)亂碼。
--bulk-delete 批量刪除source上的舊數(shù)據(jù)(例如每次1000行的批量刪除操作)
--for-update：在每個(gè)select語(yǔ)句后面加入for update

經(jīng)典需求：

一張表有 10億+ ，現(xiàn)在要求按照條件1000w數(shù)據(jù)
歸檔數(shù)據(jù)到別的節(jié)點(diǎn)。

使用案例：

歸檔到數(shù)據(jù)庫(kù)(本地)

pt-archiver --source h=10.0.0.51,D=world,t=city,u=root,p=123 --dest h=10.0.0.51,D=world,t=city2,u=root,p=123 --where 'id<1000' --no-check-charset --no-delete --limit=100 --commit-each --progress 200 --statistics

歸檔到數(shù)據(jù)庫(kù)(異地)

pt-archiver --source h=10.0.0.51,D=world,t=city,u=root,p=123 --dest h=10.0.0.52,D=world,t=city2,u=root,P=3307,p=123 --where 'id<1000' --no-check-charset --no-delete --limit=100 --commit-each --progress 200 --statistics

只清理數(shù)據(jù)

pt-archiver --source h=10.0.0.51,D=world,t=city,u=root,p=123 --where 'id<1000' --purge --limit=1 --no-check-charset

只把數(shù)據(jù)導(dǎo)出到外部文件，但是不刪除源表里的數(shù)據(jù)

pt-archiver --source h=10.0.0.51,D=world,t=city,u=root,p=123 --where 'id>900' --no-check-charset --no-delete --file="/tmp/archiver.dat"

pt-osc Online DDL
原理：
1、檢查更改表是否有主鍵或唯一索引，是否有觸發(fā)器
2、檢查修改表的表結(jié)構(gòu)，創(chuàng)建一個(gè)臨時(shí)表，在新表上執(zhí)行ALTER TABLE語(yǔ)句
3、在源表上創(chuàng)建三個(gè)觸發(fā)器分別對(duì)于INSERT UPDATE DELETE操作
4、從源表拷貝數(shù)據(jù)到臨時(shí)表，在拷貝過(guò)程中，對(duì)源表的更新操作會(huì)寫入到新建表中
5、將臨時(shí)表和源表rename（需要元數(shù)據(jù)修改鎖，需要短時(shí)間鎖表）
6、刪除源表和觸發(fā)器，完成表結(jié)構(gòu)的修改。

=====================================================##

pt-osc工具限制
1、源表必須有主鍵或唯一索引，如果沒(méi)有工具將停止工作
2、如果線上的復(fù)制環(huán)境過(guò)濾器操作過(guò)于復(fù)雜，工具將無(wú)法工作
3、如果開(kāi)啟復(fù)制延遲檢查，但主從延遲時(shí)，工具將暫停數(shù)據(jù)拷貝工作
4、如果開(kāi)啟主服務(wù)器負(fù)載檢查，但主服務(wù)器負(fù)載較高時(shí)，工具將暫停操作
5、當(dāng)表使用外鍵時(shí)，如果未使用--alter-foreign-keys-method參數(shù)，工具將無(wú)法執(zhí)行
6、只支持Innodb存儲(chǔ)引擎表，且要求服務(wù)器上有該表1倍以上的空閑空間。

pt-osc之a(chǎn)lter語(yǔ)句限制
1、不需要包含alter table關(guān)鍵字，可以包含多個(gè)修改操作，使用逗號(hào)分開(kāi)，如"drop clolumn c1, add column c2 int"
2、不支持rename語(yǔ)句來(lái)對(duì)表進(jìn)行重命名操作
3、不支持對(duì)索引進(jìn)行重命名操作
4、如果刪除外鍵，需要對(duì)外鍵名加下劃線，如刪除外鍵fk_uid, 修改語(yǔ)句為"DROP FOREIGN KEY _fk_uid"
pt-osc之命令模板

--execute表示執(zhí)行

--dry-run表示只進(jìn)行模擬測(cè)試

表名只能使用參數(shù)t來(lái)設(shè)置，沒(méi)有長(zhǎng)參數(shù)

pt-online-schema-change
--host="127.0.0.1"
--port=3358
--user="root"
--password="root@root"
--charset="utf8"
--max-lag=10
--check-salve-lag='xxx.xxx.xxx.xxx'
--recursion-method="hosts"
--check-interval=2
--database="testdb1"
t="tb001"
--alter="add column c4 int"
--execute

例子：
pt-online-schema-change --user=root --password=123 --host=10.0.0.52 --alter "add column age int default 0" D=world,t=city --print --execute

主從一致性校驗(yàn)

（1）創(chuàng)建數(shù)據(jù)庫(kù)

Create database pt CHARACTER SET utf8;

創(chuàng)建用戶checksum并授權(quán)

GRANT ALL ON . TO 'checksum'@'10.0.0.%' IDENTIFIED BY 'checksum';
flush privileges;

--[no]check-replication-filters：是否檢查復(fù)制的過(guò)濾器，默認(rèn)是yes，建議啟用不檢查模式。
--databases | -d：指定需要被檢查的數(shù)據(jù)庫(kù)，多個(gè)庫(kù)之間可以用逗號(hào)分隔。
--[no]check-binlog-format：是否檢查binlog文件的格式，默認(rèn)值yes。建議開(kāi)啟不檢查。因?yàn)樵谀J(rèn)的row格式下會(huì)出錯(cuò)。
--replicate：把checksum的信息寫入到指定表中。
--replicate-check-only：只顯示不同步信息

小坑

所有庫(kù)：
autocommit=1

從庫(kù) ：
report_host=10.0.0.xx
report_port=3306

應(yīng)用：

針對(duì)表校驗(yàn)：
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=pt.checksums --create-replicate-table --databases=world --tables=city h=10.0.0.52,u=checksum,p=checksum,P=3306

針對(duì)庫(kù)校驗(yàn)：
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=pt.checksums --create-replicate-table --databases=test h=10.0.0.51,u=checksum,p=checksum,P=3306

腳本模板：

!/bin/bash

date >> /root/db/checksum.log
pt-table-checksum --nocheck-binlog-format --nocheck-plan
--nocheck-replication-filters --replicate=pt.checksums --set-vars
innodb_lock_wait_timeout=120 --databases test -u'checksum' -p'checksum'
-h'10.0.0.11' >> /root/db/checksum.log
date >> /root/db/checksum.log

pt-table-sync

主要參數(shù)介紹
--replicate ：指定通過(guò)pt-table-checksum得到的表.
--databases : 指定執(zhí)行同步的數(shù)據(jù)庫(kù)。
--tables ：指定執(zhí)行同步的表，多個(gè)用逗號(hào)隔開(kāi)。
--sync-to-master ：指定一個(gè)DSN，即從的IP，他會(huì)通過(guò)show processlist或show slave status 去自動(dòng)的找主。
h= ：服務(wù)器地址，命令里有2個(gè)ip，第一次出現(xiàn)的是Master的地址，第2次是Slave的地址。
u= ：帳號(hào)。
p= ：密碼。
--print ：打印，但不執(zhí)行命令。
--execute ：執(zhí)行命令。
pt-table-sync --replicate=pt.checksums h=10.0.0.52,u=root,p=123,P=3306 --print
pt-table-sync --replicate=pt.checksums h=10.0.0.52,u=root,p=123,P=3306 --execute

顯示主從結(jié)構(gòu)：pt-slave-find

[root@db01 tmp]# pt-slave-find -h10.0.0.52 -P3306 -uchecksum -pchecksum
10.0.0.51
Version 5.7.28-log
Server ID 51
Uptime 27:57 (started 2020-05-15T13:24:15)
Replication Is not a slave, has 1 slaves connected, is not read_only
Filters
Binary logging ROW
Slave status
Slave mode STRICT
Auto-increment increment 1, offset 1
InnoDB version 5.7.28
+- 10.0.0.52
Version 5.7.28-log
Server ID 52
Uptime 28:18 (started 2020-05-15T13:23:54)
Replication Is a slave, has 0 slaves connected, is not read_only
Filters
Binary logging ROW
Slave status 0 seconds behind, running, no errors
Slave mode STRICT
Auto-increment increment 1, offset 1
InnoDB version 5.7.28
[root@db01 tmp]#

6.檢查指定表的重復(fù)索引

pt-duplicate-key-checker

db01 [world]>alter table city add index idx(countrycode,population);

[root@db01 tmp]# pt-duplicate-key-checker --host=10.0.0.52 --user='checksum' --password='checksum' --databases=world --tables=city

world.city

CountryCode is a left-prefix of idx

Key definitions:

KEY `CountryCode` (`CountryCode`),

KEY `idx` (`CountryCode`,`Population`),

Column types:

`countrycode` char(3) not null default ''

`population` int(11) not null default '0'

To remove this duplicate index, execute:

ALTER TABLE world.city DROP INDEX CountryCode;

Summary of indexes

Size Duplicate Indexes 12564

Total Duplicate Indexes 1

Total Indexes 4

[root@db01 tmp]#

監(jiān)控主從延時(shí)

pt-heartbeat

主庫(kù)：
pt-heartbeat --user=root --ask-pass --host=10.0.0.52 --create-table -D test --interval=1 --update --replace --daemonize

從庫(kù)：
pt-heartbeat --user=root --ask-pass --host=10.0.0.51 -D test --table=heartbeat --monitor

pt-show-grants

pt-show-grants -h10.0.0.52 -P3306 -uchecksum -pchecksum

-- Grants dumped by pt-show-grants
-- Dumped from server 10.0.0.51 via TCP/IP, MySQL 5.7.28-log at 2020-05-15 17:11:06
-- Grants for 'checksum'@'10.0.0.%'
CREATE USER IF NOT EXISTS 'checksum'@'10.0.0.%';
ALTER USER 'checksum'@'10.0.0.%' IDENTIFIED WITH 'mysql_native_password' AS 'E5E390AF1BDF241B51D9C0DBBEA262CC9407A2DF' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON . TO 'checksum'@'10.0.0.%';
-- Grants for 'mysql.session'@'localhost'
CREATE USER IF NOT EXISTS 'mysql.session'@'localhost';
ALTER USER 'mysql.session'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS 'THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;
GRANT SELECT ON mysql.user TO 'mysql.session'@'localhost';
GRANT SELECT ON performance_schema.* TO 'mysql.session'@'localhost';
GRANT SUPER ON . TO 'mysql.session'@'localhost';
-- Grants for 'mysql.sys'@'localhost'
CREATE USER IF NOT EXISTS 'mysql.sys'@'localhost';
ALTER USER 'mysql.sys'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS 'THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;
GRANT SELECT ON sys.sys_config TO 'mysql.sys'@'localhost';
GRANT TRIGGER ON sys. TO 'mysql.sys'@'localhost';
GRANT USAGE ON . TO 'mysql.sys'@'localhost';
-- Grants for 'repl'@'10.0.0.%'
CREATE USER IF NOT EXISTS 'repl'@'10.0.0.%';
ALTER USER 'repl'@'10.0.0.%' IDENTIFIED WITH 'mysql_native_password' AS '23AE809DDACAF96AF0FD78ED04B6A265E05AA257' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT REPLICATION SLAVE ON . TO 'repl'@'10.0.0.%';
-- Grants for 'root'@'10.0.0.%'
CREATE USER IF NOT EXISTS 'root'@'10.0.0.%';
ALTER USER 'root'@'10.0.0.%' IDENTIFIED WITH 'mysql_native_password' AS '23AE809DDACAF96AF0FD78ED04B6A265E05AA257' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON . TO 'root'@'10.0.0.%';
-- Grants for 'root'@'localhost'
CREATE USER IF NOT EXISTS 'root'@'localhost';
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;

給出參數(shù)建議：

pt-variable-advisor 10.0.0.52 -uchecksum -pchecksum

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

Linux學(xué)習(xí)-MySQL-week05