MySQL and SQL Server: January 2015

Centos+MHA+keepalived+MySQL installation

Master:192.168.1.200

sMaster:192.168.1.197

Slave: 192.168.1.155

Configure master on 192.168.1.200

[root@Master ~]# cat /etc/my.cnf

[mysqld]

innodb_buffer_pool_size = 128M

log_bin=/usr/local/mysql/data/binlog.log

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

port = 3306

server_id = 1

socket = /tmp/mysql.sock

binlog-ignore-db=mysql

binlog-ignore-db=test

binlog_format=mixed

innodb_flush_log_at_trx_commit=2

transaction-isolation = READ-COMMITTED

join_buffer_size = 128M

sort_buffer_size = 2M

read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[root@Master ~]# mysql -uroot -p

Enter password:

mysql> grant replication slave on *.* to 'repl'@'192.168.1.197' identified by '123123';

Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'repl'@'192.168.1.155' identified by '123123';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'192.168.197' identified by'123123';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'192.168.155' identified by'123123';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'192.168.200' identified by'123123';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

Configure second master on 192.168.1.197

[root@sMaster ~]# cat /etc/my.cnf

[mysqld]

innodb_buffer_pool_size = 128M

log_bin=/usr/local/mysql/data/binlog.log

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

port = 3306

server_id = 2

socket = /tmp/mysql.sock

binlog-ignore-db=mysql

binlog-ignore-db=test

binlog_format=mixed

innodb_flush_log_at_trx_commit=2

transaction-isolation = READ-COMMITTED

read_only=1

relay_log_purge=0

join_buffer_size = 128M

sort_buffer_size = 2M

read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[root@sMaster ~]# mysql -uroot -p

Enter password:

mysql> grant replication slave on *.* to 'repl'@'192.168.1.155' identified by '123123';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'192.168.197' identified by'123123';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'192.168.200' identified by'123123';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'192.168.155' identified by'123123';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

Configure Slave 192.168.1.155

[root@Slave ~]# mysql -uroot -p

Enter password:

mysql> grant replication slave on *.* to 'repl'@'192.168.1.200' identified by '123123';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'192.168.155' identified by'123123';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'192.168.197' identified by'123123';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'192.168.200' identified by'123123';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

[root@Slave ~]# cat /etc/my.cnf

[mysqld]

innodb_buffer_pool_size = 128M

#log_bin

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

port = 3306

server_id = 3

socket = /tmp/mysql.sock

join_buffer_size = 128M

sort_buffer_size = 2M

read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Replication configure:

On Master 192.168.1.200

mysql> show master status;

+---------------+----------+--------------+------------------+-------------------+

+---------------+----------+--------------+------------------+-------------------+

+---------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

On sMaster 192.168.1.197

mysql> change master to

-> master_host='192.168.1.200',

-> master_user='repl',

-> master_password='123123',

-> master_log_file='binlog.000001',

-> master_log_pos=120;

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

mysql> start slave;

Query OK, 0 rows affected (0.05 sec)

mysql> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.200

…..

Relay_Log_File: sMaster-relay-bin.000002

Relay_Log_Pos: 280

Relay_Master_Log_File: binlog.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

……

1 row in set (0.00 sec)

On sMaster 192.168.1.155

mysql> change master to

-> master_host='192.168.1.200',

-> master_user='repl',

-> master_password='123123',

-> master_log_file='binlog.000001',

-> master_log_pos=120;

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

mysql> start slave;

Query OK, 0 rows affected (0.05 sec)

mysql> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.200

…..

Relay_Log_File: sMaster-relay-bin.000002

Relay_Log_Pos: 280

Relay_Master_Log_File: binlog.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

……

1 row in set (0.00 sec)

Install keepalived on Master(192.168.1.200) and sMaster(192.168.1.197)

[root@Master local]# tar -zxvf keepalived-1.2.9.tar.gz

[root@Master local]# mv keepalived-1.2.9 keepalived

[root@Master keepalived]# yum install gcc openssl-devel -y

[root@Master keepalived]# ./configure --prefix=/usr --sysconf=/etc

checking for gcc... gcc

checking whether the C compiler works... yes

checking for C compiler default output file name... a.out

……………

Keepalived configuration

------------------------

Keepalived version : 1.2.9

Compiler : gcc

Compiler flags : -g -O2

Extra Lib : -lssl -lcrypto -lcrypt

