手把手教你实现MySQL双机数据同步

编者按：很多朋友一开始接触MySQL双机同步需求的时候可能会感到不知道从哪里入手，事实上这是MySQL本身就支持的功能之一。本文提供有关MySQL主从同步的初步思路，供大家参考。

一.需求问题

假设目前有两台 MySQL 数据库服务器，如何实现这两台机器的数据同步问题？即在一台机器上修改数据库后，另一台机器会同步更新所修改的信息。

二.解决方案

查资料发现 MySQL 支持单向，异步复制，复制过程中一个服务器充当主服务器，而另一个或多个其他服务器充当从服务器。

原理是这样的：

主服务器将更新写入二进制日志文件，并维护文件的一个索引来跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接受从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

2.1 测试环境

 
 
 
    
  
  
  
  1. Master : 192.168.7.67 (CentOS 5.5  x86_64 )   MySQL Version  :  5.0.77  
    2.   
  
  
  2. Slave: 192.168.56.103 (CentOS 5.3 i386)  MySQL  Version : 5.0.45 
    3.

备注：

Master 和 slave 端的 MySQL 版本最好要一样的，或者 Master 端的版本高于 Slave 端

2.2 配置过程

2.2.1 Master 端设置

开启 MySQL 服务并新建一个测试数据库 abc:

 
 
 
    
  
  
  
  1. root@camlit ~: /etc/init.d/mysqld start   
    2.   
  
  
  2. jian.ma@camlit  ~: mysql -u root -p   
    3.   
  
  
  3. Enter password: xxxx  
    4.   
  
  
  4. Welcome to the MySQL monitor.  Commands end with ; or \g.   
    5.   
  
  
  5. Your MySQL connection id is 3   
    6.   
  
  
  6. Server version: 5.0.77 Source distribution   
    7.   
  
  
  7.  
    8.   
  
  
  8. Type 'help;' or '\h' for help. Type '\c' to clear the buffer.   
    9.   
  
  
  9.  
    10.   
  
  
  10. mysql> create database abc;   
    11.   
  
  
  11. Query OK, 1 row affected (0.31 sec)  
    12.   
  
  
  12.  
    13.   
  
  
  13. ###创建一个用来同步的用户，指定只能在 192.168.56.103 登录  
    14.   
  
  
  14. ###REPLICATION SLAVE: Enable replication slaves to read binary log events from the master  
    15.   
  
  
  15.  
    16.   
  
  
  16. mysql> grant replication slave on *.* to 'test1'@'192.168.56.103' identified by 'test1';   
    17.   
  
  
  17. Query OK, 0 rows affected (0.16 sec)  
    18.

修改配置文件：

 
 
 
    
  
  
  
  1. root@camlit ~: vi /etc/my.cnf 
    2.

备注：在修改配置文件之前做好该文件的备份工作。

 
 
 
    
  
  
  
  1. [mysqld]   
    2.   
  
  
  2. datadir=/var/lib/mysql   
    3.   
  
  
  3. socket=/var/lib/mysql/mysql.sock   
    4.   
  
  
  4. user=mysql   
    5.   
  
  
  5. old_passwords=1   
    6.   
  
  
  6.  
    7.   
  
  
  7. ##增加下面内容  
    8.   
  
  
  8. server_id=1###1 表示 master, 2 表示 slave   binlog-do-db=abc ###需要同步的数据库，如果有多个数据库，每个数据库一行  binlog-ignore-db=mysql###不需要同步的数据库 log-bin=mysql-bin   
    9.   
  
  
  9.    
    10.   
  
  
  10. [mysqld_safe]   
    11.   
  
  
  11. log-error=/var/log/mysqld.log   
    12.   
  
  
  12. pid-file=/var/run/mysqld/mysqld.pid  
    13.

重启服务：

 
 
 
    
  
  
  
  1. root@camlit ~: /etc/init.d/mysqld restart  
    2.

2.2.2  Slave 端设置

和 master 端一样创建一个相同的数据库: abc

 
 
 
    
  
  
  
  1. Enter password:   
    2.   
  
  
  2. Welcome to the MySQL monitor.  Commands end with ; or \g.   
    3.   
  
  
  3. Your MySQL connection id is 5   
    4.   
  
  
  4. Server version: 5.0.45-log Source distribution   
    5.   
  
  
  5.    
    6.   
  
  
  6. Type 'help;' or '\h' for help. Type '\c' to clear the buffer.   
    7.   
  
  
  7.    
    8.   
  
  
  8. mysql> create database abc;   
    9.   
  
  
  9. Query OK, 1 row affected (0.31 sec) 
    10.

