中小型企業(yè)可參考的類(lèi)MySQL雙主架構(gòu)方案

作者：燕十三 2014-04-11 13:47:14

主從同步一般都是一個(gè)主庫(kù),一旦主庫(kù)出現(xiàn)問(wèn)題,就有可能直接導(dǎo)致整個(gè)主從同步架構(gòu)崩盤(pán),雖然發(fā)現(xiàn)后也是可以慢慢恢復(fù)的,但是這個(gè)恢復(fù)時(shí)間對(duì)于很多公司來(lái)說(shuō)是難以接受的,今天的這篇博文就是主要給解決主庫(kù)單點(diǎn)故障這個(gè)問(wèn)題提供一個(gè)思路。

在企業(yè)中,一般系統(tǒng)架構(gòu)的瓶頸會(huì)出現(xiàn)在數(shù)據(jù)庫(kù)這一部分,mysql主從架構(gòu)在很大程度上解決了這部分瓶頸,但是在mysql主從同步的架構(gòu)也存在很多問(wèn)題；比如:1. 關(guān)于數(shù)據(jù)寫(xiě)入部分(也就是主庫(kù))往往很難做到擴(kuò)展,雖然很多大公司在邏輯業(yè)務(wù)方面就進(jìn)行對(duì)數(shù)據(jù)的拆分,比如商品庫(kù)存按照區(qū)域去拆分(一個(gè)區(qū)域走一個(gè)庫(kù)存也就是一個(gè)主庫(kù),然后定時(shí)同步總的庫(kù)存),按照商品類(lèi)型去劃分(一個(gè)類(lèi)型的商品走一套數(shù)據(jù)庫(kù)),但是這對(duì)于很多中小型公司來(lái)說(shuō)實(shí)現(xiàn)起來(lái)還是比較困難的； 2. 主從同步一般都是一個(gè)主庫(kù),一旦主庫(kù)出現(xiàn)問(wèn)題,就有可能直接導(dǎo)致整個(gè)主從同步架構(gòu)崩盤(pán),雖然發(fā)現(xiàn)后也是可以慢慢恢復(fù)的,但是這個(gè)恢復(fù)時(shí)間對(duì)于很多公司來(lái)說(shuō)是難以接受的,今天的這篇博文就是主要給解決主庫(kù)單點(diǎn)故障這個(gè)問(wèn)題提供一個(gè)思路:

主要思路是:

1.一臺(tái)主庫(kù)(我們稱(chēng)之為master-01)提供服務(wù),只負(fù)責(zé)數(shù)據(jù)的寫(xiě)入；

2.拿出一臺(tái)數(shù)據(jù)庫(kù)服務(wù)器(我們稱(chēng)之為Master-02)資源做master-01主庫(kù)的從庫(kù)(之間做主從同步)；

3.兩臺(tái)主庫(kù)之間做高可用,可以采用keepalived等方案(一定要保證master-01同時(shí)也要作為keepalived的主)

4.程序在調(diào)用主庫(kù)IP地址的地方寫(xiě)為高可用的VIP地址；

5.所有提供服務(wù)的從服務(wù)器與master-02進(jìn)行主從同步；

6.建議采用高可用策略的時(shí)候,當(dāng)master-01出現(xiàn)問(wèn)題切換到master-02的時(shí)候,即使master-01恢復(fù)了,也不要讓它去自動(dòng)承接VIP地址,否則可能造成數(shù)據(jù)的混寫(xiě)；

