Eternally Eternally

MySQL—MHA高可用

in 技术向 read (8324) 文章转载请注明来源!

操作系统:CentOS 6.6X 64位
架构规划:
Mysql服务器:
192.168.157.128 从 Node数据节点 Manager管理节点
192.168.157.129 主 Node数据节点
192.168.157.130 从 Node数据节点

以下操作分别在128、129、130服务器上操作

1、安装Mariadb

安装相关依赖包

yum -y install cmake gcc gcc-c++ make autoconf libtool-ltdl-devel gd-devel freetype-devel libxml2-devel libjpeg-devel libpng-devel openssl-devel curl-devel bison patch unzip libmcrypt-devel libmhash-devel ncurses-devel sudo bzip2 flex libaio-devel cmake ncurses ncurses

安装libunwind

tar -zxvf libunwind-1.1.tar.gz
cd libunwind-1.1
./configure
make && make install

安装gperftools

tar zxvf gperftools-2.1.tar.gz
cd gperftools-2.1
./configure --enable-frame-pointers
make && make install
echo '/usr/local/lib' > /etc/ld.so.conf.d/usr_local_lib.conf
/sbin/ldconfig
mkdir /tmp/tcmalloc
chmod 777 /tmp/tcmalloc -R

创建数据与日志目录

mkdir -p /data/mysqldata
mkdir -p /data/mysqlbinlog
mkdir -p /data/mysqllog

创建mysql运行用户

