目录

一、备份与导出数据

二、升级到MySQL-8.0.22同时解决字符集问题

2.1、卸载MySQL-5.7.32

2.2、安装MySQL-8.0.22

2.3、修改配置文件 my.cnf

三、导入数据及配置

3.1、重新创建数据库wordpress

3.2、导入表结构和数据

3.3、启动httpd

今天，贫僧的个人博客https://www.stackoperator.top上线了，以后会逐渐和本博客保持同步更新。顺便记录一下，个人博客MySQL数据库的升级过程。

背景：个人博客跑在MySQL 5.7.32 + Wordpress 5.5.3环境上，将数据库升级到MySQL 8.0.22，同时将字符集转换为utf8mb4。

一、备份与导出数据

停止应用，防止数据写入。备份wordpress目录。将数据库wordpress分别导出表结构和数据。

systemctl stop httpd
tar cvzf /tmp/wordpress_bak.tar.gz wordpress/*
mysqldump -uroot -p --no-data --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF --databases wordpress > /tmp/wordpress.sql
mysqldump -uroot -p --no-create-info --master-data=2 --flush-logs --routines --events --triggers --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF --databases wordpress > /tmp/wordpress_data.sql

把表结构wordpress.sql复制一份，把里面的字符集全部修改为utf8mb4。

cp /tmp/wordpress.sql /tmp/wordpress_utf8mb4.sql
vim /tmp/wordpress_utf8mb4.sql
# :%s/utf8/utf8mb4/g 如果已有utf8mb4会被替换成utf8mb4mb4，注意别替换错了。

二、升级到MySQL-8.0.22同时解决字符集问题

2.1、卸载MySQL-5.7.32

查看目前安装了mysql的版本，全部卸载，删除/var/lib/mysql目录。

yum list installed | grep mysql
......
mysql-community-client.x86_64         5.7.32-1.el7                   @mysql57-community
mysql-community-common.x86_64         5.7.32-1.el7                   @mysql57-community
mysql-community-libs.x86_64           5.7.32-1.el7                   @mysql57-community
mysql-community-libs-compat.x86_64    5.7.32-1.el7                   @mysql57-community
mysql-community-server.x86_64         5.7.32-1.el7                   @mysql57-community
mysql80-community-release.noarch      el7-3                          @/mysql80-community-release-el7-3.noarch
......
yum remove mysql-community-client.x86_64 mysql-community-common.x86_64 mysql-community-libs.x86_64 mysql-community-libs-compat.x86_64 mysql-community-server.x86_64 mysql80-community-release.noarch
rm /var/lib/mysql/ -rf

2.2、安装MySQL-8.0.22

更新yum源，启用mysql80-community，安装MySQL-8.0.22。

cd /etc/yum.repos.d/
mv mysql-community.repo.rpmsave mysql-community.repovim mysql-community.repo
# [mysql80-community]
# name=MySQL 8.0 Community Server
# baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch/
# enabled=1
# gpgcheck=1
# gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysqlyum clean all
yum makecache
yum install mysql-community-server

2.3、修改配置文件 my.cnf

备份/etc/my.cnf后可以修改。

binary log部分。据官方手册17.1.6.4 Binary Logging Options and Variables，binary log可以不做修改。

• mysql-8.0.x以后，不论是否制定log-bin参数，binary log都默认开启，放在数据文件目录。
• mysql-8.0.x以后，如果不指定log-bin参数，binary log文件名binlog.<numeric>。如果指定了log-bin参数，那么binary log文件名和以前版本一样<hostname>-bin.<numeric>。
• mysql-8.0.x以后，可以用skip-log-bin或者disable-log-bin禁用binary log。
• mysql-8.0.x以后，如果log-bin、skip-log-bin或者disable-log-bin同时存在，那么以最后一条配置为准生效。

17.1.6.4 Binary Logging Options and Variables

Specifies the base name to use for binary log files. With binary logging enabled, the server logs all statements that change data to the binary log, which is used for backup and replication. The binary log is a sequence of files with a base name and numeric extension. The --log-bin option value is the base name for the log sequence. The server creates binary log files in sequence by adding a numeric suffix to the base name.

If you do not supply the --log-bin option, MySQL uses binlog as the default base name for the binary log files. For compatibility with earlier releases, if you supply the --log-bin option with no string or with an empty string, the base name defaults to host_name-bin, using the name of the host machine.

The default location for binary log files is the data directory. You can use the --log-bin option to specify an alternative location, by adding a leading absolute path name to the base name to specify a different directory. When the server reads an entry from the binary log index file, which tracks the binary log files that have been used, it checks whether the entry contains a relative path. If it does, the relative part of the path is replaced with the absolute path set using the --log-bin option. An absolute path recorded in the binary log index file remains unchanged; in such a case, the index file must be edited manually to enable a new path or paths to be used. The binary log file base name and any specified path are available as the log_bin_basename system variable.

In earlier MySQL versions, binary logging was disabled by default, and was enabled if you specified the --log-bin option. From MySQL 8.0, binary logging is enabled by default, whether or not you specify the --log-bin option. The exception is if you use mysqld to initialize the data directory manually by invoking it with the --initialize or --initialize-insecure option, when binary logging is disabled by default. It is possible to enable binary logging in this case by specifying the --log-bin option. When binary logging is enabled, the log_bin system variable, which shows the status of binary logging on the server, is set to ON.

To disable binary logging, you can specify the --skip-log-bin or --disable-log-bin option at startup. If either of these options is specified and --log-bin is also specified, the option specified later takes precedence. When binary logging is disabled, the log_bin system variable is set to OFF.

字符集部分。据官方手册10.3.2 Server Character Set and Collation，默认字符集是utf8mb4。为了防止开发连接时指定字符集，保证字符集编码的统一，我们设置忽略连接自己的设置，与全局保持一致。

10.3.2 Server Character Set and Collation

MySQL Server has a server character set and a server collation. By default, these are utf8mb4 and utf8mb4_unicode_ci, but they can be set explicitly at server startup on the command line or in an option file and changed at runtime.

启动mysql数据库，在 /var/log/mysqld.log里面找到默认的root密码，并执行安全安装脚本：修改root密码、移除匿名用户、禁止root远程访问、移除测试库。

cp /etc/my.cnf /etc/my.cnf.bak20201212
vim /etc/my.cnf## mysql-8.0.x默认开启binary log，不需要log-bin参数，文件名binlog.<numeric>## mysql-8.0.x默认字符集utf8mb4，这里字符集设置统一utf8mb4#连接建立时执行设置的语句，对super权限用户无效
init_connect='SET NAMES utf8mb4'#设置服务端校验规则，如果字符串需要区分大小写，设置为utf8mb4_bin
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci#忽略应用连接自己设置的字符编码，保持与全局设置一致
skip-character-set-client-handshakesystemctl start mysqld.service
grep "temporary password" /var/log/mysqld.log
mysql_secure_installationmysql -uroot -p
mysql> show variables like '%char%set%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8                           |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.01 sec)mysql> show variables like '%collation%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_general_ci |
| collation_database            | utf8mb4_general_ci |
| collation_server              | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_unicode_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.00 sec)

网上一些人人云亦云，这是错误的！说什么要设置innodb_file_format=Barracuda、innodb_file_format_max=Barracuda、innodb_file_per_table=1、innodb_large_prefix=ON，这是错误的，会导致数据库不能启动。这些参数已经被移除了，不要添加。

Options and Variables Removed in MySQL 8.0

• innodb_file_format: Format for new InnoDB tables. Removed in MySQL 8.0.0.

• innodb_file_format_check: Whether InnoDB performs file format compatibility checking. Removed in MySQL 8.0.0.

• innodb_file_format_max: File format tag in shared tablespace. Removed in MySQL 8.0.0.

• innodb_large_prefix: Enables longer keys for column prefix indexes. Removed in MySQL 8.0.0.

三、导入数据及配置

3.1、重新创建数据库wordpress

根据官方手册1.3 What Is New in MySQL 8.0，mysql-8.0.x以后，授权只能授权，不能同时创建用户、修改用户属性、设置密码，因此这里建库有点不同。

The following features related to account management are removed:

• Using GRANT to create users. Instead, use CREATE USER. Following this practice makes the NO_AUTO_CREATE_USER SQL mode immaterial for GRANT statements, so it too is removed, and an error now is written to the server log when the presence of this value for the sql_mode option in the options file prevents mysqld from starting.

  
  

• Using GRANT to modify account properties other than privilege assignments. This includes authentication, SSL, and resource-limit properties. Instead, establish such properties at account-creation time with CREATE USER or modify them afterward with ALTER USER.

• IDENTIFIED BY PASSWORD 'auth_string' syntax for CREATE USER and GRANT. Instead, use IDENTIFIED WITH auth_plugin AS 'auth_string' for CREATE USER and ALTER USER, where the 'auth_string' value is in a format compatible with the named plugin.

  
  

  Additionally, because IDENTIFIED BY PASSWORD syntax was removed, the log_builtin_as_identified_by_password system variable is superfluous and was removed.

  
  

• The PASSWORD() function. Additionally, PASSWORD() removal means that SET PASSWORD ... = PASSWORD('auth_string') syntax is no longer available.

  
  

• The old_passwords system variable.

$ mysql -uroot -p
mysql> CREATE DATABASE wordpress;
mysql> CREATE USER 'wpuser'@'localhost' IDENTIFIED BY <password>;
mysql> GRANT ALL PRIVILEGES ON wordpress.* TO 'wpuser'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> EXIT

3.2、导入表结构和数据

先导入表结构，再导入数据。

mysql -uroot -p wordpress < /tmp/wordpress_utf8mb4.sql
mysql -uroot -p wordpress < /tmp/wordpress_data.sql

3.3、启动httpd

systemctl start httpd

检查一下，完美。