設(shè)計(jì)MySQL高可用架構(gòu)的目的是為了避免單點(diǎn)故障臂容，減少因系統(tǒng)故障或者數(shù)據(jù)庫(kù)崩潰所造成的恢復(fù)或者停機(jī)時(shí)間脓杉，為企業(yè)提供7*24的持續(xù)及高性能服務(wù)尿赚，基本原理大多數(shù)為通過(guò)主從自動(dòng)切換及vip漂移

比較常見(jiàn)的高可用架構(gòu)有MHA，MM督赤，PXC等

MM架構(gòu)優(yōu)點(diǎn)是：

搭建快速方便，適合中小型公司没卸，MySQL互為主從秒旋，保證兩臺(tái)數(shù)據(jù)庫(kù)的一致性煤蚌，keepalived實(shí)現(xiàn)虛擬IP和自動(dòng)的服務(wù)監(jiān)控功能尉桩，利用VIP自動(dòng)切換保證高可用，基本可以滿足業(yè)務(wù)需要蜘犁。

但是這種架構(gòu)存在以下問(wèn)題：

1 切換后出現(xiàn)雙vip的問(wèn)題这橙，腦裂問(wèn)題等屈扎，可以通過(guò)增加仲裁節(jié)點(diǎn)助隧。

2 切換時(shí)丟數(shù)據(jù)，可以配合腳本監(jiān)控binlog日志或者采用5.7版本的增強(qiáng)半同步哩牍。

3 主從延遲不能避免膝昆，如果要求沒(méi)有主從延遲荚孵，可以采用PXC架構(gòu)骄呼。

4 當(dāng)發(fā)生故障的服務(wù)器恢復(fù)后蜓萄，不能自動(dòng)切回嫉沽，切換回來(lái)比較麻煩，還需要手動(dòng)修改配置文件中的權(quán)重魂毁。

5 對(duì)腳本編寫(xiě)的能力也有不小考驗(yàn)臣咖。

想要了解keepalived工作原理和配置文件說(shuō)明的童鞋可以自行百度，或者看看這篇很詳細(xì)漱牵， 傳送門keepalived工作原理與配置文件說(shuō)明

公司大部分的架構(gòu)為MM+keepalived或者M(jìn)M+heartbeat，然后主庫(kù)下面掛上從庫(kù)作為讀庫(kù)疚漆，再配合mycat集群實(shí)現(xiàn)讀寫(xiě)分離和分庫(kù)分表就是一套比較完善的MySQL高可用架構(gòu)了酣胀。

現(xiàn)在有新的業(yè)務(wù)需要一套新的環(huán)境，在此記錄下部署過(guò)程娶聘，希望對(duì)剛接觸MySQL的同學(xué)有所幫助闻镶，前面MySQL安裝和主從搭建可以不看，廢話不多少铆农，架構(gòu)圖如下

[圖片上傳失敗...(image-2b9556-1559096651346)]

一 環(huán)境準(zhǔn)備

操作系統(tǒng)版本為 Ubuntu 14.04.5

服務(wù)器為16G內(nèi)存 4核CPU 1T數(shù)據(jù)目錄存儲(chǔ)

數(shù)據(jù)庫(kù)版本為 5.6.39-log

IP規(guī)劃

master1： 192.168.70.154

master2： 192.168.70.156

vip 192.168.70.157

二 MySQL雙主搭架

在兩臺(tái)數(shù)據(jù)庫(kù)服務(wù)器上

上傳安裝包

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

解壓

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

將解壓后的包移動(dòng)到MySQL的basedir

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

創(chuàng)建mysql組和用戶

groupadd mysql

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

修改相關(guān)目錄的權(quán)限

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

chown -R mysql:mysql /data/mysql

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

編輯配置文件

配置文件需要特別注意的地方為：

1 server-id必須不同，否則主從搭建報(bào)錯(cuò)书劝。

2 主鍵自增參數(shù)，可以避免主鍵沖突的錯(cuò)誤

