MHA + keepalived mysql高可用方案部署

目录

    原理

    安装mysql主从

    安装mha

    测试切换

    恢复故障

    增加keepalive，避免跨网段无法访问虚地址    

原理

        master出现故障时，可以自动把最新数据的slave提升为新的master，把所有其他的slave重新指向新的master。切换过程对应用程序透明。迁移时间20-30秒左右。

        自动切换过程中，mha尝试从原master保存binlog，最大程度保证数据不丢失，如果服务器宕机（无法ssh连接），mha无法保存二进制日志，只进行故障转移丢失少量最新数据。

        使用mysql5.5版半同步机制可以大大降低数据丢失风险。如果只有一个slave收到最新的binlog，会同步数据到所有slave，保证所有节点的数据一致性。

部署信息

    主 10.78.72.73 centos6.5

    从1(备主) 10.78.72.74 centos6.5

    从2 10.78.72.75 centos6.5

    管理节点 10.78.72.76 centos7

    VIP 10.78.72.77

安装mysql主从

# 1主2从 安装mysql（版本：Ver 14.14 Distrib 5.1.71, for redhat-linux-gnu (x86_64) using readline 5.1）

[root@mysql01 ~]# yum install mysql mysql-server mysql-libs mysql-devel -y

# 配置文件

# 另外两个将server-id修改为不同值