Use IPVS Framework : Yes

IPVS sync daemon support : Yes

IPVS use libnl : No

Use VRRP Framework : Yes

Use VRRP VMAC : Yes

SNMP support : No

SHA1 support : No

Use Debug flags : No

[root@Master keepalived]# make

[1] 15813

make -C keepalived install

make -C lib || exit 1;

make[1]: Entering directory `/usr/local/keepalived/keepalived'

……

Make complete

make[1]: Leaving directory `/usr/local/keepalived/keepalived'

make -C genhash

make[1]: Entering directory `/usr/local/keepalived/genhash'

………………….

Make complete

make[1]: Leaving directory `/usr/local/keepalived/genhash'

Make complete

[root@Master keepalived]# make install

make -C keepalived install

make[1]: Entering directory `/usr/local/keepalived/keepalived'

install -d /usr/sbin

……

make[1]: Leaving directory `/usr/local/keepalived/genhash'

[root@Master keepalived]# service keepalived status

keepalived is stopped

[root@Master keepalived]# ls -lah

total 16K

drwxr-xr-x. 3 root root 4.0K Jan 8 10:35 .

drwxr-xr-x. 62 root root 4.0K Jan 8 10:35 ..

-rw-r--r--. 1 root root 3.5K Jan 8 10:35 keepalived.conf

drwxr-xr-x. 2 root root 4.0K Jan 8 10:35 samples

[root@Master keepalived]# vi keepalived.conf

global_defs {

router_id LVS_DEVEL

}

