MYSQL Master/Slave 主从备份和双向数据同步_

Master/Slave配置]:
1:打开 Master 的 my.ini 在 [mysqld] 下增加以下行:
log-bin = mysql-bin #因为MYSQL是基于二进制的日志来做同步的,每个日志文件大小为 1G
server-id = 1 #主标服务标识号,必需唯一
binlog-do-db = bb_com #要同步的库名
binlog-do-db = bd_com
binlog-ignore-db=mysql,test #不记录日志的库,即不需要同步的库
2:重启 Master
net stop mysql
net start mysql
3:连接 Master 查看 master 状态,出现以下表示成功了，否则查看 mysql.err 记录
mysql -uroot -proot
mysql> show master status;
+——————+———-+—————+——————————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+—————+——————————-+
| mysql-bin.000001 | 98 | bb_com,bd_com | mysql,test |
+——————+———-+—————+——————————-+
4:创建一个从slave服务器访问master服务器的有权限的账号供slave主机访问.
grant replication slave,reload,super,select on *.* to slave@’%’ identified by ‘123456′;
flush privileges;
5:备份 Master 中要同步的二个库 bb_com,bd_com ,注意：如果你同步的库中的表的类型都是 MyISAM 则可以直接复制文件的.
A：mysql> flush tables with read lock; #先锁住 mysql3306 不让其有写入操作
B：再打开一个新窗口执行以下命令.
D:\>mysqldump -uroot -proot -P3306 –databases bb_com bd_com > backdb.sql
(这个窗口不要关，否则 lock 会失效)
6:打开 Slave 目录下的 my.ini 在 [mysqld] 下增加以下行:
server-id = 2
log-bin = mysql-bin #如果不做双向同步则slave机不需要写二进制日志的
log-slave-updates
#master-host = 192.168.109.73(不过据网上消息介绍，Mysql版本从5.1.7以后开始就不支持“master-host”类似的参数;
)
#master-user = slave
#master-password = 123456
#master-port = 3306
replicate-ignore-db = mysql #不同步的数据库
replicate-do-db = bb_com #要同步的数据库
replicate-do-db = bd_com
7:重启 Slave
net stop mysql
net start mysql
8:先把 Master 的数据导入到 Slave 来
mysql -uroot -proot < backdb.sql
(如果同步的二个数据库表都是 MyISAM 的话则可以用: load data from master;)
mysql -uroot -proot
mysql> stop slave; #停止 slave;
#告诉 slave 当前 master 的值
mysql> change master to master_host=’192.168.109.73′,master_user=’slave’,master_password=’123456′,master_port=3306,master_log_file=’mysql-bin.000001′,master_log_pos=98;
mysql> start slave; #开始同步
9:检测 Slave 状态
mysql> show slave status\G;
#最重要的三个字段为以下值时成功了
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
10:返回到Master机
mysql> unlock tables;
至此 Master/Slave 应该配置好了,你自己在 Master 上要同步的数据库表插入一条记录
然后在 Slave 中应该可以 select 看到了.
[双向同步配置]:
双向其实就是在 Master/Slave 成功的基础上,反过来把 Slave看到Master,把 Master看到Slave.
1:打开 Slave 的 my.ini 在 [mysqld] 节中加入以下内容
# =======BEGIN 双向备份=======
binlog-do-db = bb_com
binlog-do-db = bd_com
binlog-ignore-db=mysql,test
# =======END 双向备份=========
2:重启 Slave 后查看 slave 状态是否正常，记录下 master 状态.
net stop mysql
net start mysql
mysql -uroot -proot
mysql> show master status;
+——————+———-+—————+——————————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+—————+——————————-+
| mysql-bin.000005 | 98 | bb_com,bd_com | mysql,test |
+——————+———-+—————+——————————-+
4:创建一个从Master服务器访问这台Slave服务器的账号.
grant replication slave,reload,super,select on *.* to slave@’%’ identified by ‘123456′;
flush privileges;
5:打开 Master 的 my.ini 在 [mysqld] 节中加入以下内容
# =======BEGIN 双向备份=======
log-slave-updates
master-host = 192.168.203.128
master-user = slave
master-password = 123456
master-port = 3306
replicate-ignore-db = mysql #不同步的数据库
replicate-do-db = bb_com #要同步的数据库
replicate-do-db = bd_com
# =======END 双向备份=========
6:重启 Master 并设置 master信息(其实就是 Slave上的信息)
net stop mysql
net start mysql
mysql> slave stop;
mysql> change master to master_host=’192.168.203.128′,master_user=’slave’,master_password=’123456′,master_port=3306,master_log_file=’mysql-bin.000005′,master_log_pos=98;
mysql> slave start;
好了，打完收工！现在分别在Master,Slave中对同步的库进行任何操作都可以同步了！
[经验总结]：
1：要同步的数据库多个是必需分工写,像这样：
binlog-do-db = bb_com
binlog-do-db = bd_com
我今天下午开始时是这样写的:
binlog-do-db = bb_com,bd_com
FUCK MYSQL竟然不报错,连 show status 时效果都一样，害得我找原因试了一下午！
2：当我们往 Slave机做数据镜像时,如果表的类型全部是 MyISAM 时,
直接使用 tar 进行文件级的复制效果更好;如果是 InnoDB 则只能只能用 mysqldump了
3：当发现 Slave机有数据不同步时,对于表类型为 MyISAM 时可以用以下方式同步:
slave stop;
load data from master;
slave start;