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

前端之家收集整理的这篇文章主要介绍了Ubuntu上搭建mysql服务器及外部访问前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

安装MysqL服务器和客户端

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

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

liukang@liukang@H_403_5@-virtual-machine:@H_403_5@~$ @H_403_5@sudo service MysqL restart

命令行登录MysqL:

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

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

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

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

liukang@liukang-virtual-machine:~$ sudo vi /etc/MysqL/my@H_403_5@.cnf  
[MysqLd]  
port=3606@H_403_5@  
datadir=/var/lib/MysqL  
socket=/var/lib/MysqL/MysqL.sock  
user=MysqL  
# Disabling symbolic-links is recommended to prevent assorted security risks @H_403_5@
symbolic-links=0@H_403_5@  

[MysqLd_safe]  
log@H_403_5@-error@H_403_5@=/var/log@H_403_5@/MysqLd.log@H_403_5@  
pid-file@H_403_5@=/var/run@H_403_5@/MysqLd/MysqLd.pid  

"my.cnf"@H_403_5@ 11@H_403_5@L,261@H_403_5@C written  

liukang@liukang-virtual-machine:~$

重新启动MysqL

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

# Instead of@H_403_5@ skip@H_403_5@-networking the default@H_403_5@ is@H_403_5@ now to@H_403_5@ listen only on@H_403_5@  
# localhost which is@H_403_5@ more compatible and@H_403_5@ is@H_403_5@ not@H_403_5@ less secure.  
# bind-address           = 127.0@H_403_5@.0.1@H_403_5@

修改配置记得重启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;'@H_403_5@ or '\h'@H_403_5@ for help. Type '\c'@H_403_5@ 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,password from user; +-------------------------+------------------+-------------------------------------------+@H_403_5@
| host | user | password | +-------------------------+------------------+-------------------------------------------+@H_403_5@
| localhost               | root             | *AEDD34275BE2CCF550FF1257B2C822B57DB858F0 | | liukang-virtual-machine | root | *@H_403_5@AEDD34275BE2CCF550FF1257B2C822B57DB858F0 |
| 127.0.0.1               | root             | *AEDD34275BE2CCF550FF1257B2C822B57DB858F0 | | ::1 | root | *@H_403_5@AEDD34275BE2CCF550FF1257B2C822B57DB858F0 |
| localhost | debian-sys-maint | *AFC871473228030333589B66C3085F7741545136 | +-------------------------+------------------+-------------------------------------------+@H_403_5@
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,password from user; +-----------+------------------+-------------------------------------------+@H_403_5@
| host | user | password | +-----------+------------------+-------------------------------------------+@H_403_5@
| localhost | root             | *AEDD34275BE2CCF550FF1257B2C822B57DB858F0 | | % | root | *@H_403_5@AEDD34275BE2CCF550FF1257B2C822B57DB858F0 |
| 127.0.0.1 | root             | *AEDD34275BE2CCF550FF1257B2C822B57DB858F0 | | ::1 | root | *@H_403_5@AEDD34275BE2CCF550FF1257B2C822B57DB858F0 |
| localhost | debian-sys-maint | *AFC871473228030333589B66C3085F7741545136 | +-----------+------------------+-------------------------------------------+@H_403_5@
5 rows in set (0.00 sec)

MysqL>

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

猜你在找的Ubuntu相关文章