编者按：很多朋友一开始接触MySQL双机同步需求的时候可能会感到不知道从哪里入手，事实上这是MySQL本身就支持的功能之一。该篇文章提供有关MySQL主从同步的初步思路，供大家参考。
一.需求问题
假设目前有两台 MySQL 数据库服务器，如何实现这两台机器的数据同步问题？即在一台机器上修改数据库后，另一台机器会同步更新所修改的信息。
二.解决方案
查资料发现 MySQL 支持单向，异步复制，复制过程中一个服务器充当主服务器，而另一个或多个其他服务器充当从服务器。
原理是这样的：
主服务器将更新写入二进制日志文件，并维护文件的一个索引来跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接受从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。
2.1 测试环境
Master : 192.168.7.67 (CentOS 5.5 x86_64 ) MySQL Version : 5.0.77 Slave: 192.168.56.103 (CentOS 5.3 i386) MySQL Version : 5.0.45
备注：
Master 和 slave 端的 MySQL 版本最好要一样的，或者 Master 端的版本高于 Slave 端
2.2 配置过程
2.2.1 Master 端设置
开启 MySQL 服务并新建一个测试数据库 abc:
chinastor.com-root@camlit ~: /etc/init.d/mysqld start jian.ma@camlit ~: mysql -u root -p Enter password: xxxx Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 3 Server version: 5.0.77 Source distribution Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> create database abc; Query OK, 1 row affected (0.31 sec) ###创建一个用来同步的用户，指定只能在 192.168.56.103 登录 ###REPLICATION SLAVE: Enable replication slaves to read binary log events from the master mysql> grant replication slave on *.* to 'test1'@'192.168.56.103' identified by 'test1'; Query OK, 0 rows affected (0.16 sec)
修改配置文件：
chinastor.com-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] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
重启服务：
chinastor.com-root@camlit ~: /etc/init.d/mysqld restart
2.2.2 Slave 端设置
和 master 端一样创建一个相同的数据库: abc
Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 5 Server version: 5.0.45-log Source distribution Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> create database abc; Query OK, 1 row affected (0.31 sec)
修改配置文件：
chinastor.com-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/mysqld.pid
重启服务：
chinastor.com-root@test2~: /etc/init.d/mysqld restart
备注：
配置成功 后会在 mysql 目录(/var/lib/mysql/)下生成 master.info 文件，如果要更改 slave 设置，要先将该文件删除才会起作用。
进入 mysql，输入下面命令：
chinastor.com-root@test2~: mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 4 Server version: 5.0.45-log Source distribution Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> slave start; Query OK, 0 rows affected, 1 warning (0.00 sec) ###查看同步情况 mysql > show slave status; 或 show master status;
2.3 结果测试
在 Master 端进行数据库 abc 的一些操作，如下所示：
jian.ma@camlit ~: mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 3 Server version: 5.0.77-log Source distribution Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> use abc; Database changed mysql> create table test1 (IP VARCHAR(20),USER VARCHAR(100), MAIL VARCHAR(100)); Query OK, 0 rows affected (1.20 sec) mysql> insert into test1(IP,USER,MAIL) values('192.168.7.66', 'test', 'test@test.com.cn'); Query OK, 1 row affected (0.06 sec)
在 Slave 端查看是否能够更新：
chinastor.com-root@test2 ~: mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 6 Server version: 5.0.45-log Source distribution Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | foo| | mysql | | test | |abc | +--------------------+ 5rows in set (0.00 sec) mysql> use abc; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +---------------+ | Tables_in_abc | +---------------+ | test1 | +---------------+ 1 row in set (0.03 sec) mysql> select * from test1; +--------------+------+------------------+ | IP | USER | MAIL | +--------------+------+------------------+ | 192.168.7.66 | test | test@test.com.cn | +--------------+------+------------------+ 1 row in set (0.00 sec)
从上面的结果看到的是 Master 端的数据可以同步到 Slave 端里面。说明此时主从数据库的同步问题已经成功解决。
三.补充资料
关于如何连接到远程 MySQL 问题，可以采取下面的步骤：
首先先登录到远程机器：
jian.ma@camlit ~: ssh chinastor.com-root@192.168.56.103 password: xxx chinastor.com-root@test2 ~:
编辑配置文件：
chinastor.com-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
重启服务：
chinastor.com-root@test2 ~: /etc/init.d/mysqld restart
创建测试数据库：
chinastor.com-root@test2 ~: mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.0.45 Source distribution Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> create database foo ; Query OK, 1 row affected (0.00 sec) ###增加用户 test123 从任何主机登录到 MySQL mysql> grant all privileges on *.* to 'test123'@'%' identified by 'test123' with grant option; Query OK, 0 rows affected (0.00 sec) ###增加用户 test1 从 192.168.7.67 主机登录到 MySQL mysql> grant all privileges on foo.* to 'test1'@'192.168.7.67' identified by 'test1' with grant option; Query OK, 0 rows affected (0.00 sec)
如果有防火墙的设置的话，可以如下设置：
chinastor.com-root@test2 ~: iptables -A INPUT -i eth0 -s 192.168.7.67 -p tcp --dport 3306 -j ACCEPT chinastor.com-root@test2~: /etc/init.d/iptales save
最后在客户端就可以输入下面命令来远程进入 MySQL 数据库：
jian.ma@camlit ~: mysql -u test1 -h 192.168.56.103 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 13 Server version: 5.0.45 Source distribution Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysqld.pl内容如下：
#!/usr/bin/perl #This script is used to check if the mysql replication is ok use strict; use DBI; use POSIX "strftime"; my $host = "192.168.56.103"; my $user = "test1"; my $passwd = "test1"; my $port = "3306"; my $max_behind = "120"; my $check_log = "./mysql_check.log"; #Open the log file open (FH, ">> $check_log") or die $!; #Connect the mysql server my $dbh = &MysqlConnect ($host, $port, $user, $passwd); #Get slave sql status my $slave_status = &MysqlQuery( $dbh, 'show slave status'); print FH "Error: SQL Query Error:" . $dbh->errstr; my $slave_IO = $slave_status->{Slave_IO_Running}; my $slave_SQL = $slave_status->{Slave_SQL_Running}; my $seconds_behind_master = $slave_status->{Seconds_Behind_Master}; my $now_time = POSIX::strftime ("[%Y-%m-%d %H:%M:%S]", localtime); print "Check the Slave MySQL stauts....n"; print "_" x 50, "n"; print "Time:ttt$now_timen"; print "Slave IO Running:tt$slave_IOn"; print "Slave SQL Running::tt$slave_SQLn"; print "Behind Master Seconds:tt$seconds_behind_mastern"; if ($seconds_behind_master > $max_behind){ print "Slave SQL Server is far behind master "; } #---Functions----# 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) = @_; my $sth = $dbh->prepare($query); my $res = $sth->execute; return undef unless $res; my $row = $sth->fetchrow_hashref; $sth->finish; return $row; }
编者按：很多朋友一开始接触MySQL双机同步需求的时候可能会感到不知道从哪里入手，事实上这是MySQL本身就支持的功能之一。该篇文章提供有关MySQL主从同步的初步思路，供大家参考。
一.需求问题
假设目前有两台 MySQL 数据库服务器，如何实现这两台机器的数据同步问题？即在一台机器上修改数据库后，另一台机器会同步更新所修改的信息。
二.解决方案
查资料发现 MySQL 支持单向，异步复制，复制过程中一个服务器充当主服务器，而另一个或多个其他服务器充当从服务器。
原理是这样的：
主服务器将更新写入二进制日志文件，并维护文件的一个索引来跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接受从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。
2.1 测试环境
Master : 192.168.7.67 (CentOS 5.5 x86_64 ) MySQL Version : 5.0.77 Slave: 192.168.56.103 (CentOS 5.3 i386) MySQL Version : 5.0.45
备注：
Master 和 slave 端的 MySQL 版本最好要一样的，或者 Master 端的版本高于 Slave 端
2.2 配置过程
2.2.1 Master 端设置
开启 MySQL 服务并新建一个测试数据库 abc:
chinastor.com-root@camlit ~: /etc/init.d/mysqld start jian.ma@camlit ~: mysql -u root -p Enter password: xxxx Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 3 Server version: 5.0.77 Source distribution Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> create database abc; Query OK, 1 row affected (0.31 sec) ###创建一个用来同步的用户，指定只能在 192.168.56.103 登录 ###REPLICATION SLAVE: Enable replication slaves to read binary log events from the master mysql> grant replication slave on *.* to 'test1'@'192.168.56.103' identified by 'test1'; Query OK, 0 rows affected (0.16 sec)
修改配置文件：
chinastor.com-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] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
重启服务：
chinastor.com-root@camlit ~: /etc/init.d/mysqld restart
2.2.2 Slave 端设置
和 master 端一样创建一个相同的数据库: abc
Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 5 Server version: 5.0.45-log Source distribution Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> create database abc; Query OK, 1 row affected (0.31 sec)
修改配置文件：
chinastor.com-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/mysqld.pid
重启服务：
chinastor.com-root@test2~: /etc/init.d/mysqld restart
备注：
配置成功 后会在 mysql 目录(/var/lib/mysql/)下生成 master.info 文件，如果要更改 slave 设置，要先将该文件删除才会起作用。
进入 mysql，输入下面命令：
chinastor.com-root@test2~: mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 4 Server version: 5.0.45-log Source distribution Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> slave start; Query OK, 0 rows affected, 1 warning (0.00 sec) ###查看同步情况 mysql > show slave status; 或 show master status;
2.3 结果测试
在 Master 端进行数据库 abc 的一些操作，如下所示：
jian.ma@camlit ~: mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 3 Server version: 5.0.77-log Source distribution Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> use abc; Database changed mysql> create table test1 (IP VARCHAR(20),USER VARCHAR(100), MAIL VARCHAR(100)); Query OK, 0 rows affected (1.20 sec) mysql> insert into test1(IP,USER,MAIL) values('192.168.7.66', 'test', 'test@test.com.cn'); Query OK, 1 row affected (0.06 sec)
在 Slave 端查看是否能够更新：
chinastor.com-root@test2 ~: mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 6 Server version: 5.0.45-log Source distribution Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | foo| | mysql | | test | |abc | +--------------------+ 5rows in set (0.00 sec) mysql> use abc; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +---------------+ | Tables_in_abc | +---------------+ | test1 | +---------------+ 1 row in set (0.03 sec) mysql> select * from test1; +--------------+------+------------------+ | IP | USER | MAIL | +--------------+------+------------------+ | 192.168.7.66 | test | test@test.com.cn | +--------------+------+------------------+ 1 row in set (0.00 sec)
从上面的结果看到的是 Master 端的数据可以同步到 Slave 端里面。说明此时主从数据库的同步问题已经成功解决。
三.补充资料
关于如何连接到远程 MySQL 问题，可以采取下面的步骤：
首先先登录到远程机器：
jian.ma@camlit ~: ssh chinastor.com-root@192.168.56.103 password: xxx chinastor.com-root@test2 ~:
编辑配置文件：
chinastor.com-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
重启服务：
chinastor.com-root@test2 ~: /etc/init.d/mysqld restart
创建测试数据库：
chinastor.com-root@test2 ~: mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.0.45 Source distribution Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> create database foo ; Query OK, 1 row affected (0.00 sec) ###增加用户 test123 从任何主机登录到 MySQL mysql> grant all privileges on *.* to 'test123'@'%' identified by 'test123' with grant option; Query OK, 0 rows affected (0.00 sec) ###增加用户 test1 从 192.168.7.67 主机登录到 MySQL mysql> grant all privileges on foo.* to 'test1'@'192.168.7.67' identified by 'test1' with grant option; Query OK, 0 rows affected (0.00 sec)
如果有防火墙的设置的话，可以如下设置：
chinastor.com-root@test2 ~: iptables -A INPUT -i eth0 -s 192.168.7.67 -p tcp --dport 3306 -j ACCEPT chinastor.com-root@test2~: /etc/init.d/iptales save
最后在客户端就可以输入下面命令来远程进入 MySQL 数据库：
jian.ma@camlit ~: mysql -u test1 -h 192.168.56.103 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 13 Server version: 5.0.45 Source distribution Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysqld.pl内容如下：
#!/usr/bin/perl #This script is used to check if the mysql replication is ok use strict; use DBI; use POSIX "strftime"; my $host = "192.168.56.103"; my $user = "test1"; my $passwd = "test1"; my $port = "3306"; my $max_behind = "120"; my $check_log = "./mysql_check.log"; #Open the log file open (FH, ">> $check_log") or die $!; #Connect the mysql server my $dbh = &MysqlConnect ($host, $port, $user, $passwd); #Get slave sql status my $slave_status = &MysqlQuery( $dbh, 'show slave status'); print FH "Error: SQL Query Error:" . $dbh->errstr; my $slave_IO = $slave_status->{Slave_IO_Running}; my $slave_SQL = $slave_status->{Slave_SQL_Running}; my $seconds_behind_master = $slave_status->{Seconds_Behind_Master}; my $now_time = POSIX::strftime ("[%Y-%m-%d %H:%M:%S]", localtime); print "Check the Slave MySQL stauts....n"; print "_" x 50, "n"; print "Time:ttt$now_timen"; print "Slave IO Running:tt$slave_IOn"; print "Slave SQL Running::tt$slave_SQLn"; print "Behind Master Seconds:tt$seconds_behind_mastern"; if ($seconds_behind_master > $max_behind){ print "Slave SQL Server is far behind master "; } #---Functions----# 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) = @_; my $sth = $dbh->prepare($query); my $res = $sth->execute; return undef unless $res; my $row = $sth->fetchrow_hashref; $sth->finish; return $row; }
未经允许不得转载：泰泽科创站 » 手把手教你实现MySQL双机数据同步(1)
