运维必学：MariaDB 无痛部署指南-171主机测评

运维必学：MariaDB 无痛部署指南

前言

MariaDB是MySQL的开源社区分支，由MySQL原开发团队维护，SQL语法、命令、配置、主从复制机制几乎和MySQL完全兼容，无使用门槛。CentOS7系统默认数据库即为MariaDB，中小企业、网站后端、测试环境广泛使用。

一、实验环境规划

节点IP与主机名对照表

主机名IP地址角色

mariadb-server	10.1.8.10/24	单机数据库服务端
mariadb-client	10.1.8.11/24	远程测试客户端

1 主机静态IP、主机名配置

mariadb-server 配置

[root@localhost ~]# hostnamectl set-hostname mariadb-server
[root@localhost ~]# nmcli connection modify ens33 ipv4.method manual ipv4.addresses 10.1.8.10/24 ipv4.gateway 10.1.8.2 ipv4.dns 10.1.8.2 autoconnect yes
[root@localhost ~]# nmcli connection up ens33

mariadb-client 配置

[root@localhost ~]# hostnamectl set-hostname mariadb-client
[root@localhost ~]# nmcli connection modify ens33 ipv4.method manual ipv4.addresses 10.1.8.11/24 ipv4.gateway 10.1.8.2 ipv4.dns 10.1.8.2 autoconnect yes
[root@localhost ~]# nmcli connection up ens33

二、单机MariaDB安装与安全初始化

1 YUM安装数据库服务

[root@mariadb-server ~]# yum install -y mariadb mariadb-server
# 设置开机自启并立即启动
[root@mariadb-server ~]# systemctl start mariadb
[root@mariadb-server ~]# systemctl enable mariadb
# 查看运行状态
[root@mariadb-server ~]# systemctl status mariadb

2 生产必做安全加固脚本 mysql_secure_installation

[root@mariadb-server ~]# mysql_secure_installation

完整交互步骤说明：

Enter current password for root (enter for none): 直接回车，新装数据库root无密码

Set root password? [Y/n] y 输入y，设置root管理员密码（示例密码huawei）

New password: 输入自定义root密码

Re-enter new password: 重复密码确认

Remove anonymous users? [Y/n] y 删除匿名测试账号，防止无密码登录

Disallow root login remotely? [Y/n] y 禁止root账号远程连接，提升安全

Remove test database and access to it? [Y/n] y 删除默认test测试库

Reload privilege tables now? [Y/n] y 立即刷新权限，所有配置生效

3 数据库本地/远程登录方式

本地登录（仅本机可用）

[root@mariadb-server ~]# mysql -uroot -p
Enter password: huawei
Welcome to the MariaDB monitor. Commands end with ; or \\g.
Your MariaDB connection id is 11
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)]>

指定IP、端口登录

[root@mariadb-server ~]# mysql -uroot -p -h127.0.0.1 -P3306
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \\g.
Your MariaDB connection id is 12
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)]>

三、全套基础SQL操作

注意：所有SQL语句末尾必须带英文分号 ; 结束

3.1 数据库库操作

— 登录数据库
[root@mariadb–server ~]# mysql -uroot -p -h127.0.0.1 -P3306
Enter password:

— 创建普通数据库
MariaDB [(none)]> create database school;
Query OK, 1 row affected (0.00 sec)

— 创建指定utf8mb4字符集数据库（支持中文、emoji表情）
MariaDB [(none)]> create database school2 default character set utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)

— 切换进入指定数据库
MariaDB [(none)]> use school;
Database changed

— 查询当前所在数据库
MariaDB [school]> select database();
+————+
| database() |
+————+
| school |
+————+
1 row in set (0.00 sec)

— 删除数据库
MariaDB [school]> drop database school2;
Query OK, 0 rows affected (0.00 sec)

3.2 数据表操作

— 查看当前库所有数据表
MariaDB [school]> show tables;
Empty set (0.00 sec)

— 创建学生表student（自增主键、姓名、年龄字段）
MariaDB [school]> create table student(
id int primary key auto_increment,
name varchar(20),
age int
);

— 创建备用测试表student2
MariaDB [school]> create table student2(
id int primary key auto_increment,
name varchar(20),
age int
);

— 查看表字段结构
MariaDB [school]> desc student;
+——-+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+——-+————-+——+—–+———+—————-+
3 rows in set (0.00 sec)

— 查看完整建表语句
MariaDB [school]> show create table student;
+——–+———————————————————————————————————-+
| Table | Create Table |
+——–+———————————————————————————————————-+
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+——–+———————————————————————————————————-+
1 row in set (0.00 sec)

