【mysql】windows解压版mysql安装配置

一、下载地址：

http://dev.mysql.com/downloads/mysql/

二、配置环境变量

1.新增环境变量

变量名：MYSQL_HOME

变量值：E:\software\mysql-5.7.21-winx64（改成你自己的安装目录）

2.修改环境变量PATH

在PATH后面加入%MYSQL_HOME%\bin，注：加入新的变量值需要用;隔开

三、添加my.ini配置文件

mysql8.0版本：

1.下载的压缩文件中没有my.ini配置文件和data文件夹，需要手动在解压目录下新建文本my.ini

[client]
 
port=3306
 
[mysql]
 
default-character-set=UTF8MB4
 
 
# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#
[mysqld]
 
default-time-zone = '+8:00'
 
# The TCP/IP Port the MySQL Server will listen on
port=3306
 
#Path to installation directory. All paths are usually resolved relative to this.
basedir=D:\\develop\\servers\\mysql\\mysql-8.0.30-winx64
 
#Path to the database root
datadir=D:\\develop\\servers\\mysql\\mysql-8.0.30-winx64\\data
 
# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=UTF8MB4
 
# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB
 
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
# Set the SQL mode to strict
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO

[Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.

[Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

mysql5.7版本：

1.下载的压缩文件中没有my.ini配置文件和data文件夹，需要手动在解压目录下新建文本my.ini

[client]

port=3306

[mysql]

default-character-set=UTF8MB4


# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#
[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=3306


#Path to installation directory. All paths are usually resolved relative to this.
basedir=D:\\develop\\software\\mysql-5.7.25-winx64

#Path to the database root
datadir=D:\\develop\\software\\mysql-5.7.25-winx64\\data

# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=UTF8MB4

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

四、初始化mysql，启动mysql服务

mysql8.0版本：

 1.初始化

mysqld --initialize --user=mysql --console

 2.安装mysql服务

mysqld --install --defaults-file="D:\xxxxxxxxxxxxxxxx\my.ini" mysql8
或者
.\mysqld.exe --install mysql8 --defaults-file="D:\xxxxxxxxxxxxxxxx\my.ini"


mysqld --remove mysql8

 3.启动服务

net start mysql8
net stop mysql8

4.修改密码(在上面data目录下找后缀为ERR的文件找初始密码或者命令界面直接会输出显示)

use mysql;

ALTER USER 'root'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '111111';

mysql5.7版本： 

1.以管理员身份运行命令行cmd，进入bin目录(不是蓝色界面的那个，认准黑色界面的那个cmd)

cd D:\mysql\mysql5.7.20-winx64\bin

bin\mysqld --initialize-insecure

待选项：（mysqld --initialize-insecure --defaults-file=d:\xxxxxxxx\my.ini 或 mysqld --initialize-insecure --user=mysql）

会生成无密码的root用户和在根目录下生成一个data文件夹

2.安装或移除mysql服务

mysqld --install --defaults-file="D:\xxxxxxxxxxxxx\my.ini" mysql57
mysqld --remove mysql57

3.启动服务

net start mysql57
net stop mysql57

4.设置或更新mysql密码

   方法1： 用SET PASSWORD命令

mysql -u root -p

　　mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

  方法2：用mysqladmin

mysqladmin -u root password "newpass"

　　如果root已经设置过密码，采用如下方法

　　mysqladmin -u root password oldpass "newpass"

  方法3： 用UPDATE直接编辑user表

mysql -u root -p

　　mysql> use mysql;

　　mysql> UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';

　　mysql> FLUSH PRIVILEGES;

  在丢失root密码的时候，可以这样

1. 关闭正在运行的MySQL服务。 
2. 打开DOS窗口，转到mysqlbin目录。 
3. 输入mysqld --skip-grant-tables 回车。--skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。 
4. 再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysqlbin目录。 
5. 输入mysql回车，如果成功，将出现MySQL提示符 >。 
6. 连接权限数据库： use mysql; 。 
6. 改密码：update user set password=password("123") where user="root";（别忘了最后加分号） 。 
7. 刷新权限（必须步骤）：flush privileges;　。 
8. 退出 quit。 
9. 重启服务，使用用户名root和刚才设置的新密码123登录。

开启远程连接权限

# mysql 5.7
use mysql;
grant all privileges  on *.* to root@'%' identified by "hzl";
FLUSH PRIVILEGES;


# mysql 8.0
use mysql;
alter user 'root'@'localhost' identified with mysql_native_password by '**********';
update user set host='%' where user='root';
FLUSH PRIVILEGES;

五，安装过程中可能出现的错误

1.遇到MSVCR120.dll文件丢失错误的解决方案

下载 VC redist packages for x64，下载完成，点击运行即可。