修改配置文件：

 
 
 
    
  
  
  
  1. root@test2 ~: vi /etc/my.cnf 
    2.

 

 
 
 
    
  
  
  
  1. [mysqld]   
    2.   
  
  
  2. datadir=/var/lib/mysql   
    3.   
  
  
  3. socket=/var/lib/mysql/mysql.sock   
    4.   
  
  
  4. user=mysql   
    5.   
  
  
  5. old_passwords=1   
    6.   
  
  
  6.    
    7.   
  
  
  7. ###增加下面内容  
    8.   
  
  
  8. 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  ###不要接受的数据库  
    9.   
  
  
  9.    
    10.   
  
  
  10. [mysqld_safe]   
    11.   
  
  
  11. log-error=/var/log/mysqld.log   
    12.   
  
  
  12. pid-file=/var/run/mysqld/mysqld.pid  
    13.

重启服务：

 
 
 
    
  
  
  
  1. root@test2~: /etc/init.d/mysqld restart  
    2.

备注：

配置成功 后会在 mysql 目录(/var/lib/mysql/)下生成 master.info 文件，如果要更改 slave 设置，要先将该文件删除才会起作用。

进入 mysql，输入下面命令：

 
 
 
    
  
  
  
  1. root@test2~: mysql -u root -p  
    2.   
  
  
  2. Enter password:   
    3.   
  
  
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.   
    4.   
  
  
  4. Your MySQL connection id is 4   
    5.   
  
  
  5. Server version: 5.0.45-log Source distribution   
    6.   
  
  
  6.    
    7.   
  
  
  7. Type 'help;' or '\h' for help. Type '\c' to clear the buffer.   
    8.   
  
  
  8.    
    9.   
  
  
  9. mysql> slave start;   
    10.   
  
  
  10. Query OK, 0 rows affected, 1 warning (0.00 sec)   
    11.   
  
  
  11. ###查看同步情况  
    12.   
  
  
  12. mysql > show slave status;  或 show master status;  
    13.

2.3 结果测试

在 Master 端进行数据库 abc 的一些操作，如下所示：

 
 
 
    
  
  
  
  1. jian.ma@camlit ~: mysql  -u root -p   
    2.   
  
  
  2. Enter password:   
    3.   
  
  
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.   
    4.   
  
  
  4. Your MySQL connection id is 3   
    5.   
  
  
  5. Server version: 5.0.77-log Source distribution   
    6.   
  
  
  6.    
    7.   
  
  
  7. Type 'help;' or '\h' for help. Type '\c' to clear the buffer.   
    8.   
  
  
  8.    
    9.   
  
  
  9. mysql> use abc;   
    10.   
  
  
  10. Database changed   
    11.   
  
  
  11. mysql> create table test1 (IP VARCHAR(20),USER VARCHAR(100), MAIL   
    12.   
  
  
  12. VARCHAR(100));   
    13.   
  
  
  13. Query OK, 0 rows affected (1.20 sec)   
    14.   
  
  
  14. mysql> insert into test1(IP,USER,MAIL) values('192.168.7.66', 'test', 'test@test.com.cn');   
    15.   
  
  
  15. Query OK, 1 row affected (0.06 sec)  
    16.

在 Slave 端查看是否能够更新：

 
 
 
    
  
  
  
  1. root@test2 ~: mysql -u root -p   
    2.   
  
  
  2. Enter password:   
    3.   
  
  
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.   
    4.   
  
  
  4. Your MySQL connection id is 6   
    5.   
  
  
  5. Server version: 5.0.45-log Source distribution   
    6.   
  
  
  6.    
    7.   
  
  
  7. Type 'help;' or '\h' for help. Type '\c' to clear the buffer.   
    8.   
  
  
  8.    
    9.   
  
  
  9. mysql> show databases;   
    10.   
  
  
  10. +--------------------+   
    11.   
  
  
  11. | Database   |   
    12.   
  
  
  12. +--------------------+   
    13.   
  
  
  13. | information_schema |   
    14.   
  
  
  14. | foo|   
    15.   
  
  
  15. | mysql  |   
    16.   
  
  
  16. | test   |   
    17.   
  
  
  17. |abc  |   
    18.   
  
  
  18. +--------------------+   
    19.   
  
  
  19. 5rows in set (0.00 sec)   
    20.   
  
  
  20. mysql> use abc;   
    21.   
  
  
  21. Reading table information for completion of table and column names   
    22.   
  
  
  22. You can turn off this feature to get a quicker startup with -A   
    23.   
  
  
  23. Database changed   
    24.   
  
  
  24. mysql> show tables;   
    25.   
  
  
  25. +---------------+   
    26.   
  
  
  26. | Tables_in_abc |   
    27.   
  
  
  27. +---------------+   
    28.   
  
  
  28. | test1 |   
    29.   
  
  
  29. +---------------+   
    30.   
  
  
  30. 1 row in set (0.03 sec)   
    31.   
  
  
  31.  mysql> select * from test1;   
    32.   
  
  
  32. +--------------+------+------------------+   
    33.   
  
  
  33. | IP   | USER | MAIL |   
    34.   
  
  
  34. +--------------+------+------------------+   
    35.   
  
  
  35. | 192.168.7.66 | test | test@test.com.cn |   
    36.   
  
  
  36. +--------------+------+------------------+   
    37.   
  
  
  37. 1 row in set (0.00 sec)  
    38.

