在MySQL中双机拷贝备份的实现是依靠日志文件来实现的，当主服务器的数据库有改变时，这些改变会被记录到日志中去，则日志一旦改变则会发送到从服务器，由从服务器来执行这些改变，在MySQL中设置双机拷贝备份其实比较容易，按如下步骤执行即可
1、确保主从服务器的MySQL库是兼容的，具体的兼容问题请查看MySQL官方文档，这里建议主从服务器用一样的数据库版本
2、创建拷贝用户，并赋予replication及slave权限，如grant replication slave on *.* to 'repl'@'%' identified by 'replpass'
3、执行flush tables with read lock来清空所有表和块的写入语句
4、为主服务器的my.cnf设置log_bin以及server_id选项，如
[mysqld]
log_bin=mysql_bin
server_id=1
并重新启动服务器，此时调用show master status将会出现类似如下内容：
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000001 |     3692 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
5、修改从服务器的my.cnf，设置server_id选项，如下所示：
[mysqld]
server_id=2
6、将主服务器和从服务器的数据库手动同步，这里建议最好直接清空数据库。
7、重启从服务器，执行如下语句：
change master to master_host='host ip', master_user='repl', master_password='replpass'
8、执行start slave
9、此时已经启动成功，如果执行show processlist\g命令在主服务器端会看到类似如下内容：
+----+------+---------------------+------+-------------+------+----------------------------------------------------------------+------------------+
| Id | User | Host                | db   | Command     | Time | State                                                          | Info             |
+----+------+---------------------+------+-------------+------+----------------------------------------------------------------+------------------+
| 7 | repl | 59.64.180.115:36349 | NULL | Binlog Dump | 8991 | Has sent all binlog to slave; waiting for binlog to be updated | NULL             |
| 20 | root | localhost           | NULL | Query       |    0 | NULL                                                           | show processlist |
+----+------+---------------------+------+-------------+------+----------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
而从服务器端可以看到如下内容：

+----+-------------+-----------+------+---------+-------+----------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                            | Info             |
+----+-------------+-----------+------+---------+-------+----------------------------------+------------------+
| 4 | system user |           | NULL | Connect | 14500 | Waiting for master to send event | NULL             |
| 6 | root        | localhost | NULL | Query   |     0 | NULL                             | show processlist |
+----+-------------+-----------+------+---------+-------+----------------------------------+------------------+
2 rows in set (0.00 sec)

发表于 @ 2009年05月30日　00:18:00 | 评论( loading... ) | 编辑| 举报| 收藏

旧一篇:搭建集群负载均衡系统 | 新一篇:MySQL Replication(复制)基本原理