One 、 To configure VIP drift

Host name

IP Address (NAT)

drift VIP

describe

mysql-db01

eth0:192.168.0.51

VIP:192.168.0.60

System :CentOS6.5(6.x Fine ) install :mysql5.6

mysql-db02

eth0:192.168.0.52

VIP:192.168.0.60

System :CentOS6.5(6.x Fine ) install :mysql5.6

mysql-db03

eth0:192.168.0.53

VIP:192.168.0.60

System :CentOS6.5(6.x Fine ) install :mysql5.6

1.1 IP Two ways of drift

adopt keepalived The way , Manage virtual IP The drift of

adopt MHA With script mode , Manage virtual IP The drift of

1.2 MHA Script management

(1) Get management scripts master_ip_failover

Tips :yum Installed manager There is no script .

We need to go from manager Copy one from your source code package .

[root@mysql-db03 ~]# ll mha4mysql-manager-0.56.tar.gz

-rw-r--r--. 1 root root 113914 Sep  4 18:43 mha4mysql-manager-0.56.tar.gz

[root@mysql-db03 ~]# tar xf mha4mysql-manager-0.56.tar.gz

[root@mysql-db03 ~]# cd mha4mysql-manager-0.56

[root@mysql-db03 mha4mysql-manager-0.56]# cd samples/scripts/

[root@mysql-db03 scripts]# ll

total 32

-rwxr-xr-x. 1 4984 users  3648 Mar 31  2014 master_ip_failover  # That's managing virtual IP Script for

-rwxr-xr-x. 1 4984 users  9870 Mar 31  2014 master_ip_online_change

-rwxr-xr-x. 1 4984 users 11867 Mar 31  2014 power_manager

-rwxr-xr-x. 1 4984 users  1360 Mar 31  2014 send_report

Copied to the /usr/local/bin Under the table of contents , Take this script as a command

[root@mysql-db03 scripts]# cp master_ip_failover /usr/local/bin/    # Copied to the /usr/local/bin Under the table of contents

[root@mysql-db03 scripts]# which master_ip_failover

/usr/local/bin/master_ip_failover

chmod +x /usr/local/bin/master_ip_failover

2) modify mha The configuration file

[root@mysql-db03 scripts]# vim /etc/mha/mha1.cnf

[root@mysql-db03 scripts]# cat /etc/mha/mha1.cnf

[server default]

manager_log=/var/log/mha/mha1/manager

manager_workdir=/var/log/mha/mha1

master_binlog_dir=/usr/local/mysql/data

master_ip_failover_script=/usr/local/bin/master_ip_failover     # Add script location

password=123123

ping_interval=2

repl_password=123123

repl_user=rep

ssh_user=root

user=mha

#candidate_master=1         # Keep spare comment entries

#check_repl_delay=0         # Keep spare comment entries

[server1]

hostname=192.168.0.51

port=3306

[server2]

hostname=192.168.0.52

port=3306

[server3]

hostname=192.168.0.53

port=3306

(3) Modify the source program VIP Drift script

# In the script below, add the following 4 Line code :

[root@mysql-db03 scripts]# sed -n '34,37p' /usr/local/bin/master_ip_failover

my $vip = '192.168.0.60/24';        # Definition VIP

my $key = '0';                      # Define the network card suffix value , If eth0 Namely 0,eth1 Namely 1

my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";    # binding VIP The order of

my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";     # close VIP The order of

hot tip :

Due to its own template script special pit , There's too much to change , therefore , Students can directly copy the script file to /usr/local/bin Under the table of contents , And give x jurisdiction .

4 The modified master_ip_failover The content of the script is as follows :

[root@mysql-db03 ~]# cat /usr/local/bin/master_ip_failover

#!/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.0.60/24';

my $key = '0';

my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";

