当前位置：首页 > 编程日记 > 正文

工作中InnoDB引擎数据库主从复制同步心得

编程日记 2024-11-21 18:20:00

近期将公司的MySQL架构升级了，由原先的一主多从换成了DRBD+Heartbeat双主多从，正好手上有一个电子商务网站新项目也要上线了，用的是DRBD+Heartbeat双主一从，由于此过程还是有别于以前的MyISAM引擎的，所以这里也将其心得归纳总结了一下：

1）MySQL的replication过程是一个异步同步的过程，并非完全的主从同步，所以同步的过程中是有延迟的，如果做了读写分离的业务的话，建议也要监控此延迟时间；

2）MySQL的master与slave机器记得server-id要保持不一致，如果一样的话，replication过程中会出现如下报错:

Fatal error: The slave I/O threadstopsbecause master and slavehave equal MySQL server ids; these ids mustbedifferent for replication to work(or the --replicate-same-server-id optionmustbe used on slave but this doesnot always make sense; please check themanualbefore using it).

这个问题很好处理，即将slave机的server-id修改成跟master机器不一致即可。

3）我以前的一个误区就是，slave机器是用自己的二进制日志来完成replication过程的，其实不是这样的，根据复制的工作原理：slave服务器是copy主服务器的二进制日志到自己的中继日志，即relay-log日志（即centos3-relay-bin.000002这种名字的）中，然后再把更新应用用到自己的数据库上，所以slave机器是不需要开启二进制日志的，这样过程一样会成功的；除非是准备做主主架构，这才需要slave机器开启二进制日志，这个问题一直在导着我，我以一直以为slave机器搭建replication环境时是一定要开启二进制的，

4）在master机器上授权时，尽量只给某一个或某几个固定机器权限，让它们只有replication slav，replication client权限，尽量不要给grant权限；另外，虽然数据库我们一般是通过内网操作，但越是在在内网对MySQL数据库进行授权操作，越是要注意安全；

5）replication搭建过程按照正常流程走的话，一般很容易实施成功，如果出错的话，多检查下网络环境、权限问题，一般来说整个搭建过程应该还是会比较顺利的。

在数据库设计初期，我已经将此电子商务的数据库引擎定义为InnoDB，除了数据库中原有的系统表之外，其它表全部由MyISAM转成了InnoDB，原因有二：

1）电子商务业务会涉及到交易付款，在这种基本OLTP的应用中，InnoDB应该作为核心应用表的首选存储引擎；

2）DRBD系统重启时的过程会比较缓慢，会频繁的读表，如果表引擎为MyISAM的话极有可能出现损坏情况，为了造成不必要的问题，我将数据库的表引擎由MyISAM均转成了InnoDB引擎的表。

DRBD+Heartbeat+MySQL参考以前的工作文档，搭建的比较顺利，就是在搭建replication环境时遇到了1062报错，详细过程如下：

初期参考MySQL手册操作，取master机器的快照备份，用的是--single-transaction选项，然后同步过程频繁1062报错，报错日志如下：

Last_SQL_Error: Error 'Duplicate entry'd36ad91bff36308de540bbd9ae6f4279' for key 'PRIMARY'' on query. Defaultdatabase: 'myproject'. Query: 'INSERT INTO `lee_sessions` (`session_id`,`ip_address`, `user_agent`, `last_activity`, `user_data`) VALUES('d36ad91bff36308de540bbd9ae6f4279', '180.153.201.218', 'Mozilla/4.0',1353394206, '')'

后来改变思路，用--master-data选项来取主master快照备份，命令如下所示：

mysqldump -uroot --quick--flush-logs--master-data=1 -p myproject > myproject.sql

附注：--master-data的用法为：通过此参数来备份SQL文件时会建议一个slavereplication，当其值为1时，SQL文件中会记录change master语句；当其值为2时，change master会被写成SQL注释，--master-data在没有使用--single-transaction选项的情况下会自动使用lock-all-tables选项（即这二代选项不要搭配使用）。如何查找SQL中的的LOG_FILE及LOG_POS呢？我们可以用如下命令(请注意change单词要写成大写的)，如下所示：

grep "CHANGE"myproject.sql

命令显示结果如下：

CHANGE MASTER TOMASTER_LOG_FILE='mysql-bin.000008',MASTER_LOG_POS=106;

接下来的replication过程就不详细说明了，同步完成后我们经过相当长时间的观察，再也没1062报错了，如下所示：

mysql> show slave status \G;
*************************** 1.row***************************Slave_IO_State: Waitingformaster to send eventMaster_Host: 192.168.11.174Master_User: rep1Master_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000008Read_Master_Log_Pos: 27880Relay_Log_File:centos3-relay-bin.000002Relay_Log_Pos: 28025Relay_Master_Log_File: mysql-bin.000008Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 27880Relay_Log_Space: 28182Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:
1 row in set (0.00 sec)

工作中InnoDB引擎数据库主从复制同步心得以前的项目也比较多的牵涉到InnoDB数据库的备份及replication，较多的一个做法是停库进行replication，虽然也是解决问题的一种思路，但毕竟属于停机维护，在一些特殊应用场景中是不允许的，我们应该多尝试采用mysqldump这种逻辑备份方式来取master主机快照。

https://www.dkcj.cn/info/28512.html

面试官：因为这个语言，我淘汰了90%的人！

很多人都有这样的经历：大量重复性工作；日报、周报、各种报，无穷无尽；不计其数的数据提取琐碎繁杂的事务让工作的效率极低。如果可以一键完成就好了。对这些问题来说，最高效的解决途径就是 Python。1991 年，…

编程日记2024/11/21 18:10:00

SQL Server不能启动

SQL Server不能正常启动 I had a similar issue after uninstalling Visual Studio 2010 (which autmatically came with a Visual Studio Express 2013 install). I solved it by going through the follwing steps. Installing Visual Studio 2010 shell from here: https://…

编程日记2024/11/21 18:00:00

工作中InnoDB引擎数据库主从复制同步心得

相关文章：

面试官：因为这个语言，我淘汰了90%的人！

SQL Server不能启动

ASP.NET 配置节架构

IEEE迎来首位华人主席，马里兰大学终身教授刘国瑞当选

Visual C++ 2010 简介

Linux网络编程：基于UDP的程序开发回顾篇

四款5G版iPhone 12齐发，苹果股价却应声而跌

Linux编译器GCC的使用

jquery兼容IE和火狐下focus()事件

Access-Control-Allow-Origin这个header这个头不能设置通配符域名

MySQL Xtrabackup备份和恢复

吐血整理：手拿几个大厂offer的秘密武器！

.NET中获取电脑名，IP地址，当前用户

SpringMVC注解整理

Facebook是如何做搜索的？

JavaScript[对象.属性]集锦

c++ hook 钩子的使用介绍

小程序一次性上传多个本地图片，上拉加载照片以及图片加载延迟解决之道

测试项目案例思路

赠书 | Python人脸五官姿态检测

明白了这十个故事，你也就参悟了人生

java中的几种对象(PO,VO,DAO,BO,POJO)

jquery文件上传控件 Uploadify

AI 还原康乾盛世三代皇帝的样貌，简直太太太好玩了！

男人一生中要做的事

【BZOJ2157】旅游

PHP面向对象精要

码农新机会！2019-2020行业调查报告出炉，这个领域程序员缺口很大！

线性代数与矩阵论定理 1.5.6 拉格朗日插值公式

Android的ToolBar