Linux-MariaDB数据库

Linux-MariaDB数据库

📖简介：CentOS7 ，完整讲解 MariaDB 部署安装、配置文件分层结构；基础 SQL 增删改查；数据库用户与精细化权限管理；逻辑 / 物理备份恢复；企业数据库主流架构；主从复制原理与实操。

一、数据库基础概念

数据库分类

1. 关系型数据库：使用二维表（类似 Excel 表格）存储数据，代表产品：MySQL / MariaDB、Oracle、PostgreSQL。
2. NoSQL 非关系型数据库：Not Only SQL，作为关系库补充，代表：Redis、MongoDB。

MariaDB 是 MySQL 的开源社区分支，完全兼容 MySQL 语法、API，CentOS7 默认数据库。

关系库组成层级：数据库database→ 多张数据表 table → 行（记录）、列（字段）。

• 行：一条完整记录
• 列：字段，定义数据类型

二、MariaDB 部署安装

1、安装软件包

# 安装服务端、客户端工具 [root@server ~]# yum install -y mariadb-server mariadb # 开机自启并且立刻启动服务 [root@server ~]# systemctl enable --now mariadb # 防火墙放行mysql 3306端口 [root@server ~]# firewall-cmd --permanent --add-service=mysql [root@server ~]# firewall-cmd --reload

2、安全初始化

安装完数据库默认存在匿名用户、test 测试库，执行安全加固脚本。

[root@server ~]# mysql_secure_installation

交互式操作要点：

1. 设置 root 管理员密码
2. 禁止 root 远程登录
3. 删除匿名账号
4. 删除 test 演示数据库

3、进程说明

• mysqld_safe：守护监控进程（root 运行），监控 mysqld 崩溃自动重启
• mysqld：数据库核心工作进程，使用 mysql 普通用户运行，保障安全

systemd 管理环境，systemd 直接接管守护逻辑，不一定看到 mysqld_safe。

查看进程树：

[root@server ~]# pstree | grep mysqld

4、配置文件分层结构

表格

