MariaDB 数据库搭建

这里主要采用 MariaDB 作为主要数据库选择, 且内部系统采用 Debian 系的服务器来利用 Apt 进行源安装.

不涉及所有手动编译, 手动编译很多人直接都不写系统脚本让系统托管, 所以尽量能用官方源托管就利用官方源, 当然如果能够自己手动配置本地源更好.

重要: 如果完全不懂系统脚本编写, 切忌不要抄网上所有自己手动编译的教程, 直接 touch 后台托管危害是十分大的.

源安装命令

一般 Linux 发行版都内置了 MariaDB 的系统源仓库, 如果没有的需要配置仓库文件( 这个问题基本集中在 RHEL 系的服务器 ).

这里可以参考官方文档配置: 官方文档

这里直接配置 Debian11 的源( MariaDB 10.7 ):

1
2
3
sudo apt-get install -y software-properties-common dirmngr apt-transport-https
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
sudo add-apt-repository 'deb [arch=amd64,i386,arm64,ppc64el] https://mirrors.aliyun.com/mariadb/repo/10.7/debian bullseye main'

此时源配置已经完成, 就可以准备安装服务:

1
2
sudo apt-get -y update
sudo apt-get -y install mariadb-server

服务会自动配置到系统之中, 直接采用系统去调取服务:

1
2
3
4
sudo systemctl status mariadb.service # 查看系统管理状态
sudo systemctl start mariadb.service # 启用 MariaDB 服务
sudo netstat -tulpn|grep -E 'mysql|3306' # 查看系统是否启用了 mysql 的3306 端口服务
sudo systemctl enable mariadb.service # 设置开机自动启用 MariaDB 服务

没有其他报错信息, 现在系统服务已经挂起, 后续就是配置账号权限.

注意默认监听端口为 3306

账号配置

目前服务器虽然成功挂起, 但是还是需要配置内部的管理账号, 同时这里默认 root 账号管理.

这里需要设置 root 账号可以被外部其他客户端远程访问( 正式环境不允许 ), 并且创建 api 数据库和账号用于单独管理.

官方已经自带了初始化脚本命令:

1
2
3
4
5
6
7
8
9
sudo mysql_secure_installation # 调用官方初始化脚本
# 这里需要弹出需要输入命令
# Enter current password for root (enter for none)[设置目前的 root 数据库账号密码]:
# Switch to unix_socket authentication [Y/n][设置账号的系统, UnixSocket(Y)和Native(N), 默认 UnixSocket 不允许远程访问 ]:
# Change the root password? [Y/n][是否重新设置 root 数据库密码]:
# Remove anonymous users? [Y/n][是否删除掉内部匿名账号, 推荐删除]:
# Disallow root login remotely? [Y/n][是否关闭掉 root 账号的远程访问, 如果单机正式服推荐直接关闭(Y), 剩下其他情况比较复杂得按需配置]:
# Remove test database and access to it? [Y/n][是否删除系统内部测试数据库, 推荐删除]:
# Reload privilege tables now? [Y/n][是否直接重新装载权限表, 建议更新权限]:

一切完成之后就完成配置, 这里需要说明数据库账号验证体系 unix_socket 和 native:

  • unix_socket: 只允许本地 unix_domain 方式来访问而非 TCP 方式, 这里方式默认是无法进行远程通信, 只允许对
    xxx.socket 进行访问, 安全性最高但是不利于集群读写负载.
  • native: 常用的配置方式, 采用 TCP + 账号密码|证书访问模式.

如果开发环境需要的就是 root 账号切换成 native 方式方便其他客户端进行访问.

这里直接登录数据库查看目前配置:

1
mysql -uroot -p # 之后输入密码进入

后续在数据库内部查看配置:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
select Host, User, plugin
from mysql.user;
#
查看目前的用户
# 这里默认切换是 native 访问模式:
#+-----------+-------------+-----------------------+
#| Host      | User        | plugin                |
#+-----------+-------------+-----------------------+
#| localhost | mariadb.sys | mysql_native_password |
#| localhost | root        | mysql_native_password |
#| localhost | mysql       | mysql_native_password |
#+-----------+-------------+-----------------------+
 