从上面的结果可以看到 Master 端的数据可以同步到 Slave 端里面。说明此时主从数据库的同步问题已经成功解决。

#p#

三.补充资料

关于如何连接到远程 MySQL 问题，可以采取下面的步骤：

首先先登录到远程机器：

 
 
 
    
  
  
  
  1. jian.ma@camlit ~: ssh root@192.168.56.103   
    2.   
  
  
  2. password: xxx  
    3.   
  
  
  3. root@test2 ~:  
    4.

编辑配置文件：

 
 
 
    
  
  
  
  1. root@test2 ~: vi /etc/my.cnf  
    2.

增加下面一行内容：

 
 
 
    
  
  
  
  1. [mysqld]   
    2.   
  
  
  2. datadir=/var/lib/mysql   
    3.   
  
  
  3. socket=/var/lib/mysql/mysql.sock   
    4.   
  
  
  4. user=mysql   
    5.   
  
  
  5. old_passwords=1   
    6.   
  
  
  6. bind-address=192.168.56.103###此 IP 地址为 MySQL 本机的 IP 地址  
    7.   
  
  
  7. [mysqld_safe]   
    8.   
  
  
  8. log-error=/var/log/mysqld.log   
    9.   
  
  
  9. pid-file=/var/run/mysqld/mysqld.pi 
    10.

重启服务：

 
 
 
    
  
  
  
  1. root@test2 ~: /etc/init.d/mysqld restart  
    2.

创建测试数据库：

 
 
 
    
  
  
  
  1. root@test2 ~: mysql -u root -p  
    2.   
  
  
  2. Enter password:   
    3.   
  
  
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.   
    4.   
  
  
  4. Your MySQL connection id is 2   
    5.   
  
  
  5. Server version: 5.0.45 Source distribution   
    6.   
  
  
  6.    
    7.   
  
  
  7. Type 'help;' or '\h' for help. Type '\c' to clear the buffer.   
    8.   
  
  
  8.    
    9.   
  
  
  9. mysql> create database foo ;  
    10.   
  
  
  10. Query OK, 1 row affected (0.00 sec)   
    11.   
  
  
  11. ###增加用户 test123 从任何主机登录到 MySQL  
    12.   
  
  
  12. mysql> grant all privileges on *.* to 'test123'@'%' identified by 'test123' with grant  option;   
    13.   
  
  
  13. Query OK, 0 rows affected (0.00 sec)   
    14.   
  
  
  14. ###增加用户 test1 从 192.168.7.67 主机登录到 MySQL  
    15.   
  
  
  15. mysql> grant all privileges on foo.* to 'test1'@'192.168.7.67' identified by 'test1' with grant   option;   
    16.   
  
  
  16. Query OK, 0 rows affected (0.00 sec)  
    17.

如果有防火墙的设置的话，可以如下设置：

 
 
 
    
  
  
  
  1. root@test2 ~: iptables -A INPUT -i eth0 -s 192.168.7.67 -p tcp --dport 3306 -j ACCEPT   
    2.   
  
  
  2. root@test2~: /etc/init.d/iptales save  
    3.

最后在客户端就可以输入下面命令来远程进入 MySQL 数据库：

 
 
 
    
  
  
  
  1. jian.ma@camlit ~: mysql -u test1 -h 192.168.56.103 -p   
    2.   
  
  
  2. Enter password:   
    3.   
  
  
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.   
    4.   
  
  
  4. Your MySQL connection id is 13   
    5.   
  
  
  5. Server version: 5.0.45 Source distribution   
    6.   
  
  
  6. Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 
    7.

