搭建MySQL MHA高可用

时间：2024-05-12 10:04:49

本文内容参考：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='',

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 '';

在管理端创建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！！！

相关文章