部署MySQL延迟从库的几个好处

导读

MySQL延迟从库的好处多多,你了解吗

MySQL的主从复制(replication)关系,不太严谨的叫法是“同步”或者“主从同步”。实际上在早期,MySQL的主从并不能实现真正的“同步”(sync),而是“异步”的(async)。

MySQL主从复制它可以有多种模式,最经典的也是最早出现的异步复制(async replication),从5.5版本开始有了半同步复制(semi-sync replication),到了5.7又有了增强半同步。本文要讨论的延迟从库,也是在5.6之后才有的功能,在这之前需要用percona pt-slave-delay 工具来变相实现。

另外,从5.6版本开始增加了并行复制,不过这时还是基于schema的并行模式(slave-parallel-type=DATABASE),效率非常差,意义不大。到了5.7,才实现了真正的并行复制(slave-parallel-type=LOGICAL_CLOCK),复制效率提升很多;还有新增了多源复制,很方便的就能实现多主一从的架构。

了解完MySQL复制的简史,我们切入主题。

MySQL延迟从库的好处主要有几点:
1、误删除时,能更快恢复数据。 有时候手抖了,把线上数据给误删除了,或者误删除库、表、其他对象,或不加WHERE条件的更新、删除,都可以让延迟从库在误操作前的时间点停下,然后进行恢复。

2、把延迟从库作为专用的备份节点。虽然有一定的延迟,但并不影响利用该节点作为备份角色,也不影响生产节点数据库库。

3、还可以把延迟从库当做一些问题、案例研究的对象。个别时候,可能有些binlog event在普通从库上会有问题(例如早期版本中无主键会导致从库更新非常慢的经典问题),这时就有时间在延迟从库上慢慢琢磨研究了。

启用延迟从库的方法也挺简单的,下面是在MySQL 8.0的做法:

#直接用 CHANGE MASTER TO 设置,后面的N单位是秒数
CHANGE MASTER TO MASTER_DELAY = N

当发生误操作需要让延迟从库在某个位置上停下来时,用下面的命令:

START SLAVE
  UNTIL {
    #1、直到指定的GTID位置停下
    {SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS} = gtid_set

    #2、直到指定的binlog位置停下
    |   MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos

    #3、直到指定的relay log位置停下
    |   RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos

    #4、直到slave上多个并行线程之前没有延迟差距了就停下
    #因为多线程复制,不同线程的复制进度不一样,因此有差距
    |   SQL_AFTER_MTS_GAPS  }

P.S,从5.7起,修改 MASTER_DELAY 选项可以在线立即生效,而无需重启 slave 线程。

至于具体 MASTER_DELAY 设置多少合适,要估算如果发生误操作时,DBA平均能到现场的时间,一般建议1小时左右。

延伸阅读

16.3.10 Delayed Replication,https://dev.mysql.com/doc/refman/8.0/en/replication-delayed.html

MySQL复制 | SLAVE复制为什么停了

MySQL复制 | SLAVE延迟很大怎么办

MySQL复制 | 精确监控SLAVE延迟

MySQL复制 | 列类型被自动修改导致复制失败

如何确认当前事务的隔离级别

导读

我们知道可以在线修改全局或会话级的事务隔离级别,也可以修改时不指定GLOBAL/SESSION关键字,而只影响当前会话中的下一个事物,但怎么确认修改生效了呢?

我们知道,可以分别用 @@global.transaction_isolation 和 @@ session.transaction_isolation来查看全局或会话级隔离级别,或者用 @@transaction_isolation 查看会话级隔离级别。也就是说: @@session.transaction_isolation 和 @@transaction_isolation 二者等价。

在MySQL里,可以在线修改全局或会话级的事务隔离级别,例如这样: enter image description here

可以看到全局和会话级的隔离级别是不一样的

另外,我们也知道,在修改隔离级别时若不指定 GLOBAL/SESSION 关键字,则只会针对当前会话的下一个事务生效,下一个事务结束后,又会恢复当前会话此前设定的隔离级别。

但可能有些同学不太放心,或者可能就想确认某个事务的隔离级别。接下来,我们一起来看下,怎么查看确认某个事务的隔离级别。

这种情况下,我们就需要借助 information_schema.innodb_trx这个视图了,看下面例子。 enter image description here enter image description here

最后几点结论:

1、执行 select @@tx_isolation 等同于执行 select @@session.tx_isolation(或者执行 show variables like ‘transaction_isolation’),只能看到当前会话(session)的隔离级别。

2、执行 select @@global.tx_isolation 查看全局隔离级别设置,和执行 show global variables like ‘transaction_isolation’ 一样。

3、想要查看具体某个事务里的隔离级别,需要在 information_schema.innodb_trx 视图中确认。

相关文章

FAQ系列 | 修改事务隔离级别的暗门,https://mp.weixin.qq.com/s/Q6237ZCBqR4ZBERyzmYiUg

15.7.2.1 Transaction Isolation Levels,https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

enjoy MySQL :)

CHAR和VARCHAR存取的差别

导读

你真的知道CHAR和VARCHAR类型在存储和读取时的区别吗?

还是先抛几条结论吧:
1、存储的时候,CHAR总是会补足空格后再存储,不管用户插入数据时尾部有没有包含空格。

2、存储的时候,VARCHAR不会先补足空格后再存储,但如果是用户在插入时特地加了空格那就会如实存储,而不会给删除。

3、读取数据时,CHAR总是会删除尾部空格(哪怕是写入时包含空格)。

4、读取数据时,VARCHAR总是如实取出之前存入的值(如果存储时尾部包含空格,就会继续保留着,不会像CHAR那样删除尾部空格)。

下面是测试验证过程。
1、测试CHAR类型
表结构:

