程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> Percona-mysql MHA高可用實戰方案

Percona-mysql MHA高可用實戰方案

編輯:MySQL綜合教程

Percona-mysql MHA高可用實戰方案


前言

MHA(Master High Availability)目前在MySQL高可用方面是一個相對成熟的解決方案,它由日本DeNA公司youshimaton(現就職於Facebook公司)開發,是一套優秀的作為MySQLy高可用環境下故障切換和主從提升的高可用軟件。在MySQL故障切換過程中,MHA能做到在0~30秒之內自動完成數據庫的故障切換操作,並且在進行故障切換過程中,MHA能在最大程度上保證數據的一致性,以達到真正意義上的高可用。

它由兩部分組成:MHA Manager(管理節點)和MHA Node(數據節點)。MHA Manager可以單獨部署在一台獨立的機器上管理多個master-slave集群,也可以部署在一台slave上。

MHA node運行在每台MySQL服務器上,MHA Manager會定時探測集群中的master節點,當master 出現故障時,它可以自動將最新數據的slave提升為新的master,然後將所有其他的slave重新指向新的master。整個故障轉移過程對應用程序是完全透明的。

1. 安裝部署MHA前准備

MHA架構圖

wKioL1bXnxyTA1qsAABJ9QnuNdg689.png

 

 

 

 

 

 

 

 

 

具體搭建如表:

角色

IP地址

主機名

serverID

類型

Monitor host

192.168.127.100

MHA

 

監控集群組

Master

192.168.127.101

master

101

寫入

Candicate master

192.168.127.102

slave01

102

slave

192.168.127.103

slave02

103

 

vi /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.127.100 MHA

192.168.127.101 master

192.168.127.102 slave01

192.168.127.103 slave02

1.1. percona-mysql安裝(master、slave01、slave02 三台安裝)

注意:三台的server_id 不一樣,為了做主從同步

創建mysql用戶:

useradd mysql

創建安裝目錄與數據目錄:

mkdir /app

mkdir -p /data/mysql3306

解決percona-mysql軟件:

tar zxvf Percona-Server-5.6.27-rel75.0-Linux.x86_64.ssl101.tar.gz

注意:安裝的軟件需要根據openssl版本來下載

rpm -qa | grep ssl

openssl-1.0.1e-15.el6.x86_64

把解壓文件移動相應目錄:

mv Percona-Server-5.6.27-rel75.0-Linux.x86_64.ssl101 /app/mysql5.6

 

創建放慢查詢日志目錄:

mkdir /app/mysql5.6/logs

給目錄權限:

chown -R mysql:mysql /app/mysql5.6

chown -R mysql:mysql /data/mysql3306

 

創建配置文件

vi /app/mysql5.6/my.cnf

 

[client]

socket=/app/mysql5.6/mysql.sock

default-character-set=utf8

port=3306

[mysql]

prompt=\\u@\\d \\r:\\m:\\s>

no-auto-rehash

[mysqld_safe]

log-error=/data/mysql3306/mysqld.error

[mysqld]

socket=/app/mysql5.6/mysql.sock

pid-file=/app/mysql5.6/mysqld.pid

basedir=/app/mysql5.6

datadir=/data/mysql3306

port=3306

server_id=101

character-set-server=utf8

skip-external-locking

skip-name-resolve

max_connections=1024

max_connect_errors=1000

wait_timeout = 400

interactive_timeout = 400

table_definition_cache=500

table_open_cache=500

sort_buffer_size = 16M

tmp_table_size = 200M

 

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_type=0

query_cache_size=0

thread_concurrency = 16

lower_case_table_names = 1

log_bin_trust_function_creators = 1

#################slow log####################

slow-query_log=1

slow-query_log_file=/app/mysql5.6/logs/mysql.slow

long_query_time=2

####################binlog######################

log-bin=mysql-bin

binlog-format=ROW

expire_logs_days=5

sync_binlog=1

################replication##########

log-slave-updates=1

################INNODB################

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

transaction-isolation=READ-COMMITTED

innodb_buffer_pool_size=10G

innodb_flush_log_at_trx_commit=2

innodb_strict_mode=1

innodb_flush_method=O_DIRECT

innodb_file_format=Barracuda

innodb_log_files_in_group=3

innodb_file_per_table=1

innodb_io_capacity=500

innodb_support_xa=1

innodb_additional_mem_pool_size=16M

innodb_log_buffer_size=64M

 

 

[mysqldump]

quick

max_allowed_packet=128M

myisam_max_sort_sort_file_size=2G

 

 

初始化數據庫

/app/mysql5.6/scripts/mysql_install_db --user=mysql --basedir=/app/mysql5.6 --datadir=/data/mysql3306 --defaults-file=/app/mysql5.6/my.cnf

 

啟動腳本

cp /app/mysql5.6/support-files/mysql.server /etc/init.d/mysql

vi /etc/init.d/mysql

 

basedir=/app/mysql5.6

datadir=/data/mysql3306

 

注意:修改以上兩處即可

 

啟動數據庫

 

/etc/init.d/mysql start

 

Starting MySQL (Percona Server).... [ OK ]

 

環境變量配置

vi /etc/profile

 

export MYSQL_HOME=/app/mysql5.6

export MY_BASEDIR_VERSION=/app/mysql5.6

export PATH=/app/mysql5.6/bin:/app/mysql5.6/scripts:$PATH

export LD_LIBRARY_PATH=/app/mysql5.6/lib

 

生效環境變量

source /etc/profile

1.2 .主從同步搭建

注意:防火牆需要關閉

