win10下安装mysql5.7

 

1.下载mysql5.7
我这里下载的是mysql-5.7.32-winx64

2.解压文件夹
命名为:
D:\soft\mysql57_host01

3.创建相应的目录
D:\soft\mysql57_host01\conf
D:\soft\mysql57_host01\data
D:\soft\mysql57_host01\redolog
D:\soft\mysql57_host01\mysqllog\binlog
D:\soft\mysql57_host01\mysqllog\logfile
D:\soft\mysql57_host01\mysqllog\relaylog

4.将mysql bin目录添加到环境变量

5.初始化
C:\Users\Administrator>mysqld --initialize --console --user=mysql --basedir=D:\soft\mysql57_host01 --datadir=D:\soft\mysql57_host01\data --lc_messages_dir=D:\soft\mysql57_host01\share --lc_messages=en_US
2020-11-08T12:00:03.328306Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-11-08T12:00:03.501582Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-11-08T12:00:03.526135Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-11-08T12:00:03.622361Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: ef87536e-21b9-11eb-978b-0a0027000002.
2020-11-08T12:00:03.625501Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-11-08T12:00:05.325778Z 0 [Warning] CA certificate ca.pem is self signed.
2020-11-08T12:00:05.975542Z 1 [Note] A temporary password is generated for root@localhost: Q/x_8Riq<KUW

6.编辑配置文件

文件名:D:\soft\mysql57_host01\conf\my.ini
内容如下:

[mysqld]
port=3306
server-id=1
basedir=D:\\soft\mysql57_host01
datadir=D:\\soft\mysql57_host01\\data
max_connections = 1500
character_set_server=utf8mb4
skip-external-locking
key_buffer_size=64M
max_allowed_packet=32M
myisam_sort_buffer_size=64M
query_cache_size=32M
read_buffer_size=2M
sort_buffer_size=2M
#table_cache=512
#thread_cache=20
#thread_concurrency=4
interactive_timeout=86400
wait_timeout=86400
innodb_file_per_table=1
#innodb_additional_mem_pool_size=16M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout=50
innodb_log_buffer_size=128M
innodb_log_file_size=128M
innodb_log_files_in_group=5
innodb_log_group_home_dir=D:\\soft\\mysql57_host01\\redolog
innodb_thread_concurrency=8
log_bin_trust_function_creators=1
event_scheduler=1
max_binlog_size=100M
log-bin=D:\\soft\\mysql57_host01\\mysqllog\\binlog\\binlog.bin
slow_query_log=on
slow_query_log_file=D:\\soft\\mysql57_host01\\mysqllog\\logfile\\low-query.log
long_query_time=1
log-error=D:\\soft\\mysql57_host01\\mysqllog\\logfile\\mysql-err.log
binlog_format=row
##expire_logs_days=7
binlog_cache_size=4MB
skip-host-cache
#skip-name-resolve
#read-only
skip-slave-start
relay-log-index=D:\\soft\\mysql57_host01\\mysqllog\\relaylog\\slave-relay-bin.index
relay-log=D:\\soft\\mysql57_host01\\mysqllog\\relaylog\\relaylog-binlog
replicate-ignore-db=information_schema,performance_schema
slave_net_timeout=60
log_slave_updates=1
language=D:\\soft\\mysql57_host01\\share\\english
early-plugin-load=""
explicit_defaults_for_timestamp=true
gtid_mode=ON
enforce_gtid_consistency = ON
collation-server=utf8mb4_general_ci
init_connect='SET collation_connection = utf8mb4_general_ci'
init_connect='SET NAMES utf8mb4'
lower_case_table_names=1
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

[client]
port = 3306
socket=D:\\soft\mysql57_host01\\mysql.sock
default-character-set = utf8

[mysqldump]
quick
max_allowed_packet = 32M
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

 

 

7.安装mysql服务并启动
C:\Users\Administrator>mysqld.exe install MySQL --defaults-file="D:\soft\mysql57_host01\conf\my.ini"
Service successfully installed.

C:\Users\Administrator>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

8.登录修改root密码
C:\Users\Administrator>mysql -h localhost -uroot -p
Enter password: ************

mysql> set password=password('mysql');

然后进行具体的修改

update mysql.user set authentication_string=password('mysql') where user='root' and host = 'localhost';
flush privileges; 

 

9.创建开发用户
创建登陆用户
grant ALL on *.* to root@'%' identified by 'mysql' WITH GRANT OPTION;

 

 

遇到的问题:
D:\mysql57\bin>mysql -h localhost -uroot -p
Enter password: ************
ERROR 1130 (HY000): Host '::1' is not allowed to connect to this MySQL server
该问题是因为在mysql.user表中没有::1的条目

 

1.修改配置文件,加入如下配置跳过授权表
[mysqld]
skip-grant-tables

 

然后重启数据库,无密码登录，执行如下脚本
update mysql.user set host='%' where user='root'; ##%不限制主机

 

2.注释掉授权表
[mysqld]
##skip-grant-tables
然后重启数据库，登录数据库(使用初始化安装时的密码)
mysql -h localhost -uroot -p

 

修改密码
set password=password('mysql');

 

3.添加::1栏目和去掉%
grant ALL PRIVILEGES on *.* to 'root'@'::1' identified by 'mysql' WITH GRANT OPTION; ##添加::1
update mysql.user set host='localhost' where user='root' and host='%'; ##去掉不做主机限制的栏目