mysql数据库给局域网用户所有的权限

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

刚装好的服务端时必须用 sudo命令才能登录，不然就报1698的错误

然后就可以解决1698错误了

The "unix_socket" has been called by mysql authentication process (maybe related to a partial migration of database to mariadb, now removed). To get all stuff back working go su:

sudo su
then follow:

/etc/init.d/mysql stop
mysqld_safe --skip-grant-tables &
mysql -uroot
This will completely stop mysql, bypass user authentication (no password needed) and connect to mysql with user "root".

Now, in mysql console, go using mysql administrative db:

use mysql;
To reset root password to mynewpassword (change it at your wish), just to be sure of it:

update user set password=PASSWORD("mynewpassword") where User='root';
And this one will overwrite authentication method, remove the unix_socket request (and everything else), restoring a normal and working password method:

update user set plugin="mysql_native_password";
Exit mysql console:

quit;
Stop and start everything related to mysql:

/etc/init.d/mysql stop
kill -9 $(pgrep mysql)
/etc/init.d/mysql start
Don't forget to exit the su mode.

Now mySQL server is up and running. You can login it with root:

mysql -u root -p
or whatever you wish. Password usage is operative.

That's it.

 

//更新密码

mysql> update user set password=PASSWORD('123456') where user='root';

mysql> FLUSH PRIVILEGES;

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

　　mysqld_safe --skip-grant-tables&

　　mysql -u root mysql

　　mysql> UPDATE user SET password=PASSWORD("new password") WHERE user='root';

　　mysql> FLUSH PRIVILEGES;

mysql -hlocalhost -uroot -p回车然后输入密码；

mysql> use mysql;

// 创建远程访问权限的用户admin ,密码:123456
create user 'admin'@'%' identified by 'admin';
或者: 
create user 'admin'@'%192.168%' identified by 'admin';

// 赋权
grant all privileges on *.* to 'admin'@'192.168%' identified by 'admin' with grant option;
mysql> flush privileges;

select * from host;

 

2. my.cnf文件修改

[mysqld]

将“bind-address=127.0.0.1”改为“bind-address=0.0.0.0”

并添加“skip-name-resolve”

3. iptables防火墙检查