[root@ mysql01 ~]# cat /etc/my.cnf  | grep -v '^$' | grep -v '^#'
[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8
wait_timeout = 10
max_connections = 1000
log-bin=mysql-bin
relay-log=relay-bin
relay-log-index=relay-bin-index
binlog-ignore-db=mysql
replicate-ignore-db=mysql
lower_case_table_names = 1
skip-name-resolve
binlog_format=mixed
log-slave-updates
relay_log_purge=0
read_only=1

server-id       = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

# 启动mysql

# /etc/init.d/mysqld start

# 设置slave01、slave02主从同步

# 在master上创建复制用户（允许从使用repl登录）

grant replication slave, file, select on *.* to 'repl'@'10.78.72.74' identified by 'xxxxx';

grant replication slave, file, select on *.* to 'repl'@'10.78.72.75' identified by 'xxxxx';

flush privileges;

# 在备master上创建复制用户（允许从使用repl登录） （不要忘记，否则切换到这个的时候没法同步）

grant replication slave, file, select on *.* to 'repl'@'10.78.72.73' identified by 'xxxxx';

grant replication slave, file, select on *.* to 'repl'@'10.78.72.75' identified by 'xxxxx';

flush privileges;

# 在master上查询

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000003 | 663 | | mysql |

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

1 row in set (0.00 sec)

# 在slave01上同步

mysql> STOP SLAVE;

mysql> RESET SLAVE;

mysql> CHANGE MASTER TO MASTER_HOST='10.78.72.73',

MASTER_USER='repl',

MASTER_PASSWORD='xxxxx',

MASTER_LOG_FILE='mysql-bin.000003',

MASTER_LOG_POS=663;

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS \G

# 在slave02上同步

mysql> STOP SLAVE;

mysql> RESET SLAVE;

mysql> CHANGE MASTER TO MASTER_HOST='10.78.72.73',

MASTER_USER='repl',

MASTER_PASSWORD='xxxxx',

MASTER_LOG_FILE='mysql-bin.000003',

MASTER_LOG_POS=663;

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS \G

安装mha

设置免密码登录

# 在master、slave、manager上做免密登录

# master

ssh-keygen -t rsa

一直回车

ssh root@10.78.72.74 "cat >> ~/.ssh/authorized_keys && chmod 600 ~/.ssh/authorized_keys" < ~/.ssh/id_rsa.pub

ssh root@10.78.72.75 "cat >> ~/.ssh/authorized_keys && chmod 600 ~/.ssh/authorized_keys" < ~/.ssh/id_rsa.pub

ssh root@10.78.72.76 "cat >> ~/.ssh/authorized_keys && chmod 600 ~/.ssh/authorized_keys" < ~/.ssh/id_rsa.pub

# slave

...

# manager

...

创建mha复制账号

# 在master、slave01、salve02上创建mha复制账号（mha_rep）

grant all privileges on *.* to 'mha_rep'@'10.78.72.73' identified by 'xxxxx';

grant all privileges on *.* to 'mha_rep'@'10.78.72.74' identified by 'xxxxx';

grant all privileges on *.* to 'mha_rep'@'10.78.72.75' identified by 'xxxxx';

grant all privileges on *.* to 'mha_rep'@'10.78.72.76' identified by 'xxxxx';

flush privileges;

安装包

# 在master、slave01、slave02、manager上安装mha4mysql-node包

# yum install perl-DBD-MySQL -y

# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

Preparing... ########################################### [100%]

1:mha4mysql-node ########################################### [100%]

# 用于识别差异日志并应用于其他slave

# cp /usr/bin/apply_diff_relay_logs /usr/local/bin/

# 用于保存和复制二进制日志

# cp /usr/bin/save_binary_logs /usr/local/bin/

# cp /usr/bin/filter_mysqlbinlog /usr/local/bin/

# 用于清除中继日志

# cp /usr/bin/purge_relay_logs /usr/local/bin/

# master安装manager

# yum install epel-release -y && yum clean all && yum makecache

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

# tar -xf mha4mysql-manager-0.56.tar.gz && cd mha4mysql-manager-0.56

[root@manager01 mha4mysql-manager-0.56]# yum install perl* -y && perl Makefile.PL && make && make install

# 复制脚本

[root@manager01]# cp /usr/bin/masterha_* /usr/local/bin/

# 查看masterha脚本

# ll /usr/local/bin/

total 84

-rwxr-xr-x 1 root root 16367 May 7 11:27 apply_diff_relay_logs

-rwxr-xr-x 1 root root 4807 May 7 11:27 filter_mysqlbinlog

-r-xr-xr-x 1 root root 1995 May 7 11:41 masterha_check_repl

-r-xr-xr-x 1 root root 1779 May 7 11:41 masterha_check_ssh

-r-xr-xr-x 1 root root 1865 May 7 11:41 masterha_check_status

-r-xr-xr-x 1 root root 3201 May 7 11:41 masterha_conf_host

-r-xr-xr-x 1 root root 2517 May 7 11:41 masterha_manager

-r-xr-xr-x 1 root root 2165 May 7 11:41 masterha_master_monitor

-r-xr-xr-x 1 root root 2373 May 7 11:41 masterha_master_switch

-r-xr-xr-x 1 root root 5171 May 7 11:41 masterha_secondary_check

-r-xr-xr-x 1 root root 1739 May 7 11:41 masterha_stop

-rwxr-xr-x 1 root root 8261 May 7 11:27 purge_relay_logs

-rwxr-xr-x 1 root root 7525 May 7 11:27 save_binary_logs

配置manager

# mkdir -p /etc/mha/ && cp samples/conf/app1.cnf /etc/mha/

# mkdir -p /var/log/masterha/app1

# mkdir -p /etc/mha/scripts

# 配置文件

# cat /etc/mha/app1.cnf

[server default]

manager_workdir=/var/log/masterha/app1

manager_log=/var/log/masterha/app1/manager.log

user=mha_rep # MHA管理mysql的用户名

password=xxxxx                         # MHA管理mysql的密码

ssh_user=root # 免密码登录的用户名

repl_user=repl # 主从复制账号名

repl_password=xxxxx                         # 主从复制账号密码

ping_interval=1 # 用来检查master是否正常

[server1]

hostname=10.78.72.73

candidate_master=1 # master宕机后，优先启用这台作为master

[server2]

hostname=10.78.72.74

candidate_master=1 # master宕机后，优先启用这台作为master

[server3]

hostname=10.78.72.75

no_master=1 # 使服务器不能成为master

# 测试ssh互信

[deploy@manager01 ~]$ masterha_check_ssh --conf=/etc/mha/app1.cnf

Mon May 7 13:53:40 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Mon May 7 13:53:40 2018 - [info] Reading application default configuration from /etc/mha/app1.cnf..

Mon May 7 13:53:40 2018 - [info] Reading server configuration from /etc/mha/app1.cnf..

Mon May 7 13:53:40 2018 - [info] Starting SSH connection tests..

Mon May 7 13:53:41 2018 - [debug]

Mon May 7 13:53:40 2018 - [debug] Connecting via SSH from deploy@10.78.72.73(10.78.72.73:22) to deploy@10.78.72.74(10.78.72.74:22)..

Mon May 7 13:53:40 2018 - [debug] ok.

Mon May 7 13:53:40 2018 - [debug] Connecting via SSH from deploy@10.78.72.73(10.78.72.73:22) to deploy@10.78.72.75(10.78.72.75:22)..

Mon May 7 13:53:40 2018 - [debug] ok.

Mon May 7 13:53:41 2018 - [debug]

Mon May 7 13:53:41 2018 - [debug] Connecting via SSH from deploy@10.78.72.74(10.78.72.74:22) to deploy@10.78.72.73(10.78.72.73:22)..

Mon May 7 13:53:41 2018 - [debug] ok.

Mon May 7 13:53:41 2018 - [debug] Connecting via SSH from deploy@10.78.72.74(10.78.72.74:22) to deploy@10.78.72.75(10.78.72.75:22)..

Mon May 7 13:53:41 2018 - [debug] ok.

Mon May 7 13:53:42 2018 - [debug]

Mon May 7 13:53:41 2018 - [debug] Connecting via SSH from deploy@10.78.72.75(10.78.72.75:22) to deploy@10.78.72.73(10.78.72.73:22)..

Mon May 7 13:53:41 2018 - [debug] ok.

Mon May 7 13:53:41 2018 - [debug] Connecting via SSH from deploy@10.78.72.75(10.78.72.75:22) to deploy@10.78.72.74(10.78.72.74:22)..

Mon May 7 13:53:41 2018 - [debug] ok.

Mon May 7 13:53:42 2018 - [info] All SSH connection tests passed successfully.

# 测试mysql主从复制是否成功

[deploy@manager01 ~]$ masterha_check_repl --conf=/etc/mha/app1.cnf

Mon May 7 14:12:13 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Mon May 7 14:12:13 2018 - [info] Reading application default configuration from /etc/mha/app1.cnf..

Mon May 7 14:12:13 2018 - [info] Reading server configuration from /etc/mha/app1.cnf..

Mon May 7 14:12:13 2018 - [info] MHA::MasterMonitor version 0.56.

Mon May 7 14:12:14 2018 - [info] GTID failover mode = 0

Mon May 7 14:12:14 2018 - [info] Dead Servers:

Mon May 7 14:12:14 2018 - [info] Alive Servers:

Mon May 7 14:12:14 2018 - [info] 10.78.72.73(10.78.72.73:3306)

Mon May 7 14:12:14 2018 - [info] 10.78.72.74(10.78.72.74:3306)

Mon May 7 14:12:14 2018 - [info] 10.78.72.75(10.78.72.75:3306)

Mon May 7 14:12:14 2018 - [info] Alive Slaves:

Mon May 7 14:12:14 2018 - [info] 10.78.72.74(10.78.72.74:3306) Version=5.1.71-log (oldest major version between slaves) log-bin:enabled

Mon May 7 14:12:14 2018 - [info] Replicating from 10.78.72.73(10.78.72.73:3306)

Mon May 7 14:12:14 2018 - [info] Primary candidate for the new Master (candidate_master is set)

Mon May 7 14:12:14 2018 - [info] 10.78.72.75(10.78.72.75:3306) Version=5.1.71-log (oldest major version between slaves) log-bin:enabled

Mon May 7 14:12:14 2018 - [info] Replicating from 10.78.72.73(10.78.72.73:3306)

Mon May 7 14:12:14 2018 - [info] Not candidate for the new Master (no_master is set)

Mon May 7 14:12:14 2018 - [info] Current Alive Master: 10.78.72.73(10.78.72.73:3306)

Mon May 7 14:12:14 2018 - [info] Checking slave configurations..

Mon May 7 14:12:15 2018 - [info] Checking replication filtering settings..

Mon May 7 14:12:15 2018 - [info] binlog_do_db= , binlog_ignore_db= mysql

Mon May 7 14:12:15 2018 - [info] Replication filtering check ok.

Mon May 7 14:12:15 2018 - [info] GTID (with auto-pos) is not supported

Mon May 7 14:12:15 2018 - [info] Starting SSH connection tests..

Mon May 7 14:12:16 2018 - [info] All SSH connection tests passed successfully.

Mon May 7 14:12:16 2018 - [info] Checking MHA Node version..

Mon May 7 14:12:16 2018 - [info] Version check ok.

Mon May 7 14:12:16 2018 - [info] Checking SSH publickey authentication settings on the current master..

Mon May 7 14:12:16 2018 - [info] HealthCheck: SSH to 10.78.72.73 is reachable.

Mon May 7 14:12:17 2018 - [info] Master MHA Node version is 0.56.

Mon May 7 14:12:17 2018 - [info] Checking recovery script configurations on 10.78.72.73(10.78.72.73:3306)..

Mon May 7 14:12:17 2018 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql,/var/log/mysql --output_file=/var/tmp/save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000003

Mon May 7 14:12:17 2018 - [info] Connecting to deploy@10.78.72.73(10.78.72.73:22)..

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

Checking output directory is accessible or not..

ok.

Binlog found at /var/lib/mysql, up to mysql-bin.000003

Mon May 7 14:12:17 2018 - [info] Binlog setting check done.

Mon May 7 14:12:17 2018 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..

Mon May 7 14:12:17 2018 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha_rep' --slave_host=10.78.72.74 --slave_ip=10.78.72.74 --slave_port=3306 --workdir=/var/tmp --target_version=5.1.71-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx

Mon May 7 14:12:17 2018 - [info] Connecting to deploy@10.78.72.74(10.78.72.74:22)..

Checking slave recovery environment settings..

Opening /var/lib/mysql/relay-log.info ... ok.

Relay log found at /var/lib/mysql, up to relay-bin.000004

Temporary relay log file is /var/lib/mysql/relay-bin.000004

Testing mysql connection and privileges.. done.

Testing mysqlbinlog output.. done.

Cleaning up test file(s).. done.

Mon May 7 14:12:17 2018 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha_rep' --slave_host=10.78.72.75 --slave_ip=10.78.72.75 --slave_port=3306 --workdir=/var/tmp --target_version=5.1.71-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx

Mon May 7 14:12:17 2018 - [info] Connecting to deploy@10.78.72.75(10.78.72.75:22)..

Checking slave recovery environment settings..

Opening /var/lib/mysql/relay-log.info ... ok.

Relay log found at /var/lib/mysql, up to relay-bin.000004

Temporar