vrrp_sync_group VG1{

group {

VI_1

}

vrrp_instance VI_1 {

state BACKUP

interface eth0

virtual_router_id 51

priority 100

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.1.196/24 dev eth0 label eth0:1

}

virtual_server 192.168.1.196 3306 {

delay_loop 2

lb_algo rr

lb_kind DR

nat_mask 255.255.255.0

persistence_timeout 50

protocol TCP

real_server 192.168.1.200 3306 {

weight 1

notify_down /etc/checkmysql.sh

TCP_CHECK {

connect_timeout=10

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

Add VIP:192.168.1.196

[root@Master keepalived]# service keepalived start

Starting keepalived: [ OK ]

[root@Master keepalived]# tail -n 30 /var/log/messages

Jan 8 10:48:09 Master Keepalived_vrrp[17242]: VRRP_Instance(VI_1) Entering MASTER STATE

Jan 8 10:48:09 Master Keepalived_vrrp[17242]: VRRP_Instance(VI_1) setting protocol VIPs.

Jan 8 10:48:09 Master Keepalived_vrrp[17242]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.1.196

Jan 8 10:48:09 Master Keepalived_healthcheckers[17241]: Netlink reflector reports IP 192.168.1.196 added

Jan 8 10:48:14 Master Keepalived_vrrp[17242]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.1.196

Same steps on 192.168.1.197,

Vi /etc/keepalived/keepalived.conf

global_defs {

router_id LVS_DEVEL

}

vrrp_sync_group VG1{

group {

VI_1

}

vrrp_instance VI_1 {

state BACKUP

interface eth0

virtual_router_id 51

priority 100

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.1.196/24 dev eth0 label eth0:1

}

virtual_server 192.168.1.196 3306 {

delay_loop 2

lb_algo rr

lb_kind DR

nat_mask 255.255.255.0

persistence_timeout 50

protocol TCP

real_server 192.168.1.197 3306 {

weight 1

notify_down /etc/checkmysql.sh

TCP_CHECK {

connect_timeout=10

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

[root@sMaster keepalived]# vi keepalived.conf

[root@sMaster keepalived]# service keepalived status

keepalived is stopped

[root@sMaster keepalived]# service keepalived start

Starting keepalived: [ OK ]

[root@sMaster keepalived]# tail -n 10 /var/log/messages

Jan 8 12:44:56 sMaster Keepalived[15679]: Starting Healthcheck child process, pid=15685

Jan 8 12:44:56 sMaster Keepalived_healthcheckers[15685]: Netlink reflector reports IP 192.168.1.197 added

Jan 8 12:44:56 sMaster Keepalived_healthcheckers[15685]: Netlink reflector reports IP fe80::215:5dff:fe01:c312 added

Jan 8 12:44:56 sMaster Keepalived_healthcheckers[15685]: Registering Kernel netlink reflector

Jan 8 12:44:56 sMaster Keepalived_healthcheckers[15685]: Registering Kernel netlink command channel

Jan 8 12:44:56 sMaster Keepalived_healthcheckers[15685]: Opening file '/etc/keepalived/keepalived.conf'.

Jan 8 12:44:56 sMaster Keepalived_healthcheckers[15685]: Configuration is using : 10220 Bytes

Jan 8 12:44:56 sMaster Keepalived_healthcheckers[15685]: Using LinkWatch kernel netlink reflector...

Jan 8 12:44:56 sMaster Keepalived_healthcheckers[15685]: Activating healthchecker for service [192.168.1.197]:3306

Jan 8 12:44:56 sMaster kernel: IPVS: [rr] scheduler registered.

Install mha4mysql-node on master 192.168.1.200

[root@Master local]# tar -zxvf mha4mysql-node-0.54.tar.gz

[root@Master local]# mv mha4mysql-node-0.54 mha4mysql

[root@Master local]# cd mha4mysql

[root@Master mha4mysql]# yum install perl-DBD-MySQL perl-Config-Tiny perl-Params-Validate perl-Log-Dispatch perl-Parallel-ForkManager perl-devel cpan

[root@Master mha4mysql]# perl Makefile.PL

*** Module::AutoInstall version 1.03

*** Checking for Perl dependencies...

[Core Features]

- DBI ...loaded. (1.609)

- DBD::mysql ...loaded. (4.013)

*** Module::AutoInstall configuration finished.

Checking if your kit is complete...

Looks good

Writing Makefile for mha4mysql::node

[root@Master mha4mysql]# make && make install

……

Installing /usr/local/bin/apply_diff_relay_logs

Appending installation info to /usr/lib64/perl5/perllocal.pod

Same steps on sMaster(192.168.1.197) and Slave(192.168.1.155)

Configure SSH connection on the 3 db server

If use sudo user, must create the connection under sudo (dbaroot) and root user, this is very important tips.

#192.168.1.200

ssh-keygen -t rsa

ssh-copy-id -i .ssh/id_rsa.pub dbaroot@192.168.1.197

ssh-copy-id -i .ssh/id_rsa.pub dbaroot@192.168.1.155

ssh-copy-id -i .ssh/id_rsa.pub dbaroot@192.168.1.200

#192.168.1.197

ssh-keygen -t rsa

ssh-copy-id -i .ssh/id_rsa.pub dbaroot@192.168.1.200

ssh-copy-id -i .ssh/id_rsa.pub dbaroot@192.168.1.155

ssh-copy-id -i .ssh/id_rsa.pub dbaroot@192.168.1.197

#192.168.1.155

ssh-keygen -t rsa

ssh-copy-id -i .ssh/id_rsa.pub dbaroot@192.168.1.200

ssh-copy-id -i .ssh/id_rsa.pub dbaroot@192.168.1.197

ssh-copy-id -i .ssh/id_rsa.pub dbaroot@192.168.1.155

Testing the SSH connection

[dbaroot@Master ~]$ ssh dbaroot@192.168.1.197

[dbaroot@sMaster ~]$ exit

logout

Connection to 192.168.1.197 closed.

[dbaroot@Master ~]$ ssh dbaroot@192.168.1.155

[dbaroot@Slave ~]$ exit

logout

Connection to 192.168.1.155 closed.

[dbaroot@Master ~]$

Install mha4mysql-manager on slave(192.168.1.155)

[root@Slave ~]# cd /usr/local/

[root@Slave local]# tar -zxvf mha4mysql-manager-0.55.tar.gz

[root@Slave local]# mv mha4mysql-manager-0.55 mha4mysql-manager

[root@Slave local]# cd mha4mysql-manager

[root@Slave mha4mysql-manager]# ls -lah

total 84K

drwxr-xr-x. 10 root root 4.0K Dec 13 2012 .

drwxr-xr-x. 15 root root 4.0K Jan 8 15:32 ..

-rw-r--r--. 1 4984 users 78 Dec 12 2012 AUTHORS

drwxr-xr-x. 2 root root 4.0K Dec 13 2012 bin

-rw-r--r--. 1 4984 users 18K Dec 12 2012 COPYING

drwxr-xr-x. 2 root root 4.0K Dec 13 2012 debian

drwxr-xr-x. 3 root root 4.0K Dec 13 2012 inc

drwxr-xr-x. 3 root root 4.0K Dec 13 2012 lib

-rwxr-xr-x. 1 4984 users 406 Dec 12 2012 Makefile.PL

-rw-r--r--. 1 root root 4.8K Dec 12 2012 MANIFEST

-rw-r--r--. 1 root root 555 Dec 13 2012 META.yml

-rw-r--r--. 1 4984 users 258 Dec 12 2012 README

drwxr-xr-x. 2 root root 4.0K Dec 13 2012 rpm

drwxr-xr-x. 4 root root 4.0K Dec 13 2012 samples

drwxr-xr-x. 2 root root 4.0K Dec 13 2012 t

drwxr-xr-x. 3 root root 4.0K Dec 13 2012 tests

[root@Slave mha4mysql-manager]# perl Makefile.PL

*** Module::AutoInstall version 1.03

*** Checking for Perl dependencies...

[Core Features]

- DBI ...loaded. (1.609)

- DBD::mysql ...loaded. (4.013)

- Time::HiRes ...missing.

- Config::Tiny ...loaded. (2.12)

- Log::Dispatch ...loaded. (2.26)

- Parallel::ForkManager ...loaded. (0.7.9)

- MHA::NodeConst ...missing.

==> Auto-install the 2 mandatory module(s) from CPAN? [y]

[root@Slave mha4mysql-manager]# yum install perl-Time-HiRes -y

[root@Slave mha4mysql-manager]# perl Makefile.PL

*** Module::AutoInstall version 1.03

*** Checking for Perl dependencies...

[Core Features]

- DBI ...loaded. (1.609)

- DBD::mysql ...loaded. (4.013)

- Time::HiRes ...loaded. (1.9721)

- Config::Tiny ...loaded. (2.12)

- Log::Dispatch ...loaded. (2.26)

- Parallel::ForkManager ...loaded. (0.7.9)

- MHA::NodeConst ...missing.

MHA::NodeConst ...missing

Means we need to make and make install the MHA Node on slave 192.168.1.155

[root@Slave local]# cd mha4mysql-node

[root@Slave mha4mysql-node]# make && make install

cp lib/MHA/NodeUtil.pm blib/lib/MHA/NodeUtil.pm

……

Installing /usr/local/bin/purge_relay_logs

Installing /usr/local/bin/filter_mysqlbinlog

Installing /usr/local/bin/apply_diff_relay_logs

Installing /usr/local/bin/save_binary_logs

Appending installation info to /usr/lib64/perl5/perllocal.pod

[root@Slave local]# cd mha4mysql-manager

[root@Slave mha4mysql-manager]# perl Makefile.PL

*** Module::AutoInstall version 1.03

*** Checking for Perl dependencies...

[Core Features]

- DBI ...loaded. (1.609)

- DBD::mysql ...loaded. (4.013)

- Time::HiRes ...loaded. (1.9721)

- Config::Tiny ...loaded. (2.12)

- Log::Dispatch ...loaded. (2.26)

- Parallel::ForkManager ...loaded. (0.7.9)

- MHA::NodeConst ...loaded. (0.54)

*** Module::AutoInstall configuration finished.

Writing Makefile for mha4mysql::manager

[root@Slave mha4mysql-manager]# make && make install

……

Configure MHA Manager

[root@Slave mha4mysql-manager]# cd

[root@Slave ~]# mkdir -p masterha

[root@Slave ~]# cd masterha

[root@Slave masterha]# mkdir app1

[root@Slave masterha]# cd /etc

[root@Slave etc]# vi app1.cnf

[server default]

manager_workdir=/masterha/app1

manager_log=/masterha/app1/manager.log

#remote_workdir=/usr/local/mysql

user=mha

password=123123

ssh_user=dbaroot

repl_user=repl

repl_password=123123

ping_interval=1

shutdown_script=""

#master_ip_failover_script=/usr/local/bin/master_ip_failover

#master_ip_online_change_script=/usr/local/bin/master_ip_online_change_script

report_script=""

[server1]

hostname=192.168.1.200

master_binlog_dir=/usr/local/mysql/data

candidate_master=1

[server2]

hostname=192.168.1.197

master_binlog_dir=/usr/local/mysql/data

candidate_master=1

[server3]

hostname=192.168.1.155

master_binlog_dir=/usr/local/mysql/data

no_master=1

Check MHA replication and SSH status

[dbaroot@Slave ~]$ masterha_check_ssh --conf=/etc/app1.cnf

Thu Jan 8 16:39:46 2015 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Thu Jan 8 16:39:46 2015 - [info] Reading application default configurations from /etc/app1.cnf..

Thu Jan 8 16:39:46 2015 - [info] Reading server configurations from /etc/app1.cnf..

Thu Jan 8 16:39:46 2015 - [info] Starting SSH connection tests..

Thu Jan 8 16:39:50 2015 - [debug]

Thu Jan 8 16:39:46 2015 - [debug] Connecting via SSH from dbaroot@192.168.1.200(192.168.1.200:22) to dbaroot@192.168.1.197(192.168.1.197:22)..

Thu Jan 8 16:39:47 2015 - [debug] ok.

Thu Jan 8 16:39:47 2015 - [debug] Connecting via SSH from dbaroot@192.168.1.200(192.168.1.200:22) to dbaroot@192.168.1.155(192.168.1.155:22)..

Thu Jan 8 16:39:49 2015 - [debug] ok.

Thu Jan 8 16:39:50 2015 - [debug]

Thu Jan 8 16:39:46 2015 - [debug] Connecting via SSH from dbaroot@192.168.1.197(192.168.1.197:22) to dbaroot@192.168.1.200(192.168.1.200:22)..

Thu Jan 8 16:39:48 2015 - [debug] ok.

Thu Jan 8 16:39:48 2015 - [debug] Connecting via SSH from dbaroot@192.168.1.197(192.168.1.197:22) to dbaroot@192.168.1.155(192.168.1.155:22)..

Thu Jan 8 16:39:50 2015 - [debug] ok.

Thu Jan 8 16:39:50 2015 - [debug]

Thu Jan 8 16:39:47 2015 - [debug] Connecting via SSH from dbaroot@192.168.1.155(192.168.1.155:22) to dbaroot@192.168.1.200(192.168.1.200:22)..

Thu Jan 8 16:39:48 2015 - [debug] ok.

Thu Jan 8 16:39:48 2015 - [debug] Connecting via SSH from dbaroot@192.168.1.155(192.168.1.155:22) to dbaroot@192.168.1.197(192.168.1.197:22)..

Thu Jan 8 16:39:50 2015 - [debug] ok.

Thu Jan 8 16:39:50 2015 - [info] All SSH connection tests passed successfully.

[root@Slave ~]# masterha_check_repl --conf=/etc/app1.cnf

Thu Jan 8 17:01:23 2015 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Thu Jan 8 17:01:23 2015 - [info] Reading application default configurations from /etc/app1.cnf..

Thu Jan 8 17:01:23 2015 - [info] Reading server configurations from /etc/app1.cnf..

Thu Jan 8 17:01:23 2015 - [info] MHA::MasterMonitor version 0.55.

Thu Jan 8 17:01:23 2015 - [info] Dead Servers:

Thu Jan 8 17:01:23 2015 - [info] Alive Servers:

Thu Jan 8 17:01:23 2015 - [info] 192.168.1.200(192.168.1.200:3306)

Thu Jan 8 17:01:23 2015 - [info] 192.168.1.197(192.168.1.197:3306)

Thu Jan 8 17:01:23 2015 - [info] 192.168.1.155(192.168.1.155:3306)

Thu Jan 8 17:01:23 2015 - [info] Alive Slaves:

Thu Jan 8 17:01:23 2015 - [info] 192.168.1.197(192.168.1.197:3306) Version=5.6.11-log (oldest major version between slaves) log-bin:enabled

Thu Jan 8 17:01:23 2015 - [info] Replicating from 192.168.1.200(192.168.1.200:3306)

Thu Jan 8 17:01:23 2015 - [info] Primary candidate for the new Master (candidate_master is set)

Thu Jan 8 17:01:23 2015 - [info] 192.168.1.155(192.168.1.155:3306) Version=5.6.11 (oldest major version between slaves) log-bin:disabled

Thu Jan 8 17:01:23 2015 - [info] Replicating from 192.168.1.200(192.168.1.200:3306)

Thu Jan 8 17:01:23 2015 - [info] Not candidate for the new Master (no_master is set)

Thu Jan 8 17:01:23 2015 - [info] Current Alive Master: 192.168.1.200(192.168.1.200:3306)

Thu Jan 8 17:01:23 2015 - [info] Checking slave configurations..

Thu Jan 8 17:01:23 2015 - [info] read_only=1 is not set on slave 192.168.1.155(192.168.1.155:3306).

Thu Jan 8 17:01:23 2015 - [warning] relay_log_purge=0 is not set on slave 192.168.1.155(192.168.1.155:3306).

Thu Jan 8 17:01:23 2015 - [warning] log-bin is not set on slave 192.168.1.155(192.168.1.155:3306). This host can not be a master.

Thu Jan 8 17:01:23 2015 - [info] Checking replication filtering settings..

Thu Jan 8 17:01:23 2015 - [info] binlog_do_db= , binlog_ignore_db= mysql,test

Thu Jan 8 17:01:23 2015 - [info] Replication filtering check ok.

Thu Jan 8 17:01:23 2015 - [info] Starting SSH connection tests..

Thu Jan 8 17:01:28 2015 - [info] All SSH connection tests passed successfully.

Thu Jan 8 17:01:28 2015 - [info] Checking MHA Node version..

Thu Jan 8 17:01:29 2015 - [info] Version check ok.

Thu Jan 8 17:01:29 2015 - [info] Checking SSH publickey authentication settings on the current master..

Thu Jan 8 17:01:30 2015 - [info] HealthCheck: SSH to 192.168.1.200 is reachable.

Thu Jan 8 17:01:30 2015 - [info] Master MHA Node version is 0.54.

Thu Jan 8 17:01:30 2015 - [info] Checking recovery script configurations on the current master..

Thu Jan 8 17:01:30 2015 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/usr/local/mysql/data --output_file=/var/tmp/save_binary_logs_test --manager_version=0.55 --start_file=binlog.000001

Thu Jan 8 17:01:30 2015 - [info] Connecting to dbaroot@192.168.1.200(192.168.1.200)..

Creating /var/tmp if not exists.. ok.

Checking output directory is accessible or not..

ok.

Binlog found at /usr/local/mysql/data, up to binlog.000001

Thu Jan 8 17:01:31 2015 - [info] Master setting check done.

Thu Jan 8 17:01:31 2015 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..

Thu Jan 8 17:01:31 2015 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.1.197 --slave_ip=192.168.1.197 --slave_port=3306 --workdir=/var/tmp --target_version=5.6.11-log --manager_version=0.55 --relay_log_info=/usr/local/mysql/data/relay-log.info --relay_dir=/usr/local/mysql/data/ --slave_pass=xxx

Thu Jan 8 17:01:31 2015 - [info] Connecting to dbaroot@192.168.1.197(192.168.1.197:22)..

Can't exec "mysqlbinlog": No such file or directory at /usr/local/share/perl5/MHA/BinlogManager.pm line 99.

mysqlbinlog version not found!

at /usr/local/bin/apply_diff_relay_logs line 482

Thu Jan 8 17:01:32 2015 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln195] Slaves settings check failed!

Thu Jan 8 17:01:32 2015 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln375] Slave configuration failed.

Thu Jan 8 17:01:32 2015 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln386] Error happend on checking configurations. at /usr/local/bin/masterha_check_repl line 48

Thu Jan 8 17:01:32 2015 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln482] Error happened on monitoring servers.

Thu Jan 8 17:01:32 2015 - [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!

Got error:

Can't exec "mysqlbinlog": No such file or directory at /usr/local/share/perl5/MHA/BinlogManager.pm line 99

#192.168.1.197

[root@sMaster ~]# cp /usr/local/mysql/bin/mysqlbinlog /usr/bin/

[root@sMaster ~]# cp /usr/local/mysql/bin/mysql /usr/bin/

#192.168.1.155

[root@Slave ~]# cp /usr/local/mysql/bin/mysqlbinlog /usr/bin/

[root@Slave ~]# cp /usr/local/mysql/bin/mysql /usr/bin/

[root@Slave ~]# masterha_check_repl --conf=/etc/app1.cnf

Thu Jan 8 17:06:27 2015 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Thu Jan 8 17:06:27 2015 - [info] Reading application default configurations from /etc/app1.cnf..

Thu Jan 8 17:06:27 2015 - [info] Reading server configurations from /etc/app1.cnf..

Thu Jan 8 17:06:27 2015 - [info] MHA::MasterMonitor version 0.55.

Thu Jan 8 17:06:28 2015 - [info] Dead Servers:

Thu Jan 8 17:06:28 2015 - [info] Alive Servers:

Thu Jan 8 17:06:28 2015 - [info] 192.168.1.200(192.168.1.200:3306)

Thu Jan 8 17:06:28 2015 - [info] 192.168.1.197(192.168.1.197:3306)

Thu Jan 8 17:06:28 2015 - [info] 192.168.1.155(192.168.1.155:3306)

Thu Jan 8 17:06:28 2015 - [info] Alive Slaves:

Thu Jan 8 17:06:28 2015 - [info] 192.168.1.197(192.168.1.197:3306) Version=5.6.11-log (oldest major version between slaves) log-bin:enabled

Thu Jan 8 17:06:28 2015 - [info] Replicating from 192.168.1.200(192.168.1.200:3306)

Thu Jan 8 17:06:28 2015 - [info] Primary candidate for the new Master (candidate_master is set)

Thu Jan 8 17:06:28 2015 - [info] 192.168.1.155(192.168.1.155:3306) Version=5.6.11 (oldest major version between slaves) log-bin:disabled

Thu Jan 8 17:06:28 2015 - [info] Replicating from 192.168.1.200(192.168.1.200:3306)

Thu Jan 8 17:06:28 2015 - [info] Not candidate for the new Master (no_master is set)

Thu Jan 8 17:06:28 2015 - [info] Current Alive Master: 192.168.1.200(192.168.1.200:3306)

Thu Jan 8 17:06:28 2015 - [info] Checking slave configurations..

Thu Jan 8 17:06:28 2015 - [info] read_only=1 is not set on slave 192.168.1.155(192.168.1.155:3306).

Thu Jan 8 17:06:28 2015 - [warning] relay_log_purge=0 is not set on slave 192.168.1.155(192.168.1.155:3306).

Thu Jan 8 17:06:28 2015 - [warning] log-bin is not set on slave 192.168.1.155(192.168.1.155:3306). This host can not be a master.

Thu Jan 8 17:06:28 2015 - [info] Checking replication filtering settings..

Thu Jan 8 17:06:28 2015 - [info] binlog_do_db= , binlog_ignore_db= mysql,test

Thu Jan 8 17:06:28 2015 - [info] Replication filtering check ok.

Thu Jan 8 17:06:28 2015 - [info] Starting SSH connection tests..

Thu Jan 8 17:06:32 2015 - [info] All SSH connection tests passed successfully.

Thu Jan 8 17:06:32 2015 - [info] Checking MHA Node version..

Thu Jan 8 17:06:34 2015 - [info] Version check ok.

Thu Jan 8 17:06:34 2015 - [info] Checking SSH publickey authentication settings on the current master..

Thu Jan 8 17:06:35 2015 - [info] HealthCheck: SSH to 192.168.1.200 is reachable.

Thu Jan 8 17:06:35 2015 - [info] Master MHA Node version is 0.54.

Thu Jan 8 17:06:35 2015 - [info] Checking recovery script configurations on the current master..

Thu Jan 8 17:06:35 2015 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/usr/local/mysql/data --output_file=/var/tmp/save_binary_logs_test --manager_version=0.55 --start_file=binlog.000001

Thu Jan 8 17:06:35 2015 - [info] Connecting to dbaroot@192.168.1.200(192.168.1.200)..

Creating /var/tmp if not exists.. ok.

Checking output directory is accessible or not..

ok.

Binlog found at /usr/local/mysql/data, up to binlog.000001

Thu Jan 8 17:06:36 2015 - [info] Master setting check done.

Thu Jan 8 17:06:36 2015 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..

Thu Jan 8 17:06:36 2015 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.1.197 --slave_ip=192.168.1.197 --slave_port=3306 --workdir=/var/tmp --target_version=5.6.11-log --manager_version=0.55 --relay_log_info=/usr/local/mysql/data/relay-log.info --relay_dir=/usr/local/mysql/data/ --slave_pass=xxx

Thu Jan 8 17:06:36 2015 - [info] Connecting to dbaroot@192.168.1.197(192.168.1.197:22)..

Checking slave recovery environment settings..

Opening /usr/local/mysql/data/relay-log.info ... ok.

Relay log found at /usr/local/mysql/data, up to sMaster-relay-bin.000002

Temporary relay log file is /usr/local/mysql/data/sMaster-relay-bin.000002

Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure.

done.

Testing mysqlbinlog output.. done.

Cleaning up test file(s).. done.

Thu Jan 8 17:06:37 2015 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.1.155 --slave_ip=192.168.1.155 --slave_port=3306 --workdir=/var/tmp --target_version=5.6.11 --manager_version=0.55 --relay_log_info=/usr/local/mysql/data/relay-log.info --relay_dir=/usr/local/mysql/data/ --slave_pass=xxx

Thu Jan 8 17:06:37 2015 - [info] Connecting to dbaroot@192.168.1.155(192.168.1.155:22)..

Checking slave recovery environment settings..

Opening /usr/local/mysql/data/relay-log.info ... ok.

Relay log found at /usr/local/mysql/data, up to Slave-relay-bin.000002

Temporary relay log file is /usr/local/mysql/data/Slave-relay-bin.000002

Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure.

done.

Testing mysqlbinlog output.. done.

Cleaning up test file(s).. done.

Thu Jan 8 17:06:40 2015 - [info] Slaves settings check done.

Thu Jan 8 17:06:40 2015 - [info]

192.168.1.200 (current master)

+--192.168.1.197

+--192.168.1.155

Thu Jan 8 17:06:40 2015 - [info] Checking replication health on 192.168.1.197..

Thu Jan 8 17:06:40 2015 - [info] ok.

Thu Jan 8 17:06:40 2015 - [info] Checking replication health on 192.168.1.155..

Thu Jan 8 17:06:40 2015 - [info] ok.

Thu Jan 8 17:06:40 2015 - [warning] master_ip_failover_script is not defined.

Thu Jan 8 17:06:40 2015 - [warning] shutdown_script is not defined.

Thu Jan 8 17:06:40 2015 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

[root@Slave ~]#

MHA check replication successfully.

Start MHA Manager service

[root@Slave ~]# nohup masterha_manager --conf=/etc/app1.cnf < /dev/null > /masterha/app1/manager.log 2>&1 &

[1] 16086

[root@Slave ~]# masterha_check_status --conf=/etc/app1.cnf

app1 monitoring program is now on initialization phase(10:INITIALIZING_MONITOR). Wait for a while and try checking again.

[root@Slave ~]# masterha_check_status --conf=/etc/app1.cnf

app1 monitoring program is now on initialization phase(10:INITIALIZING_MONITOR). Wait for a while and try checking again.

[root@Slave ~]# masterha_check_status --conf=/etc/app1.cnf

app1 (pid:16086) is running(0:PING_OK), master:192.168.1.200

ping the keepalived VIP 192.168.1.196 from 192.168.1.155

[root@Slave ~]# ping 192.168.1.196

PING 192.168.1.196 (192.168.1.196) 56(84) bytes of data.

64 bytes from 192.168.1.196: icmp_seq=1 ttl=64 time=0.784 ms

64 bytes from 192.168.1.196: icmp_seq=2 ttl=64 time=0.175 ms

Stop mysql service on master(192.168.1.200)

[root@Master ~]# service mysql stop

Shutting down MySQL.... SUCCESS!

[root@sMaster ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 8457

Server version: 5.6.11-log MySQL Community Server (GPL)

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> show master status;

+---------------+----------+--------------+------------------+-------------------+

+---------------+----------+--------------+------------------+-------------------+

+---------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql> show slave status\G;

Empty set (0.00 sec)

ERROR:

No query specified

The sMaster automatically switch from Replication Slave to Master.

We check the slave 192.168.1.155

[root@Slave ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 33

Server version: 5.6.11 MySQL Community Server (GPL)

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> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.197

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000001

Read_Master_Log_Pos: 120

Relay_Log_File: Slave-relay-bin.000002

Relay_Log_Pos: 280

Relay_Master_Log_File: binlog.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

The slave status show us the 192.168.1.155 automatically switch sync data from 192.168.1.200 to 192.168.1.197

We ping the VIP 192.168.1.196 again

[root@Slave ~]# ping 192.168.1.196

PING 192.168.1.196 (192.168.1.196) 56(84) bytes of data.

64 bytes from 192.168.1.196: icmp_seq=1 ttl=64 time=0.447 ms

64 bytes from 192.168.1.196: icmp_seq=2 ttl=64 time=0.276 ms

[root@Slave ~]# mysql -h192.168.1.196 -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 230

Server version: 5.6.11-log MySQL Community Server (GPL)

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>

MySQL and SQL Server

Thursday, 8 January 2015

Centos MHA keepalived MySQL installation