MySQL雙主架構(gòu)主庫單點故障解決方案
作者：燕十三
在企業(yè)中，一般系統(tǒng)架構(gòu)的瓶頸會出現(xiàn)在數(shù)據(jù)庫這一部分，Mysql主從架構(gòu)在很大程度上解決了這部分瓶頸，但是在Mysql主從同步的架構(gòu)也存在很多問題。本文主要給大家提供主庫單點故障這個問題的解決方案，希望對您有所幫助。
在企業(yè)中，一般系統(tǒng)架構(gòu)的瓶頸會出現(xiàn)在數(shù)據(jù)庫這一部分，Mysql主從架構(gòu)在很大程度上解決了這部分瓶頸，但是在Mysql主從同步的架構(gòu)也存在很多問題。比如：
1. 關(guān)于數(shù)據(jù)寫入部分(也就是主庫)往往很難做到擴展，雖然很多大公司在邏輯業(yè)務(wù)方面就進行對數(shù)據(jù)的拆分，比如商品庫存按照區(qū)域去拆分(一個區(qū)域走一個庫存也就是一個主庫,然后定時同步總的庫存)，按照商品類型去劃分(一個類型的商品走一套數(shù)據(jù)庫)，但是這對于很多中小型公司來說實現(xiàn)起來還是比較困難的；
2. 主從同步一般都是一個主庫，一旦主庫出現(xiàn)問題,就有可能直接導致整個主從同步架構(gòu)崩盤，雖然發(fā)現(xiàn)后也是可以慢慢恢復的，但是這個恢復時間對于很多公司來說是難以接受的。
今天的這篇博文主要給解決主庫單點故障這個問題提供一個思路：
- 一臺主庫(我們稱之為master-01)提供服務(wù),只負責數(shù)據(jù)的寫入；
- 拿出一臺數(shù)據(jù)庫服務(wù)器(我們稱之為Master-02)資源做master-01主庫的從庫(之間做主從同步)；
- 兩臺主庫之間做高可用,可以采用keepalived等方案(一定要保證master-01同時也要作為keepalived的主)；
- 程序在調(diào)用主庫IP地址的地方寫為高可用的VIP地址；
- 所有提供服務(wù)的從服務(wù)器與master-02進行主從同步；
- 建議采用高可用策略的時候,當master-01出現(xiàn)問題切換到master-02的時候,即使master-01恢復了,也不要讓它去自動承接VIP地址,否則可能造成數(shù)據(jù)的混寫
這樣做可以在一定程度上保證主庫的高可用，在一臺主庫down掉之后,可以在極短的時間內(nèi)切換到另一臺主庫上(盡可能減少主庫宕機對業(yè)務(wù)造成的影響)，減少了主從同步給線上主庫帶來的壓力；但是也有幾個不足的地方：
- master-02可能會一直處于空閑狀態(tài)(其實完全可以讓它承擔一部分從庫的角色來負責一部分查詢請求的)；
- 這樣真正提供服務(wù)的從庫要等master-02先同步完了數(shù)據(jù)后才能去master-02上去同步數(shù)據(jù)，這樣可能會造成一定程度的同步延遲時間的加長；
- 如果master-01一旦恢復正常，會不會導致數(shù)據(jù)寫入混亂(這個可以在keepalived中設(shè)置響應(yīng)的規(guī)則,讓其不”奪權(quán)”，我們認為的去調(diào)整操作即可。
架構(gòu)的簡易圖如下：
具體實施方案：
一、在所有需要提供服務(wù)的服務(wù)器上安裝MySQL服務(wù)(建議源碼安裝)
1、yum安裝依賴包
- yum -y install cmake make gcc gcc-c++ ncurses-devel bison openssl-devel
2、添加MySQL所需要的用戶/組
- groupadd mysql
- useradd -g mysql -r mysql
3、下載MySQL源碼包
- wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.36.tar.gz
4、創(chuàng)建MySQL安裝所需要的目錄
- mkdir /data/mydata/{data,tmp,logs} –pv
5、解壓編譯安裝MySQL
- tar xf mysql-5.5.36.tar.gz
- cd mysql-5.5.36
- cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
- -DMYSQL_DATADIR=/data/mydata/data \
- -DSYSCINFDIR=/etc \
- -DWITH_INNOBASE_STORAGE_ENGINE=1 \
- -DWITH_ARCHIVE_STORAGE_ENGINE=1 \
- -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
- -DWITH_READLINE=1 \
- -DWITH_SSL=system \
- -DWITH_ZLIB=system \
- -DWITH_LIBWARP=0 \
- -DWITH_UNIX_ADDR=/tmp/mysql.sock \
- -DDEFAULT_CHARASET=uft8 \
- -DDEFAULT_COLLATTON=utf9_general_ci \
- make && make install
6、為MySQL提供啟動腳本
- cp support-files/mysql.server /etc/rc.d/init.d/mysqld
7、為master-01主庫提供配置文件(32G內(nèi)存較為保守(滿連接占用25G左右內(nèi)存)的配置文件)
- [client]
- port = 3306
- socket = /var/lib/mysql/mysql.sock
- default-character-set = utf-8
- [mysqld]
- server-id = 1
- port = 3306
- user = mysql
- basedir = /usr/local/mysql
- datadir = /data/mydata/data
- tmpdir = /data/mydata/tmp
- socket = /var/lib/mysql/mysql.sock
- skip-external-locking
- skip-name-resolve
- default-storage-engine = INNODB
- character-set-server = utf8
- wait-timeout = 100
- connect_timeout = 20
- interactive_timeout = 100
- back_log = 300
- myisam_recover
- event_scheduler = on
- log-bin=/data/mydata/logs/mysql-bin
- binlog_format = row
- max_binlog_size = 64M
- binlog_cache_size = 1M
- slave-net-timeout = 10
- skip-slave-start
- slow_query_log = 1
- long_query_time = 1
- slow_query_log_file = /data/mydata/mysqllog/logs/mysql.slow
- log-error = /data/mydata/mysqllog/logs/error.log
- max_connections = 1000
- max_user_connections = 1000
- max_connect_errors = 10000
- key_buffer_size = 32M #以MyISAM為主的服務(wù)器,要調(diào)大此值
- max_allowed_packet = 64M
- table_cache = 4096
- table_open_cache = 4096
- table_definition_cache = 4096
- sort_buffer_size = 512K
- read_buffer_size = 512K
- read_rnd_buffer_size = 512K
- join_buffer_size = 512K
- tmp_table_size = 64M
- max_heap_table_size = 64M
- query_cache_type = 0
- query_cache_size = 0
- bulk_insert_buffer_size = 16M
- thread_cache_size = 64
- thread_concurrency = 16 #CPU核數(shù)*2
- thread_stack = 256K
- innodb_data_home_dir = /data/mydata/data
- innodb_log_group_home_dir = /data/mydata/mysqllog/logs
- innodb_data_file_path = ibdata1:1G:autoextend
- innodb_buffer_pool_size = 16G
- innodb_buffer_pool_instances = 4
- innodb_additional_mem_pool_size = 16M
- innodb_log_file_size = 512M
- innodb_log_buffer_size = 32M
- innodb_log_files_in_group = 3
- innodb_flush_log_at_trx_commit = 2
- innodb_lock_wait_timeout = 10
- innodb_sync_spin_loops = 40
- innodb_max_dirty_pages_pct = 90
- innodb_support_xa = 1
- innodb_thread_concurrency = 0
- innodb_thread_sleep_delay = 500
- innodb_file_io_threads = 4
- innodb_concurrency_tickets = 1000
- log_bin_trust_function_creators = 1
- innodb_flush_method = O_DIRECT
- innodb_file_per_table #是否采用單表單空間
- innodb_write_io_threads = 8
- innodb_read_io_threads = 8
- innodb_io_capacity = 1000
- innodb_file_format = Barracuda #不開啟單表單空間,此選項無效
- innodb_purge_threads = 1
- innodb_purge_batch_size = 32
- innodb_old_blocks_pct = 75
- innodb_change_buffering = all
- transaction_isolation = READ-COMMITTED
- [mysqldump]
- quick
- max_allowed_packet = 32M
- [mysql]
- no-auto-rehash
- [myisamchk]
- key_buffer_size = 64M
- sort_buffer_size = 256M
- read_buffer = 2M
- write_buffer = 2M
- [mysqlhotcopy]
- interactive-timeout
- [mysqld_safe]
- open-files-limit = 10240
8、為master-02提供配置文件
- master-02的配置文件僅需在master-01上稍作修改
- server-id = 20
- log_slave_updates = 1 #添加(將復制事件寫入binlog,一臺服務(wù)器既做主庫又做從庫此選項必須要開啟)
- replicate-same-server-id=0 #添加(防止MySQL循環(huán)更新)
- relay_log_recovery = 1 #添加(MySQLrelay_log的自動修復功能)
9、為從庫提供配置文件(8G)
- [client]
- port = 3306
- socket = /var/lib/mysql/mysql.sock
- default-character-set = utf8
- [mysqld]
- server-id = 2
- port = 3306
- user = mysql
- basedir = /usr/local/mysql
- datadir = /data/mydata/data
- tmpdir = /data/mydata/tmp
- socket = /var/lib/mysql/mysql.sock
- skip-external-locking
- skip-name-resolve
- default-storage-engine = INNODB
- character-set-server = utf8
- wait-timeout = 100
- connect_timeout = 20
- interactive_timeout = 100
- back_log = 300
- myisam_recover
- event_scheduler = on
- log-bin=/data/mydata/logs/mysql-bin
- binlog_format = row
- max_binlog_size = 64M
- binlog_cache_size = 1M
- slave-net-timeout = 10
- relay_log_recovery = 1
- slow_query_log = 1
- long_query_time = 1
- slow_query_log_file = /data/mydata/mysqllog/logs/mysql.slow
- log-error = /data/mydata/mysqllog/logs/error.log
- max_connections = 500
- max_user_connections = 500
- max_connect_errors = 10000
- key_buffer_size = 32M #以MyISAM為主的服務(wù)器,要調(diào)大此值
- max_allowed_packet = 64M
- table_cache = 2048
- table_open_cache = 2048
- table_definition_cache = 2048
- sort_buffer_size = 128K
- read_buffer_size = 128K
- read_rnd_buffer_size = 128K
- join_buffer_size = 128K
- tmp_table_size = 16M
- max_heap_table_size = 16M
- query_cache_type = 0
- query_cache_size = 0
- bulk_insert_buffer_size = 16M
- thread_cache_size = 64
- thread_concurrency = 4 #CPU核數(shù)*2
- thread_stack = 128K
- innodb_data_home_dir = /data/mydata/data
- innodb_log_group_home_dir = /data/mydata/mysqllog/logs
- innodb_data_file_path = ibdata1:1G:autoextend
- innodb_buffer_pool_size = 2G
- innodb_buffer_pool_instances = 4
- innodb_additional_mem_pool_size = 4M
- innodb_log_file_size = 512M
- innodb_log_buffer_size = 16M
- innodb_log_files_in_group = 3
- innodb_flush_log_at_trx_commit = 2
- innodb_lock_wait_timeout = 10
- innodb_sync_spin_loops = 40
- innodb_max_dirty_pages_pct = 90
- innodb_support_xa = 1
- innodb_thread_concurrency = 0
- innodb_thread_sleep_delay = 500
- innodb_file_io_threads = 4
- innodb_concurrency_tickets = 1000
- log_bin_trust_function_creators = 1
- innodb_flush_method = O_DIRECT
- innodb_file_per_table #是否采用單表單空間
- innodb_write_io_threads = 8
- innodb_read_io_threads = 8
- innodb_io_capacity = 1000
- innodb_file_format = Barracuda #不開啟單表單空間,此選項無效
- innodb_purge_threads = 1
- innodb_purge_batch_size = 32
- innodb_old_blocks_pct = 75
- innodb_change_buffering = all
- transaction_isolation = READ-COMMITTED
- [mysqldump]
- quick
- max_allowed_packet = 32M
- [mysql]
- no-auto-rehash
- [myisamchk]
- key_buffer_size = 64M
- sort_buffer_size = 256M
- read_buffer = 2M
- write_buffer = 2M
- [mysqlhotcopy]
- interactive-timeout
- [mysqld_safe]
- open-files-limit = 10240
10、初始化MySQL
- /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/mydata/data/ --basedir=/usr/local/mysql
11、為啟動腳本賦予可執(zhí)行權(quán)限并啟動MySQL
- chmod +x /etc/rc.d/init.d/mysqld
- /etc/init.d/mysqld start
二、 配置master-01
1、添加主從同步賬戶
- mysql> grant replication slave on *.* to 'repl'@'192.168.237.%' idetified by '123456';
- mysql> flush privileges;
2、查看主庫的狀態(tài)
- mysql> show master status;
- +------------------+----------+--------------+------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +------------------+----------+--------------+------------------+
- | mysql-bin.000009 | 652 | | |
- +------------------+----------+--------------+------------------+
- 1 row in set (0.01 sec)
3、因為這是測試環(huán)境,可以保證沒數(shù)據(jù)寫入,否則需要先鎖表-->查看狀態(tài)-->備份數(shù)據(jù)-->查看狀態(tài)(保證沒變)-->解鎖表
三、 配置master-02
1、配置master-02為master-01的從
- #若是線上有數(shù)據(jù)需要先導入數(shù)據(jù)
- mysql> CHANGE MASTER TO
- -> MASTER_HOST='192.168.237.128',
- -> MASTER_PORT=3306,
- -> MASTER_USER='repl',
- -> MASTER_PASSWORD='123456',
- -> MASTER_LOG_FILE='mysql-bin.000009',
- -> MASTER_LOG_POS=652;
- Query OK, 0 rows affected (0.03 sec)
- mysql> start slave;
- mysql> show slave status \G
- Slave_IO_Running: Yes #確保為yes
- Slave_SQL_Running: Yes #確保為yes
2、配置master-02的同步用戶
- mysql> grant replication slave on *.* to 'repl’@’192.168.237.%' identified by '123456';
- mysql> flush privileges;
3、查看master-02的狀態(tài)
- mysql> show master status;
- +------------------+----------+--------------+------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +------------------+----------+--------------+------------------+
- | mysql-bin.000004 | 689 | | |
- +------------------+----------+--------------+------------------+
- 1 row in set (0.00 sec)
四、從庫根據(jù)上面步驟配置為master-02的從即可(為了節(jié)省篇幅不再一一贅述)
五、在master-01上創(chuàng)建一個數(shù)據(jù)庫測試同步效果
六、去master-02跟從庫上分別查看是否已經(jīng)同步過數(shù)據(jù)來
好了，至此數(shù)據(jù)同步已經(jīng)完成。關(guān)于keepalived實現(xiàn)雙主高可用，我會總結(jié)在下篇keepalived實現(xiàn)MySQL高可用文章中，屆時分享給各位。
責任編輯：黃丹
來源：
51TCO博客
