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

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

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

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

Fatal error: The slave I/O thread stops because master and slavehave equal MySQL server ids; these ids must be different for replication to work(or the --replicate-same-server-id option must be used on slave but this doesnot always make sense; please check the manual before 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. Default database: 'mypharma'. 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快照备份,命令如下所示:

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

--master-data的用法为:通过此参数来备份SQL文件时会建议一个slave replication,当其值为1时,SQL文件中会记录change master语句;当其值为2时,change master会被写成SQL注释,--master-data在没有使用--single-transaction选项的情况下会自动使用lock-all-tables选项(即这二代选项不要搭配使用)。

如何查找SQL中的的LOG_FILE及LOG_POS呢?我们可以用如下命令(请注意change单词要写成大写的),如下所示:

  1. [root@centos1 ~]# grep "CHANGE " myproject.sql 
  2.  
  3. CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008'MASTER_LOG_POS=106

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

  1. mysql> show slave status \G; 
  2. *************************** 1. row *************************** 
  3.                Slave_IO_State: Waiting for master to send event 
  4.                   Master_Host: 192.168.11.174 
  5.                   Master_User: rep1 
  6.                   Master_Port: 3306 
  7.                 Connect_Retry: 60 
  8.               Master_Log_File: mysql-bin.000008 
  9.           Read_Master_Log_Pos: 27880 
  10.                Relay_Log_File: centos3-relay-bin.000002 
  11.                 Relay_Log_Pos: 28025 
  12.         Relay_Master_Log_File: mysql-bin.000008 
  13.              Slave_IO_Running: Yes 
  14.             Slave_SQL_Running: Yes 
  15.               Replicate_Do_DB:  
  16.           Replicate_Ignore_DB:  
  17.            Replicate_Do_Table:  
  18.        Replicate_Ignore_Table:  
  19.       Replicate_Wild_Do_Table:  
  20.   Replicate_Wild_Ignore_Table:  
  21.                    Last_Errno: 0 
  22.                    Last_Error:  
  23.                  Skip_Counter: 0 
  24.           Exec_Master_Log_Pos: 27880 
  25.               Relay_Log_Space: 28182 
  26.               Until_Condition: None 
  27.                Until_Log_File:  
  28.                 Until_Log_Pos: 0 
  29.            Master_SSL_Allowed: No 
  30.            Master_SSL_CA_File:  
  31.            Master_SSL_CA_Path:  
  32.               Master_SSL_Cert:  
  33.             Master_SSL_Cipher:  
  34.                Master_SSL_Key:  
  35.         Seconds_Behind_Master: 0 
  36. Master_SSL_Verify_Server_Cert: No 
  37.                 Last_IO_Errno: 0 
  38.                 Last_IO_Error:  
  39.                Last_SQL_Errno: 0 
  40.                Last_SQL_Error:  
  41. 1 row in set (0.00 sec) 

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

目前在测试ext3和ext4文件系统对数据库的影响,感觉MySQL性能优化不大;反而,固态SSD硬盘对于提升磁盘I/O方面确实影响不少,这方面有研究的朋友也欢迎来信交流。

【编辑推荐】

  1. 适合初学者的MySQL学习笔记之库操作示例
  2. 适合初学者的MySQL学习笔记之表操作示例
  3. 适合初学者的MySQL学习笔记之MySQL管理心得
  4. 适合初学者的MySQL学习笔记之MySQL查询示例
  5. 适合初学者的MySQL学习笔记之管理员常用操作总结

责任编辑:彭凡 来源: 51CTO
相关推荐

2011-04-06 09:59:00

MySQL数据库主从复制

2019-05-10 15:30:18

数据库主从复制MySQL

2014-07-04 10:41:19

redis数据库缓存

2020-01-03 16:30:14

数据库读写分离分库

2023-03-19 11:53:27

2023-03-19 22:38:12

逻辑复制PostgreSQL

2023-07-03 08:57:45

Master服务TCP

2023-09-24 14:32:15

2019-02-11 09:04:24

MySQL主从复制数据库

2020-09-24 06:39:58

MySQL数据库

2011-07-27 09:33:16

MySQL数据库INNODB数据库引擎

2021-06-08 07:48:27

MySQL主从配置

2024-03-01 18:33:59

MySQL节点数据

2010-08-27 09:59:51

SQL Server

2017-06-21 08:30:20

MySQL原因解决办法

2023-12-25 08:02:09

2010-11-03 08:41:55

MySQL

2017-12-15 10:20:56

MySQLInnoDB同步机制

2021-01-12 08:03:19

Redis数据系统

2017-10-11 15:40:20

MySQL主从复制拓扑结构
点赞
收藏

51CTO技术栈公众号