MHA 相关原理 http://mp.blog.csdn.net/postedit/79183726
MHA 最佳实战 http://mp.blog.csdn.net/postedit/79183743
MySQL读写分离原理相关知识体系 http://blog.csdn.net/wjl7813/article/details/79184468
本文以第一种方式实现(基于程序代码内部实现)
参考文档：
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数据库
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
[binlog1]
hostname=192.168.137.140
master_binlog_dir=/data1/mha/binlog/3389
no_master=1
ignore_fail=1
上传MHA 脚本到 /etc/mha 路径下
chmod 755 master_ip_*
chmod 755 power_manager
创建MHA、BINLOG工作目录
mkdir -p /data1/mha/masterha/app3389
mkdir -p /data1/mha/binlog/3389
chown -R mysql:mysql /data1/mha/binlog/3389
chown -R mysql:mysql /data1/mha/masterha/app3389
.4.8. manager 端启动BINLOG SERVER
查看node1-master的 binglog位置
mysql> show master status
-> ;
+-----------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+------------------------------------------+
| 1371413389-bin.000002 | 968 | | | 3503c65e-e949-11e7-b2c3-000c295345bc:1-3 |
+-----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
su - mysql
cd /data1/mha/binlog/3389;
nohup mysqlbinlog -R --host=192.168.137.141 -P3389 --user=mha --password=mysqlDBA --raw --stop-never 1371413389-bin.000002 &
ps -ef | grep mysqlbinlog | grep -v grep # 验证binlog server进程是否存在
[mysql@mha-manager 3389]$ masterha_check_status --conf=/etc/mha/app3389.cnf
app3389 (pid:3240) is running(0:PING_OK), master:192.168.137.141
[mysql@mha-manager 3389]$ masterha_check_ssh --conf=/etc/mha/app3389.cnf
Tue Dec 26 08:37:50 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Dec 26 08:37:50 2017 - [info] Reading application default configuration from /etc/mha/app3389.cnf..
Tue Dec 26 08:37:50 2017 - [info] Reading server configuration from /etc/mha/app3389.cnf..
Tue Dec 26 08:37:50 2017 - [info] Starting SSH connection tests..
Tue Dec 26 08:37:51 2017 - [debug]
Tue Dec 26 08:37:50 2017 - [debug] Connecting via SSH from mysql@192.168.137.141(192.168.137.141:22) to mysql@192.168.137.142(192.168.137.142:22)..
Tue Dec 26 08:37:51 2017 - [debug] ok.
Tue Dec 26 08:37:51 2017 - [debug] Connecting via SSH from mysql@192.168.137.141(192.168.137.141:22) to mysql@192.168.137.143(192.168.137.143:22)..
Tue Dec 26 08:37:51 2017 - [debug] ok.
Tue Dec 26 08:37:51 2017 - [debug]
Tue Dec 26 08:37:51 2017 - [debug] Connecting via SSH from mysql@192.168.137.142(192.168.137.142:22) to mysql@192.168.137.141(192.168.137.141:22)..
Tue Dec 26 08:37:51 2017 - [debug] ok.
Tue Dec 26 08:37:51 2017 - [debug] Connecting via SSH from mysql@192.168.137.142(192.168.137.142:22) to mysql@192.168.137.143(192.168.137.143:22)..
Tue Dec 26 08:37:51 2017 - [debug] ok.
Tue Dec 26 08:37:52 2017 - [debug]
Tue Dec 26 08:37:51 2017 - [debug] Connecting via SSH from mysql@192.168.137.143(192.168.137.143:22) to mysql@192.168.137.141(192.168.137.141:22)..
Tue Dec 26 08:37:51 2017 - [debug] ok.
Tue Dec 26 08:37:51 2017 - [debug] Connecting via SSH from mysql@192.168.137.143(192.168.137.143:22) to mysql@192.168.137.142(192.168.137.142:22)..
Tue Dec 26 08:37:51 2017 - [debug] ok.
Tue Dec 26 08:37:52 2017 - [info] All SSH connection tests passed successfully.
[mysql@mha-manager 3389]$ masterha_check_repl --conf=/etc/mha/app3389.cnf
Mon Dec 25 22:47:07 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Dec 25 22:47:07 2017 - [info] Reading application default configuration from /etc/mha/app3389.cnf..
Mon Dec 25 22:47:07 2017 - [info] Reading server configuration from /etc/mha/app3389.cnf..
Mon Dec 25 22:47:07 2017 - [info] MHA::MasterMonitor version 0.57.
Mon Dec 25 22:47:07 2017 - [info] GTID failover mode = 1
Mon Dec 25 22:47:07 2017 - [info] Dead Servers:
Mon Dec 25 22:47:07 2017 - [info] Alive Servers:
Mon Dec 25 22:47:07 2017 - [info] 192.168.137.141(192.168.137.141:3389)
Mon Dec 25 22:47:07 2017 - [info] 192.168.137.142(192.168.137.142:3389)
Mon Dec 25 22:47:07 2017 - [info] 192.168.137.143(192.168.137.143:3389)
Mon Dec 25 22:47:07 2017 - [info] Alive Slaves:
Mon Dec 25 22:47:07 2017 - [info] 192.168.137.142(192.168.137.142:3389) Version=5.7.21-log (oldest major version between slaves) log-bin:enabled
Mon Dec 25 22:47:07 2017 - [info] GTID ON
Mon Dec 25 22:47:07 2017 - [info] Replicating from 192.168.137.141(192.168.137.141:3389)
Mon Dec 25 22:47:07 2017 - [info] Primary candidate for the new Master (candidate_master is set)
Mon Dec 25 22:47:07 2017 - [info] 192.168.137.143(192.168.137.143:3389) Version=5.7.21-log (oldest major version between slaves) log-bin:enabled
Mon Dec 25 22:47:07 2017 - [info] GTID ON
Mon Dec 25 22:47:07 2017 - [info] Replicating from 192.168.137.141(192.168.137.141:3389)
Mon Dec 25 22:47:07 2017 - [info] Primary candidate for the new Master (candidate_master is set)
Mon Dec 25 22:47:07 2017 - [info] Current Alive Master: 192.168.137.141(192.168.137.141:3389)
Mon Dec 25 22:47:07 2017 - [info] Checking slave configurations..
Mon Dec 25 22:47:07 2017 - [info] read_only=1 is not set on slave 192.168.137.142(192.168.137.142:3389).
Mon Dec 25 22:47:07 2017 - [info] read_only=1 is not set on slave 192.168.137.143(192.168.137.143:3389).
Mon Dec 25 22:47:07 2017 - [info] Checking replication filtering settings..
Mon Dec 25 22:47:07 2017 - [info] binlog_do_db= , binlog_ignore_db=
Mon Dec 25 22:47:07 2017 - [info] Replication filtering check ok.
Mon Dec 25 22:47:07 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Warning: Permanently added '192.168.137.140' (RSA) to the list of known hosts.
Mon Dec 25 22:47:07 2017 - [info] HealthCheck: SSH to 192.168.137.140 is reachable.
Mon Dec 25 22:47:07 2017 - [info] Binlog server 192.168.137.140 is reachable.
Mon Dec 25 22:47:07 2017 - [info] Checking recovery script configurations on 192.168.137.140(192.168.137.140:3306)..
Mon Dec 25 22:47:07 2017 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data1/mha/binlog/3389 --output_file=/data1/mha/masterha/app3389/save_binary_logs_test --manager_version=0.57 --start_file=1371413389-bin.000002
Mon Dec 25 22:47:07 2017 - [info] Connecting to mysql@192.168.137.140(192.168.137.140:22)..
Creating /data1/mha/masterha/app3389 if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /data1/mha/binlog/3389, up to 1371413389-bin.000002
Mon Dec 25 22:47:08 2017 - [info] Binlog setting check done.
Mon Dec 25 22:47:08 2017 - [info] Checking SSH publickey authentication settings on the current master..
Mon Dec 25 22:47:08 2017 - [info] HealthCheck: SSH to 192.168.137.141 is reachable.
Mon Dec 25 22:47:08 2017 - [info]
192.168.137.141(192.168.137.141:3389) (current master)
+--192.168.137.142(192.168.137.142:3389)
+--192.168.137.143(192.168.137.143:3389)
Mon Dec 25 22:47:08 2017 - [info] Checking replication health on 192.168.137.142..
Mon Dec 25 22:47:08 2017 - [info] ok.
Mon Dec 25 22:47:08 2017 - [info] Checking replication health on 192.168.137.143..
Mon Dec 25 22:47:08 2017 - [info] ok.
Mon Dec 25 22:47:08 2017 - [info] Checking master_ip_failover_script status:
Mon Dec 25 22:47:08 2017 - [info] /etc/mha/master_ip_failover.sh 192.168.137.200 1 --command=status --ssh_user=mysql --orig_master_host=192.168.137.141 --orig_master_ip=192.168.137.141 --orig_master_port=3389
Checking the Status of the script.. OK
Mon Dec 25 22:47:08 2017 - [info] OK.
Mon Dec 25 22:47:08 2017 - [info] Checking shutdown script status:
Mon Dec 25 22:47:08 2017 - [info] /etc/mha/power_manager --command=status --ssh_user=mysql --host=192.168.137.141 --ip=192.168.137.141
Mon Dec 25 22:47:08 2017 - [info] OK.
Mon Dec 25 22:47:08 2017 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
启动 MHA
[mysql@mha-manager 3389]$ nohup masterha_manager --conf=/etc/mha/app3389.cnf --ignore_last_failover &
[2] 3240
[mysql@mha-manager 3389]$ nohup: ignoring input and appending output to `nohup.out'
检查MHA 状态
[mysql@mha-manager 3389]$ masterha_check_status --conf=/etc/mha/app3389.cnf
app3389 (pid:3240) is running(0:PING_OK), master:192.168.137.141
==================== 安装 lvs 和 keeplived ==
lvs客户端
真实服务器上操作(master/slave1/slave2)，参照格式编写lvs 客户端配置脚本，保存后给予执行权限。其完整的内容如下：
# vim /usr/local/bin/lvs_real
#!/bin/bash
#description : start realserver
VIP=192.168.137.201
/etc/rc.d/init.d/functions
case "$1" in
start)
echo " start LVS of REALServer"
/sbin/ifconfig lo:0 $VIP broadcast $VIP netmask 255.255.255.255 up
echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
;;
stop)
/sbin/ifconfig lo:0 down
echo "close LVS Directorserver"
echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce
;;
*)
echo "Usage: $0 {start|stop}"
exit 1
esac
真实服务器上操作(slave1 slave2),启动lvs client并配置成开机自启动
chmod 755 /usr/local/bin/lvs_real
chmod 755 /etc/rc.d/init.d/functions
/usr/local/bin/lvs_real start
echo "/usr/local/bin/lvs_real start" >> /etc/rc.d/rc.local
验证lvs client
[root@node1-master mysql]# ifconfig
eth0 Link encap:Ethernet HWaddr 00:0C:29:53:45:BC
inet addr:192.168.137.141 Bcast:192.168.137.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe53:45bc/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:271296 errors:0 dropped:0 overruns:0 frame:0
TX packets:87851 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:328651727 (313.4 MiB) TX bytes:9119574 (8.6 MiB)
eth0:1 Link encap:Ethernet HWaddr 00:0C:29:53:45:BC
inet addr:192.168.137.200 Bcast:192.168.137.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:65536 Metric:1
RX packets:12 errors:0 dropped:0 overruns:0 frame:0
TX packets:12 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:720 (720.0 b) TX bytes:720 (720.0 b)
lo:0 Link encap:Local Loopback
inet addr:192.168.137.201 Mask:255.255.255.255
UP LOOPBACK RUNNING MTU:65536 Metric:1
====
[root@lvs-master opt]# rpm -ivh keepalived-1.2.13-5.el6_6.x86_64.rpm
[root@lvs-master opt]# rpm -ivh ipvsadm-1.26-4.el6.x86_64.rpm
[root@lvs-master opt]# scp -rp ipvsadm-1.26-4.el6.x86_64.rpm keepalived* checkSlave.py lvs-backup:/opt/
[root@lvs-master opt]# mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
lvs-backup 同样执行上面的操作
开启路由转发
负载均衡器上操作(Master Backup),开启路由转发
echo "net.ipv4.ip_forward = 1" >> /etc/sysctl.conf
sysctl -p
Master的配置文件
# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
router_id lvs_mysql_3389
}
vrrp_instance vi_3389 {
state MASTER
interface eth0
lvs_sync_daemon_inteface eth0
virtual_router_id 11
priority 90
nopreempt
advert_int 1
authentication {
auth_type PASS
auth_pass mysql3389
}
virtual_ipaddress {
192.168.137.201
}
}
virtual_server 192.168.137.201 3389 {
delay_loop 2
lb_algo wrr
lb_kind DR
# persistence_timeout 60
protocol TCP
real_server 192.168.137.141 3389 {
weight 3
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3389
}
}
real_server 192.168.137.142 3389 {
weight 3
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3389
}
}
real_server 192.168.137.143 3389 {
weight 3
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3389
}
}
}
Backup的配置文件
# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
router_id lvs_mysql_3389
}
vrrp_instance vi_3389 {
state MASTER
interface eth0
lvs_sync_daemon_inteface eth0
virtual_router_id 11
priority 10
nopreempt
advert_int 1
authentication {
auth_type PASS
auth_pass mysql3389
}
virtual_ipaddress {
192.168.137.201
}
}
virtual_server 192.168.137.201 3389 {
delay_loop 2
lb_algo wrr
lb_kind DR
# persistence_timeout 60
protocol TCP
real_server 192.168.137.141 3389 {
weight 3
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3389
}
}
real_server 192.168.137.142 3389 {
weight 3
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3389
}
}
real_server 192.168.137.143 3389 {
weight 3
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3389
}
}
}
/etc/init.d/keepalived start
chkconfig keepalived on
[root@lvs-master opt]# tail -f /var/log/messages
Feb 1 00:10:55 lvs-master Keepalived_vrrp[7824]: VRRP_Instance(vi_3389) Transition to MASTER STATE
Feb 1 00:10:56 lvs-master Keepalived_vrrp[7824]: VRRP_Instance(vi_3389) Entering MASTER STATE
Feb 1 00:10:56 lvs-master Keepalived_vrrp[7824]: VRRP_Instance(vi_3389) setting protocol VIPs.
Feb 1 00:10:56 lvs-master Keepalived_healthcheckers[7823]: Netlink reflector reports IP 192.168.137.201 added
Feb 1 00:10:56 lvs-master Keepalived_vrrp[7824]: VRRP_Instance(vi_3389) Sending gratuitous ARPs on eth0 for 192.168.137.201
Feb 1 00:11:01 lvs-master Keepalived_vrrp[7824]: VRRP_Instance(vi_3389) Sending gratuitous ARPs on eth0 for 192.168.137.201
Feb 1 00:11:01 lvs-master Keepalived_vrrp[7824]: VRRP_Instance(vi_3389) Received lower prio advert, forcing new election
Feb 1 00:11:01 lvs-master Keepalived_vrrp[7824]: VRRP_Instance(vi_3389) Sending gratuitous ARPs on eth0 for 192.168.137.201
Feb 1 00:11:01 lvs-master Keepalived_vrrp[7824]: VRRP_Instance(vi_3389) Received lower prio advert, forcing new election
Feb 1 00:11:01 lvs-master Keepalived_vrrp[7824]: VRRP_Instance(vi_3389) Sending gratuitous ARPs on eth0 for 192.168.137.201
[root@lvs-backup log]# tail -f messages
Feb 1 00:11:00 lvs-backup Keepalived_vrrp[7796]: VRRP_Instance(vi_3389) Received higher prio advert
Feb 1 00:11:00 lvs-backup Keepalived_vrrp[7796]: VRRP_Instance(vi_3389) Entering BACKUP STATE
Feb 1 00:11:00 lvs-backup kernel: IPVS: Registered protocols (TCP, UDP, SCTP, AH, ESP)
Feb 1 00:11:00 lvs-backup kernel: IPVS: Connection hash table configured (size=4096, memory=64Kbytes)
Feb 1 00:11:00 lvs-backup kernel: IPVS: ipvs loaded.
Feb 1 00:11:00 lvs-backup Keepalived_healthcheckers[7795]: Using LinkWatch kernel netlink reflector...
Feb 1 00:11:00 lvs-backup Keepalived_healthcheckers[7795]: Activating healthchecker for service [192.168.137.141]:3389
Feb 1 00:11:00 lvs-backup Keepalived_healthcheckers[7795]: Activating healthchecker for service [192.168.137.142]:3389
Feb 1 00:11:00 lvs-backup Keepalived_healthcheckers[7795]: Activating healthchecker for service [192.168.137.143]:3389
Feb 1 00:11:00 lvs-backup kernel: IPVS: [wrr] scheduler registered.
[root@lvs-master opt]# ipvsadm -Ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port Forward Weight ActiveConn InActConn
TCP 192.168.137.201:3389 wrr
-> 192.168.137.141:3389 Route 3 0 0
-> 192.168.137.142:3389 Route 3 0 0
-> 192.168.137.143:3389 Route 3 0 0
====== 测试部分 ====
创建测试用户
mysql> grant select on zabbix.* to user_zabbix@'%' identified by 'zabbix';
Query OK, 0 rows affected, 1 warning (0.09 sec)
mysql> flush privileges ;
Query OK, 0 rows affected (1.02 sec)
[root@node1-master ~]# mysql -h 192.168.137.141 -S /data1/db3389/my3389.sock -uuser_zabbix -p'zabbix' -P3389
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2655
Server version: 5.7.21-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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.
mysql> exit
[root@node1-master ~]# mysql -h 192.168.137.201 -S /data1/db3389/my3389.sock -uuser_zabbix -p'zabbix' -P3389
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2427
Server version: 5.7.21-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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.
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper
Connection id: 2427
Current database:
Current user: user_zabbix@192.168.137.201
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.21-log MySQL Community Server (GPL)
Protocol version: 10
Connection: 192.168.137.201 via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3389
Uptime: 15 hours 48 min 51 sec
Threads: 6 Questions: 18830 Slow queries: 0 Opens: 156 Flush tables: 3 Open tables: 41 Queries per second avg: 0.330
3405
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