— 删除测试表student2
MariaDB [school]> drop table student2;
Query OK, 0 rows affected (0.00 sec)

MariaDB [school]> show tables;
+—————-+
| Tables_in_school |
+—————-+
| student |
+—————-+
1 row in set (0.00 sec)

3.3 DML增删改查数据操作

— 批量插入两条学生数据
MariaDB [school]> insert into student(name,age) values('zhangsan',18),('lisi',20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

— 查询表全部数据
MariaDB [school]> select * from student;
+—-+———-+——+
| id | name | age |
+—-+———-+——+
| 1 | zhangsan | 18 |
| 2 | lisi | 20 |
+—-+———-+——+
2 rows in set (0.00 sec)

— 条件查询：年龄大于18的学生
MariaDB [school]> select * from student where age>18;
+—-+——+——+
| id | name | age |
+—-+——+——+
| 2 | lisi | 20 |
+—-+——+
1 row in set (0.00 sec)

— 更新数据：修改id=1学生年龄为19
MariaDB [school]> update student set age=19 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [school]> select * from student;
+—-+———-+——+
| id | name | age |
+—-+———-+——+
| 1 | zhangsan | 19 |
| 2 | lisi | 20 |
+—-+———-+——+
2 rows in set (0.00 sec)

— 删除指定id数据
MariaDB [school]> delete from student where id=2;
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> select * from student;
+—-+———-+——+
| id | name | age |
+—-+———-+——+
| 1 | zhangsan | 19 |
+—-+———-+——+
1 row in set (0.00 sec)

3.4 用户创建、授权、权限管理

— 创建仅本地登录admin用户，密码123456
MariaDB [(none)]> create user 'admin'@'localhost' identified by '123456';

— 创建任意远程地址可登录admin、test用户
MariaDB [(none)]> create user 'admin'@'%' identified by '123456';
MariaDB [(none)]> create user 'test'@'%' identified by '123456';

— 给admin授予school库全部操作权限
MariaDB [(none)]> grant all privileges on school.* to 'admin'@'%';

— 刷新权限，授权立即生效
MariaDB [(none)]> flush privileges;

— 查看admin@%账号拥有的权限
MariaDB [(none)]> show grants for 'admin'@'%';
+—————————————————————————————————————-+
| Grants for admin@% |
+—————————————————————————————————————-+
| GRANT USAGE ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `school`.* TO 'admin'@'%' |
+—————————————————————————————————————-+
2 rows in set (0.00 sec)

— 查看本地admin账号权限
MariaDB [(none)]> show grants for 'admin'@'localhost';
+—————————————————————————————————————-+
| Grants for admin@localhost |
+—————————————————————————————————————-+
| GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+—————————————————————————————————————-+
1 row in set (0.00 sec)

— 删除test测试账号
MariaDB [(none)]> drop user 'test'@'%';
Query OK, 0 rows affected (0.00 sec)

3.5 客户端远程连接测试

先在mariadb-client安装客户端工具：

[root@mariadb-client ~]# yum install mariadb -y
# 使用admin账号远程连接10.1.8.10数据库
[root@mariadb-client ~]# mysql -uadmin -p123456 -h10.1.8.10 -P3306
Welcome to the MariaDB monitor. Commands end with ; or \\g.
Your MariaDB connection id is 15
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核心配置文件详解

配置文件路径

主全局配置：/etc/my.cnf

分段子配置目录：/etc/my.cnf.d/*.cnf

常用核心配置（写入/etc/my.cnf.d/server.cnf）

[mysqld]
# 单机/主从区分ID，主从环境两台服务器必须不同
server-id=1
# 数据库数据存储目录
datadir=/var/lib/mysql
# 监听地址0.0.0允许所有远程IP访问，127.0.0.1仅本地
bind-address=0.0.0.0
# 默认字符集utf8mb4，兼容中文、emoji
character-set-server=utf8mb4
# 开启二进制日志，主从复制必备
log_bin=mysql-bin
# bin日志格式mixed兼顾性能与一致性
binlog_format=m

[mysqld_safe]
# 错误日志存放路径
log-error=/var/log/mariadb/mariadb.log
# PID进程文件
pid-file=/var/run/mariadb/mariadb.pid

修改配置后重载数据库

[root@mariadb-server ~]# systemctl restart mariadb

防火墙放行数据库3306端口（远程连接必备）

[root@mariadb-server ~]# firewall-cmd –add-port=3306/tcp –permanent
[root@mariadb-server ~]# firewall-cmd –reload

五、MariaDB一主一从复制完整实战

5.1 主从复制原理说明

主库 binary binlog二进制日志：记录所有增删改、建库建表等数据变更操作，是同步数据源；

从库 relay中继日志：从主库拉取binlog临时存储，防止网络中断丢失数据；

主库线程：binlog dump线程，推送日志给从库IO线程；

从库IO线程：连接主库，拉取binlog写入本地relay日志；

从库SQL线程：读取relay日志，重放SQL语句，实现数据同步。

主从实验节点规划

主机名IP地址角色

mariadb-server1	10.1.8.10/24	主数据库
mariadb-server2	10.1.8.11/24	从数据库

5.2 两台主机基础配置

mariadb-server1（主）

[root@localhost ~]# hostnamectl set-hostname mariadb-server1
[root@localhost ~]# nmcli connection modify ens33 ipv4.method manual ipv4.addresses 10.1.8.10/24 ipv4.gateway 10.1.8.2 ipv4.dns 10.1.8.2 autoconnect yes
[root@localhost ~]# nmcli connection up ens33

mariadb-server2（从）

[root@localhost ~]# hostnamectl set-hostname mariadb-server2
[root@localhost ~]# nmcli connection modify ens33 ipv4.method manual ipv4.addresses 10.1.8.11/24 ipv4.gateway 10.1.8.2 ipv4.dns 10.1.8.2 autoconnect yes
[root@localhost ~]# nmcli connection up ens33

5.3 两台服务器统一安装、初始化数据库

# 主、从两台机器都执行安装命令
[root@mariadb-server1 ~]# yum install -y mariadb mariadb-server
[root@mariadb-server2 ~]# yum install -y mariadb mariadb-server

# 启动并开机自启
[root@mariadb-server1 ~]# systemctl start mariadb
[root@mariadb-server1 ~]# systemctl enable mariadb
[root@mariadb-server2 ~]# systemctl start mariadb
[root@mariadb-server2 ~]# systemctl status mariadb

# 两台机器都执行安全初始化 mysql_secure_installation
[root@mariadb-server1 ~]# mysql_secure_installation
[root@mariadb-server2 ~]# mysql_secure_installation
# 交互步骤和前文一致：回车无密码→设root密码→删匿名→禁远程root→删test库→刷新权限

5.4 主数据库（server1）主从配置

修改配置文件开启binlog、设置server-id

[root@mariadb-server1 ~]# vim /etc/my.cnf

写入配置：

[mysqld]
server-id=1
log_bin=mysql-bin

重启数据库生效

[root@mariadb-server1 ~]# systemctl restart mariadb.service

创建主从同步专用复制账号repl

[root@mariadb-server1 ~]# mysql -uroot -phuawei
MariaDB [(none)]> create user 'repl'@'%' identified by 'Repl@123456';
Query OK, 0 rows affected (0.00 sec)
# 授予全局复制权限
MariaDB [(none)]> grant replication slave on *.* to 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
# 查看主库当前binlog文件与偏移位置，记录File、Position参数
MariaDB [(none)]> show master status;
+——————+——+—————-+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+——+—————-+——————+
| mysql-bin.000001 | 548 | |
+——————+——+—————-+
1 row in set (0.00 sec)

5.5 从数据库（server2）配置

修改配置文件，设置独立server-id、开启relay日志

[root@mariadb-server2 ~]# vim /etc/my.cnf

写入配置：

[mysqld]
server-id=2
relay_log=mysql-relay-bin

重启服务

[root@mariadb-server2 ~]# systemctl restart mariadb.service

登录数据库配置主库同步信息

[root@mariadb-server2 ~]# mysql -uroot -phuawei
MariaDB [(none)]> change master to
master_host='10.1.8.10',
master_user='repl',
master_password='Repl@123456',
master_log_file='mysql-bin.000001',
master_log_pos=548;
# 启动从库同步线程
MariaDB [(none)]> start slave;
# 查看从库同步状态
MariaDB [(none)]> show slave status\\G

关键状态字段查看：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# 两个状态全部Yes代表同步正常

5.6 主从同步验证测试

在主库创建测试库

登录从库查看，自动同步出新建数据库

六、总结

MariaDB作为兼容MySQL的开源数据库，是运维、后端开发必备基础组件。本文完整覆盖单机部署安全加固、全量SQL语句、账号权限、自定义配置、一主一从主从复制整套实操，所有命令可直接在CentOS7复现，适合数据库入门学习、线上环境部署参考。