my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";

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 {

if ( $command eq "stop" || $command eq "stopssh" ) {

my $exit_code = 1;

eval {

print "Disabling the VIP on old master:$orig_master_host \n";

&stop_vip();

$exit_code = 0;

};

if ($@) {

warn "Got Error: $@\n";

exit $exit_code;

}

exit $exit_code;

}

elsif ( $command eq "start" ) {

my $exit_code = 10;

eval {

print "Enabling the VIP - $vip on the new master - $new_master_host \n";

&start_vip();

$exit_code = 0;

};

if ($@) {

warn $@;

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;

}

}

sub start_vip() {

`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;

}

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=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";

}

5) Restart mha The management end

[root@mysql-db03 ~]# ps -ef | grep mha | grep -v grep       # see mha process

root      14138  13211  0 19:22 pts/3    00:00:00 perl /usr/bin/masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover

[root@mysql-db03 ~]# pkill perl         # kill perl process

[1]+  Exit 1                  nohup masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/mha1/manager.log 2>&1

[root@mysql-db03 ~]# ps -ef | grep mha | grep -v grep       # see mha process

[root@mysql-db03 ~]# nohup masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null >      # start-up mha process /var/log/mha/mha1/manager.log 2>&1 &

There are three cases of startup failure : Configuration file error 、ssh verification 、 Master slave replication verification

Tips :

If you start mha Process failed , Need to carry out mha Connection detection for

masterha_check_ssh --conf=/etc/mha/mha1.cnf ssh Connection detection

masterha_check_repl --conf=/etc/mha/mha1.cnf Master slave copy detection

Two 、VIP Drift script validation test

Architecture initial state :

(1) see mysql-db01 Network state

(2) Stop mysql-db01 Of MySQL Database services

[root@mysql-db01 ~]# /etc/init.d/mysqld stop

Shutting down MySQL..... SUCCESS!

[root@mysql-db01 ~]# ifconfig                   #VIP Vanished

eth0      Link encap:Ethernet  HWaddr 00:0C:29:59:7C:EF

inet addr:192.168.0.51  Bcast:192.168.0.255  Mask:255.255.255.0

inet6 addr: fe80::20c:29ff:fe59:7cef/64 Scope:Link

UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

RX packets:316130 errors:0 dropped:0 overruns:0 frame:0

TX packets:58058 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:363635227 (346.7 MiB)  TX bytes:6462008 (6.1 MiB)

lo        Link encap:Local Loopback

inet addr:127.0.0.1  Mask:255.0.0.0

inet6 addr: ::1/128 Scope:Host

UP LOOPBACK RUNNING  MTU:16436  Metric:1

RX packets:69 errors:0 dropped:0 overruns:0 frame:0

TX packets:69 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:0

RX bytes:9752 (9.5 KiB)  TX bytes:9752 (9.5 KiB)

(3) see mysql-db02

[root@mysql-db02 ~]# ifconfig           #VIP There is

eth0      Link encap:Ethernet  HWaddr 00:0C:29:4C:D5:92

inet addr:192.168.0.52  Bcast:192.168.0.255  Mask:255.255.255.0

inet6 addr: fe80::20c:29ff:fe4c:d592/64 Scope:Link

UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

RX packets:287225 errors:0 dropped:0 overruns:0 frame:0

TX packets:47133 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:361247254 (344.5 MiB)  TX bytes:5159560 (4.9 MiB)

eth0:0    Link encap:Ethernet  HWaddr 00:0C:29:4C:D5:92

inet addr:192.168.0.60  Bcast:192.168.0.255  Mask:255.255.255.0

UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

lo        Link encap:Local Loopback

inet addr:127.0.0.1  Mask:255.0.0.0

inet6 addr: ::1/128 Scope:Host

UP LOOPBACK RUNNING  MTU:16436  Metric:1

RX packets:45 errors:0 dropped:0 overruns:0 frame:0

TX packets:45 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:0

RX bytes:7718 (7.5 KiB)  TX bytes:7718 (7.5 KiB)

(4) see mysql-db03 Master slave synchronization of

[root@mysql-db03 ~]# mysql -uroot -p123123 -e 'show slave status\G'

Warning: Using a password on the command line interface can be insecure.

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.52     #mysql-db03 The main library of has been switched to mysql-db02

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000008

Read_Master_Log_Pos: 271

Relay_Log_File: mysql-db03-relay-bin.000002

Relay_Log_Pos: 361

Relay_Master_Log_File: mysql-bin.000008

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

(5)mysql-db01 Fault recovery

[root@mysql-db01 ~]# /etc/init.d/mysqld start

Starting MySQL. SUCCESS!

[root@mysql-db01 ~]# mysql -uroot -p123123

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.52', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='123123';

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

mysql> start slave;

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.52

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000008

Read_Master_Log_Pos: 271

Relay_Log_File: mysql-db01-relay-bin.000002

Relay_Log_Pos: 361

Relay_Master_Log_File: mysql-bin.000008

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

(6) Make up for the missing mha The configuration file

[root@mysql-db03 ~]# vim /etc/mha/mha1.cnf

[root@mysql-db03 ~]# cat /etc/mha/mha1.cnf

[server default]

manager_log=/var/log/mha/mha1/manager

manager_workdir=/var/log/mha/mha1

master_binlog_dir=/usr/local/mysql/data

master_ip_failover_script=/usr/local/bin/master_ip_failover

password=123123

ping_interval=2

repl_password=123123

repl_user=rep

ssh_user=root

user=mha

[server1]

hostname=192.168.0.51

port=3306

[server2]

hostname=192.168.0.52

port=3306

[server3]

hostname=192.168.0.53

port=3306

(7) start-up mha Management process

[root@mysql-db03 ~]# ps -ef | grep mha | grep -v grep

[root@mysql-db03 ~]# nohup masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/mha1/manager.log 2>&1 &

[1] 14921

[root@mysql-db03 ~]# ps -ef | grep mha | grep -v grep

root      14921  13211  3 20:11 pts/3    00:00:00 perl /usr/bin/masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover

The architecture state at this point

3、 ... and , To configure binlog-server Backup server

Main library down , Maybe it'll cause the main library binlog Data loss occurs due to untimely replication , So configuration binlog-server Synchronous backup from time to time , It's a necessary means of security .

3.1  modify mha The configuration file

[root@mysql-db03 ~]# cat /etc/mha/mha1.cnf

[server default]

manager_log=/var/log/mha/mha1/manager

manager_workdir=/var/log/mha/mha1

master_binlog_dir=/usr/local/mysql/data     # Overall binlog Storage location

master_ip_failover_script=/usr/local/bin/master_ip_failover

password=123123

ping_interval=2

repl_password=123123

repl_user=rep

ssh_user=root

user=mha

[server1]

hostname=192.168.0.51

port=3306

[server2]

hostname=192.168.0.52

port=3306

[server3]

hostname=192.168.0.53

port=3306

[binlog1]           # add to binlog modular

no_master=1         # Switching to master is not allowed

hostname=192.168.0.53       # Deposit IP

master_binlog_dir=/data/mysql/binlog/       #binlog The priority of storage location is higher than that of global

3.2  Pull... From the main library binlog Log to mysql-db03 It's in our catalogue

[root@mysql-db03 ~]# mkdir -p /data/mysql/binlog    # Create a storage directory

[root@mysql-db03 ~]# cd /data/mysql/binlog/ # Go to the storage directory

[root@mysql-db03 binlog]# ll

total 0

[root@mysql-db03 binlog]# which mysqlbinlog   ( This command is to pull logs )

/usr/local/bin/mysqlbinlog

[root@mysql-db03 binlog]# mysqlbinlog -R --host=192.168.0.52 --user=mha --password=123123 --raw --stop-never mysql-bin.000001 &     # Pull the main library binlog

[2] 15694

[root@mysql-db03 binlog]# Warning: Using a password on the command line interface can be insecure.

[root@mysql-db03 binlog]# ll

total 32

-rw-r--r--. 1 root root  143 Sep  5 20:53 mysql-bin.000001

-rw-r--r--. 1 root root  143 Sep  5 20:53 mysql-bin.000002

-rw-r--r--. 1 root root  331 Sep  5 20:53 mysql-bin.000003

-rw-r--r--. 1 root root 3114 Sep  5 20:53 mysql-bin.000004

-rw-r--r--. 1 root root  254 Sep  5 20:53 mysql-bin.000005

-rw-r--r--. 1 root root  800 Sep  5 20:53 mysql-bin.000006

-rw-r--r--. 1 root root 2714 Sep  5 20:53 mysql-bin.000007

-rw-r--r--. 1 root root  120 Sep  5 20:53 mysql-bin.000008

[root@mysql-db03 binlog]# ps -ef | grep mysqlbinlog | grep -v grep

root      16061  12786  0 20:53 pts/2    00:00:00 mysqlbinlog -R --host=192.168.0.52 --user=mha --password=x xxxx --raw --stop-never mysql-bin.000001

3.3  start-up mha Management process

[root@mysql-db03 binlog]# nohup masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/mha1/manager.log 2>&1 &

[root@mysql-db03 binlog]# ps -ef | grep perl | grep -v grep

root      15697  13211  0 20:42 pts/3    00:00:00 perl /usr/bin/masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover

Now start MHA A lot of things need to be tested :

ssh Master slave copy   The configuration file   Script   Pull log process ( You have to start )

MHA( Next ) More articles about

  1. 【MySQL】MHA Deployment and MasterFailover The code analysis

    Official website :https://code.google.com/p/mysql-master-ha/ Reference resources :http://blog.csdn.net/wulantian/article/details/1328 ...

  2. mysql High availability Architecture MHA,haproxy Realize the separation of reading and writing

    MySQL High availability Architecture MHA One . What operation and maintenance personnel need to master MySQL technology : 1. basic SQL sentence 2. Basic management [ Management of library table data    Authority management ] 3. disaster        Ensure that data is not lost . Two . In the work MySQ ...

  3. C++ Program structure ---1

    C++ Basic course Beta edition original :Juan Soulié translate :Jing Xu (aqua) English original This tutorial is based on Juan Soulie English version of C++ The course is translated and adapted . This edition is the latest proofreading edition , Not yet finalized . Such as ...

  4. CENTOS6.6 Next mysql MHA Architecture building

    This article comes from my github pages Blog http://galengao.github.io/ namely www.gaohuirong.cn Abstract : This is a self built one mysql MHA article The previous installation steps are based on ...

  5. CentOS6.8 Next MySQL MHA Architecture notes

    Reprint please indicate the source , This paper addresses :http://www.cnblogs.com/ajiangg/p/6552855.html Here are CentOS6.8 Next MySQL MHA Architecture notes IP Resource Planning : 192. ...

  6. linux Next mysql5.7 Of MHA High availability architecture

    One .MHA brief introduction MHA(Master High Availability) Currently in mysql High availability is relatively mature . It's an excellent set of actions mysql High availability software for failover and master-slave promotion in high availability environment . stay MySQL fault ...

  7. High availability mysql And MHA Principle

    MHA How it works ? MHA How it works ? ============================================================================== ...

  8. High availability mysql And MHA Source analysis

    * MHA The whole breakdown of ( offline ) Handover process - Check the status of the main library , Make sure you crash . - Confirm service crash , preservation binlog, Push it to the main control , And the main library can be forced to close to avoid brain fissure . - Find out the latest data from the library ( That is to say read_mas ...

  9. MYSQL MHA

    MYSQL MHA brief introduction : MHA(Master High Availability) Currently in MySQL The high availability aspect is a relatively mature solution , It's from Japan DeNA company youshimaton( Now I work in Face ...

Random recommendation

  1. oracle Frequently used scripts

    Auto start oracle sqlplus -S sys/123456 as sysdba <<EOF startup quit EOF lsnrctl start lsnrctl reload

  2. jQuery Reset form The method of form

    1  $("#formChangePwd")[0].reset();// Clear the values of form elements 2   Or directly javascript Code operation http://www.jb51.net/art ...

  3. Cannot instantiate in thread spring The solution to the injected service

    Problem description stay Java Web Multithreading is used in the application , Find out Spring The injected service has been reporting NullPointerException. Use annotated statements @Resource and XML Configured bean Statement , All null pointers ...

  4. design pattern And its use

    What is design pattern ? design pattern It's a universal , A reusable solution to a common problem . Why design patterns ? The theoretical basis for introducing design patterns is very simple . We have problems every day . We may come across a decision to use ...

  5. pure css Realize the special effect of card reversal

    Have you ever seen the flashy effect of reshuffle on the Internet , The cards are facing us , And then click on , The cards are turned over , The effect is cool , Do you really want to know how to achieve it , The code is simple , Follow Xiaobian down . First of all, let's introduce the principle of flop : 1. Parent container settings ...

  6. git Learning and using records of

    git initls -ahgit add xxxgit commit -m "some message" git statusgit loggit log --pretty=on ...

  7. VBS timer 2

    Turn on timer , If you click pause , It shows the time you just spent on the thing ( In minutes ) dim c // Control cycle c= vbyes while c<>vbno dim a a= 60*hour(now)+m ...

  8. Android related

    The build env. ensure that your computer’s BIOS is set up to support Intel’s virtualization extensio ...

  9. Android-Recyclerview- Use split lines

    because Recyclerview Is in  android.support.v7.widget. package RecyclerView, So we need to guide Recycler library : guide Recycler library : Select project , Right click -->  ...

  10. queue hardware os

    Computer Science An Overview 11th Edition Queues are often used as the underlying structure of a buf ...