能否將不同機器上的不同庫中的表同步到同一個機器的同一個庫中？表是不同的.而且對于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,這種解決方案已通過測試．

來源： http://blog.chinaunix.net/uid-220350-id-149202.html