Ubuntu 18.04 中的 Mysql 远程连接设置

参考链接:
https://juejin.im/post/5d1b0b4c5188255d6c21ed59
https://blog.youkuaiyun.com/qq_41834400/article/details/96500094
https://blog.youkuaiyun.com/leondryu/article/details/82493719

走了一些弯路, 搞定后发一篇自己的教程.
前提, 登陆账户为 root , 如果不是 root 账户则在命令前面加 sudo

登陆到 Mysql root 账户

使用命令:

mysql -uroot -p

出现 Enter password: , 输入密码.

登陆完成出现下列提示:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 249
Server version: 5.5.62-log Source distribution

Copyright (c) 2000, 2018, 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>

增加授权

GRANT ALL PRIVILEGES ON . TO 'root'@'%'IDENTIFIED BY 'password' WITH GRANT OPTION;

FLUSH PRIVILEGES;

其中 password 改为自己要设置的密码. 执行后要再执行 FLUSH PRIVILEGES; 命令才能使新增的授权生效.

之后使用下面的命令查看所有账户, 看到表中有 host 列为 % , user 列为 root 即代表设置成功.

use mysql;
select host, user from user;

使用 ctrl + c 退出 mysql

修改 my.cnf 文件

找到并修改 my.cnf 文件, 不清楚的话使用 find / -name my.cnf 搜索, 假如有多个的情况下, 选择不是 mysql-test 目录下的那个文件修改.

使用 vim 打开后会看到类似如下内容:

[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
datadir = /usr/local/mysql/var
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
thread_cache_size = 8
query_cache_size = 8M
tmp_table_size = 16M
bind-address = 0.0.0.0

#skip-networking
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
...

在 [mysqld] 下加入这行 bind-address = 0.0.0.0 , 已存在但地址不是 0.0.0.0 的话则修改为 0.0.0.0 , 然后重启 mysql.

重启 mysql

/usr/local/mysql/support-files/mysql.server stop
/usr/local/mysql/support-files/mysql.server start

注意有可能你的 mysql.server 文件不在这个目录下, 那就 find / -name mysql.server 找一下.