MySQL 5.7 MHA 最佳实践

MHA 集群架构

参考文档：
MHA原理：https://code.google.com/p/mysql-master-ha/wiki/HowMHAWorks
MHA原理PPT：http://www.slideshare.net/matsunobu/automated-master-failover
Linux配置代理方法：http://blog.csdn.net/bojie5744/article/details/42148719


软件下载：
Centos Base Yum Repository： http://mirrors.163.com/.help/CentOS6-Base-163.repo
epel(RHEL 6)Yum Repository：http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
MySQL5.7 Yum Repository：https://dev.mysql.com/get/mysql57-community-release-el6-11.noarch.rpm
mysql-master-ha(mgr)：https://github.com/linyue515/mysql-master-ha/raw/master/mha4mysql-manager-0.57-0.el7.noarch.rpm
mysql-master-ha(node)：https://github.com/linyue515/mysql-master-ha/raw/master/mha4mysql-node-0.57-0.el7.noarch.rpm

系统版本
CentOS release 6.7 (Final) x86_64
MySQL版本
mysql-5.7.20.-x86_64(RPM)
MHA版本
mha4mysql-manager-0.57
mha4mysql-node-0.57
---前期准备工作 -- 
[root@node1-slave2 mysql]# cat /etc/hosts 
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.137.140 mha-manager
192.168.137.141 node1-master
192.168.137.142 node1-slave1
192.168.137.143 node1-slave2
192.168.137.144 lvs-master
192.168.137.145 lvs-backup


mha-manager  节点上传相关软件 


[root@mha-manager opt]# ls -al 
total 156
drwxr-xr-x.  3 root root  4096 Dec 25 21:24 .
dr-xr-xr-x. 25 root root  4096 Dec 25 20:50 ..
-rw-r--r--   1 root root 81080 Dec 25 21:22 mha4mysql-manager-0.57-0.el7.noarch.rpm
-rw-r--r--   1 root root 35360 Dec 25 21:22 mha4mysql-node-0.57-0.el7.noarch.rpm
-rw-r--r--   1 root root 25664 Dec 25 21:22 mysql57-community-release-el6-11.noarch.rpm
drwxr-xr-x.  2 root root  4096 Mar 26  2015 rh






scp -rp mha4mysql-* mysql57-community-release-el6-11.noarch.rpm  node1-slave1:/opt/
scp -rp mha4mysql-* mysql57-community-release-el6-11.noarch.rpm  node1-slave2:/opt/
scp -rp mha4mysql-* mysql57-community-release-el6-11.noarch.rpm  node1-master:/opt/


mha-manager/node-master/slave 安装  mysql57-community-release-el6-11.noarch.rpm   


MHA manager 安装 MySQL客服端
yum -y install  mysql-community-client.x86_64
mha-node-master/slave 安装MySQL服务端
yum -y install mysql-community-server.x86_64  




(2)  master/slave  
mkdir /etc/mysql
mkdir -p /data1/db3389
mkdir -p /data1/tmp
chown -R mysql:mysql /data1/db3389
chown -R mysql:mysql /data1/tmp
cd /etc/mysql
vim my3389.cnf 


[mysqld]
# GENERAL #
user                           = mysql
port                           = 3389
default_storage_engine         = InnoDB
socket                         = /data1/db3389/my3389.sock
pid_file                       = /data1/db3389/mysql.pid
#read-only =0
tmpdir                  = /data1/tmp
#key_buffer_size                = 128M
max_allowed_packet             = 32M
max_connect_errors             = 1000000
datadir          = /data1/db3389/
log_bin = 1371413389-bin
relay-log=  1371413389-relay-bin
expire_logs_days               = 7
#sync_binlog                    = 0
tmp_table_size                 = 32M
max_heap_table_size            = 32M
max_connections                = 5000
thread_cache_size              = 512
table_definition_cache         = 4096
table_open_cache               = 4096
wait_timeout            = 28800
interactive_timeout     = 28800
transaction-isolation = READ-COMMITTED
binlog-format=row
character-set-server=utf8
skip-name-resolve
back_log=1024
explicit_defaults_for_timestamp=true
server_id=1371403389


# INNODB #
innodb_flush_method            = O_DIRECT
#innodb_data_home_dir = /data1/db3389
innodb_data_file_path = ibdata1:100M:autoextend
#redo log
#innodb_log_group_home_dir=./
innodb_log_files_in_group      = 3
innodb_log_file_size           = 128M
#innodb performance
innodb_flush_log_at_trx_commit = 0
innodb_file_per_table          = 1
innodb_buffer_pool_instances   = 8
innodb_io_capacity             = 2000
innodb_lock_wait_timeout       = 30
binlog_error_action = ABORT_SERVER
innodb_buffer_pool_size        = 128M
innodb_max_dirty_pages_pct=90
innodb_file_format=Barracuda
innodb_support_xa=0
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
#innodb undo log
innodb_undo_tablespaces=4
innodb_undo_logs=2048
innodb_purge_rseg_truncate_frequency=512
innodb_max_undo_log_size=2G
innodb_undo_log_truncate=1


log_error                      = error.log
#log_queries_not_using_indexes = 1
slow_query_log                 = 1
slow_query_log_file            = slow-queries.log
long_query_time=2
gtid_mode=ON
enforce-gtid-consistency
log-slave-updates
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync_master_info = 10000
slave_sql_verify_checksum=1
skip-slave-start
init-connect='SET NAMES utf8'
character-set-server=utf8
skip-character-set-client-handshake
bind-address=0.0.0.0
skip-external-locking
slave-parallel-workers=6