# 之后就是创建远程管理账户, 这里密码最好采用 MD5 加密之后处理
CREATE
USER meteorcat IDENTIFIED BY 'cc6d8ee8b4b1c5696e5dbcb82e6329ff';
 
#
创建之后账号是无法直接使用的, 需要对账号进行授权(增删改查切换库等权限), 密码基本上保持上面一致
GRANT ALL PRIVILEGES ON *.* TO 'meteorcat'@'%' IDENTIFIED BY 'cc6d8ee8b4b1c5696e5dbcb82e6329ff' WITH GRANT OPTION;
 
#
完成之后重新刷新权限即可
FLUSH PRIVILEGES;

之后就可以进行权限连接访问, 这里提供命令行技巧确认是否联通:

1
2
# 可以直接利用 -h 强制指定外网或者局域网的IP, 这也就会被识别成外部访问
mysql -h 192.168.0.8 -umeteorcat -p

如果直接访问到这里也没办法访问, 那么最好查看下是否系统强制只允许 127.0.0.1 访问:

1
2
3
mysqladmin variables |grep 'bind_address'
# 如果内部打印如下, 就说明系统默认只给 127.0.0.1 本地访问:
#  bind_address | 127.0.0.1

如果只允许 127.0.0.1 访问, 这时候就需要开放公网/局域网进行访问( 这里采用公网暴露方式, 正式一般都是设置内网局域网 ):

1
2
3
4
5
6
7
8
9
# 一般 MariaDB 配置文件和 Mysql 放置位置不一样需要自己查找, 这里本地直接定位找到:
sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf
 
# 注释修改的配置
# bind-address            = 127.0.0.1
bind-address		  = 0.0.0.0
 
# 重新启动数据库服务, 再次进行访问即可
sudo systemctl restart mariadb.service

如果 Window 系统想访问建议安装官方提供的会话管理器 - HeidiSQL

基本上这样就能创建允许远程维护的系统 MariaDB 管理账号.

性能调优

建议依据服务器配置来调整, 不要盲目进行配置, 以下配置有些需要审慎开启都会进行标注说明:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
[mysqld]
# 配置项默认放置于 `/etc/mysql/mariadb.conf.d/50-server.cnf`
 
#
# * 基础配置
#
pid-file = /run/mysqld/mysqld.pid
basedir = /usr
tmpdir = /tmp
 
# 如果外加硬盘扩展, 需要把数据库放置到其他硬盘则需要将该配置写成扩展盘对应路径
datadir = /var/lib/mysql
 
# 如果访问数据库配置采用 IP 访问最好启用该项, 有的数据库采用域名访问所以需要利用DNS解析, 注释该项就会直接访问的时候请求DNS获取真实地址
skip-name-resolve
 
# 绑定访问本地地址, `0.0.0.0` 代表全暴露, `127.0.0.1` 代表只允许本地
bind-address = 0.0.0.0
 
# 设置服务器默认的字符集
character_set_server = utf8mb4
collation-server = utf8mb4_unicode_ci
 
# 设置默认的数据库引擎
default-storage-engine = InnoDB
 
# 最大等待连接, 当新请求超过该值则会等待前面连接请求释放才允许连接
# 每个连接都会占用 256kb, 默认设置为 50, 如果服务(PHP/JAVA) 没有线程池|长连接需要稍微提高
# 建议采用设置 256 之后进行微调. 该值实际上配置方法也是有公式的, 可以用以下命令行执行获取:
# sysctl -n net.ipv4.tcp_max_syn_backlog # 获取该值之后除以2
# 如果该值低于 1024 则需要对系统内核对应来配置再进行数据库该值配置
back_log = 256
 
# 最大请求连接数, 主要并发请求连接数, 如果并发很高的服务需要对应调高该值
# 默认系统该值为 151, 数据库都会针对每个连接申请内存空间, 调高会相应消耗更高内存, 最大连接数不要超过 16384
# 这个值需要根据服务器内存和请求计算, 没有统一的计算公式
max_connections = 2048
 
 
#
# * 缓存配置
#
 
