安装mysql或mariadb数据库
# 安装mysql或mariadb数据库
MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可。开发这个分支的原因之一是：甲骨文公司收购了MySQL后，有将MySQL闭源的潜在风险，因此社区采用分支的方式来避开这个风险。MariaDB完全兼容mysql，使用方法也是一样的。
## 检查基础情况
有的centos7已经默认安装了Mariadb，可以使用如下命令查看系统是否安装了Mariadb。
```shell
[root@hadoop-1 ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.56-2.el7.x86_64
```
若运行结果如上所示，则表明当前系统只有`mariadb-libs-5.5.56-2.el7.x86_64`包，并没有安装Mariadb。
若当前系统安装的Mariadb与自己期望的版本不相符，可以使用`yum remove mariadb-server`命令卸载已有的Mariadb，再重新安装一个自己期望版本的。
查看操作系统版本信息
```shell
[root@hadoop-1 ~]# cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)
```
## 1 安装MariaDB
通过yum安装就行了。简单快捷，安装mariadb-server，默认依赖安装mariadb，一个是服务端、一个是客户端。
```shell
[root@hadoop-1 ~]# yum install mariadb-server -y
```
## 2 配置MariaDB
1）安装完成后首先要把MariaDB服务开启，并设置为开机启动
```shell
[root@hadoop-1 ~]# systemctl start mariadb # 开启服务
[root@hadoop-1 ~]# systemctl enable mariadb # 设置为开机自启动服务
```
2）首次安装需要进行数据库的配置，命令都和mysql的一样
```shell
[root@hadoop-1 ~]# mysql_secure_installation
```
3）配置时出现的各个选项
```shell
[root@hadoop-1 ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none): # 输入数据库超级管理员root的密码(注意不是系统root的密码)，第一次进入还没有设置密码则直接回车
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y # 设置root用户密码
New password: # 新密码，假如设置的是：jywei
Re-enter new password: # 再次输入刚才的密码以确认
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y # 移除匿名用户
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] n # 拒绝root远程登录，n，不管y/n，都会拒绝root远程登录
... skipping.
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y # 删除test数据库，y：删除。n：不删除，数据库中会有一个test数据库，一般不需要
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y # 重新加载权限表，y。或者重启服务也可以
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
```
4）使用`mysql -u root -p`测试是否能够登录成功，出现`MariaDB [(none)]>`就表示已经能够正常登录使用MariaDB数据库了
# 3 设置MariaDB字符集为utf-8
1）编辑/etc/my.cnf文件，在[mysqld]标签下添加如下配置
```shell
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
```
2）编辑/etc/my.cnf.d/client.cnf文件，在[client]标签下添加如下配置
```shell
default-character-set=utf8
```
3）编辑/etc/my.cnf.d/mysql-clients.cnf文件，在[mysql]标签下添加如下配置
```shell
default-character-set=utf8
```
4）重启服务
```shell
[root@hadoop-1 ~]# systemctl restart mariadb
```
5）进入mariadb查看字符集
修改字符集之前查询的结果如下：
```sql
MariaDB [(none)]> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]>
```
修改字符集之后查询的结果如下：
```sql
MariaDB [(none)]> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database | utf8_unicode_ci |
| collation_server | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)
MariaDB [(none)]>
```
## 4 配置远程链接mariadb数据库
mariadb默认是拒绝root远程登录的，为了方数据库的管理和维护，我们将开启mariadb的远程登录功能并使用navicat软件连接数据库。首先查看mysql数据库中的user表信息：
```shell
[root@hadoop-1 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.64-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)]>
```
执行如下sql操作
```sql
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
MariaDB [mysql]> select host, user from user;
+-----------+------+
| host | user |
+-----------+------+
| 127.0.0.1 | root |
| ::1 | root |
| localhost | root |
+-----------+------+
4 rows in set (0.00 sec)
MariaDB [mysql]>
```
1）将host字段值与主机名相等的记录的host字段值改为"%"，我的主机名为hadoop-1
```sql
MariaDB [mysql]> update user set host='%' where host='hadoop-1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [mysql]> select host, user from user;
+-----------+------+
| host | user |
+-----------+------+
| % | root |
| 127.0.0.1 | root |
| ::1 | root |
+-----------+------+
4 rows in set (0.00 sec)
```
2）刷新权限表
```sql
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
```
或重启mariadb服务
```shell
[root@hadoop-1 ~]# systemctl restart mariadb
```
注意：更新权限信息的以上两个操作二选一即可；另外，刷新权限表是在数据库中执行，重启服务是在命令行中执行。
3）使用Navicat Premiumt等工具新建一个Mariadb连接并测试是否能够“连接成功”