说明：
本篇文章实现xm8开启多个实例，同时做为xm7,xm9的mysql从库，实时同步xm7的qq_data库和xm9的qq_data库。其中qq_data为innodb引擎，innodb引擎数据库数据都保存在ibdata1中。因为xm7和xm9的myql库都为qq_data，且都是innodb引擎的，所以replicate主从过来后会交叉覆盖，从而导致从服务器数据混乱。因此我们需要为每个实例配置配置不同的数据库存储目录。
环境：
mysql主1 xm7 192.168.1.7 /home/mysql/data xm7的qq_data数据库目录 mysql从 xm8 192.168.1.8 /home/mysql/data1存储xm7的qq_data；/home/mysql/data2存储xm9的qq_data mysql主2 xm9 192.168.1.9 /home/mysql/data xm9的qq_data数据库目录
实现：
1.修改xm7,xm9的/etc/my.cnf，开启binlog，开启主从
修改xm7的/etc/my.cnf文件，设置需要同步的数据库qq_data # vim /etc/my.cnf //在log-bin=mysql-bin.log后面添加以下内容 expire_logs_days=10 binlog_do_db=qq_data binlog_ignore_db=mysql 修改xm9的/etc/my.cnf文件，设置需要同步的数据库qq_data # vim /etc/my.cnf //在log-bin=mysql-bin.log后面添加以下内容 expire_logs_days=10 binlog_do_db=qq_data binlog_ignore_db=mysql 修改完成后分别重启xm7,xm9的mysql，使配置生产。
2.修改xm8的/etc/my.cnf，设置mysqld_multi与mysqld多实例，针对实例设置binlog，开启主从
从服务器xm8配置文件/etc/my.cnf，主要有两部分[mysqld_multi]和[mysqld]模块。[mysqld2]就是[mysqld1]的一个拷贝，区别于[mysqld1]的端口和文件位置。注意server-id不要重复
# grep -v '^#' /etc/my.cnf //以下为/etc/my.cnf完整配置 [client] [mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin user = root password = 123456 [mysqld1] port = 3306 socket = /tmp/mysql1.sock skip-external-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M log-error = /home/mysql/logs/mysql1_error.log pid-file = /usr/local/mysql/mysql1.pid datadir = /home/mysql/data1 log-bin=mysql1-bin binlog_format=mixed server-id = 70 master-host = 192.168.45.7 master-user = slave master-password = 123456 master-port = 3306 master-connect-retry = 5 replicate-do-db = qq_data replicate-ignore-db = mysql relay-log=relay-bin1 relay-log-index=relay-bin1.index relay-log-info-file=relay-log1.info innodb_data_home_dir = /home/mysql/data1/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /home/mysql/data1/ innodb_buffer_pool_size = 16M innodb_additional_mem_pool_size = 2M innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqld2] port = 3307 socket = /tmp/mysql2.sock skip-external-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M log-error = /home/mysql/logs/mysql2_error.log pid-file = /usr/local/mysql/mysql2.pid datadir = /home/mysql/data2 log-bin=mysql2-bin binlog_format=mixed server-id = 90 master-host = 192.168.45.9 master-user = slave master-password = 123456 master-port = 3306 master-connect-retry = 5 replicate-do-db = qq_data replicate-ignore-db = mysql relay-log=relay-bin2 relay-log-index=relay-bin2.index relay-log-info-file=relay-log2.info innodb_data_home_dir = /home/mysql/data2/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /home/mysql/data2/ innodb_buffer_pool_size = 16M innodb_additional_mem_pool_size = 2M innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
3.xm7,xm9给予xm8 replication,slave权限
xm7给予xm8 replication,slave的权限,账号为slave,密码123456 # /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql.sock > grant replication slave on *.* to slave@192.168.1.8 identified by '123456'; > quit 同样xm9给予xm8 replication,slave的权限,账号为slave,密码123456 # /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql.sock > grant replication slave on *.* to slave@192.168.1.8 identified by '123456'; > quit
4.xm8上新建两个/home/mysql/data1，/home/mysql/data2，分别初始化mysql数据库目录data1，data2
# /usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/home/mysql/data1/ --user=mysql # /usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/home/mysql/data2/ --user=mysql
5.分别停止xm7，xm8的数据库，当然锁定qq_data也可以
# /etc/init.d/mysqld stop //在xm7,xm9上分别运行，停止数据库
6.在xm8上rsync同步xm7，xm9的数据库文件qq_data到xm8上
# rsync -av root@192.168.45.7:/home/mysql/data/ibdata1 /home/mysql/data1/ //rsync xm7的qq_data数据库数据到xm8上 # rsync -av root@192.168.45.7:/home/mysql/data/qq_data /home/mysql/data1/ //rsync xm7的qq_data数据库表结构到xm8上 # rsync -av root@192.168.45.9:/home/mysql/data/ibdata1 /home/mysql/data2/ # rsync -av root@192.168.45.9:/home/mysql/data/qq_data /home/mysql/data2/
qq_data为innodb引擎，innodb引擎数据库数据都保存在ibdata1中
7.开启xm7,xm9的mysql，xm7，xm9分别运行以下命令
# /etc/init.d/mysql start
8.启动/停止xm8 mysql实例
# /usr/local/mysql/bin/mysqld_multi start 1,2 # /usr/local/mysql/bin/mysqld_multi stop 1,2 查看每个实例是否都起来了 # ps aux|grep mysql|grep -v grep 我们可以看到四个mysql进程，每个实例分别有两个进程 分别访问mysql实例1，实例2 # /usr/local/mysql/bin/mysql -uroot -p123456 -P3306 -S /tmp/mysql1.sock # /usr/local/mysql/bin/mysql -uroot -p123456 -P3307 -S /tmp/mysql2.sock
9.查看主从数据同步情况
在xm7上查看
# /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql.sock > show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000007 | 371 | qq_data | mysql | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
在xm9上查看
# /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql.sock > show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000006 | 537 | qq_data | mysql | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
在xm8从数据库上查看，因为xm8上运行两个实例，实例1对应xm7 qq_data，实例2对应xm9的qq_data，因此需要独立运行查看
# /usr/local/mysql/bin/mysql -uroot -p123456 -P3306 -S /tmp/mysql1.sock //实例1
> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.45.7
Master_User: slave
Master_Port: 3306
Connect_Retry: 5
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 371
Relay_Log_File: relay-bin1.000002
Relay_Log_Pos: 516
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: qq_data
Replicate_Ignore_DB: mysql
# /usr/local/mysql/bin/mysql -uroot -p123456 -P3307 -S /tmp/mysql2.sock //实例2
> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.45.9
Master_User: slave
Master_Port: 3306
Connect_Retry: 5
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 537
Relay_Log_File: relay-bin2.000004
Relay_Log_Pos: 682
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: qq_data
Replicate_Ignore_DB: mysql
可以看到Slave_IO_State为Waiting for master to send event，Slave_IO_Running，Slave_SQL_Running都是Yes。而且Master_Log_File与Read_Master_Log_Pos都分别与xm7，xm9对应。说明主从正确运行。
测试：
在xm7上创建users表 # /usr/local/mysql/bin/mysql -uroot -p123456 > use qq_data > CREATE TABLE `users` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(20) NOT NULL, `website` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 在xm9上创建users_log表 # /usr/local/mysql/bin/mysql -uroot -p123456 > user qq_data > CREATE TABLE `users_log` ( `user_log_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `info` varchar(200) DEFAULT NULL, `uid` mediumint(8) unsigned NOT NULL, PRIMARY KEY (`user_log_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
然后分别登陆xm8的两个实例(实例1对应xm7 qq_data，实例2对应xm9的qq_data)，就可以看到xm7 qq_data库上新建的users表，xm9 qq_data新建的users_log表。
附录：
mysql的编译安装可以参考：mysql 5.1 编译安装
mysql的主从配置可以参考：mysql主从同步备份
不错，正好用到。博主挺厉害。
谢谢