MySQL高可用架構之MHA實踐
本文主要是描述MHA高可用快速部署步驟以及簡單使用命令
1、下載安裝包
mha 依賴包：
perl-DBD-MySQL
perl-Config-Tiny
perl-Log-Dispatch
perl-Parallel-ForkManager
perl-Config-IniFiles
perl-MailTools
perl-Params-Validate
perl-TimeDate
perl-IO-stringy
cmake-2.8.12.2.tar.gz
DBD-mysql-4.033_02.tar.gz
DBI-1.636.tar.gz
mha rpm包安裝：
mha4mysql-manager-0.56-0.el6.noarch.rpm
mha4mysql-node-0.56-0.el6.noarch.rpm
mha 原始碼安裝：
mha4mysql-node-0.56.tar.gz
mha4mysql-manager-0.56.tar.gz
MySQL安裝包：
percona-server-5.6.32-78.0.tar.gz
2、解壓安裝包
for i in `ls`; do tar -xzvf $i; tar -xvf $i; done
3、安裝依賴包
配置本地yum源：
[root@node3 MHA]# mkdir /media/cdrom
[root@node3 MHA]# mount CentOS-6.4-x86_64-bin-DVD1.iso /media/cdrom/ -o loop
[root@node3 MHA]# rm -rf /etc/yum.repos.d/*.repo
[root@node3 MHA]# vi /etc/yum.repos.d/CentOS6.repo
[Base]
name=CentOS6 ISO Base
baseurl=file:///media/cdrom
enabled=1
gpgcheck=0
依賴包檢查安裝：
yum install -y git scons gcc g++ gcc-c++ openssl check cmake bison libaio libboost-all-dev libasio-dev libaio-dev libncurses5-dev libreadline-dev libpam-dev ncurses-devel
yum -y install gcc gcc-c++ gcc-g77 autoconf automake zlib* fiex* libxml* ncurses-devel libmcrypt* libtool-ltdl-devel* make cmake
rpm -q git scons gcc g++ gcc-c++ openssl check cmake bison libaio libboost-all-dev libasio-dev libaio-dev libncurses5-dev libreadline-dev libpam-dev ncurses-devel
cmake安裝：
[root@node3 MHA]# cd cmake-2.8.12.2
[root@node3 cmake-2.8.12.2]# ./bootstrap
[root@node3 cmake-2.8.12.2]# make -j 8
[root@node3 cmake-2.8.12.2]# make install
Perl工具安裝；
[root@node3 MHA]# rpm -ivh perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm
[root@node3 MHA]# rpm -ivh perl-Config-Tiny-2.12-7.1.el6.noarch.rpm
[root@node3 MHA]# rpm -ivh perl-Params-Validate-0.95-5.9.x86_64.rpm
[root@node3 MHA]# rpm -ivh perl-TimeDate-2.22-1.noarch.rpm
[root@node3 MHA]# rpm -ivh perl-MailTools-2.04-4.el6.noarch.rpm （不是必須）
[root@node3 MHA]# rpm -ivh perl-Convert-BinHex-1.119-4.el6.noarch.rpm --nodeps （不是必須）
[root@node3 MHA]# rpm -ivh perl-List-MoreUtils-0.33-107.1.x86_64.rpm （不是必須）
[root@node3 MHA]# rpm -ivh perl-IO-stringy-2.110-8.el6.noarch.rpm --nodeps （不是必須）
[root@node3 MHA]# rpm -ivh perl-Config-IniFiles-2.72-2.el6.noarch.rpm --nodeps
[root@node3 MHA]# rpm -ivh perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
[root@node3 MHA]# rpm -ivh perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
[root@node3 MHA]# rpm -ivh perl-Log-Dispatch-2.27-1.el6.noarch.rpm --nodeps
DBI安裝：
[root@node3 MHA]# cd DBI-1.636
[root@node3 DBI-1.636]# perl Makefile.PL
[root@node3 DBI-1.636]# make -j 8
[root@node3 DBI-1.636]# make install
DBD安裝：
[root@node3 MHA]# cd DBD-mysql-4.033_02
[root@node3 DBD-mysql-4.033_02]# perl Makefile.PL --mysql_config=/usr/local/mysql/bin/mysql_config
[root@node3 DBD-mysql-4.033_02]# make -j 8
[root@node3 DBD-mysql-4.033_02]# make install
4、MySQL安裝和主從部署
Percona Server 5.6安裝
[root@node3 MHA]# tar -xzvf percona-server-5.6.32-78.0.tar.gz
[root@node3 MHA]# useradd mysql -s /sbin/nologin
[root@node3 MHA]# cd percona-server-5.6.32-78.0
[root@node3 percona-server-5.6.32-78.0]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_DATADIR=/usr/local/mysql/data/ -DMYSQL_USER=mysql -DENABLE_DOWNLOADS=1 -DWITH_WSREP=1 -DWITH_EDITLINE=0
[root@node3 percona-server-5.6.32-78.0]# make -j 4
[root@node3 percona-server-5.6.32-78.0]# make install
初始化資料庫：
[root@node3 MHA]# cd /usr/local/mysql
[root@node3 mysql]# ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/
[root@node3 mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@node3 mysql]# chkconfig --add mysqld
[root@node3 mysql]# chkconfig mysqld on
[root@node3 mysql]# chown -R mysql.mysql /usr/local/mysql
[root@node3 mysql]# vi ~/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
[root@node3 mysql]# source ~/.bash_profile
配置my.cnf
node1節點：
[client]
socket=/usr/local/mysql/mysql.sock
[mysqld]
datadir=/usr/local/mysql/data
user=mysql
log-bin=mysql-binlog
binlog_format=ROW
server-id=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
node2節點
[client]
socket=/usr/local/mysql/mysql.sock
[mysqld]
datadir=/usr/local/mysql/data
user=mysql
log-bin=mysql-binlog
binlog_format=ROW
server-id=2
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
node3節點
[client]
socket=/usr/local/mysql/mysql.sock
[mysqld]
datadir=/usr/local/mysql/data
user=mysql
log-bin=mysql-binlog
binlog_format=ROW
server-id=3
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
啟動Node1資料庫
[root@node1 mysql]# service mysqld start
清理MySQL無用賬戶
mysql> delete from mysql.user where user ='';
mysql> delete from mysql.user where user ='root' and host='::1';
mysql> delete from mysql.user where user ='root' and host='node1';
mysql> delete from mysql.user where user ='root' and host='127.0.0.1';
建立manager管理賬號
mysql> GRANT SUPER,RELOAD,REPLICATION CLIENT,SELECT ON *.* TO manager@'10.x.x.%' IDENTIFIED BY 'manager';
mysql> GRANT CREATE,INSERT,UPDATE,DELETE,DROP ON*.* TO manager@'10.x.x.%';
建立主從複製賬號
mysql> GRANT RELOAD, SUPER, REPLICATION SLAVE ON*.* TO 'replicat'@'10.x.x.%' IDENTIFIED BY 'backup';
mysql> flush privileges;
啟動node2節點資料庫
[root@node2 mysql]# service mysqld start
啟動node3節點資料庫
[root@node3 mysql]# service mysqld start
主從資料庫複製搭建：
node1登陸MYSQL後執行
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-binlog.000005
Position: 973
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
node2登陸MYSQL後執行
mysql> change master to master_host='masterip',master_user='replicat',master_password='backup',master_port=3306,master_log_file='mysql-binlog.000005',master_log_pos= 973;
mysql> start slave;
node3登陸MYSQL後執行
mysql> change master to master_host='masterip',master_user='replicat',master_password='backup',master_port=3306,master_log_file='mysql-binlog.000005',master_log_pos= 973;
mysql> start slave;
5、MHA安裝和部署
node1為master節點，node2,node3為slave節點並且node3為manager節點
1）配置ssh對等性
node1
[root@node1 MHA]# ssh-keygen -t rsa
[root@node1 MHA]# vi ~/.ssh/authorized_keys
將node1,node2,node3的公鑰檔案id_rsa.pub內容放進去
node2
[root@node2 MHA]# ssh-keygen -t rsa
將node1的authorized_keys複製到/root/.ssh/下
node3
[root@node3 MHA]# ssh-keygen -t rsa
將node1的authorized_keys複製到/root/.ssh/下
透過ssh互相登陸驗證，確保ssh登陸不需要輸入密碼。
需要配置/etc/hosts主機名和IP的解析
ip2 node2
ip3 node3
ip1 node1
2）node節點原始碼安裝
node1
[root@node1 MHA]# tar -xzvf mha4mysql-node-0.56.tar.gz
[root@node1 MHA]# cd mha4mysql-node-0.56
[root@node1 mha4mysql-node-0.56]# perl Makefile.PL
[root@node1 mha4mysql-node-0.56]# make -j 8
[root@node1 mha4mysql-node-0.56]# make install
node2
[root@node2 MHA]# tar -xzvf mha4mysql-node-0.56.tar.gz
[root@node2 MHA]# cd mha4mysql-node-0.56
[root@node2 mha4mysql-node-0.56]# perl Makefile.PL
[root@node2 mha4mysql-node-0.56]# make -j 8
[root@node2 mha4mysql-node-0.56]# make install
node3
[root@node3 MHA]# tar -xzvf mha4mysql-node-0.56.tar.gz
[root@node3 MHA]# cd mha4mysql-node-0.56
[root@node3 mha4mysql-node-0.56]# perl Makefile.PL
[root@node3 mha4mysql-node-0.56]# make -j 8
[root@node3 mha4mysql-node-0.56]# make install
3）manager節點原始碼安裝
[root@node3 MHA]# tar -xzvf mha4mysql-manager-0.56.tar.gz
[root@node3 MHA]# cd mha4mysql-manager-0.56
[root@node3 mha4mysql-manager-0.56]# perl Makefile.PL
[root@node3 mha4mysql-manager-0.56]# make -j 8
[root@node3 mha4mysql-manager-0.56]# make install
4）manager節點配置MHA
[root@node3 MHA]# mkdir /etc/mha
[root@node3 MHA]# mkdir -p /usr/local/mha/log
[root@node3 MHA]# vi /etc/mha/manager.cnf
[server default]
manager_workdir=/usr/local/mha/log
manager_log=/usr/local/mha/log/manager.log
#ssh免金鑰登入的帳號名
ssh_user=root
#mha管理賬戶
user=manager
password=manager
#mysql複製帳號，用來在主從機之間同步二進位制日誌等
repl_user=replicat
repl_password=backup
#ping間隔，用來檢測master是否正常
ping_interval=1
[server1]
hostname=node1
ip=IP1
#master機宕掉後,優先啟用這臺作為新master
#candidate_master=1
master_binlog_dir=/usr/local/mysql/data
[server2]
hostname=node2
ip=IP2
candidate_master=1
master_binlog_dir=/usr/local/mysql/data
[server3]
hostname=node3
ip=IP3
master_binlog_dir=/usr/local/mysql/data
##在manager節點檢查ssh連線正常與否
[root@node3 MHA]# masterha_check_ssh --conf=/etc/mha/manager.cnf
5）啟動manager
[root@node3 MHA]# nohup masterha_manager --conf=/etc/mha/manager.cnf >/usr/local/mha/log/mha_manager.log 2>&1 &
6)檢查manager狀態
[root@node3 MHA]# masterha_check_status --conf=/etc/mha/manager.cnf
7)關閉manager
[root@node3 MHA]# masterha_stop --conf=/etc/mha/manager.cnf
6、MHA日常管理
1）檢查複製結構
masterha_check_repl --conf=/etc/mha/manager.cnf
2）檢查MHA狀態
masterha_check_status --conf=/etc/mha/manager.cnf
3）啟動MHA Manager
nohup masterha_manager --conf=/etc/mha/manager.cnf > /usr/local/mha/log/mha_manager.log 2>&1 &
4）手動線上切換
masterha_master_switch --conf=/etc/mha/manager.cnf --master_state=alive --new_master_host=node1 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
5）手動故障切換
masterha_master_switch --conf=/etc/mha/manager.cnf --master_state=dead --dead_master_host=node1 --dead_master_port=3306 --new_master_host=node2 --new_master_port=3306 --ignore_last_failover
6）手動分步切換詳細步驟
設定manager自動監控為關閉：masterha_stop --conf=/etc/mha/manager.cnf
設定node1節點masterdead:masterha_master_switch --conf=/etc/mha/manager.cnf --master_state=dead --dead_master_host=node1 設定node2節點為新master:masterha_master_switch --conf=/etc/mha/manager.cnf --master_state=alive --new_master_host=node2
非互動線上切換：masterha_master_switch --conf=/etc/mha/manager.cnf --master_state=alive --new_master_host=node2 --interactive=0
7、安裝錯誤資訊以及解決方案
1）報錯資訊01
[root@node3 MHA]# masterha_check_repl --conf=/etc/mha/manager.cnf
Sun Aug 21 10:19:11 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Aug 21 10:19:11 2016 - [info] Reading application default configuration from /etc/mha/manager.cnf..
Sun Aug 21 10:19:11 2016 - [info] Reading server configuration from /etc/mha/manager.cnf..
Sun Aug 21 10:19:11 2016 - [info] MHA::MasterMonitor version 0.56.
Sun Aug 21 10:19:11 2016 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln301] Got MySQL error when connecting node3(x.x.x.x:3306) :1130:Host 'node3' is not allowed to connect to this MySQL server, but this is not a MySQL crash. Check MySQL server settings.
at /usr/local/share/perl5/MHA/ServerManager.pm line 297
Sun Aug 21 10:19:11 2016 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln301] Got MySQL error when connecting node2(x.x.x.x:3306) :1130:Host 'x.x.x.x' is not allowed to connect to this MySQL server, but this is not a MySQL crash. Check MySQL server settings.
at /usr/local/share/perl5/MHA/ServerManager.pm line 297
Sun Aug 21 10:19:11 2016 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln301] Got MySQL error when connecting node1(x.x.x.x:3306) :1045:Access denied for user 'root'@'xx.xx.xx.xx' (using password: NO), but this is not a MySQL crash. Check MySQL server settings.
at /usr/local/share/perl5/MHA/ServerManager.pm line 297
Sun Aug 21 10:19:11 2016 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln309] Got fatal error, stopping operations
Sun Aug 21 10:19:11 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. at /usr/local/share/perl5/MHA/MasterMonitor.pm line 326
Sun Aug 21 10:19:11 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
Sun Aug 21 10:19:11 2016 - [info] Got exit code 1 (Not master dead).
MySQL Replication Health is NOT OK!
解決方案：
需要在manager節點配置檔案中加入mysql管理賬戶和密碼，需要賦予SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SUPER, REPLICATION CLIENT的許可權
2）報錯資訊02
Tue Aug 23 14:10:51 2016 - [info] Checking MHA is not monitoring or doing failover..
Tue Aug 23 14:10:51 2016 - [error][/usr/local/share/perl5/MHA/MasterRotate.pm, ln142] Getting advisory lock failed on the current master. MHA Monitor runs on the current master. Stop MHA Manager/Monitor and try again.
Tue Aug 23 14:10:51 2016 - [error][/usr/local/share/perl5/MHA/ManagerUtil.pm, ln177] Got ERROR: at /usr/local/bin/masterha_master_switch line 53
解決方案：
做手動切換時，需要先關閉自動切換監控，
[root@node3 ~]# masterha_stop --conf=/etc/mha/manager.cnf
Stopped manager successfully.
[root@node3 ~]# masterha_master_switch --global_conf=/etc/mha/masterha_default.conf --conf=/etc/mha/manager.cnf --master_state=alive --new_master_host=node2 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
8、附錄
1)MHA常用工具命令
--Manager工具
masterha_master_monitor #檢測master是否當機
masterha_master_switch #用於手動Master切換
masterha_manager #啟動MHA監控
masterha_stop #停止MHA監控
masterha_check_status #檢查MHA執行狀態
masterha_check_ssh #檢查各Node之間SSH登入是否正常
masterha_check_repl #檢查mysql複製是否正常
masterha_secondary_check #檢查多路由配置
masterha_conf_host #新增或刪除配置的Server資訊
--Node工具
save_binary_logs #儲存和複製master的二進位制日誌
apply_diff_relay_logs #識別差異的中繼日誌事件並應用於其它Slave
filter_mysqlbinlog #去除不必要的Rollback事件（MHA已不再使用該工具）
purge_relay_logs #清除中繼日誌（不會阻塞SQL執行緒）
注：Node工具通常由Manager的指令碼觸發呼叫，無需手工呼叫
2)附錄指令碼
關閉relay log自動刪除，定期進行清理
purge_relay_logs --user=root --password=xxx --disable_relay_log_purge --port=3306
多重檢查防止網路單點故障
secondary_check_script=/etc/mha/masterha_secondary_check -s node2 -s mysql --user=root --master_host=node1 --master_ip=ip1 --master_port=3306
預防腦裂
shutdown_script =/etc/mha/power_manager
1、下載安裝包
mha 依賴包：
perl-DBD-MySQL
perl-Config-Tiny
perl-Log-Dispatch
perl-Parallel-ForkManager
perl-Config-IniFiles
perl-MailTools
perl-Params-Validate
perl-TimeDate
perl-IO-stringy
cmake-2.8.12.2.tar.gz
DBD-mysql-4.033_02.tar.gz
DBI-1.636.tar.gz
mha rpm包安裝：
mha4mysql-manager-0.56-0.el6.noarch.rpm
mha4mysql-node-0.56-0.el6.noarch.rpm
mha 原始碼安裝：
mha4mysql-node-0.56.tar.gz
mha4mysql-manager-0.56.tar.gz
MySQL安裝包：
percona-server-5.6.32-78.0.tar.gz
2、解壓安裝包
for i in `ls`; do tar -xzvf $i; tar -xvf $i; done
3、安裝依賴包
配置本地yum源：
[root@node3 MHA]# mkdir /media/cdrom
[root@node3 MHA]# mount CentOS-6.4-x86_64-bin-DVD1.iso /media/cdrom/ -o loop
[root@node3 MHA]# rm -rf /etc/yum.repos.d/*.repo
[root@node3 MHA]# vi /etc/yum.repos.d/CentOS6.repo
[Base]
name=CentOS6 ISO Base
baseurl=file:///media/cdrom
enabled=1
gpgcheck=0
依賴包檢查安裝：
yum install -y git scons gcc g++ gcc-c++ openssl check cmake bison libaio libboost-all-dev libasio-dev libaio-dev libncurses5-dev libreadline-dev libpam-dev ncurses-devel
yum -y install gcc gcc-c++ gcc-g77 autoconf automake zlib* fiex* libxml* ncurses-devel libmcrypt* libtool-ltdl-devel* make cmake
rpm -q git scons gcc g++ gcc-c++ openssl check cmake bison libaio libboost-all-dev libasio-dev libaio-dev libncurses5-dev libreadline-dev libpam-dev ncurses-devel
cmake安裝：
[root@node3 MHA]# cd cmake-2.8.12.2
[root@node3 cmake-2.8.12.2]# ./bootstrap
[root@node3 cmake-2.8.12.2]# make -j 8
[root@node3 cmake-2.8.12.2]# make install
Perl工具安裝；
[root@node3 MHA]# rpm -ivh perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm
[root@node3 MHA]# rpm -ivh perl-Config-Tiny-2.12-7.1.el6.noarch.rpm
[root@node3 MHA]# rpm -ivh perl-Params-Validate-0.95-5.9.x86_64.rpm
[root@node3 MHA]# rpm -ivh perl-TimeDate-2.22-1.noarch.rpm
[root@node3 MHA]# rpm -ivh perl-MailTools-2.04-4.el6.noarch.rpm （不是必須）
[root@node3 MHA]# rpm -ivh perl-Convert-BinHex-1.119-4.el6.noarch.rpm --nodeps （不是必須）
[root@node3 MHA]# rpm -ivh perl-List-MoreUtils-0.33-107.1.x86_64.rpm （不是必須）
[root@node3 MHA]# rpm -ivh perl-IO-stringy-2.110-8.el6.noarch.rpm --nodeps （不是必須）
[root@node3 MHA]# rpm -ivh perl-Config-IniFiles-2.72-2.el6.noarch.rpm --nodeps
[root@node3 MHA]# rpm -ivh perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
[root@node3 MHA]# rpm -ivh perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
[root@node3 MHA]# rpm -ivh perl-Log-Dispatch-2.27-1.el6.noarch.rpm --nodeps
DBI安裝：
[root@node3 MHA]# cd DBI-1.636
[root@node3 DBI-1.636]# perl Makefile.PL
[root@node3 DBI-1.636]# make -j 8
[root@node3 DBI-1.636]# make install
DBD安裝：
[root@node3 MHA]# cd DBD-mysql-4.033_02
[root@node3 DBD-mysql-4.033_02]# perl Makefile.PL --mysql_config=/usr/local/mysql/bin/mysql_config
[root@node3 DBD-mysql-4.033_02]# make -j 8
[root@node3 DBD-mysql-4.033_02]# make install
4、MySQL安裝和主從部署
Percona Server 5.6安裝
[root@node3 MHA]# tar -xzvf percona-server-5.6.32-78.0.tar.gz
[root@node3 MHA]# useradd mysql -s /sbin/nologin
[root@node3 MHA]# cd percona-server-5.6.32-78.0
[root@node3 percona-server-5.6.32-78.0]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_DATADIR=/usr/local/mysql/data/ -DMYSQL_USER=mysql -DENABLE_DOWNLOADS=1 -DWITH_WSREP=1 -DWITH_EDITLINE=0
[root@node3 percona-server-5.6.32-78.0]# make -j 4
[root@node3 percona-server-5.6.32-78.0]# make install
初始化資料庫：
[root@node3 MHA]# cd /usr/local/mysql
[root@node3 mysql]# ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/
[root@node3 mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@node3 mysql]# chkconfig --add mysqld
[root@node3 mysql]# chkconfig mysqld on
[root@node3 mysql]# chown -R mysql.mysql /usr/local/mysql
[root@node3 mysql]# vi ~/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
[root@node3 mysql]# source ~/.bash_profile
配置my.cnf
node1節點：
[client]
socket=/usr/local/mysql/mysql.sock
[mysqld]
datadir=/usr/local/mysql/data
user=mysql
log-bin=mysql-binlog
binlog_format=ROW
server-id=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
node2節點
[client]
socket=/usr/local/mysql/mysql.sock
[mysqld]
datadir=/usr/local/mysql/data
user=mysql
log-bin=mysql-binlog
binlog_format=ROW
server-id=2
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
node3節點
[client]
socket=/usr/local/mysql/mysql.sock
[mysqld]
datadir=/usr/local/mysql/data
user=mysql
log-bin=mysql-binlog
binlog_format=ROW
server-id=3
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
啟動Node1資料庫
[root@node1 mysql]# service mysqld start
清理MySQL無用賬戶
mysql> delete from mysql.user where user ='';
mysql> delete from mysql.user where user ='root' and host='::1';
mysql> delete from mysql.user where user ='root' and host='node1';
mysql> delete from mysql.user where user ='root' and host='127.0.0.1';
建立manager管理賬號
mysql> GRANT SUPER,RELOAD,REPLICATION CLIENT,SELECT ON *.* TO manager@'10.x.x.%' IDENTIFIED BY 'manager';
mysql> GRANT CREATE,INSERT,UPDATE,DELETE,DROP ON*.* TO manager@'10.x.x.%';
建立主從複製賬號
mysql> GRANT RELOAD, SUPER, REPLICATION SLAVE ON*.* TO 'replicat'@'10.x.x.%' IDENTIFIED BY 'backup';
mysql> flush privileges;
啟動node2節點資料庫
[root@node2 mysql]# service mysqld start
啟動node3節點資料庫
[root@node3 mysql]# service mysqld start
主從資料庫複製搭建：
node1登陸MYSQL後執行
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-binlog.000005
Position: 973
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
node2登陸MYSQL後執行
mysql> change master to master_host='masterip',master_user='replicat',master_password='backup',master_port=3306,master_log_file='mysql-binlog.000005',master_log_pos= 973;
mysql> start slave;
node3登陸MYSQL後執行
mysql> change master to master_host='masterip',master_user='replicat',master_password='backup',master_port=3306,master_log_file='mysql-binlog.000005',master_log_pos= 973;
mysql> start slave;
5、MHA安裝和部署
node1為master節點，node2,node3為slave節點並且node3為manager節點
1）配置ssh對等性
node1
[root@node1 MHA]# ssh-keygen -t rsa
[root@node1 MHA]# vi ~/.ssh/authorized_keys
將node1,node2,node3的公鑰檔案id_rsa.pub內容放進去
node2
[root@node2 MHA]# ssh-keygen -t rsa
將node1的authorized_keys複製到/root/.ssh/下
node3
[root@node3 MHA]# ssh-keygen -t rsa
將node1的authorized_keys複製到/root/.ssh/下
透過ssh互相登陸驗證，確保ssh登陸不需要輸入密碼。
需要配置/etc/hosts主機名和IP的解析
ip2 node2
ip3 node3
ip1 node1
2）node節點原始碼安裝
node1
[root@node1 MHA]# tar -xzvf mha4mysql-node-0.56.tar.gz
[root@node1 MHA]# cd mha4mysql-node-0.56
[root@node1 mha4mysql-node-0.56]# perl Makefile.PL
[root@node1 mha4mysql-node-0.56]# make -j 8
[root@node1 mha4mysql-node-0.56]# make install
node2
[root@node2 MHA]# tar -xzvf mha4mysql-node-0.56.tar.gz
[root@node2 MHA]# cd mha4mysql-node-0.56
[root@node2 mha4mysql-node-0.56]# perl Makefile.PL
[root@node2 mha4mysql-node-0.56]# make -j 8
[root@node2 mha4mysql-node-0.56]# make install
node3
[root@node3 MHA]# tar -xzvf mha4mysql-node-0.56.tar.gz
[root@node3 MHA]# cd mha4mysql-node-0.56
[root@node3 mha4mysql-node-0.56]# perl Makefile.PL
[root@node3 mha4mysql-node-0.56]# make -j 8
[root@node3 mha4mysql-node-0.56]# make install
3）manager節點原始碼安裝
[root@node3 MHA]# tar -xzvf mha4mysql-manager-0.56.tar.gz
[root@node3 MHA]# cd mha4mysql-manager-0.56
[root@node3 mha4mysql-manager-0.56]# perl Makefile.PL
[root@node3 mha4mysql-manager-0.56]# make -j 8
[root@node3 mha4mysql-manager-0.56]# make install
4）manager節點配置MHA
[root@node3 MHA]# mkdir /etc/mha
[root@node3 MHA]# mkdir -p /usr/local/mha/log
[root@node3 MHA]# vi /etc/mha/manager.cnf
[server default]
manager_workdir=/usr/local/mha/log
manager_log=/usr/local/mha/log/manager.log
#ssh免金鑰登入的帳號名
ssh_user=root
#mha管理賬戶
user=manager
password=manager
#mysql複製帳號，用來在主從機之間同步二進位制日誌等
repl_user=replicat
repl_password=backup
#ping間隔，用來檢測master是否正常
ping_interval=1
[server1]
hostname=node1
ip=IP1
#master機宕掉後,優先啟用這臺作為新master
#candidate_master=1
master_binlog_dir=/usr/local/mysql/data
[server2]
hostname=node2
ip=IP2
candidate_master=1
master_binlog_dir=/usr/local/mysql/data
[server3]
hostname=node3
ip=IP3
master_binlog_dir=/usr/local/mysql/data
##在manager節點檢查ssh連線正常與否
[root@node3 MHA]# masterha_check_ssh --conf=/etc/mha/manager.cnf
5）啟動manager
[root@node3 MHA]# nohup masterha_manager --conf=/etc/mha/manager.cnf >/usr/local/mha/log/mha_manager.log 2>&1 &
6)檢查manager狀態
[root@node3 MHA]# masterha_check_status --conf=/etc/mha/manager.cnf
7)關閉manager
[root@node3 MHA]# masterha_stop --conf=/etc/mha/manager.cnf
6、MHA日常管理
1）檢查複製結構
masterha_check_repl --conf=/etc/mha/manager.cnf
2）檢查MHA狀態
masterha_check_status --conf=/etc/mha/manager.cnf
3）啟動MHA Manager
nohup masterha_manager --conf=/etc/mha/manager.cnf > /usr/local/mha/log/mha_manager.log 2>&1 &
4）手動線上切換
masterha_master_switch --conf=/etc/mha/manager.cnf --master_state=alive --new_master_host=node1 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
5）手動故障切換
masterha_master_switch --conf=/etc/mha/manager.cnf --master_state=dead --dead_master_host=node1 --dead_master_port=3306 --new_master_host=node2 --new_master_port=3306 --ignore_last_failover
6）手動分步切換詳細步驟
設定manager自動監控為關閉：masterha_stop --conf=/etc/mha/manager.cnf
設定node1節點masterdead:masterha_master_switch --conf=/etc/mha/manager.cnf --master_state=dead --dead_master_host=node1 設定node2節點為新master:masterha_master_switch --conf=/etc/mha/manager.cnf --master_state=alive --new_master_host=node2
非互動線上切換：masterha_master_switch --conf=/etc/mha/manager.cnf --master_state=alive --new_master_host=node2 --interactive=0
7、安裝錯誤資訊以及解決方案
1）報錯資訊01
[root@node3 MHA]# masterha_check_repl --conf=/etc/mha/manager.cnf
Sun Aug 21 10:19:11 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Aug 21 10:19:11 2016 - [info] Reading application default configuration from /etc/mha/manager.cnf..
Sun Aug 21 10:19:11 2016 - [info] Reading server configuration from /etc/mha/manager.cnf..
Sun Aug 21 10:19:11 2016 - [info] MHA::MasterMonitor version 0.56.
Sun Aug 21 10:19:11 2016 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln301] Got MySQL error when connecting node3(x.x.x.x:3306) :1130:Host 'node3' is not allowed to connect to this MySQL server, but this is not a MySQL crash. Check MySQL server settings.
at /usr/local/share/perl5/MHA/ServerManager.pm line 297
Sun Aug 21 10:19:11 2016 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln301] Got MySQL error when connecting node2(x.x.x.x:3306) :1130:Host 'x.x.x.x' is not allowed to connect to this MySQL server, but this is not a MySQL crash. Check MySQL server settings.
at /usr/local/share/perl5/MHA/ServerManager.pm line 297
Sun Aug 21 10:19:11 2016 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln301] Got MySQL error when connecting node1(x.x.x.x:3306) :1045:Access denied for user 'root'@'xx.xx.xx.xx' (using password: NO), but this is not a MySQL crash. Check MySQL server settings.
at /usr/local/share/perl5/MHA/ServerManager.pm line 297
Sun Aug 21 10:19:11 2016 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln309] Got fatal error, stopping operations
Sun Aug 21 10:19:11 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. at /usr/local/share/perl5/MHA/MasterMonitor.pm line 326
Sun Aug 21 10:19:11 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
Sun Aug 21 10:19:11 2016 - [info] Got exit code 1 (Not master dead).
MySQL Replication Health is NOT OK!
解決方案：
需要在manager節點配置檔案中加入mysql管理賬戶和密碼，需要賦予SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SUPER, REPLICATION CLIENT的許可權
2）報錯資訊02
Tue Aug 23 14:10:51 2016 - [info] Checking MHA is not monitoring or doing failover..
Tue Aug 23 14:10:51 2016 - [error][/usr/local/share/perl5/MHA/MasterRotate.pm, ln142] Getting advisory lock failed on the current master. MHA Monitor runs on the current master. Stop MHA Manager/Monitor and try again.
Tue Aug 23 14:10:51 2016 - [error][/usr/local/share/perl5/MHA/ManagerUtil.pm, ln177] Got ERROR: at /usr/local/bin/masterha_master_switch line 53
解決方案：
做手動切換時，需要先關閉自動切換監控，
[root@node3 ~]# masterha_stop --conf=/etc/mha/manager.cnf
Stopped manager successfully.
[root@node3 ~]# masterha_master_switch --global_conf=/etc/mha/masterha_default.conf --conf=/etc/mha/manager.cnf --master_state=alive --new_master_host=node2 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
8、附錄
1)MHA常用工具命令
--Manager工具
masterha_master_monitor #檢測master是否當機
masterha_master_switch #用於手動Master切換
masterha_manager #啟動MHA監控
masterha_stop #停止MHA監控
masterha_check_status #檢查MHA執行狀態
masterha_check_ssh #檢查各Node之間SSH登入是否正常
masterha_check_repl #檢查mysql複製是否正常
masterha_secondary_check #檢查多路由配置
masterha_conf_host #新增或刪除配置的Server資訊
--Node工具
save_binary_logs #儲存和複製master的二進位制日誌
apply_diff_relay_logs #識別差異的中繼日誌事件並應用於其它Slave
filter_mysqlbinlog #去除不必要的Rollback事件（MHA已不再使用該工具）
purge_relay_logs #清除中繼日誌（不會阻塞SQL執行緒）
注：Node工具通常由Manager的指令碼觸發呼叫，無需手工呼叫
2)附錄指令碼
關閉relay log自動刪除，定期進行清理
purge_relay_logs --user=root --password=xxx --disable_relay_log_purge --port=3306
多重檢查防止網路單點故障
secondary_check_script=/etc/mha/masterha_secondary_check -s node2 -s mysql --user=root --master_host=node1 --master_ip=ip1 --master_port=3306
預防腦裂
shutdown_script =/etc/mha/power_manager
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27067062/viewspace-2123856/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL高可用架構之MHA 原理與實踐MySql架構
- MySQL 實現高可用架構之 MHAMySql架構
- mysql高可用架構MHA搭建MySql架構
- mysql MHA 高可用架構部署MySql架構
- MySQL高可用架構之PXC實踐MySql架構
- 構建MHA實現MySQL高可用叢集架構MySql架構
- MySQL高可用架構之MaxScale實踐MySql架構
- MySQL資料庫實現高可用架構之MHA的實戰MySql資料庫架構
- MySQL高可用架構-MMM、MHA、MGR、PXCMySql架構
- MySQL 高可用架構 - MHA環境部署記錄MySql架構
- MySQL高可用架構-MHA環境部署記錄MySql架構
- MHA高可用架構的實現方式架構
- mysql高可用架構MHA搭建（centos7+mysql5.7.28）MySql架構CentOS
- 【Mysql】高可用架構之-Lvs+keepalive+Altas+MHAMySql架構
- 【MHA】mysql高可用之MHAMySql
- MySQL 高可用架構之 MMM 架構MySql架構
- MySQL高可用架構案例篇：UCloud最佳實踐MySql架構Cloud
- Mysql 5.7 MHA 高可用MySql
- MySQL MHA高可用方案MySql
- Redis 高可用架構最佳實踐Redis架構
- MySQL叢集架構：MHA+MySQL-PROXY+LVS實現MySQL叢集架構高可用/高效能MySql架構
- 部署MHA+keepalived+ProxySQL高可用架構SQL架構
- MHA+MySQL主從配置實現MySQL高可用MySql
- 基於 MHA 高可用的 MySQLMySql
- MySQL高可用方案MHA介紹MySql
- MySQL高可用架構：mysql+keepalived實現MySql架構
- MySQL高可用架構之Keepalived+主從架構部署MySql架構
- MySQL高可用架構對比MySql架構
- Keepalived 架構高可用 Mysql架構MySql
- 構建高併發高可用的電商平臺架構實踐架構
- 【DB寶42】MySQL高可用架構MHA+ProxySQL實現讀寫分離和負載均衡MySql架構負載
- 同程旅行基於 RocketMQ 高可用架構實踐MQ架構
- 來自 Google 的高可用架構理念與實踐Go架構
- MySQL高可用架構設計分析MySql架構
- 《MySQL效能優化和高可用架構實踐》簡介與推薦序MySql優化架構
- Mysql 高可用(MHA)-讀寫分離(Atlas)MySql
- MHA高可用架構工作原理？主庫當機處理過程架構
- 淺談MySQL叢集高可用架構MySql架構