MySQL-5.7新特性

最新推荐文章于 2024-07-15 15:01:56 发布

sunashe

最新推荐文章于 2024-07-15 15:01:56 发布

阅读量1.8k

点赞数 1

文章标签： mysql

欢迎转载，但请注明出处。

本文链接：https://blog.csdn.net/sun_ashe/article/details/47785431

版权

新增特性

与安全相关的新增特性

【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在创建索引时的速度，当然也包括创建全文索引时，在创建空间索引时不支持。原理如下：
首先使用索引将数据排序，然后进行插入

 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】

sunashe

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL-5.7新特性

Mysql在被sun，继而被oracle收购之后，并没有被oracle这个巨人雪藏，反而近几年的发展速度极为之快，谁都知道，你oracle做不好mysql，开源的市场就会被别人取代，pg，MariaDB，percona server，如果说开源又不挣钱，我就只能呵呵了。这里我选的版本是最新的开发版，5.7.8.只为了看一下最新最近的技术，当然，这个版本是不可能被用到生产中去的，因为还不够成熟，还需要
复制链接

扫一扫