文件路径	作用
/etc/my.cnf	主总配置，优先级最高，会加载/etc/my.cnf.d/*.cnf
/etc/my.cnf.d/server.cnf	服务端 mysqld 配置：端口、监听地址、数据目录、存储引擎
/etc/my.cnf.d/client.cnf	客户端配置：mysql 命令默认账号、密码、主机、提示符

server.cnf 常用参数，写在[mysqld]段落

[mysqld] port=3306 bind-address=0.0.0.0 # 监听所有网卡，允许远程访问 datadir=/var/lib/mysql default-storage-engine=InnoDB # skip-networking=1 # 开启后只允许本地socket连接，关闭TCP网络

修改配置后重启服务生效

[root@server ~]# systemctl restart mariadb

5、登录数据库

方式 1：本地 socket 登录

[root@server ~]# mysql -u root -p Enter password: MariaDB [(none)]>

方式 2：TCP 远程登录

[root@client ~]# mysql -u user -p'123' -h 10.1.8.11

方式 3：非交互执行 SQL，适合脚本

[root@server ~]# mysql -u root -p -e 'show databases;'

客户端配置简化登录

编辑/etc/my.cnf.d/client.cnf

[client] user=root password='你的密码' prompt="\\u@\\h [\\d]> "

保存后，直接输入mysql即可登录，无需账号密码。

三、基础 SQL 语句实操

SQL 关键字习惯大写；库名表名区分大小写；语句末尾分号;结束。

1、数据库库操作

-- 查看全部数据库 SHOW DATABASES; -- 切换使用某个数据库 USE inventory; -- 创建数据库 CREATE DATABASE webapp; -- 删除数据库（危险，全部数据丢失） DROP DATABASE webapp;

系统自带库说明

• mysql：系统库，保存用户账号、权限
• information_schema：元数据，保存表、字段信息
• performance_schema：性能监控数据

2、数据表操作

查看库里面的表

USE inventory; SHOW TABLES;

查看表结构

DESCRIBE product;

字段含义：

• Field：字段名
• Type：数据类型
• Null：是否允许为空
• Key：主键 PRI
• Extra：auto_increment 自增

CRUD 增删改查

查询 SELECT

-- 查询整张表全部内容 SELECT * FROM product; -- 只查询指定列 SELECT name,price,stock FROM product; -- WHERE条件过滤 SELECT * FROM product WHERE price > 100; -- 条件运算符 > < >= <= BETWEEN IN LIKE AND OR SELECT * FROM product WHERE id BETWEEN 1 AND 3; SELECT * FROM product WHERE name LIKE '%Server%'; -- 排序 ORDER BY desc降序 SELECT * FROM product ORDER BY price DESC; -- 聚合函数 avg max min sum count SELECT avg(price) FROM product; SELECT sum(stock) FROM product; -- GROUP BY分组统计 SELECT id_category,sum(stock) FROM product GROUP BY id_category; -- 多表关联查询 SELECT product.name,product.price FROM product,category WHERE product.id_category = category.id AND category.name='Servers';

插入 INSERT

INSERT INTO staff(id,name,age) VALUES (1,'zhangsan',22);

更新 UPDATE

⚠️不加 WHERE 条件，整张表全部更新，生产严禁！

UPDATE staff SET age=25 WHERE id=1;

删除 DELETE

⚠️不加 WHERE 条件，清空整张表！

DELETE FROM staff WHERE id=1;

创建表

CREATE TABLE staff( id INT(11) NOT NULL, name VARCHAR(100) NOT NULL, age INT(11) DEFAULT 10 );

删除表

DROP TABLE staff;

四、数据库用户与权限管理

MariaDB 用户和 Linux 系统用户相互独立，格式：用户名@访问主机user@'%'代表 user 可以从任意地址登录；user@localhost仅本地登录。

1、创建用户

CREATE USER user@'%' IDENTIFIED BY 'Redhat@123';

2、GRANT 授予权限

授权语法

GRANT 权限列表 ON 库名.表名 TO '用户'@'主机'; FLUSH PRIVILEGES; --刷新权限

权限作用范围：

1. *.*：全局所有库所有表（DBA 管理员）
2. webapp.*：webapp 库下面所有表
3. webapp.user：webapp 库中 user 单张表
4. 列级别：webapp.user(name,phone)只允许操作指定列

常用权限：SELECT查询，INSERT插入，UPDATE修改，DELETE删除，CREATE建库建表，DROP删除库表，ALL PRIVILEGES全部权限。

示例

-- 给user对inventory库全部读写权限 GRANT SELECT,INSERT,UPDATE,DELETE ON inventory.* TO user@'%'; FLUSH PRIVILEGES; -- 管理员全部权限 GRANT ALL PRIVILEGES ON *.* TO root@localhost WITH GRANT OPTION;

3、查看用户权限

SHOW GRANTS FOR user@'%';

4、回收权限 REVOKE

REVOKE DELETE ON inventory.* FROM user@'%'; FLUSH PRIVILEGES;

5、删除用户

DROP USER user@'%';

6、重置密码

root 忘记密码恢复步骤

1. 修改/etc/my.cnf.d/server.cnf，[mysqld]添加：skip-grant-tables=1
2. 重启 mariadb 服务

[root@server ~]# systemctl restart mariadb

1. 无密码登录，修改 root 密码

[root@server ~]# mysql -u root USE mysql; UPDATE user SET password=PASSWORD('新密码') WHERE user='root'; FLUSH PRIVILEGES; exit

1. 注释掉skip-grant-tables=1，重启数据库恢复权限校验。

五、数据库备份与恢复

两种备份类型

1. 物理备份：直接拷贝数据库原始数据目录/var/lib/mysql，速度快，需要停止数据库；跨系统兼容性差。
2. 逻辑备份 mysqldump：导出 SQL 文本，运行时备份，兼容性好，速度相对慢，生产最常用。

1、mysqldump 逻辑备份

# 备份单个库，不包含create database语句 [root@server ~]# mysqldump -u root -p inventory > /backup/inventory.sql # -B参数，备份同时包含创建数据库语句 [root@server ~]# mysqldump -u root -p -B inventory > /backup/inventory-b.sql # 备份全部所有库 [root@server ~]# mysqldump -u root -p -A > /backup/all-db.sql

2、逻辑备份恢复

# 方式一：shell重定向导入 [root@server ~]# mysql -u root -p inventory < /backup/inventory.sql # 方式二：mysql命令行source导入 [root@server ~]# mysql -u root -p MariaDB [(none)]> source /backup/inventory-b.sql;

注意：不带-B参数的备份文件，恢复前需要手动CREATE DATABASE并且USE数据库。

3、物理备份实操（停机拷贝）

# 停止数据库 [root@server ~]# systemctl stop mariadb # 拷贝数据目录 [root@server ~]# cp -a /var/lib/mysql /backup/mysql-backup # 恢复 [root@server ~]# rm -rf /var/lib/mysql [root@server ~]# cp -a /backup/mysql-backup /var/lib/mysql [root@server ~]# chown -R mysql:mysql /var/lib/mysql # 启动服务 [root@server ~]# systemctl start mariadb

六、MySQL/MariaDB 主从复制

作用：实现读写分离、数据备份；主库负责写入，从库同步主库数据，可以承担查询读流量。

复制原理流程

1. 主库 Master：客户端执行写操作，变更记录写入binlog 二进制日志。
2. 从库 Slave I/O 线程：连接主库，拉取 binlog 日志，写入本地 relay-log 中继日志。
3. 从库 SQL 线程：读取中继日志，重放 SQL，在从库复现数据变更。

复制三种模式

1. 异步复制（默认）：主库写完 binlog 直接返回客户端，不等从库确认；性能高，有数据丢失风险。
2. 半同步复制：主库等待至少一台从库收到 binlog 才返回；超时降级异步，兼顾安全和性能。
3. 全同步复制：等待所有从库执行完成，性能很差，原生不支持。

主从部署实操

环境规划

表格

主机	IP	角色
db1	10.1.8.11	master 主库
db2	10.1.8.12	slave 从库

①主库配置 /etc/my.cnf.d/master.cnf

[mysqld] server_id=1 log_bin=mysql-bin binlog_format=ROW relay_log=mysql-relay-bin binlog-ignore-db=information_schema binlog-ignore-db=performance_schema binlog-ignore-db=sys

重启服务

[root@db1 ~]# systemctl restart mariadb

创建复制账号，给从库使用

GRANT replication slave,replication client ON *.* TO repl@'10.1.8.12' IDENTIFIED BY 'Repl@123'; FLUSH PRIVILEGES; -- 查看主库binlog文件名和position偏移量，记录，后面从库要使用 SHOW MASTER STATUS\G

②从库配置 /etc/my.cnf.d/master.cnf

server_id 不能和主库重复

[mysqld] server_id=2 log_bin=mysql-bin binlog_format=ROW relay_log=mysql-relay-bin binlog-ignore-db=information_schema binlog-ignore-db=performance_schema binlog-ignore-db=sys

重启服务

[root@db2 ~]# systemctl restart mariadb

从库配置连接主库，填入主库 IP、repl 账号密码、binlog 文件名、position

CHANGE MASTER TO master_host='10.1.8.11', master_user='repl', master_password='Repl@123', master_port=3306, master_log_file='mysql-bin.000003', master_log_pos=327, master_connect_retry=30; -- 启动复制 START SLAVE; -- 查看复制状态 SHOW SLAVE STATUS\G

关键检查两项必须为 Yes：Slave_IO_Running: YesSlave_SQL_Running: Yes

③测试验证

主库创建库、插入数据，观察从库是否自动同步到数据。

主从常见问题

1. IO 线程 No：网络不通、防火墙拦截、账号密码错误、IP 不对。
2. SQL 线程 No：主从数据不一致、主键冲突；需要跳过冲突或者重新全量备份重建主从。
3. Seconds_Behind_Master代表主从复制延迟秒数。

七、企业数据库架构简单科普

1. 基础一主多从：简单，无自动故障切换；读请求分发从库，读写分离。
2. MHA：传统企业经典高可用，故障自动切换主库，成熟稳定。
3. MGR（MySQL Group Replication）官方组复制，新一代，强一致性。
4. 分库分表：单机 MySQL 写入存在上限，大流量业务做水平拆分，搭配 Redis 缓存扛读流量。

📖小结

1. MariaDB 是 MySQL 社区分支；配置文件分层：my.cnf总配置，my.cnf.d/*.cnf拆分服务端、客户端配置。
2. SQL：DDL 定义库表；DML 增删改查；DCL 用户授权；UPDATE/DELETE 不加 WHERE 会操作整张表，生产禁止。
3. 用户格式user@host，权限粒度分全局、库、表、列；最小权限原则，不要随便给 ALL PRIVILEGES。
4. 备份：mysqldump 逻辑备份导出 SQL；物理备份直接拷贝数据目录，需要停机。
5. 主从复制依靠 binlog+relay‑log；异步复制性能高，半同步提升数据安全；部署注意 server‑id 不能重复，Slave_IO_Running、Slave_SQL_Running必须全部 Yes 才算正常工作。