Mysql high availability solution-MHA

Environment Planning: Host Name IP address management node tong3192.168.1.249 master node tong2192.168.1.248 master node tong1194251.247mysqldba Technology Group 378190849 Wuhan-linux O & M group 2364156191. Network and host name configuration set the IP address of each host and etchosts File Parsing

Environment Planning: 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 mysqldba Technology Group 378190849 Wuhan-linux O & M group 236415619 1. configure the network and host name, and set the IP address of each host and the/etc/hosts file to parse each other [root

Environment 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

Mysql dba Technical Group 378190849

Wuhan-linux O & M group 236415619

1. Network and host name Configuration

Set the IP address of each host and the/etc/hosts file to parse each other

[Root @ tong1 ~] # Cat/etc/hosts
192.168.1.247 tong1
192.168.1.248 tong2
192.168.1.249 tong3
[Root @ tong1 ~] # Ping tong1-c1 -- the network must be pinged
PING tong1 (192.168.1.247) 56 (84) bytes of data.
64 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 0 ms
Rtt min/avg/max/mdev = 0.021/0.021/0.021/0.000 MS
[Root @ tong1 ~] # Ping tong2-c1
PING tong2 (192.168.1.248) 56 (84) bytes of data.
64 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 0 ms
Rtt min/avg/max/mdev = 0.109/0.109/0.109/0.000 MS
[Root @ tong1 ~] # Ping tong3-c1
PING tong3 (192.168.1.249) 56 (84) bytes of data.
64 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 0 ms
Rtt min/avg/max/mdev = 0.124/0.124/0.124/0.000 MS
[Root @ tong1 ~] #

2. Install mha management software mha manager

Tong3 management node:

[Root @ tong3 ~] # Yum install perl-DBD-MySQL cpan -- install the perl Tool

[Root @ tong3 ~] # Tar xvf mha4mysql-manager-0.53.tar.gz-C/usr/local/

[Root @ tong3 ~] # Cd/usr/local/mha4mysql-manager-0.53/

[Root @ tong3 mha4mysql-manager-0.53] # perl Makefile. PL

[Root @ tong3 mha4mysql-manager-0.53] # echo $?
0
[Root @ tong3 mha4mysql-manager-0.53] # make & make install

[Root @ tong3 mha4mysql-manager-0.53] # echo $?
0
[Root @ tong3 mha4mysql-manager-0.53] #

3. Install mha node on each data node

[Root @ tong2 ~] # Tar xvf mha4mysql-node-0.53.tar.gz
[Root @ tong2 ~] # Cd mha4mysql-node-0.53
[Root @ tong2 mha4mysql-node-0.53] # yum install perl-DBD-mysql cpan-y

[Root @ tong2 mha4mysql-node-0.53] # perl Makefile. PL
[Root @ tong2 mha4mysql-node-0.53] # make & make install

[Root @ tong2 mha4mysql-node-0.53] # echo $?
0
[Root @ tong2 mha4mysql-node-0.53] #

4. ssh mutual trust between nodes

[Root @ tong3 ~] # Ssh-keygen-t dsa

[Root @ tong3 ~] # Cd. ssh

[Root @ tong3. ssh] # cat id_dsa.pub> authorized_keys

[Root @ tong3. ssh] # scp 192.168.1.247:/root/. ssh/id_dsa.pub 247

[Root @ tong3. ssh] # scp 192.168.1.248:/root/. ssh/id_dsa.pub 248

[Root @ tong3. ssh] # cat 248 247> authorized_keys
[Root @ tong3. ssh] # scp authorized_keys 192.168.1.248:/root/. ssh/
Authorized_keys 100% 1800 1.8KB/s
[Root @ tong3. ssh] # scp authorized_keys 192.168.1.247:/root/. ssh/
Root@192.168.1.247's password:
Authorized_keys 100% 1800 1.8KB/s
[Root @ tong3. ssh] # ssh tong1 date
Tue Apr 28 8 12:57:02 CST 2015
[Root @ tong3. ssh] # ssh tong2 date
Tue Apr 28 8 12:59:57 CST 2015
[Root @ tong3. ssh] # ssh tong3 date
Tue Apr 28 8 12:57:25 CST 2015
[Root @ tong3. ssh] #

5. Edit the configuration file on the Management Node

[Root @ tong3. ssh] # mkdir/etc/mysqlmha
[Root @ tong3. ssh] # cd/etc/mysqlmha/
[Root @ tong3 mysqlmha] # cp-a/usr/local/mha4mysql-manager-0.53/samples /*.
[Root @ tong3 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 a 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 primary database

[Server2]
Hostname = 192.168.1.248
Master_binlog_dir = "/usr/local/mysql-5.6.23/data/" -- binary log file storage
Candidate_master = 1 -- you can switch to the primary database.

[Server4]
Hostname = 192.168.1.247
Master_binlog_dir = "/usr/local/mysql-5.6.23/data /"
Candidate_master = 1-you can switch to the primary database.

[Root @ tong3 ~] # Masterha_check_ssh -- conf =/etc/mysqlmha/conf/app1.cnf
Tue Apr 28 15:39:21 2015-[warning] Global configuration file/etc/masterha_default.cnf not found. Skipping.
Tue Apr 28 15:39:21 2015-[info] Reading application default deployments from/etc/mysqlmha/conf/app1.cnf ..
Tue Apr 28 15:39:21 2015-[info] Reading server deployments from/etc/mysqlmha/conf/app1.cnf ..
Tue Apr 28 15:39:21 2015-[info] Starting SSH connection tests ..
Tue Apr 28 15:39:22 2015-[debug]
Tue Apr 28 15:39:21 2015-[debug] Connecting via SSH from root@192.168.1.249 (192.168.1.249: 22) to root@192.168.1.248 (192.168.1.248: 22 )..
Tue Apr 28 15:39:22 2015-[debug] OK.
Tue Apr 28 15:39:22 2015-[debug] Connecting via SSH from root@192.168.1.249 (192.168.1.249: 22) to root@192.168.1.247 (192.168.1.247: 22 )..
Tue Apr 28 15:39:22 2015-[debug] OK.
Tue Apr 28 15:39:23 2015-[debug]
Tue Apr 28 15:39:22 2015-[debug] Connecting via SSH from root@192.168.1.248 (192.168.1.248: 22) to root@192.168.1.249 (192.168.1.249: 22 )..
Tue Apr 28 15:39:22 2015-[debug] OK.
Tue Apr 28 15:39:22 2015-[debug] Connecting via SSH from root@192.168.1.248 (192.168.1.248: 22) to root@192.168.1.247 (192.168.1.247: 22 )..
Tue Apr 28 15:39:23 2015-[debug] OK.
Tue Apr 28 15:39:24 2015-[debug]
Tue Apr 28 15:39:22 2015-[debug] Connecting via SSH from root@192.168.1.247 (192.168.1.247: 22) to root@192.168.1.249 (192.168.1.249: 22 )..
Tue Apr 28 15:39:23 2015-[debug] OK.
Tue Apr 28 15:39:23 2015-[debug] Connecting via SSH from root@192.168.1.247 (192.168.1.247: 22) to root@192.168.1.248 (192.168.1.248: 22 )..
Tue Apr 28 15:39:23 2015-[debug] OK.
Tue Apr 28 15:39:24 2015-[info] All SSH connection tests passed successfully.
[Root @ tong3 ~] #

6. Modify the database configuration file and create a user

Create the same user on all nodes:

Mysql> grant all privileges on *. * to root1 @ '2017. 192.% 'identified by 'system ';

Mysql> grant replication slave, replication client on *. * to repl_user @ '2017. 192.% 'identified by 'System! # % 246 ';

Tong1 node:

[Root @ tong1 ~] # 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

[Root @ tong1 ~] #

Tong2 node:

[Root @ tong2 ~] # 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

[Root @ tong2 ~] #

Tong3 node:

[Root @ tong3 ~] # 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

[Root @ tong3 ~] #

7. Build the main mode of tong1 and tong2

Tong1 Host:

[Root @ tong1. 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) 2000,201 5, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
Affiliates. Other names may be 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 = 192, 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:

[Root @ tong2. 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) 2000,201 5, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
Affiliates. Other names may be 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 = 192, 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 whether there are errors in replication.

[Root @ tong3 ~] # Masterha_check_repl -- conf =/etc/mysqlmha/conf/app1.cnf

Tue Apr 28 15:53:23 2015-[info] Checking replication health on 192.168.1.249 ..
Tue Apr 28 15:53:23 2015-[info] OK.
Tue Apr 28 15:53:23 2015-[info] Checking replication health on 192.168.1.248 ..
Tue Apr 28 15:53:23 2015-[info] OK.
Tue Apr 28 15:53:23 2015-[warning] master_ip_failover_script is not defined.
Tue Apr 28 15:53:23 2015-[warning] shutdown_script is not defined.
Tue Apr 28 15:53:23 2015-[info] Got exit code 0 (Not master dead ).

MySQL Replication Health is OK.

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

[Root @ tong3 mysqlmha] # jobs
[1] + Running nohup masterha_manager -- conf =/etc/mysqlmha/conf/app1.cnf>/tmp/mha_manager 2> & 1 &
[Root @ tong3 mysqlmha] # masterha_check_status -- conf =/etc/mysqlmha/conf/app1.cnf
App1 (pid: 24330) is running (0: PING_ OK), master: 192.168.1.247 -- the master node is on the 247 server.
[Root @ tong3 mysqlmha] #

9. Fault Test

Tong1 node:

[Root @ tong1 ~] #/Etc/init. d/mysqld stop -- stop the database of the tong1 Node
Shutting down MySQL... SUCCESS!
[Root @ tong2 ~] #/Etc/init. d/mysqld start -- the node must be started before switching.

Starting MySQL. SUCCESS!
[Root @ tong2 ~] #

Check the log status of the tong2 host. You must synchronize the tong1 node to the tong2 node before switching (change master to master_host = '2017. 168.1.248, master _....................)

Tong3 node:

[Root @ tong3 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> & 1 -- the monitoring file of the Management node must be deleted.

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

[Root @ tong3 ~] # Masterha_check_status -- conf =/etc/mysqlmha/conf/app1.cnf
App1 (pid: 27870) is running (0: PING_ OK), master: 192.168.1.248
[Root @ tong3 ~] #