MYSQL實戰-MHA搭建及問題總結
##################MHA部署####################
1.伺服器
192.168.56.21 mydb1 #Master
192.168.56.22 mydb2 #Slave
192.168.56.23 mydb3 #MHA manager
2. 所有機器修改vim /etc/hosts
192.168.56.21 mydb1
192.168.56.22 mydb2
192.168.56.23 mydb3
3.設定SSH公鑰免密碼登入
#root使用者操作(/root),pwd
mkdir ~/.ssh
chmod 700 ~/.ssh
cd ~/.ssh
ssh-keygen -t rsa #一直回車
ls -al
cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
ls -al
chmod 600 ~/.ssh/authorized_keys
cd ..
scp -r .ssh 192.168.56.22:/root/
scp -r .ssh 192.168.56.23:/root/
ssh mydb1
ssh mydb2
ssh mydb3
4.下載mha4mysql-manager和mha4mysql-node
https://github.com/yoshinorim/mha4mysql-manager
https://github.com/yoshinorim/mha4mysql-node
5.在三個節點（node 和 manager）安裝perl-DBD-MySQL，用光碟作yum源，#包括了3個包：Perl-DBD-MySQL,mysql-libs,perl-DBI
yum install perl-DBD-MySQL
yum install perl-DBI
yum install mysql-libs
6.在三個節點安裝node（包括管理節點）
yum -y install perl-CPAN 下載perl需要的依賴包庫
perl -MCPAN -e "install inc::Module::Install 在依賴包庫中，查詢和安裝Makefile.PL需要的包，具體程式是 use inc::Module::Install;
參考https://perlmaven.com/cant-locate-inc-module-install-in-inc
上面實際下載的是Module-Install-1.18.tar.gz 這個包
//perl -MCPAN -e shell
//perl -MCPAN -e "install Module::Depends"
參考 http://cache.baiducontent.com/c?m=9f65cb4a8c8507ed4fece7631046893b4c4380147d8c8c4668d4e419ce3b4c413037bfa6663f405a8e906b6075ab4f5bbdf06270670123b598c8d40895ac925f75ce786a6459db0144dc42f39a5125b07fcd0caef14ef0ba8169cfef9480810a44cb23127af7e78a2a424bdd6f814f6df4a6e85f135d07bb9d6b39ae&p=8b2a971cc89401ff57ee967c525285&newp=80769a478a8702c308e2977c0f43cc231610db2151d7d0106b82c825d7331b001c3bbfb423241a05d8ce7c6d04aa4b5feaf4357534072ba3dda5c91d9fb4c57479d463771c&user=baidu&fm=sc&query=inc/Module/Install%2Epm+in+%40INC&qid=e9eec041000013a8&p1=10
tar xzvf mha4mysql-node-0.56.tar.gz
cd mha4mysql-node-0.56
perl Makefile.PL
注意 在安裝perl install模組後，這裡終於可以正常執行了，在執行PL後，系統會驗證哪些包missing，這時就可以根據他的提示安裝少的包了
make && make install
7.在管理節點安裝manager
//可以先檢視本機已安的包名
rpm -qa perl-Config-Tiny perl-Params-Validate perl-MIME-Types perl-Email-Date-Format perl-Mail-Sender perl-Mail-Sendmail perl-TimeDate perl-MailTools perl-MIME-Lite perl-Log-Dispatch perl-Parallel-ForkManager
共11個包
rpm -qa perl-Config-Tiny perl-Params-Validate perl-MIME-Types perl-Email-Date-Format perl-Mail-Sender perl-Mail-Sendmail perl-TimeDate perl-MailTools perl-MIME-Lite perl-Log-Dispatch perl-Parallel-ForkManager | xargs rpm -ivh
上面命令直接安裝
rpm -ivh perl-Config-Tiny-2.12-1.el5.rf.noarch.rpm
rpm -ivh perl-Params-Validate-0.95-1.el5.rf.i386.rpm
rpm -ivh perl-MIME-Types-1.28-2.el6.noarch.rpm
rpm -ivh perl-Email-Date-Format-1.002-5.el6.noarch.rpm
rpm -ivh perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
rpm -ivh perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
rpm -ivh perl-TimeDate-1.16-11.1.el6.noarch.rpm
rpm -ivh perl-MailTools-2.04-4.el6.noarch.rpm
rpm -ivh perl-MIME-Lite-3.027-2.el6.noarch.rpm
rpm -ivh perl-Log-Dispatch-2.26-1.el5.rf.noarch.rpm
rpm -ivh perl-Parallel-ForkManager-0.7.5-2.2.el5.rf.noarch.rpm
如果上面包都沒有，直接從yum源下載並安裝
yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes -y
yum install perl-Time-HiRes #光碟yum源
tar xzvf mha4mysql-manager-0.56.tar.gz
cd mha4mysql-manager-0.56
perl Makefile.PL
make && make install
8 配置主從同步GTID方式
這裡用的是init.sh指令碼，能自動搭建主從GTID伺服器，且在一臺機器上。當機器上沒有mysql使用者時，此指令碼有些問題，在指令碼初始化後要
做的修改是
show grants for 'rpl_user@127.0.0.1';
主庫上建立repl使用者
grant replication slave on *.* to rpl_user@% identified by '1234';
從庫上去掉多餘的rpl_user,注意要先去掉許可權才能刪除這個使用者
revoke replication slave on *.* from rpl_user@127.0.0.1
revoke replication slave on *.* from 'rpl_user'@'%';
drop user 'rpl_user'@'%';
9修改root使用者預設密碼
安裝mysql開始 root使用者沒有密碼，怕影響mha，所以建立root使用者密碼
use mysql;
mysql> (老版本)update user set password=password("123456") where user="root";
（5.7.11）update user set authentication_string=password("123456") where user="root";
flush privileges;
ln -s /usr/local/mysql_5.7/bin/mysql /usr/bin //軟連結呼叫mysql命令
####################常用命令###############
/usr/local/bin/masterha_check_ssh --conf=/u01/mha/etc/app.cnf
/usr/local/bin/masterha_check_repl --conf=/u01/mha/etc/app.cnf
/usr/local/bin/masterha_manager --conf=/u01/mha/etc/app.cnf &
/u01/mha/etc/masterha_check_status --conf=/u01/mha/etc/app.cnf
/usr/local/bin/masterha_stop --conf=/u01/mha/etc/app.cnf
masterha_check_ssh --conf=/u01/mha/etc/app.cnf //監控ssh是否正常
masterha_check_repl --conf=/u01/mha/etc/app.cnf //監控主從複製是否正常
MHA masterha_check_repl 檢測過程
###################切換######################
一、failover故障切換
1.模擬主庫當機
[root@mydb3~]# ssh mydb1 "killall -r mysqld"
2.檢視管理節點日誌，可以看到VIP已經漂移
[root@mydb3 ~]# cat /u01/mha/log/manager.log |grep -i vip
3. 驗證VIP是否位於節點mydb2
[root@mydb3 ~]# ssh mydb2 "ifconfig |grep 56.201 -B1"
4. 檢視管理節點MHA切換日誌
[root@mydb3 ~]# tail /u01/mha/log/manager.log
5. new master(old slave)
show master status\G
6. new slave(old:master)
(1)開啟MySQL
mysqld_safe --defaults-file=/u01/my3306/my.cnf &
(2)檢查資料庫
show master status\G
show slave status\G
(3)在管理節點日誌中查主庫的日誌檔案和位置
cat /u01/mha/etc/app/manager.log |grep -i change
(4)在slave連線master
CHANGE MASTER TO
MASTER_HOST='192.168.56.21',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=120,
MASTER_USER='rep',
MASTER_PASSWORD='rep123';
7.啟動管理節點
/usr/local/bin/masterha_manager --conf=/u01/mha/etc/app.cnf &
/usr/local/bin/masterha_manager --conf=/u01/mha/etc/app.cnf --ignore_last_failover &
二、switchover線上切換
1.master：關閉event_scheduler（即192.168.56.21）
set global event_scheduler=off;
2.manager：關閉管理程式 （即192.168.56.23）
/usr/local/bin/masterha_stop --conf=/u01/mha/etc/app.cnf
3.manager：檢查配置檔案
/u01/mha/etc/app.cnf 有沒有被修改破壞。如果破壞需要重新編輯正確配置檔案：/u01/mha/etc/app.cnf
cp /u01/mha/etc/app.cnf.bak /u01/mha/etc/app.cnf
4.開始切換：
/u01/mha/etc/masterha_master_switch --master_state=alive --conf=/u01/mha/etc/app.cnf
5.new master(old slave)
mysql> show master status\G
*************************** 1. row ***************************
File: binlog.000021
Position: 299
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
6.new slave(old master)
CHANGE MASTER TO
MASTER_HOST='192.168.56.22',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000021',
MASTER_LOG_POS=299,
MASTER_USER='rep',
MASTER_PASSWORD='rep123';
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.12
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000017
Read_Master_Log_Pos: 299
Relay_Log_File: relaylog.000002
Relay_Log_Pos: 280
Relay_Master_Log_File: binlog.000017
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
7.啟動管理節點
/u01/mha/etc/masterha_manager --conf=/u01/mha/etc/app.cnf &
/u01/mha/etc/masterha_manager --conf=/u01/mha/etc/app.cnf --remove_dead_master_conf --ignore_last_failover
https://www.cnblogs.com/zhoujinyi/p/3808673.html
#################配置檔案###################
vim /u01/mha/etc/app.cnf
[server default]
user = root
password = 123456
ssh_user = root
repl_user = rpl_user
repl_password = rpl_pass
ping_interval = 1
ping_type = SELECT
manager_workdir=/u01/mha/etc/app
manager_log=/u01/mha/log/manager.log
remote_workdir=/u01/mha/etc/app
master_binlog_dir="/u01/mysql_5.7/s1/log/binlog"
#master_ip_failover_script="/u01/mha/etc/master_ip_failover"
#master_ip_online_change_script="/u01/mha/etc/master_ip_failover"
shutdown_script=""
report_script=""
#check_repl_delay=0
[server1]
hostname=iZ2zehy7gff0kpg1swp1czZ
port=33091
master_binlog_dir="/apps/dbdat/mysql5_data33091/log"
candidate_master=1
ignore_fail=1
[server2]
hostname=iZ2zehy7gff0kpg1swp1czZ
port=33092
master_binlog_dir="/u01/mysql_5.7/s2/log/binlog"
candidate_master=1
ignore_fail=1
---------------------------------------------------------------------------------
vim /u01/mha/etc/master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
my $vip = '192.168.56.201/24'; # Virtual IP
my $key = "1";
my $int = "eth0";
my $ssh_start_vip = "/sbin/ifconfig $int:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig $int:$key down";
my $arp_effect = "/sbin/arping -Uq -s192.168.56.201 -I $int 192.168.56.1 -c 3"; # Virtual IP and gat
eway
#my $test = "echo successfull >/tmp/test.txt";
$ssh_user = "root";
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);
exit &main();
sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at global catalog database,
# invalidate orig_master_ip here.
my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
# all arguments are passed.
# If you manage master ip address at global catalog database,
# activate new_master_ip here.
# You can also grant write access (create user, set read_only=0, etc) here.
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
#`ssh $ssh_user\@cluster1 \" $ssh_start_vip \"`;
&status();
exit 0;
}
else {
&usage();
exit 1;
}
}
# A simple system call that enable the VIP on the new master
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
`ssh $ssh_user\@$new_master_host \" $arp_effect \"`;
# `ssh $ssh_user\@$new_master_host \" $test \"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub status() {
print `ssh $ssh_user\@$orig_master_host \" ip add show $int \"`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_maste
r_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/750077/viewspace-2147125/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql讀寫分離實戰二-windows 上mysql主從資料庫搭建及問題總結MySqlWindows資料庫
- MySQL MHA部署實戰MySql
- Mysql MHA部署-07常見問題MySql
- MHA架構搭建中遇到的問題架構
- MySQL專案實戰總結MySql
- MYSQL+複製+MHA+VIP配置總結MySql
- MHA搭建及故障維護
- mysql常見問題總結MySql
- mysql相關問題總結MySql
- Mysql安裝過程問題總結及處理方法MySql
- MySQL MHA詳細搭建過程MySql
- mysql高可用架構MHA搭建MySql架構
- javascript的物件問題及總結JavaScript物件
- mysql MHA搭建和切換測試MySql
- 架構-穩定性建設邏輯問題實戰總結架構
- MySQL高可用方案MHA的一些總結和思考MySql
- 【MySQL】MHA的基本配置及註釋MySql
- MySQL資料庫實現高可用架構之MHA的實戰MySql資料庫架構
- PHP問題總結：PHP最佳化及高效提速問題小結(轉)PHP
- MHA配置搭建
- MySQL運維實戰系列：MySQL5.7GroupBy問題MySql運維
- 問題總結
- mysql高可用架構MHA搭建（centos7+mysql5.7.28）MySql架構CentOS
- 【MHA】mysql高可用之MHAMySql
- Manila實戰總結
- MySQL修改資料型別的問題總結MySql資料型別
- EF使用MySql DBFirst產品的問題總結MySql
- Oracle 錯誤總結及問題解決 ORAOracle
- Python及requests亂碼問題的總結Python
- 專案中遇到的RediS快取問題及面試問題總結Redis快取面試
- Mysql MHA部署-04MHA配置MySql
- MHA搭建步驟
- iOS開發——專案實戰總結&UITableView效能優化與卡頓問題iOSUIView優化
- Elasticsearch 問題總結Elasticsearch
- Swoole 問題總結
- Kerberos問題總結ROS
- 面試問題總結面試
- mysql之MHAMySql