How to solve the problem that mysql cannot log in for the first time

#1.停止mysql数据库/etc/init.d/mysqld stop
#2.执行如下命令mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
#3.使用root登录mysql数据库mysql -u root mysql
#4.更新root密码mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root';
#最新版MySQL请采用如下SQL：mysql> UPDATE user SET authentication_string=PASSWORD('newpassword') where USER='root';
#5.刷新权限 mysql> FLUSH PRIVILEGES;
#6.退出mysqlmysql> quit
#7.重启mysql/etc/init.d/mysqld restart
#8.使用root用户重新登录mysqlmysql -uroot -p 
Enter password: <输入新设的密码newpassword>

Methods to allow remote users to log in and access mysql

需要手动增加可以远程访问数据库的用户。
方法一、本地登入mysql，更改 "mysql" 数据库里的 "user" 表里的 "host" 项，将"localhost"改为"%"#mysql -u root -prootmysql>use mysql;
mysql>update user set host = '%' where user = 'root';
mysql>select host, user from user;
方法二、直接授权(推荐)
　　从任何主机上使用root用户，密码：youpassword（你的root密码）连接到mysql服务器：
MySQL登录时出现 Access denied for user 'root'@'xxx.xxx.xxx.xxx' (using password: YES) 的原因及解决办法。# mysql -u root -h 192.168.194.142 -pEnter password: 
ERROR 1045 (28000): Access denied for user 'root'@'192.168.194.142' (using password: YES)

【解决办法】1. 先用localhost登录# mysql -u root -pEnter password: 
2. 执行授权命令
mysql> grant all privileges on *.* to root@'%' identified by '123';
Query OK, 0 rows affected (0.07 sec)3. 退出再试
mysql> quit
Bye
再试登录：# mysql -u root -h 192.168.194.142 -pEnter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3Server version: 5.6.33 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 itsaffiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 
成功啦！

下面详细说说如何给用户授权。
mysql> grant 权限1,权限2, ... 权限n on 数据库名称.表名称 to 用户名@用户地址 identified by '连接口令';
权限1，权限2，... 权限n 代表 select、insert、update、delete、create、drop、index、alter、grant、references、reload、shutdown、process、file 等14个权限。
当权限1，权限2，... 权限n 被 all privileges 或者 all 代替时，表示赋予用户全部权限。
当 数据库名称.表名称 被 *.* 代替时，表示赋予用户操作服务器上所有数据库所有表的权限。
用户地址可以是localhost，也可以是IP地址、机器名和域名。也可以用 '%' 表示从任何地址连接。
'连接口令' 不能为空，否则创建失败。

举几个例子：
mysql> grant select,insert,update,delete,create,drop on vtdc.employee to joe@10.163.225.87 identified by ‘123′;
给来自10.163.225.87的用户joe分配可对数据库vtdc的employee表进行select,insert,update,delete,create,drop等操作的权限，并设定口令为123。

mysql> grant all privileges on vtdc.* to joe@10.163.225.87 identified by ‘123′;
给来自10.163.225.87的用户joe分配可对数据库vtdc所有表进行所有操作的权限，并设定口令为123。

mysql> grant all privileges on *.* to joe@10.163.225.87 identified by ‘123′;
给来自10.163.225.87的用户joe分配可对所有数据库的所有表进行所有操作的权限，并设定口令为123。

mysql> grant all privileges on *.* to joe@localhost identified by ‘123′;
给本机用户joe分配可对所有数据库的所有表进行所有操作的权限，并设定口令为123。

FLUSH PRIVILEGES;

The above is the detailed content of How to solve the problem that mysql cannot log in for the first time. For more information, please follow other related articles on the PHP Chinese website!