Linux中mariadb的安装及使用

1.1、相似点：
​ 由于 MariaDB 是 MySQL 的分支，因此这两个关系数据库管理系统有许多相似之处。例如，MariaDB 保留了 MySQL 的结构、命名约定和数据定义文件。此外，它还支持所有 MySQL 连接器、连接和端口。所以 MySQL 客户端包在 MariaDB 中可以正常工作。

ACID 合规性    原子性、一致性、隔离性和持久性 (ACID) 是确保数据库事务可靠性的四个核心原则。MySQL 和 MariaDB 都遵循这些原则。这两种数据库都通过遵循 ACID 来保持数据的准确性和完整性。
SQL 兼容性    MySQL 和 MariaDB 都是将数据整理到表中的关系数据库。MariaDB 和 MySQL 都使用 SQL 来管理和查询数据。您可以在这些系统之中使用许多相同的命令。
开源软件    作为开源关系数据库管理系统，MySQL 和 MariaDB 都是开发人员社区共同努力的结果。两者的源代码都向公众公开。MySQL 数据库有一个完全开源的版本，根据通用公共许可证 (GPL) 发布。它还具有一个付费的企业版本，该版本带有附加功能和支持。MariaDB 在 GitHub 上是完全开源的。
安全性    MySQL 和 MariaDB 提供类似的基本安全功能。它们提供加密功能、访问控制机制、用户身份验证和授权功能以及 SSL/TLS 支持。它们还允许进行精细的访问控制，让您可以为不同的用户提供不同的权限级别。
1.2、主要差异：
MySQL    MariaDB
JSON    MySQL 将 JSON 报告存储为二进制对象。    MariaDB 将 JSON 报告存储在字符串中。MariaDB 的 JSON 数据类型是 LONGTEXT 的别名。
Oracle 数据库兼容性    MySQL 具有很高的兼容性，但不支持 PL/SQL。    MariaDB 具有很高的兼容性，自 10.3 版本起支持 PL/SQL。
速度和性能    在复制和查询方面，MySQL 比 MariaDB 稍慢一些。    在复制和查询方面，MariaDB 比 MySQL 稍快一些。
功能    MySQL 支持超级只读函数、动态列和数据掩码。    MariaDB 支持隐形列和临时表空间。
身份验证    MySQL 有 validate_password 组件。    MariaDB 有三个密码验证器组件。
加密    MySQL 数据库使用 InnoDB 和 AES 对静态数据进行加密。    MariaDB 支持临时日志加密和二进制日志加密。
存储引擎    MySQL 的存储引擎比 MariaDB 少。    MariaDB 的存储引擎比 MySQL 多，可以在一个表中使用多个引擎。
许可证    MySQL 有两个版本：MySQL 企业版和 GPL 版本。    MariaDB 完全采用 GPL 版本。
线程池    MySQL 企业版带有线程池。    MariaDB 可以同时管理超过 20 万个连接，比 MySQL 更多。
2、安装Mariadb
2.1、使用命令安装mariadb
yum -y install mariadb-server mariadb
1
出现complete即下载完成

开启mariadb服务并且使其开机自启动

systemctl start mariadb
systemctl enable mariadb
systemctl status mariadb

2.2、mariadb初始化
输入命令初始化mariadb

mysql_secure_installation
1
输入当前密码，初次安装没有密码，直接回车就行

是否为root设置新密码，选择y

输入新密码并确认

是否移除匿名用户，随意，建议y

是否拒绝远程登录，建议选n

是否删除test库，随意选择

重新加载权限表：y

3、mariadb数据库基础操作
mysql -uroot -p

输入刚设置的密码后进入数据库

3.1、用户
创建一个名为admin的本地用户，密码为123

create user shihk@localhost identified by '123';

授予该用户所有权限

GRANT ALL PRIVILEGES ON my_database.* TO 'shihk'@'localhost';

查看所有用户

SELECT host,user,select_priv FROM mysql.user;    ————select_priv为是否存在可查询权限

删除用户

DROP USER 'username'@'host';    ————username为用户名称，————host为本地访问，可以更改为%，意为任意访问

3.2、数据库
创建test数据库

CREATE DATABASE test;

进入test数据库

use test;

删除数据库

DROP DATABASE IF EXISTS 'database';    ————database为数据库名称，'IF EXISTS'指在删除前检查数据库是否存在

3.3、数据表
创建数据表s001,s002,并设置id为主字段且自增（AUTO_INCREMENT PRIMARY KEY）。

CREATE TABLE s001 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

CREATE TABLE s002 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

查询所有数据库中存在的表

show tables;

删除数据表

DROP TABLE '数据表';

3.3.1、数据表基础操作
#插入数据    ————INSERT INTO 数据表 (字段名，字段名……) VALUES( ),( ),( )……;
INSERT INTO s002 (name, age) VALUES ('Dlice', 30), ('Eob', 22), ('Farol', 28);
#查询数据    ————SELECT 字段 FROM 数据表;  查询全部字段用'*'
SELECT * FROM s002;

关联查询多张表表内容

#SELECT column1, column2, ... FROM table1
 ->UNION
 ->SELECT column1, column2, ... FROM table2;
#column1, column2, ... 表示要选择的列，table1 和 table2 分别是要查询的表,全部查询用'*'。

#查询表s001，s002中name和age列内容
SELECT name,age FROM s001 UNION SELECT name,age FROM s002;

#删除数据    ————DELETE FROM 数据库 WHERE 字段 = ' ';
DELETE FROM s002 WHERE id = 3;

3.3.2、主外键设置
​ 创建表s003并设置int型字段id，为自增主字段；int型字段s002_id为外键，绑定s002中的id，使s002删除数据，s003也同步删除

