mysql数据库同步详解_MySQL

同步介绍：

MySQL 的数据同步，在MySQL 官方网站文档上，叫Replication 字面是重作的意思，意译就是同步了。其实，MySQL 的同步，并不是使用同步sync 这个单词而是用重作replication，很准确表明了MySQL 数据库操作的实质，是作同样的操作，或叫重作同样的操作，以保持主数据库服务器master 与 从属服务器slave 之样的数据保持一致。replication 就是有重复，重作的意思。

同步原理：

MySQL 为了实现replication 必须打开bin-log 项，也是打开二进制的MySQL 日志记录选项。MySQL 的bin log 二进制日志，可以记录所有影响到数据库表中存储记录内容的sql 操作，如insert / update / delete 操作，而不记录select 这样的操作。因此，我们可以通过二进制日志把某一时间段内丢失的数据可以恢复到数据库中（如果二进制日志中记录的日志项，包涵数据库表中所有数据，那么， 就可以恢复本地数据库的全部数据了）。 而这个二进制日志，如果用作远程数据库恢复，那就是replication 了。这就是使用replication 而不用sync 的原因。这也是为什么要设置bin-log = 这个选项的原因。

在同步过程中，最重要的同步参照物，就是同步使用那一个二进制日志文件，从那一条记录开始同步。

同步过程：

首先，你应该有两个或两个以上的MySQL 数据库服务器，版本最好是在3.3 以上 （当然，两个服务器不一定是两台机器，一台机器上安装两个MySQL 服务是可以的，同时，如果你对MySQL replication 原理十分精通的话，你甚至可以在一个MySQL 服务的两个不同数据库database 之间作同步，看有没有需要了）说明： 这两个服务器一般设置一个为主服务器，或叫源服务器，master mysql server, 另一台或其他多台就是replication slave 同步从服务器了。一台slave 与多台slave 设置方法是一样的，这样你就可以作类似数据库集群了。

设置可访问MySQL 帐号，操作以英文为准。

MySQL 帐号一般设置为限定IP 访问，以保障安全性

MySQL 帐号一般在master 与slave 设置为相同帐号，同时是远程可访问

特别注意，如果你用Linux / Unix 操作系统，那一定要注意一下防火墙firewall 有没有限制MySQL 远程访问，如果是，最好是打开远程访问端口，并作好访问IP 限制

由于my.cnf 中要明文存储MySQL 帐号密码，请注意保护my.cnf 不让其他用户访问到（看来要向MySQL 说明下以后用密码存passwd）。

第三步，当然是设置两个服务器要同步的数据库为同样的数据库了。

这里有一些技巧，包括mysql 的sql 指令说明下。

方法之一，就是英文说明中说的，先锁定数据库读写功能( 其实最好是停止mysqld 服务，再作备份) 然后用tar 备份数据库目录，转到slave 服务器相同数据目录中。

方法之二，使用mysql studio 这样的实用工具，直接使用mysql studio 的backup database 工具把数据库同步

以上两种方法都是master 数据库中有不少数据记录，按上两 种方法得到master 与slave 有相同数据库与数据记录。 而第三种方法，则是适合于新建数据库的情况，特别适合于 master 与slave 在my.cnf 已经设置好replication 关系 （但 未指定database 同步数据库) 的情况: 这就是使用　mysql 的sql 语句load table from master 与load data from master;

load table from master 可以从master 数据库把表结构复制到slave 数据库中，这样可以建立同步的表。load data from master 是从master 数据库把数据导入到slave 数据表中，条件是master 从一开始安装运行就使用了bin-log 参数而保存有二进制日志

接下来就是配置master 与slave 的my.cnf 文件，使得replcation 能有合适的启动参数以支持数据同步

技巧： 如果你使用win2k 的mysql 那么，你可以下载mysql.com 出的官方mysql administrator 实用程序，直接在mysql administrator 中即可配置master 与slave ，同时也可以配置query-cache 。

在master 的my.cnf（如果是win32 那就是my.ini）增加

[mysqld]

log-bin =

server-id=1

注意，上面的log-bin = 中的 等号= 是不可少的。

在slave 的my.cnf 修改

[mysqld]

server-id=2 # 如果有多个slave 就改为不重复的id 就好，在mysql 4.1 中，这个已经取消了

master-host=10.10.10.22

master-user=backup #同步用户帐号

master-password=1234

master-port=3306

master-connect-retry=60 #预设重试间隔60秒

replicate-do-db=test # 告诉slave只做test 数据库的更新

bin-log =

检查master 与slave 配置状态，使用show master status; 与show slave status; 这两个SQL 指令即可在master 与slave 查看配置状态。这里有两个状态变量十分重要

mysql > SHOW MASTER STATUS;

+---------------+----------+--------------+------------------+

| File　　| Position | Binlog_Do_DB | Binlog_Ignore_DB |

+---------------+----------+--------------+------------------+

| mysql-bin.003 | 73　　| test　　| manual,mysql　|

+---------------+----------+--------------+------------------+

注意File 是mysql-bin.003 而position 是73