groupadd mysql
useradd -g mysql mysql -s /sbin/nologin -M
tar xf mariadb-10.1.16.tar.gz
cd mariadb-10.1.16
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql/ \
-DMYSQL_UNIX_ADDR=/data/mysqldata/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STPRAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DWITH_INNODB_MEMCACHED=1 \
-DWITH_DEBUG=OFF \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DENABLED_PROFILING=ON \
-DMYSQL_MAINTAINER_MODE=OFF \
-DMYSQL_DATADIR=/data/mysqldata \
-DMYSQL_TCP_PORT=3306 \
-DMYSQL_UNIX_ADDR=/data/mysqldata/mysql.sock
make
make install
sed -i 's@executing mysqld_safe@executing mysqld_safenexport LD_PRELOAD=/usr/local/lib/libtcmalloc.so@' /usr/local/mysql/bin/mysqld_safe
chown -R mysql:mysql /data/*
echo "MANPATH /usr/local/mysql/man" >> /etc/man.config
echo "/usr/local/mysql/lib" >> /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysqldata
cp support-files/mysql.server /etc/rc.d/init.d/mysqld
chmod 700 /etc/rc.d/init.d/mysqld

添加开机启动

chkconfig --add mysqld
chkconfig --level 345 mysqld on

添加环境变量

echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile

环境变量配置生效

source /etc/profile

写入配置文件

cp /etc/my.cnf /etc/my.cnfbak
vim /etc/my.cnf

129为主,128,130分别为从,配置文件需要修改不同的server-id,innodb_buffer_pool_size修改为系统内存60%-70%左右
至此,mysql搭建完成。

二、搭建MHA集群
1、安装node节点,三台都需要安装
yum -y install perl-DBD-MySQL
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
2、安装管理节点,只需在mysql从128上执行
先安装相关依赖包 yum install -y perl-Log-Dispatch perl-Config-Tiny perl-Parallel-ForkManager perl-Time-HiRes perl-DBD-MySQL
否则出现以下报错:
1.png
rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

3、各节点配置ssh免秘钥登录
128上执行 ssh-keygen -t rsa
2.png
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.157.129
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.157.130
注意:因为管理节点在从128上,所以ssh免登陆都要在128上执行
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.157.128
129上执行
3.png
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.157.128
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.157.130
130上执行
4.png
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.157.128
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.157.129
4、创建manager管理节点配置文件及切换脚本
mkdir -p /usr/local/mha/conf
mkdir -p /usr/local/mha/logs
mkdir -p /usr/local/mha/app1
mkdir -p /usr/local/mha/scripts

vim /usr/local/mha/conf/manager.cnf

vim /usr/local/mha/conf/app1.conf

切换perl脚本

vim /usr/local/mha/scripts/master_ip_failover

chmod +x /usr/local/mha/scripts/master_ip_failover
创建Mysql远程登录允许账号,需要STOP SLAVE, CHANGE MASTER, RESET SLAVE等相关权限,该账户要添加到mha配置文件中,主从切换时用到, 在mysql数据库各节点(128.、129、130)执行:
grant all privileges on . to mha@'192.168.157.%' identified by 'mysql_2015';
flush privileges;
创建用于建立数据复制关系的账号,在mysql数据库各节点(128、129、130)执行:
grant all privileges on . to rep@'192.168.157.%' identified by 'rep';
flush privileges;
5、登录主备,即管理节点128,和从节点130上设置slave同步主129
登录主129数据库show master status/G; 记录
5.png
主从复制命令:
change master to master_host='192.168.157.129', MASTER_PORT=3306, master_user='rep', master_password='rep', master_log_file='mysql-bin.000001', master_log_pos=986;
start slave;
show slave statusG;
6.png
6、以下操作在管理节点128上执行
利用mha工具检测ssh
masterha_check_ssh --global_conf=/usr/local/mha/conf/manager.cnf --conf=/usr/local/mha/conf/app1.cnf
7.png
使用mha工具check检查repl复制环境
masterha_check_repl --global_conf=/usr/local/mha/conf/manager.cnf --conf=/usr/local/mha/conf/app1.cnf
检查过程报错以及解决办法
1、Can't exec "mysqlbinlog": No such file or directory at /usr/share/perl5/vendor_perl/MHA/BinlogManager.pm line 106.
mysqlbinlog version command failed with rc 1:0, please verify PATH, LD_LIBRARY_PATH, and client options
8.png
解决办法:每台mysql执行 ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
2、 Testing mysql connection and privileges..sh: mysql: command not found
9.png
解决办法 每台mysql执行 ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
检查成功
10.png
7、主mysql130 添加虚拟ip
/sbin/ifconfig eth0:1 192.168.157.140;/sbin/arping -I eth0 -c 3 -s 192.168.157.140 192.168.157.2 >/dev/null 2>&1
11.png

7、启动manager命令
masterha_manager --global_conf=/usr/local/mha/conf/manager.cnf --conf=/usr/local/mha/conf/app1.cnf &
12.png
停止manager命令
masterha_stop --global_conf=/usr/local/mha/conf/manager.cnf --conf=/usr/local/mha/conf/app1.cnf
13.png

查看启动日志
tail -f /usr/local/mha/logs/mha.log
14.png
使用masterha_check_status检测下mha状态
masterha_check_status --global_conf=/usr/local/mha/conf/manager.cnf --conf=/usr/local/mha/conf/app1.cnf
15.png
二、测试
1、主mysql129死机自动切换测试
在主mysql上停止mysql服务
16.png
查看129vip ,明显vip已经切换了
17.png
打印128mha日志,观察切换效果
tail -f /usr/local/mha/logs/mha.log
18.png
查看128vip是否漂移过来成功
19.png
查看130mysql同步信息,show slave statusG;明显主已切换到128
20.png
由以上结果,切换成功
2、原主129恢复后,手动变为从
查看主128master状态
21.png
建立与新主128数据复制
change master to master_host='192.168.157.128', MASTER_PORT=3306, master_user='rep', master_password='rep', master_log_file='mysql-bin.000018', master_log_pos=1486;
start slave;
show slave statusG;
22.png

3、一旦发生切换管理进程(Manager)将会退出,无法进行再次测试,需将故障数据库解决掉之后,重新change加入到MHA环境中来,因为主已经切换到新的机器128上,所以必须修改新的mha配置文件
cp /usr/local/mha/conf/app1.cnf /usr/local/mha/conf/app2.conf
app2.cnf只需要修改三个参数
app2.cnf.txt
cp /usr/local/mha/conf/manager.cnf /usr/local/mha/conf/manager2.conf
manager2.cnf.txt
重新使用mha工具check检查repl复制环境
masterha_check_repl --global_conf=/usr/local/mha/conf/manager2.cnf --conf=/usr/local/mha/conf/app2.cnf
23.png
重新启动manager
masterha_manager --global_conf=/usr/local/mha/conf/manager2.cnf --conf=/usr/local/mha/conf/app2.cnf &
查看启动日志,启动成功
24.png
使用masterha_check_status检测下mha状态
masterha_check_status --global_conf=/usr/local/mha/conf/manager2.cnf --conf=/usr/local/mha/conf/app2.cnf
25.png
至此。Mysql-mha搭建完成

jrotty WeChat Pay

微信打赏

jrotty Alipay

支付宝打赏

文章二维码

扫描二维码,在手机上阅读!

本文基于《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》许可协议授权
文章链接:https://www.97hjh.cn/%E6%8A%80%E6%9C%AF%E5%90%91/20180621/MySQL-MHA%E9%AB%98%E5%8F%AF%E7%94%A8.html (转载时请注明本文出处及文章链接)

MySQLMHALinux运维
发表新评论
博客已坚挺运行
© 2019 本博客模板由 Eternally 基于YoDu二次开发 By 粤ICP备17076045号
前篇 后篇
雷姆
拉姆