目录
- 1. 前言
- 2. 安装配置
- 3. 如何连接MySQL
- 4. 错误解决方案
- 4.1 初始安装错误集合
- 4.2 MySQL连接错误集合
- 4.2.1 ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)
- 4.2.2 ERROR 2003: Can't connect to MySQL server on '192.168.3.14' (10060)
- 4.2.3 错误:1251 - client does not support authentication protocol requested by server; consider upgrading MySQL client
- 4.2.4 错误:1045 - Access denied for user 'root'@'localhost'(using password:YES)
- 4.2.5 错误:1130 - HOST xxx is not allowed to connect to this MySQL server
- 4.2.6 MySQL 服务无法启动
- 4.2.7 ERROR 1040 (HY000): Too many connections
- 4.2.8 Public Key Retrieval is not allowed
- 4.3 忘记root密码
- 5. MySQL常用命令
1. 前言
本笔记可当工具书使用,不断更新各类使用中的错误及解决方案。
1.1 MySQL版本及选择
关于版本的选择,可以看另外一篇文章MySQL5.7安装步骤
1.2 MySQL下载地址
一般从MySQL官网直接下,不需要到其他镜像网站,谁知道镜像网站会插入些什么呢?
下载地址为:https://dev.mysql.com/downloads/mysql/
选择ZIP Archive版本,点击Download即可。
到下一个页面,可能会叫你注册一个账号,不想注册账号的,直接点“No thanks, just start my download.”就可以直接下载
2. 安装配置
2.1 说明
笔者下载的是zip包,不需要安装,但需要配置才能用。因为笔者不喜欢注册表装太多东西。如果希望用自动安装软件的,可以在上述页面下载MySQL Installer MIS。
2.2 系统设置
- 在开始之前,在命令行输入mysqld --verbose --help,因为还没配置,所以应该显示错误信息
C:\Windows\system32>mysqld --verbose --help
'mysqld' 不是内部或外部命令,也不是可运行的程序
或批处理文件。
- 把下载的文件解压,笔者解压后的路径如下
D:\Tools\mysql-8.0.23
- 在环境变量中,在系统变量中,在Path这个变量下,添加解压路径的bin目录,按笔者上述的解压位置,添加的值为:D:\Tools\mysql-8.0.23\bin
- 再打开一个新的命令行窗口一定记得是另外打开一个,不要用原来的,在命令行再次输入mysqld --verbose --help (如果遇到错误,请参照4. 错误解决方案)
- 嗯,窗口会显示很多信息,让它滚动一会儿
到此,MySQL的系统配置就搞定了,是不是很简单?但别高兴得太早,还有数据库本身的很多信息要设置。
2.3 初始化
- 用管理员权限打开命令行窗口,输入mysqld --initialize-insecure --user=mysql,等待片刻,没有出现错误信息,再次出现命令提示符,说明初始化成功(这里笔者认为用户体验不友好,什么都没提示,首次初始化是否成功,心里是很忐忑的)
C:\Windows\system32>mysqld --initialize-insecure --user=mysql
C:\Windows\system32>
- 切换到MySQL的解压目录下的bin目录,再运行mySQL的安装命令,出现成功的提示,则表示安装成功
D:\Tools\mysql-8.0.23\bin>mysqld -install
Service successfully installed.
- 在命令行窗口输入以下命令启动:net start MySQL
D:\Tools\mysql-8.0.23\bin>net start MySQL
MySQL 服务正在启动 .
MySQL 服务已经启动成功。
- 最后设定初始密码,就完成配置了(以下Enter password那里,什么都不要输入,直接按回车即可) mysqladmin -uroot -p password 123456(这里的123456是数据库密码,如果是自学用的可以设简单一些,如果是正式用的就要设置复杂一些的密码了)
D:\Tools\mysql-8.0.23\bin>mysqladmin -uroot -p password 123456
Enter password:
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
由于第1步初始化时使用了 –initialize-insecure 参数,所以在“Enter password: ”时直接回车不用输入密码。不加-insecure使用mysqld --initialize命令初始化数据库时会随机生成密码,这样在修改密码时Enter password要输入随机生成的密码,如果提示需要密码,可以打开data目录下的.err文件,搜索"A temporary password is generated for root@localhost",最后面就是密码
- 如果想删除MySql服务,先让服务停止运行,然后用下边命令删除
C:\Windows\system32>sc delete mysql
[SC] DeleteService 成功
3. 如何连接MySQL
笔者用的是navicat连接的,这个工具比较简单,自行在网上搜索即可。在navicat中,输入上述端口,用户名,密码,即可访问。新建连接如下:
4. 错误解决方案
4.1 初始安装错误集合
4.1.1 计算机中丢失VCRUNTIME140_1.dll
当运行mysqld --verbose --help的时候,可能会出现以下错误
出现问题的原因是因为电脑缺少C++运行库,到官方网站下载即可。
解决方案:下载VS Redist。官方下载地址
选择合适自己操作系统的版本(一般从最新的开始试起)。我选择的是:https://aka.ms/vs/17/release/vc_redist.x64.exe
安装完需要重启计算机
4.1.2 Install/Remove of the Service Denied!
在运行mysqld的时候,可能会出现以下错误提示,遇到这个问题,是因为执行命令的窗口权限不够,需要用管理员权限打开命令行窗口,即可解决问题
C:\Windows\system32>mysqld --initialize-insecure --user=mysql
Install/Remove of the Service Denied!
4.1.3 net start MySQL 发生系统错误 2。
在运行net start MySQL的时候,系统可能会出现以下提示:
C:\Users\Administrator>net start MySQL
发生系统错误 2。
系统找不到指定的文件。
这个原因是前面运行mysqld -install命令时,没有在MySQL的解压路径下执行。
C:\Users\Administrator>mysqld -install
Service successfully installed.
C:\Users\Administrator>net start MySQL
发生系统错误 2。
系统找不到指定的文件。
这个时候要运行反安装指令mysqld -remove, 将刚才安装的mysql反安装,再到正确的路径下安装
C:\Users\Administrator>mysqld -remove
Service successfully removed.
然后切换到本机MySQL解压的Bin目录下重新运行安装命令
C:\Users\Administrator>cd D:\Tools\mysql-8.0.23\bin
C:\Users\Administrator>d:
D:\Tools\mysql-8.0.23\bin>mysqld -install
Service successfully installed.
D:\Tools\mysql-8.0.23\bin>net start MySQL
MySQL 服务正在启动 ..
MySQL 服务已经启动成功。
4.2 MySQL连接错误集合
4.2.1 ERROR 2003 (HY000): Can’t connect to MySQL server on ‘localhost’ (10061)
在本地命令行窗口,输入mysql时,出现该错误,是因为mysql没有启动,只需要用net start mysql启动即可
D:\Tools\mysql-8.0.20\bin>mysql
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)
4.2.2 ERROR 2003: Can’t connect to MySQL server on ‘192.168.3.14’ (10060)
如果遇到远程连接,同样的错误,先检查对方是否开启了mysql服务,如果确定开启了,那就是windows防火墙没开启
解决方案就是对应的机器打开防火墙。如果是windows系统,防火墙设置如下文
Windows防火墙firewall设置
4.2.3 错误:1251 - client does not support authentication protocol requested by server; consider upgrading MySQL client
在使用Navicat连接MySQL8.0的时候,会出现以下错误(连接5.x不会出现)
出现问题的原因是:mySQL5.x和MySQL8.x默认的加密规则不同
MySQL版本 | 加密规则 |
---|---|
MySQL5.x | mysql_native_password |
MySQL8.x | caching_sha2_password |
那么如何解决呢?有两种方法: | |
方法1.升级navicat驱动; | |
方法2.把mysql用户登录密码加密规则还原成mysql_native_password. |
虽然caching_sha2_password 的加密规则更加安全,但笔者使用MySQL作为学习,还是还原回旧的方式比较方便。所以笔者使用方法2来解决这个问题。
- 首先从命令行窗口进入MySQL控制台窗口
输入mysql -u root -p ,填写密码之后,可看到提示符从 C:\Users\Administrator> 变成了 mysql>
C:\Users\Administrator>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.20 MySQL Community Server - GPL
mysql>
- 使用命令变更加密规则
输入:ALTER USER ‘root’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘你的密码’; 记得改密码,以及最后要有分号,案例见下图。
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)
4.2.3.1 第2步的命令遇到以下错误:
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'
这是因为用户域并不是‘%’。输入以下命令进行验证
mysql> use mysql;
Database changed
mysql> select user, host, plugin from user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
4 rows in set (0.01 sec)
可以看到root的域是localhost,所以,语句只要改成如下就可以了:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.02 sec)
完成之后对比区别:
4.2.4 错误:1045 - Access denied for user ‘root’@‘localhost’(using password:YES)
出现这个错误,是数据库密码错了,需要找回创建数据库时的密码。使用正确密码来连接
4.2.5 错误:1130 - HOST xxx is not allowed to connect to this MySQL server
出现这个错误,是因为刚初始化之后的MySQL控制表,只允许本地访问,那么我们需要做的,就是开放访问控制。开放的控制可以根据通配符控制允许哪些IP访问。操作步骤(如果对命令不熟悉,可以往后看5. MySQL常用命令):
- 通过命令行窗口进入MySQL控制台窗口mysql -u {user} -p
C:\Users\Administrator>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.20 MySQL Community Server - GPL
mysql>
- 选择MySQL控制数据库 use mysql;
mysql> use mysql;
Database changed
mysql>
- 查看表“user”的原始数据,查询语句
select user, host, plugin from user;
mysql> select user, host, plugin from user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)
可以看到,"root"这个用户,只有"localhost"才能访问,这就是为什么其他的机器访问不到的原因。
host有几个选项可以选择:
host的值 | 开放权限 | 说明 |
---|---|---|
localhost | 只有本机可以访问 | 自己单机开发可以不用理会 |
192.168.1.% | 前缀为192.168.1.*的整个网段可以访问 | 在局域网内有人协同开发可以使用这个 |
% | 所有人可以访问 | 不建议开启 |
题外话,更安全的做法是新创建一个用户给远程使用,而root仅保留给本机访问。
- 变更root的host权限,这里笔者只打算开放给局域网内使用,笔者的网段为192.168.124.*, 因此SQL语句如下
update user set host = ‘192.168.124.%’ where user =‘root’;
mysql> update user set host = '192.168.124.%' where user ='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
- 最后,运行刷新权限语句,令到配置立即生效
flush privileges;
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
- 这就可以进行远程访问了
4.2.6 MySQL 服务无法启动
家里的电脑很久没用MySQL了,有项目需要研究,build的时候发现MySQL连不上,手动到cmd里启动,出现以下错误,提示:输入NET HELPMSG 3534以获得更多的帮助。
但是,童话里果然都是骗人的,输入了也没报告任何错误。
C:\Users\Administrator>net start mysql
MySQL 服务正在启动 ..
MySQL 服务无法启动。
服务没有报告任何错误。
请键入 NET HELPMSG 3534 以获得更多的帮助。
C:\Users\Administrator>NET HELPMSG 3534
服务没有报告任何错误。
C:\Users\Administrator>
在管理服务中也是一样
重启大法也无法解决。只好到百度上找,可行的解决方案如下:
- 到MySQL的安装目录,把data文件夹备份到其他地方,因为data文件夹里有自定义数据库文件,如果数据重要的话,需要把相关的数据库备份,然后放回新的data中。备份之后,data文件夹可以删除,以确定第二步操作有结果。
- 在cmd窗口中输入mysqld --initialize,运行没有提示,但看到安装目录下又生成了一个data文件夹
C:\Users\Administrator>mysqld --initialize
C:\Users\Administrator>
- 再次启动MySQL。启动成功之后,可以把备份的data文件夹删除了。
C:\Users\Administrator>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。
- 从备份的data文件家中,把自定义数据库文件夹复制回来。
错误原因应该是data中的log数据出错,导致MySQL启动时访问log数据出错。
4.2.7 ERROR 1040 (HY000): Too many connections
当从应用或其他地方连接发生这个错误的时候,即使在本机也无法通过root访问。
C:\Users\Administrator>mysql -u root -p
Enter password: ******
ERROR 1040 (HY000): Too many connections
在解决这个问题之前,需要看是否有应用程序正在不断访问数据库,如果能找到,就把连接的应用停止,再用root访问,如果仍然不行,使用net stop再net start,然后用root进入mysql
在mysql console中,使用show processlist;可以查到连接数。
mysql> show processlist;
+------+-----------------+-----------------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------------+-----------------+------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 1815 | Waiting on empty queue | NULL |
| 1679 | root | localhost:53215 | NULL | Query | 0 | starting | show processlist |
+------+-----------------+-----------------+------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)
再运行以下命令查看最大连接数
mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| max_connections | 151 |
| mysqlx_max_connections | 100 |
+------------------------+-------+
2 rows in set, 1 warning (0.00 sec)
因为我的MySQL找不到my.cnf和my.ini,所以用了命令行临时改大连接数:
set global max_connections=2000
以后看怎么进行永久性更改
4.2.8 Public Key Retrieval is not allowed
在使用DBeaver连接MySQL的时候,可能会出现这个错误。
解决方案:
右键点击需要连接的数据库 > 编辑连接 > 驱动属性 > allowPublicKeyRetrieval=true
4.3 忘记root密码
MySQL5.x与 MySQL8以上的重设密码有一些不同。主要不同之处有以下几点:
- MySQL 8以上没有my.ini文件
- MySQL 8以上以下2个指令都不起作用
update mysql.user set password='newpassword' where user='root';
update mysql.user set password=PASSWORD('newpassword') where User='root';
开始操作了,需要打开两个cmd窗口。
4.3.1 窗口一
窗口一运行以下2个语句,第2个语句输入之后,这个窗口是没有反映了的,相当于是锁死了这个窗口,接下来就要到第二个窗口操作。
C:\Users\Administrator>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。
C:\Users\Administrator>mysqld --shared-memory --skip-grant-tables
4.3.2 窗口二
输入mysql -uroot -p,并且在提示输入密码时直接回车,看到可以成功进入mysql控制窗口了
C:\Users\Administrator>mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.20 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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>
如下所示输入几个指令。其中第4个指令中的12345678是自己设置的新密码
use mysql;
update user set authentication_string='' where user='root';
flush privileges;
alter user 'root'@'localhost' identified by '12345678';
lush privileges;
指令效果
mysql> use mysql;
Database changed
mysql> update user set authentication_string='' where user='root';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)
mysql> alter user 'root'@'localhost' identified by '12345678';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql>
到这里打完收工,关闭2个窗口,net start mysql开启mysql服务,可以使用新密码访问了
5. MySQL常用命令
5.1 进入MySQL命令行
mysql -u {user} -p
其中{user}为用户名,命令如下,执行该命令后,进入MySQL控制台界面
C:\Users\Administrator>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.20 MySQL Community Server - GPL
mysql>
5.2 创建数据库
create database {db name};
该语句创建新的数据库
mysql> create database mydb;
Query OK, 1 row affected (0.01 sec)
mysql>
5.2 显示所有数据库
show databases;
执行该命令,可以看到MySQL里所有的数据库,该语句必须在MySQL控制台,而不是在windows的命令行输入。使用5.1的命令可以进入MySQL的命令行。在刚初始化MySQL之后,输入该命令可以看到所有的数据库。执行效果如下
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| supermarket |
| sys |
| umanager |
+--------------------+
6 rows in set (0.00 sec)
5.3 进入某个数据库
use {数据库名};
操作上,必须要进入某个数据库,才能操作数据库中的表,特别是刚初始化之后,可能其他的工具都连不上,需要通过MySQL命令行来执行一些命令才可以使用。执行效果如下
mysql> use mysql;
Database changed
mysql>
5.4 修改数据库密码
alter user ‘root’@‘localhost’ identified by ‘newpassword’;
用这个命令,需要是已经通过mysql -u root -p 进入数据库。newpassword为希望设置的新密码
alter user 'root'@'localhost' identified by 'newpassword';