这个文件是对test 这个 数据库的二进制日志记录，记录数据变化的当前记录条数是73

前面我们说过，二进制日志记录着某个数据库所有数据记录变化的sql 操作语句，如insert / update / delete 等，正是因为如此，在slave 同步mysql 数据库操作时，其实是读取master 中这个mysql-bin.003 二进制日志中的sql 操作，同在slave 中执行这些sql 操作，所以，同步成功有以下几个条件：

master 与slave 有相同的数据库表结构，最好database name 也一样（可以设置为不一样的database name）

master 与slave 有相同的初始数据记录，保证同步操作开始后两者数据一致

master 必须使用bin-log 二进制日志记录（推荐slave 也使用bin-log）

slave 是从master 的bin-log 是读取sql 记录来同步，所以，从哪一条log 开始读取很重要（下面的第7 条操作，就是保证slave 能与master 保持相同的记录读取，并读取正确的bin-log 日志文件）

在slave 执行下面sql 操作

mysql> CHANGE MASTER TO

->　MASTER_HOST=&＃39;master_host_name&＃39;,

->　MASTER_USER=&＃39;replication_user_name&＃39;,

->　MASTER_PASSWORD=&＃39;replication_password&＃39;,

->　MASTER_LOG_FILE=&＃39;recorded_log_file_name&＃39;,

->　MASTER_LOG_POS=recorded_log_position;

这里，把 上面的redcorded_log_file_name 改为 mysql-bin.003 而recorded_log_position 改为73 （特别注意，这是关系关键）。

linux 下的mysql 与win2k 下的MySQL 作replication 会有什么技巧：

注意Linux 下有大小写区分，而win2k 下没有。所以最好使用mysqldump 方法来让两个数据库初始化同步，而不能使用tar 方式直接copy 数据库表文件。使用mysql studio 来作database backup 也不错

注意win2k 下的mysql 配置文件是c:/my.ini。你可以使用mysql administrator 0.9 这个图型界面的实用工具来设置。

如果win2k 下的mysql 为master ， 一定要注意show master status 中显示出来的结果。

win2k 下的mysql 为slave 时，注意不要把同步时间周期设置太长。

同步实战：

环境：

环境配置

操作系统：CentOS release 5.3 (Final) 64bit

MySQL：5.14

主服务器A IP：10.224.194.239

从服务器B IP：10.224.194.237

同步数据库: test（用于测试，两台服务器初始数据一致）

主服务器（master）设置

新建一个用于备份的用户（直接用root用户也可以）：

GRANT FILE ON *.* TO backup@10.224.194.237 IDENTIFIED BY ‘pass’；

GRANT REPLICATION SLAVE ON *.* TO backup@10.224.194.237 IDENTIFIED BY &＃39;pass&＃39;;

编辑“/etc/my.cnf”（不同服务器可能路径不同）文件，在该文件添加以下内容：server-id=1 #设置服务器id，主从服务器要不同log-bin=mysqllog #启用二进制变更日志（即把所有对数据进行操作的SQL命令以二进制格式记入日志）#其中mysqllog是日志文件的名称，日志的文件名是mysqllog.n，其中n是一个6位数字的整数。binlog-do-db=test #指定需要启用二进制变更日志的数据库"test" binlog-ignore-db = mysql #指定不需要启用二进制变更日志的数据库"mysql"

重启mysqld服务，可以用mysql命令：SHOW MASTER STATUS;查看“启用二进制变更日志”情况

从服务器（slave）设置

编辑“/etc/my.cnf”（不同服务器可能路径不同）文件，在该文件添加以下内容：

server-id=2 #设置服务器id，主从服务器要不同replicate-do-db=test #指定需要从master同步过来的数据库"test" replicate-ignore-db = mysql #指定不需要从master同步过来的数据库"mysql" #设置master服务器的IP地址、登陆用户、密码、端口master-host=10.224.194.239 master-user=backup master-password=pass master-port=3306 master-connect-retry=60 #连接master服务器失败后重试的延迟时间slave-skip-errors=all #跳过所有错误继续执行同步工作log-slave-updates #启用从属服务器上的日志同步功能

注意：如果从服务器上存在master.info文件（如：/var/lib/mysql/master.info），要使以上配置选项生效，在重启mysqld服务前必须删除该文件。

重启mysqld服务，可以用mysql命令：SHOW SLAVE STATUS;查看同步情况

从数据库的相关命令：

slave st; slave start ; 开始停止从数据库。

show slave statusG; 显示从库正读取哪一个主数据库二进制日志

验证：

在主/次 服务器上创建一张表名为test01,最好设置主键：让后在主服务器上插入一条数据，此时会同步到次服务器上，如果没有成功，查看log，号配置项是否正确

create table test01

( name_id varchar(10) not null,

primary key(name_id)

)

insert into test01(name_id) value(1);

成功完成以上配置后，在主服务器A的test库里添加数据或删除数据，在从服务器B的test库里马上也能看到相应的变更。两台服务器的同步操作可以说是瞬间完成的。

本文出自 “迈小步、不停步！” 博客