【2016年11月27日创建】

【2016年11月28日修改】- 添加测试实验

【2016年12月2日修改】- 添加SSL测试实验

【2016年12月6日新增】


1. 性能

1)性能提升

    MySQL5.7在支持多处理器和高度并发CPU线程的系统上,提供更持续的线性性能和扩展性。实现这一点的关键是通过Oracle InnoDB存储引擎的效率的并发性,来消除InnoDB内核争用和互斥锁定的现象。

    在OLTP只读模式下,MySQL5.7比MySQL5.6性能提高3倍。

    在OLTP读/写模式下,MySQL5.7比MySQL5.6性能提高2倍。


2. 安全性

1) 默认开启SSL

【2016年11月27日创建】

    1) MySQL5.7和Percona5.7版本里支持。

    2) MySQL5.7在启动时,使用openSSL可以自动生成SSL和RSA证书和密钥文件。

    3)  安全套接层(Secure Sockets Layer,SSL)及其继任者传输层安全(Transport Layer Security,TLS)是为网络通信提供安全及数据完整性的一种安全协议。TLS与SSL在传输层对网络连接加密,用以保障在internet上数据传输之安全,利用数据加密(Encryption)技术,可以确保在网络上传输过程中不会被截取及窃听。

    4) 安装部分请查看MySQL 5.7安装

    5) MySQL 5.7 记录密码工具

    mysql_config_editor set --login-path=client --host-localhost --user=root --password

    会在/root目录下生成隐藏文件.mylogin.cnf,查看是乱码

        * 在root密码变更后,需要重新执行mysql_config_editor

        * 登录mysql --login-path=client

        * MariaDB 10.1版本没有此工具

测试:

[root@test03 mysql]# mysql_config_editor set --login-path=client --host=localhost --user=root --password
Enter password: 输入密码
[root@test03 mysql]# mysql --login-path=client  #登录
[root@test03 mysql]# mysql_config_editor print  #查看
[client]
user = root
password = *****
host = localhost
[root@test03 mysql]# mysql_config_editor remove --login-path=client  #移除
[root@test03 mysql]# mysql_config_editor print    #只显示client
[root@test03 mysql]# mysql_config_editor print --all  #显示所有
mysql_config_editor set --login-path=client330701 --socket=/data/mysql/mysql3307/mysql_3307.sock --port=3307 --host=localhost  --user=root --password
--host
--port
--socket  # mysql 5.7.14测试版本
--user
--password
--all   # 打印所有login path



    6) 指定用户使用SSL连接

    创建用户

然后我/usr/local/mysql/bin/mysql_ssl_rsa_setup  --help
verbose                           FALSE
datadir                           /data/mysql/mysql3306/data
suffix                            5.7.14
uid                               (No default value)



【MySQL 5.7】

1)新建SSL权限

使用工具生成密钥文件

/usr/local/mysql/bin/mysql_ssl_rsa_setup   #这也是我的安装目录

执行完成之后,我是想看看生成的密码是什么样子,发现各种找,都没找到。

然后我/usr/local/mysql/bin/mysql_ssl_rsa_setup  --helpverbose                           FALSEdatadir                           /data/mysql/mysql3306/datasuffix                            5.7.14uid                               (No default value)

有戏,my.cnf在/etc下,自动识别。


[root@test03 mysql3306]# ll /data/mysql/mysql3306/data

总用量 422964

-rw-------. 1 mysql mysql      1679 11 29 13:26 ca-key.pem

# CA自签证书

-rw-r--r--. 1 mysql mysql      1074 11 29 13:26 ca.pem

# CA私钥

-rw-r--r--. 1 mysql mysql      1078 11 29 13:26 client-cert.pem

# 客户端证书

-rw-------. 1 mysql mysql      1675 11 29 13:26 client-key.pem

# 客户端私钥

-rw-r--r--. 1 mysql mysql      1078 11 29 13:26 server-cert.pem

# 服务端证书

-rw-------. 1 mysql mysql      1679 11 29 13:26 server-key.pem

# 服务端私钥

#启动时产生RSA密钥对

-rw-------. 1 mysql mysql      1675 11 29 13:26 private_key.pem

-rw-r--r--. 1 mysql mysql       451 11 29 13:26 public_key.pem


添加SSL参数到my.cnf

[root@test03 mysql3306]# vim /etc/my.cnf 
[mysql]
ssl-ca=/data/mysql/mysql3306/data/ca.pem
ssl-cert=/data/mysql/mysql3306/data/client-cert.pem
ssl-key=/data/mysql/mysql3306/data/client-key.pem
[mysqld]
ssl-ca=/data/mysql/mysql3306/data/ca.pem
ssl-cert=/data/mysql/mysql3306/data/client-cert.pem
ssl-key=/data/mysql/mysql3306/data/client-key.pem



重启mysql

验证服务器是否开启SSL,如果没有开启SSL,创建的ssluser是无法连接的

root@localhost [(none)]>show variables like '%ssl%';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| have_openssl  | YES                                        |
| have_ssl      | YES                                        |
| ssl_ca        | /data/mysql/mysql3306/data/ca.pem          |
| ssl_capath    |                                            |
| ssl_cert      | /data/mysql/mysql3306/data/client-cert.pem |
| ssl_cipher    |                                            |
| ssl_crl       |                                            |
| ssl_crlpath   |                                            |
| ssl_key       | /data/mysql/mysql3306/data/client-key.pem  |
+---------------+--------------------------------------------+



