MySQL 5.7安装及版本升级到8.0

官方文档

支持的系统和架构

https://www.mysql.com/support/supportedplatforms/database.html

https://www.mysql.com/support/eol-notice.html

Operating System	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	•	•	•
Fedora Yum Repo	•	•	•
Debian/Ubuntu APT Repo	•	•	•
SUSE Repo	•	•	•

确认安装的版本

准备安装MySQL时，请确定要使用哪个版本和发行格式（二进制或源码）。

首先，决定要安装开发版本还是通用版本（GA）。开发版本具有最新功能，但不建议用于生产环境。 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.

[root@localhost mysqlsh]# less /tmp/upgrade.txt

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 阅读(2787) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

hopeless-dream

导航

公告

MySQL 5.7安装及版本升级到8.0

官方文档

确认安装的版本

安装

安装依赖

卸载mariadb的库

添加用户和组

创建数据目录等

上传软件包、校验和解压

创建软连接和添加环境变量

查看mysql版本

授权

初始化

不安全的方式（本例选此种方式初始化，也可用于生产环境）

安全的方式（本例不采用，只做演示）

编辑配置文件

注意：server_id必须加，要不起不来，报错Starting MySQL. ERROR! The server quit without updating PID file (/data/mysql/data_3306/db.pid).

启动mysql

添加启动项

版本升级

5.7-8.0版本参数的变化

上传并解压

查看目录结构

添加环境变量

使用mysqlsh检查版本兼容情况

原版本授权用户

开始检测

查看结果

修改配置文件

修改环境变量

关闭数据库并备份数据（可以物理备份，也可以逻辑备份）

拷贝启动脚本

启动数据库

查看结果

登录查看

查看升级后数据目录结构变化

8.0的数据目录结构

5.7的数据目录结构