# 索引缓冲区的容量, 随着缓冲区增大可以更好处理那些进行过索引的数据
# 该值配置主要针对 MyISAM 引擎的表, 过大会导致索引查找之后速度反而过慢
# 一般可以套取计算公式配置, 直接采用内存值来计算, 假设 8G(8192M) 内存服务器:
# 8192 * 9.5% = 778 ( 设置的值, 硬套公式即可 )
# 这里开发机只分配了 1G 所以该值相对小
key_buffer_size = 97M
 
# 索引缓冲区的容量, 这里和上面功能类似但是主要针对的是 InnoDB 引擎
# 默认该值为 128M, 但是这个配置涉及的方面比较多, 该值实际需要外加 8% 开销来处理数据结构等信息, 同时还有另外 12% 开销进行保存恢复信息
# 所以该值需要外加 21% 进行考虑, 也就是以2G(2048M)内存设置为例:
# (2048 * 21%) + 2024 = 2454M( 这里就是最后内存会总的占用数额 )
# 这个值和 MyISAM 引擎不一样, 如果配置过大方便将其数据信息放置内存, 也就是直接从内存取完整数据, 速度不走磁盘IO所以极快.
# 如果服务器仅仅作为数据库服务器, 考虑可以把内存全部移交处理, 这里开发机器 1G 分配给他 768M 进行处理
innodb_buffer_pool_size = 768M
 
# InnoDB 存储引擎的事务日志缓冲区, 一般事务日志启用为了性能都是直接写入缓冲区而非直接文件落地, 默认8M
# 一般来说事务最好保持在 4~8M 之中, 没必要进行太多大改, 设置保持系统默认都可.
# 不建议对其进行改动, 保持默认即可, 性能瓶颈主题并不是涉及对其处理
innodb_log_buffer_size = 8M
 
# 查询结构缓冲区, 只针对 select 的时候, 当语句命中系统 Hash 算法就会以字符串 hash 语句从而直接返回结构集而去解析查询.
# 该参数官方说明配置得当能够提升238%的查询效率, 但是如果表修改|变动频繁会导致缓存失效同时频繁去生成缓存.
# 默认配置 32M, 但是最大配置值最好不要超过 512M 避免大量生成无意义缓存
# 大型服务建议取 256M 即可, 如果是大型的静态查询而不会涉及删除/修改的时候, 可以将值适度提高; 对于小中等服务, 可以考虑保持 32/48M 切换.
query_cache_size = 32M
 
 
#
# * 查询配置
#
 
# 单个连接的查询缓冲区, 默认值为2M
# 该值针对数据库表的顺序扫描生成多个缓冲区, 该值为动态值会不断按照系统查询需求申请扩大
# 该值会按照数据库请求变动所以基本上不需要怎么处理, 建议 2/4/8M 对应小/中/大型服务器系统处理, 需要留意每个连接请求内存的消耗避免直接内存不足
read_buffer_size = 2M
 
# 单个连接的排序缓冲区, 默认值为2M
# 该值是语句执行之后排序缓冲区容量, 建议该值也和 read_buffer_size 一样采用三值选择
sort_buffer_size = 2M
 
# 单个连接的临时表空间, 默认值为16M
# 该值可以有效提升 MyISAM 引擎表的JOIN查询相关, JOIN 查询会生成大量临时表.
# 该值基本保持默认即可, 实际业务上面 JOIN 需求不多, 一般要么外部缓存( Redis/Mongo ), 要么拆分查询利用索引加速( 不追求一次全查而使用多次查询利用索引加速 )
tmp_table_size = 16M
 
 
#
# * 缓存配置
#
 
# 所有线程打开的表数量, 注意该值受到文件描述符影响, 默认值为 512
# 该值不允许超过系统内核的文件描述符, 查询系统文件描述符数量可以用以下命令:
# ulimit -n # 获取的值为最大文件描述符
# 该值越大可以让数据库表能够对请求响应速度越快, 也能有效提高请求访问的执行速度
# 实际上这里有套规范就是 2G 内存以下的设备该值取 256~512, 当超过 4G 内存附近取值 1024~2048, 以此不断类推追加该值.
table_open_cache = 512
 