CREATE TABLE `tchar` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `c1` char(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入几条记录:

insert into tchar values (1, concat('a', repeat(' ',19)));
insert into tchar values (2, concat(' ', repeat('a',19)));
insert into tchar values (3, 'a');
insert into tchar values (4, ' ');
insert into tchar values (5, '');

查看存储结构:

(1) INFIMUM record offset:99 heapno:0 ...
(2) SUPREMUM record offset:112 heapno:1 ...
(3) normal record offset:126 heapno:2 ... <- id=1
(4) normal record offset:169 heapno:3 ... <- id=2
(5) normal record offset:212 heapno:4 ... <- id=3
(6) normal record offset:255 heapno:5 ... <- id=4
(7) normal record offset:298 heapno:6 ... <- id=5

看到这坨东西有点懵是不是,还记得我给你们安利过的一个工具不,看这里:innblock | InnoDB page观察利器。

可以看到,无论我们存储多长的字符串进去,每条记录实际都是占用43(169-126=43)字节。由此结论1成立。
简单说下,43字节的由来:
DB_TRX_ID, 6字节。
DB_ROLL_PTR, 7字节。
id, int, 4字节。
c1, char(20), 20字节;因为是CHAR类型,还需要额外1字节。
每条记录总是需要额外5字节头信息(row header)。
这样总的加起来就是43字节了。

再看下读取tchar表的结果:

select id,concat('000',c1,'$$$'),length(c1) from tchar ;
+----+----------------------------+------------+
| id | concat('000',c1,'$$$')     | length(c1) |
+----+----------------------------+------------+
|  1 | 000a$$$                    |          1 | <- 删除尾部空格
|  2 | 000 aaaaaaaaaaaaaaaaaaa$$$ |         20 |
|  3 | 000a$$$                    |          1 |
|  4 | 000$$$                     |          0 | <- 删除尾部空格,结果和id=5一样
|  5 | 000$$$                     |          0 |
+----+----------------------------+------------+

2、测试VARCHAR类型
表结构:

CREATE TABLE `tvarchar` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `c1` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

插入几条记录:

insert into tvarchar values (1, concat('a', repeat(' ',19)));
insert into tvarchar values (2, concat(' ', repeat('a',19)));
insert into tvarchar values (3, 'a');
insert into tvarchar values (4, ' ');
insert into tvarchar values (5, '');
insert into tvarchar values (6, '');

查看存储结构:

(1) INFIMUM record offset:99 heapno:0 ...
(2) SUPREMUM record offset:112 heapno:1 ...
(3) normal record offset:126 heapno:2 ... <- id=1
(4) normal record offset:169 heapno:3 ... <- id=2
(5) normal record offset:212 heapno:4 ... <- id=3
(6) normal record offset:236 heapno:5 ... <- id=4
(7) normal record offset:260 heapno:6 ... <- id=5
(8) normal record offset:283 heapno:7 ... <- id=6

可以看到,几条记录的字节数分别是:43、43、24、24、23、23(最后一条记录和id=5那条记录一样)。
对上面这个结果有点诧异是不是,尤其是id=1的记录(插入的是’a…后面19个空格’),居然也要消耗43字节,这就佐证了上面的结论2。
同样的,id=3和id=4这两条记录都是占用24字节,而id=5和id=6这两条记录都是占用23字节(没有额外存储字符串的字节数,只有id列4个字节)。

再看下读取tvarchar表的结果:

select id,concat('000',c1,'$$$'),length(c1) from tvarchar;
+----+----------------------------+------------+
| id | concat('000',c1,'$$$')     | length(c1) |
+----+----------------------------+------------+
|  1 | 000a                   $$$ |         20 | <- 读取结果中没有删除尾部的空格
|  2 | 000 aaaaaaaaaaaaaaaaaaa$$$ |         20 |
|  3 | 000a$$$                    |          1 |
|  4 | 000 $$$                    |          1 | <- 读取结果中没有删除此空格
|  5 | 000$$$                     |          0 |
|  6 | 000$$$                     |          0 |
+----+----------------------------+------------+

总的来说,可以总结成两条结论:
1、从读取的结果来看,CHAR类型列看起来像是在存储时把空格给吃了,但实际上只是在读取时才给吃了(显示层面上把空格删除了)。
2、从读取的结果来看,VARCHAR类型列看起来像是反倒保留了多余的空格,实际上也是只在读取时才恢复这些空格(但实际物理存储时还是会删掉这些空格)。

最后,来看下文档里怎么说的:

When CHAR values are stored, they are right-padded with spaces to the
specified length. 简言之,CHAR列在存储时尾部加空格补齐长度。

When CHAR values are retrieved, trailing spaces are removed unless the
PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.
简言之,CHAR列在读取时会去掉尾部空格,除非设置sql_mode值PAD_CHAR_TO_FULL_LENGTH=1。

VARCHAR values are not padded when they are stored.
简言之,存VARCHAR时尾部不加空格。

Trailing spaces are retained when values are stored and retrieved, in
conformance with standard SQL. 简言之,读取VARCHAR时会显示空格。

以上测试使用的版本及环境:

mysql> select version()\G
...
version(): 8.0.15

mysql> select @@sql_mode\G
...
@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

参考文档
11.4.1 The CHAR and VARCHAR Types,https://dev.mysql.com/doc/refman/5.7/en/char.html

innblock | InnoDB page观察利器

更多MySQL有趣冷知识,还可以看这里哦
enter image description here

玩转MySQL 8.0源码编译

导读

最近十年来又一次玩玩MySQL源码编译安装,挺D疼的。

这阵子结课了,闲来无事就继续折腾之前记录的一些TODO,其中一个是令人D疼的MySQL源码编译

此前我最后一次编译MySQL源码大概10年前了,再后来有人问我怎么编译源码安装,我一般都是建议直接下载二进制包安装,其实真挺方便点的,性能上的损失也是微乎其微。

编译安装MySQL前,需要先安装boost,因此有两种不同的安装方案。

提示下,编译MySQL 8.0版本,需要gcc版本5.3.0以上才行。我这次的编译环境是CentOS 7.5,其gcc版本是4.8.5,因此我在后面附上安装新版本gcc的方法,并且在编译参数中特别指定了新版本的绝对路径(这里有个小插曲,一开始我设置了环境变量,不行;修改Makefile参数,也不行;后来把新版本gcc做个软链接就行了。最后在重庆八怪的提醒下,才知道原来还可以在编译参数里设置绝对路径)。

先说第一种,自带boost的源码编译
首先,下载带boost代码的MySQL源码包,在官网下载页面有相应的提示:
enter image description here

解压缩后,就可以开始编译了。首先,指定编译的选项:

[root@yejr.me]# cd /opt/src/mysql-8.0.15/
cmake . \
-DWITH_BOOST=/opt/mysql-8.0.15/boost/ \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-8.0.15 \
-DMYSQL_DATADIR=/data/mysql \
-DWITHOUT_CSV_STORAGE_ENGINE=1 \
-DWITHOUT_BLACKHOLD_STORAGE_ENGINE=1 \
-DWITHOUT_FEDERATED_STORAGE_ENGINE=1 \
-DWITHOUT_ARCHIVE_STORAGE_ENGINE=1 \
-DWITHOUT_MRG_MYISAM_STORAGE_ENGINE=1 \
-DWITHOUT_NDBCLUSTER_STORAGE_ENGINE=1 \
-DFORCE_INSOURCE_BUILD=1 \
-DCMAKE_CXX_COMPILER=/usr/local/bin/g++ \
-DCMAKE_C_COMPILER=/usr/local/bin/gcc

在上面的参数中,我把不常用的引擎直接给禁用了,读者们若有需要个别引擎可自行删除对应选项即可。而DEBUG、SSL等几个选项则建议不要禁用。

为了好玩,也为了应对某些流氓安全扫描工具(你们懂得的^@@^),我特地把MySQL版本号修改掉,只需修改 mysql_version.h 文件即可,例如:

[root@yejr.me]# vim include/mysql_version.h

...
#define MYSQL_SERVER_VERSION       "3306.0.15"
#define MYSQL_BASE_VERSION         "mysqld-3306.0"
#define MYSQL_SERVER_SUFFIX_DEF    ""
#define MYSQL_VERSION_ID            33060015
#define LIBMYSQL_VERSION           "3306.0.15"
#define LIBMYSQL_VERSION_ID         33060015

接下来就是执行 make && make install ,顺利的话就完成编译并安装了。如果编译错误,则删除CMakeCache.txt文件后解决对应错误,重新编译即可。

编译完后看到的MySQL版本号就是这样的了:

[root@yejr.me]# /usr/local/mysql/bin/mysqld --verbose -V
/usr/local/mysql/bin/mysqld  Ver 3306.0.15 for Linux on x86_64 (Source distribution)

有没有感觉有点酷酷的,嘿。

再说第二种,自行安装boost后再编译
这种方式实在费劲,强烈不推荐。

可以采用YUM/RPM包方式安装boost,也可以自己下载源码包编译安装,或在编译MySQL时指定参数自行下载编译(建议用这个方法)。

下面演示的版本是Percona Server 8.0.15,要求boost版本是1.68.0以上,而CentOS 7.5安装的boost版本是1.53.0,所以需要自行下载安装。

P.S,需要下载PRM包的话,可以在这里搜索相应版本下载

https://www.rpmfind.net/linux/rpm2html/search.php

如果没找到合适的版本,可以在下面这个地址下载 boost-1.69.0 版本源码包。

https://sourceforge.net/projects/boost/

下载后,执行编译并安装即可。

[root@yejr.me]# ./bootstrap.sh --prefix=/usr/local/boost
[root@yejr.me]# ./b2 install

安装完boost后,再开始编译MySQL。

另外,上面有提到过,如果不想事先手动安装boost,可以增加一个 DOWNLOAD_BOOST 参数,就像下面这样:

[root@yejr.me]# cd /opt/src/percona-server-8.0.15-5
cmake . \
-DOWNLOAD_BOOST=1 \
-DWITH_BOOST=/opt/src/percona-8.0.15-5/boost \
-DCMAKE_INSTALL_PREFIX=/usr/local/percona-8.0.15-5 \
-DMYSQL_DATADIR=/data/mysql \
-DWITHOUT_CSV_STORAGE_ENGINE=1 \
-DWITHOUT_BLACKHOLD_STORAGE_ENGINE=1 \
-DWITHOUT_FEDERATED_STORAGE_ENGINE=1 \
-DWITHOUT_ARCHIVE_STORAGE_ENGINE=1 \
-DWITHOUT_MRG_MYISAM_STORAGE_ENGINE=1 \
-DWITHOUT_NDBCLUSTER_STORAGE_ENGINE=1 \
-DWITHOUT_TOKUDB_STORAGE_ENGINE=1 \
-DWITHOUT_TOKUDB=1 \
-DWITHOUT_ROCKSDB_STORAGE_ENGINE=1 \
-DWITHOUT_ROCKSDB=1 \
-DFORCE_INSOURCE_BUILD=1 \
-DCMAKE_CXX_COMPILER=/usr/local/bin/g++ \
-DCMAKE_C_COMPILER=/usr/local/bin/gcc

后面同样是执行 make && make install 等着顺利编译安装即可。

enjoy mysql 8.0。

附录:在CentOS 7.5下安装gcc 5.5.0

安装官方MySQL 8.0需要用gcc 5.3.0以上版本,所以需要先安装高版本gcc,我这里选择的是gcc-5.5.0。

在这里下载:

http://ftp.tsukuba.wide.ad.jp/software/gcc/releases/

解压缩后,执行下面的命令,下载其他依赖包:

[root@yejr.me]# cd gcc-5.5.0
#下载mpfr、gmp、mpc、isl等依赖包
[root@yejr.me]# ./contrib/download_prerequisites

#开始编译gcc
[root@yejr.me]# mkdir build && cd build
[root@yejr.me]# ../configure --enable-checking=release \
--enable-languages=c,c++ --disable-multilib
[root@yejr.me]# make && make install

看起来其实挺简单的对不对 :)

