背景
MySQL 8.0 正式版 8.0.11 已发布, 官方表示 MySQL 8 要比 MySQL 5.7 快 2 倍, 还带来了大量的改进和更快的性能!
升级至 MySQL8.0
从 MySQL 5.7 升级到 MySQL 8.0 仅支持通过使用 in-place 方式进行升级, 并且不支持从 MySQL 8.0 降级到 MySQL 5.7(或从某个 MySQL 8.0 版本降级到任意一个更早的 MySQL 8.0 版本). 唯一受支持的替代方案是在升级之前对数据进行备份.
新特性与改进
性能:
MySQL 8.0 的速度要比 MySQL 5.7 快 2 倍. MySQL 8.0 在以下方面带来了更好的性能: 读 / 写工作负载, IO 密集型工作负载, 以及高竞争 ("hot spot" 热点竞争问题) 工作负载.
NoSQL
MySQL 从 5.7 版本开始提供 NoSQL 存储功能, 目前在 8.0 版本中这部分功能也得到了更大的改进. 该项功能消除了对独立的 NoSQL 文档数据库的需求, 而 MySQL 文档存储也为 schema-less 模式的 JSON 文档提供了多文档事务支持和完整的 ACID 合规性.
窗口函数(Window Functions)
从 MySQL 8.0 开始, 新增了一个叫窗口函数的概念, 它可以用来实现若干新的查询方式. 窗口函数与 SUM(),COUNT() 这种集合函数类似, 但它不会将多行查询结果合并为一行, 而是将结果放回多行当中. 即窗口函数不需要 GROUP BY
隐藏索引
在 MySQL 8.0 中, 索引可以被 "隐藏" 和 "显示". 当对索引进行隐藏时, 它不会被查询优化器所使用. 我们可以使用这个特性用于性能调试, 例如我们先隐藏一个索引, 然后观察其对数据库的影响. 如果数据库性能有所下降, 说明这个索引是有用的, 然后将其 "恢复显示" 即可; 如果数据库性能看不出变化, 说明这个索引是多余的, 可以考虑删掉.
降序索引
MySQL 8.0 为索引提供按降序方式进行排序的支持, 在这种索引中的值也会按降序的方式进行排序.
通用表表达式(Common Table Expressions CTE)
在复杂的查询中使用嵌入式表时, 使用 CTE 使得查询语句更清晰.
UTF-8 编码
从 MySQL 8 开始, 使用 utf8mb4 作为 MySQL 的默认字符集.
JSON
MySQL 8 大幅改进了对 JSON 的支持, 添加了基于路径查询参数从 JSON 字段中抽取数据的 JSON_EXTRACT() 函数, 以及用于将数据分别组合到 JSON 数组和对象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函数.
可靠性
InnoDB 现在支持表 DDL 的原子性, 也就是 InnoDB 表上的 DDL 也可以实现事务完整性, 要么失败回滚, 要么成功提交, 不至于出现 DDL 时部分成功的问题, 此外还支持 crash-safe 特性, 元数据存储在单个事务数据字典中.
高可用性(High Availability)
InnoDB 集群为您的数据库提供集成的原生 HA 解决方案.
安全性
对 OpenSSL 的改进, 新的默认身份验证, SQL 角色, 密码强度, 授权.
下载地址
官方多平台下载地址
https://dev.mysql.com/downloads/mysql/
Linux Generic(通用版)最新版 64 位下载地址 [8.0.11]
https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.11-linux-glibc2.12-x86_64.tar
安装实践
环境
- [root@sea_bj_master ~]# cat /etc/redhat-release
- CentOS Linux release 7.3.1611 (Core)
- [root@sea_bj_master ~]# uname -r
- 3.10.0-514.21.1.el7.x86_64
下载
- [root@sea_bj_master ~]# cd /usr/local/src/
- [root@sea_bj_master src]# wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.11-linux-glibc2.12-x86_64.tar
- --2018-06-05 00:19:12-- https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.11-linux-glibc2.12-x86_64.tar
创建 mysql 用户名与用户组
[root@sea_bj_master src]# useradd -r -s /sbin/nologin -c 'mysql application run user' mysql
解压 MySQL 压缩包, 并将解压包移动至 / usr/local 目录下
- [root@sea_bj_master src]# tar xf mysql-8.0.11-linux-glibc2.12-x86_64.tar
- [root@sea_bj_master src]# ll mysql-*8.0.11-linux-glibc2.12-x86_64.tar*
- -rw-r--r-- 1 root root 654131200 Apr 8 14:30 mysql-8.0.11-linux-glibc2.12-x86_64.tar
- -rw-r--r-- 1 7161 31415 603019898 Apr 8 16:29 mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz
- -rw-r--r-- 1 7161 31415 51099557 Apr 8 16:27 mysql-test-8.0.11-linux-glibc2.12-x86_64.tar.gz
- [root@sea_bj_master src]# tar xf mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz
- [root@sea_bj_master src]# mv mysql-8.0.11-linux-glibc2.12-x86_64 /usr/local/ -v
- 'mysql-8.0.11-linux-glibc2.12-x86_64' -> '/usr/local/mysql-8.0.11-linux-glibc2.12-x86_64'
创建软连接, 方便今后升级本体程序不影响配置文件
- [root@sea_bj_master src]# ln -s /usr/local/mysql-8.0.11-linux-glibc2.12-x86_64 /usr/local/mysql -v
- '/usr/local/mysql' -> '/usr/local/mysql-8.0.11-linux-glibc2.12-x86_64'
创建 MySQL 数据导入 / 导出数据专放目录文件夹, 修改权限
- [root@sea_bj_master src]# mkdir -v /usr/local/mysql/mysql-files
- mkdir: created directory '/usr/local/mysql/mysql-files'
创建数据文件夹
- [root@sea_bj_master src]# mkdir /application/mysql/data -v
- mkdir: created directory '/application/mysql/data'
修改 MySQL 目录所有者和所有组
[root@sea_bj_master src]# chown root.mysql -R /usr/local/mysql-8.0.11-linux-glibc2.12-x86_64
修改 MySQL 数据目录与 数据导入 / 导出专放目录的所属用户与所属组
- [root@sea_bj_master src]# chown mysql.mysql -R /application/mysql/data /usr/local/mysql/mysql-files -v
- ownership of '/application/mysql/data' retained as mysql:mysql
- ownership of '/usr/local/mysql/mysql-files' retained as mysql:mysql
重命名不使用系统自带 MySQL 配置文件 /etc/my.cnf [ debian 类系统在 /etc/mysql/my.cnf ]
- [root@sea_bj_master src]# mv /etc/my.cnf{,.old} -v
- mv: overwrite '/etc/my.cnf.old'? y
- '/etc/my.cnf' -> '/etc/my.cnf.old'
初始化
- [root@sea_bj_master src]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/application/mysql/data --basedir=/usr/local/mysql
- 2018-06-04T16:32:33.389541Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.11) initializing of server in progress as process 681
- 2018-06-04T16:32:39.406682Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: mD4Tbl#0d8:W
- 2018-06-04T16:32:42.307915Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.11) initializing of server has completed
启动 ssl 连接(可选)
[root@sea_bj_master src]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --datadir=/application/mysql/data --basedir=/usr/local/mysql
创建启动脚本[systemd]
- [root@sea_bj_master src]# cat /usr/lib/systemd/system/mysqld.service
- #
- # Simple MySQL systemd service file
- #
- # systemd supports lots of fancy features, look here (and linked docs) for a full list:
- # http://www.freedesktop.org/software/systemd/man/systemd.exec.html
- #
- # Note: this file ( /usr/lib/systemd/system/mysql.service )
- # will be overwritten on package upgrade, please copy the file to
- #
- # /etc/systemd/system/mysql.service
- #
- # to make needed changes.
- #
- # systemd-delta can be used to check differences between the two mysql.service files.
- #
- [Unit]
- Description=MySQL Community Server
- After=network.target
- After=syslog.target
- [Install]
- WantedBy=multi-user.target
- Alias=mysql.service
- [Service]
- User=mysql
- Group=mysql
- # Execute pre and post scripts as root
- PermissionsStartOnly=true
- # Needed to create system tables etc.
- #ExecStartPre=/usr/bin/mysql-systemd-start pre
- # Start main service
- ExecStart=/usr/local/mysql/bin/mysqld_safe
- # Don't signal startup success before a ping works
- #ExecStartPost=/usr/bin/mysql-systemd-start post
- # Give up if ping don't get an answer
- TimeoutSec=600
- Restart=always
- PrivateTmp=false
systemd 加载启动配置文件
[root@sea_bj_master src]# systemctl daemon-reload
创建 MySQL 配置文件 my.cnf, 指定数据目录
- [root@sea_bj_master src]# cat /etc/my.cnf
- [mysqld]
- user = mysql
- bind_address = 0.0.0.0
- character_set_server=utf8mb4
- skip_name_resolve = 1
- max_connections = 800
- max_connect_errors = 1000
- datadir = /application/mysql/data
启动 MySQL 服务
- [root@sea_bj_master src]# systemctl start mysqld
- [root@sea_bj_master src]# systemctl status mysqld
- mysqld.service - MySQL Community Server
- Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
- Active: active (running) since Tue 2018-06-05 00:42:23 CST; 5s ago
- Main PID: 2273 (mysqld_safe)
- CGroup: /system.slice/mysqld.service
- 2273 /bin/sh /usr/local/mysql/bin/mysqld_safe
- 2424 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/application/mysql/data --plugin...
Jun 05 00:42:23 sea_bj_master systemd[1]: Started MySQL Community Server.
Jun 05 00:42:23 sea_bj_master systemd[1]: Starting MySQL Community Server...
Jun 05 00:42:23 sea_bj_master mysqld_safe[2273]: 2018-06-04T16:42:23.125697Z mysqld_safe Logging to '/applicati...rr'.
Jun 05 00:42:23 sea_bj_master mysqld_safe[2273]: 2018-06-04T16:42:23.144258Z mysqld_safe Starting mysqld daemon...data
Hint: Some lines were ellipsized, use -l to show in full.
连接 MySQL 服务并修改初始密码
[root@sea_bj_master src]# mysql -p'mD4Tbl#0d8:W'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 8
- Server version: 8.0.11
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- mysql> set password = '123';
- Query OK, 0 rows affected (0.04 sec)
- mysql> \s
- --------------
- mysql Ver 8.0.11 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)
- Connection id: 8
- Current database:
- Current user: root@localhost
- SSL: Not in use
- Current pager: stdout
- Using outfile: ''
- Using delimiter: ;
- Server version: 8.0.11
- Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
- UNIX socket: /tmp/mysql.sock
- Uptime: 1 min 0 sec
Threads: 2 Questions: 7 Slow queries: 0 Opens: 238 Flush tables: 2 Open tables: 73 Queries per second avg: 0.116
--------------
来源: http://blog.51cto.com/lisea/2124845