創建復制賬號(master、slave1(mha新主))

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.127.%' IDENTIFIED BY 'repl';

flush privileges;

 

查看master binlog POS點信息

root@(none) 06:47:05>show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000004 | 409 | | | |

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

1 row in set (0.01 sec)

 

建立主從復制(slave01、slave02)

[root@slave01 ~]# mysql

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

Your MySQL connection id is 2

Server version: 5.6.27-75.0-log Percona Server (GPL), Release 75.0, Revision 8bb53b6

 

Copyright (c) 2009-2015 Percona LLC and/or its affiliates

Copyright (c) 2000, 2015, 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.

 

root@(none) 07:03:39>CHANGE MASTER TO MASTER_HOST='192.168.127.101',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=409;

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

 

root@(none) 07:03:41>start slave;

Query OK, 0 rows affected (0.02 sec)

 

查看主從復制

root@(none) 07:03:42>show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.127.101

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 409

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 409

Relay_Log_Space: 457

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 101

Master_UUID: 8b1cf62d-e063-11e5-84ba-000c2908253f

Master_Info_File: /data/mysql3306/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.02 sec)

 

ERROR:

No query specified

 

以上主從已經搭建好,下面我們安裝與配置MHA

(1)slave服務器(192.168.127.102,192.168.103)設置read only;

mysql> set global read_only=1;

(2)設置relay log清除方式(在每個slave 下)

mysql> set global relay_log_purge=0;

(3)創建監控用戶,在所有MYSQL上執行

mysql> grant all privileges on *.* to 'root'@'192.168.127.%' identified by '123456';

mysql>flush privileges;

 

 

(4)在slave01(192.168.127.102)上創建復制用戶:

mysql> grant replication slave on *.* to 'repl'@'192.168.127.%' identified by 'repl';

mysql>flush privileges;

 

 

2.安裝部署MHA

2.1安裝MHA node(在所有Mysql服務器上安裝)

(1)安裝依賴包

rpm -Uvh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6

yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Config-IniFiles perl-Time-HiRes perl-Time-HiRes perl-CPAN

 

(2)在所有的節點上安裝mha node:

tar zxvf mha4mysql-node-0.56.tar.gz

cd mha4mysql-node-0.56

perl Makefile.PL

make

make install

 

2.2.安裝MHA Manager

MHA Manager中主要包括了幾個管理員的命令行工具,例如masterha_manager,masterha_master_switch等。

(1) 安裝依賴包

rpm -Uvh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6

yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Config-IniFiles perl-Time-HiRes perl-Time-HiRes perl-CPAN

 

(2) 安裝MHA node軟件包。注意,在MHA Manger的主機上也要安裝MHA node.

tar zxvf mha4mysql-node-0.56.tar.gz

cd mha4mysql-node-0.56

perl Makefile.PL

make

make install

 

(3) 安裝MHA Manager軟件包。

tar zxvf mha4mysql-manager-0.56.tar.gz

cd mha4mysql-manager-0.56

perl Makefile.PL

make

make install

 

2.3. 配置SSH 登錄無密碼驗證

(1) 在manager (192.168.127.100)上配置到所有節點的無密碼驗證

ssh-keygen -t rsa

ssh-copy-id -i ~/.ssh/id_rsa.pub root@MHA

ssh-copy-id -i ~/.ssh/id_rsa.pub root@master

ssh-copy-id -i ~/.ssh/id_rsa.pub root@slave01

ssh-copy-id -i ~/.ssh/id_rsa.pub root@slave02

(2) 在MHA Node master(192.168.127.101)上:

ssh-keygen -t rsa

ssh-copy-id -i ~/.ssh/id_rsa.pub root@MHA

ssh-copy-id -i ~/.ssh/id_rsa.pub root@master

ssh-copy-id -i ~/.ssh/id_rsa.pub root@slave01

ssh-copy-id -i ~/.ssh/id_rsa.pub root@slave02

(3) 在MHA Node slave01(192.168.127.102)上:

ssh-keygen -t rsa

ssh-copy-id -i ~/.ssh/id_rsa.pub root@MHA

ssh-copy-id -i ~/.ssh/id_rsa.pub root@master

ssh-copy-id -i ~/.ssh/id_rsa.pub root@slave01

ssh-copy-id -i ~/.ssh/id_rsa.pub root@slave02

(4) 在MHA Node slave02(192.168.127.103)上:

ssh-keygen -t rsa

ssh-copy-id -i ~/.ssh/id_rsa.pub root@MHA

ssh-copy-id -i ~/.ssh/id_rsa.pub root@master

ssh-copy-id -i ~/.ssh/id_rsa.pub root@slave01

ssh-copy-id -i ~/.ssh/id_rsa.pub root@slave02

 

在每台做以下步驟