這樣做可以在一定程度上保證主庫(kù)的高可用,在一臺(tái)主庫(kù)down掉之后,可以在極短的時(shí)間內(nèi)切換到另一臺(tái)主庫(kù)上(盡可能減少主庫(kù)宕機(jī)對(duì)業(yè)務(wù)造成的影響),減少了主從同步給線(xiàn)上主庫(kù)帶來(lái)的壓力；但是也有幾個(gè)不足的地方:比如master-02可能會(huì)一直處于空閑狀態(tài)(其實(shí)完全可以讓它承擔(dān)一部分從庫(kù)的角色來(lái)負(fù)責(zé)一部分查詢(xún)請(qǐng)求的),2. 這樣真正提供服務(wù)的從庫(kù)要等master-02先同步完了數(shù)據(jù)后才能去master-02上去同步數(shù)據(jù),這樣可能會(huì)造成一定程度的同步延遲時(shí)間的加長(zhǎng)；3. 如果master-01一旦恢復(fù)正常,會(huì)不會(huì)導(dǎo)致數(shù)據(jù)寫(xiě)入混亂(這個(gè)可以在keepalived中設(shè)置響應(yīng)的規(guī)則,讓其不”奪權(quán)”,我們認(rèn)為的去調(diào)整操作即可

架構(gòu)的簡(jiǎn)易圖如下:

具體實(shí)施方案:

1. 在所有需要提供服務(wù)的服務(wù)器上安裝MySQL服務(wù)(建議源碼安裝)

1.1 yum安裝依賴(lài)包

yum -y install cmake make gcc gcc-c++  ncurses-devel bison openssl-devel

1.2 添加MySQL所需要的用戶(hù)/組

groupadd mysql   
useradd -g mysql -r mysql

1.3 下載MySQL源碼包

wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.36.tar.gz

1.4 創(chuàng)建MySQL安裝所需要的目錄

mkdir /data/mydata/{data,tmp,logs} –pv

1.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

1.6 為MySQL提供啟動(dòng)腳本

cp support-files/mysql.server /etc/rc.d/init.d/mysqld

1.7 為master-01主庫(kù)提供配置文件(32G內(nèi)存較為保守(滿(mǎn)連接占用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    #不開(kāi)啟單表單空間,此選項(xiàng)無(wú)效   
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

1.8 為master-02提供配置文件

master-02的配置文件僅需在master-01上稍作修改   
server-id = 20   
log_slave_updates = 1    #添加(將復(fù)制事件寫(xiě)入binlog,一臺(tái)服務(wù)器既做主庫(kù)又做從庫(kù)此選項(xiàng)必須要開(kāi)啟)   
replicate-same-server-id=0 #添加(防止MySQL循環(huán)更新)   
relay_log_recovery = 1   #添加(MySQLrelay_log的自動(dòng)修復(fù)功能)

1.9 為從庫(kù)提供配置文件(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     #不開(kāi)啟單表單空間,此選項(xiàng)無(wú)效   
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

1.10 初始化MySQL

/usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/mydata/data/ --basedir=/usr/local/mysql

1.11 為啟動(dòng)腳本賦予可執(zhí)行權(quán)限并啟動(dòng)MySQL

chmod +x /etc/rc.d/init.d/mysqld  
/etc/init.d/mysqld start

2. 配置master-01

2.1 添加主從同步賬戶(hù)

mysql> grant replication slave on *.* to 'repl'@'192.168.237.%' idetified by '123456'；   
mysql> flush privileges；

2.2 查看主庫(kù)的狀態(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)

2.3 因?yàn)檫@是測(cè)試環(huán)境,可以保證沒(méi)數(shù)據(jù)寫(xiě)入,否則需要先鎖表-->查看狀態(tài)-->備份數(shù)據(jù)-->查看狀態(tài)(保證沒(méi)變)-->解鎖表

3. 配置master-02

3.1 配置master-02為master-01的從

#若是線(xiàn)上有數(shù)據(jù)需要先導(dǎo)入數(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

3.2 配置master-02的同步用戶(hù)

mysql> grant replication slave on *.* to 'repl’@’192.168.237.%' identified by '123456'；   
mysql> flush privileges；

3.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)

4. 從庫(kù)根據(jù)上面步驟配置為master-02的從即可(為了節(jié)省篇幅不再一一贅述)

5. 在master-01上創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)測(cè)試同步效果