MySQL从5.7.18到8.0.17的华丽转身
升级前对旧数据库进行检查：
手动检查：
一、注意事项：
mysql从5.7升级到8.0是支持的，但是只支持GA版本的升级，并且要求版本为5.7.9或者更高
在升级到8.0之前，建议升级到5.7的最新版本。仅仅支持从5.7版本升级到8.0，不支持5.6版本升级到8.0
二、升级准备工作：
1，备份数据（包括当前的数据库和日志文件）
2，升级检查：mysqlcheck -u root -p --all-databases --check-upgrade #如果全部返回OK为正常
demo.categories OK
demo.customers OK
demo.employees OK
demo.ord_detail OK
demo.order_details OK
3，检查分区表（不支持分区的存储引擎）
(root@153_master)[(none)]> select table_schema,table_name from information_schema.tables where engine not in ('innodb','ndbcluster') and create_options like '%partitioned';
Empty set (0.01 sec) #返回空为正常
如果存在记录，必须把对应表修改为innodb引擎或者把表修改为非分区的
设置表的存储引擎为INNODB
alter table table_name engine=INNODB
移除表分区
alter table table_name remove partitioning;
4，在mysql数据库中，必须不存在与mysql8.0数据字典相同名称的表。
select table_schema,table_name from information_schema.tables where lower(table_schema)='mysql' and lower(table_name) in (
'catalogs',
'character_sets',
'collations',
'column_statistics',
'column_type_elements',
'columns',
'dd_properties',
'events',
'foreign_key_column_usage',
'foreign_keys',
'index_column_usage',
'index_partitions',
'index_stats',
'indexes',
'parameter_type_elements',
'parameters',
'resource_groups',
'routines',
'schemata',
'st_spatial_reference_systems',
'table_partition_values',
'table_partitions',
'table_stats',
'tables',
'tablespace_files',
'tablespaces',
'triggers',
'view_routine_usage',
'view_table_usage'
);
Empty set (0.01 sec) #为空表示正常
所有同名的表必须要重命名。
5，必须要不存在外键超过64字符的表。
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN
(SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1),
INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1)
FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN
WHERE LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);
Empty set (0.04 sec) #为空表示正常
如果存在，必须修改表。
6，必须要确保不存在拥有ENUM或者SET列元素并且超过255字符或者1020字节长度的表和存储过程
7，mysql5.7中必须不使用8.0不支持的特性。例如NDB引擎，8.0不再支持；部分启动选项不再支持：例如--ignore-db-dir已被移除。
8，如果innodb使用了XA事务，确保所有XA事务提交或者回滚。
9，如果存在加密的innodb表空间，执行语句：alter instance rotate innodb master key;
10，如果mysql5.7启动设置了innodb_fast_shutdown参数为2（冷停止），通过设置参数为1或者0来实现快速或者慢停止。
set global innodb_fast_shutdown=1; ---fast shutdown
set global innodb_fast_shutdown=0; ---slow shutdown
11，停止数据库：mysqladmin -u root -p shutdown
通过mysql-shell自动检查：
安装mysql-shell:
下载地址：https://dev.mysql.com/downloads/shell/
yum install -y mysql-shell-8.0.18-1.el6.i686.rpm
或者直接下载解压，即可使用：
mysql-shell-8.0.18-linux-glibc2.12-x86-64bit.tar.gz
执行升级前置检查：
mysqlsh -h 127.0.0.1 -u admin -p'123456' -e "util.checkForServerUpgrade();"
检查结果有2个警告，分别是字符集utf8mb3和caching_sha2_password认证插件。MySQL 8.0 推荐使用utf8mb4，密码认证如果想要兼容5.7，需要加上
default_authentication_plugin=mysql_native_password。
[root@node153 mysql8.0.17]# mysqlsh -h127.0.0.1 -uroot -p'Hexin123.' -e "util.checkForServerUpgrade();"
WARNING: Using a password on the command line interface can be insecure.
The MySQL server at 127.0.0.1:3306, version 5.7.18-log - MySQL Community Server
(GPL), will now be checked for compatibility issues for upgrade to MySQL
8.0.18...
开始升级数据库：
一、环境：
[root@node153 ~]# uname -a
Linux node153 2.6.32-696.el6.x86_64 #1 SMP Tue Mar 21 19:29:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
[root@node153 ~]# cat /etc/redhat-release
CentOS release 6.9 (Final)
[root@node153 ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper
二、备份数据库 （-A 参数是备份全部数据库的数据和结构）
mysqldump -uroot -p123456 -A > /opt/databackup.sql
//用户名是root 密码是123456 备份到/opt目录下命名为databackup.sql
卸载旧版数据库
rpm -qa|grep mysql //查看系统有哪些包
[root@node ~]# rpm -qa | grep mysql
mysql-community-server-5.7.18-1.el6.x86_64
mysql-community-common-5.7.18-1.el6.x86_64
mysql-community-libs-5.7.18-1.el6.x86_64
mysql-community-client-5.7.18-1.el6.x86_64
使用RPM 方式卸载掉所显示的包
[root@node ~]#rpm -e mysql-community-devel-5.7.18-1.el6.x86_64 mysql-community-common-5.7.18-1.el6.x86_64 mysql-community-server-5.7.18-1.el6.x86_64 mysql-community-client-5.7.18-1.el6.x86_64 mysql-community-libs-5.7.18-1.el6.x86_64 mysql-community-libs-compat-5.7.18-1.el6.x86_64 --nodeps
查看是否卸载完成：
[root@node ~]#rpm -qa | grep mysql
[root@node ~]#
检查结果如上,查看不到任何mysql的包文件。便可开始安装新版本mysql
安装新版mysql
安装前准备工作,到MySQL官网下载8.0.17 RPM安装包,解压后以下：
[root@node153 mysql8.0.17]# ls
mysql-community-client-8.0.17-1.el6.x86_64.rpm mysql-community-libs-compat-8.0.17-1.el6.x86_64.rpm
mysql-community-common-8.0.17-1.el6.x86_64.rpm mysql-community-server-8.0.17-1.el6.x86_64.rpm
mysql-community-devel-8.0.17-1.el6.x86_64.rpm mysql-community-test-8.0.17-1.el6.x86_64.rpm
mysql-community-libs-8.0.17-1.el6.x86_64.rpm
执行安装
yum install perl.x86_64 perl-devel.x86_64 -y
yum install perl-JSON.noarch -y
[root@node153 mysql8.0.17]# rpm -ivh mysql-community-client-8.0.17-1.el6.x86_64.rpm mysql-community-common-8.0.17-1.el6.x86_64.rpm mysql-community-devel-8.0.17-1.el6.x86_64.rpm mysql-community-libs-8.0.17-1.el6.x86_64.rpm mysql-community-libs-compat-8.0.17-1.el6.x86_64.rpm mysql-community-server-8.0.17-1.el6.x86_64.rpm mysql-community-test-8.0.17-1.el6.x86_64.rpm
warning: mysql-community-client-8.0.17-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:mysql-community-common ########################################### [ 14%]
2:mysql-community-libs ########################################### [ 29%]
3:mysql-community-client ########################################### [ 43%]
4:mysql-community-server ########################################### [ 57%]
5:mysql-community-test ########################################### [ 71%]
6:mysql-community-devel ########################################### [ 86%]
7:mysql-community-libs-co########################################### [100%]
修改配置文件：
cat /etc/my.cnf
[mysqld]
default_authentication_plugin=mysql_native_password
lower_case_table_names=1
先通过通过脚本手动启动mysql，查看是否有报错
[root@node153 mysql8.0.17]# mysqld_safe --user=mysql --datadir=/var/lib/mysql
2019-11-19T08:44:35.099114Z mysqld_safe Logging to '/var/log/mysqld.log'.
2019-11-19T08:44:35.144489Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
启动日志：
2019-11-19T08:44:35.913710Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.17) starting as process 7049
2019-11-19T08:44:36.078293Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2019-11-19T08:44:39.721041Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001287 - 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead
2019-11-19T08:44:41.295078Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2019-11-19T08:44:42.252998Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80017' started.
2019-11-19T08:44:46.021101Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80017' completed.
2019-11-19T08:44:46.224561Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-11-19T08:44:46.302686Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.17' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
2019-11-19T08:44:46.604210Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
破解root密码：如果是升级数据库，数据库密码就是原来的密码，不会变，不需要破解root密码
cat /etc/my.cnf
[mysqld]
skip-grant-tables
[root@node~]# service mysqld restart
[root@node~]# mysql -uroot -p
Enter password: ## 直接回车
mysql> use mysql; ## 进入mysql表
mysql> update user set authentication_string='' where user='root'; ## 将密码设置为空（必须在mysql库设置）
mysql> quti
[root@node~]# vi /etc/my.cnf ##将添加的skip那一行删除，然后重启服务；
[root@node~]# service mysqld restart
[root@node~]# mysql -uroot -p
Enter password: ##直接回车
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Root@123'; ##设置密码（密码要复杂一点，否指会报错）
mysql> exit
#mysql_upgrade -u root -p //检查兼容更新,8.0.16版本后不需要执行这一步，由mysqld直接执行完成了
修改my.cnf文件：
MySQL8.0.17默认会开启二进制日志，为了与旧版数据库的日志名称一样，需要修改日志名称;
cat /etc/my.cnf
[mysqld]
log_bin=mysql-bin
server_id=1
service mysqld start // 启动服务
mysql -uroot -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 283
Server version: 8.0.17 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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> \s
--------------
mysql Ver 8.0.17 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id: 325
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.17 MySQL Community Server - GPL
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 6 min 44 sec
Threads: 3 Questions: 267 Slow queries: 0 Opens: 203 Flush tables: 3 Open tables: 123 Queries per second avg: 0.660
--------------
升级完毕
[root@node153 ~]# mysql -V
mysql Ver 8.0.17 for Linux on x86_64 (MySQL Community Server - GPL)
升级前后数据目标文件变化：
[root@node153 mysql8.0.17]# cd /var/lib/mysql
[root@node153 mysql]# ls
auto.cnf demo ib_logfile1 mysql-bin.000003 private_key.pem server-key.pem
ca-key.pem flexviews maxscale_schema mysql-bin.000004 public_key.pem sys
ca.pem ib_buffer_pool mysql mysql-bin.index relay-bin.000001 webcron
client-cert.pem ibdata1 mysql-bin.000001 node153.log relay-bin.index
client-key.pem ib_logfile0 mysql-bin.000002 performance_schema server-cert.pem
[root@node153 mysql]# service mysqld start
正在启动 mysqld： [确定]
[root@node153 mysql]# ls
auto.cnf ib_buffer_pool mysql mysql.ibd public_key.pem undo_002
ca-key.pem ibdata1 mysql-bin.000001 mysql.sock relay-bin.000001 webcron
ca.pem ib_logfile0 mysql-bin.000002 mysql.sock.lock relay-bin.index
client-cert.pem ib_logfile1 mysql-bin.000003 mysql_upgrade_info server-cert.pem
client-key.pem ibtmp1 mysql-bin.000004 node153.log server-key.pem
demo #innodb_temp mysql-bin.000005 performance_schema sys
flexviews maxscale_schema mysql-bin.index private_key.pem undo_001
