MySQL 主从同步及全文索引

这两个都是自己在实际操作上遇到的具体问题,很常见也很实用。MySQL主从复制的需求是因为在将GitHub上一个BitTorrent DHT搜索引擎架设到服务器时,想通过复制实现异步数据同步。而全文索引也是想提高千万数据前提下模糊查询速度,将在之后写出。

主从同步

其中很多都是官方文档中意译过来的,英文文档可能更详细 https://dev.mysql.com/doc/refman/5.7/en/replication-howto.html

一、实现机制

主从同步是在主从复制的基础上个实现的。

MySQL支持两种复制方式:基于行复制和基于语句复制。但本质上都是通过在主库上记录二进制日志(binlog),然后从库上通过一个I/O线程从主库上读取binlog,然后传输到从库的中继日志中,然后从库的SQL线程从中继日志中读取中继日志,然后应用到从库的数据库中。这样就实现了主从同步。

当然实现A->B->C也是这个道理,只不过C读取的是B的 binlog。

二、配置步骤

1、创建复制帐号

创建单独的复制帐号,主从推荐都加,而权限只需要REPLICATION SLAVE 即可。另外开启MySQL远程连接自然也是必须的。

2、主库配置

主库必须开启二进制日志选项,并指定唯一的 serve-id,在配置文件my.cn或my.ini的[mysqld]部分修改,修改后重启。

server-id 必填,并且取值在1 ~ (2^32)-1,不为0

在复制设置中尽可能使InnoDB事务的持久性和一致性,您可在master my.cnf文件中使用innodb_flush_log_at_trx_commit = 1和sync_binlog = 1。

3、确定主库当前binlog位置

通过命令行在主库开启一个会话,阻止所有表写入操作(确保位置准确)

然后查看当前binlog 文件名(File)和位置(Position),需记住并在之后用到

在结束第四步后则释放锁

4、创建数据快照

这是后就有三种情况了:

  • 主从库都没有数据,也没有要导入的数据;
  • 主从库都没有数据,但有需要导入的数据;
  • 主库有存在的数据,但从库是新的空库;

第一种情况可直接跳到下一步,配置从库。

第二种情况也可直接跳到下一步,配置好从库后,将数据导入主库即自动同步到从库。当然也可分别导入主、从库,然后再从头进行主从同步配置。

第三种情况先将主库中数据导出,再导入到从库。

5、从库配置

从库修改配置,并重启数据库。

从库启动复制配置

启动复制,并查看状态

全文索引

一、前言

个人在实践中遇到的情况是这样的:在InnoDB引擎的一个70万+记录的表中,要模糊查询一个text 类型的字段。常见的like 模糊查询需要消耗1.7 + sec ,已经不让人满意了,更不用说更大量的数据。

二、过程

发现全文索引(FULLTEXT)可能是个不错的办法(当然也可使用一些搜索引擎软件,不过对于几十万量级好像没必要),不过在文档中发现:

1、MySQL是从5.6版本才开始支持InnoDB引擎的全文索引,语法层面上大多数兼容之前MyISAM的全文索引模式。但是不支持中、韩、日文

2、从MySQL 5.7.3开始InnoDB支持全文索引插件,用户可以以Plugin的模式来定义自己的分词规则,或是引入社区开发的全文索引解析器。于是可以使用支持中文的全文索引插件。

3、从MySQL5.7.6版本开始提供了一种内建的全文索引 ngram parser,可以很好的支持CJK字符集(中文、日文、韩文)。内建在代码中,该解析器默认安装,你可以通过指定索引属性(WITH PARSER ngram)来利用该parser。

当然如果数据量大的话,这可能需要花不少时间(对我可怜的ECS、VPS够呛的)当然这是值得的,通过全文索引能将查询时间降低到0.06 sec 左右。

而N-Gram是使用一种特殊的方式来进行分词,举个简单的例子,假设要对单词’abcd’进行分词,那么其分词结果为:

N取决于ngram_token_size的设置,默认值为2。

在执行查询时,用户传递的搜索词也会基于N-Gram进行分解后进行检索。而关于停词和分词处理 可参考官方博文 http://mysqlserverteam.com/innodb全文索引:n-gram-parser/

阿里的这篇特性讲解也很详细 http://mysql.taobao.org/monthly/2015/10/01/

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*