mysql_数据库的同步问题



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'