root
密码忘记,重置mysql的root密码
:t
一、修改mysql的配置文件f
1.
在[mysqld]的段中加上一句:skip-grant-tables
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-name-resolve
skip-grant-tables
保存并且退出vi。
(
或执行mysqld_safe--skip-grant-tables&
)
2.重新启动mysqld
#/etc/init.d/mysqldrestart
StoppingMySQL:[OK]
StartingMySQL:[OK]
3.登录并修改MySQL的root密码
#/usr/bin/mysql
mandsendwith;or\g.
YourMySQLconnectionidis3toserverversion:3.23.56
Type'help;'or'\h'forhelp.Type'\c'toclearthebuffer.
mysql>usemysql;
Readingtableinformationforcompletionoftableandcolumnnames
Youcanturnoffthisfeaturetogetaquickerstartupwith-A
Databasechanged
mysql>
update user set authentication_string = password('root'), password_expired = 'N', password_last_changed = now() where user = 'root';
QueryOK,0rowsaffected(0.00sec)
Rowsmatched:2Changed:0Warnings:0
mysql>flushprivileges;
QueryOK,0rowsaffected(0.01sec)
mysql>quit
Bye
4.将MySQL的登录设置修改回来
#vi/etc/f
将刚才在[mysqld]的段中加上的skip-grant-tables删除
保存并且退出vi。
5.重新启动mysqld
#/etc/init.d/mysqldrestart
StoppingMySQL:[OK]
StartingMySQL:[OK]
二、
停止mysql服务
/etc/init.d/mysqldstop
sudomysqld_safe--skip-grant-table&
mysql
usemysql;
updateusersetpassword=password('yourpasswd')whereuser='root';
flushprivileges;
重启下mysql服务即可重新用新密码登录
/etc/init.d/mysqldrestart
允许远程连接mysql
通过navicat连接MySQL的时候发生的这个错误
ERROR1130:Host***.***.***.***isnotallowedtoconnecttothisMySQLserver
说明所连接的用户帐号没有远程连接的权限,只能在本机(localhost)登录。
需更改MySQL数据库里的user表里的host项
把localhost改称%
一、
登陆到MySQL,首先useMySQL;
按照别人提供的方式update的时候,出现错误。
MySQL>updateusersethost='%'whereuser='root';
ERROR1062(23000):Duplicateentry'%-root'forkey'PRIMARY'
然后查看了下数据库的host信息如下:
MySQL>selecthostfromuserwhereuser='root';
+-----------------------+
|host|
+-----------------------+
|%|
|127.0.0.1|
|localhost.localdomain|
+-----------------------+
3rowsinset(0.00sec)
host已经有了%这个值,所以直接运行命令:
MySQL>flushprivileges;
二、
mysql>
grantallprivilegeson*.*to'root'@'%'withgrantoption;
QueryOK,0rowsaffected(0.02sec)
mysql>
flushprivileges;
QueryOK,0rowsaffected(0.00sec)
mysql>
selectuser.hostfromuser;
+-----------+--------------+
|user|host|
+-----------+--------------+
|root|
%
|
|root|127.0.0.1|
|repl_user|192.168.1.52|
+-----------+--------------+
3rowsinset(0.00sec)
允许指定
IP
访问
mysql
mysql>
grantallprivilegeson*.*to
'root'@116.224.126.250
identifiedby'123456';
QueryOK,0rowsaffected(0.00sec)