# * 慢查询
#
 
# 相比较性能稍微损失, 慢日志日志用来排查语句性能值得牺牲
slow_query_log = On
slow_query_log_file = mariadb_slow_query.log
log_queries_not_using_indexes = On # 只记录没有触发索引的查询

以上就是比较推荐设置的系统调优值, 剩下的最好认真学习运维和数据库知识再去进行微调, 内部涉及很多知识点.

备份与还原

官方默认已经提供的系统导出工具, 但是这里需要说下导出细节:

  • 不要在请求热门时间进行导出, 一般采用凌晨三四点这段冷门时间导出最佳.
  • 不要保存同个磁盘, 如果云服务器建议扩展第二硬盘来保存快照备份.
  • 千万注意硬盘容量必须是大于 (数据表大小 * 80%) + 数据表大小; 因为会先导出表结构, 之后数据最后打包成压缩压缩包后删除多余文件,
    其中生成的文件特别多.

这里没有进行扩展盘所以采用本地备份, 实际上最好采用扩展盘放置.

常见命令导出格式如下:

1
2
3
4
5
6
7
# 导出整个数据库
# `--add-drop-table` 命令指名导出时候带有 DROP TABLE 语句清空原来的数据
# `--default-character-set=utf8mb4` 命令指定导出的字符集
mysqldump -u 数据库账号 -p [数据库密码,可有可无] --add-drop-table --default-character-set=utf8mb4 数据库名 > 导出文件名.sql
 
# 导出数据库并压缩数据
mysqldump -u 数据库账号 -p --add-drop-table --default-character-set=utf8mb4 数据库名 | gzip > 导出文件名.gz

导出数据单位是以库单位, 所以这里只列举单个数据库导出而不列举其他, 这里测试上面导出系统 mysql 数据库:

1
2
3
4
5
# 这里直接展示创建的账号导出数据库, 同时附带了密码直接导出
mysqldump -u meteorcat -p'cc6d8ee8b4b1c5696e5dbcb82e6329ff' --add-drop-table --default-character-set=utf8mb4 mysql > /tmp/mysql.sql
 
# 这里采用数据导出并进行压缩
mysqldump -u meteorcat -p'cc6d8ee8b4b1c5696e5dbcb82e6329ff' --add-drop-table --default-character-set=utf8mb4 mysql | gzip > /tmp/mysql.gz

后续就是还原数据库的数据, 直接命令行执行即可:

1
2
3
4
5
# 注意这里采用 mysql 而非 mysqldump
mysql -u 数据库账号 -p [数据库密码,可有可无] --default-character-set=utf8mb4 数据库名 < 导出文件名.sql
 
# 压缩文件还原进数据库
gunzip < 导出文件名.gz | mysql -u 数据库账号 -p [数据库密码,可有可无] --default-character-set=utf8mb4 数据库名

这里采用上面备份的数据进行还原处理:

1
2
3
4
5
# 直接文件还原数据
mysql -u meteorcat -p'cc6d8ee8b4b1c5696e5dbcb82e6329ff' --default-character-set=utf8mb4 mysql < /tmp/mysql.sql
 
# 压缩文件解压获取数据并还原数据
gunzip < /tmp/mysql.gz | mysql -u meteorcat -p'cc6d8ee8b4b1c5696e5dbcb82e6329ff' --default-character-set=utf8mb4 mysql

后续可以尝试自己编写脚本定时保存数据库.

定时脚本

这里采用系统内部的 Systemctl 编写系统脚本, 用于统一的备份脚本, 计划直接采用的备份命令类似如下:

1
sudo systemctl start mariadb-backup@数据库名

这样直接将数据库保存到指定位置去, 生成 数据库名_时间戳.gz, 同时需要独立单个账号只允许本地服务器进行执行.

1
2
3
4
5
6
7
8
9
10
11
12
13
#
创建本地权限账号
CREATE
USER 'backup'@'localhost' IDENTIFIED BY 'cc6d8ee8b4b1c5696e5dbcb82e6329ff';
 
