MySQL—MHA高可用

操作系统：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_safe\nexport 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集群

#安装node节点，三台都需要安装
yum -y install perl-DBD-MySQL
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
#安装管理节点，只需在mysql从128上执行
先安装相关依赖包 yum install -y perl-Log-Dispatch perl-Config-Tiny perl-Parallel-ForkManager perl-Time-HiRes perl-DBD-MySQL
否则出现以下报错：
rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

3、各节点配置ssh免秘钥登录

#128上执行 
ssh-keygen -t rsa
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

1
2
3

#129上执行
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

1
2
3

#130上执行
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; 记录
主从复制命令：
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 status\G;

6、以下操作在管理节点128上执行利用mha工具检测ssh
masterha_check_ssh --global_conf=/usr/local/mha/conf/manager.cnf --conf=/usr/local/mha/conf/app1.cnf

使用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

解决办法：每台mysql执行 ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
2、 Testing mysql connection and privileges…sh: mysql: command not found

解决办法每台mysql执行 ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
检查成功

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

7、启动manager命令
masterha_manager --global_conf=/usr/local/mha/conf/manager.cnf --conf=/usr/local/mha/conf/app1.cnf &

停止manager命令
masterha_stop --global_conf=/usr/local/mha/conf/manager.cnf --conf=/usr/local/mha/conf/app1.cnf

查看启动日志
tail -f /usr/local/mha/logs/mha.log

使用masterha_check_status检测下mha状态
masterha_check_status --global_conf=/usr/local/mha/conf/manager.cnf --conf=/usr/local/mha/conf/app1.cnf

二、测试
1、主mysql129死机自动切换测试在主mysql上停止mysql服务
查看129vip ，明显vip已经切换了
打印128mha日志，观察切换效果
tail -f /usr/local/mha/logs/mha.log

查看128vip是否漂移过来成功
查看130mysql同步信息，show slave status\G;明显主已切换到128

由以上结果，切换成功
2、原主129恢复后，手动变为从查看主128master状态
建立与新主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 status\G;

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

重新启动manager
masterha_manager --global_conf=/usr/local/mha/conf/manager2.cnf --conf=/usr/local/mha/conf/app2.cnf &
查看启动日志，启动成功
使用masterha_check_status检测下mha状态
masterha_check_status --global_conf=/usr/local/mha/conf/manager2.cnf --conf=/usr/local/mha/conf/app2.cnf

至此。Mysql-mha搭建完成