MySQL 5.7安装及版本升级到8.0
官方文档
支持的系统和架构
| Architecture | ||||
| Oracle Linux / Red Hat / CentOS | ||||
| Oracle Linux 8 / Red Hat Enterprise Linux 8 / CentOS 8 | x86_64, ARM 64 | • | ||
| Oracle Linux 7 / Red Hat Enterprise Linux 7 / CentOS 7 | ARM 64 | • | ||
| Oracle Linux 7 / Red Hat Enterprise Linux 7 / CentOS 7 | x86_64 | • | • | • |
| Oracle Linux 6 / Red Hat Enterprise Linux 6 / CentOS 6 | x86_32, x86_64 | • | • | • |
| Oracle Solaris | ||||
| Solaris 11 (Update 4+) | SPARC_64, x86_64 | • | • | • |
| Solaris 10 (Update 11+) | SPARC_64, x86_32, x86_64 | • | ||
| Canonical | ||||
| Ubuntu 18.04 LTS | x86_32, x86_64 | • | • | |
| Ubuntu 16.04 LTS | x86_32, x86_64 | • | • | |
| SUSE | ||||
| SUSE Enterprise Linux 15 / OpenSUSE 15 | x86_64 | • | ||
| SUSE Enterprise Linux 12 (12.3+) | x86_64 | • | • | • |
| Debian | ||||
| Debian GNU/Linux 10 | x86_64 | • | • | |
| Debian GNU/Linux 9 | x86_32, x86_64 | • | • | • |
| Debian GNU/Linux 8 | x86_32, x86_64 | • | • | |
| Microsoft Windows Server | ||||
| Microsoft Windows 2019 Server | x86_64 | • | ||
| Microsoft Windows 2016 Server | x86_64 | • | • | • |
| Microsoft Windows 2012 Server R2 | x86_64 | • | • | • |
| Microsoft Windows | ||||
| Microsoft Windows 10 | x86_64 | • | • | |
| Apple | ||||
| macOS 10.14 | x86_64 | • | • | |
| macOS 10.13 | x86_64 | • | • | |
| FreeBSD | ||||
| FreeBSD 12 | x86_64 | • | ||
| Various Linux | ||||
| Generic Linux (tar format) | x86_32, x86_64, glibc 2.12, libstdc++ 4.4 | • | • | • |
| • | • | • | ||
| • | • | • | ||
| • | • | • |
确认安装的版本
首先，决定要安装开发版本还是通用版本（GA）。开发版本具有最新功能，但不建议用于生产环境。
MySQL 8.0中的命名方案使用的发行版名称由三个数字和一个可选的后缀组成（例如， **mysql-8.0.1-dmr**），版本名称中的数字解释如下：
- 第一个数字（**8**）是主版本号。 - 第二个数字（**0**）是次要版本号。总而言之，主要和次要数字构成发行版本号。序列号描述了稳定的功能集。 - 第三个数字（**1**）是发行系列中的版本号。对于每个新的错误修正版本，此值均递增。在大多数情况下，系列中的最新版本是最佳选择。 版本名称也可以包含一个后缀，以指示版本的稳定性。在一系列发行中，发布会通过一组后缀来指示稳定性水平如何提高。可能的后缀是： - **dmr**指示开发里程碑版本（DMR）。MySQL开发使用里程碑模型，其中每个里程碑都引入了一小部分经过全面测试的功能。从一个里程碑到下一个里程碑，基于尝试这些正常发布的社区成员提供的反馈，功能界面可能会更改，甚至功能可能会被删除。里程碑版本中的功能可能被视为具有预生产质量。 - **rc**表示发布候选（RC）。通过了MySQL的所有内部测试后，发布候选版本被认为是稳定的。RC版本中可能仍会引入新功能，但是重点将转移到修复错误上，以稳定本系列中较早引入的功能。
- 没有后缀表示具有一般可用性（GA）或正式版。GA版本稳定，已成功通过了较早的发行阶段，并且被认为是可靠的，没有严重的错误并且适合在生产系统中使用。
安装
安装依赖
[root@localhost local]# yum install -y libaio-devel
卸载mariadb的库
[root@localhost local]# yum remove -y mariadb-libs
添加用户和组
[root@localhost ~]# yum install -y bash-comple* tree telnet net-tools bind-utils [root@localhost ~]# groupadd mysql [root@localhost ~]# useradd -r -g mysql -M -s /sbin/nologin mysql
创建数据目录等
[root@localhost ~]# mkdir -p /data/mysql/data_3306
[root@localhost ~]# mkdir -p /data/mysql/binlog_3306
上传软件包、校验和解压
[root@localhost ~]# mkdir /opt/softwares [root@localhost ~]# cd /opt/softwares [root@localhost softwares]# rz -E rz waiting to receive. [root@localhost softwares]# ll total 1121804 -rw-r--r-- 1 root root 644869837 Dec 29 09:41 mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -rw-r--r-- 1 root root 503854832 Dec 28 03:22 mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
[root@localhost softwares]# md5sum mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz 08a3b385db2f151598017b63fbcb6c43 mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz [root@localhost softwares]# md5sum mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz 686b454f7f7f0b0bf4814929fcd0fa81 mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
[root@localhost softwares]# tar xfz mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ [root@localhost softwares]# tar xf mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz -C /usr/local/
创建软连接和添加环境变量
[root@localhost ~]# cd /usr/local/ [root@localhost local]# ln -sv mysql-5.7.26-linux-glibc2.12-x86_64/ mysql57 ‘mysql57’ -> ‘mysql-5.7.26-linux-glibc2.12-x86_64/’ [root@localhost local]# ln -sv mysql-8.0.18-linux-glibc2.12-x86_64/ mysql8 ‘mysql8’ -> ‘mysql-8.0.18-linux-glibc2.12-x86_64/’
[root@localhost local]# vim /etc/profile export PATH=/usr/local/mysql57/bin:$PATH [root@localhost local]# . /etc/profile
查看mysql版本
[root@localhost local]# mysql -V mysql Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using EditLine wrapper
授权
[root@localhost local]# chown -R mysql.mysql /data /usr/local/mysql*
初始化
不安全的方式（本例选此种方式初始化，也可用于生产环境）
没有密码。也没有密码策略
[root@localhost local]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3306 2020-05-23T07:18:54.705153Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2020-05-23T07:18:54.966921Z 0 [Warning] InnoDB: New log files created, LSN=45790 2020-05-23T07:18:55.009145Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2020-05-23T07:18:55.066780Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: a9464927-9cc5-11ea-adad-000c295e277d. 2020-05-23T07:18:55.067835Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2020-05-23T07:18:55.069022Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
安全的方式（本例不采用，只做演示）
初始化后会生成临时密码
[root@localhost ~]# mysqld --initialize --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3306/ 2020-05-23T09:04:23.384515Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2020-05-23T09:04:24.107635Z 0 [Warning] InnoDB: New log files created, LSN=45790 2020-05-23T09:04:24.163224Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2020-05-23T09:04:24.246032Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 65c26572-9cd4-11ea-9c9e-000c295e277d. 2020-05-23T09:04:24.246566Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2020-05-23T09:04:24.253947Z 1 [Note] A temporary password is generated for root@localhost: p6ST4pttak*H
然后使用mysqladmin命令修改密码
[root@localhost ~]# systemctl start mysqld [root@localhost ~]# ss -lntp State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* users:(("sshd",pid=740,fd=3)) LISTEN 0 128 :::22 :::* users:(("sshd",pid=740,fd=4)) LISTEN 0 80 :::3306 :::* users:(("mysqld",pid=12550,fd=22)) [root@localhost ~]# mysqladmin -uroot -p password Secure.pass@123 Enter password: mysqladmin: [Warning] Using a password on the command line interface can be insecure. Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
编辑配置文件
[root@localhost local]# vim /etc/my.cnf [mysqld] user=mysql basedir=/usr/local/mysql57 datadir=/data/mysql/data_3306 socket=/tmp/mysql.sock log_bin=/data/mysql/binlog_3306 port=3306
server_id=5
[mysql]
socket=/tmp/mysql.sock
注意：server_id必须加，要不起不来，报错Starting MySQL. ERROR! The server quit without updating PID file (/data/mysql/data_3306/db.pid).
启动mysql
添加启动项
[root@localhost ~]# cp /usr/local/mysql57/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# chkconfig --add mysqld
[root@localhost ~]# systemctl start mysqld [root@localhost ~]# ss -lntp State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* users:(("sshd",pid=740,fd=3)) LISTEN 0 128 :::22 :::* users:(("sshd",pid=740,fd=4)) LISTEN 0 80 :::3306 :::*
版本升级
5.7-8.0版本参数的变化
https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html
参数比较多，MySQL提供了MySQL shell管理工具来解决这个问题，下载地址：https://dev.mysql.com/downloads/shell/
上传并解压
[root@localhost softwares]# ll total 1156520 -rw-r--r-- 1 root root 644869837 Dec 29 09:41 mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -rw-r--r-- 1 root root 503854832 Dec 28 03:22 mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz -rw-r--r-- 1 root root 35547267 Dec 27 19:08 mysql-shell-8.0.18-linux-glibc2.12-x86-64bit.tar.gz [root@localhost softwares]# tar xf mysql-shell-8.0.18-linux-glibc2.12-x86-64bit.tar.gz -C /usr/local/ [root@localhost softwares]# ln -sv /usr/local/mysql-shell-8.0.18-linux-glibc2.12-x86-64bit/ /usr/local/mysql-sh
查看目录结构
[root@localhost mysqlsh]# ll /usr/local/mysqlsh/ total 0 drwxr-xr-x 2 7161 31415 58 Sep 13 2019 bin drwxr-xr-x 3 7161 31415 21 Sep 13 2019 lib drwxr-xr-x 3 7161 31415 21 Sep 13 2019 share
添加环境变量
[root@localhost mysqlsh]# vim /etc/profile export PATH=/usr/local/mysql57/bin:/usr/local/mysql-sh/bin:$PATH [root@localhost mysqlsh]# . /etc/profile
使用mysqlsh检查版本兼容情况
原版本授权用户
mysql> grant all on *.* to root@'10.0.0.%' identified by '1234'; Query OK, 0 rows affected, 1 warning (0.00 sec)
开始检测
[root@localhost mysqlsh]# mysqlsh root:1234@10.0.0.10:3306 -e "util.checkForServerUpgrade()" > /tmp/upgrade.txt WARNING: Using a password on the command line interface can be insecure.
查看结果
[root@localhost mysqlsh]# less /tmp/upgrade.txt The MySQL server at 10.0.0.10:3306, version 5.7.26-log - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 8.0.18... 1) Usage of old temporal type No issues found 2) Usage of db objects with names conflicting with new reserved keywords No issues found 3) Usage of utf8mb3 charset No issues found 4) Table names in the mysql schema conflicting with new tables in 8.0 No issues found 5) Partitioned tables using engines with non native partitioning No issues found 6) Foreign key constraint names longer than 64 characters No issues found 7) Usage of obsolete MAXDB sql_mode flag No issues found 8) Usage of obsolete sql_mode flags No issues found 9) ENUM/SET column definitions containing elements longer than 255 characters No issues found 10) Usage of partitioned tables in shared tablespaces No issues found 11) Circular directory references in tablespace data file paths No issues found 12) Usage of removed functions No issues found 13) Usage of removed GROUP BY ASC/DESC syntax No issues found 14) Removed system variables for error logging to the system log configuration To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary More information: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging 15) Removed system variables To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary More information: https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed ...skipping... https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed 16) System variables with new default values To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary More information: https://mysqlserverteam.com/new-defaults-in-mysql-8-0/ 17) Schema inconsistencies resulting from file removal or corruption No issues found 18) Issues reported by 'check table x for upgrade' command No issues found 19) New default authentication plugin considerations Warning: The new default authentication plugin 'caching_sha2_password' offers more secure password hashing than previously used 'mysql_native_password' (and consequent improved client connection authentication). However, it also has compatibility implications that may affect existing MySQL installations. If your MySQL installation must serve pre-8.0 clients and you encounter compatibility issues after upgrading, the simplest way to address those issues is to reconfigure the server to revert to the previous default authentication plugin (mysql_native_password). For example, use these lines in the server option file: [mysqld] default_authentication_plugin=mysql_native_password However, the setting should be viewed as temporary, not as a long term or permanent solution, because it causes new accounts created with the setting in effect to forego the improved authentication security. If you are using replication please take time to understand how the authentication plugin changes may impact you. More information: https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication Errors: 0 Warnings: 1 Notices: 0 No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.
No issues found和Warning可以忽略
修改配置文件
本质就是修改环境变量
[root@localhost mysqlsh]# vim /etc/my.cnf [mysqld] user=mysql #basedir=/usr/local/mysql57 basedir=/usr/local/mysql8 # 修改程序路径 datadir=/data/mysql/data_3306 socket=/tmp/mysql.sock log_bin=/data/mysql/binlog_3306 port=3306 server_id=5 [mysql] socket=/tmp/mysql.sock user=root password=123456
修改环境变量
[root@localhost mysqlsh]# vim /etc/profile export PATH=/usr/local/mysql8/bin:/usr/local/mysql-sh/bin:$PATH [root@localhost mysqlsh]# . /etc/profile
关闭数据库并备份数据（可以物理备份，也可以逻辑备份）
[root@localhost mysqlsh]# ss -lntp State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* users:(("sshd",pid=740,fd=3)) LISTEN 0 128 :::22 :::* users:(("sshd",pid=740,fd=4))
[root@localhost mysqlsh]# cp /data/mysql/data_3306{,.bak} -r
注意：一旦升级了，很难回退，即使使用逻辑备份，也只能恢复业务表，不能恢复系统表
拷贝启动脚本
[root@localhost mysqlsh]# cp /usr/local/mysql8/support-files/mysql.server /etc/init.d/mysqld
cp: overwrite ‘/etc/init.d/mysqld’? y
启动数据库
[root@localhost mysqlsh]# /etc/init.d/mysqld start Starting MySQL......... SUCCESS! 2020-05-23T10:01:01.128251Z 0 [System] [MY-010116] [Server] /usr/local/mysql8/bin/mysqld (mysqld 8.0.18) starting as process 20616 2020-05-23T10:01:01.182272Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory. 2020-05-23T10:01:03.591196Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data. 2020-05-23T10:01:04.032546Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80018' started. 2020-05-23T10:01:07.110049Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80018' completed. 2020-05-23T10:01:08.073459Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2020-05-23T10:01:08.521931Z 0 [System] [MY-010931] [Server] /usr/local/mysql8/bin/mysqld: ready for connections. Version: '8.0.18' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server - GPL. 2020-05-23T10:01:08.651638Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060
查看结果
[root@localhost mysqlsh]# ss -lntp State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* users:(("sshd",pid=740,fd=3)) LISTEN 0 128 :::22 :::* users:(("sshd",pid=740,fd=4)) LISTEN 0 70 :::33060 :::* users:(("mysqld",pid=20616,fd=32)) LISTEN 0 128 :::3306 :::* users:(("mysqld",pid=20616,fd=26))
登录查看
[root@localhost mysqlsh]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.18 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.
查看升级后数据目录结构变化
8.0的数据目录结构
[root@localhost mysqlsh]# ll /data/mysql/data_3306 total 175176 -rw-r----- 1 mysql mysql 56 May 23 05:10 auto.cnf -rw------- 1 mysql mysql 1676 May 23 06:01 ca-key.pem -rw-r--r-- 1 mysql mysql 1112 May 23 06:01 ca.pem -rw-r--r-- 1 mysql mysql 1112 May 23 06:01 client-cert.pem -rw------- 1 mysql mysql 1676 May 23 06:01 client-key.pem -rw-r----- 1 mysql mysql 8811 May 23 06:01 db.err -rw-r----- 1 mysql mysql 6 May 23 06:01 db.pid -rw-r----- 1 mysql mysql 476 May 23 05:58 ib_buffer_pool -rw-r----- 1 mysql mysql 12582912 May 23 06:01 ibdata1 -rw-r----- 1 mysql mysql 50331648 May 23 06:01 ib_logfile0 -rw-r----- 1 mysql mysql 50331648 May 23 06:01 ib_logfile1 -rw-r----- 1 mysql mysql 12582912 May 23 06:01 ibtmp1 drwxr-x--- 2 mysql mysql 187 May 23 06:01 #innodb_temp drwxr-x--- 2 mysql mysql 143 May 23 06:01 mysql -rw-r----- 1 mysql mysql 28311552 May 23 06:01 mysql.ibd -rw-r----- 1 mysql mysql 6 May 23 06:01 mysql_upgrade_info drwxr-x--- 2 mysql mysql 8192 May 23 06:01 performance_schema -rw------- 1 mysql mysql 1680 May 23 06:01 private_key.pem -rw-r--r-- 1 mysql mysql 452 May 23 06:01 public_key.pem -rw-r--r-- 1 mysql mysql 1112 May 23 06:01 server-cert.pem -rw------- 1 mysql mysql 1680 May 23 06:01 server-key.pem drwxr-x--- 2 mysql mysql 28 May 23 06:01 sys -rw-r----- 1 mysql mysql 12582912 May 23 06:01 undo_001 -rw-r----- 1 mysql mysql 12582912 May 23 06:01 undo_002
5.7的数据目录结构
[root@localhost mysqlsh]# ll /data/mysql/data_3306.bak/ total 122924 -rw-r----- 1 root root 56 May 23 05:55 auto.cnf -rw-r----- 1 root root 3518 May 23 05:55 db.err -rw-r----- 1 root root 6 May 23 05:55 db.pid -rw-r----- 1 root root 419 May 23 05:55 ib_buffer_pool -rw-r----- 1 root root 12582912 May 23 05:55 ibdata1 -rw-r----- 1 root root 50331648 May 23 05:55 ib_logfile0 -rw-r----- 1 root root 50331648 May 23 05:55 ib_logfile1 -rw-r----- 1 root root 12582912 May 23 05:55 ibtmp1 drwxr-x--- 2 root root 4096 May 23 05:55 mysql drwxr-x--- 2 root root 8192 May 23 05:55 performance_schema drwxr-x--- 2 root root 8192 May 23 05:55 sys
posted on 2020-05-23 18:09 hopeless-dream 阅读(2829) 评论(0) 编辑 收藏 举报
