Windows MySQL本地服务器设置并导入数据库和数据

小结

最近需要在本地Windows环境中设置MySQL服务器，并导入数据库和数据，记录过程。

问题及解决

导出数据库

首先需要导出数据库：

C:\mysql-8.0.37-winx64\bin>mysqldump -h192.168.0.1 -P3306 -uroot  -p database_name > data-dump.sql

导出的数据库及数据就是这个data-dump.sql文件。

Windows MySQL本地服务器设置

下载MySQL Windows版本服务器，我这里下载的文件名是： mysql-8.0.37-winx64.zip

1. 解压缩 mysql-8.0.37-winx64.zip，这里使用目录C:\Software\
2. 安装MySQL服务

     C:\mysql-8.0.37-winx64\bin>mysqld --install

3. 初始化MySQL服务器

C:\mysql-8.0.37-winx64\bin>mysqld --console --initialize
2024-05-03T06:17:37.256497Z 0 [System] [MY-013169] [Server] C:\mysql-8.0.37-winx64\bin\mysqld.exe (mysqld 8.0.37) initializing of server in progress as process 18068
2024-05-03T06:17:37.289381Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-05-03T06:17:37.658838Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-05-03T06:17:40.311602Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: YorFd5-c1loa

这里会生成一个临时的密码

4. 修改MySQL服务器root密码
   创建一个change_mysql_pwd.txt文件放在c盘，内容为：

ALTER USER 'root'@'localhost' IDENTIFIED BY '你的新密码';

重新启动一个命令行，并执行以下指令：

C:\mysql-8.0.37-winx64\bin>mysqld --console --init-file=C:\\change_mysql_pwd.txt
2024-05-03T06:19:29.327039Z 0 [System] [MY-010116] [Server] C:\mysql-8.0.37-winx64\bin\mysqld.exe (mysqld 8.0.37) starting as process 15364
2024-05-03T06:19:29.356395Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-05-03T06:19:29.653146Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-05-03T06:19:30.038645Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-05-03T06:19:30.038884Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-05-03T06:19:30.103250Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060
2024-05-03T06:19:30.103413Z 0 [System] [MY-010931] [Server] C:\mysql-8.0.37-winx64\bin\mysqld.exe: ready for connections. Version: '8.0.37'  socket: ''  port: 3306  MySQL Community Server - GPL.

以上密码修改完成。

以上是把修改密码的指令存放在文件change_mysql_pwd.txt中，也可以使用生成的临时密码进行登录，在MySQL里面执行MySQL指令修改密码。

5. 使用修改MySQL服务器root后的新密码进行登录测试。

导入数据库和数据

首先创建一个数据库，名为database_name ，再导入数据库和数据使用以下指令：

C:\mysql-8.0.37-winx64\bin>mysql -uroot -p database_name < data-dump.sql
Enter password: ********

碰到的问题，ERROR 3546 (HY000) at line 24: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

使用以下指令后，再重试，可以解决：

RESET MASTER

碰到问题，ERROR 3185 (HY000) at line 25: Can't find master key from keyring, please check in the server log if a keyring plugin is loaded and initialized successfully.， 对data-dump.sql文件进行以下修改可以解决问题：

删除文件中所有的ENCRYPTION='Y'，也就是不加密。

参考

MySQL Community Downloads
MySQL: 6.5.2 SQL Data Export and Import Wizard
Reset MySQL 8.0 root Password in Windows
stackoverflow: Failed to find valid data directory. MySQL generic binary installion
Digital Ocean: How To Import and Export Databases in MySQL or MariaDB
MySQL: 13.4.1.2 RESET MASTER Statement
stackoverflow: Cloud sql database export import issue
stackoverflow: How can I import an encrypted database in MYSQL?