半步多 玄玉的博客

Windows安装MySQL

2016-07-22
玄玉

关于 CentOS-6.4-minimal 上面通过源码来安装 MySQL-5.5.38,传送门在此:CentOS安装MySQL

Windows 上安装完 MySQL,以前都会有一个配置向导工具,一路点下去就可以了

今天安装完 mysql-5.7.14.msi 发现:配置向导不提供了,要我们手工修改配置文件

下面来详细介绍一下操作过程

下载

官网下载社区版的页面是:http://dev.mysql.com/downloads/mysql/

这里 5.7.14 的安装包有300多MB,有点太大了(里面包含了一堆令人不感冒的附加管理工具)

其实 5.6.X 开始,官方就不再明确提供 msi 的下载地址了

我们可以看到 Looking for previous GA versions 里只提供了 5.5.X 这种容量要小很多的 msi 下载

现在,到了脑洞大开的时候了

官网提供的 5.5.X 下载地址为:http://cdn.mysql.com/Downloads/MySQL-5.5/mysql-5.5.50-winx64.msi

依此类推

mysql-5.6.32-winx64.msi(37.0MB):http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.32-winx64.msi

mysql-5.7.14-winx64.msi(95.2MB):http://cdn.mysql.com/Downloads/MySQL-5.7/mysql-5.7.14-winx64.msi

mysql-8.0.13-winx64.msi(106MB):http://cdn.mysql.com/Downloads/MySQL-8.0/mysql-8.0.13-winx64.msi

安装

下面以 mysql-8.0.13-winx64.msi 为例:双击安装包后,一路Next 下去(注意安装路径)

配置

这是在我的电脑上,安装后的目录结构(以前的图片)

配置my.ini

先备份 my-default.ini,再重命名为 my.ini(mysql-8.0.13-winx64安装后没有my-default.ini,那么直接新建my.ini就可以了)

下面是 my-default.ini 的内容

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....


# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

下面是 my.ini 的内容

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
# skip-grant-tables
port=3306
# 设置mysql的安装目录
basedir=D:\\Develop\\MySQL\\MySQLServer80
# 设置mysql数据库的数据的存放目录(data目录在下面初始化时会自动创建,不需要我们手动创建)
datadir=D:\\Develop\\MySQL\\MySQLServer80\\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数(防止有人从该主机试图攻击数据库系统)
max_connect_errors=10
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# mysql-8.0.4开始,其密码认证插件就由mysql_native_password改为了caching_sha2_password,而很多数据库工具和链接包暂时还不支持caching_sha2_password
default_authentication_plugin=mysql_native_password

设置环境变量

# 计算机---属性---高级系统设置---环境变量---系统变量
MYSQL_HOME=D:\Develop\MySQL\MySQLServer80
path=%MYSQL_HOME%\bin;......

初始化data目录

管理员身份运行CMD

C:\Users\Jadyer>mysqld --initialize --console(注意:两个横杠)
2018-10-24T03:06:20.534951Z 0 [System] [MY-013169] [Server] D:\Develop\MySQL\MySQLServer80\bin\mysqld.exe (mysqld 8.0.13) initializing of server in progress as process 9812
2018-10-24T03:06:20.538000Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2018-10-24T03:06:23.423916Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: lMiZMiu*e43:
2018-10-24T03:06:24.774539Z 0 [System] [MY-013170] [Server] D:\Develop\MySQL\MySQLServer80\bin\mysqld.exe (mysqld 8.0.13) initializing of server has completed

C:\Users\Jadyer>

执行完成后,会打印 root 用户的初始默认密码,也就是上面的 lMiZMiu*e43:(后面修改Root密码时会用到它)

安装MySQL服务

管理员身份运行CMD:C:\Users\Jadyer>mysqld install,安装成功会提示 Service successfully installed.

启动的话,可以运行 services.msc 手工启动,也可以:C:\Users\Jadyer>net start mysql

修改Root密码(MySQL-8.0.13)

管理员身份运行CMD

C:\Users\hongyu.lu>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.13

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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> use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> set PASSWORD = PASSWORD("xuanyu");
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 'PASSWORD("xuanyu")' at line 1
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'xuanyu';
Query OK, 0 rows affected (0.01 sec)

mysql> use mysql;
Database changed
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> SELECT user, host, plugin, authentication_string FROM mysql.user;
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
| user             | host      | plugin                | authentication_string                                                  |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | mysql_native_password | *E1CD6F8619B9BFA1EB9DBA67D110FCEEBA4B08C2                              |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'root'@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                                                                                                                                                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION   |
| GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION                                                                                                    |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                             |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

至此,MySQL-8.0.13 配置完毕

修改Root密码(MySQL-5.7.14)

首次安装后,修改 Root 密码时,会报告下面的错误

注:下面在操作时,直接回车,即视为输入空密码(因为后面要手工修改root密码,所以这里空密码就行)

D:\Develop\MySQL\MySQLServer5714\bin>mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

这时需要在 my.ini[mysqld] 条目下增加配置:skip-grant-tables,并重启 MySQL

然后再继续修改,如下所示(注意里面执行的几个SQL命令)

D:\Develop\MySQL\MySQLServer5714\bin>mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

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> use mysql
Database changed
mysql> update user set authentication_string=password("xuanyu") where user="root";
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

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

mysql> quit;
Bye

修改完,再把刚才 my.ini 中增加的 skip-grant-tables 配置去掉,再重启 MySQL

这个时候用新密码可以登录了

但在执行诸如 show databases 或者 use mysql 等命令时,会看到下面的提示错误

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> use mysql
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

解决办法是:重新设置一下密码(这里设置的新密码可以与上面设置的root密码相同)

mysql> set PASSWORD = PASSWORD("xuanyu");
Query OK, 0 rows affected, 1 warning (0.00 sec)

然后用刚才新设置的密码,重新登录,就可以执行 SQL 指令了

至此,MySQL-5.7.14 配置完毕


Comments

Content