也谈mysql 数据库的同步问题(双机热备)

网上有不少关于 mysql 数据库服务器的数据同步文章，其实只有二位朋友发表的文章，其他都是转抄。
而这些文章中，仅对数据同步过程的操作作了一个记录，而没有把操作方法，原理与一些过程中出现的问题说明清楚。

因为工作需要，我公司需要对美国服务器上的 mysql 数据库同步到香港服务器上，仅对部分业务相关表进行同步，而同时又需要把香港服务器上的产品数据同步到美国服务器上，这是同一个数据库，不同表 table 的双向同步，我在设置过程中，参考了网上的中文文章与 msql.com 网站上的在线文档，多次成败，也算是把 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 = 这个选项的原因。

在同步过程中，最重要的同步参照物，就是同步使用那一个二进制日志文件，从那一条记录开始同步。
下面，我们来讲解最核心的过程


上面同步的单词是 synchronization ，呵呵简写 sync ，也是 linux 下一个重要操作

下面的官方文档，说明如何安装与设置 mysql 同步操作，当然，英文版
http://dev.mysql.com/doc/mysql/en/Replication_HOWTO.html

现在，我们用中文复述一下过程

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

2. 设置可访问 mysql 帐号，操作以英文为准
  说明： 2.1 mysql 帐号一般设置为限定 ip 访问，以保障安全性
 ??2.2 mysql 帐号一般在 master 与 slave 设置为相同帐号??笔窃冻炭煞梦?
 ??2.3 特别注意，如果你用 linux / unix 操作系统，那一定要注意一下防火墙 firewall
 ???有没有限制 mysql 远程访问，如果是，最好是打开远程访问端口，并作好访问 ip 限制
 ??2.4 由于 my.cnf 中要明文存储 mysql 帐号密码，请注意保护 my.cnf 不让其他用户访问到
 ???( 看来要向 mysql 说明下以后用密码存 passwd )

3. 第三步，当然是设置两个服务器要同步的数据库为同样的数据库了
  这里有一些技巧，包括 mysql 的 sql 指令说明下
 3.1 方法之一，就是英文说明中说的，先锁定数据库读写功能 ( 其实最好是停止 mysqld 服务，再作备份) 然后用 tar 备份数据库目录，转到 slave 服务器相同数据目录中

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

  3.3 以上两种方法都是 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 参数而保存有二进制日志

4. 接下来就是配置 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 =


5. 检查 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 操作，所以，同步成功有以下几个条件
  1. master 与 slave 有相同的数据库表结构，最好 database name 也一样 ( 可以设置为不一样的 database name)
  2. master 与 slave 有相同的初始数据记录，保证同步操作开始后两者数据一致
  3. master 必须使用 bin-log 二进制日志记录 ( 推荐 slave 也使用 bin-log )
  4. slave 是从 master 的 bin-log 是读取 sql 记录来同步，所以，从哪一条 log 开始读取很重要 ( 下面的第 7 条操作，就是保证 slave 能与 master 保持相同的记录读取，并读取正确的 bin-log 日志文件 )

6.

7. 在 slave 执行下面 sql 操作

mysql> CHANGE MASTER TO
  ->? MASTER_HOST='master_host_name',
  ->? MASTER_USER='replication_user_name',
  ->? MASTER_PASSWORD='replication_password',
  ->? MASTER_LOG_FILE='recorded_log_file_name',
  ->? MASTER_LOG_POS=recorded_log_position;

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


最后，测试一下吧


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


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

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

3. 如果 win2k 下的 mysql 为 master , 一定要注意 show master status 中显示出来的结果
4. win2k 下的 mysql 为 slave 时，注意不要把同步时间周期设置太长。

 

*******相关命令:

stop slave    #停止同步

start slave    #开始同步，从日志终止的位置开始更新。

SET SQL_LOG_BIN=0|1  #主机端运行，需要super权限，用来开停日志，随意开停，会造成主机从机数据不一致，造成错误

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n  # 客户端运行，用来跳过几个事件，只有当同步进程出现错误而停止的时候才可以执行。

RESET MASTER  #主机端运行,清除所有的日志，这条命令就是原来的FLUSH MASTER

RESET SLAVE   #从机运行，清除日志同步位置标志，并重新生成master.info
虽然重新生成了master.info,但是并不起用，最好，将从机的mysql进程重启一下，

LOAD TABLE tblname FROM MASTER #从机运行，从主机端重读指定的表的数据，每次只能读取一个，受timeout时间限制，需要调整timeout时间。执行这个命令需要同步账号有reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值

LOAD DATA FROM MASTER  #从机执行，从主机端重新读入所有的数据。执行这个命令需要同步账号有reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值

CHANGE MASTER TO master_def_list  #在线改变一些主机设置，多个用逗号间隔,比如
CHANGE MASTER TO
  MASTER_HOST='master2.mycompany.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='bigs3cret'

FLUSH MASTER;

FLUSH SLAVE;

MASTER_POS_WAIT() #从机运行

SHOW MASTER STATUS #主机运行，看日志导出信息

SHOW SLAVE HOSTS #主机运行，看连入的从机的情况。

SHOW SLAVE STATUS (slave)

show status slave\G;

SHOW MASTER LOGS (master)

SHOW BINLOG EVENTS [ IN 'logname' ] [ FROM pos ] [ LIMIT [offset,] rows ]

PURGE [MASTER] LOGS TO 'logname' ; PURGE [MASTER] LOGS BEFORE 'date'