master1 設(shè)置如下

auto-increment-increment = 2

auto-increment-offset = 1

master 2 設(shè)置如下

auto-increment-increment = 2

auto-increment-offset = 2

下面給出一個(gè)比較全的可以作為參考坑赡，其他的要根據(jù)自己的實(shí)際情況修改：

vim /etc/my.cnf

[client]

password = [your_password]

port = 3306
socket = /tmp/mysql.sock

設(shè)置客戶端字符集

default-character-set = utf8
[mysqld]

generic configuration options

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

設(shè)置服務(wù)器時(shí)區(qū)螟加，建議每次都顯示指定

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

設(shè)置服務(wù)器端字符集黍图，注意和客戶端字符集一樣，建議都設(shè)置utf8,防止中文亂碼

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

是否支持federated 分布式引擎默認(rèn)不支持

federated

設(shè)置sql校驗(yàn)?zāi)Ｊ剑撛O(shè)置影響到數(shù)據(jù)庫(kù)對(duì)字段數(shù)據(jù)的校驗(yàn)嚴(yán)格程度

sql_mode = STRICT_TRANS_TABLES

expire_logs_days = 15

在同步配置中這個(gè)要額外注意否則容易導(dǎo)致主鍵沖突

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

這個(gè)變量是針對(duì)Solaris系統(tǒng)的炫刷，如果設(shè)置這個(gè)變量的話圃庭，mysqld就會(huì)調(diào)用thr_setconcurrency()涂屁。#這個(gè)函數(shù)使應(yīng)用程序給同一時(shí)間運(yùn)行的線程系統(tǒng)提供期望的線程數(shù)目栏账。

thread_concurrency = 4

無(wú)論是否有misam表建議都16-128M茶鹃，如果misam表多那么建議設(shè)置為128M最大不要超#過(guò)256M

query_cache_size = 64M

query_cache_limit = 2M

ft_min_word_len = 4

memlock

5.5以下版本建議顯示設(shè)置兑障。5.5默認(rèn)是innodb

default-storage-engine = innodb
thread_stack = 192K

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

transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M

防止夸庫(kù)更新混亂，同步里推薦的設(shè)置

replicate_wild_do_table=tang_monitor_report.%

replicate_wild_do_table=%.%

建議顯示指定binlog和relaylog的名字，防止因修改hostname而導(dǎo)致出錯(cuò)

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

binlog記錄格式，建議設(shè)置row棍好，防止statement格式導(dǎo)致的數(shù)據(jù)不一直無(wú)法檢測(cè)的問(wèn)題

binlog_format=mixed

設(shè)置是否將主庫(kù)的更新寫(xiě)入binlog ，一般情況關(guān)閉，建議關(guān)閉

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

半同步配置參數(shù)鸟整，很簡(jiǎn)單吧兑燥，簡(jiǎn)單的讓人震驚

rpl_semi_sync_slave_enabled=1

rpl_semi_sync_master_enabled=1

rpl_semi_sync_master_timeout=1000

slave庫(kù)的時(shí)候有用，設(shè)置是否slave庫(kù)為只讀，建議開(kāi)啟

read_only

misam index 緩存如果misam表多建議適當(dāng)?shù)脑龃?/h1>

key_buffer_size = 128M
bulk_insert_buffer_size = 64M

當(dāng)在REPAIR TABLE或用CREATE INDEX創(chuàng)建索引或ALTER TABLE過(guò)程中排序 MyISAM索引分#配的緩沖區(qū)。

myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G

設(shè)置misam表修復(fù)的線程數(shù)

myisam_repair_threads = 1

myisam表自動(dòng)崩潰恢復(fù)級(jí)別

myisam_recover= BACKUP,FORCE

設(shè)置innodb數(shù)據(jù)字典的緩存糠排，一般16-20M基本可以唉工，如果innodb表特別多可適當(dāng)增大到#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铅乡，默認(rèn)是4即使修改也無(wú)用 ,5.5之后可以修改最大#不超過(guò)64

