Centos7 安装MariaDB

零、准备

centos7 的一些准备工作可以参考我这篇  Centos7 yum源的变更及常用依赖的安装

安装方式常用yum方式和自行下载安装包的方式。前者版本更新较缓慢，但胜在稳定，后者版本选择灵活自主。这里主要介绍yum方式

一、yum方式

1.1 安装

#yum直接安装
yum install mariadb-server

#启动并设置开机启动
systemctl start mariadb
systemctl enable mariadb

#查看数据库状态
systemctl status mariadb

1.2 配置

#执行安全性相关任务。你没看错，就是如下这样简单的一句话即可
mysql_secure_installation

#相关选项及含义
#设置系统管理员的密码（root用户），啥也不想设就直接回车即可
Enter current password for root (enter for none):  

#是否设置root密码，y
Set root password? [Y/n]  

#设置新密码并重复
New password:  
Re-enter new password: 

#是否不允许匿名用户访问，安全起见，建议选y
Remove anonymous users? [Y/n] 

是否拒绝远程登录。这里肯定选n吧。后续肯定会用各种工具远程访问的吧。
Disallow root login remotely? [Y/n] 

#是否删除test数据库，根据自己喜好选择即可
Remove test database and access to it? [Y/n] 

#重新加载权限表。y即可
Reload privilege tables now? [Y/n] 

补充一句，mariadb不像MySQL，没有密码复杂度的要求

1.3 测试本地登录

先测试本地登录，在命令行执行如下命令，如果看到如下面所示内容，说明本地登录成功

-bash-4.2$ mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

1.4： 用户管理

安装完毕后，可以新增用户，方法有如下两种

# 方法一
MariaDB [(none)]> grant all privileges on *.* to dark@% identified by '1234abcd';
Query OK, 1 row affected, 4 warnings (0.24 sec)

# 方法二
MariaDB [(none)]> insert into mysql.user(user,host,password) values('dark','localhost',password('1234abcd'));
Query OK, 1 row affected, 4 warnings (0.24 sec)

1.5： 远程登录

关闭本地firewall和iptables或打开3306端口，操作可  点击此处

再查看mysql库的user表，如下所示

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

# 先确认一下当前dark用户的host字段值
MariaDB [mysql]> select host, user from user;
+-------------------+------+
| host              | user |
+-------------------+------+
| localhost         | dark |
| 127.0.0.1         | root |
| ::1               | root |
| base-template0110 |      |
| base-template0110 | root |
| localhost         |      |
| localhost         | root |
+-------------------+------+
7 rows in set (0.00 sec)

# 修改dark用户的登录服务器，从localhost 改为 % 通配形式
MariaDB [mysql]> update user set host='%' where host='localhost' and user = 'dark';
Query OK, 1 row affected, 4 warnings (0.24 sec)


# 修改完权限后，执行下面语句刷新权限。或重启服务皆可使新权限生效
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.25 sec)


#查看修改后的数据是否生效，改为 % 说明已生效
MariaDB [mysql]> select host, user from user;
+-------------------+------+
| host              | user |
+-------------------+------+
| %                 | dark |
| 127.0.0.1         | root |
| ::1               | root |
| base-template0110 |      |
| base-template0110 | root |
| localhost         |      |
| localhost         | root |
+-------------------+------+
7 rows in set (0.00 sec)

1.6 设置字符集

这部分关系到后续创建的库和表的字符集及插入数据的正确性。所以请务必小心

#在/etc/my.cnf中的[mysql]下设置字符集，如下所示

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#配置字符集
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

 在/etc/my.cnf.d/client.cnf 中的 [client] 标签下添加如下内容

# These two groups are read by the client library
# Use it for options that affect all clients, but not the server

[client]
default-character-set=utf8

 在 /etc/my.cnf.d/mysql-clients.cnf 中的 [mysql] 标签下添加如下内容

# These groups are read by MariaDB command-line tools
# Use it for options that affect only one utility

[mysql]
default-character-set=utf8

以上三处修改完毕后，重启服务  systemctl restart mariadb。然后登录mariadb执行如下命令

# 执行 show variables like "%character%";
MariaDB [(none)]> show variables like "%character%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

# 执行 show variables like "%collation%";
MariaDB [(none)]> show variables like "%collation%";
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+