windows10 安装多个mysql8.x及多个实例启动目录问题

下载mysql：https://dev.mysql.com/downloads/mysql/

下载后解压到你指定的目录，并复制3份

在每个一文件夹下面分别建一个my.ini文件

[client]
port=33061
default-character-set=utf8mb4
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[mysqld]
# 设置3306端口
port=33061
server_id=1000
# 设置mysql的安装目录
basedir=D:\datastorage\mysql\mysql1
# 设置mysql数据库的数据的存放目录
datadir=D:\datastorage\mysql\mysql1\data
# 允许最大连接数 
max_connections=1024
# 服务端使用的字符集默认为8比特编码的latin1字符集
character_set_server=utf8mb4
# 创建新表时将使用的默认存储引擎 
default-storage-engine=INNODB
# sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER
# 默认使用mysql_native_password插件认证
default_authentication_plugin=mysql_native_password

[WinMySQLAdmin]
Server=D:\datastorage\mysql\mysql1\bin\mysqld.exe

其它目录ini文件仅修改port，server_id，basedir，datadir即可。

用管理员方式运行CMD进入mysql的bin目录：

C:\Windows\system32>d:

D:\> cd datastorage\mysql\mysql3\bin

初始化，成功会有密码，先记住（标红部分）：

D:\datastorage\mysql\mysql3\bin>mysqld --defaults-file=D:\datastorage\mysql\mysql3\my.ini --initialize --console
2021-10-14T01:41:17.970550Z 0 [System] [MY-013169] [Server] D:\datastorage\mysql\mysql3\bin\mysqld.exe (mysqld 8.0.26) initializing of server in progress as process 6868
2021-10-14T01:41:18.321342Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-10-14T01:41:24.711069Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-10-14T01:41:38.134971Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2021-10-14T01:41:38.135667Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2021-10-14T01:41:38.571640Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: eR%4XKENe;fq

安装服务：

D:\datastorage\mysql\mysql3\bin>mysqld --install mysql_slave2 --defaults-file=D:\datastorage\mysql\mysql3\my.ini
Service successfully installed.

到此服务安装完成，但是由于我得机器之前安装mysql，导致服务启动失败：

 输入regedit进入注册表：

进入之后：

修改mysql路径，编辑ImagePath：

修改之后再回到cmd窗口，执行：

D:\datastorage\mysql\mysql1\bin>net start mysql_master
mysql_master 服务正在启动 .......
mysql_master 服务已经启动成功。

 服务可以正常启动了，在服务里查看：

使用临时密码（即上面初始化红色部分）登录mysql：

D:\datastorage\mysql\mysql1\bin>mysql -uroot -p
Enter password: ************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

修改密码：

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.06 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql> exit;
Bye

修改密码后使用修改后密码重新登录：

D:\datastorage\mysql\mysql1\bin>mysql -uroot -p
Enter password: ***********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 远程访问：

mysql> create user 'brady'@'%' identified by 'brady';

Query OK, 0 rows affected (0.02 sec)

mysql> grant all privileges on *.*  to  'brady'@'%'  identified by 'brady'  with grant option;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'brady'  with grant option' at line 1

mysql> GRANT ALL PRIVILEGES ON *.* TO 'brady'@'%';

Query OK, 0 rows affected (0.04 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'brady'@'%' IDENTIFIED WITH mysql_native_password BY 'brady';

Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

如果用Navicat连接的回报不支持caching_sha2_password验证方式的错误。

将远程访问用户的验证方式改为： msyql_native_password

执行：

ALTER USER 'username'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

FLUSH PRIVILEGES;