innodb_write_io_threads = 8
innodb_read_io_threads = 8

innodb_force_recovery=1

設(shè)置innodb內(nèi)部線程并發(fā)數(shù)

innodb_thread_concurrency = 16

設(shè)置日志刷新的方式

innodb_flush_log_at_trx_commit = 1

設(shè)置binlog刷新方式，1表示提交前寫(xiě)入了二進(jìn)制夺克，但事務(wù)commit失敗。二進(jìn)制日志無(wú)法回#滾解決：設(shè)置innodb_support_xa=1（默認(rèn)開(kāi)啟）,能夠確保二進(jìn)制日志和數(shù)據(jù)文件的同步

sync_binlog = 1

innodb_fast_shutdown

innodb_log_buffer_size = 4M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3

配置數(shù)據(jù)臟葉的比例

innodb_max_dirty_pages_pct = 75

設(shè)置mysql 數(shù)據(jù)刷新的方式默認(rèn)是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的權(quán)限

skip-show-database

控制是否支持UDF

log_bin_trust_function_creators = 1

控制是否slave線程隨mysql server的重啟而重啟鹏控，建議開(kāi)啟

skip_slave_start

之前是回滾當(dāng)前query，現(xiàn)在是回滾整個(gè)事物曹抬，這樣可以更快的釋放資源

innodb_rollback_on_timeout = ON

設(shè)置連接的超時(shí)時(shí)間灾锯，如果不希望長(zhǎng)連接可以設(shè)置相對(duì)較小的值，有個(gè)高手老王建議設(shè)置為10-15

interactive_timeout = 300

wait_timeout只作用于TCP/IP和Socket鏈接的線程囱井，一般設(shè)置值和interactive_timeout一樣

wait_timeout = 300

新連接時(shí)候用到，在高并發(fā)的系統(tǒng)里建議10-15默認(rèn)10

connect_timeout=15

主從復(fù)制里面slave檢查主庫(kù)是否正常并試圖從新連接之前的等待時(shí)間的時(shí)間蔚鸥，默認(rèn)1小時(shí)建議30秒

slave_net_timeout = 30

不復(fù)制的數(shù)據(jù)庫(kù)

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

以上配置完成后開(kāi)始初始化

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

出現(xiàn)讓你修改root密碼之類的信息說(shuō)明初始化成功藤滥，如果沒(méi)成功也許會(huì)提示你安裝相關(guān)的依賴包

啟動(dòng)

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

驗(yàn)證是否安裝成功可以通過(guò)查看MySQL進(jìn)程是否存在金句，3306端口是否被監(jiān)聽(tīng)等

ps -ef |grep mysql

netstat -nat |grep 3306

配置主主架構(gòu)

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

在154上

創(chuàng)建復(fù)制用戶

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

flush privileges;

查看日志號(hào)和偏移量

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

兩個(gè)同步進(jìn)程都為yes說(shuō)明主從復(fù)制成功。

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 #兩臺(tái)都設(shè)置BACKUP
interface eth0 #網(wǎng)卡
virtual_router_id 157 #主備相同治力，在同一個(gè)局域網(wǎng)中不是一對(duì)的keepalived router_id不能相同，否則報(bào)錯(cuò)废亭，這里用157
priority 100 #優(yōu)先級(jí)液兽，backup設(shè)置90
advert_int 1
nopreempt #不主動(dòng)搶占資源，只在master這臺(tái)優(yōu)先級(jí)高的設(shè)置凭迹，backup不設(shè)置
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算法，用不到蚀狰，我們就關(guān)閉了

lb_kind DR #LVS模式扮授，如果不關(guān)閉关贵，備用服務(wù)器不能通過(guò)VIP連接主MySQL

