目录
今天，贫僧的个人博客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/ -rf2.2、安装MySQL-8.0.22
更新yum源，启用mysql80-community，安装MySQL-8.0.22。
cd /etc/yum.repos.d/
mv mysql-community.repo.rpmsave mysql-community.repo
vim 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-mysql
yum clean all
yum makecache
yum install mysql-community-server2.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-binoption 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-binoption, MySQL usesbinlogas the default base name for the binary log files. For compatibility with earlier releases, if you supply the--log-binoption with no string or with an empty string, the base name defaults to, using the name of the host machine.host_name-binThe default location for binary log files is the data directory. You can use the
--log-binoption 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-binoption. 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-binoption. From MySQL 8.0, binary logging is enabled by default, whether or not you specify the--log-binoption. The exception is if you use mysqld to initialize the data directory manually by invoking it with the--initializeor--initialize-insecureoption, when binary logging is disabled by default. It is possible to enable binary logging in this case by specifying the--log-binoption. 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-binis 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
utf8mb4andutf8mb4_0900_ai_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-handshake
systemctl start mysqld.service
grep "temporary password" /var/log/mysqld.log
mysql_secure_installation
mysql -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_0900_ai_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_USERSQL 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 thesql_modeoption 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 'syntax for CREATE USER and GRANT. Instead, useauth_string'IDENTIFIED WITHfor CREATE USER and ALTER USER, where theauth_pluginAS 'auth_string''value is in a format compatible with the named plugin.auth_string'
Additionally, because
IDENTIFIED BY PASSWORDsyntax was removed, thelog_builtin_as_identified_by_passwordsystem 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_passwordssystem 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> EXIT3.2、导入表结构和数据
先导入表结构，再导入数据。
mysql -uroot -p wordpress < /tmp/wordpress_utf8mb4.sql
mysql -uroot -p wordpress < /tmp/wordpress_data.sql3.3、启动httpd
systemctl start httpd
检查一下，完美。
扫一扫
1920
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
