溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

MySQL高可用架構(gòu)之MHA實踐

發(fā)布時間：2020-08-08 08:41:25 來源：ITPUB博客 閱讀：218 作者：yepkeepmoving 欄目：MySQL數(shù)據(jù)庫
本文主要是描述MHA高可用快速部署步驟以及簡單使用命令
1、下載安裝包
    mha 依賴包：                     http://rpm.pbone.net/index.php3
    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                   http://www.linuxfromscratch.org/blfs/view/7.5/general/cmake.html
    DBD-mysql-4.033_02.tar.gz      http://www.filewatcher.com/d/FreeBSD/distfiles/Other/DBD-mysql-4.018.tar.gz.133427.html
    DBI-1.636.tar.gz                       http://www.cpan.org/modules/by-module/DBI/

    mha rpm包安裝：                         https://code.google.com/p/mysql-master-ha/
    mha4mysql-manager-0.56-0.el6.noarch.rpm
    mha4mysql-node-0.56-0.el6.noarch.rpm
    mha 源碼安裝：                     https://code.google.com/p/mysql-master-ha/
    mha4mysql-node-0.56.tar.gz
    mha4mysql-manager-0.56.tar.gz
    MySQL安裝包：
    percona-server-5.6.32-78.0.tar.gz       https://www.percona.com/downloads/Percona-Server-5.6/LATEST/
    
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
    初始化數(shù)據(jù)庫：
    [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節(jié)點(diǎn)：
[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節(jié)點(diǎn)
[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節(jié)點(diǎn)
[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數(shù)據(jù)庫
    [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';
    創(chuàng)建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.%';
    創(chuàng)建主從復(fù)制賬號
    mysql> GRANT RELOAD, SUPER, REPLICATION SLAVE ON*.* TO 'replicat'@'10.x.x.%' IDENTIFIED BY 'backup';
    mysql> flush privileges;
    啟動node2節(jié)點(diǎn)數(shù)據(jù)庫
    [root@node2 mysql]# service mysqld start
    啟動node3節(jié)點(diǎn)數(shù)據(jù)庫
    [root@node3 mysql]# service mysqld start
    主從數(shù)據(jù)庫復(fù)制搭建：
    node1登陸MYSQL后執(zhí)行
    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后執(zhí)行
    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后執(zhí)行
    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節(jié)點(diǎn)，node2,node3為slave節(jié)點(diǎn)并且node3為manager節(jié)點(diǎn)
    1）配置ssh對等性
    node1
    [root@node1 MHA]# ssh-keygen  -t rsa
    [root@node1 MHA]# vi ~/.ssh/authorized_keys
        將node1,node2,node3的公鑰文件id_rsa.pub內(nèi)容放進(jìn)去
    node2
    [root@node2 MHA]# ssh-keygen  -t rsa
        將node1的authorized_keys復(fù)制到/root/.ssh/下
    node3
    [root@node3 MHA]# ssh-keygen  -t rsa
        將node1的authorized_keys復(fù)制到/root/.ssh/下
    通過ssh互相登陸驗證，確保ssh登陸不需要輸入密碼。
    需要配置/etc/hosts主機(jī)名和IP的解析
    ip2 node2
    ip3 node3
    ip1 node1
    2）node節(jié)點(diǎn)源碼安裝
    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節(jié)點(diǎn)源碼安裝
    [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節(jié)點(diǎn)配置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復(fù)制帳號，用來在主從機(jī)之間同步二進(jìn)制日志等
repl_user=replicat
repl_password=backup
#ping間隔，用來檢測master是否正常
ping_interval=1   

[server1]
hostname=node1
ip=IP1
#master機(jī)宕掉后,優(yōu)先啟用這臺作為新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節(jié)點(diǎn)檢查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狀態(tài)
    [root@node3 MHA]# masterha_check_status --conf=/etc/mha/manager.cnf
    7)關(guān)閉manager
    [root@node3 MHA]# masterha_stop  --conf=/etc/mha/manager.cnf  

6、MHA日常管理

    1）檢查復(fù)制結(jié)構(gòu)
    masterha_check_repl  --conf=/etc/mha/manager.cnf
    2）檢查MHA狀態(tài)
    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）手動分步切換詳細(xì)步驟
    設(shè)置manager自動監(jiān)控為關(guān)閉：masterha_stop  --conf=/etc/mha/manager.cnf
    設(shè)置node1節(jié)點(diǎn)masterdead:masterha_master_switch --conf=/etc/mha/manager.cnf    --master_state=dead --dead_master_host=node1    設(shè)置node2節(jié)點(diǎn)為新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節(jié)點(diǎn)配置文件中加入mysql管理賬戶和密碼，需要賦予SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SUPER, REPLICATION CLIENT的權(quán)限
    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
    解決方案：
    做手動切換時，需要先關(guān)閉自動切換監(jiān)控，
    [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是否宕機(jī)
    masterha_master_switch #用于手動Master切換
    masterha_manager #啟動MHA監(jiān)控
    masterha_stop #停止MHA監(jiān)控
    masterha_check_status #檢查MHA運(yùn)行狀態(tài)
    masterha_check_ssh #檢查各Node之間SSH登錄是否正常
    masterha_check_repl #檢查mysql復(fù)制是否正常
    masterha_secondary_check #檢查多路由配置
    masterha_conf_host #添加或刪除配置的Server信息
    --Node工具
    save_binary_logs #保存和復(fù)制master的二進(jìn)制日志
    apply_diff_relay_logs #識別差異的中繼日志事件并應(yīng)用于其它Slave
    filter_mysqlbinlog #去除不必要的Rollback事件（MHA已不再使用該工具）
    purge_relay_logs #清除中繼日志（不會阻塞SQL線程）
    注：Node工具通常由Manager的腳本觸發(fā)調(diào)用，無需手工調(diào)用
    2)附錄腳本
    關(guān)閉relay log自動刪除，定期進(jìn)行清理
    purge_relay_logs --user=root --password=xxx --disable_relay_log_purge  --port=3306
    多重檢查防止網(wǎng)絡(luò)單點(diǎn)故障
    secondary_check_script=/etc/mha/masterha_secondary_check -s node2 -s mysql --user=root --master_host=node1 --master_ip=ip1 --master_port=3306
    預(yù)防腦裂
    shutdown_script =/etc/mha/power_manager


向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手