persistence_timeout 50 #同一IP的連接60秒內(nèi)被分配到同一臺(tái)真實(shí)服務(wù)器
protocol TCP
real_server 192.168.70.154 3306 { #檢測(cè)本地mysql，backup也要寫(xiě)檢測(cè)本地mysql
weight 3
notify_down /etc/keepalived/mysql.sh #當(dāng)mysq服down時(shí)兑宇，執(zhí)行此腳本碍侦，殺死keepalived實(shí)現(xiàn)切換
TCP_CHECK {
connect_timeout 3 #連接超時(shí)
nb_get_retry 3 #重試次數(shù)
delay_before_retry 3 #重試間隔時(shí)間
}
}
~

156上配置如下

! Configuration File forkeepalived
vrrp_instance VI_156 {
state BACKUP #兩臺(tái)都設(shè)置BACKUP
interface eth0
virtual_router_id 157 #主備相同
priority 90 #優(yōu)先級(jí)，backup設(shè)置90
advert_int 1
nopreempt #不主動(dòng)搶占資源隶糕，只在master這臺(tái)優(yōu)先級(jí)高的設(shè)置瓷产，backup不設(shè)置
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算法，用不到枚驻，我們就關(guān)閉了

lb_kind DR #LVS模式濒旦，如果不關(guān)閉，備用服務(wù)器不能通過(guò)VIP連接主MySQL

persistence_timeout 50 #同一IP的連接60秒內(nèi)被分配到同一臺(tái)真實(shí)服務(wù)器
protocol TCP
real_server 192.168.70.156 3306 { #檢測(cè)本地mysql再登，backup也要寫(xiě)檢測(cè)本地mysql
weight 3
notify_down /etc/keepalived/mysql.sh #當(dāng)mysq服down時(shí)尔邓，執(zhí)行此腳本，殺死keepalived實(shí)現(xiàn)切換
TCP_CHECK {
connect_timeout 3 #連接超時(shí)
nb_get_retry 3 #重試次數(shù)
delay_before_retry 3 #重試間隔時(shí)間
}
}

編寫(xiě)腳本锉矢，該腳本的作用就是當(dāng)檢測(cè)不到mysql的服務(wù)的時(shí)候梯嗽，kill掉keepalived的進(jìn)程，實(shí)現(xiàn)VIP漂移

vim /etc/keepalived/mysql.sh

!/bin/bash

pkill keepalived

授予執(zhí)行權(quán)限

chmod +x /etc/keepalived/mysql.sh

啟動(dòng)

/etc/init.d/keepalived start

查看日志

ubuntu系統(tǒng)日志在/var/log/syslog

centos系統(tǒng)日志在 /var/log/message

154上的啟動(dòng)日志

[圖片上傳失敗...(image-4b731-1559096651346)]

156上的啟動(dòng)日志

[圖片上傳失敗...(image-6cc4b4-1559096651346)]

vip出現(xiàn)在154上

[圖片上傳失敗...(image-323185-1559096651346)]

四 驗(yàn)證

模擬154服務(wù)器數(shù)據(jù)庫(kù)宕機(jī)

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

查看日志

154上keepalived檢查不到mysql的服務(wù)沽损，狀態(tài)變?yōu)閒ail 灯节，將虛擬IP remove，執(zhí)行腳本绵估，殺掉keepalived進(jìn)程

[圖片上傳失敗...(image-56a3d9-1559096651346)]

156上查看日志 變?yōu)閙aster狀態(tài)

[圖片上傳失敗...(image-62d496-1559096651346)]

ip addr show

vip已經(jīng)在156上炎疆，使用navicate工具連接VIP還是能正常提供服務(wù)

[圖片上傳失敗...(image-1f4241-1559096651346)]

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

VIP如何切換回來(lái)最安全呢磷雇？

現(xiàn)在模擬154恢復(fù)，讓VIP從156漂回到154□锞龋現(xiàn)在把154上的數(shù)據(jù)庫(kù)啟動(dòng)唯笙，

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上啟動(dòng)keepalived

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

• Starting keepalived keepalived [ OK ]

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

/etc/init.d/keepalived stop

至此完成切回。