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.host
MariaDB [(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 status
Status: inactive

// 开启防火墙
// 为3306放行
# sudo ufw allow 3306
# ufw enable

# ufw status
Status: active
To                         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
# ll
total 24
drwxr-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

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