Ubuntu上搭建mysql服务器及外部访问

安装mysql服务器和客户端

liukang@liukang-virtual-machine:~$ sudo apt-get install mysql-server mysql-client

判断mysql是否安装成功:
启动mysql服务

liukang@liukang-virtual-machine:~$ sudo service mysql restart

命令行登录mysql:

liukang@liukang-virtual-machine:~$ mysql -u root -p  
Enter password:  

使用命令show global variables like ‘port’;查看端口号

mysql> show global variables like 'port';  
+---------------+-------+  
| Variable_name | Value |  
+---------------+-------+  
| port | 3306 |  
+---------------+-------+  
1 row in set (0.00 sec)  

修改端口，编辑/etc/mysql/my.cnf , 注意该端口未被使用，保存退出。

liukang@liukang-virtual-machine:~$ sudo vi /etc/mysql/my.cnf  
[mysqld]  
port=3606  
datadir=/var/lib/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  

"my.cnf" 11L, 261C written  

liukang@liukang-virtual-machine:~$  

重新启动mysql

liukang@liukang-virtual-machine:~$ /etc/init.d/mysqld restart  

---

安装完成后使用Navicat连接一下，但是报错了：
2003 can’t connect to mysql server on ‘192.168.1.4’ (10038)

本机用“ mysql -u root -p”都可访问，但外部却无法访问？：
/etc/mysql/my.cnf中的 bind-address = 127.0.0.1注释掉即可

liukang@liukang-virtual-machine:~$ sudo vi /etc/mysql/my.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  

修改配置记得重启mysql

再次用Navicat连接一下，还是报错：
1130 - Host ‘192.168.1.2’ is nor allowed to connect to this MySQL server
不被允许？那就是访问权限的问题了

liukang@liukang-virtual-machine:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.5.55-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2017, 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> select host, user, password from user;
ERROR 1046 (3D000): No database selected
mysql> use mysql;
Database changed
mysql> select host, user, password from user;
+-------------------------+------------------+-------------------------------------------+
| host                    | user             | password                                  |
+-------------------------+------------------+-------------------------------------------+
| localhost               | root             | *AEDD34275BE2CCF550FF1257B2C822B57DB858F0 |
| liukang-virtual-machine | root             | *AEDD34275BE2CCF550FF1257B2C822B57DB858F0 |
| 127.0.0.1               | root             | *AEDD34275BE2CCF550FF1257B2C822B57DB858F0 |
| ::1                     | root             | *AEDD34275BE2CCF550FF1257B2C822B57DB858F0 |
| localhost               | debian-sys-maint | *AFC871473228030333589B66C3085F7741545136 |
+-------------------------+------------------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> update user set host = "%" where host = "liukang-virtual-machine" and user = "root";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    41
Current database: mysql

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select host, user, password from user;
+-----------+------------------+-------------------------------------------+
| host      | user             | password                                  |
+-----------+------------------+-------------------------------------------+
| localhost | root             | *AEDD34275BE2CCF550FF1257B2C822B57DB858F0 |
| %         | root             | *AEDD34275BE2CCF550FF1257B2C822B57DB858F0 |
| 127.0.0.1 | root             | *AEDD34275BE2CCF550FF1257B2C822B57DB858F0 |
| ::1       | root             | *AEDD34275BE2CCF550FF1257B2C822B57DB858F0 |
| localhost | debian-sys-maint | *AFC871473228030333589B66C3085F7741545136 |
+-----------+------------------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> 

这样在另一台电脑上使用Navicat就可以成功连接至MySQL数据库服务器了