Windows 下的 MySQL 主从关系配置

MySQL 主从复制是其最重要的功能之一。主从复制是一台服务器充当主服务器，另一台或多台服务器充当从服务器，主机自动复制到从机。对于多级复制，数据服务器即可充当主机，也可充当从机。MySQL 复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。

要做 MySQL 主从关系的设置，需要有两台 MySQL 主机。所以在开始配置之前需要准备两个 MySQL 服务器，可以是在虚拟机里安装，也可以在真实机器上安装。

最好是在主/从服务器上安装相同的 MySQL 版本。或者是 slave 上的 MySQL 版本要高于 Master 上的版本。另外，推荐是选择较新的正式的 MySQL 版本。

MySQL 手册中对配置主从复制架构的提示和建议：

1. We recommend using the most recent MySQL version available because replication capabilities are continually being improved.
2. We also recommend using the same version for both the master and the slave.
3. We recommend upgrading masters and slaves running alpha or beta versions to new (production) versions.
4. Replication from a 5.0.3 master to a 5.0.2 slave will fail; from a 5.0.4 master to a 5.0.3 slave will also fail.
5. In general, slaves running MySQL 5.0.x may be used with older masters(even those running MySQL 3.23, 4.0, or 4.1), but not the reverse.
6. You cannot replicate from a master that uses a newer binary log format to a slave that uses an older format (for example, from MySQL 5.0 to MySQL 4.1.)
7. The binary log format as implemented in MySQL 5.0 is considerably different from that used in previous versions. Major changes were made in MySQL 5.0.3(for improvements to handling of character sets and LOAD DATA INFILE) and 5.0.4 (for improvements to handling of time zones).

一、操作环境说明

主机(master)：
Windows Server 2003，IP：10.0.0.101
MySQL版本：mysql-essential-5.1.47-win32.msi
MySQL 安装路径：D:\MySQLServer5.1

从机(slave)：
Windows Server 2003，IP：10.0.0.102
MySQL版本：mysql-essential-5.1.47-win32.msi
MySQL 安装路径：D:\MySQLServer5.1

其他相关软件：
MySQL 管理客户端：
Navicat 8 for MySQL(Windows)

本文的实验环境是在 Vmware workstation 7.0 虚拟机下搭建的。

二、配置 MySQL 主机(master)：

首先打开 MySQL 服务器的配置文件 my.ini(Linux 下是文件 my.cnf)，一般都在 MySQL 安装目录下，本实验中在 D:\MySQLServer5.1 下。记得先备份以下原来的配置文件。然后在 my.ini 文件末尾加入下面的代码：

#Master Config
server-id = 1
log-bin = mysql-bin
binlog-do-db = test
binlog-ignore-db = mysql

注意上面这几项配置，如果在配置文件中已经存在就不用添加了(MySQL5.0较早的版本中有，本实验的5.1.47版本没有)。注意都是小写字母。

上面的这些配置的含义:

- server-id 顾名思义就是服务器标识id号了
- log-bin 指定日志类型
- binlog-do-db 是你需要复制的数据库名称，如果有多个就用逗号“,”分开
- binlog-ignore-db 是不需要复制的数据库名称，如果有多个就用逗号“,”分开

然后添加一个用户，用于从服务器访问：

很多教程使用 MySQL 命令行(CLI)命令来操作，我们这里使用 MySQL 图形界面的管理客户端 Navicat 来操作，结果是一样的。

连接到 10.0.0.101 上的 MySQL 服务器，进入用户管理界面，建立一个用户 slave，密码也是 slave。主机填写“10.%”，意味着只允许该账户从 IP 地址开头为“10”的主机上连接。当然填写“%”也可以。

账户权限：需要为 slave 账户开放“全局许可权”：Reload、Super、Replication Slave、Replication Client。因为配置中是对 test 数据库进行复制，所以还要开放该对数据库 test 的一切权限。

用 MySQL 命令行操作的话，请参考以下形式的语句：

grant usage on *.* to 'slave'@'10.%' identified by 'slave';

该命令就是添加一个用户了。“@”前面的“slave”是用户名，后面的是有效的域，“10.%”也就是说以“10”开头的任意IP都能使用这个帐号访问这台 MySQL 服务器，也可以设置成固定的 I P比如“10.0.0.102”。by 后面的“slave”是密码。

MySQL 主服务器配置完毕。把 MySQL 服务重新启动一下，在命令行窗口中执行：

net stop mysql //关闭MySQL服务
net start mysql //开启MySQL服务

当然也可以在 Windows 服务管理控制台操作 MySQL 服务。

三、配置 MySQL 从机(slave)：

打开从服务器的配置文件 my.ini，同样在末尾加入下面的代码：

# Slave Config
server-id = 2
master-host = 10.0.0.101
master-port = 3306
master-user = slave
master-password = slave
replicate-do-db = test

下面来解释下：

Server-id 从服务器标识id号
Master-host 主服务器的 IP 地址
Master-port 主服务器的端口
Master-user 用于和主服务器同步数据的用户名，我们在配置主服务器时已经添加了，即slave
Master-password 就是 Master-user 的密码
Replicate-do-db 要备份的数据库名称

注意，如果 my.ini 文件中原来就有一个 server-id=1 的配置，需要删除掉(MySQL5.0较早的版本中有，本实验的5.1.47版本没有)。配置语句也都是小写字母。

重启从服务器上的 MySQL 服务。

四、测试

登录到从服务器命令行界面，执行以下命令，查看从服务器的配置是否正确：

Show slave status;

执行后会显示很多东西, 然后找到下面这两项：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果两项都是 Yes 那么就配置成功了。如果不全是 Yes，则有可能是主服务器上 slave 账户的权限配置问题，需要反复检查。

接着执行命令：
load data from master;

这个命令就是从主服务器载入数据。本命令用于对主服务器进行快照，并拷贝到从属服务器上。它可以更新MASTER_LOG_FILE和MASTER_LOG_POS的值，这样，从属服务器就可以从正确的位置开始进行复制。使用--replicate-*-do-*和--replicate-*-ignore-*选项指定的表和数据库排除规则均被兑现。--

如果命令执行后显示信息“Query OK, 0 rows affected (0.13 sec)”，则说明正确执行了数据复制。

本“load data from master”语句只对 MyISAM 类型的表起作用。如果试图载入一个非MyISAM表，会导致以下错误：
ERROR 1189 (08S01): Net error reading from master

并且当拍摄快照时，会获得对主服务器的全局读取锁定。在载入操作期间，该锁定会阻止对主服务器的更新。

然后，在主服务器建表和增删记录，应该在从服务器中看到对应的变化。说明配置成功了。

五、MySQL 主从复制错误的处理

MySQL 主从复制架构配置成功后，首先应该在主服务器上锁住表，然后备份数据，同步到从服务器上。

flush tables with read lock;
-- copy data files ...
unlock tables;

或者是通过 mysqldump 工具来备份和同步数据：
mysqldump --user=root --password=xxxx --master-data=1 --all-databases > dbsnapshot.sql

然后主从复制开始正确工作。

如果主服务器已经存在一定数量的数据，或从服务器由于某些原因中断了同步的进程，这个时候可以按照以下的步骤来重新同步：

1、从主服务器导出欲同步的数据库；
2、在从服务器运行 stop slave 停止同步；
3、导入第1步的数据库到从服务器；
4、在从服务器上运行 start slave 开始同步。

作者：张庆(网眼) 西安 PHP 教育培训中心 2010-6-8
来自“网眼视界”：http://blog.why100000.com
“十万个为什么”电脑学习网：http://www.why100000.com