ubuntu16.04下安装和使用mariadb常见问题

1. 更新本地的仓库:

sudo apt-get update

2. ubuntu16.04下安装

apt-get install mariadb-server

3. MariaDB root 账户默认使用了 UNIX_SOCKET 插件，只允许 root 用户连接，无需密码。

mysql -uroot -p

4. 进入mysql，设置root密码，并刷新权限表

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

FLUSH PRIVILEGES;

5. 添加远程用户访问权限，root为用户名称，%代表所有用户均可访问，可指定IP，如'192.168.11.1',mypassword为密码

GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

FLUSH PRIVILEGES;

6. 如远程无法访问，需要修改配置文件server.cnf，位置如下图，

pxl@pxl-virtual-machine:/etc/mysql/mariadb.conf.d$ ll
总用量 24
drwxr-xr-x 2 root root 4096 2月   9 09:30 ./
drwxr-xr-x 4 root root 4096 2月   9 08:42 ../
-rw-r--r-- 1 root root  495 1月  17 00:46 50-client.cnf
-rw-r--r-- 1 root root  336 1月  17 00:46 50-mysql-clients.cnf
-rw-r--r-- 1 root root  321 1月  17 00:46 50-mysqld_safe.cnf
-rw-r--r-- 1 root root 3494 2月   9 09:30 50-server.cnf

将下图的skip-external-locking和bind-address注释

[mysqld]
# * Basic Settings
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
#skip-external-locking
# 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

重启mariadb

service mysql restart