Mysql MHA (GTID) configuration (real)

Implementing the Environment
centos6.7
MYSQL5.6.36
Master: 192.168.1.191
From 1:192.168.1.145
From 2:192.168.1.146
Monitoring: Placed on the 192.168.1.146 from 2
Virtual ip:192.168.1.222

Prepare package: Download Link: https://pan.baidu.com/s/1jHYafcU Password: IRBV
EPEL-RELEASE-6-8.NOARCH.RPM (on all servers)

　　MHA4MYSQL-NODE-0.56-0.EL6.NOARCH.RPM (on all servers)

MHA4MYSQL-MANAGER-0.56-0.EL6.NOARCH.RPM (Monitoring Server)

　

Steps

1. Create a replication account on the master DB server (performed on master)
# MySQL
mysql> CREATE USER ' repl ' @ ' 192.168.1.% ' identified by ' 123456 ';
Mysql> GRANT REPLICATION SLAVE on * * to ' repl ' @ ' 192.168.1.% ';

2. Primary server configuration (performed on master)
# VIM/ETC/MY.CNF
----------------------------content is as follows---------------------------------

[Mysqld]

Basedir =/usr/local/mysql
DataDir =/data/mysql
Port = 3306

# Binary Logging #
Log-bin =/data/mysql/log-bin
# replice #
server_id = 191
Gtid_mode = On
Enforce_gtid_consiste = On
Log_slave_updates = On
----------------------------content as above---------------------------------

3. From the server configuration (performed on both slave servers)
# VIM/ETC/MY.CNF
----------------------------content is as follows----------------------------------

[Mysqld]

Basedir =/usr/local/mysql
DataDir =/data/mysql
Port = 3306

# Binary Logging #
Log-bin =/data/mysql/mysql-bin
# replice #
# Server-id = 145 #选择对应的
# Server-id = 146 #选择对应的
Relay_log =/data/mysql/relay-bin
Gtid_mode = On
Enforce_gtid_consistency = On
Log_slave_update = On
Read_Only = On
Master_info_repository = TABLE
Relay_log_info_repository = TABLE
----------------------------content as above----------------------------------

4. Restart all MySQL (performed on all servers)
# Service Mysqld Restart

5. Back up the primary database (if the database version is the same as the Master and Slave) (can be fully prepared) (performed on master)
# mysqldump--single-transaction--master-data=2--triggers--routines--all-databases > Full.sql

6. Transfer the primary database backup to the slave database (performed on master)
# scp/root/full.sql [Email protected]:/root
# scp/root/full.sql [Email protected]:/root

7. Import the backup of the primary database into the slave database (executed on both)
# Mysql-uroot-p < Full.sql

8. Initial replication link (performed on two slave)
# MySQL
Mysql>
Change Master to
Master_host= ' 192.168.1.191 ',
Master_user= ' Repl ',
Master_password= ' 123456 ',
Master_auto_position=1;
Mysql>start slave;
Mysql>show slave Status\g

All of the above are Gtid-based replication configuration sections

##########################################################################

9.SSH configuration (current user is root) (performed on all servers)
# Ssh-keygen #全部按回车
# ssh-copy-id-i/root/.ssh/id_rsa '-P 22 192.168.1.191 '
# ssh-copy-id-i/root/.ssh/id_rsa '-P 22 192.168.1.145 '
# ssh-copy-id-i/root/.ssh/id_rsa '-P 22 192.168.1.146 '

10. Install mha4mysql-node-0.56-0.el6.noarch.rpm (performed on all servers)
# RPM-IVH epel-release-6-8.noarch.rpm
# yum-y Install Perl-dbd-mysql ncftp
# RPM-IVH mha4mysql-node-0.56-0.el6.noarch.rpm

11. Install mha4mysql-manager-0.56-0.el6.noarch.rpm (execute on monitoring server)
# yum-y Install Perl-config-tiny.noarch perl-time-hires perl-parallel-forkmanager perl-log-dispatch-perl.noarch
# RPM-IVH mha4mysql-manager-0.56-0.el6.noarch.rpm

12. Create MHA working directory and database account
# mkdir-p/home/mysql_mha #创建MHA的工作目录 (performed on all servers)
# MySQL
Mysql> Grant all privileges on * * to [e-mail protected] ' 192.168.1.% ' identified by ' 123456 '; (executes only on the primary database and can be synced to other servers)
Mysql> Show grants for [email protected] ' 192.168.1.% '; #检查创建结果 (performed on all servers)

13. Configure MHA configuration file (monitor server execution)
# mkdir-p/ETC/MHA
# touch/etc/mha/mysql_mha.cnf
# vim/etc/mha/mysql_mha.cnf ----------------------------content is as follows----------------------------------
[Server default]
User=mha
password=123456
Manager_workdir=/home/mysql_mha             #MHA工作目录
manager_log= /home/mysql_mha/manager.log   #MHA管理日志
Remote_workdir=/home/mysql_mha               #MHA远程工作目录
Ssh_user=root                     #ssh时的用户
REPL_USER=REPL
repl_password=123456
ping_interval=1
master_binlog_dir=/data/mysql     #对应修改
Master_ip_failover_script=/usr/bin/master_ip_failover  
Secondary_check_script=/usr/bin/masterha_ Secondary_check-s 192.168.1.191-s 192.168.1.145-s 192.168.1.146

