Percona-mysql MHA高可用實戰方案前言 1. 安裝部署MHA前準備 2.安裝部署MHA 3.配置MHA 4.檢查SSH的配置 5.檢查整個複制環境 6.通過腳本管理 VIP 7.開啟MHA Manager監控 8.檢視啟動狀态 9. 檢視啟動日志 10. 修改當機的Master 11. 修複master變成從庫 12. 開啟新的MHA Manager監控 13.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。整個故障轉移過程對應用程式是完全透明的。

MHA架構圖

具體搭建如表：

角色

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

注意：三台的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]

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

basedir=/app/mysql5.6

datadir=/data/mysql3306

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

注意：修改以上兩處即可

啟動資料庫

/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

注意：防火牆需要關閉

建立複制賬号（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;

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

| 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

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

（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

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

（2）安裝MHA node軟體包。注意，在MHA Manger的主機上也要安裝MHA node.

（3）安裝MHA Manager軟體包。

tar zxvf mha4mysql-manager-0.56.tar.gz

cd mha4mysql-manager-0.56

perl Makefile.PL

（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@slave02

（3）在MHA Node slave01(192.168.127.102)上：

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

（4）在MHA Node slave02(192.168.127.103)上：

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

在每台做以下步驟

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/

配置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

check_repl_delay=0

[server3]

hostname=192.168.127.103

no_master=1

檢查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] 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: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] 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] 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)..

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] 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; 注釋掉

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] 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.

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)..

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)..

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

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

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] 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.

說明成功

修改master_ip_failover檔案（/usr/local/bin）

#!/usr/bin/env perl

# 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.

# activate new_master_ip here.

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

my $exit_code = 10;

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

&start_vip();

warn $@;

# If you want to continue failover, exit 10.

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 {

"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

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

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

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

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

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: <LOOPBACK,UP,LOWER_UP> 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: <BROADCAST,MULTICAST,UP,LOWER_UP> 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

3: pan0: <BROADCAST,MULTICAST> 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

Your MySQL connection id is 27

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

Master_Host: 192.168.127.102 #已經自動切換了

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 981

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Exec_Master_Log_Pos: 981

Until_Log_Pos: 0

Master_Server_Id: 102

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

Executed_Gtid_Set:

1 row in set (0.00 sec)

檢視VIP漂移slave01(192.168.247.102)上

[root@slave01 ~]# ip addr

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

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

通常情況自動切換後，原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.

在master(192.168.127.101)操作如下：

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

Starting MySQL (Percona Server).. [ OK ]

[root@master ~]# mysql

Your MySQL connection id is 1

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;

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

Master_Host: 192.168.127.102

Auto_Position: 0

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

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

修改配置如下，注意：紅色是修改地方

[root@MHA masterha]# vi app2.cnf

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

檢視

[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 </dev/null 2>&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

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

為了保證資料一緻性采用半同步複制

（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)的操作

以上配置成功，不需要重新開機

檢視主庫的半同步

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)

檢視從庫的半同步

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

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

本文轉自 jxzhfei 51CTO部落格，原文連結:http://blog.51cto.com/jxzhfei/1747013

繼續閱讀

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

windows不能在本地計算機上運作oracleDbConsoleorcl

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

Bugku-WEB-web33

mysql使用source指令導入.sql檔案

Oracle 批量查詢傳入List 傳回List