参考

10分钟让你明白MySQL是如何利用索引的

作者:王航威 – fordba.com 来源:http://fordba.com/spend-10-min-to-understand-how-mysql-use-index.html 备注:王航威是知数堂第8期同学,现任职有赞DBA

一、前言

在MySQL中进行SQL优化的时候,经常会在一些情况下,对MySQL能否利用索引有一些迷惑。

譬如:

  1. MySQL 在遇到范围查询条件的时候就停止匹配了,那么到底是哪些范围条件?
  2. MySQL 在LIKE进行模糊匹配的时候又是如何利用索引的呢?
  3. MySQL 到底在怎么样的情况下能够利用索引进行排序?

今天,我将会用一个模型,把这些问题都一一解答,让你对MySQL索引的使用不再畏惧


二、知识补充

key_len

EXPLAIN执行计划中有一列 key_len 用于表示本次查询中,所选择的索引长度有多少字节,通常我们可借此判断联合索引有多少列被选择了。

在这里 key_len 大小的计算规则是:

  • 一般地,key_len 等于索引列类型字节长度,例如int类型为4 bytes,bigint为8 bytes;
  • 如果是字符串类型,还需要同时考虑字符集因素,例如:CHAR(30) UTF8则key_len至少是90 bytes;
  • 若该列类型定义时允许NULL,其key_len还需要再加 1 bytes;
  • 若该列类型为变长类型,例如 VARCHAR(TEXT\BLOB不允许整列创建索引,如果创建部分索引也被视为动态列类型),其key_len还需要再加 2 bytes;

三、哪些条件能用到索引

首先非常感谢登博,给了我一个很好的启发,我通过他的文章,然后结合自己的理解,制作出了这幅图

乍一看,是不是很晕,不急,我们慢慢来看