[mysql5.6]
myisam_recover                 = FORCE,BACKUP




scp -rp my3389.cnf node1-slave1:/etc/mysql/
 
 
node1-slave1/slave2 修改一些东西即可 
log_bin = 1371413389-bin
relay-log=  1371413389-relay-bin
server_id=1371413389












mysqld --defaults-file=/etc/mysql/my3389.cnf --initialize --user=mysql  初始化数据库


mysqld_safe --defaults-file=/etc/mysql/my3389.cnf &   启动MySQL数据库


echo "mysqld_safe --defaults-file=/etc/mysql/my3389.cnf &" >> /etc/rc.local




cat /data1/db3389/error.log | grep temp
mysql -S /data1/db3389/my3389.sock -p'srbe,bLde3sp'  
mysql> set password='';  #重置密码为空
Query OK, 0 rows affected (0.01 sec)




master主库检查 GTID 
mysql> show master status ;
+-----------------------+----------+--------------+------------------+----------------------------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+-----------------------+----------+--------------+------------------+----------------------------------------+
| 1371413389-bin.000002 |      357 |              |                  | 3503c65e-e949-11e7-b2c3-000c295345bc:1 |
+-----------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)


mysql> show master status\G ;
*************************** 1. row ***************************
             File: 1371413389-bin.000002
         Position: 357
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 3503c65e-e949-11e7-b2c3-000c295345bc:1
1 row in set (0.00 sec)


ERROR: 
No query specified










master主库操作 
grant replication slave, replication client on *.* to replica@'192.168.137.%' identified by 'mycatDBA';
grant all privileges on *.* to mha@'192.168.137.140' identified by 'mysqlDBA';
flush privileges;


mysqldump -S /data1/db3389/my3389.sock --single-transaction --master-data=2 --opt -A | gzip >  /data1/tmp/full_3389.tar.gz


mysqldump -S /data1/db3389/my3389.sock --single-transaction --master-data=2 --opt -A  >  /tmp/full3389.sql 
scp -rp /tmp/full3389.sql node1-slave1:/tmp/




node1-slave1端MySQL操作 
mysql> reset master ;
Query OK, 0 rows affected (0.02 sec)
mysql -S /data1/db3389/my3389.sock < /tmp/full3389.sql


change master to master_host='192.168.137.141',master_port=3389,master_user='replica',master_password='mycatDBA',master_auto_position=1;




manager/master/slave 都需要安装的 
yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes


#根据MHA角色安装对应的软件包即可
yum -y --nogpgcheck install mha4mysql-node-0.57-0.el7.noarch.rpm  所有节点安装
yum -y install --nogpgcheck mha4mysql-manager-0.57-0.el7.noarch.rpm --manager节点安装 


master添加VIP 


/sbin/ifconfig eth0:1 192.168.137.200 broadcast 192.168.137.255 netmask 255.255.255.0
/sbin/arping -f -q -c 5 -w 5 -I eth0 -s 192.168.137.200 -U 192.168.137.1




配置SSH互信
mha-manager/node1-slave1  操作
ssh-keygen  -t rsa 
rm -rf ~/.ssh/* 




node-master操作 
ssh-keygen  -t rsa 
cd .ssh/
mv id_rsa.pub authorized_keys
scp -rp authorized_keys  id_rsa  mha-manager:~/.ssh/
scp -rp authorized_keys  id_rsa  node1-slave1:~/.ssh/


然后相互验证 
ssh node1-slave1 date 
ssh node1-slave2 date 
ssh mha-manager date








 
所有节点配置mysql用户sudo权限


cd /etc/sudoers.d/
vim mysql 添加如下内容
User_Alias  MYSQL_USERS = ALL
Runas_Alias MYSQL_RUNAS = root
Cmnd_Alias  MYSQL_CMNDS = ALL
MYSQL_USERS ALL = (MYSQL_RUNAS) NOPASSWD: MYSQL_CMNDS




manager 配置MHA 相关文件 
mkdir /etc/mha




# cat app3389.cnf 
[server default]
user=mha
password=mysqlDBA
manager_workdir=/data1/mha/masterha/app3389
manager_log=/data1/mha/masterha/app3389/app3389.log
remote_workdir=/data1/mha/masterha/app3389
ssh_user=mysql
repl_user=replica    
repl_password=mycatDBA
ping_interval=3         


secondary_check_script="masterha_secondary_check -s 192.168.137.140 -s 192.168.137.140"
master_ip_failover_script="/etc/mha/master_ip_failover.sh 192.168.137.200 1"
master_ip_online_change_script="/etc/mha/master_ip_online_change.sh 192.168.137.200 1"
shutdown_script="/etc/mha/power_manager"
#report_script="/etc/mha/end_report"


[server1]
hostname=192.168.137.141
port=3389
master_binlog_dir=/data1/db3389
candidate_master=1   
master_pid_file=/data1/db3389/mysql.pid               


[server2]
hostname=192.168.137.142
port=3389
master_binlog_dir=/data1/db3389
candidate_master=1
master_pid_file=/data1/db3389/mysql.pid    


[server3]
hostname=192.168.137.143
port=3389
master_binlog_dir=/data1/db3389
candidate_master=1
master_pid_file=/data1/db3389/mysql.pid   


[binlo