2)使用SSL用户登录

    1. 默认登录是跳过ssl登录的( Defaults to on; use --skip-ssl to disable.

    2. --ssl参数已经废弃,可以使用--ssl-mode

    3. 下面是相关参数

--ssl-mode=name     SSL connection mode.
  --ssl               Deprecated. Use --ssl-mode instead.
                      (Defaults to on; use --skip-ssl to disable.)
  --ssl-verify-server-cert 
                      Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.
  --ssl-ca=name       CA file in PEM format.
  --ssl-capath=name   CA directory.
  --ssl-cert=name     X509 cert in PEM format.
  --ssl-cipher=name   SSL cipher to use.
  --ssl-key=name      X509 key in PEM format.
  --ssl-crl=name      Certificate revocation list.
  --ssl-crlpath=name  Certificate revocation list path.
  --tls-version=name  TLS version to use, permitted values are: TLSv1, TLSv1.1




3)删除SSL权限:


    root@localhost [(none)]>select user,host,ssl_type,ssl_cipher from mysql.user where user='ssluser';

+---------+-----------+----------+------------+

| user    | host      | ssl_type | ssl_cipher |

+---------+-----------+----------+------------+

| ssluser | %         | ANY      |            |

| ssluser | localhost | ANY      |            |

+---------+-----------+----------+------------+

root@localhost [(none)]>show grants for ssluser@'%'; +----------------------------------------------+ | Grants for ssluser@%                         | +----------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'%' | +----------------------------------------------+

删除SSL权限

root@localhost [(none)]>alter user 'ssluser'@'%' require none;
Query OK, 0 rows affected (0.00 sec)
 
root@localhost [(none)]>select user,host,ssl_type,ssl_cipher from mysql.user where user='ssluser';
+---------+-----------+----------+------------+
| user    | host      | ssl_type | ssl_cipher |
+---------+-----------+----------+------------+
| ssluser | %       |         |           |  # 看这里ssl_type没有ANY了
| ssluser | localhost | ANY      |            |
+---------+-----------+----------+------------+
2 rows in set (0.00 sec)







2) 不再明文显示用户密码

    【2016年11月27日创建】

        MySQL 5.6/MySQL 5.7和percona 5.6/5.7支持

        Mariadb 10.1对于binlog中和用户密码相关的操作不加密。


3)sql_mode改变

    【2016年11月27日创建】

        MySQL 5.7/MariaDB 10.1默认启用STRICT_TRANS_TABLES严格模式。该模式的作用是进行数据校验,错误数据不能插入,报错并且进行事务回滚。

        例如:如果age字段为init数值整形,如插入字符类型时会发出警告,如果sql_mode为空,会继续插   入。如果为strict_trans_tables模式,会发警告并进行事务回滚。


3. InnoDB存储引擎的提升

    1)更改索引名字时不会锁表

        【2016年11月27日创建】

            MySQL5.7/Percona 5.7版本支持

    2)在线DDL修改varchar字段属性时不锁表

        【2016年11月27日创建】

    MySQL 5.7针对DDL功能做了加强,修改varchar字段已不锁表。

        * ALGORITHM用于指定创建或删除索引的算法

        * COPY表示按照MySQL 5.1版本之前的方法,即创建临时表并全表拷贝数据,原表加全局读锁

        * INPLACE表示创建字段或删除字段操作不需要创建临时表

        * DEFAULT表示通过INPLACE的算法还是COPY的算法

        alter table sbtest ALGORITHM=INPLACE,modify pad varchar(90) not null;


3)innodb/myisam存储引擎支持中文全文索引

  该功能只在mysql 5.7和percona 5.7版本中支持


4)innodb buffer pool预热改进

    只支持mysql 5.7和percona 5.7。当数据库重启时,对innodb buffer pool预热,以便于快速恢复到之前的性能状态。

    在mysql 5.6/mariadb 10.0版本里,为解决上述问题,提供了一个新特性来快速预热buffer_pool缓冲池。在my.cnf添加:

    innodb_buffer_pool_dump_at_shutdown =1

    该命令用于在关闭时把热数据dump到本地磁盘。

    采用手工方式把热数据dump到本地磁盘:

    innodb_buffer_pool_dump_now = 1

    启动时把热数据加载到内存:

    innodb_buffer_pool_load_at_startup = 1

    采用手工方式把热数据加载到内存:

    innodb_buffer_pool_load_now = 1



10)修改innodb redo log事务日志文件大小更人性化

在MySQL 5.5版本里,如果想修改ib_logfile(redo log)文件大小,那么必须如下:

1)执行set global innodb_fast_shutdown=0; 命令将所有的脏页刷到磁盘

2)执行mysqladmin shutdown命令关闭数据库

3)在my.cnf文件里修改innodb_log_file_size参数值

4)执行mv ib_logfile*  bak/,将redo log移动到bak下(不移走会报错)

5)执行mysql_safe --defaults-file=/etc/my.cnf --user=mysql &,再启动mysql


在MySQL 5.6/5.7或MariaDB 10.0/10.1版本里:将步骤4)省略,直接启动mysql


在MySQL 5.6/5.7或MariaDB 10.0/10.1版本里:

    1. innodb redo log的大小从最大4G提高到512G,可通过参数innodb_log_file_size来配置

    2. innodb_log_files_in_group设置为3组redo log,那么innodb_log_file_size * innodb_log_files_in_group 不能超过512G。设置的值越大,越可以减少checkpoint刷新脏页的频率,这对提升mysql性能很重要,风险增加了宕机恢复时。建议TPS在200~300每秒/写的业务场景,通过设置4GB即可


11)死锁可以打印到错误日志里

在MySQL 5.6版本中查看死锁,需要执行show engine innodb status\G;

在MySQL 5.6/5.7 或 MariaDB 10.0/10.1版本中,在my.cnf配置文件里加入:

innodb_print_all_deadlocks=1






--- 尊重版权《MySQL管理之道(性能调优、高可用与监控)》(贺春旸)