图中一共分了三个部分:

  1. Index Key :MySQL是用来确定扫描的数据范围,实际就是可以利用到的MySQL索引部分,体现在Key Length。
  2. Index Filter:MySQL用来确定哪些数据是可以用索引去过滤,在启用ICP后,可以用上索引的部分。
  3. Table Filter:MySQL无法用索引过滤,回表取回行数据后,到server层进行数据过滤。

我们细细展开。

Index Key

Index Key是用来确定MySQL的一个扫描范围,分为上边界和下边界。

MySQL利用=、>=、> 来确定下边界(first key),利用最左原则,首先判断第一个索引键值在where条件中是否存在,如果存在,则判断比较符号,如果为(=,>=)中的一种,加入下边界的界定,然后继续判断下一个索引键,如果存在且是(>),则将该键值加入到下边界的界定,停止匹配下一个索引键;如果不存在,直接停止下边界匹配。

exp: idx_c1_c2_c3(c1,c2,c3) where c1>=1 and c2>2 and c3=1 –> first key (c1,c2) –> c1为 ‘>=’ ,加入下边界界定,继续匹配下一个 –> c2 为 ‘>’,加入下边界界定,停止匹配

上边界(last key)和下边界(first key)类似,首先判断是否是否是(=,<=)中的一种,如果是,加入界定,继续下一个索引键值匹配,如果是(<),加入界定,停止匹配。

exp: idx_c1_c2_c3(c1,c2,c3) where c1<=1 and c2=2 and c3<3 –> first key (c1,c2,c3) –> c1为 ‘<=’,加入上边界界定,继续匹配下一个 –> c2为 ‘=’加入上边界界定,继续匹配下一个 –> c3 为 ‘<‘,加入上边界界定,停止匹配

注:这里简单的记忆是,如果比较符号中包含’=’号,’>=’也是包含’=’,那么该索引键是可以被利用的,可以继续匹配后面的索引键值;如果不存在’=’,也就是’>’,'<‘,这两个,后面的索引键值就无法匹配了。同时,上下边界是不可以混用的,哪个边界能利用索引的的键值多,就是最终能够利用索引键值的个数。

Index Filter

字面理解就是可以用索引去过滤。也就是字段在索引键值中,但是无法用去确定Index Key的部分。

exp: idex_c1_c2_c3 where c1>=1 and c2<=2 and c3 =1 index key –> c1 index filter–> c2 c3

这里为什么index filter 只是c1呢?因为c2 是用来确定上边界的,但是上边界的c1没有出现(<=,=),而下边界中,c1是>=,c2没有出现,因此index key 只有c1字段。c2,c3 都出现在索引中,被当做index filter。

Table Filter

无法利用索引完成过滤,就只能用table filter。此时引擎层会将行数据返回到server层,然后server层进行table filter。


四、Between 和Like 的处理

那么如果查询中存在between 和like,MySQL是如何进行处理的呢?

Between

where c1 between  'a' and 'b' 等价于 where c1>='a' and c1 <='b',所以进行相应的替换,然后带入上层模型,确定上下边界即可。

Like

首先需要确认的是%不能是最在最左侧,where c1 like '%a' 这样的查询是无法利用索引的,因为索引的匹配需要符合最左前缀原则。

where c1 like 'a%'  其实等价于 where c1>='a' and c1<'b' 大家可以仔细思考下。


五、索引的排序

在数据库中,如果无法利用索引完成排序,随着过滤数据的数据量的上升,排序的成本会越来越大,即使是采用了limit,但是数据库是会选择将结果集进行全部排序,再取排序后的limit 记录,而且MySQL 针对可以用索引完成排序的limit 有优化,更能减少成本。

Make sure it uses index It is very important to have ORDER BY with LIMIT executed without scanning and sorting full result set, so it is important for it to use index – in this case index range scan will be started and query execution stopped as soon as soon as required amount of rows generated.

<

pre class=””>CREATE TABLE t1 (
 
id int(11) NOT NULL AUTO_INCREMENT,
 
c1 int(11) NOT NULL DEFAULT ‘0’,
 
c2 int(11) NOT NULL DEFAULT ‘0’,
 
c3 int(11) NOT NULL DEFAULT ‘0’,
 
c4 int(11) NOT NULL DEFAULT ‘0’,
 
c5 int(11) NOT NULL DEFAULT ‘0’,
 PRIMARY KEY
(id),
 KEY
idx_c1_c2_c3 (c1,c2,c3)) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 select * from t1;
+—-+—-+—-+—-+—-+—-+
| id | c1 | c2 | c3 | c4 | c5 |
+—-+—-+—-+—-+—-+—-+
|  1 |  3 |  3 |  2 |  0 |  0 |
|  2 |  2 |  4 |  5 |  0 |  0 |
|  3 |  3 |  2 |  4 |  0 |  0 |
|  4 |  1 |  3 |  2 |  0 |  0 |
|  5 |  1 |  3 |  3 |  0 |  0 |
|  6 |  2 |  3 |  5 |  0 |  0 |
|  7 |  3 |  2 |  6 |  0 |  0 |
+—-+—-+—-+—-+—-+—-+
7 rows in set (0.00 sec)

select c1,c2,c3 from t1;
+—-+—-+—-+
| c1 | c2 | c3 |
+—-+—-+—-+
|  1 |  3 |  2 |
|  1 |  3 |  3 |
|  2 |  3 |  5 |
|  2 |  4 |  5 |
|  3 |  2 |  4 |
|  3 |  2 |  6 |
|  3 |  3 |  2 |
+—-+—-+—-+
7 rows in set (0.00 sec)

存在一张表,c1,c2,c3上面有索引,select c1,c2,c3 from t1; 查询走的是索引全扫描,因此呈现的数据相当于在没有索引的情况下select c1,c2,c3 from t1 order by c1,c2****,c3``; 的结果。 因此,索引的有序性规则是怎么样的呢?

c1=3 —> c2 有序,c3 无序 c1=3,c2=2 — > c3 有序 c1 in(1,2) —> c2 无序 ,c3 无序

有个小规律,idx_c1_c2_c3,那么如何确定某个字段是有序的呢?c1 在索引的最前面,肯定是有序的,c2在第二个位置,只有在c1 唯一确定一个值的时候,c2才是有序的,如果c1有多个值,那么c2 将不一定有序,同理,c3也是类似


六、小结

针对MySQL索引,我这边只是提到了在单表查询情况下的模型,通过这篇文章,想必大家应该了解到MySQL大部分情况下是如何利用索引的,如果存在疑问,欢迎联系我😯

参考

您可能感兴趣的文章:

GROUP BY另类优化技巧

分享嘉宾:知数堂〖SQL开发优化班〗讲师郑松华,韩国Infobridge的SQL优化专家&7年SQL开发和调优经验&资深数据库工程师。

本次主题《GROUP BY另类优化技巧》,主要内容是从 GROUP BY、ORDER BY的基础语法到内部算法,最后到实际应用,对需要开发含有复杂的排序功能的同学们更是提供了思路。

内容干货满满,实用性强,小伙伴们快来围观吧!

提示:PPT的内容有限,建议直接观看视频,效果更佳!

1、资料发布

本次公开课的PPT、视频以及课中提到的相关学习资料均已上传到百度云盘,链接: https://pan.baidu.com/s/1bOau9w,欢迎转存及转发。

2、优惠资讯

  • SQL开发与优化课程:郑老师主讲《SQL开发与优化》课程,助力DBA、开发工程师等加薪升职!首期课程预计5月初开班,目前筹备期特享原价直减1200元超低优惠折特惠,仅需3000元,机会不容错过哦!
  • MySQL DBA实战班、优化提升班:持续招生中,第十一期课程于4月24日开班,双班报名更优惠;
  • Python运维开发班:从零基础入学,结业可达中级Python工程师水平,随到随学,持续招生中扫码加入QQ群 579036588 撩各位助教妹子获得最新课程信息。

3、你问我答

  • group by的效率和distinct与order by的效率比较哪个更好 ?

答:没有区别,因为没有 limit 关键字,所以两个都是进行全局扫描 。

  • 老师请问如何尽快学好SQL 优化方法论呢 ?

答:报班学习,因为有系统课程+学习氛围+讨论,能有效提升学习的效率。

  •  推荐理解MySQL底层运行机制的资料

答:叶老师是国内MySQL圈里权威人物,他的课程值得信赖。

  • select a ,b from tab where c=2 group by a 怎么优化

答:首先 这个SQL是有点 问题的 ,原因是这里的b没有聚合函数,不能保证结果的严谨;其次,因为数据分布不明确,无法给予,因为如果c=2选择率特别好,没啥数据,那么只要在c中有索引就行,这样的假设将会不断,所以光凭这个就无法提供优化建议。

  •  order by a is null,a asc这个例子如下,这样比较容易理解:

答:zst01@3306>[employees]>select emp_no , emp_no is null  from t_order order by  emp_no is  null ,emp_no desc ;

+——–+—————-+

| emp_no | emp_no is null |

+——–+—————-+

|  50449 |              0 |

|  49667 |              0 |

|  48317 |              0 |

|  40983 |              0 |

|  31112 |              0 |

|  30970 |              0 |

|  24007 |              0 |

|  22744 |              0 |

|  10004 |              0 |

|   NULL |              1 |

+——–+—————-+

  • SQL开发能力较弱,有没有什么方法可以提高?

答:要综合提升,还是需要系统的学习,并辅以实战操练,报班就可以提供给你这个氛围,能帮助尽快提升SQL开发能力。

优化案例 | CASE WHEN进行SQL改写优化

导读

今天给大家分享一个通过SQL改写而独辟蹊径的SQL优化案例

待优化场景

发现SLOW QUERY LOG中有下面这样一条记录:

...
# Query_time: 59.503827  Lock_time: 0.000198  Rows_sent: 641227  Rows_examined: 13442472  Rows_affected: 0
...
select uid,sum(power) powerup from t1 where 
date>='2017-03-31' and 
UNIX_TIMESTAMP(STR_TO_DATE(concat(date,' ',hour),'%Y-%m-%d %H'))>=1490965200 and 
UNIX_TIMESTAMP(STR_TO_DATE(concat(date,' ',hour),'%Y-%m-%d %H'))<1492174801  and 
aType in (1,6,9) group by uid;

实话说,看到这个SQL我也忍不住想骂人啊,究竟是哪个脑残的XX狗设计的?

竟然把日期时间中的 date 和 hour 给独立出来成两列,查询时再合并成一个新的条件,简直无力吐槽。

吐槽归吐槽,该干活还得干活,谁让咱是DBA呢,SQL优化是咱的拿手好戏不是嘛~

SQL优化之路

SQL优化思路

不厌其烦地再说一遍SQL优化思路。

想要优化一个SQL,一般来说就是先看执行计划,观察是否尽可能用到索引,

同时要关注预计扫描的行数,

以及是否产生了临时表(Using temporary) 或者 

是否需要进行排序(Using filesort),

想办法消除这些情况。

SQL性能瓶颈定位

毫无疑问,想要优化,先看表DDL以及执行计划:

CREATE TABLE `t1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL DEFAULT '0000-00-00',
  `hour` char(2) NOT NULL DEFAULT '00',
  `kid` int(4) NOT NULL DEFAULT '0',
  `uid` int(11) NOT NULL DEFAULT '0',
  `aType` tinyint(2) NOT NULL DEFAULT '0',
  `src` tinyint(2) NOT NULL DEFAULT '1',
  `aid` int(11) NOT NULL DEFAULT '1',
  `acount` int(11) NOT NULL DEFAULT '1',
  `power` decimal(20,2) DEFAULT '0.00',
  PRIMARY KEY (`id`,`date`),
  UNIQUE KEY `did` (`date`,`hour`,`kid`,`uid`,`aType`,`src`,`aid`)
) ENGINE=InnoDB AUTO_INCREMENT=50486620 DEFAULT CHARSET=utf8mb4
/*!50500 PARTITION BY RANGE  COLUMNS(`date`)
(PARTITION p20170316 VALUES LESS THAN ('2017-03-17') ENGINE = InnoDB,
 PARTITION p20170317 VALUES LESS THAN ('2017-03-18') ENGINE = InnoDB
...

yejr@imysql.com[myDB]> EXPLAIN select uid,sum(power) powerup from t1 where 
date>='2017-03-31' and 
UNIX_TIMESTAMP(STR_TO_DATE(concat(date,' ',hour),'%Y-%m-%d %H'))>=1490965200 and 
UNIX_TIMESTAMP(STR_TO_DATE(concat(date,' ',hour),'%Y-%m-%d %H'))<1492174801  and 
aType in (1,6,9) group by uid\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p20170324,p20170325,....all partition
         type: ALL
possible_keys: did
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25005577
     filtered: 15.00
        Extra: Using where; Using temporary; Using filesort

明显的,这个SQL效率非常低,全表扫描没有索引有临时表需要额外排序,什么倒霉催的全赶上了。

优化思考

这个SQL是想统计符合条件的power列总和,虽然 date 列已有索引,但WHERE子句中却对 date 列加了函数,而且还是 date 和 hour 两列的组合条件,那就无法用到这个索引了。

还好,有个聪明伶俐的妹子,突发起想(事实上这位妹子本来就擅长做SQL优化的~),可以用 CASE WHEN 方法来改造下SQL,改成像下面这样的:

select uid,sum(powerup+powerup1) from
(
   select uid,
          case when concat(date,' ',hour) >='2017-03-24 13:00' then power else '0' end as powerup,
          case when concat(date,' ',hour) < '2017-03-25 13:00' then power else '0' end as powerup1
   from t1
   where date>='2017-03-24' 
   and   date <'2017-03-25'
   and  aType in (1,6,9)
) a  group by uid;

是不是很有才,直接把这个没办法用到索引的条件给用CASE WHEN来改造了。看看新的SQL执行计划:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p20170324
         type: range
possible_keys: did
          key: idx2_date_addRedType
      key_len: 4
          ref: NULL
         rows: 876375
     filtered: 30.00
        Extra: Using index condition; Using temporary; Using filesort

看看这个SQL的执行代价:

+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Handler_read_first         | 1       |
| Handler_read_key           | 1834590 |
| Handler_read_last          | 0       |
| Handler_read_next          | 1834589 |
| Handler_read_prev          | 0       |
| Handler_read_rnd           | 232276  |
| Handler_read_rnd_next      | 232277  |
+----------------------------+---------+

及其SLOW QUERY LOG记录的信息:

# Query_time: 6.381254  Lock_time: 0.000166  Rows_sent: 232276  Rows_examined: 2299141  Rows_affected: 0
# Bytes_sent: 4237347  Tmp_tables: 1  Tmp_disk_tables: 0  Tmp_table_sizes: 4187168
# InnoDB_trx_id: 0
# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0
#   InnoDB_IO_r_ops: 0  InnoDB_IO_r_bytes: 0  InnoDB_IO_r_wait: 0.000000
#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000
#   InnoDB_pages_distinct: 9311

看起来还不是太理想啊,虽然不再扫描全表了,但毕竟还是 有临时表 和 额外排序,想办法消除后再对比看下。

有个变化不知道大家注意到没,新的SLOW QUERY LOG记录多了不少信息,这是因为用了Percona分支版本的插件才支持,这个功能确实不错,甚至还能记录Profiling的详细信息,强烈推荐。

我们新建个 uid 列上的索引,看看能除临时表及排序后的代价如何,看看这个的开销会不会更低。

yejr@imysql.com[myDB]> ALTER TABLE t1 ADD INDEX idx_uid(uid);
yejr@imysql.com[myDB]> EXPLAIN select uid,sum(powerup+powerup1) from
(
   select uid,
          case when concat(date,' ',hour) >='2017-03-24 13:00' then power else '0' end as powerup,
          case when concat(date,' ',hour) < '2017-03-25 13:00' then power else '0' end as powerup1
   from t1
   where date>='2017-03-24' 
   and   date <'2017-03-25'
   and  aType in (1,6,9)
) a  group by uid\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: if_date_hour_army_count
   partitions: p20170331,p20170401...
         type: index
possible_keys: did,idx_uid
          key: idx_uid
      key_len: 4
          ref: NULL
         rows: 12701520
     filtered: 15.00
        Extra: Using where

看看添加索引后SQL的执行代价:

+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Handler_read_first         | 1       |
| Handler_read_key           | 1       |
| Handler_read_last          | 0       |
| Handler_read_next          | 1834589 |
| Handler_read_prev          | 0       |
| Handler_read_rnd           | 0       |
| Handler_read_rnd_next      | 0       |
+----------------------------+---------+

及其SLOW QUERY LOG记录的信息:

# Query_time: 5.772286  Lock_time: 0.000330  Rows_sent: 232276  Rows_examined: 1834589  Rows_affected: 0
# Bytes_sent: 4215071  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# InnoDB_trx_id: 0
# QC_Hit: No  Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0
#   InnoDB_IO_r_ops: 0  InnoDB_IO_r_bytes: 0  InnoDB_IO_r_wait: 0.000000
#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000
#   InnoDB_pages_distinct: 11470

我们注意到,虽然加了 uid 列索引后的SQL扫描的data page更多了,但执行效率其实是更高的因为消除了 临时表 和 额外排序,这从 Handlerread% 的结果中也能看出来,很显然它的顺序I/O更多,随机I/O更少所以虽然需要扫描的 data page 更多,实际上效率却是更快的

后记

再想想这个SQL还有优化空间吗,显然是有的,那就是把数据表重新设计,将 date 和 hour 列整合到一起,这样就不用费劲的拼凑条件并且也能用到索引了。


最后安利下,知数堂培训马上推出 SQL开发优化 课程,由业界资深SQL优化专家郑老师授课。

该课程关键字:MySQL、Oracle、SQL调优、EXPLAIN、DBMS_XPLAN、OPTIMIZER TRACE、SQL改写、NESTED LOOP、OUTER JOIN、HASH JOIN、ERD图、HINT、SORT MERGE、Materialized View、ROWNUM。

学完本课程,无论您是DBA工程师、运维工程师,还是开发工程师,抑或系统架构师、技术主管,都将大幅增强您的职场实力,加薪50%轻轻松松。此外,我们也会将优秀的学员直接推向各大一线互联网公司。

本周四晚上郑老师还会再进行一次公开课分享,讲讲GROUP BY的用法及堵门优化技巧。

有兴趣的同学可以扫码加入知数堂QQ群 579036588 关注课程进展。

优化案例 | 分区表场景下的SQL优化

导读

有个表做了分区,每天一个分区。

该表上有个查询,经常只查询表中某一天数据,但每次都几乎要扫描整个分区的所有数据,有什么办法进行优化吗?

待优化场景

有一个大表,每天产生的数据量约100万,所以就采用表分区方案,每天一个分区。

下面是该表的DDL:

CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `kid` int(11) DEFAULT '0',
  `uid` int(11) NOT NULL,
  `iid` int(11) DEFAULT '0',
  `icnt` int(8) DEFAULT '0',
  `tst` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `countp` smallint(11) DEFAULT '1',
  `isr` int(2) NOT NULL DEFAULT '0',
  `clv` int(5) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`,`date`),
  UNIQUE KEY `date` (`date`,`uid`,`iid`),
  KEY `date_2` (`date`,`kid`)
) ENGINE=InnoDB AUTO_INCREMENT=3180686682 DEFAULT CHARSET=utf8mb4
/*!50500 PARTITION BY RANGE  COLUMNS(`date`)
(PARTITION p20161201 VALUES LESS THAN ('2016-12-02') ENGINE = InnoDB,
 PARTITION p20161202 VALUES LESS THAN ('2016-12-03') ENGINE = InnoDB,
 PARTITION p20161203 VALUES LESS THAN ('2016-12-04') ENGINE = InnoDB,
...

该表上经常发生下面的慢查询:

SELECT ... FROM `t1` WHERE `date` = '2017-04-01' AND `icnt` > 300 AND `id` = '801301';

SQL优化之路

SQL优化思路

想要优化一个SQL,一般来说就是先看执行计划,观察是否尽可能用到索引,同时要关注预计扫描的行数,以及是否产生了临时表(Using temporary) 或者 是否需要进行排序(Using filesort),想办法消除这些情况。

更进一步的优化策略则可能需要调整程序代码逻辑,甚至技术架构或者业务需求,这个动作比较大,一般非核心系统上的核心问题,不会这么大动干戈,绝大多数情况,还是需要靠DBA尽可能发挥聪明才智来解决。

SQL性能瓶颈定位

现在,我们来看下这个SQL的执行计划:

yejr@imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM `t1` WHERE 
  `date` = '2017-03-02' AND `icnt` > 100 AND `iid` = '502302'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p20170302
         type: range
possible_keys: date,date_2
          key: date
      key_len: 3
          ref: const
         rows: 9384602
        Extra: Using where

这个执行计划看起来还好,有索引可用,也没临时表,也没filesort。不过,我们也注意到,预计要扫描的行数还是挺多的 rows: 9384602,而且要扫描zheng整个分区的所有数据,难怪效率不高,总是SLOW QUERY。

优化思考

我们注意到这个SQL总是要查询某一天的数据,这个表已经做了按天分区,那是不是可以忽略 WHERE 子句中的 时间条件呢?

还有,既然去掉了 date 条件,反观表DDL,剩下的条件貌似就没有合适的索引了吧?

所以,我们尝试新建一个索引:

yejr@imysql.com[myDB]> ALTER TABLE t1 ADD INDEX iid (iid, icnt);

然后,把SQL改造成下面这样,再看下执行计划:

yejr@imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM `t1` partition(p2017030) WHERE 
  `icnt` > 100 AND `iid` = '502302'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p20170302
         type: ref
possible_keys: date,date_2,iid
          key: iid
      key_len: 10
          ref: const
         rows: 7800
        Extra: Using where

这优化效果,杠杠滴。

事实上,如果不强制指定分区的话,也是可以达到优化效果的:

yejr@imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM `t1` WHERE 
  `date` = '2017-03-02' AND `icnt` > 100 AND `iid` = '502302'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p20170302
         type: ref
possible_keys: date,date_2,iid
          key: iid
      key_len: 10
          ref: NULL
         rows: 7800
        Extra: Using where

后记

绝大多数的SQL通过添加索引、适当调整SQL代码(例如调整驱动表顺序)等简单手法来完成。

多说几句,遇到SQL优化性能瓶颈问题想要在技术群里请教时,麻烦先提供几个必要的信息:

  • 表DDL
  • 表常规统计信息,可执行 SHOW TABLE STATUS LIKE ‘t1’ 查看
  • 表索引分布信息,可执行 SHOW INDEX FROM t1 查看
  • 有问题的SQL及相应的执行计划 没有这些信息的话,就别去麻烦别人了吧。

最后安利下,知数堂培训马上推出 SQL开发优化 课程,由业界资深SQL优化专家郑老师授课。

该课程关键字:MySQL、Oracle、SQL调优、EXPLAIN、DBMS_XPLAN、OPTIMIZER TRACE、SQL改写、NESTED LOOP、OUTER JOIN、HASH JOIN、ERD图、HINT、SORT MERGE、Materialized View、ROWNUM。

学完本课程,无论您是DBA工程师、运维工程师,还是开发工程师,抑或系统架构师、技术主管,都将大幅增强您的职场实力,加薪50%轻轻松松。此外,我们也会将优秀的学员直接推向各大一线互联网公司。

有兴趣的同学可以扫码加入知数堂QQ群 579036588 关注课程进展。

基于MySQL 5.7多源复制及Keepalived搭建三节点高可用架构

导读

本内容摘自知数堂第35期公开课《MySQL 5.7 高可用新玩法》

本次公开课视频请访问  http://pan.baidu.com/s/1mia6MZu

知数堂公开课相关视频请访问  https://ke.qq.com/course/172600

课中涉及到的脚本及配置文件请点击文末 “阅读原文

基本环境准备

使用Centos 6.X 64位系统 MySQL 使用 MySQL-5.7.17-x86_64 版本,去官方下载mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz 版本

机器名 操作系统 Ip
node1 centos-6.8 192.168.11.100
node2 centos-6.8 192.168.11.101
node3 centos-6.8 192.168.11.102

三节点集群设置VIP为 192.168.11.110

一般我们建议关闭iptables

[wubx@zhishuedu.com ~]# chkconfig —del iptables
[wubx@zhishuedu.com ~]# /etc/init.d/iptables stop

并且关闭 selinux

[wubx@zhishuedu.com ~]# setenforce 0

并且将配置文件 /etc/sysconfig/selinux 中的下面这行
SELINUX=permissive

更改为

SELINUX=disabled

下载MySQL

[wubx@zhishuedu.com ~]# mkdir /data/Soft
[wubx@zhishuedu.com ~]# cd /data/Soft
[wubx@zhishuedu.com ~]# wget -c https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86\_64.tar.gz

MySQL部署约定

二进制文件放置到 /opt/mysql/ 下面对应的目录。
数据文件全部放置到 /data/mysql/ 下面对应的目录。
原始二进制文件下载到 /data/Soft/ 目录下。

MySQL基本安装

以下安装步骤需要在node1, node2, node3上分别执行。

[wubx@zhishuedu.com ~]# mkdir /opt/mysql
[wubx@zhishuedu.com ~]# cd /opt/mysql
[wubx@zhishuedu.com ~]# tar zxvf /data/Soft/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
[wubx@zhishuedu.com ~]# ln -s /opt/mysql/mysql-5.7.17-linux-glibc2.5-x86_64 /usr/local/mysql
[wubx@zhishuedu.com ~]# mkdir /data/mysql/mysql3309/{data,logs,tmp} -p
[wubx@zhishuedu.com ~]# groupadd mysql
[wubx@zhishuedu.com ~]# useradd -g mysql -s /sbin/nologin -d /usr/local/mysql -M mysql
[wubx@zhishuedu.com ~]# chown -R mysql:mysql /data/mysql/
[wubx@zhishuedu.com ~]# chown -R mysql:mysql /usr/local/mysql
[wubx@zhishuedu.com ~]# cd /usr/local/mysql/
[wubx@zhishuedu.com ~]# ./bin/mysqld —defaults-file=/data/mysql/mysql3309/my3309.cnf —initialize
[wubx@zhishuedu.com ~]# cat /data/mysql/mysql3309/data/error.log |grep password
[wubx@zhishuedu.com ~]# /usr/local/mysql/bin/mysqld —defaults-file=/data/mysql/mysql3309/my3309.cnf &
[wubx@zhishuedu.com ~]# echo “export PATH=$PATH:/usr/local/mysql/bin” >>/etc/profile
[wubx@zhishuedu.com ~]# source /etc/profile

[wubx@zhishuedu.com ~]# mysql -S /tmp/mysql3309.sock -uroot -pXX

mysql> grant replication slave,replication client on . to ‘repl’@’%’ identified by ‘repl4slave’;
mysql> grant all privilegs on test.* to ‘wubx’@’%’ identified by ‘wubx’;
mysql> reset master;

每个节点按上面进行,遇到初始化和启动故障请认真阅读 error log 日志文件。

搭建主从结构

node1上设置master

mysql> change master to master_host=’192.168.11.101’,
master_port=3309, master_user=’repl’, 
master_password=’repl4slave’, master_auto_position=1 
for channel ‘192_168_11_101_3309’;

mysql> change master to master_host=’192.168.11.102’,
master_port=3309, master_user=’repl’, 
master_password=’repl4slave’, master_auto_position=1 
for channel ‘192_168_11_102_3309’;

#确认同步OK
mysql> start slave; 
mysql> show slave status\G

node2上设置master

mysql> change master to master_host=’192.168.11.100’,
master_port=3309, master_user=’repl’, 
master_password=’repl4slave’, master_auto_position=1 
for channel ‘192_168_11_100_3309’;

mysql> change master to master_host=’192.168.11.102’,
master_port=3309,master_user=’repl’, 
master_password=’repl4slave’,master_auto_position=1 
for channel ‘192_168_11_102_3309’;

#确认同步OK
mysql> start slave; 
mysql> show slave status\G

node3上设置master

mysql> change master to master_host=’192.168.11.100’,
master_port=3309, master_user=’repl’, 
master_password=’repl4slave’, master_auto_position=1 
for channel ‘192_168_11_100_3309’;

mysql> change master to master_host=’192.168.11.101’,
master_port=3309, master_user=’repl’, 
master_password=’repl4slave’,master_auto_position=1 
for channel ‘192_168_11_101_3309’;

#确认同步OK
mysql> start slave;
mysql> show slave status\G

安装keepalived

node1, node2, node3 上分别安装keepalived。

yum install keepalivled

安装python依赖模块。

yum install MySQL-python.x86_64 yum install python2-filelock.noarch

keepalived配置 配置文件放置在 /etc/keepalived/keepalived.conf,内容如下

vrrp_script vs_mysql_82 {

    script "/etc/keepalived/checkMySQL.py -h 127.0.0.1 -P 3309"

    interval 15

}

vrrp_instance VI_82 {

    state backup

    nopreempt

    interface eth1

    virtual_router_id 82

    priority 100

    advert_int 5

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    track_script {

        vs_mysql_82

    }

    notify /etc/keepalived/notify.py

    virtual_ipaddress {

        192.168.11.110

    }

}

在node1, node2, node3分别执行下面命令,启动keepalived。

/etc/init.d/keepalived start

观察每个系统上的 /var/log/messages 中是否有报错等内容。

在client端机器上测试验证当前连接到哪个实例上。

mysql -h 192.168.11.110 -P 3309 -uwubx -pwubx -e “select @@hostname”

可以尝试关闭实例,自行触发keepalived高可用切换,完成一次高可用自动切换。

扫码关注 知数堂 公众号,第一时间关注干货文章

点击“阅读原文”,获取三节点高可用切换脚本。

2017.4.6发车 |《MySQL 5.7高可用新玩法》

1、主题

《MySQL 5.7高可用新玩法》

2、嘉宾介绍

吴炳锡 知数堂培训联合创始人,前新媒传信首席DBA,吴炳锡老师具有多年MySQL及系统架构设计及培训教学经验,擅长MySQL大规模运维管理优化、高可用方案、多IDC架构设计,以及企业应用数据库设计等经验。

3、课程简介

MySQL 5.7版本中新增了真正意义上的多线程复制、多源复制、增强半同步复制,以及Group Replication,众多新特性让人眼前一亮。随着这些新特性的日益成熟,在MySQL 5.7上的高可用架构也可以有了新玩法。

在本次分享中,先介绍Group Replicaiton的实现原理,重点介绍MySQL 5.7下利用多源复制实现的高可用架构方案。

内容大纲:

1、MySQL 5.7在复制方面的几个新特性;

2、MySQL 5.7 Group Replication实现原理;

3、利用多源复制,实现新的高可用架构;

4、基于多源复制及Group Replication限制。

分享方

分享时间:2017.4.6(周四) 晚上20:30 – 21:30

分享方式:通过YY语音同步直播,以及在QQ&微信群发送PPT等图文内容

YY频道:53695719(需提前安装YY客户端,支持windows/ios/andriod多平台)

请提前加入知数堂技术交流QQ群:579036588,或扫描识别下面二维码加入。(已经在知数堂一群、二群的无需再加入三群,分享时三群联动)

关于知数堂

“知数堂培训”是由资深MySQL专家叶金荣、吴炳锡联合推出专业优质在线培训课程,主要有MySQL DBA实战优化、Python运维开发和 SQL开发优化三个课程,是业内最有良心、最有品质的培训品牌。

目前MySQL DBA实战优化班以及Python运维开发班均在招生中。学员已超600多人,众多优秀学员在腾讯、淘宝、支付宝、百度、网易、京东、乐视、去哪儿、滴滴、猎豹、58、微博、金山云、聚美、顺丰、德邦、韵达、苏宁、恩墨、沃趣、爱可生、37玩、宝存、人人贷、美的、新东方、平安金融等众多知名公司担任DBA等职位,在获得更好的职业发展机遇同时薪资也得到了大幅提升。

有兴趣的同学请关注 知数堂 官方公众号”izhishuedu” 后发送 “开班”、“招生”、“大纲” 等关键字即可获得课程详细信息。