一步一步教你如何实现MySQL双机数据同步

假设目前有两台MySQL数据库服务器，如何实现两台机器之间的数据同步？很多朋友在第一次接触MySQL双机同步需求时可能会感到困惑，不知从何入手。其实这是MySQL本身支持的功能之一。本文提供了关于MySQL主从同步的初步思路，供大家参考。 编者按：很多朋友第一次接触MySQL双机同步需求时可能会感觉不知道从何入手。其实这是MySQL本身支持的功能之一。本文提供了关于MySQL主从同步的初步思路，供大家参考。 1、需求问题 假设目前有两台MySQL数据库服务器，如何实现两台机器之间的数据同步？即在一台机器上修改数据库后，修改的信息会在另一台机器上同步更新。 2. 解决方案 查资料发现MySQL支持单向异步复制。在复制过程中，一台服务器充当主服务器，一台或多台其他服务器充当从服务器。 原理是这样的： 主服务器将更新写入二进制日志文件并维护文件索引以跟踪日志轮换。这些日志记录发送到从属服务器的更新。当从站连接到主站时，它会通知主站从站在日志中读取的最后一次成功更新的位置。从服务器接受此后发生的任何更新，然后阻塞并等待主服务器收到新更新的通知。 2.1 测试环境 主站：192.168.7.67 (CentOS 5.5 x86_64) MySQL 版本：5.0.77 从站：192.168.56.103 (CentOS 5.3 i386) MySQL 版本：5.0.45 评论： Master和Slave端的MySQL版本最好相同，或者Master端的版本高于Slave端。 2.2 配置流程 2.2.1 主控端设置 启动MySQL服务并创建新的测试数据库abc：root@camlit ~: /etc/init.d/mysqld start www.sychzs.cn@camlit ~: mysql -u root -p 输入密码: xxxx 欢迎使用 MySQL 监视器。命令以 ; 结尾或\g。您的 MySQL 连接 ID 为 3 服务器版本：5.0.77 源代码分发 输入“help;”或“\h”寻求帮助。键入“\c”以清除缓冲区。 mysql> 创建数据库 abc; Query OK, 1 row受影响(0.31 sec) ## #创建用户进行同步，指定只能在192.168.56.103登录 ###REPLICATION SLAVE: 启用复制从机从主机读取二进制日志事件 mysql> grant将 *.* 上的从站复制到由“test1”标识的“test1”@“192.168 .56.103”；查询正常，0 行受影响（0.16 秒） 修改配置文件： root@camlit ~: vi /etc/my.cnf 注意：修改配置文件前请先备份。[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql old_passwords=1 ##添加以下内容 server_id=1###1表示master，2表示slave binlog- do -db=abc ###需要同步的数据库，如果有多个数据库，每个数据库一行 binlog-ignore-db=mysql###不需要同步的数据库 log-bin= mysql-bin [mysqld_safe] 日志错误=/var/log/mysqld.log pid-file=/var/run/mysqld/www.sychzs.cn 重新启动服务： root@camlit ~: /etc/init.d/mysqld restart 2.2.2 从机端设置 创建与master相同的数据库：abc 输入密码： 欢迎使用 MySQL 监视器。命令以 ; 结尾或\g。您的 MySQL 连接 ID 是 5 服务器版本：5.0.45-log 源分布 输入“help;”或“\h”寻求帮助。键入“\c”以清除缓冲区。 mysql> 创建数据库 abc;查询正常，1 行受影响（0.31 秒） 修改配置文件： root@test2 ~: vi /etc/my.cnf[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql old_passwords=1 ###添加以下内容 server_id=2 log-bin=mysql-bin master-host= 192.168 .7.67 master-user=test1 master-password=test1 master-port=3306 master-connect-retry=10 ###连接数replicate-do-db=abc ###接受的数据库名称replicate-ignore-db= mysql ###不接受数据库 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/www.sychzs.cn 重新启动服务： root@test2~: /etc/init.d/mysqld restart 评论： 配置成功后，会在mysql目录（/var/lib/mysql/）下生成www.sychzs.cn文件。如果要更改从属设置，必须先删除该文件才能生效。 进入mysql并输入以下命令：root@test2~: mysql -u root -p 输入密码: 欢迎使用 MySQL 监视器。命令以 ; 结尾或\g。您的 MySQL 连接 ID 是 4 服务器版本：5.0.45-log 源分布 输入“help;”或“\h”寻求帮助。键入“\c”以清除缓冲区。 mysql> 从属启动； Query OK, 0 rows受影响, 1 warning (0.00 sec) ###查看同步mysql > show Slave status;或显示主状态； 2.3 结果测试 在Master端对数据库abc进行一些操作，如下图：www.sychzs.cn@camlit ~:mysql -u root -p 输入密码: 欢迎使用 MySQL 监视器。命令结尾为;或\g。您的 MySQL 连接 ID 是 3 服务器版本：5.0.77-log 来源分布 键入“help;”或“\h”寻求帮助。输入“\c”以清除缓冲区。 mysql> 使用 abc;数据库已更改 mysql> 创建表 test1 (IP VARCHAR(20),USER VARCHAR(100), MAIL VARCHAR(100));查询正常，0 行受影响（1.20 秒） mysql> insert into test1(IP,USER,MAIL) values('192.168.7.66', 'test', 'support@www.sychzs.cn');查询正常，1 行受影响（0.06 秒） 在Slave端查看是否能够更新：root@test2 ~:mysql -u root -p 输入密码：欢迎使用 MySQL 监视器。命令结尾为;或\g。您的 MySQL 连接 ID 是 6 服务器版本：5.0.45-log 来源分布 键入“help;”或“\h”寻求帮助。输入“\c”以清除缓冲区。 mysql> 显示数据库； + |数据库   | + |信息架构 | |富| | mysql | mysql |测试|  | |ABC | + 集合中 5 行（0.00 秒） mysql> 使用 abc;读取表信息以完成表和列名称您可以关闭此功能，以使用 -A 数据库已更改 mysql> 显示表来更快地启动； + |表_in_abc | + |测试1 | + 集合中的 1 行（0.03 秒） mysql> select * from test1; + |知识产权 |用户 |邮件| + | 192.168.7.66 |测试| support@www.sychzs.cn | + 1 行一组（0.00 秒） 从上面的结果可以看到Master端的数据可以同步到Slave端里面。说明此时主从数据库的同步问题已经成功解决。 #p# 三、补充资料 关于如何连接到远程MySQL问题，可以采取下面的步骤： 首先先登录到远程机器：www.sychzs.cn@camlit ~: ssh root@192.168.56.103 密码: xxx root@test2 ~: 编辑配置文件： root@test2 ~: vi /etc/my.cnf 添加以下行： [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql old_passwords=1 bind-address=192.168.56.103###这个IP地址是MySQL机器的IP地址[ mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pi 重新启动服务： root@test2 ~: /etc/init.d/mysqld restart 创建测试数据库：root@test2 ~: mysql -u root -p 输入密码： 欢迎使用 MySQL 监视器。命令以 ; 结尾或\g。您的 MySQL 连接 ID 为 2 服务器版本：5.0.45 源代码分发 输入“help;”或 '\h' 寻求帮助。键入“\c”以清除缓冲区。 mysql> 创建数据库 foo ;查询正常，1行受影响（0.00秒）###添加用户test123从任何主机登录MySQL mysql>使用授予选项将* .*上的所有权限授予'test123'@'%'标识的'test123'； Query OK, 0 rows受影响 (0.00 sec) ###Add user test1 to log in to MySQL from 192.168.7.67 host mysql> grant all attempts on foo .* to 'test1'@'192.168.7.67' recognizes by 'test1'具有授予选项；查询正常，0 行受影响（0.00 秒） 如果有防火墙设置，可以按如下方式设置： root@test2 ~: iptables -A INPUT -i eth0 -s 192.168.7.67 -p tcp root@test2~: /etc/init.d/iptales 保存 最后可以在客户端输入以下命令远程访问MySQL数据库：www.sychzs.cn@camlit ~: mysql -u test1 -h 192.168.56.103 -p 输入密码：欢迎使用 MySQL 监视器。命令结尾为;或\g。您的 MySQL 连接 ID 是 13 服务器版本：5.0.45 源发行版 输入“help;”或“\h”寻求帮助。输入“\c”以清除缓冲区。 www.sychzs.cn内容如下：使用严格；使用 DBI；使用 POSIX“strftime”；我的$主机           = “192.168.56.103”;我的$user           =“test1”;我的$passwd         =“test1”;我的$端口           =“3306”；我的$max_behind =“120”；我的$check_log      =“./mysql_check.log”; open (FH,">> $check_log") or die $!;我的$dbh=&MysqlConnect($host,$port,$user,$passwd);我的$slave_status=&MysqlQuery($dbh,'显示从属状态');打印 FH“错误：SQL 查询错误：”。 $dbh->errstr;我的$slave_IO              = $slave_status->{Slave_IO_Running};我的$slave_SQL             = $slave_status->{Slave_SQL_Running};我的 $seconds_behind_master = $slave_status->{Seconds_Behind_Master};我的 $now_time              = POSIX::strftime ("[%Y-%m-%d %H:%M:%S]", 当地时间); print“检查从属 MySQL 状态...\n”；打印“_”x 50，“\n”；打印“时间：\t\t\t$now_time\n”；打印“从机 IO 正在运行：\t\t$slave_IO\n”； print“从属 SQL 正在运行::\t\t$slave_SQL\n”;打印“落后主秒：\t\t$seconds_behind_master\n"; if ($seconds_behind_master > $max_behind){ print "从 SQL Server 远远落后于主服务器 "; } sub MysqlConnect { my ($host, $port, $user, $passwd) = @_; my $dsn = "DBI:mysql:host=$host;port=$port"; return DBI->connect($dsn, $user, $passwd, {RaiseError => 1}); } sub MysqlQuery { my ($dbh, $ query) = @_; 我的 $sth = $dbh->prepare($query); 我的 $res = $sth->execute; 返回 undef 除非 $res; 我的 $row = $sth->fetchrow_hashref; $sth-> finish; return $row; } [编辑推荐] FreeBSD 8 上 Rsync 同步的简单教程 生产环境MySQL数据库主从同步总结 分享MySQL数据库同步的示例演示