ln -s /app/mysql5.6/bin/* /usr/local/bin/

把腳本拷貝相關目錄

[root@MHA ~]# cp /root/mha4mysql-manager-0.56/samples/scripts/master_ip_failover /usr/local/bin/

 

[root@MHA ~]# cp /root/mha4mysql-manager-0.56/samples/scripts/master_ip_online_change /usr/local/bin/

 

[root@MHA~]#cp /root/mha4mysql-manager-0.56/samples/scripts/send_report /usr/local/bin/

 

[root@MHA ~]# cp /root/mha4mysql-manager-0.56/bin/masterha_secondary_check /usr/bin/

 

3.配置MHA

配置MHA的步驟如下。

(1) 創建MHA工作目錄,並且創建相關配置文件:

mkdir -p /etc/masterha

mkdir -p /masterha/app1

配置如下

vi /etc/masterha/app1.cnf

[server default]

manager_workdir=/masterha/app1

manager_log=/masterha/app1/app1.log

master_ip_failover_script=/usr/local/bin/master_ip_failover

master_ip_online_change_script=/usr/local/bin/master_ip_online_change

 

user=root

password=123456

ssh_user=root

repl_user=repl

repl_password=repl

ping_interval=1

remote_workdir=/tmp

report_script=/usr/local/bin/send_report

secondary_check_script=/usr/bin/masterha_secondary_check -s MHA -s slave02 --user=root --master_host=master --master_ip=192.168.127.101 --master_port=3306 --password=123456

shutdown_script=""

report_script=""

 

 

[server1]

hostname=192.168.127.101

master_binlog_dir=/data/mysql3306

candidate_master=1

[server2]

hostname=192.168.127.102

master_binlog_dir=/data/mysql3306

candidate_master=1

check_repl_delay=0

 

[server3]

hostname=192.168.127.103

master_binlog_dir=/data/mysql3306

no_master=1

 

4.檢查SSH的配置

檢查MHA Manager到所有MHA node的SSH連接狀態:

[root@MHA ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnf

Wed Mar 2 19:03:30 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Wed Mar 2 19:03:30 2016 - [info] Reading application default configuration from /etc/masterha/app1.cnf..

Wed Mar 2 19:03:30 2016 - [info] Reading server configuration from /etc/masterha/app1.cnf..

Wed Mar 2 19:03:30 2016 - [info] Starting SSH connection tests..

Wed Mar 2 19:03:31 2016 - [debug]

Wed Mar 2 19:03:30 2016 - [debug] Connecting via SSH from [email protected](192.168.127.101:22) to [email protected](192.168.127.102:22)..

Wed Mar 2 19:03:30 2016 - [debug] ok.

Wed Mar 2 19:03:30 2016 - [debug] Connecting via SSH from [email protected](192.168.127.101:22) to [email protected](192.168.127.103:22)..

Wed Mar 2 19:03:30 2016 - [debug] ok.

Wed Mar 2 19:03:31 2016 - [debug]

Wed Mar 2 19:03:30 2016 - [debug] Connecting via SSH from [email protected](192.168.127.102:22) to [email protected](192.168.127.101:22)..

Wed Mar 2 19:03:31 2016 - [debug] ok.

Wed Mar 2 19:03:31 2016 - [debug] Connecting via SSH from [email protected](192.168.127.102:22) to [email protected](192.168.127.103:22)..

Wed Mar 2 19:03:31 2016 - [debug] ok.

Wed Mar 2 19:03:32 2016 - [debug]

Wed Mar 2 19:03:31 2016 - [debug] Connecting via SSH from [email protected](192.168.127.103:22) to [email protected](192.168.127.101:22)..

Wed Mar 2 19:03:31 2016 - [debug] ok.

Wed Mar 2 19:03:31 2016 - [debug] Connecting via SSH from [email protected](192.168.127.103:22) to [email protected](192.168.127.102:22)..

Wed Mar 2 19:03:32 2016 - [debug] ok.

Wed Mar 2 19:03:32 2016 - [info] All SSH connection tests passed successfully.

5.檢查整個復制環境

[root@MHA ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnf

Wed Mar 2 19:03:30 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Wed Mar 2 19:03:30 2016 - [info] Reading application default configuration from /etc/masterha/app1.cnf..

Wed Mar 2 19:03:30 2016 - [info] Reading server configuration from /etc/masterha/app1.cnf..

Wed Mar 2 19:03:30 2016 - [info] Starting SSH connection tests..

Wed Mar 2 19:03:31 2016 - [debug]

Wed Mar 2 19:03:30 2016 - [debug] Connecting via SSH from [email protected](192.168.127.101:22) to [email protected](192.168.127.102:22)..

Wed Mar 2 19:03:30 2016 - [debug] ok.

Wed Mar 2 19:03:30 2016 - [debug] Connecting via SSH from [email protected](192.168.127.101:22) to [email protected](192.168.127.103:22)..

Wed Mar 2 19:03:30 2016 - [debug] ok.

Wed Mar 2 19:03:31 2016 - [debug]

Wed Mar 2 19:03:30 2016 - [debug] Connecting via SSH from [email protected](192.168.127.102:22) to [email protected](192.168.127.101:22)..

Wed Mar 2 19:03:31 2016 - [debug] ok.

Wed Mar 2 19:03:31 2016 - [debug] Connecting via SSH from [email protected](192.168.127.102:22) to [email protected](192.168.127.103:22)..

Wed Mar 2 19:03:31 2016 - [debug] ok.

Wed Mar 2 19:03:32 2016 - [debug]

Wed Mar 2 19:03:31 2016 - [debug] Connecting via SSH from [email protected](192.168.127.103:22) to [email protected](192.168.127.101:22)..

Wed Mar 2 19:03:31 2016 - [debug] ok.

Wed Mar 2 19:03:31 2016 - [debug] Connecting via SSH from [email protected](192.168.127.103:22) to [email protected](192.168.127.102:22)..

Wed Mar 2 19:03:32 2016 - [debug] ok.

Wed Mar 2 19:03:32 2016 - [info] All SSH connection tests passed successfully.

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

Wed Mar 2 19:04:12 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Wed Mar 2 19:04:12 2016 - [info] Reading application default configuration from /etc/masterha/app1.cnf..

Wed Mar 2 19:04:12 2016 - [info] Reading server configuration from /etc/masterha/app1.cnf..

Wed Mar 2 19:04:12 2016 - [info] MHA::MasterMonitor version 0.56.

Wed Mar 2 19:04:12 2016 - [info] GTID failover mode = 0

Wed Mar 2 19:04:12 2016 - [info] Dead Servers:

Wed Mar 2 19:04:12 2016 - [info] Alive Servers:

Wed Mar 2 19:04:12 2016 - [info] 192.168.127.101(192.168.127.101:3306)

Wed Mar 2 19:04:12 2016 - [info] 192.168.127.102(192.168.127.102:3306)

Wed Mar 2 19:04:12 2016 - [info] 192.168.127.103(192.168.127.103:3306)

Wed Mar 2 19:04:12 2016 - [info] Alive Slaves:

Wed Mar 2 19:04:12 2016 - [info] 192.168.127.102(192.168.127.102:3306) Version=5.6.27-75.0-log (oldest major version between slaves) log-bin:enabled

Wed Mar 2 19:04:12 2016 - [info] Replicating from 192.168.127.101(192.168.127.101:3306)

Wed Mar 2 19:04:12 2016 - [info] Primary candidate for the new Master (candidate_master is set)

Wed Mar 2 19:04:12 2016 - [info] 192.168.127.103(192.168.127.103:3306) Version=5.6.27-75.0-log (oldest major version between slaves) log-bin:enabled

Wed Mar 2 19:04:12 2016 - [info] Replicating from 192.168.127.101(192.168.127.101:3306)

Wed Mar 2 19:04:12 2016 - [info] Not candidate for the new Master (no_master is set)

Wed Mar 2 19:04:12 2016 - [info] Current Alive Master: 192.168.127.101(192.168.127.101:3306)

Wed Mar 2 19:04:12 2016 - [info] Checking slave configurations..

Wed Mar 2 19:04:12 2016 - [info] Checking replication filtering settings..

Wed Mar 2 19:04:12 2016 - [info] binlog_do_db= , binlog_ignore_db=

Wed Mar 2 19:04:12 2016 - [info] Replication filtering check ok.

Wed Mar 2 19:04:12 2016 - [info] GTID (with auto-pos) is not supported

Wed Mar 2 19:04:12 2016 - [info] Starting SSH connection tests..

Wed Mar 2 19:04:14 2016 - [info] All SSH connection tests passed successfully.

Wed Mar 2 19:04:14 2016 - [info] Checking MHA Node version..

Wed Mar 2 19:04:15 2016 - [info] Version check ok.

Wed Mar 2 19:04:15 2016 - [info] Checking SSH publickey authentication settings on the current master..

Wed Mar 2 19:04:15 2016 - [info] HealthCheck: SSH to 192.168.127.101 is reachable.

Wed Mar 2 19:04:15 2016 - [info] Master MHA Node version is 0.56.

Wed Mar 2 19:04:15 2016 - [info] Checking recovery script configurations on 192.168.127.101(192.168.127.101:3306)..

Wed Mar 2 19:04:15 2016 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql3306 --output_file=/tmp/save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000004

Wed Mar 2 19:04:15 2016 - [info] Connecting to [email protected](192.168.127.101:22)..

Creating /tmp if not exists.. ok.

Checking output directory is accessible or not..

ok.

Binlog found at /data/mysql3306, up to mysql-bin.000004

Wed Mar 2 19:04:15 2016 - [info] Binlog setting check done.

Wed Mar 2 19:04:15 2016 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..

Wed Mar 2 19:04:15 2016 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.127.102 --slave_ip=192.168.127.102 --slave_port=3306 --workdir=/tmp --target_version=5.6.27-75.0-log --manager_version=0.56 --relay_log_info=/data/mysql3306/relay-log.info --relay_dir=/data/mysql3306/ --slave_pass=xxx

Wed Mar 2 19:04:15 2016 - [info] Connecting to [email protected](192.168.127.102:22)..

Checking slave recovery environment settings..

Opening /data/mysql3306/relay-log.info ... ok.

Relay log found at /data/mysql3306, up to mysqld-relay-bin.000002

Temporary relay log file is /data/mysql3306/mysqld-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.

Wed Mar 2 19:04:16 2016 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.127.103 --slave_ip=192.168.127.103 --slave_port=3306 --workdir=/tmp --target_version=5.6.27-75.0-log --manager_version=0.56 --relay_log_info=/data/mysql3306/relay-log.info --relay_dir=/data/mysql3306/ --slave_pass=xxx

Wed Mar 2 19:04:16 2016 - [info] Connecting to [email protected](192.168.127.103:22)..

Checking slave recovery environment settings..

Opening /data/mysql3306/relay-log.info ... ok.

Relay log found at /data/mysql3306, up to mysqld-relay-bin.000002

Temporary relay log file is /data/mysql3306/mysqld-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.

Wed Mar 2 19:04:16 2016 - [info] Slaves settings check done.

Wed Mar 2 19:04:16 2016 - [info]

 

+--192.168.127.102(192.168.127.102:3306)

+--192.168.127.103(192.168.127.103:3306)

 

Wed Mar 2 19:04:16 2016 - [info] Checking replication health on 192.168.127.102..

Wed Mar 2 19:04:16 2016 - [info] ok.

Wed Mar 2 19:04:16 2016 - [info] Checking replication health on 192.168.127.103..

Wed Mar 2 19:04:16 2016 - [info] ok.

Wed Mar 2 19:04:16 2016 - [info] Checking master_ip_failover_script status:

Wed Mar 2 19:04:16 2016 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.127.101 --orig_master_ip=192.168.127.101 --orig_master_port=3306

Bareword "FIXME_xxx" not allowed while "strict subs" in use at /usr/local/bin/master_ip_failover line 93.

Execution of /usr/local/bin/master_ip_failover aborted due to compilation errors.

Wed Mar 2 19:04:16 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln226] Failed to get master_ip_failover_script status with return code 255:0.

Wed Mar 2 19:04:16 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. at /usr/local/bin/masterha_check_repl line 48

Wed Mar 2 19:04:16 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.

Wed Mar 2 19:04:16 2016 - [info] Got exit code 1 (Not master dead).

 

MySQL Replication Health is NOT OK!

說明以上沒有成功需要修改以上的問題

把93行#FIXME_xxx; 注釋掉

 

 

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

Wed Mar 2 19:04:52 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Wed Mar 2 19:04:52 2016 - [info] Reading application default configuration from /etc/masterha/app1.cnf..

Wed Mar 2 19:04:52 2016 - [info] Reading server configuration from /etc/masterha/app1.cnf..

Wed Mar 2 19:04:52 2016 - [info] MHA::MasterMonitor version 0.56.

Wed Mar 2 19:04:52 2016 - [info] GTID failover mode = 0

Wed Mar 2 19:04:52 2016 - [info] Dead Servers:

Wed Mar 2 19:04:52 2016 - [info] Alive Servers:

Wed Mar 2 19:04:52 2016 - [info] 192.168.127.101(192.168.127.101:3306)

Wed Mar 2 19:04:52 2016 - [info] 192.168.127.102(192.168.127.102:3306)

Wed Mar 2 19:04:52 2016 - [info] 192.168.127.103(192.168.127.103:3306)

Wed Mar 2 19:04:52 2016 - [info] Alive Slaves:

Wed Mar 2 19:04:52 2016 - [info] 192.168.127.102(192.168.127.102:3306) Version=5.6.27-75.0-log (oldest major version between slaves) log-bin:enabled

Wed Mar 2 19:04:52 2016 - [info] Replicating from 192.168.127.101(192.168.127.101:3306)

Wed Mar 2 19:04:52 2016 - [info] Primary candidate for the new Master (candidate_master is set)

Wed Mar 2 19:04:52 2016 - [info] 192.168.127.103(192.168.127.103:3306) Version=5.6.27-75.0-log (oldest major version between slaves) log-bin:enabled

Wed Mar 2 19:04:52 2016 - [info] Replicating from 192.168.127.101(192.168.127.101:3306)

Wed Mar 2 19:04:52 2016 - [info] Not candidate for the new Master (no_master is set)

Wed Mar 2 19:04:52 2016 - [info] Current Alive Master: 192.168.127.101(192.168.127.101:3306)

Wed Mar 2 19:04:52 2016 - [info] Checking slave configurations..

Wed Mar 2 19:04:52 2016 - [info] Checking replication filtering settings..

Wed Mar 2 19:04:52 2016 - [info] binlog_do_db= , binlog_ignore_db=

Wed Mar 2 19:04:52 2016 - [info] Replication filtering check ok.

Wed Mar 2 19:04:52 2016 - [info] GTID (with auto-pos) is not supported

Wed Mar 2 19:04:52 2016 - [info] Starting SSH connection tests..

Wed Mar 2 19:04:54 2016 - [info] All SSH connection tests passed successfully.

Wed Mar 2 19:04:54 2016 - [info] Checking MHA Node version..

Wed Mar 2 19:04:54 2016 - [info] Version check ok.

Wed Mar 2 19:04:54 2016 - [info] Checking SSH publickey authentication settings on the current master..

Wed Mar 2 19:04:54 2016 - [info] HealthCheck: SSH to 192.168.127.101 is reachable.

Wed Mar 2 19:04:55 2016 - [info] Master MHA Node version is 0.56.

Wed Mar 2 19:04:55 2016 - [info] Checking recovery script configurations on 192.168.127.101(192.168.127.101:3306)..

Wed Mar 2 19:04:55 2016 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql3306 --output_file=/tmp/save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000004

Wed Mar 2 19:04:55 2016 - [info] Connecting to [email protected](192.168.127.101:22)..

Creating /tmp if not exists.. ok.

Checking output directory is accessible or not..

ok.

Binlog found at /data/mysql3306, up to mysql-bin.000004

Wed Mar 2 19:04:55 2016 - [info] Binlog setting check done.

Wed Mar 2 19:04:55 2016 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..

Wed Mar 2 19:04:55 2016 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.127.102 --slave_ip=192.168.127.102 --slave_port=3306 --workdir=/tmp --target_version=5.6.27-75.0-log --manager_version=0.56 --relay_log_info=/data/mysql3306/relay-log.info --relay_dir=/data/mysql3306/ --slave_pass=xxx

Wed Mar 2 19:04:55 2016 - [info] Connecting to [email protected](192.168.127.102:22)..

Checking slave recovery environment settings..

Opening /data/mysql3306/relay-log.info ... ok.

Relay log found at /data/mysql3306, up to mysqld-relay-bin.000002

Temporary relay log file is /data/mysql3306/mysqld-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.

Wed Mar 2 19:04:55 2016 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.127.103 --slave_ip=192.168.127.103 --slave_port=3306 --workdir=/tmp --target_version=5.6.27-75.0-log --manager_version=0.56 --relay_log_info=/data/mysql3306/relay-log.info --relay_dir=/data/mysql3306/ --slave_pass=xxx

Wed Mar 2 19:04:55 2016 - [info] Connecting to [email protected](192.168.127.103:22)..

Checking slave recovery environment settings..

Opening /data/mysql3306/relay-log.info ... ok.

Relay log found at /data/mysql3306, up to mysqld-relay-bin.000002

Temporary relay log file is /data/mysql3306/mysqld-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.

Wed Mar 2 19:04:55 2016 - [info] Slaves settings check done.

Wed Mar 2 19:04:55 2016 - [info]

  1.  

    +--192.168.127.102(192.168.127.102:3306)

    +--192.168.127.103(192.168.127.103:3306)

     

    Wed Mar 2 19:04:55 2016 - [info] Checking replication health on 192.168.127.102..

    Wed Mar 2 19:04:55 2016 - [info] ok.

    Wed Mar 2 19:04:55 2016 - [info] Checking replication health on 192.168.127.103..

    Wed Mar 2 19:04:55 2016 - [info] ok.

    Wed Mar 2 19:04:55 2016 - [info] Checking master_ip_failover_script status:

    Wed Mar 2 19:04:55 2016 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.127.101 --orig_master_ip=192.168.127.101 --orig_master_port=3306

    Wed Mar 2 19:04:55 2016 - [info] OK.

    Wed Mar 2 19:04:55 2016 - [warning] shutdown_script is not defined.

    Wed Mar 2 19:04:55 2016 - [info] Got exit code 0 (Not master dead).

     

    MySQL Replication Health is OK.

     

    說明成功

     

    6.通過腳本管理 VIP

    修改master_ip_failover文件(/usr/local/bin)

     

    #!/usr/bin/env perl

     

    # Copyright (C) 2011 DeNA Co.,Ltd.

    #

    # This program is free software; you can redistribute it and/or modify

    # it under the terms of the GNU General Public License as published by

    # the Free Software Foundation; either version 2 of the License, or

    # (at your option) any later version.

    #

    # This program is distributed in the hope that it will be useful,

    # but WITHOUT ANY WARRANTY; without even the implied warranty of

    # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

    # GNU General Public License for more details.

    #

    # You should have received a copy of the GNU General Public License

    # along with this program; if not, write to the Free Software

    # Foundation, Inc.,

    # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA

     

    ## Note: This is a sample script and is not complete. Modify the script based on your environment.

     

    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.127.202/24';

    my $key="2";

    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" ) {

     

    # $orig_master_host, $orig_master_ip, $orig_master_port are passed.

    # If you manage master ip address at global catalog database,

    # invalidate orig_master_ip here.

    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" ) {

     

    # all arguments are passed.

    # If you manage master ip address at global catalog database,

    # activate new_master_ip here.

    # You can also grant write access (create user, set read_only=0, etc) here.

    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 $@;

     

    # If you want to continue failover, exit 10.

    exit $exit_code;

    }

    exit $exit_code;

    }

    elsif ( $command eq "status" ) {

    print "Checking the Status of the script.. ok \n";

    # do nothing

    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";

    }

     

    注意:首先啟動VIP在192.168.127.101(master)上

    /sbin/ifconfig eth0:2 192.168.127.202/24

    7.開啟MHA Manager監控

    nohup masterha_manager --conf=/etc/masterha/app1.cnf > /masterha/app1/manager.log &1 &

    8.查看啟動狀態

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

    app1 (pid:27237) is running(0:PING_OK), master:192.168.127.101

     

    9. 查看啟動日志

    [root@MHA ~]# tail -f /masterha/app1/app1.log

    +--192.168.127.103(192.168.127.103:3306)

     

    Wed Mar 2 19:08:34 2016 - [info] Checking master_ip_failover_script status:

    Wed Mar 2 19:08:34 2016 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.127.101 --orig_master_ip=192.168.127.101 --orig_master_port=3306

    Wed Mar 2 19:08:34 2016 - [info] OK.

    Wed Mar 2 19:08:34 2016 - [warning] shutdown_script is not defined.

    Wed Mar 2 19:08:34 2016 - [info] Set master ping interval 1 seconds.

    Wed Mar 2 19:08:34 2016 - [info] Set secondary check script: /usr/bin/masterha_secondary_check -s MHA -s slave02 --user=root --master_host=master --master_ip=192.168.127.101 --master_port=3306 --password=123456

    Wed Mar 2 19:08:34 2016 - [info] Starting ping health check on 192.168.127.101(192.168.127.101:3306)..

    Wed Mar 2 19:08:34 2016 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..

     

    查看VIP

    [root@master ~]# ip addr

    1: lo: mtu 16436 qdisc noqueue state UNKNOWN

    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

    inet 127.0.0.1/8 scope host lo

    inet6 ::1/128 scope host

    valid_lft forever preferred_lft forever

    2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000

    link/ether 00:0c:29:08:25:3f brd ff:ff:ff:ff:ff:ff

    inet 192.168.127.101/24 brd 192.168.127.255 scope global eth0

    inet 192.168.127.202/24 brd 192.168.127.255 scope global secondary eth0:2

    inet6 fe80::20c:29ff:fe08:253f/64 scope link

    valid_lft forever preferred_lft forever

    3: pan0: mtu 1500 qdisc noop state DOWN

    link/ether 0e:ed:39:ba:c1:1b brd ff:ff:ff:ff:ff:ff

     

     

    10.測試切換

    測試關閉主庫

    [root@master ~]# /etc/init.d/mysql stop

    Shutting down MySQL (Percona Server)...... [ OK ]

    查看slave02復制狀態:

    [root@slave02 ~]# mysql

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

    Your MySQL connection id is 27

    Server version: 5.6.27-75.0-log Percona Server (GPL), Release 75.0, Revision 8bb53b6

     

    Copyright (c) 2009-2015 Percona LLC and/or its affiliates

    Copyright (c) 2000, 2015, 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.

     

    root@(none) 07:42:08>show slave status\G;

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

    Slave_IO_State: Waiting for master to send event

    Master_Host: 192.168.127.102 #已經自動切換了

    Master_User: repl

    Master_Port: 3306

    Connect_Retry: 60

    Master_Log_File: mysql-bin.000003

    Read_Master_Log_Pos: 981

    Relay_Log_File: mysqld-relay-bin.000002

    Relay_Log_Pos: 283

    Relay_Master_Log_File: mysql-bin.000003

    Slave_IO_Running: Yes

    Slave_SQL_Running: Yes

    Replicate_Do_DB:

    Replicate_Ignore_DB:

    Replicate_Do_Table:

    Replicate_Ignore_Table:

    Replicate_Wild_Do_Table:

    Replicate_Wild_Ignore_Table:

    Last_Errno: 0

    Last_Error:

    Skip_Counter: 0

    Exec_Master_Log_Pos: 981

    Relay_Log_Space: 457

    Until_Condition: None

    Until_Log_File:

    Until_Log_Pos: 0

    Master_SSL_Allowed: No

    Master_SSL_CA_File:

    Master_SSL_CA_Path:

    Master_SSL_Cert:

    Master_SSL_Cipher:

    Master_SSL_Key:

    Seconds_Behind_Master: 0

    Master_SSL_Verify_Server_Cert: No

    Last_IO_Errno: 0

    Last_IO_Error:

    Last_SQL_Errno: 0

    Last_SQL_Error:

    Replicate_Ignore_Server_Ids:

    Master_Server_Id: 102

    Master_UUID: 1bb38a96-e066-11e5-84cb-000c2976ee35

    Master_Info_File: /data/mysql3306/master.info

    SQL_Delay: 0

    SQL_Remaining_Delay: NULL

    Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

    Master_Retry_Count: 86400

    Master_Bind:

    Last_IO_Error_Timestamp:

    Last_SQL_Error_Timestamp:

    Master_SSL_Crl:

    Master_SSL_Crlpath:

    Retrieved_Gtid_Set:

    Executed_Gtid_Set:

    Auto_Position: 0

    1 row in set (0.00 sec)

     

    ERROR:

    No query specified

    查看VIP漂移slave01(192.168.247.102)上

    [root@slave01 ~]# ip addr

    1: lo: mtu 16436 qdisc noqueue state UNKNOWN

    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

    inet 127.0.0.1/8 scope host lo

    inet6 ::1/128 scope host

    valid_lft forever preferred_lft forever

    2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000

    link/ether 00:0c:29:76:ee:35 brd ff:ff:ff:ff:ff:ff

    inet 192.168.127.102/24 brd 192.168.127.255 scope global eth0

    inet 192.168.127.202/24 brd 192.168.127.255 scope global secondary eth0:2

    inet6 fe80::20c:29ff:fe76:ee35/64 scope link

    valid_lft forever preferred_lft forever

    3: pan0: mtu 1500 qdisc noop state DOWN

    link/ether 1e:77:57:63:5e:b0 brd ff:ff:ff:ff:ff:ff

     

    10. 修改宕機的Master

    通常情況自動切換後,原master 可能已經廢棄掉,待原master 主機修改很復後,如果數據完整的情況,可能想把原master重新作為新主庫的slave,這是我們就需要借助當時自動切換時刻的MHA日志來完成對原master的修復。下面是提取相關日志的命令:

     

    [root@MHA ~]# grep -i 'change' /masterha/app1/app1.log

    Wed Mar 2 19:09:23 2016 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.127.102', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=981, MASTER_USER='repl', MASTER_PASSWORD='xxx';

    Wed Mar 2 19:09:23 2016 - [info] Executed CHANGE MASTER.

    11. 修復master變成從庫

    在master(192.168.127.101)操作如下:

    [root@master ~]# /etc/init.d/mysql start

    Starting MySQL (Percona Server).. [ OK ]

    [root@master ~]# mysql

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

    Your MySQL connection id is 1

    Server version: 5.6.27-75.0-log Percona Server (GPL), Release 75.0, Revision 8bb53b6

     

    Copyright (c) 2009-2015 Percona LLC and/or its affiliates

    Copyright (c) 2000, 2015, 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.

     

    root@(none) 07:26:45>CHANGE MASTER TO MASTER_HOST='192.168.127.102', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=981, MASTER_USER='repl', MASTER_PASSWORD='repl';

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

     

    root@(none) 07:26:47>start slave;

    Query OK, 0 rows affected (0.02 sec)

    root@(none) 07:26:49>show slave status\G;

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

    Slave_IO_State: Waiting for master to send event

    Master_Host: 192.168.127.102

    Master_User: repl

    Master_Port: 3306

    Connect_Retry: 60

    Master_Log_File: mysql-bin.000003

    Read_Master_Log_Pos: 981

    Relay_Log_File: mysqld-relay-bin.000002

    Relay_Log_Pos: 283

    Relay_Master_Log_File: mysql-bin.000003

    Slave_IO_Running: Yes

    Slave_SQL_Running: Yes

    Replicate_Do_DB:

    Replicate_Ignore_DB:

    Replicate_Do_Table:

    Replicate_Ignore_Table:

    Replicate_Wild_Do_Table:

    Replicate_Wild_Ignore_Table:

    Last_Errno: 0

    Last_Error:

    Skip_Counter: 0

    Exec_Master_Log_Pos: 981

    Relay_Log_Space: 457

    Until_Condition: None

    Until_Log_File:

    Until_Log_Pos: 0

    Master_SSL_Allowed: No

    Master_SSL_CA_File:

    Master_SSL_CA_Path:

    Master_SSL_Cert:

    Master_SSL_Cipher:

    Master_SSL_Key:

    Seconds_Behind_Master: 0

    Master_SSL_Verify_Server_Cert: No

    Last_IO_Errno: 0

    Last_IO_Error:

    Last_SQL_Errno: 0

    Last_SQL_Error:

    Replicate_Ignore_Server_Ids:

    Master_Server_Id: 102

    Master_UUID: 1bb38a96-e066-11e5-84cb-000c2976ee35

    Master_Info_File: /data/mysql3306/master.info

    SQL_Delay: 0

    SQL_Remaining_Delay: NULL

    Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

    Master_Retry_Count: 86400

    Master_Bind:

    Last_IO_Error_Timestamp:

    Last_SQL_Error_Timestamp:

    Master_SSL_Crl:

    Master_SSL_Crlpath:

    Retrieved_Gtid_Set:

    Executed_Gtid_Set:

    Auto_Position: 0

    1 row in set (0.00 sec)

     

    ERROR:

    No query specified

    12. 開啟新的MHA Manager監控

    [root@MHA ~]# cd /etc/masterha/

    [root@MHA masterha]# cp app1.cnf app2.cnf

    修改配置如下,注意:紅色是修改地方

    [root@MHA masterha]# vi app2.cnf

    [server default]

    manager_workdir=/masterha/app1

    manager_log=/masterha/app1/app1.log

    master_ip_failover_script=/usr/local/bin/master_ip_failover

    master_ip_online_change_script=/usr/local/bin/master_ip_online_change

     

    user=root

    password=123456

    ssh_user=root

    repl_user=repl

    repl_password=repl

    ping_interval=1

    remote_workdir=/tmp

    report_script=/usr/local/bin/send_report

    secondary_check_script=/usr/bin/masterha_secondary_check -s master -s slave01 --user=root --master_host=slave01 --master_ip=192.168.127.102 --master_port=3306 --password=123456

    shutdown_script=""

    report_script=""

     

     

    [server1]

    hostname=192.168.127.102

    master_binlog_dir=/data/mysql3306

    candidate_master=1

    [server2]

    hostname=192.168.127.101

    master_binlog_dir=/data/mysql3306

    candidate_master=1

    check_repl_delay=0

     

    [server3]

    hostname=192.168.127.103

    master_binlog_dir=/data/mysql3306

    no_master=1

     

    查看

    [root@MHA masterha]# masterha_check_status --conf=/etc/masterha/app1.cnf

    app1 is stopped(2:NOT_RUNNING).

    啟動新的MHA監控

    [root@MHA masterha]# nohup masterha_manager --conf=/etc/masterha/app2.cnf > /masterha/app1/manager.log &1 &

    [1] 2089

    查看啟動狀態

    [root@MHA masterha]# masterha_check_status --conf=/etc/masterha/app2.cnf

    app2 (pid:2089) is running(0:PING_OK), master:192.168.127.102

     

    以上測試成功,為了保證穩定,反復測試一下。

     

    13.MHA+半同步復制

    為了保證數據一致性采用半同步復制

    (1)Master(192.168.127.101),slave01(192.168.127.102)操作如下:

    執行安裝相關插入件啟動半同步復制

    INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

    SET GLOBAL rpl_semi_sync_master_enabled=1;

    SET GLOBAL rpl_semi_sync_master_timeout=10000;

    切換時也可能當作從庫,所以也操作如下步驟

    INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

    SET GLOBAL rpl_semi_sync_slave_enabled=1;

     

    在配置文件my.cnf增加以下參數

    #############半同步###########

    rpl_semi_sync_master_enabled=1

    rpl_semi_sync_master_timeout=1000

    rpl_semi_sync_master_trace_level=32

    rpl_semi_sync_master_wait_no_slave=on

     

    rpl_semi_sync_slave_enabled=1

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

     

    (2) 所以的從都操作如下:

    Slave02(192.168.127.102)的操作

    執行安裝相關插入件啟動半同步復制

    INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

    SET GLOBAL rpl_semi_sync_slave_enabled=1;

    在配置文件my.cnf增加以下參數

    #############半同步###########

    rpl_semi_sync_slave_enabled=1

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

     

    以上配置成功,不需要重啟

     

    查看主庫的半同步

    root@(none) 11:36:36>show variables like 'rpl%';

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

    | Variable_name | Value |

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

    | rpl_semi_sync_master_enabled | ON |

    | rpl_semi_sync_master_timeout | 10000 |

    | rpl_semi_sync_master_trace_level | 32 |

    | rpl_semi_sync_master_wait_no_slave | ON |

    | rpl_semi_sync_slave_enabled | ON |

    | rpl_semi_sync_slave_trace_level | 32 |

    | rpl_stop_slave_timeout | 31536000 |

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

    7 rows in set (0.01 sec)

     

    查看從庫的半同步

     

    root@(none) 11:36:36>show variables like 'rpl%';

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

    | Variable_name | Value |

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

    | rpl_semi_sync_slave_enabled | ON |

    | rpl_semi_sync_slave_trace_level | 32 |

    | rpl_stop_slave_timeout | 31536000 |

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

    3 rows in set (0.01 sec)

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved