设计MySQL高可用架构的目的是为了避免单点故障，减少因系统故障或者数据库崩溃所造成的恢复或者停机时间，为企业提供7*24的持续及高性能服务，基本原理大多数为通过主从自动切换及vip漂移

比较常见的高可用架构有MHA，MM，PXC等

MM架构优点是：

搭建快速方便，适合中小型公司，MySQL互为主从，保证两台数据库的一致性，keepalived实现虚拟IP和自动的服务监控功能，利用VIP自动切换保证高可用，基本可以满足业务需要。

但是这种架构存在以下问题：

1 切换后出现双vip的问题，脑裂问题等，可以通过增加仲裁节点。

2 切换时丢数据，可以配合脚本监控binlog日志或者采用5.7版本的增强半同步。

3 主从延迟不能避免，如果要求没有主从延迟，可以采用PXC架构。

4 当发生故障的服务器恢复后，不能自动切回，切换回来比较麻烦，还需要手动修改配置文件中的权重。

5 对脚本编写的能力也有不小考验。

想要了解keepalived工作原理和配置文件说明的童鞋可以自行百度，或者看看这篇很详细， 传送门keepalived工作原理与配置文件说明

公司大部分的架构为MM+keepalived或者MM+heartbeat，然后主库下面挂上从库作为读库，再配合mycat集群实现读写分离和分库分表就是一套比较完善的MySQL高可用架构了。

现在有新的业务需要一套新的环境，在此记录下部署过程，希望对刚接触MySQL的同学有所帮助，前面MySQL安装和主从搭建可以不看，废话不多少，架构图如下

[图片上传失败...(image-2b9556-1559096651346)]

一 环境准备

操作系统版本为 Ubuntu 14.04.5

服务器为16G内存 4核CPU 1T数据目录存储

数据库版本为 5.6.39-log

IP规划

master1： 192.168.70.154

master2： 192.168.70.156

vip 192.168.70.157

二 MySQL双主搭架

在两台数据库服务器上

上传安装包

mysql-5.6.39-linux-glibc2.12-x86_64.tar.gz

解压

tar -zxvf mysql-5.6.39-linux-glibc2.12-x86_64.tar.gz

将解压后的包移动到MySQL的basedir

mv mysql-5.6.39-linux-glibc2.12-x86_64 /usr/local/mysql

创建mysql组和用户

groupadd mysql

useradd -r -m -s /bin/bash -g mysql mysql

修改相关目录的权限

chown -R mysql:mysql /usr/local/mysql/

chown -R mysql:mysql /data/mysql

chown -R mysql:mysql /etc/my.cnf

编辑配置文件

配置文件需要特别注意的地方为：

1 server-id必须不同，否则主从搭建报错。

2 主键自增参数，可以避免主键冲突的错误

master1 设置如下

auto-increment-increment = 2

auto-increment-offset = 1

master 2 设置如下

auto-increment-increment = 2

auto-increment-offset = 2

下面给出一个比较全的可以作为参考，其他的要根据自己的实际情况修改：

vim /etc/my.cnf

[client]

password = [your_password]

port = 3306
socket = /tmp/mysql.sock

设置客户端字符集

default-character-set = utf8
[mysqld]

generic configuration options

port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
user = mysql

设置服务器时区，建议每次都显示指定

default-time-zone='+8:00'

设置服务器端字符集，注意和客户端字符集一样，建议都设置utf8,防止中文乱码

character-set-server=utf8
skip-name-resolve

是否支持federated 分布式引擎默认不支持

federated

设置sql校验模式，该设置影响到数据库对字段数据的校验严格程度

sql_mode = STRICT_TRANS_TABLES

expire_logs_days = 15

在同步配置中这个要额外注意否则容易导致主键冲突

auto-increment-increment = 2
auto-increment-offset = 1
back_log = 50
max_connections = 10000
max_connect_errors = 100000
table_open_cache = 1024

external-locking

max_allowed_packet = 16M
binlog_cache_size = 2M
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
sort_buffer_size = 8M
join_buffer_size = 4M
thread_cache_size = 64

这个变量是针对Solaris系统的，如果设置这个变量的话，mysqld就会调用thr_setconcurrency()。#这个函数使应用程序给同一时间运行的线程系统提供期望的线程数目。

thread_concurrency = 4

无论是否有misam表建议都16-128M，如果misam表多那么建议设置为128M最大不要超#过256M

query_cache_size = 64M

query_cache_limit = 2M

ft_min_word_len = 4

memlock

5.5以下版本建议显示设置。5.5默认是innodb

default-storage-engine = innodb
thread_stack = 192K

READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M

防止夸库更新混乱，同步里推荐的设置

replicate_wild_do_table=tang_monitor_report.%

replicate_wild_do_table=%.%

建议显示指定binlog和relaylog的名字，防止因修改hostname而导致出错

log-bin=mysql-bin
relay-log=mysql-relay-bin

binlog记录格式，建议设置row，防止statement格式导致的数据不一直无法检测的问题

binlog_format=mixed

设置是否将主库的更新写入binlog ，一般情况关闭，建议关闭

log_slave_updates

log_warnings

slow_query_log
slow_query_log_file=/data/mysql/myslow.log
long_query_time = 2
innodb_buffer_pool_instances=4
innodb_change_buffering =changes
innodb_old_blocks_time=1000
innodb_autoextend_increment=50
sync_binlog=100
innodb_open_files=1024
innodb_file_per_table =1
open-files-limit = 8192
log-error =/data/mysql/error.log
server-id = 3306154

半同步配置参数，很简单吧，简单的让人震惊

rpl_semi_sync_slave_enabled=1

rpl_semi_sync_master_enabled=1

rpl_semi_sync_master_timeout=1000

slave库的时候有用，设置是否slave库为只读，建议开启

read_only

misam index 缓存如果misam表多建议适当的增大

key_buffer_size = 128M
bulk_insert_buffer_size = 64M

当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分#配的缓冲区。

myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G

设置misam表修复的线程数

myisam_repair_threads = 1

myisam表自动崩溃恢复级别

myisam_recover= BACKUP,FORCE

设置innodb数据字典的缓存，一般16-20M基本可以，如果innodb表特别多可适当增大到#32M

innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 10G
innodb_data_file_path = ibdata1:512M:autoextend

5.1版本为（innodb_file_io_threads）及其之前的版本都是硬编码为4，默认是4即使修改也无用 ,5.5之后可以修改最大#不超过64

innodb_write_io_threads = 8
innodb_read_io_threads = 8

innodb_force_recovery=1

设置innodb内部线程并发数

innodb_thread_concurrency = 16

设置日志刷新的方式

innodb_flush_log_at_trx_commit = 1

设置binlog刷新方式，1表示提交前写入了二进制，但事务commit失败。二进制日志无法回#滚解决：设置innodb_support_xa=1（默认开启）,能够确保二进制日志和数据文件的同步

sync_binlog = 1

innodb_fast_shutdown

innodb_log_buffer_size = 4M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3

配置数据脏叶的比例

innodb_max_dirty_pages_pct = 75

设置mysql 数据刷新的方式默认是fdatasync

doublewrite flush logic. The default value is "fdatasync", another

option is "O_DSYNC".

innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 15

控制普通用户show database的权限

skip-show-database

控制是否支持UDF

log_bin_trust_function_creators = 1

控制是否slave线程随mysql server的重启而重启，建议开启

skip_slave_start

之前是回滚当前query，现在是回滚整个事物，这样可以更快的释放资源

innodb_rollback_on_timeout = ON

设置连接的超时时间，如果不希望长连接可以设置相对较小的值，有个高手老王建议设置为10-15

interactive_timeout = 300

wait_timeout只作用于TCP/IP和Socket链接的线程，一般设置值和interactive_timeout一样

wait_timeout = 300

新连接时候用到，在高并发的系统里建议10-15默认10

connect_timeout=15

主从复制里面slave检查主库是否正常并试图从新连接之前的等待时间的时间，默认1小时建议30秒

slave_net_timeout = 30

不复制的数据库

replicate-ignore-db=performance_schema
replicate-ignore-db=information_schema
replicate-ignore-db=test
replicate-ignore-db=mysql
[mysqldump]

Do not buffer the whole result set in memory before writing it to

file. Required for dumping very large tables

quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash

Only allow UPDATEs and DELETEs that use keys.

safe-updates

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

以上配置完成后开始初始化

mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql --defaults-file=/etc/my.cnf

出现让你修改root密码之类的信息说明初始化成功，如果没成功也许会提示你安装相关的依赖包

启动

mysqld_safe --defaults-file=/etc/my.cnf &

验证是否安装成功可以通过查看MySQL进程是否存在，3306端口是否被监听等

ps -ef |grep mysql

netstat -nat |grep 3306

配置主主架构

154(master)--156(slave) 搭建如下

在154上

创建复制用户

grant replication slave on . to 'repl'@'192.168.70.156' dentified by 'mysql';

flush privileges;

查看日志号和偏移量

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 | 1106 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在156上

CHANGE MASTER TO
MASTER_HOST='192.168.70.154',
MASTER_USER='repl',
MASTER_PASSWORD='mysql',
MASTER_LOG_FILE='mysql-bin.000008',
MASTER_LOG_POS=1106;

