1.环境
Server version: 5.5.24-0ubuntu0.12.04.1-log (Ubuntu)
mysql是通过 apt-get方式安装的
2.新建主从库的初始化数据库
mysql_install_db --datadir=/var/lib/mysql1 --user=mysql
mysql_install_db --datadir=/var/lib/mysql1 --user=mysql
如果执行出错，那请到/etc/apparmor.d/中编辑usr.sbin.mysqld,添加如下内容
##############mysql1###############
/var/log/mysql1.log rw,
/var/log/mysql1.err rw,
/var/lib/mysql1/ r,
/var/lib/mysql1/** rwk,
/var/log/mysql1/ r,
/var/log/mysql1/* rw,
#/var/run/mysqld/mysqld1.pid w,
#/var/run/mysqld/mysqld1.sock w,
#/run/mysqld/mysqld1.pid w,
#/run/mysqld/mysqld1.sock w,
##############mysql2###############
/var/log/mysql2.log rw,
/var/log/mysql2.err rw,
/var/lib/mysql2/ r,
/var/lib/mysql2/** rwk,
/var/log/mysql2/ r,
/var/log/mysql2/* rw,
#/var/run/mysqld/mysqld2.pid w,
#/var/run/mysqld/mysqld2.sock w,
#/run/mysqld/mysqld2.pid w,
#/run/mysqld/mysqld2.sock w,
然后重启下mysql，在执行以上语句即可成功创建初始数据库
2.在/etc/mysql目录下新建mysqld_multi.cnf
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = root //此用户名和密码要和数据库实例的用户名和密码对应，否则将无法关闭数据库实例
#password = multipass
log = /var/log/mysqld_multi.log
[mysqld1]
socket = /tmp/mysql1.sock
port = 3307
pid-file = /tmp/mysql1.pid
datadir = /var/lib/mysql1
user = mysql
#log = /var/log/mysql1.log
log-bin = /var/lib/mysql1/mysql-bin-3307
binlog_do_db = m_test1_3307
server-id = 1
[mysqld2]
socket = /tmp/mysql2.sock
port = 3308
pid-file = /tmp/mysql2.pid
datadir = /var/lib/mysql2
user = mysql
#log = /var/log/mysql2.log
#log-bin = /var/lib/mysql2/mysql-bin-3308
replicate_do_db = m_test1_3307
server-id = 2
3.启动数据库实例
mysqld_multi --defaults-extra-file=/etc/mysql/mysqld_multi.cnf start #启动
mysqld_multi --defaults-extra-file=/etc/mysql/mysqld_multi.cnf stop #关闭
mysqld_multi --defaults-extra-file=/etc/mysql/mysqld_multi.cnf report #查看状态
4.分别在两个实例中建要同步复制的数据库和表,如配置中的m_test1_3307
最好都用相同的数据库名和表名（略）
登陆如： mysql -uroot -p -h127.0.0.1 -P3307
5.主库上相关设置
新建用于同步复制的用户
grant replication slave on *.* to 'slave3307'@'127.0.0.1' identified by '3307';
+-----------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------------+----------+--------------+------------------+
| mysql-bin-3307.000004 | 107 | m_test1_3307 | |
+-----------------------+----------+--------------+------------------+
记住表中的参数，设置从库是需要用到
6.设置从库
change master to master_host='127.0.0.1',master_port=3307,master_user='slave3307',master_password='3307',master_log_file='mysql-bin-3307.000004',master_log_pos=107;
start slave;
show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: slave3307
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin-3307.000004
Read_Master_Log_Pos: 107
Relay_Log_File: mysql2-relay-bin.000010
Relay_Log_Pos: 258
Relay_Master_Log_File: mysql-bin-3307.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: m_test1_3307
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 566
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
出现以上信息，表示同步成功了，可以在主库表中插入数据，再查看从库对应的表中是否有相同的数据测试下。
注意事项：
设置关闭多个实例
mysql -u root -S /tmp/mysql1.sock -p
mysql> grant shutdown on *.* to root@'localhost' identified by 'multipass';
log-slave-updates #启用从库日志，这样可以进行链式复制
read-only=1 #从库是否只读，0表示可读写，1表示只读
relay-log-purge=1 #复制完的sql语句是否立即从中继日志中清除，1表示立即清除
posted on
