MHA of MySQL high-availability Program

Environmental planning:

Node description host name IP address

Management node Tong3 192.168.1.249

Master Node Tong2 192.168.1.248

Master Node Tong1 192.168.1.247

1. Network and Host name configuration

Set each host's IP address and/etc/hosts file to parse each other

[Email protected] ~]# cat/etc/hosts
192.168.1.247 tong1
192.168.1.248 tong2
192.168.1.249 Tong3
[[email protected] ~]# Ping TONG1-C1--the network must ping through
PING tong1 (192.168.1.247) bytes of data.
bytes from localhost (192.168.1.247): icmp_seq=1 ttl=64 time=0.021 ms

---tong1 ping statistics---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
RTT Min/avg/max/mdev = 0.021/0.021/0.021/0.000 ms
[[email protected] ~]# Ping tong2-c1
PING tong2 (192.168.1.248) bytes of data.
Bytes from tong2 (192.168.1.248): icmp_seq=1 ttl=64 time=0.109 ms

---tong2 ping statistics---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
RTT Min/avg/max/mdev = 0.109/0.109/0.109/0.000 ms
[[email protected] ~]# Ping tong3-c1
PING tong3 (192.168.1.249) bytes of data.
Bytes from Tong3 (192.168.1.249): icmp_seq=1 ttl=64 time=0.124 ms

---tong3 ping statistics---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
RTT Min/avg/max/mdev = 0.124/0.124/0.124/0.000 ms
[Email protected] ~]#

2. Install MHA management Software MHA Manager

TONG3 Management node:

[[email protected] ~]# Yum install perl-dbd-mysql CPAN--Installing Perl tools

[Email protected] ~]# tar xvf mha4mysql-manager-0.53.tar.gz-c/usr/local/

[Email protected] ~]# cd/usr/local/mha4mysql-manager-0.53/

[Email protected] mha4mysql-manager-0.53]# Perl makefile.pl

[[email protected] mha4mysql-manager-0.53]# echo $?
0
[[email protected] mha4mysql-manager-0.53]# make && make install

[[email protected] mha4mysql-manager-0.53]# echo $?
0
[Email protected] mha4mysql-manager-0.53]#

3. Install MHA node on each data node

[Email protected] ~]# tar xvf mha4mysql-node-0.53.tar.gz
[Email protected] ~]# CD mha4mysql-node-0.53
[email protected] mha4mysql-node-0.53]# Yum install perl-dbd-mysql cpan-y

[Email protected] mha4mysql-node-0.53]# Perl makefile.pl
[[email protected] mha4mysql-node-0.53]# make && make install

[[email protected] mha4mysql-node-0.53]# echo $?
0
[Email protected] mha4mysql-node-0.53]#

4. Each node SSH trust each other

[Email protected] ~]# ssh-keygen-t DSA

[[Email protected] ~]# CD. SSH

[email protected]. ssh]# Cat id_dsa.pub > Authorized_keys

[email protected]. ssh]# SCP 192.168.1.247:/root/.ssh/id_dsa.pub 247

[email protected]. ssh]# SCP 192.168.1.248:/root/.ssh/id_dsa.pub 248

[email protected]. ssh]# Cat 248 247 >> Authorized_keys
[email protected]. ssh]# SCP Authorized_keys 192.168.1.248:/root/.ssh/
Authorized_keys 100% 1800 1.8KB/S 00:00
[email protected]. ssh]# SCP Authorized_keys 192.168.1.247:/root/.ssh/
[email protected] ' s password:
Authorized_keys 100% 1800 1.8KB/S 00:00
[email protected]. ssh]# SSH tong1 Date
Tue APR 12:57:02 CST 2015
[email protected]. ssh]# SSH tong2 Date
Tue APR 12:59:57 CST 2015
[email protected]. ssh]# SSH tong3 Date
Tue APR 12:57:25 CST 2015
[email protected]. ssh]#

5. Edit the configuration file in the management node

