in Programming

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指令修改密码。

  1. 使用修改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?

Write a Comment

Comment