mysqld.pl内容如下：

 
 
 
    
  
  
  
  1. #!/usr/bin/perl 
    2.   
  
  
  2.  
    3.   
  
  
  3. #This script is used to check if the mysql replication is ok 
    4.   
  
  
  4.  
    5.   
  
  
  5. use strict; 
    6.   
  
  
  6. use DBI; 
    7.   
  
  
  7. use POSIX "strftime"; 
    8.   
  
  
  8.  
    9.   
  
  
  9. my $host           = "192.168.56.103"; 
    10.   
  
  
  10. my $user           = "test1"; 
    11.   
  
  
  11. my $passwd         = "test1"; 
    12.   
  
  
  12. my $port           = "3306"; 
    13.   
  
  
  13. my $max_behind     = "120"; 
    14.   
  
  
  14. my $check_log      = "./mysql_check.log"; 
    15.   
  
  
  15.  
    16.   
  
  
  16.  
    17.   
  
  
  17. #Open the log file  
    18.   
  
  
  18. open (FH, ">> $check_log") or die $!; 
    19.   
  
  
  19.  
    20.   
  
  
  20. #Connect the mysql server  
    21.   
  
  
  21. my $dbh = &MysqlConnect ($host, $port, $user, $passwd); 
    22.   
  
  
  22.  
    23.   
  
  
  23.  
    24.   
  
  
  24. #Get slave sql status 
    25.   
  
  
  25. my $slave_status = &MysqlQuery( $dbh, 'show slave status'); 
    26.   
  
  
  26. print FH "Error: SQL Query Error:" . $dbh->errstr; 
    27.   
  
  
  27.  
    28.   
  
  
  28.  
    29.   
  
  
  29. my $slave_IO              = $slave_status->{Slave_IO_Running}; 
    30.   
  
  
  30. my $slave_SQL             = $slave_status->{Slave_SQL_Running}; 
    31.   
  
  
  31. my $seconds_behind_master = $slave_status->{Seconds_Behind_Master}; 
    32.   
  
  
  32. my $now_time              = POSIX::strftime ("[%Y-%m-%d %H:%M:%S]", localtime); 
    33.   
  
  
  33.  
    34.   
  
  
  34.  
    35.   
  
  
  35. print "Check the Slave MySQL stauts....\n"; 
    36.   
  
  
  36. print "_" x 50, "\n"; 
    37.   
  
  
  37. print "Time:\t\t\t$now_time\n"; 
    38.   
  
  
  38. print "Slave IO Running:\t\t$slave_IO\n"; 
    39.   
  
  
  39. print "Slave SQL Running::\t\t$slave_SQL\n"; 
    40.   
  
  
  40. print "Behind Master Seconds:\t\t$seconds_behind_master\n"; 
    41.   
  
  
  41.  
    42.   
  
  
  42.  
    43.   
  
  
  43. if ($seconds_behind_master > $max_behind){ 
    44.   
  
  
  44.     print "Slave SQL Server is far behind master "; 
    45.   
  
  
  45. } 
    46.   
  
  
  46.  
    47.   
  
  
  47.  
    48.   
  
  
  48. #---Functions----# 
    49.   
  
  
  49. sub MysqlConnect  { 
    50.   
  
  
  50.  
    51.   
  
  
  51.     my ($host, $port, $user, $passwd) = @_; 
    52.   
  
  
  52.     my $dsn  = "DBI:mysql:host=$host;port=$port"; 
    53.   
  
  
  53.     return DBI->connect($dsn, $user, $passwd, {RaiseError => 1}); 
    54.   
  
  
  54. } 
    55.   
  
  
  55.  
    56.   
  
  
  56.  
    57.   
  
  
  57. sub MysqlQuery { 
    58.   
  
  
  58.  
    59.   
  
  
  59.     my ($dbh , $query) = @_; 
    60.   
  
  
  60.     my $sth = $dbh->prepare($query); 
    61.   
  
  
  61.     my $res = $sth->execute; 
    62.   
  
  
  62.     return undef unless $res; 
    63.   
  
  
  63.     my $row = $sth->fetchrow_hashref; 
    64.   
  
  
  64.     $sth->finish; 
    65.   
  
  
  65.     return $row; 
    66.   
  
  
  66. } 
    67.

【编辑推荐】

  1. FreeBSD 8上的Rsync同步简易教程
  2. 生产环境下的MySQL数据库主从同步总结
  3. MySQL数据库同步的实例演示大分享

文章名称：手把手教你实现MySQL双机数据同步
URL分享：http://www.csdahua.cn/qtweb/news44/357544.html

网站建设、网络推广公司-快上网，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 快上网

成都快上网为您推荐相关内容

网页设计公司知识

同城分类信息

品牌网站定制　　 　瑞杰管理　　 　成都网站改版　　 　成都SEO公司　　 　营山产后护理　　 　成都网站建设　　 　成都免费做网站　　 　光华机房服务器托管　　 　复盛机电设备安装　　 　成都平面广告设计　　 　尊格展览　　 　柴油发电机租赁　　 　网站建设报价方案　　 　绵阳主机托管　　 　古巴雪茄　　 　成都ssl证书申请　　 　达州主机托管　　 　四川门窗　　 　域名申请　　 　云主机