[email protected]. ssh]# MKDIR/ETC/MYSQLMHA
[email protected]. ssh]# cd/etc/mysqlmha/
[Email protected] mysqlmha]# cp-a/usr/local/mha4mysql-manager-0.53/samples/*.
[Email protected] mysqlmha]# vim conf/app1.cnf
[Server default]
Manager_workdir=/var/log/masterha/app1
Manager_log=/var/log/masterha/app1/manager.log
USER=ROOT1--Remote login user
Password=system
Ssh_user=root
Repl_user=repl_user--Copy user
repl_password=system!#%246
Ping_interval=1--Heartbeat detection

[Server1]
hostname=192.168.1.249
Master_binlog_dir= "/usr/local/mysql-5.6.23/data/"
# candidate_master=1
No_master=1--Cannot switch to master database

[Server2]
hostname=192.168.1.248
Master_binlog_dir= "/usr/local/mysql-5.6.23/data/"-Binary log file storage
Candidate_master=1--can be switched to the master database

[Server4]
hostname=192.168.1.247
Master_binlog_dir= "/usr/local/mysql-5.6.23/data/"
Candidate_master=1-can be switched to master database

[Email protected] ~]# masterha_check_ssh--conf=/etc/mysqlmha/conf/app1.cnf
Tue APR-15:39:21-[WARNING] Global configuration file/etc/masterha_default.cnf not found. Skipping.
Tue APR 15:39:21-[INFO] Reading application default configurations from/etc/mysqlmha/conf/app1.cnf.
Tue APR 15:39:21-[INFO] Reading server configurations from/etc/mysqlmha/conf/app1.cnf.
Tue APR 15:39:21-[INFO] Starting SSH connection tests.
Tue APR 15:39:22-[debug]
Tue APR 15:39:21-[debug] connecting via SSH from [email protected] (192.168.1.249:22) to [email protected] (192.1 68.1.248:22)..
Tue APR 15:39:22-[debug] OK.
Tue APR 15:39:22-[debug] connecting via SSH from [email protected] (192.168.1.249:22) to [email protected] (192.1 68.1.247:22)..
Tue APR 15:39:22-[debug] OK.
Tue APR 15:39:23-[debug]
Tue APR 15:39:22-[debug] connecting via SSH from [email protected] (192.168.1.248:22) to [email protected] (192.1 68.1.249:22)..
Tue APR 15:39:22-[debug] OK.
Tue APR 15:39:22-[debug] connecting via SSH from [email protected] (192.168.1.248:22) to [email protected] (192.1 68.1.247:22)..
Tue APR 15:39:23-[debug] OK.
Tue APR 15:39:24-[debug]
Tue APR 15:39:22-[debug] connecting via SSH from [email protected] (192.168.1.247:22) to [email protected] (192.1 68.1.249:22)..
Tue APR 15:39:23-[debug] OK.
Tue APR 15:39:23-[debug] connecting via SSH from [email protected] (192.168.1.247:22) to [email protected] (192.1 68.1.248:22)..
Tue APR 15:39:23-[debug] OK.
Tue APR 15:39:24-[INFO] All SSH connection tests passed successfully.
[Email protected] ~]#

6. Modify the database configuration file and create the user

Create the same user on all nodes:

Mysql> Grant all privileges on * * to [e-mail protected] ' 192.168.1.% ' identified by ' system ';

Mysql> Grant replication Slave,replication Client on * * to [e-mail protected] ' 192.168.1.% ' identified by ' system!#%246 ' ;

TONG1 node:

[Email protected] ~]# VIM/ETC/MY.CNF

Basedir =/usr/local/mysql-5.6.23
DataDir =/usr/local/mysql-5.6.23/data
Port = 3306
server_id = 20
Socket =/tmp/mysql.sock

Replicate-do-db=tong
Replicate-ignore-db=mysql

Log-bin=mysql-bin
Log-bin-index=mysql-bin-index

Auto_increment_offset=1
auto_increment_increment=2
Relay_log_purge=0
Read-only=1

[Email protected] ~]#

TONG2 node:

[Email protected] ~]# VIM/ETC/MY.CNF

Basedir =/usr/local/mysql-5.6.23
DataDir =/usr/local/mysql-5.6.23/data
Port = 3306
server_id = 10
Socket =/tmp/mysql.sock

Replicate-do-db=tong
Replicate-ignore-db=mysql

Log-bin=mysql-bin
Log-bin-index=mysql-bin-index

auto_increment_offset=2
auto_increment_increment=2

