远程连接mysql服务器

刚开始学习mysql,折腾了1天的样子.勉强实现远程连接mysql.记录下流程.

1.搭建mysql环境

省略

2.准备

需要修改一些参数

2.1数据库-修改mysql表

2.1.1 表结构如下:

root@instance-:~# mysql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

2.1.2 打开mysql表

mysql> use mysql;

2.1.3 查询

mysql> select host,user,plugin,authentication_string from user;
+-----------+------------------+-----------------------+-------------------------------------------+
| host      | user             | plugin                | authentication_string                     |
+-----------+------------------+-----------------------+-------------------------------------------+
|           | root             | auth_socket           |                                           |
+-----------+------------------+-----------------------+-------------------------------------------+

2.1.4 修改

mysql> update user set host ='%',plugin='mysql_native_password ',authentication_string=PASSWORD('123456') where user='root';

结果如下:

mysql> select host,user,plugin,authentication_string from user;
+-----------+------------------+-----------------------+-------------------------------------------+
| host      | user             | plugin                | authentication_string                     |
+-----------+------------------+-----------------------+-------------------------------------------+
| %         | root             | mysql_native_password | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------------------+-----------------------+-------------------------------------------+

说明:

host:可以访问当前数据库的服务器地址 .%号代表皮配所有,默认可能是localhost或者为空
plugin:具体是啥我也不太清楚,有点像验证方式.改为 mysql_native_password
authentication_string:算是密码吧 123456 加密后长这样

2.1.4 重启

(mysql> flush privileges; 这个感觉没有效果)
退出mysql

mysql> quit; 

关闭服务->重新服务

root@instance:~# service mysql stop
root@instance-:~# service mysql start

2.2mysql配置修改

注销 bind-address = 127.0.0.1
在文件 /etc/mysql/mysql.conf.d/mysqld.cnf 中
(百度有说在 /etc/mysql/my.cnf 中, 但我没找到)

//文件路径 /etc/mysql/mysql.conf.d/mysqld.cnf 
# bind-address = 127.0.0.1

//bind-address = 127.0.0.1 可能会导致如下 
ERROR 2003 (HY000): Can't connect to MySQL server on '106.106.106.106' (111)

3.连接

root@instance:~# mysql -p3306 -h 106.106.106.106 -u root -p

说明:

1.-p: 端口号(-p3306 连起来写, 可能会被当做表名,如下 )(3306是默认端口号)

//错误示例
root@instance:~# mysql -p 3306 -h 106.106.106.106 -u root -p
Enter password: 
ERROR 1049 (42000): Unknown database '3306'

2.-h:服务器地址
3.-u:账户名 (也可以-uroot)
4.-p:密码