Windows下的MySQL升级

发表于 更新于 分类于 ，

关于将MySQL从5.x升级到8.0.16的过程记录。

下载新的mysql

https://downloads.mysql.com/archives/community/

下载后解压

创建my.ini

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
[mysqld]
default_authentication_plugin=mysql_native_password
log-bin-trust-function-creators=1
binlog_expire_logs_seconds =604800
innodb_file_per_table
innodb_flush_log_at_trx_commit = 2
lower_case_table_names=1
group_concat_max_len=1M
log_output=table,file
long_query_time = 2
port=3322
# set basedir to your installation path
basedir=D:/xxx/mysql_new_13312
# set datadir to the location of your data directory
datadir=D:/xxx/mysql_new_13312/data

character_set_server=utf8mb4
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
connect_timeout=600
wait_timeout=600
ft_min_word_len=1
innodb_ft_min_token_size=1
ngram_token_size=1
sql_mode=
[mysql]
default-character-set=utf8mb4
[client]
default-character-set=utf8mb4

初始化

1
2
3
4
5
6
7
8
9
10
PS D:\xxx\mysql_new_13312\bin> .\mysqld.exe --initialize --console
2022-01-04T07:16:04.156544Z 0 [System] [MY-013169] [Server] D:\xxx\mysql_new_13312\bin\mysqld.exe (mysqld 8.0.16) initializing of server in progress as process 23960
2022-01-04T07:16:05.332304Z 0 [Warning] [MY-010161] [Server] You need to use --log-bin to make --binlog-expire-logs-seconds work.
2022-01-04T07:16:06.675688Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: )(Qg.huCD2fw
2022-01-04T07:16:07.316226Z 0 [System] [MY-013170] [Server] D:\xxx\mysql_new_13312\bin\mysqld.exe (mysqld 8.0.16) initializing of server has completed
PS D:\xxx\mysql_new_13312\bin> .\mysqld.exe --log-bin --console
2022-01-04T07:19:04.951572Z 0 [System] [MY-010116] [Server] D:\xxx\mysql_new_13312\bin\mysqld.exe (mysqld 8.0.16) starting as process 29224
2022-01-04T07:19:06.490266Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-01-04T07:19:06.558095Z 0 [System] [MY-010931] [Server] D:\xxx\mysql_new_13312\bin\mysqld.exe: ready for connections. Version: '8.0.16'  socket: ''  port: 3322  MySQL Community Server - GPL.
2022-01-04T07:19:06.626795Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060

数据迁移

导出数据

备份原先5.x的数据，低版本mysql执行mysqldump，

如果是linux环境，则直接使用mysqldump [options] > dump.sql即可。

如果是windows下，需要使用--result_file来指定输出文件，否则无法重新导入。

1
.\mysqldump.exe --opt -E -R -uroot -p db_name --result_file=backup_old_01.sql

查阅官网得知，在 Windows 上使用 PowerShell 制作的带有输出的转储，重定向会创建一个具有 UTF-16 编码的文件，但是MySQL不允许将 UTF-16 作为连接字符集。

查看不允许的客户端字符集

解决这个问题，可以使用--result-file选项，它以 ASCII 格式创建输出： 

1
mysqldump [options] --result-file=dump.sql

导入高版本mysql

1
2
CREATE DATABASE `db_name`
    /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
1
2
3
> mysql.exe -P 3322 -uroot -p
mysql> use db_name
mysql> source D:/path/backup.sql

如果需要的话，改下其他ip可以访问root

1
2
3
4
mysql> use mysql;
mysql> update user set host = '%' where user = 'root';
mysql> commit;
mysql> flush privileges;