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 -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 40Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 selectedmysql> use mysql;Database changedmysql> 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 awayNo connection. Trying to reconnect...Connection id:    41Current database: mysqlQuery OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> 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数据库服务器了