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 mysql帐号一般在 master 与slave 设置为相同帐号笔窃冻炭煞梦？特别注意，如果你用linux / unix操作系统，那一定要注意一下防火墙firewall有没有限制mysql远程访问，如果是，最好是打开远程访问端口，并作好访问ip限制由于 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同步数据库）的情况：这就是使用的 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 ） 增加mysqldlog-bi n 二server-id=1注意，上面的log-bin二中的 等号 二 是不可少的在slave 的my.cnf修改mysqldserver-id=2 #如果有多个 slave 就改为不重复的id就好，在 mysql 4.1中，这个已经取消了master-host=10.10.10.22master-user二backup #同步用户帐号master-password=1234master-port=3306master-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| mysql-bi n.003 | 73注意 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->->->->G_FILE二'recorded_log_file_ name'.->这里，把上面的 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 adm ini strator 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进程重启一下，timeout时间限制，需要调整 timeout时间。执行这« LOAD TABLE tblname FROM MASTER 从机运行，从主机端重读指定的表的数据，每次只能读取一个，受个命令需要同步账号有 reload和super权限。以及对相应的库有 select权限。如果表比较大，要增加的值« LOAD DATA FROM MASTEF从机执行，从主机端重新读入所有的数据。执行这个命令需要同步账号有n et_read_timeout和 net_write_timeoutreload和super权限。以及对相应的库有select权限如果表比较大，要增加 net_read_timeout 和net write timeout 的值 CHANGE MASTER TO m