#
授予需要的权限, 注意这里授予本地访问
GRANT ALTER
, INSERT, LOCK TABLES, SHOW VIEW, SELECT, UPDATE ON *.* TO 'backup'@'localhost' IDENTIFIED BY 'cc6d8ee8b4b1c5696e5dbcb82e6329ff' WITH GRANT OPTION;
 
#
重新装载权限
FLUSH PRIVILEGES;

这里有个知识点就是利用系统配置文件: /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf 的 [mysqldump] 配置块.

这里编辑客户端配置文件 50-mysql-clients.cnf:

1
2
3
4
[mysqldump]
# 设置默认的 MYSQLDUMP 默认执行账号
user = backup
password = cc6d8ee8b4b1c5696e5dbcb82e6329ff

这里默认 mysqldump 就会调用到该账号, 这里测试直接导出数据是否可以直接允许导出:

1
2
# 可以看到直接不设置账号密码来处理, 直接就能导出数据
mysqldump --add-drop-table --default-character-set=utf8mb4 mysql > /tmp/mysql.sql

这时候如果没问题能够正常导出的话就说明默认本地账户思路是正确的, 这时候就考虑编写系统服务.

创建系统服务文件:

1
2
# 创建 mariadb-backup@.service 系统文件
sudo vim /lib/systemd/system/mariadb-backup@.service

系统配置内容如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
# MariaDB Backup Service
# @Author: MeteorCat
#
# MariaDB-based database backup system services
#
[Unit]
Description=MariaDB database backup( Database = %I )
Documentation=https://mariadb.org/
After=syslog.target
After=mysql.service
 
 
# 这里编写本地的备份路径, 确保该目录创建并且授权给 mysql:mysql
ConditionPathIsDirectory=/backup
 
[Service]
# 执行系统类型, 这里采用单次执行类型
Type=oneshot
RemainAfterExit=no
 
# 执行的权限用户
User=mysql
Group=mysql
 
# 启动命令
ExecStart=/usr/bin/mysqldump --add-drop-table --default-character-set=utf8mb4 %I
StandardOutput=file:/backup/%I.sql
StandardError=file:/backup/%I_error.sql
 
# 移动成带有时间后缀的文件
ExecStartPost=/usr/bin/sh -c "exec /usr/bin/mv -f /backup/%I.sql /backup/%I_`/usr/bin/date +%%Y%%m%%d`.sql"
 
# 剩下的启动配置
PrivateTmp=false
 
[Install]
WantedBy=multi-user.target

之后重新更新系统的注册服务并且创建备份目录执行备份:

1
2
3
sudo systemctl daemon-reload # 更新系统服务
sudo mkdir /backup # 创建备份目录
sudo chown -R mysql:mysql /backup # 授予备份目录权限

测试系统备份服务:

1
2
# 直接备份数据库 mysql 到本地保存
sudo systemctl start mariadb-backup@mysql.service

如果没问题生成了 备份文件.sql 那就是已经完成系统备份, 接下来就是编写每天/每周定时服务来执行, 采用 Systemctl
的定时器来触发:

1
2
# 创建系统定时服务
sudo vim /lib/systemd/system/mariadb-backup@.timer

定时器内容如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[Unit]
Description=MariaDB-based database backup every hour
 
[Timer]
# 这里先用于本地测试尝试每分钟跑一次能够正确执行
OnCalendar=*-*-* *:*:00
 
# 真实环境备份, 每周一凌晨三点运行一次
# OnCalendar=Mon --* 04:00:00
 
# 对应的系统服务启动
Unit=mariadb-backup@%I.service
 
[Install]
WantedBy=multi-user.target

这样定时器也完成编写, 加载系统服务并且确实定时脚本:

1
2
3
4
sudo systemctl daemon-reload # 更新系统服务
sudo systemctl start mariadb-backup@mysql.timer # 开启定时器
sudo systemctl list-timers # 查看所有的定时器
sudo systemctl enable mariadb-backup@mysql.timer # 定时器设置开机启动

确认备份会被定时触发, 这样就能看到备份数据库是否正常.