Ubuntu 16 MariaDB 开启远程访问

前言:
在Ubuntu16上安装MariaDB后, 只能在本地访问, 不能在windows中通过navicat远程访问, 一直提示错误：

Can't connect to MySQL server on 192.168.x.xxx (Unknown error: 10061)

本文记录下解决过程。

---

首先感谢这位博主给我的启发, 虽然Mysql和Maria很像, 部分配置任然不同.
http://blog.csdn.net/SCTU_vroy/article/details/53019198

---

解决思路

1. 确定Mysql服务已开启

$ service mysql status// 服务已开启 Active: active● mysql.service - LSB: Start and stop the mysql database server daemon   Loaded: loaded (/etc/init.d/mysql; bad; vendor preset: enabled)   Active: active (running) since Thu 2017-09-28 15:39:41 ULAST; 25min ago   ...// 服务未启动状态 Active: inactive● mysql.service - LSB: Start and stop the mysql database server daemon   Loaded: loaded (/etc/init.d/mysql; bad; vendor preset: enabled)   Active: inactive (dead) since Thu 2017-09-28 16:06:52 ULAST; 6s ago   ...

开启mysql服务

$ service mysql start==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===Authentication is required to start 'mysql.service'.Authenticating as: nginx,,, (nginx)Password: ==== AUTHENTICATION COMPLETE ===

2. 在mysql.user表中添加新用户(与linux用户无关), 配置好相关权限

进入 Maria 数据库, 先看看已有用户以及数据库有哪些

// 使用管理员进入数据库$ sudo mysql -uroot -p// 查看当前已存在用户MariaDB [(none)]> select host,user from mysql.user;+-----------+-------+| host      | user  |+-----------+-------+| %         | admin || %         | root  || localhost | root  |+-----------+-------+// 查看已有数据库MariaDB [(none)]> show databases;+--------------------+| Database           |+--------------------+| information_schema || my_test            || mysql              || performance_schema |+--------------------+

接下来在mysql.user表中添加一个新用户, 示例添加一条dbadmin用户, 创建新的数据库my_db, 并为dbadmin配置权限: 拥有my_db库所有权限(一般来说dba不会把root开放给我们).

// 创建数据库, 这里作为演示, 简单创建MariaDB [(none)]> create database `my_db`;MariaDB [(none)]> show databases;+--------------------+| Database           |+--------------------+| information_schema || my_db              || my_test            || mysql              || performance_schema |+--------------------+// 创建新用户// dbadmin: 新用户登录名      mysql.user.user// sqlpass: 新用户登录密码    mysql.user.password// % :      无限制链接地址    mysql.user.hostMariaDB [(none)]> create user 'dbadmin'@'%' identified by 'sqlpass';MariaDB [(none)]> select host,user from mysql.user where user='dbadmin';+------+---------+| host | user    |+------+---------+| %    | dbadmin |+------+---------+// 为用户分配权限// 完全控制 my_db// my_db.*: my_db库中的所有表MariaDB [(none)]> GRANT ALL PRIVILEGES ON my_db.* TO 'dbadmin'@'%';// 权限分配完成后一定要记得刷新// 否则不会生效MariaDB [(none)]> flush privileges;

到这如果防火墙和Maria配置没问题的话, 已经可以远程访问了. 如果还是不能远程访问, 继续往下看.

3.检查防火墙配置

两种解决方案: a 关闭防火墙; b. 为3306放行

// 关闭防火墙# ufw disable# ufw statusStatus: inactive// 开启防火墙// 为3306放行# sudo ufw allow 3306# ufw enable# ufw statusStatus: activeTo                         Action      From--                         ------      ----3306                       ALLOW       Anywhere                  3306 (v6)                  ALLOW       Anywhere (v6) 

4. 检查 .conf 配置, 修改配置文件后要重启生效

配置文件位置与Mysql略有不同.

• MySQL配置文件位置: /etc/mysql/mysql.conf.d/mysqld.cnf
• MariaDB配置文件位置: /etc/mysql/mariadb.conf.d/50-server.conf

# cd /etc/mysql/mariadb.conf.d# lltotal 24drwxr-xr-x 2 root root 4096 Sep 28 15:38 ./drwxr-xr-x 4 root root 4096 Sep 27 19:08 ../-rw-r--r-- 1 root root  495 Jul  1 05:26 50-client.cnf-rw-r--r-- 1 root root  336 Jul  1 05:26 50-mysql-clients.cnf-rw-r--r-- 1 root root  321 Jul  1 05:26 50-mysqld_safe.cnf-rw-r--r-- 1 root root 3492 Sep 28 15:38 50-server.cnf

修改 50-server.conf 文件 (vim 显示行号 : set number/nonumber), 找到 bind-address = 127.0.0.1这一行, 并注释掉(前面插入井号:#)

# vim 50-server.conf 22 datadir         = /var/lib/mysql 23 tmpdir          = /tmp 24 lc-messages-dir = /usr/share/mysql 25 skip-external-locking 26  27 # Instead of skip-networking the default is now to listen only on 28 # localhost which is more compatible and is not less secure. 29 # bind-address            = 127.0.0.1 30  31 # 32 # * Fine Tuning 33 # 34 key_buffer_size         = 16M

如有错误, 欢迎指正探讨.