mysql tcp 远程_MySQL_远程连接的坑与路

前言：受朋友之托，帮他解决一个远程连接不上mysql的问题，本来想着这是一个很常见的问题，只需要修改下配置文件的，以前也碰到过，但是时间间隔的有点久了，还是有些生疏了，特此记录碰到的一些问题。

1.修改用户登陆权限

1.0.如果记得root密码，此步可以跳过，修改root密码:

1.0.1.停止mysql服务

$ service mysql stop

1.0.2.修改my.cnf文件

查找my.cnf文件在哪里

$ find / -name my.cnf

/etc/mysql/my.cnf

修改my.cnf文件，在文件新增 skip-grant-tables，在启动mysql时不启动grant-tables，授权表

$ vim /etc/mysql/my.cnf

[mysqld]

skip-grant-tables

1.0.3. 启动mysql服务

$ service mysql restart

1.0.4.更改mysql root用户密码

$ mysql

mysql> use mysql;

#更改user表中root用户密码

mysql> update user set authentication_string=PASSWORD("new_pass") where user='root';

1.0.5.再次修改my.cnf文件, 需要把my.cnf配置文件改回去，把skip-grant-tables注释掉就可以了

$ vim /etc/mysql/my.cnf

[mysqld]

# skip-grant-tables

1.0.6.再次重启mysql

$ service mysql restart

用新root密码登录mysql了

1.1.登陆权限简介：

mysql用户设置当中多了一个主机选项，意思是允许这个用户使用什么主机登陆。

一般常见的主机选项为：%，localhost，IP地址

%：任意主机可以登陆

localhost：仅本机可以登陆

IP地址：指定的IP地址可以登陆

1.2.修改root的登陆权限

mysql -u root -p

use mysql;

1.3.修改登陆权限

update db set host = '%' where user = '用户名';

将权限改为ALL PRIVILEGES

mysql> grant all privileges on *.* to root@'%' identified by "password";

1.4.刷新权限列表

flush privileges;

2.root权限下(su)，修改mysql配置文件(有两处!!!):

2.1. /etc/mysql/mysql.conf.d/mysqld.cnf

[root@my ~]# vi /etc/mysql/mysql.conf.d/mysqld.cnf

# Instead of skip-networking the default is now to listen only on

# localhost which is more compatible and is not less secure.

#bind-address = 127.0.0.1 # bind-address = 127.0.0.1注释掉

2.2. 如果安装的是Mariadb10，对应目录文件会发生一些改变,修改/etc/mysql/mariadb.conf.d/50-server.cnf

[root@my ~]# cd /etc/mysql/mariadb.conf.d

[root@my ~]/etc/mysql/mariadb.conf.d# ls

50-client.cnf 50-mysql-clients.cnf 50-mysqld_safe.cnf 50-server.cnf

vi 50-server.cnf # 同样是注释掉bind-address

2.3. 重启MySQL

[root@my ~]:~# service mysql restart

2.4. 重新远程连接

[root@nb0 ~]# mysql -h xx.xx.xx.xx -uroot -p

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MySQL connection id is 10

Server version: 5.7.11-0kord6 (Ubuntu)

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>

3.几个重点问题：

3.1.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111 "Connection refused")

解决方法：打开/etc/mysql/mysql.conf.d/mysqld.cnf, 添加protocol = tcp

vim /etc/mysql/mysql.conf.d/mysqld.cnf

3.2.

Can 't connect to local MySQL server through socket '/tmp/mysql.sock '(2) ";

解决方法：

添加 [client] 配置项，如下所示

配置前：(配置 [client] 前，会报错'/tmp/mysql.sock' (2))

[mysqld]

datadir=/usr/local/mysql/data

basedir=/usr/local/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

配置后：(配置 [client] 后，重启 mysql服务)

[mysqld]

datadir=/usr/local/mysql/data

basedir=/usr/local/mysql

socket=/var/lib/mysql/mysql.sock(跟这个socket路径一样)

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[client]

port=3306

socket=/var/lib/mysql/mysql.sock

3.3.

[....] Restarting mysql (via systemctl): mysql.serviceJob for mariadb.service failed because the control process exited with error code.

See "systemctl status mariadb.service" and "journalctl -xe" for details.

failed!

解决方法：

不妨使用ll/ls -al来查看一下/var/run/mysqld的所属，如果不都是mysql的话，使用如下命令修改过来。

>ls -al

>chown -R mysql:mysql mysqld/

3.4.

ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded

解决方法：

/etc/init.d/mysql stop

sudo killall mysqld_safe

sudo killall mysqld

sudo mysqld_safe --skip-grant-tables &

mysql -u root

use mysql;

update user set password=PASSWORD("mynewpassword") where User='root';

update user set plugin="mysql_native_password";

quit;

/etc/init.d/mysql stop

sudo kill -9 $(pgrep mysql)

/etc/init.d/mysql start

参考链接：