CREATE TABLE s003 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    s002_id INT,
    other_column VARCHAR(50),
    FOREIGN KEY (s002_id) REFERENCES s002(id) ON DELETE CASCADE
);

3.4、存储过程与触发器
3.4.1、存储过程设置
创建存储过程，使在s002中插入数据时，s003同时插入

#存储过程的前提是两表之间有主外键关系
DELIMITER //
CREATE PROCEDURE InsertIntoS002AndS003(IN name_val VARCHAR(50), IN age_val INT)
BEGIN
    DECLARE new_id INT;

    INSERT INTO s002 (name, age) VALUES (name_val, age_val);

    SET new_id = LAST_INSERT_ID();

    INSERT INTO s003 (s002_id, other_column) VALUES (new_id, CONCAT('Data for s002_id ', new_id));
END;
//

使用存储过程

CALL InsertIntoS002AndS003('name', age);

ps：存储过程类似于编程语言中的函数，需要主动去调用

3.4.2、触发器设置
创建触发器，在s004中插入数据时，s005同时插入

DELIMITER //
CREATE TRIGGER InsertIntoS005OnS004Insert

#insert意为在表s004插入后触发，修改为updata，删除为delete
AFTER INSERT ON s004
#设定触发范围（每插入一行都触发）
FOR EACH ROW
BEGIN
    INSERT INTO s005 (s004_id, other_info)
    VALUES (NEW.id, CONCAT('Other info for Item ', NEW.id));
END;
//
DELIMITER ;

插入数据测试

INSERT INTO s004 (name, description) VALUES ('Item 1', 'Description for Item 1');

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZqX6lQd7-1692667071700)(https://yw-linux-1318457786.cos.ap-nanjing.myqcloud.com/QQ%E6%88%AA%E5%9B%BE20230817113559.png)]

3.4.3、总结
触发器和存储过程的区别：

执行时机：

触发器：自动在特定数据库操作前后执行，不需要手动调用，通常用于数据完整性、审计等需求。

存储过程：手动调用执行，根据需要在任何时候执行，通常用于封装业务逻辑、复杂的数据操作。

用途：

触发器：用于处理数据层面的约束、关联操作、审计记录等。

存储过程：用于执行自定义的业务逻辑、数据操作、数据转换等。

复用性：

触发器：通常面向特定表和操作，较难在多个地方复用。

存储过程：可以在多个地方调用，提供更好的复用性。

​ 总体而言，触发器适用于在数据层面维护完整性和触发操作，存储过程可以参考编程语言中的函数，更适用于执行业务逻辑和封装可复用的操作。

4、备份数据库
4.1、编写数据库备份的脚本back-up-db.sh
#!/bin/bash

#备份地址（需要先创建指定目录）
backupdir=/root/sql/crontab/db/logs
#备份文件后缀时间
time=_`date +%Y_%m_%d_%H_%M_%S`
dbfile=db${time}.sql
dbuser=root
dbpassword=123

#-h 后面跟随mariadb服务端的ip地址
mysql -e "show databases;" -h192.168.18.150 -u${dbuser} -p${dbpassword}| grep -Ev "Database|information_schema|performance_schema|mysql|test" | xargs mysqldump -h192.168.18.150 -u${dbuser} -p${dbpassword} --databases > ${backupdir}/${dbfile}

#以下为单独手动备份
#mysqldump -h192.168.18.150 --port=3306 -uroot -p123 --lock-tables=0 --all-databases > /root/db-`date "+%Y-%m-%d-%H-%M-%S"`.sql

#删除7天之前的备份文件
find $backupdir -name "db*.sql" -type f -mtime +7 -exec rm -rf {} \; > /dev/null 2>&1

echo ${dbfile}" ok"

授予脚本可执行权限

chmod +x back-up-db.sh

4.2、授予数据库用户权限
进入mariadb数据库(-u后面选择要进入的用户)

mysql -uroot -p

授予该用户在192.168.18.150上有访问权限

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.18.150' IDENTIFIED BY '123' WITH GRANT OPTION;

刷新权限后退出数据库

FLUSH PRIVILEGES;
exit

执行back-up-db.sh脚本

./back-up-db.sh

显示数据库备份已经创建成功

5、设置定时任务
更改vim默认编辑器

export VISUAL=vim

编辑定时任务

#crontab -e

# 每天 15 点 45 执行
45 15 * * * /bin/bash /root/sql/crontab/db/back-up-db.sh

# 启动后执行
@reboot /bin/bash /root/sql/crontab/db/back-up-db.sh

运行效果如下

6、以备份来还原数据库内容
6.1、进入数据库后删除users表
#mysql -uroot -p
#use Test;
#DROP TABLE users
退出数据库
#exit

6.2、用备份数据还原数据库
root为账户，123为账户密码，Test为需要还原的数据库

/root/sql/crontab/db/logs/db_2023_07_19_15_45_01.sql为要导入的备份文件

mysql -uroot -p123 Test < /root/sql/crontab/db/logs/db_2023_07_19_15_45_01.sql

效果如下

图片转存中…(img-Kqpszazg-1692667071700)]

6、以备份来还原数据库内容
6.1、进入数据库后删除users表
#mysql -uroot -p
#use Test;
#DROP TABLE users
退出数据库
#exit

6.2、用备份数据还原数据库
root为账户，123为账户密码，Test为需要还原的数据库

/root/sql/crontab/db/logs/db_2023_07_19_15_45_01.sql为要导入的备份文件

mysql -uroot -p123 Test < /root/sql/crontab/db/logs/db_2023_07_19_15_45_01.sql