[Server1]
hostname=192.168.1.191
Candidate_master=1
[Server2]
hostname=192.168.1.145
Candidate_master=1
[Server3]
hostname=192.168.1.146
No_master=1 #不参入选举

----------------------------content as above----------------------------------

14. Create a master_ip_failover script for automatic switching (monitor server execution)
# Touch/usr/bin/master_ip_failover
# CHOMD 777/usr/bin/master_ip_failover
# Vim/usr/bin/master_ip_failover
----------------------------content is as follows----------------------------------(script is found online, pro-Test energy)
#!/usr/bin/env Perl
Use strict;
Use warnings FATAL = ' all ';

Use Getopt::long;

My (
$command, $ssh _user, $orig _master_host, $orig _master_ip,
$orig _master_port, $new _master_host, $new _master_ip, $new _master_port
);

My $vip = ' 192.168.1.222/24 '; #对应修改虚拟IP就可以了
My $key = "1";
My $ssh _start_vip = "/sbin/ifconfig eth0: $key $vip";
My $ssh _stop_vip = "/sbin/ifconfig eth0: $key down";
My $exit _code = 0;

GetOptions (
' command=s ' + \ $command,
' ssh_user=s ' + \ $ssh _user,
' orig_master_host=s ' + \ $orig _master_host,
' orig_master_ip=s ' + \ $orig _master_ip,
' orig_master_port=i ' + \ $orig _master_port,
' new_master_host=s ' + \ $new _master_host,
' new_master_ip=s ' + \ $new _master_ip,
' new_master_port=i ' + \ $new _master_port,
);

Exit &main ();

Sub Main {

#print "\n\nIN SCRIPT test==== $ssh _stop_vip== $ssh _start_vip===\n\n";

if ($command eq "Stop" | | $command eq "STOPSSH") {

# $orig _master_host, $orig _master_ip, $orig _master_port is passed.
# If you manage the master IP address at global Catalog database,
# invalidate ORIG_MASTER_IP here.
My $exit _code = 1;
eval {
print "\n\n\n***************************************************************\n";
Print "Disabling the vip-$vip on Old master: $orig _master_host\n";
print "***************************************************************\n\n\n\n";
&AMP;STOP_VIP ();
$exit _code = 0;
};
if ([email protected]) {
Warn "Got Error: [Email protected]\n";
Exit $exit _code;
}
Exit $exit _code;
}
elsif ($command eq "Start") {

# All arguments is passed.
# If you manage the master IP address at global Catalog database,
# Activate NEW_MASTER_IP here.
# can also grant Write access (create user, set read_only=0, etc) here.
My $exit _code = 10;
eval {
print "\n\n\n***************************************************************\n";
print "Enabling the vip-$vip on new master: $new _master_host \ n";
print "***************************************************************\n\n\n\n";
&AMP;START_VIP ();
$exit _code = 0;
};
if ([email protected]) {
Warn [email protected];
Exit $exit _code;
}
Exit $exit _code;
}
elsif ($command eq "status") {
Print "Checking The Status of the script. OK \ n ";
' SSH $ssh _user\@ $orig _master_host \ "$ssh _start_vip \";
Exit 0;
}
else {
&usage ();
Exit 1;
}
}

# A Simple system call to enable the VIP on the new master
Sub Start_vip () {
' SSH $ssh _user\@ $new _master_host \ "$ssh _start_vip \";
}
# A Simple system Call this disable the VIP on the Old_master
Sub Stop_vip () {
' SSH $ssh _user\@ $orig _master_host \ "$ssh _stop_vip \";
}

Sub Usage {
Print
"Usage:master_ip_failover–command=start|stop|stopssh|status–orig_master_host=host–orig_master_ip=ip–orig_ Master_port=po
Rt–new_master_host=host–new_master_ip=ip–new_master_port=port\n ";
}
----------------------------content as above----------------------------------

15. Check the relevant configuration and start MHA (monitor server execution)
# Masterha_check_ssh--CONF=/ETC/MHA/MYSQL_MHA.CNF
# MASTERHA_CHECK_REPL--CONF=/ETC/MHA/MYSQL_MHA.CNF
# nohup Masterha_manager--conf=/etc/mha/mysql_mha.cnf & #后台启动

16. Configure the virtual IP (performed on master)
# IP Addr
# ifconfig Eth0:1 192.168.1.222/24
# IP Addr

17. Test can automatically transfer
# Service Mysqld Stop (performed on master)
# IP addr (performed on master)
# IP addr (performed on 1)

If 192.168.1.222 successfully from the 192.168.1.191 drift to 192.168.1.146 on the success.

The above operating procedures for my actual record, the experiment successfully achieved VIP drift.

Mysql MHA (GTID) configuration (real)