配置Mysql server 5.5 的双机备份，也就是master-slave模式。本例子还是一个多database复制的情况。
现在有两个database在同一台mysql server，也就是master，各自有自己的user访问和操作，用于不同的应用程序。这两个database都要通过replication配置，实时复制到另一台mysql server上，也就是slave。
配置步骤：
1. 从master上到处现有的数据
mysqldump -R -E -uroot -p ADB > masteradb.sql;
mysqldump -R -E -uroot -p BDB > masterbdb.sql;
2. 为master和slave定制配置文件/etc/my.cnf，然后启动mysql
vi /etc/my.cnf
###详细内容见附录
service mysql restart
3. 在slave上创建database.
create database ADB;
create database BDB;
4. 在master和slave上配置user和privilege;
GRANT ALL PRIVILEGES ON ADB.* TO usera@`%` IDENTIFIED BY 'pass' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON ADB.* TO usera@`localhost` IDENTIFIED BY 'pass' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON BDB.* TO userb@`%` IDENTIFIED BY 'pass' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON BDB.* TO userb@`localhost` IDENTIFIED BY 'pass' WITH GRANT OPTION;
flush privileges;
5. 在master上配置用于数据复制的user和privilege;
grant SUPER ON *.* TO 'auser'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION;
grant SUPER ON *.* TO 'buser'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION;
GRANT SELECT ON mysql.proc TO 'auser'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION;
GRANT SELECT ON mysql.proc TO 'buser'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION;
GRANT REPLICATION SLAVE ON *.* TO repluser IDENTIFIED BY 'pass';
flush privileges;
6. 在master上运行下列命令，得到master的状态，包含binlog的文件名和当前位置
mysql> show master status;
+------------------+----------+--------------------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------------------------+------------------+
| mysql-bin.000001 | 000173| ADB,BDB | |
+------------------+----------+--------------------------------+------------------+
1 row in set (0.02 sec)
7. 在slave配置复制来源，包括hostname，user，password，binlog文件名，位置。
CHANGE MASTER TO MASTER_HOST='10.224.106.225', MASTER_USER='repluser', MASTER_PASSWORD='pass', master_log_file='mysql-bin.000001', master_log_pos=173;
start slave;
8. 运行下列命令查看slave的当前状态。
show slave status\G
结果出现
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
表示数据同步复制已经成功运行。
提示：如果show slave status\G 出现了错误，运行下列命令跳过，在重启slave
SET GLOBAL SQL_SLAVE_SKIP_COUNTER =100;start slave;
show slave status\G;
stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER =0;start slave;
附录：
master my.cnf配置
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock [mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer = 384M
max_allowed_packet = 32M
table_open_cache = 5120
key_buffer_size = 64M
sort_buffer_size = 512M
net_buffer_length = 8K
read_buffer_size = 16M
read_rnd_buffer_size = 128M
myisam_sort_buffer_size = 128M query_cache_size = 256M
tmp_table_size = 128M
max_heap_table_size = 128M
thread_concurrency = 8
max_connections=500
group_concat_max_len=1048576 max_sp_recursion_depth=255 slow_query_log_file=/spare/mysql/slow_queries.log
long_query_time = 5 binlog_cache_size = 2M
join_buffer_size = 32M
thread_cache_size = 16
query_cache_limit = 2M
transaction_isolation = REPEATABLE-READ
log-bin=mysql-bin
auto_increment_increment = 2
auto_increment_offset = 1
server-id = 1 binlog-do-db = ADB
binlog-do-db = BDB log_slave_updates = 1
relay-log=RELAY_LOCALHOST-relay-bin innodb_data_home_dir = /spare/mysql/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /spare/mysql/ innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 8G
innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 8M innodb_log_file_size = 250M max_binlog_size=100M
expire_logs_days=3
sync_binlog=1 #Ensure all writes to binary are flushed to disk in a timely manner
binlog-format=ROW [mysqldump]
quick
max_allowed_packet = 256M [mysql]
no-auto-rehash [isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M [myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M [mysqlhotcopy]
interactive-timeout
slave my.cnf配置
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock [mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer = 384M
max_allowed_packet = 32M
table_open_cache = 5120
key_buffer_size = 64M
sort_buffer_size = 512M
net_buffer_length = 8K
read_buffer_size = 16M
read_rnd_buffer_size = 128M
myisam_sort_buffer_size = 128M query_cache_size = 256M
tmp_table_size = 128M
max_heap_table_size = 128M
thread_concurrency = 8
max_connections=500
group_concat_max_len=1048576 max_sp_recursion_depth=255 slow_query_log_file=/spare/mysql/slow_queries.log
long_query_time = 5 binlog_cache_size = 2M
join_buffer_size = 32M
thread_cache_size = 16
query_cache_limit = 2M
transaction_isolation = REPEATABLE-READ
log-bin=mysql-bin
auto_increment_increment = 2
auto_increment_offset = 1 # the slave offset keep same as master
server-id = 2 replicate-do-db = ADB
replicate-do-db = BDB log_slave_updates = 1
relay-log=RELAY_LOCALHOST-relay-bin innodb_data_home_dir = /spare/mysql/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /spare/mysql/ innodb_additional_mem_pool_size = 16M
innodb_autoextend_increment = 256M
innodb_buffer_pool_size = 8G
innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M
####innodb_log_buffer_size = 32M
innodb_log_file_size = 250M max_binlog_size=100M
expire_logs_days=3
sync_binlog=1 #Ensure all writes to binary are flushed to disk in a timely manner
binlog-format=ROW [mysqldump]
quick
max_allowed_packet = 256M [mysql]
no-auto-rehash [isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M [myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M [mysqlhotcopy]
interactive-timeout