今天有人問我一個比較有趣的問題.能不能將不同機器上的不同庫中的表同步到同一個機器的同一個庫中.

表是不同的.而且對於slave這臺機子來說,這些表只用來讀.

同步不同庫的表很簡單了,用

replicate-do-table=db_name.tbl_name

多個不同機子上的不同庫的表同步到同一個機子的同一個庫中,再加上下面這句.

replicate-rewrite-db=from_name->to_name

配置slave 上的my.cnf

cat my.cnf

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin

[mysqld1]
port = 3306
socket = /tmp/mysql.sock1
skip-locking
pid-file=/usr/local/mysql/data/test-db1a.pid
datadir = /usr/local/mysql/data

log=/usr/local/mysql/data/test-db1.log
user = mysql
log-slow-queries=/usr/local/mysql/data/slowquery1.log
long_query_time = 2
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache = 32
query_cache_size = 32M
thread_concurrency = 2
max_connections=500
server-id = 2
master-host = 192.168.0.1
master-user = 'replcate'
master-password = '1234567890'
master-port = 3306
report-host = test-db3
master-connect-retry = 30
log-bin
log-slave-updates

replicate-rewrite-db=from_name->to_name

replicate-do-table=db_name.tbl_name

[mysqld2]
port = 3307
socket = /tmp/mysql.sock2
pid-file = /usr/local/mysql/data/test-db2b.pid
datadir = /usr/local/mysql/data
log=/usr/local/mysql/data/test-db2.log
user = mysql
log-slow-queries=/usr/local/mysql/data/slowquery2.log
long_query_time = 10
key_buffer = 128M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 1M
read_buffer_size = 1M
myisam_sort_buffer_size = 32M
thread_cache = 32
query_cache_size = 16M
thread_concurrency = 2
max_connections=300
server-id = 2
master-host = 192.168.0.2
master-user = 'repl'
master-password = '1234567890'
master-port = 3307
report-host = test-db3
master-connect-retry = 30
log-bin
log-slave-updates

replicate-rewrite-db=from_name->to_name

replicate-do-table=db_name.tbl_name

好了,看看上面配置,明白了兩個mysqld例項用了相同的庫表檔案,會不會有問題呢?

當然,使用不當的話會有問題,首先要符合兩個例項讀寫的物件沒有交叉.(同步過來的表唯一),其次選擇合適的存儲引擎,MYISM比innodb從理論上更適合這種應用．再次使用過程中對slave上的兩個mysqld例項，只使用一個進行本機其它庫表（非同步過來的）寫操作．

ok,這種解決方案已透過測試．希望能夠對你有所幫助．

come from：http://blog.chinaunix.net/u/4312/showart_320014.html

多主一從mysql replication同步表的大膽嘗試.

相關文章