Read-only=1
Relay_log_purge=0

[Email protected] ~]#

TONG3 node:

[Email protected] ~]# VIM/ETC/MY.CNF

Basedir =/usr/local/mysql-5.6.23
DataDir =/usr/local/mysql-5.6.23/data
Port = 3306
server_id = 30
Socket =/tmp/mysql.sock

Replicate-do-db=tong
Replicate-ignore-db=mysql

[Email protected] ~]#

7. Build tong1 and tong2 into main mode

TONG1 Host:

[email protected]. ssh]# mysql-u root-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 60
Server Version:5.6.23-log MySQL Community Server (GPL)

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> Change Master to master_host= ' 192.168.1.248 ', master_port=3306,master_user= ' repl_user ', master_password= ' system!#%246 ', master_log_file= ' mysql-bin.000010 ', master_log_pos=120;

Query OK, 0 rows affected, 2 warnings (0.45 sec)

mysql> start slave;
Query OK, 0 rows affected (0.05 sec)

Mysql>

TONG1 Host:

[email protected]. ssh]# mysql-u root-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 60
Server Version:5.6.23-log MySQL Community Server (GPL)

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> Change Master to master_host= ' 192.168.1.247 ', master_port=3306,master_user= ' repl_user ', master_password= ' system!#%246 ', master_log_file= ' mysql-bin.000010 ', master_log_pos=120;

Query OK, 0 rows affected, 2 warnings (0.45 sec)

mysql> start slave;
Query OK, 0 rows affected (0.05 sec)

Mysql>

8. Check if replication is wrong

[Email protected] ~]# MASTERHA_CHECK_REPL--conf=/etc/mysqlmha/conf/app1.cnf

Tue APR 15:53:23-[INFO] Checking replication Health on 192.168.1.249.
Tue APR 15:53:23-[info] OK.
Tue APR 15:53:23-[INFO] Checking replication Health on 192.168.1.248.
Tue APR 15:53:23-[info] OK.
Tue APR 15:53:23-[WARNING] master_ip_failover_script is not defined.
Tue APR 15:53:23-[WARNING] shutdown_script is not defined.
Tue APR 15:53:23-[INFO] Got exit code 0 (not master dead).

MySQL Replication Health is OK.

[Email protected] mysqlmha]# nohup masterha_manager--conf=/etc/mysqlmha/conf/app1.cnf >>/tmp/mha_manager 2 >&1 &

[[email protected] mysqlmha]# jobs
[1]+ Running nohup masterha_manager--conf=/etc/mysqlmha/conf/app1.cnf >>/tmp/mha_manager 2>&am P;1 &
[Email protected] mysqlmha]# Masterha_check_status--conf=/etc/mysqlmha/conf/app1.cnf
App1 (pid:24330) is running (0:PING_OK), master:192.168.1.247--at this point the master node is on the 247 server
[Email protected] mysqlmha]#

9. Fault Testing

TONG1 node:

[[email protected] ~]#/etc/init.d/mysqld stop--Stop the database of the TONG1 node
Shutting down to MySQL ..... success!
[[email protected] ~]#/etc/init.d/mysqld start --the node must be started to switch

Starting MySQL. success!
[Email protected] ~]#

To view the log status of the Tong2 host, the TONG1 node must be synchronized to the TONG2 node before switching (change master to master_host= ' 192.168.1.248,master_ ....................)

TONG3 node:

[Email protected] mysqlmha]# Rm-rf/var/log/masterha/app1/app1.failover.complete
[1]+ done nohup masterha_manager--conf=/etc/mysqlmha/conf/app1.cnf >>/tmp/mha_manager 2>&am P;1--You must delete the monitoring files for the management node

[Email protected] mysqlmha]# nohup masterha_manager--conf=/etc/mysqlmha/conf/app1.cnf >>/tmp/mha_manager 2 >&1 &

[Email protected] ~]# Masterha_check_status--conf=/etc/mysqlmha/conf/app1.cnf
App1 (pid:27870) is running (0:PING_OK), master:192.168.1.248
[Email protected] ~]#

This article is from the "Days Together" blog, please be sure to keep this source http://tongcheng.blog.51cto.com/6214144/1639693

MHA of MySQL high-availability Program