Linux下的MySQL主主複製

為什麼，會有mysql的主主複製。因為在一些高可用的環境中，mysql的主從不能滿足現實中的一些實際需求。比如，一些流量大的網站資料庫訪問有了瓶頸，需要負載平衡的時候就用兩個或者多個的mysql伺服器，而這些mysql伺服器的資料庫資料必須要保持一致，那麼就會用到主主複製。
mysql主從架構中其實就一個主在工作，而從就相當於一個備份機器，從通過日誌監測的方式來備份主庫上的資料而保證主庫的資料安全。在這種架構中如果從上的資料做了改變，主要資料是不會用任何變化的。因為mysql主從架構主要是mysql從監控mysql主的日誌變化來實現同步，相反的在這個架構中主並沒有監控從的日誌變化。所以，mysql從資料反生變化，主也就沒有什麼變化了。
通過上述描述，可以看到如果想實現主主複製，無非就是在mysql主從架構上讓mysql主實現監測從的日誌變化，從而實現兩台機器相互同步。(主從的架構前面有博文  )
實驗環境：兩台伺服器：
主機名稱：HA1,HA2(呵呵,這個主機名稱是英文縮寫High availability，高可用的意思)
ip:192.168.1.231
192.168.1.232
主機系統：CentOS6.4
mysql版本5.5.22
首先，看下HA1（192.168.1.231）的mysql設定檔
vim /etc/my.cnf
# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password      = your_password
port            = 3306
socket          = /usr/local/mysql/tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port            = 3306
socket          = /usr/local/mysql/tmp/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
log-slave-updates
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id      = 1

在這個設定檔中，需要特別注意的三處地方：
log-bin=mysql-bin：這個選項基本預設都是開著的，如果沒有開啟，可以手動開啟。
log-slave-updates：這個選項特別的重要它是為了讓slave也能充當master，同時也為了更好的服務於 m-m + s 的環境，保證slave掛在任何一台master上都會接收到另一個master的寫入資訊。當然不局限於這個架構，級聯複製的架構同樣也需要log-slave-updates的支援。
server-id = 1：這個ID為伺服器ID如果配置一樣會出現衝突，而不能複製
接著再看下HA2（192.168.1.232）的mysql設定檔
vim /etc/my.cnf
# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password      = your_password
port            = 3306
socket          = /usr/local/mysql/tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port            = 3306
socket          = /usr/local/mysql/tmp/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
log-slave-updates
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id      = 10
# Replication Slave (comment out master section to use this)

在HA2的mysql設定檔中，除了server-id不一樣，其他幾乎一模一樣。設定檔寫好後，我們把兩台伺服器上的mysql伺服器啟動起來。

更多詳情見請繼續閱讀下一頁的精彩內容：

推薦閱讀：

生產環境MySQL主主同步主鍵衝突處理

MySQL + KeepAlived + LVS 單點寫入主主同步高可用架構實驗

MySQL 主主同步配置

CentOS 6.3下MySQL主從複製筆記

1 2 下一頁