新增特性
与安全相关的新增特性
【1】移除 mysql_old_password plugin
先看下mysql-5.6
mysql> select @@version;
+------------+
| @@version |
+------------+
| 5.6.23-log |
+------------+
#show plugins 命令查看当前server所支持的password函数加密方式,其中包括了mysql_native_password,mysql_old_password,sha256_password三种方式。
mysql> show plugins;
+----------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
------------------------------------------------------------------------
#5.6版本依然能支持old_password加密方式
mysql> select old_password('aaa');
+---------------------+
| old_password('aaa') |
+---------------------+
| 7cd402192beb696b |
+---------------------+
1 row in set, 1 warning (0.00 sec)
#在mysql中,当使用password函数时,用old_password 参数控制使用的是何种加密算法,如下
mysql> SET old_passwords = 0;
mysql> SELECT PASSWORD('mypass'), OLD_PASSWORD('mypass');
+-------------------------------------------+------------------------+
| PASSWORD('mypass') | OLD_PASSWORD('mypass') |
+-------------------------------------------+------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | 6f8c114b58f2ce9e |
+-------------------------------------------+------------------------+
mysql> SET old_passwords = 1;
mysql> SELECT PASSWORD('mypass'), OLD_PASSWORD('mypass');
+--------------------+------------------------+
| PASSWORD('mypass') | OLD_PASSWORD('mypass') |
+--------------------+------------------------+
| 6f8c114b58f2ce9e | 6f8c114b58f2ce9e |
+--------------------+------------------------+
mysql> SET old_passwords = 2;
mysql> SELECT PASSWORD('mypass'),old_password('mypass');
+----------------------------------------------------------------------+------------------------+
| PASSWORD('mypass') | old_password('mypass') |
+----------------------------------------------------------------------+------------------------+
| $5$#.%CDc0~3])ydY=Ss4R$Wd.p0jVk2gTQNTqpRAbgrUMCzTnYgt3SDW.WLI25JF7 | 6f8c114b58f2ce9e |
+----------------------------------------------------------------------+------------------------+
#看个用old_password授权账号的例子,以及如何用高版本的client登陆
如下是5.7版本
mysql> select @@version;
+------------+
| @@version |
+------------+
| 5.7.17-log |
+------------+
1 row in set (0.00 sec)
已经移除了old_password的加密方式
mysql> show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+--------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
并且old_passwords参数也不能在设置为1.
mysql> SET old_passwords = 1;
ERROR 1231 (42000): Variable 'old_passwords' can't be set to the value of '1'
解释:
1,mysql在4.1版本之前,在创建用户,设置密码时,使用现在称之为old_password()函数的加密方式。在之后,引入了更多的加密方式,mysql_native_password/sha256_password。
2,mysql4.1之前的客户端,即平时使用的mysql命令,无法连接4.1之后版本的数据库中使用mysql_native_password 加密过的账户,因为4.1之前的client无法正确处理用户输入的密码,将其转换为41位的密文。
3,mysql为了使4.1 之前的client向后续server兼容,引入old_passwords参数,此参数为1时创建的用户,可使用client<4.1 登陆,为0时创建的用户,不能登陆
4,mysql5.7中彻底的抛弃了old_password 的加密方式,同时参数old_passwords参数枚举0/2,0代表在创建用户时使用mysql_native_password,2表示使用sha256_password。
5,使用mysql4.1之后的client,在登陆mysql时,需要server运行在secure_auth=off,并且mysqlclient链接时添加–skip-secure-auth参数,才能识别old_passwords加密方式。
【2】账户过期时间设置
mysql5.7中的mysql.user 表新增password_lifetime 列,控制用户还有多久过期。过期之后必须重新设置密码。
mysql> grant select on *.* to 'test_pass'@'%' identified by 'test';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> alter user 'test_pass'@'%' PASSWORD EXPIRE INTERVAL 180 DAY;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.user where user='test_pass'\G
*************************** 1. row ***************************
Host: %
User: test_pass
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29
password_expired: N
password_last_changed: 2017-01-13 14:25:52
password_lifetime: 180 #180天后过期
account_locked: N
1 row in set (0.00 sec)
相关参数
mysql> show global variables like '%life%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| default_password_lifetime | 0 |
+---------------------------+-------+
#这个参数的意思是指的默认的新建账号的过期时间,0为永不过期,此参数在5.7.10及之前都是默认360天,在之后的版本中默认为0。所以之前在离线库中遇到过,登陆提示账号密码过期的问题。
1 row in set (0.00 sec)
【3】给账户加锁
给用户加锁之后,用户将无法访问数据库,并且在登陆的时候返回相应的错误信息,数据库会增加global 状态值Locked_connects,同时将相应的信息记录在错误日志
#加锁
mysql> alter user 'test_pass'@'%' account lock;
Query OK, 0 rows affected (0.01 sec)
#登陆报错
[root@192-168-110-145 ~]# mysql -h192.168.110.145 -utest_pass -p -P13307
Enter password:
ERROR 3118 (HY000): Access denied for user 'test_pass'@'192.168.110.145'. Account is locked.
[root@192-168-110-145 ~]# fg
mysql -S /tmp/mysql_13307.sock
#记录Locked_connects状态值
mysql> show global status like '%Locked_connects%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Locked_connects | 1 |
+-----------------+-------+
1 row in set (0.00 sec)
mysql>
[1]+ Stopped mysql -S /tmp/mysql_13307.sock
[root@192-168-110-145 ~]# mysql -h192.168.110.145 -utest_pass -ptest -P13307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3118 (HY000): Access denied for user 'test_pass'@'192.168.110.145'. Account is locked.
[root@192-168-110-145 ~]# mysql -h192.168.110.145 -utest_pass -ptest -P13307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3118 (HY000): Access denied for user 'test_pass'@'192.168.110.145'. Account is locked.
[root@192-168-110-145 ~]# fg
mysql -S /tmp/mysql_13307.sock
#增加Locked_connects状态值
mysql> show global status like '%Locked_connects%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Locked_connects | 3 |
+-----------------+-------+
1 row in set (0.00 sec)
#记录错误日志
2017-01-13T14:31:21.293199+08:00 7 [Note] Access denied for user 'test_pass'@'192.168.110.145'. Account is locked.
2017-01-13T14:32:17.083993+08:00 8 [Note] Access denied for user 'test_pass'@'192.168.110.145'. Account is locked.
2017-01-13T14:32:18.791480+08:00 9 [Note] Access denied for user 'test_pass'@'192.168.110.145'. Account is locked.
【4】初始化数据库
初始化数据库时,默认使用mysqld –initialize,其具备如下特性
- 仅生成一个 ‘root’@’localhost’,密码随机12位。控制mysqld初始化的代码在sql_initialize.cc中,用户可自定义。
sql_initialize.cc
#define INSERT_USER_CMD "CREATE USER root@localhost IDENTIFIED BY '%s' PASSWORD EXPIRE;\n"
#define INSERT_USER_CMD_INSECURE "CREATE USER root@localhost;\n"
#define GENERATED_PASSWORD_LENGTH 12
static void generate_password(char *password, int size)
.........
- 不再创建匿名用户
- 不在创建 test库
与sql_mode相关的改进
mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
【1】STRICT_TRANS_TABLES默认开启
此模式的意思是,开启对事务类型表的严格模式,对于非法输入的控制更加严格
mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into test values(10,'kkskskskssksksk');
ERROR 1406 (22001): Data too long for column 'name' at row 1
#利用sys.list_drop函数关闭STRICT_TRANS_TABLES。
mysql> SET @@sql_mode = sys.list_drop(@@sql_mode, 'STRICT_TRANS_TABLES');
Query OK, 0 rows affected, 1 warning (0.01 sec)
#看warnings,提示,'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO'这三个模式应该归并为严格模式中,在以后的版本中将会实施。
mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3135 | 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into test values(10,'kkskskskssksksk');
Query OK, 1 row affected, 1 warning (0.02 sec)
#在非严格模式下,显示插入数据被截断
mysql> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from test;
+----+------------+
| id | name |
+----+------------+
| 5 | ashe |
| 6 | ashe |
| 7 | ashe |
| 8 | ashe |
| 9 | ashe |
| 10 | kkskskskss |
+----+------------+
6 rows in set (0.00 sec)
#非严格模式下,ddl后字段值可能会改变
关于严格模式
online ddl改进
innodb相关改进
【1】varchar类型字段modify操作改进
这一功能的增强,可以满足绝大多数modify场景。
对于varchar类型的字段,从varchar(1)增加到varchar(255),,从varchar(256)增加到varchar(21842),都采用了in-place的方式操作,可以在线添加,不会影响dml操作。
increase varchar(N)减少varchar(n)n值的操作,依然使用copy-table方式。
- 而从varchar(1-255)增加到varchar(266-21842)是不可以在线添加的,依然采取copy table的方式,影响dml操作。
- char改变成varchar,或者是反过来,依然还是copy table的方式。
【2】其他ddl的性能提升
【3】INNODB_TEMP_TABLE_INFO
【4】新增空间索引数据类型DATA_GEOMETRY
http://www.cnblogs.com/linkerCRed/p/5443200.html
【5】新增参数innodb_temp_data_file_path
innodb_temp_data_file_path用来控制非压缩的innodb临时表时的存放位置。
mysql> show global variables like 'innodb_temp_data_file_path';
+----------------------------+-----------------------+
| Variable_name | Value |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
如上所示,证明此实例的innodb临时表位于data目录下,初始化大小12M,自增。删除临时表并不会释放此空间。并且对于innodb临时表的undo 日志也是存放在此处的。
MySQL包括两类临时表,一类是通过create temporary table创建的临时表,一类是在query过程中using temporary而创建的临时表。
5.7之前,using temporary创建的临时表,默认只能使用myisam引擎,而在5.7之后,可以选择InnoDB引擎来创建。
mysql> show global variables like '%tmp%engine%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| default_tmp_storage_engine | InnoDB |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
2 rows in set (0.00 sec)
mysql> explain select * from test as test_1 left join test_checksum as test_2 on test_1.name=test_2.name left join test_replace as test_3 on test_2.name=test_3.name order by test_1.id;
+----+-------------+--------+------------+------+---------------+------+---------+------------------+----------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------------------+----------+----------+----------------------------------------------------+
| 1 | SIMPLE | test_1 | NULL | ALL | NULL | NULL | NULL | NULL | 18 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | test_2 | NULL | ref | name | name | 33 | ashe.test_1.name | 16326503 | 100.00 | Using where; Using index |
| 1 | SIMPLE | test_3 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+--------+------------+------+---------------+------+---------+------------------+----------+----------+----------------------------------------------------+
未能详细研究,更多解释说明http://mysql.taobao.org/monthly/2016/06/02/
【6】innochecksum功能增强
innochecksum是mysql自带的一个离线文件checksum工具,针对innodb类型的表
【7】新增innodb_buffer_pool_dump_pct
innodb_buffer_pool_dump_pct参数用于控制进行热数据导出时的百分比,
【8】新增全文索引的解析插件
http://mysqlserverteam.com/innodb%E5%85%A8%E6%96%87%E7%B4%A2%E5%BC%95%EF%BC%9An-gram-parser/
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body) WITH PARSER ngram
) ENGINE=InnoDB CHARACTER SET utf8mb4;
【9】新增innodb_page_cleaners
mysql将刷脏页的操作从master线程剥离出来,交给page_cleaner线程去操作。并且page_cleaner线程的数量由全局参数innodb_page_cleaners来控制。
【10】动态修改innodb_buffer_pool_size
5.7版本中,innodb缓冲池可以动态的调节大小,并且会记录全局状态值Innodb_buffer_pool_resize_status。同样在mysql错误日志中也会记录
#相关参数
mysql> set global innodb_buffer_pool_size=1*1024*1024*1024;
Query OK, 0 rows affected (0.01 sec)
mysql> show global status like 'Innodb_buffer_pool_resize_status';
+----------------------------------+----------------------------------------------------+
| Variable_name | Value |
+----------------------------------+----------------------------------------------------+
| Innodb_buffer_pool_resize_status | Completed resizing buffer pool at 170119 20:58:00. |
+----------------------------------+----------------------------------------------------+
#还有另外一个参数,如下
innodb_buffer_pool_chunk_size #此参数定义了
在线动态修改innodb_buffer_pool_size的内部原理
此操作是由mysql后台线程去做的,当增大缓冲池时,其过程如下:
1,以定义的innodb_buffer_pool_chunk_size,来逐步增大缓冲池,
2,将哈希表,链表,指针适用于新的内存
3,将新添加的页放到空闲链表的最后。
此过程中,其他线程是无法访问缓冲池的。
操作注意事项
一定要在业务低峰期间做。
测试如下,修改innodb_buffer_pool_size 对业务的影响:
#连续修改innodb_buffer_pool_size的值,同时sysbench压此实例
mysql> set global innodb_buffer_pool_size=90*1024*1024*1024;
Query OK, 0 rows affected (0.00 sec)
mysql> set global innodb_buffer_pool_size=120*1024*1024*1024;
Query OK, 0 rows affected (0.00 sec)
mysql> set global innodb_buffer_pool_size=1*1024*1024*1024;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> set global innodb_buffer_pool_size=90*1024*1024*1024;
ERROR 3166 (HY000): Another buffer pool resize is already in progress.
#sysbench表现出 mysql的性能波动
[ 49s] threads: 32, tps: 539.99, reads/s: 7527.92, writes/s: 2162.98, response time: 92.52ms (95%)
[ 50s] threads: 32, tps: 536.01, reads/s: 7564.08, writes/s: 2148.02, response time: 87.04ms (95%)
[ 51s] threads: 32, tps: 533.00, reads/s: 7420.00, writes/s: 2136.00, response time: 92.38ms (95%)
[ 52s] threads: 32, tps: 517.01, reads/s: 7323.08, writes/s: 2071.02, response time: 102.83ms (95%)
[ 53s] threads: 32, tps: 540.01, reads/s: 7522.12, writes/s: 2148.03, response time: 89.98ms (95%)
[ 54s] threads: 32, tps: 499.98, reads/s: 6929.78, writes/s: 2001.94, response time: 87.56ms (95%)
[ 55s] threads: 32, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[ 56s] threads: 32, tps: 1.00, reads/s: 170.00, writes/s: 23.00, response time: 2062.83ms (95%)
[ 57s] threads: 32, tps: 519.01, reads/s: 7169.15, writes/s: 2059.04, response time: 2145.97ms (95%)
[ 58s] threads: 32, tps: 529.99, reads/s: 7453.86, writes/s: 2122.96, response time: 96.71ms (95%)
[ 59s] threads: 32, tps: 533.00, reads/s: 7404.99, writes/s: 2133.00, response time: 98.52ms (95%)
[ 60s] threads: 32, tps: 524.01, reads/s: 7329.10, writes/s: 2089.03, response time: 105.76ms (95%)
[ 61s] threads: 32, tps: 545.99, reads/s: 7641.86, writes/s: 2186.96, response time: 87.79ms (95%)
[ 62s] threads: 32, tps: 547.01, reads/s: 7619.13, writes/s: 2201.04, response time: 96.74ms (95%)
[ 63s] threads: 32, tps: 557.00, reads/s: 7857.01, writes/s: 2229.00, response time: 84.57ms (95%)
[ 64s] threads: 32, tps: 531.00, reads/s: 7471.94, writes/s: 2116.98, response time: 93.21ms (95%)
[ 65s] threads: 32, tps: 539.01, reads/s: 7505.14, writes/s: 2159.04, response time: 88.80ms (95%)
[ 66s] threads: 32, tps: 11.00, reads/s: 144.00, writes/s: 35.00, response time: 79.68ms (95%)
[ 67s] threads: 32, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[ 68s] threads: 32, tps: 0.00, reads/s: 0.00, writes/s: 0.00, response time: 0.00ms (95%)
[ 69s] threads: 32, tps: 24.00, reads/s: 228.00, writes/s: 95.00, response time: 4058.79ms (95%)
[ 70s] threads: 32, tps: 510.00, reads/s: 7232.03, writes/s: 2045.01, response time: 134.22ms (95%)
[ 71s] threads: 32, tps: 564.01, reads/s: 7910.19, writes/s: 2259.05, response time: 75.05ms (95%)
[ 72s] threads: 32, tps: 568.00, reads/s: 7945.98, writes/s: 2261.00, response time: 73.49ms (95%)
[ 73s] threads: 32, tps: 563.99, reads/s: 7911.83, writes/s: 2258.95, response time: 79.99ms (95%)
[ 74s] threads: 32, tps: 563.00, reads/s: 7924.93, writes/s: 2255.98, response time: 76.34ms (95%)
[ 75s] threads: 32, tps: 570.02, reads/s: 7945.24, writes/s: 2271.07, response time: 75.25ms (95%)
【11】 Transportable Tablespace
Copying an InnoDB Table From One Server To Another
innodb表跨实例迁移的方法之一。其优点在于拷贝部分表时,不影响实例中其他表的正常操作,某些场景下可能会用到,5.7增强了这一特性,支持了分区表的跨实例拷贝。如下实例为非分区表示例。
Example 1:
将一个innodb表从实例a拷贝到实例b
#step1,在目标库新建和源库中表结构一致的表
mysql> CREATE TABLE `a` (
-> `name` varchar(10) DEFAULT NULL,
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `age` int(11) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=6291457 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)
#step2,在目标库执行如下sql:
mysql> ALTER TABLE a DISCARD TABLESPACE;
#step3,在源库执行如下sql:
mysql> FLUSH TABLES a FOR EXPORT;
在执行完如上sql之后,源库的data目录下会生成a.cfg文件
#step4,拷贝源库数据目录下的.cfg,.ibd文件到目标库,并且授权数据库运行账号
[root@192-168-110-145 ashe]# cp ./a.cfg /data/mysql/data/13307/test/
[root@192-168-110-145 ashe]# cp ./a.ibd /data/mysql/data/13307/test/
[root@192-168-110-145 test]# chown mysql:mysql a.*
#step5,在源库解锁表
mysql> UNLOCK TABLES;
#step6,在目标库执行如下sql:
mysql> ALTER TABLE a IMPORT TABLESPACE;
#step7,checksum验证
mysql> checksum table a;
+--------+-----------+
| Table | Checksum |
+--------+-----------+
| test.a | 138150582 |
+--------+-----------+
1 row in set (5.52 sec)
mysql> checksum table a;
+--------+-----------+
| Table | Checksum |
+--------+-----------+
| ashe.a | 138150582 |
+--------+-----------+
#错误日志中会记录相关操作
2017-01-20T15:00:40.460833+08:00 3 [Note] InnoDB: Sync to disk of `ashe`.`a` started.
2017-01-20T15:00:40.460911+08:00 3 [Note] InnoDB: Stopping purge
2017-01-20T15:00:40.462147+08:00 3 [Note] InnoDB: Writing table metadata to './ashe/a.cfg'
2017-01-20T15:00:40.462434+08:00 3 [Note] InnoDB: Table `ashe`.`a` flushed to disk
2017-01-20T15:08:58.980387+08:00 3 [Note] InnoDB: Deleting the meta-data file './ashe/a.cfg'
2017-01-20T15:08:58.980419+08:00 3 [Note] InnoDB: Resuming purge
如上方法,同样可以用于rename database操作。
native partitioning
从mysql5.7.5开始,支持innodb native partitioning,在之前的版本中,mysql 的分区表一直依赖与ha_partition。native partition的优势在于:
在5.6里面,分区的信息是在MySQLServer层维护的(在.par文件里面),InnoDB引擎层是不知道有分区这个概念的,InnoDB引擎层把每一个分区都当成一张普通的InnoDB表。在打开一个分区表时,会打开很多个分区,打开这些分区表就相当于打开了同等数量的InnoDB表,这需要更多内存存放InnoDB表的元数据和各种与ibd文件打开相关的各种cache与handler的信息。在5.7里面,InnoDB引入了Native Partitioning,它把分区的信息从Server层移到了InnoDB层,打开一个分区表和打开一个InnoDB表的内存开销基本是一样的。
mysql官网有对这项新功能详细的介绍http://mysqlserverteam.com/innodb-native-partitioning-early-access/
【12】引入空间索引,并且支持inplace方式在线添加索引【体量太大,暂时略过】
【13】sorted index build
sorted index build,mysql在5.7.5版本中引进这一特性,其作用是为了加快innodb在创建索引时的速度,当然也包括创建全文索引时,在创建空间索引时不支持。原理如下:
首先使用索引将数据排序,然后进行插入
实现相关
https://dev.mysql.com/worklog/task/?id=7277
PageBulk::insert
相关参数
innodb_fill_factor
在mysql5.7.5中引入,定义了在sorted index build过程中,每个索引页使用的空间百分比,假如此值为80,则保留20%的空间用于将来的数据填充。
【14】Truncating Undo Logs
mysql5.7.5开始,可以进行undo log的truncate操作
#想要进行undo truncate操作,必须在初始化时,将undo日志从indata文件中分离出来,如下:
./bin/mysqld --initialize --basedir=./ --datadir=/data/mysql/data/13308/ --innodb-undo-tablespaces --user=mysql
#初始化完成之后,可以在数据目录中看到undo001和undo002
[root@192-168-110-145 base]# ll ../data/13308/
total 131100
-rw-r----- 1 mysql mysql 56 Jan 20 18:58 auto.cnf
-rw-r----- 1 mysql mysql 416 Jan 20 18:58 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jan 20 18:58 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jan 20 18:58 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jan 20 18:58 ib_logfile1
drwxr-x--- 2 mysql mysql 4096 Jan 20 18:58 mysql
drwxr-x--- 2 mysql mysql 4096 Jan 20 18:58 performance_schema
drwxr-x--- 2 mysql mysql 12288 Jan 20 18:58 sys
-rw-r----- 1 mysql mysql 10485760 Jan 20 18:58 undo001
-rw-r----- 1 mysql mysql 10485760 Jan 20 18:58 undo002
关于undo 新增的参数
mysql> show global variables like '%undo%';
+--------------------------+------------------------+
| Variable_name | Value |
+--------------------------+------------------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory | /data/mysql/data/13308 |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 2 |
+--------------------------+------------------------+
5 rows in set (0.00 sec)
#undo truncate 相关
mysql> show global variables like '%truncate%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| innodb_purge_rseg_truncate_frequency | 128 |
| innodb_undo_log_truncate | OFF |
+--------------------------------------+-------+
2 rows in set (0.00 sec)
truncate 过程
undo的truncate主要由下面两个参数控制:innodb_purge_rseg_truncate_frequency,innodb_undo_log_truncate。
1. innodb_undo_log_truncate是开关参数。
2. innodb_purge_rseg_truncate_frequency默认128,表示purge undo轮询128次后,进行一次undo的truncate。
当设置innodb_undo_log_truncate=ON的时候, undo表空间的文件大小,如果超过了innodb_max_undo_log_size, 就会被truncate到初始大小,但有一个前提,就是表空间中的undo不再被使用。
其主要步骤如下:
1. 超过大小了之后,会被mark truncation,一次会选择一个
2. 选择的undo 空间不能再分配新给新的事务
3. purge线程清理不再需要的rollback segment
4. 等所有的回滚段都释放了后,truncate操作,使其成为install db时的初始状态。
默认情况下, 是purge触发128次之后,进行一次rollback segment的free操作,然后如果全部free就进行一个truncate。
但mark的操作需要几个依赖条件需要满足:
1. 系统至少得有两个undo表空间,防止一个offline后,至少另外一个还能工作
2. 除了ibdata里的segment,还至少有两个segment可用
3. undo表空间的大小确实超过了设置的阈值
复制相关改进
【1】多线程并行复制
MySQL5.6开始支持以schema为维度的并行复制,即如果binlog row event操作的是不同的schema的对象,在确定没有DDL和foreign key依赖的情况下,就可以实现并行复制。
下面主要从【master】和【slave】两个方面说明mysql5.7 多线程复制是如何实现的。
MySQL5.7的并行复制,multi-threaded slave即MTS,期望最大化还原主库的并行度,实现方式是在binlog event中增加必要的信息,以便slave节点根据这些信息实现并行复制。必要的信息就是二进制日志中的last_committed,如下所示
#170209 21:13:36 server id 13308 end_log_pos 40683 GTID last_committed=87 sequence_number=88
SET @@SESSION.GTID_NEXT= '6f154ec4-deff-11e6-8cc0-000c29b12d46:47827'/*!*/;
# at 40683
#170209 21:13:36 server id 13308 end_log_pos 40751 Query thread_id=80 exec_time=0 error_code=0
SET TIMESTAMP=1486646016/*!*/;
BEGIN
/*!*/;
# at 40751
#170209 21:13:36 server id 13308 end_log_pos 40819 Rows_query
# insert into test values(4,'test_last_committed')
# at 40819
#170209 21:13:36 server id 13308 end_log_pos 40865 Table_map: `ashe`.`test` mapped to number 225
# at 40865
#170209 21:13:36 server id 13308 end_log_pos 40922 Write_rows: table id 225 flags: STMT_END_F
BINLOG '
AGucWB38MwAARAAAAHOfAACAADBpbnNlcnQgaW50byB0ZXN0IHZhbHVlcyg0LCd0ZXN0X2xhc3Rf
Y29tbWl0dGVkJyk=
AGucWBP8MwAALgAAAKGfAAAAAOEAAAAAAAEABGFzaGUABHRlc3QAAgMPAlgCAg==
AGucWB78MwAAOQAAANqfAAAAAOEAAAAAAAEAAgAC//wEAAAAEwB0ZXN0X2xhc3RfY29tbWl0dGVk
'/*!*/;
### INSERT INTO `ashe`.`test`
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='test_last_committed' /* VARSTRING(600) meta=600 nullable=1 is_null=0 */
# at 40922
#170209 21:13:36 server id 13308 end_log_pos 40949 Xid = 192827
COMMIT/*!*/;
记录last_committed 是master的职责,而从库在回放时,就可以根据此值来合理的并行回放。
mysql5.7的并行复制建立在group commit的基础上,并且在后续版本中超出了group commit的范畴。所有在同一组中提交的事务之间必定是不冲突的,因此在同步到从机时,由coordinator线程分配到不同的worker线程执行。而后面发现即使不是发生在同一组提交中的事务,也可以分配相同的last_committed值,进行并行回放,所以说超出了group commit的范畴,但是最初设计的时候确实是根据主库group commit来的。
两阶段提交以及group commit的过程如下
1. binlog prepare
2. InnoDB prepare
3. binlog commit(ordered commit)
--3.1 Stage #1: flushing transactions to binary log
--3.2 Stage #2: Syncing binary log file to disk
--3.3 Stage #3: Commit all transactions in order.
4. InnoDB commit
binlog group commit 由Stage_manager管控,维护3个队列,分别为FLUSH_STAGE队列,SYNC_STAGE队列,COMMIT_STAGE队列。每一个队列中的成员为每一个发起commit请求的线程,当然每一个线程中伴随着将要提交的读写事务(只读事务不会走这个逻辑)。
队列由stage_manager中的enroll_for函数生成。
这三个阶段所完成的工作:
- flush stage: flushing transaction to binary log 将线程事务cache信息写入binlog buffer
- sync stage: 将binlog落盘
- commit stage 事务提交阶段。
group commit的整个过程基本上在函数MYSQL_BIN_LOG::ordered_commit中完成。
#源码位于/mysql-5.7.17/sql/binlog.cc
8899 int MYSQL_BIN_LOG::ordered_commit(THD *thd, bool all, bool skip_commit)
ordered_commit函数内部调用如下
在进行正式的flush操作之前要做如下判断,判断是否是MTS回放线程的并行回放,这时涉及到binlog与relaylog顺序一致问题,如果不是直接进入flush阶段
- 8971 if (change_stage(thd, Stage_manager::FLUSH_STAGE, thd, NULL, &LOCK_log))【通过change_stage函数更改队列状态为flush stage】
- flush_error= process_flush_stage_queue(&total_bytes, &do_rotate,&wait_queue);【flush阶段的入口函数】
- THD *first_seen= stage_manager.fetch_queue_for(Stage_manager::FLUSH_STAGE)【获取整个flush队列,返回leader线程,并且将队列清空,以便下次使用】;
- return m_queue[stage].fetch_and_empty【真正获取并且清空队列的过程】
- ha_flush_logs(NULL, true);【redo落盘过程//flush prepared records of transactions to the log of storage engine (for example, InnoDB redo log) in a group right before flushing them to binary log】
- assign_automatic_gtids_to_flush_group(first_seen);【给每一个线程的事务赋值GTID,在这之前,每个事务已经分配了相应的空间存放gtid,在此函数调用之前为空,GTID 分为两个部分,一个是sid,即使uuid,另外一个是gno,根据配置来确定。】
- MYSQL_BIN_LOG::flush_thread_caches(THD *thd)【计算每个事务的大小,确定写binlog文件的位置,写Xid,此处写xid与innodb xid关系暂时未搞清楚。】【通过一个for循环,从leader线程开始,循环其队列后的每一个线程事务,直到为空结束。flush队列的将每一个线程的缓存,flush到binlog
buffer】至此,flush阶段结束,进入下sync阶段。
- binlog_cache_data::flush【】
#疑惑点,binlog xid的写入,在此处。
739 int flush(THD *thd, my_off_t *bytes_written, bool *wrote_xid)
740 {
741 my_off_t stmt_bytes= 0;
742 my_off_t trx_bytes= 0;
743 DBUG_ASSERT(stmt_cache.has_xid() == 0);
744 int error= stmt_cache.flush(thd, &stmt_bytes, wrote_xid);
745 if (error)
746 return error;
747 DEBUG_SYNC(thd, "after_flush_stm_cache_before_flush_trx_cache");
748 if (int error= trx_cache.flush(thd, &trx_bytes, wrote_xid))
749 return error;
750 *bytes_written= stmt_bytes + trx_bytes;
751 return 0;
752 }
进入sync阶段
- if (change_stage(thd, Stage_manager::SYNC_STAGE, wait_queue, &LOCK_log, &LOCK_sync))【通过change_stage改变队列的状态,进入sync stage】
- sync阶段 flush_cache_to_file(&flush_end_pos) 【将缓存刷入binlog file】
commit阶段
- MYSQL_BIN_LOG::process_commit_stage_queue 【commit过程在此处完成;如果在flush/sync阶段中产生错误,整个group中的事务将被标记为未提交状态。全局变量gtid_executed值的增加此处完成;】
- ha_commit_low【存储引擎提交】
commit阶段完成之后,返回order_commit,整组提交成功。
MYSQL_BIN_LOG::process_flush_stage_queue
你会发现在group commit中没有说明是如何给每一个事务添加last_committed值的。这个操作并没有发上在事务commit的阶段,而是在binlog:prepare函数中,处于整个事务执行阶段。
binlog.cc
1584 static int binlog_prepare(handlerton *hton, THD *thd, bool all)
1585 {
1586 DBUG_ENTER("binlog_prepare");
1587 if (!all)
1588 {
1589 Logical_clock& clock= mysql_bin_log.max_committed_transaction;
1590 thd->get_transaction()->
1591 store_commit_parent(clock.get_timestamp());
1592 }
1593
1594 DBUG_RETURN(all && is_loggable_xa_prepare(thd) ?
1595 mysql_bin_log.commit(thd, true) : 0);
1596 }
Logical_clock
以上都是对mysql MTS功能主库上的实现原理描述,以下介绍从机是如何实现的
实施
必要参数开启
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=8
多线程复制中,所有事务标记last_commit值相同的事务,在从机回放时,可以进行并行回放,但是,此时可能出现从机乱序的问题。也就是说从机的事务执行顺序可能跟主库不一致,当然这不影响数据一致性,因为多线程之间是没有数据冲突的。
减少group commit delay值,因为从机的并行回放已经不在完全group commit。如果是为了增加从机的并行度而增加此参数,完全没有必要,当然增加此参数的值,在磁盘性能较差,数据库写入并发高的情况下,可以加大binlog的吞吐量。
binlog_group_commit_sync_delay = 0 #单位是微秒,最大值为1秒。减少group commit的等待。
mysql提供如下参数,来保证主从在多线程复制情况下事务执行顺序是一致的,在最新的mysql文档中有详细的介绍。
slave_preserve_commit_order=1
【多主复制】
多主复制是mysql5.7在复制方面又一项重要的改进,同时也增加了复制架构的多样性,满足各种不同的数据复制需求。
【Master dump thread improvements】
mysql5.7.2,包括整个5.6版本,主库写binlog,dump thread 读binlog日志,是互斥的,不能并行执行,之后的版本中,降低了dump thread的锁粒度,使得多个dump thread可以并行读binlog event,并且可以在主库写入的时候读取。
server端的其他增强
【explain 新赠语法】
mysql> explain for connection 4;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+----------+----------+-------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+----------+----------+-------------------------------------------------------------+
| 1 | SIMPLE | a | NULL | index | PRIMARY | name | 33 | NULL | 16326503 | 100.00 | Using index |
| 1 | SIMPLE | b | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 16326503 | 100.00 | Range checked for each record (index map: 0x1); Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+----------+----------+-------------------------------------------------------------+
2 rows in set (0.01 sec)
【Optimizer Hint】
【trigger】
可以在同一张表上增加多个具有相同trigger event
(INSERT, UPDATE, DELETE) 和action time (BEFORE, AFTER)的trigger,在之前只能有一个。
【 Generated Columns】
mysql> CREATE TABLE triangle (
-> sidea DOUBLE,
-> sideb DOUBLE,
-> sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql> select * from triangle;
+-------+-------+--------------------+
| sidea | sideb | sidec |
+-------+-------+--------------------+
| 1 | 1 | 1.4142135623730951 |
| 3 | 4 | 5 |
| 6 | 8 | 10 |
+-------+-------+--------------------+
mysql> show create table triangle;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| triangle | CREATE TABLE `triangle` (
`sidea` double DEFAULT NULL,
`sideb` double DEFAULT NULL,
`sidec` double GENERATED ALWAYS AS (sqrt(((`sidea` * `sidea`) + (`sideb` * `sideb`)))) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
以下改进/性能提升口述。还没有深入了解如何实现。
【ctrl+c】
mysql> ^C
mysql> ^C
mysql> ^C
mysql> ^C
mysql> show databases ^C
mysql>
【Database name rewriting with mysqlbinlog】
--rewrite-db=name Rewrite the row event to point so that it can be applied
to a new database
【 Index condition pushdown support for partitioned tables】