start slave ;

show slave status\G

两个同步进程都为yes说明主从复制成功。

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

156(master)-154(slave) 搭架同理；

至此主从搭建完成。

三 keepalived安装配置

直接采用apt-get的方式安装

apt-get -y install keepalived

修改配置文件154上

vim /etc/keepalived/keepalived.conf

! Configuration File forkeepalived
vrrp_instance VI_154 {
state BACKUP #两台都设置BACKUP
interface eth0 #网卡
virtual_router_id 157 #主备相同，在同一个局域网中不是一对的keepalived router_id不能相同，否则报错，这里用157
priority 100 #优先级，backup设置90
advert_int 1
nopreempt #不主动抢占资源，只在master这台优先级高的设置，backup不设置
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.70.157
}
}
virtual_server 192.168.70.157 3306 {
delay_loop 2

lb_algo rr #LVS算法，用不到，我们就关闭了

lb_kind DR #LVS模式，如果不关闭，备用服务器不能通过VIP连接主MySQL

persistence_timeout 50 #同一IP的连接60秒内被分配到同一台真实服务器
protocol TCP
real_server 192.168.70.154 3306 { #检测本地mysql，backup也要写检测本地mysql
weight 3
notify_down /etc/keepalived/mysql.sh #当mysq服down时，执行此脚本，杀死keepalived实现切换
TCP_CHECK {
connect_timeout 3 #连接超时
nb_get_retry 3 #重试次数
delay_before_retry 3 #重试间隔时间
}
}
~

156上配置如下

! Configuration File forkeepalived
vrrp_instance VI_156 {
state BACKUP #两台都设置BACKUP
interface eth0
virtual_router_id 157 #主备相同
priority 90 #优先级，backup设置90
advert_int 1
nopreempt #不主动抢占资源，只在master这台优先级高的设置，backup不设置
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.70.157
}
}
virtual_server 192.168.70.157 3306 {
delay_loop 2

lb_algo rr #LVS算法，用不到，我们就关闭了

lb_kind DR #LVS模式，如果不关闭，备用服务器不能通过VIP连接主MySQL

persistence_timeout 50 #同一IP的连接60秒内被分配到同一台真实服务器
protocol TCP
real_server 192.168.70.156 3306 { #检测本地mysql，backup也要写检测本地mysql
weight 3
notify_down /etc/keepalived/mysql.sh #当mysq服down时，执行此脚本，杀死keepalived实现切换
TCP_CHECK {
connect_timeout 3 #连接超时
nb_get_retry 3 #重试次数
delay_before_retry 3 #重试间隔时间
}
}

编写脚本，该脚本的作用就是当检测不到mysql的服务的时候，kill掉keepalived的进程，实现VIP漂移

vim /etc/keepalived/mysql.sh

!/bin/bash

pkill keepalived

授予执行权限

chmod +x /etc/keepalived/mysql.sh

启动

/etc/init.d/keepalived start

查看日志

ubuntu系统日志在/var/log/syslog

centos系统日志在 /var/log/message

154上的启动日志

[图片上传失败...(image-4b731-1559096651346)]

156上的启动日志

[图片上传失败...(image-6cc4b4-1559096651346)]

vip出现在154上

[图片上传失败...(image-323185-1559096651346)]

四 验证

模拟154服务器数据库宕机

root@qsbilldatahis-db01:~# mysqladmin -uroot -p shutdown
Enter password:

查看日志

154上keepalived检查不到mysql的服务，状态变为fail ，将虚拟IP remove，执行脚本，杀掉keepalived进程

[图片上传失败...(image-56a3d9-1559096651346)]

156上查看日志 变为master状态

[图片上传失败...(image-62d496-1559096651346)]

ip addr show

vip已经在156上，使用navicate工具连接VIP还是能正常提供服务

[图片上传失败...(image-1f4241-1559096651346)]

至此，所有的都搭建完成。

VIP如何切换回来最安全呢？

现在模拟154恢复，让VIP从156漂回到154。现在把154上的数据库启动，

root@qsbilldatahis-db01:~# mysqld_safe --defaults-file=/etc/my.cnf &
[1] 592
root@qsbilldatahis-db01:~# 180411 17:24:49 mysqld_safe Logging to '/data/mysql/error.log'.
180411 17:24:49 mysqld_safe Starting mysqld daemon with databases from /data/mysql

154上启动keepalived

oot@qsbilldatahis-db01:~# /etc/init.d/keepalived start

• Starting keepalived keepalived [ OK ]

在156上停掉keepalived，vip就可以漂回154了

/etc/init.d/keepalived stop

至此完成切回。