搭建MySQL MHA高可用
本文内容参考:http://www.ttlsa.com/mysql/step-one-by-one-deploy-mysql-mha-cluster/
MySQL MHA 高可用集群
环境:
Linux: centos 6.6
MySQL: 5.5.49
MHA: mha4mysql-manager-0.56-0.el6.noarch.rpm(管理端) 以及 mha4mysql-node-0.56-0.el6.noarch.rpm(节点) 192.168.178.128
MySQL主从环境:
Master: 192.168.178.130:3306
Slave: 192.168.178.130:3307
192.168.178.130:3308
192.168.178.130:3309
一、配置MySQL多实例:
1)编译安装MySQL
tar xf mysql-5.5.32.tar.gz cd mysql-5.5.32 cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 -DMYSQL_DATADIR=/application/mysql-5.5.32/data -DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii -DENABLED_LOCAL_INFILE=ON -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITHOUT_PARTITION_STORAGE_ENGINE=1 -DWITH_FAST_MUTEXES=1 -DWITH_ZLIB=bundled -DENABLED_LOCAL_INFILE=1 -DWITH_READLINE=1 -DWITH_EMBEDDED_SERVER=1 -DWITH_DEBUG=0 make make install
2)配置多实例
* 上传配置文件
mkdir /data/{3306,3307}/data -p unzip data.zip mkdir /data/{3306,3307}/data -p ln -s /application/mysql-5.5.32/ /application/mysq find /data -type f -name "mysql"|xargs chmod +x chown -R mysql.mysql /data
* 初始化并启动数据库
cd /application/mysql ./scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3306/data --user=mysql ./scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3307/data --user=mysql /data/3306/mysql start lsof -i:3306 mysql -S /data/3306/mysql.sock /data/3307/mysql start lsof -i:3307 mysql -S /data/3307/mysql.sock
3)配置MySQL主从
*开启binlog,确保主从server-id不同
[root@mysql 3306]# grep log-bin my.cnf log-bin = /data/3306/mysql-bin [root@mysql 3306]# grep server-id my.cnf server-id = 1
*登录主库授权从库登录用户
mysql> grant replication slave on *.* to 'rep'@'%' identified by 'oldboy123'; mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
* 从库导出数据
mysql> flush table with read lock; 锁表 Query OK, 0 rows affected (0.00 sec) mysql> show master status; +------------------+----------+-------- | mysql-bin.000001 | 476 | +------------------+----------+-------- [root@mysql ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -A -B --events >/opt/mysql_bak.sql mysql> unlock tables; 解锁
* 将数据恢复到从库
[root@mysql opt]# mysql -uroot -poldboy456 -S /data/3307/mysql.sock < mysql_bak.sql [root@mysql opt]# mysql -uroot -poldboy456 -S /data/3307/mysql.sock -e "show databases;"
* 配置用户及位置信息(slave上执行)
CHANGE MASTER TO MASTER_HOST='192.168.160.138', MASTER_PORT=3307, MASTER_USER='slave', MASTER_PASSWORD='123', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
* 开启同步开关
mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0
下面这一步一定要做,否则在使用masterha_check_repl检查主从复制情况的时候会报错
# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog # ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
首选需要确保MySQL主从配置成功
二、配置MHA
需要用到的两个软件:
mha4mysql-manager-0.56-0.el6.noarch.rpm (MHA管理端)
mha4mysql-node-0.56-0.el6.noarch.rpm (node)
MHA管理服务器上安装:
mha4mysql-manager-0.56-0.el6.noarch.rpm (MHA管理端)
mha4mysql-node-0.56-0.el6.noarch.rpm (node)
管理端
# yum install perl-DBD-MySQL # yum install perl-Config-Tiny # yum install perl-Log-Dispatch # yum install perl-Parallel-ForkManager # yum install -y rrdtool perl-rrdtool rrdtool-devel perl-Params-Validate # yum install -y perl-Time-HiRes
rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm --nodeps --force rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm --nodeps --force
在node节点上
yum install perl-DBD-MySQL
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm --nodeps --force
在所有MySQL的实例上授权MHA管理账号
mysql> grant all privileges on *.* to mha@'%' identified by '123456';
在管理端创建MHA的工作目录和日志目录
mkdir /etc/masterha/ -p /var/log/masterha/app1/ -p
编辑配置文件/etc/masterha/app1.cnf
[server default] manager_workdir=/var/log/masterha/app1 manager_log=/var/log/masterha/app1/manager.log user=mha #MHA的授权管理用户 password=123456 ssh_user=root repl_user=slave #MySQL主从实例授权的用户 repl_password=123 ping_interval=1 shutdown_script="" master_ip_online_change_script="" report_script="" [server1] hostname=192.168.178.130 port=3306 candidate_master=1 #可以成为master master_binlog_dir="/data/3306" #binlog目录 [server2] hostname=192.168.178.130 port=3307 candidate_master=1 #可以成为master master_binlog_dir="/data/3307" [server3] hostname=192.168.178.130 port=3308 master_binlog_dir=/data/3308 no_master=1 #不能成为master [server4] hostname=192.168.178.130 port=3309 master_binlog_dir=/data/3309 no_master=1 #不能成为master
检验MHA各种配置信息:
masterha_check_ssh --conf=/etc/masterha/app1.cnf #检查SSH互认是否成功 masterha_check_repl --conf=/etc/masterha/app1.cnf #检查MySQL主从复制是否成功 masterha_manager --conf=/etc/masterha/app1.cnf &
观察日志:
Sat Aug 27 10:33:04 2016 - [info] db2 (current master) +--db1 +--db3 +--db4 Sat Aug 27 10:33:04 2016 - [warning] master_ip_failover_script is not defined. Sat Aug 27 10:33:04 2016 - [warning] shutdown_script is not defined. Sat Aug 27 10:33:04 2016 - [info] Set master ping interval 1 seconds. Sat Aug 27 10:33:04 2016 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes. Sat Aug 27 10:33:04 2016 - [info] Starting ping health check on (192.168.178.130:3306).. Sat Aug 27 10:33:04 2016 - [info] Ping succeeded, sleeping until it doesn't respond..
故障测试:
stop 3306实例,看日志主库是否切换到3307实例上
Started automated(non-interactive) failover. The latest slave 192.168.178.130(192.168.178.130:3307) has all relay logs for recovery. Selected 192.168.178.130(192.168.178.130:3307) as a new master. 192.168.178.130(192.168.178.130:3307): OK: Applying all logs succeeded. 192.168.178.130(192.168.178.130:3309): This host has the latest relay log events. 192.168.178.130(192.168.178.130:3308): This host has the latest relay log events. Generating relay diff files from the latest slave succeeded. 192.168.178.130(192.168.178.130:3309): OK: Applying all logs succeeded. Slave started, replicating from 192.168.178.130(192.168.178.1 30:3307) 192.168.178.130(192.168.178.130:3308): OK: Applying all logs succeeded. Slave started, replicating from 192.168.178.130(192.168.178.1 30:3307) 192.168.178.130(192.168.178.130:3307): Resetting slave info succeeded. Master failover to 192.168.178.130(192.168.178.130:3307) completed successfully.
说明切换成功!!!
此篇文档有点乱,在搭建的过程中要根据自己的配置信息及日志信息,一步一步的解决问题直至一切OK!!!