数据库配置

数据库是ERP系统的核心组件，其性能、可靠性和安全性直接影响整个系统的运行质量。本文介绍ERP系统的数据库配置方法，包括数据库选择、安装、优化及维护等内容。

数据库选型

支持的数据库系统

ERP系统支持以下数据库：

MariaDB（推荐，MySQL的增强分支）
MySQL
PostgreSQL（适用于大规模部署和高级查询需求）

各数据库系统比较：

特性	MariaDB	MySQL	PostgreSQL
兼容性	最佳	良好	良好
性能	高	高	高
功能丰富度	高	中	最高
社区支持	活跃	活跃	活跃
事务支持	完整	完整	完整
扩展性	良好	良好	最佳

推荐选择：对于大多数部署场景，推荐使用MariaDB 10.6或以上版本。

数据库服务器配置

硬件推荐

数据库服务器硬件配置建议：

企业规模	CPU	内存	存储	RAID级别
小型（<50用户）	4核心	16GB	500GB SSD	RAID 1
中型（50-200用户）	8核心	32GB	1TB SSD	RAID 10
大型（>200用户）	16核心+	64GB+	2TB+ SSD	RAID 10

存储配置

表空间与日志分离：将数据文件和日志文件放在不同的物理磁盘上
使用SSD：至少将日志文件放在SSD上，提高写入性能
RAID配置：使用RAID 10提供最佳性能和冗余保护

数据库安装与基础配置

MariaDB安装（Ubuntu为例）

安装MariaDB服务器

bash

sudo apt update
sudo apt install -y mariadb-server

配置安全选项
bash
```
sudo mysql_secure_installation
```
1
按照提示设置:
- 设置root密码
- 删除匿名用户
- 禁止root远程登录
- 删除测试数据库
- 重新加载权限表

创建数据库和用户

sql

CREATE DATABASE erp_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'erp_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON erp_database.* TO 'erp_user'@'localhost';
FLUSH PRIVILEGES;

MariaDB基础配置

编辑MariaDB配置文件:

bash

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

添加或修改以下配置:

ini

[mysqld]
# 基本设置
bind-address = 0.0.0.0  # 允许远程连接，生产环境建议使用内网IP
max_connections = 300  # 最大连接数
max_allowed_packet = 64M  # 最大允许数据包大小

# 缓存设置
innodb_buffer_pool_size = 4G  # 设置为服务器内存的50%-70%
innodb_buffer_pool_instances = 4  # 多个缓冲池实例减少争用
query_cache_size = 32M  # 查询缓存大小
query_cache_limit = 2M  # 单个查询最大缓存

# 日志设置
slow_query_log = 1  # 启用慢查询日志
long_query_time = 2  # 超过2秒的查询记录到慢查询日志
log_error = /var/log/mysql/error.log
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
expire_logs_days = 7  # 二进制日志保留7天

# InnoDB设置
innodb_file_per_table = 1  # 每个表使用单独的表空间文件
innodb_flush_log_at_trx_commit = 1  # 每次事务提交时写入磁盘，确保数据安全
innodb_flush_method = O_DIRECT  # 直接I/O，绕过操作系统缓存
innodb_log_file_size = 512M  # 日志文件大小
innodb_log_buffer_size = 16M  # 日志缓冲区大小

# 字符集设置
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

重启MariaDB服务:

bash

sudo systemctl restart mariadb

PostgreSQL安装与配置

如果选择PostgreSQL：

安装PostgreSQL

bash

sudo apt update
sudo apt install -y postgresql postgresql-contrib

创建数据库和用户

bash

sudo -u postgres psql

sql

CREATE DATABASE erp_database WITH ENCODING 'UTF8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8';
CREATE USER erp_user WITH PASSWORD 'strong_password';
GRANT ALL PRIVILEGES ON DATABASE erp_database TO erp_user;
\q

配置PostgreSQL

bash

sudo nano /etc/postgresql/13/main/postgresql.conf

修改以下配置：

listen_addresses = '*'  # 允许远程连接
max_connections = 300
shared_buffers = 4GB  # 内存的25%
work_mem = 32MB
maintenance_work_mem = 256MB
effective_cache_size = 12GB  # 内存的75%
wal_buffers = 16MB
checkpoint_timeout = 15min
max_wal_size = 2GB

配置客户端认证

bash

sudo nano /etc/postgresql/13/main/pg_hba.conf

添加或修改：

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.0.0/16          md5  # 允许内网连接，根据实际情况调整

重启PostgreSQL服务
bash
```
sudo systemctl restart postgresql
```
1

数据库性能优化

索引优化

分析查询性能

sql

-- MariaDB/MySQL
EXPLAIN SELECT * FROM your_table WHERE your_column = 'value';

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM your_table WHERE your_column = 'value';

识别需要索引的列
- 经常用于WHERE条件的列
- 经常用于JOIN条件的列
- 经常用于ORDER BY排序的列

创建必要的索引

sql

-- 创建单列索引
CREATE INDEX idx_column_name ON table_name(column_name);

-- 创建复合索引
CREATE INDEX idx_multi_column ON table_name(column1, column2);

查询优化

**避免使用SELECT ***，只选择需要的列
使用适当的WHERE条件限制结果集
限制结果集大小，使用LIMIT
优化JOIN操作，确保JOIN字段已建立索引
避免在大表上使用ORDER BY，如必须使用确保排序字段已建立索引

配置优化

内存分配优化:

确保InnoDB缓冲池大小设置合理，通常为系统内存的50%-70%
对于PostgreSQL，调整shared_buffers和effective_cache_size

并发优化:

监控max_connections设置，避免连接数超限
优化线程池大小

数据库备份策略

备份方法

逻辑备份（推荐用于小型数据库）

bash

# MariaDB/MySQL
mysqldump -u root -p --single-transaction --routines --triggers --events erp_database > erp_backup_$(date +%Y%m%d).sql

# PostgreSQL
pg_dump -U postgres -d erp_database -F c -f erp_backup_$(date +%Y%m%d).dump

物理备份（推荐用于大型数据库）

bash

# 使用Percona XtraBackup（MariaDB/MySQL）
xtrabackup --backup --target-dir=/backup/full-backup-$(date +%Y%m%d)

# 使用pg_basebackup（PostgreSQL）
pg_basebackup -D /backup/full-backup-$(date +%Y%m%d) -F t -X fetch -z -P

备份计划

建议的备份计划:

每日增量备份
每周完整备份
定期验证备份可恢复性
将备份存储在异地或云存储

自动化备份脚本

创建自动备份脚本 /usr/local/bin/db_backup.sh:

bash

#!/bin/bash
# 数据库备份脚本

# 配置
BACKUP_DIR="/backup/mysql"
DB_USER="root"
DB_PASS="your_password"
DB_NAME="erp_database"
DATE=$(date +%Y%m%d)
KEEP_DAYS=7

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
mysqldump -u $DB_USER -p$DB_PASS --single-transaction --routines --triggers --events $DB_NAME | gzip > $BACKUP_DIR/$DB_NAME-$DATE.sql.gz

# 删除旧备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +$KEEP_DAYS -delete

# 备份成功通知
echo "Database backup completed: $BACKUP_DIR/$DB_NAME-$DATE.sql.gz"

设置执行权限：

bash

sudo chmod +x /usr/local/bin/db_backup.sh

添加到crontab执行计划：

bash

sudo crontab -e

添加每天午夜执行：

0 0 * * * /usr/local/bin/db_backup.sh > /var/log/db_backup.log 2>&1

数据库高可用设置

主从复制（MariaDB/MySQL）

主服务器配置

编辑主服务器配置:

bash

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

添加或修改:

ini

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
binlog_format = ROW
expire_logs_days = 7

重启MariaDB:

bash

sudo systemctl restart mariadb

创建复制用户:

sql

CREATE USER 'replicator'@'%' IDENTIFIED BY 'strong_repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

从服务器配置

编辑从服务器配置:

bash

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

添加或修改:

ini

[mysqld]
server-id = 2
relay_log = /var/log/mysql/relay-bin
relay_log_index = /var/log/mysql/relay-bin.index
read_only = ON

重启MariaDB:

bash

sudo systemctl restart mariadb

配置主从复制:

sql

CHANGE MASTER TO
  MASTER_HOST='主服务器IP',
  MASTER_USER='replicator',
  MASTER_PASSWORD='strong_repl_password',
  MASTER_LOG_FILE='mariadb-bin.000001', -- 从主服务器获取正确的值
  MASTER_LOG_POS=4; -- 从主服务器获取正确的值

START SLAVE;

复制状态监控

检查复制状态:

sql

SHOW SLAVE STATUS\G

关键指标:

Slave_IO_Running: 应该是"Yes"
Slave_SQL_Running: 应该是"Yes"
Seconds_Behind_Master: 复制延迟秒数

故障转移方案

手动故障转移
- 停止从服务器复制: STOP SLAVE;
- 在从服务器上重置只读模式: SET GLOBAL read_only = OFF;
- 将应用程序连接重定向到从服务器
- 修复主服务器，设置为新的从服务器
自动故障转移
- 使用ProxySQL或MaxScale实现自动故障检测和转移
- 配置虚拟IP地址，在故障时自动漂移

数据库监控和维护

性能监控

安装监控工具

bash

# 安装Percona Monitoring and Management (PMM)
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb
sudo apt update
sudo apt install -y pmm2-client

配置监控客户端

bash

sudo pmm-admin config --server-insecure-tls --server-url=https://admin:admin@pmm-server-ip:443
sudo pmm-admin add mysql --username=pmm_user --password=password

日常维护任务

表分析和优化

sql

-- MariaDB/MySQL
ANALYZE TABLE table_name;
OPTIMIZE TABLE table_name;

-- PostgreSQL
VACUUM ANALYZE table_name;

表碎片整理

sql

-- 对大表进行重组
ALTER TABLE large_table ENGINE=InnoDB;

慢查询分析

bash

# 使用pt-query-digest分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > slow_query_report.txt

数据库安全加固

网络安全

限制数据库访问

bash

# 在防火墙中只允许指定IP访问数据库端口
sudo ufw allow from 192.168.1.0/24 to any port 3306

使用SSL加密连接

MariaDB配置:

ini

[mysqld]
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

创建用户时要求SSL:

sql

CREATE USER 'secure_user'@'%' IDENTIFIED BY 'password' REQUIRE SSL;

权限管理

应用最小权限原则

sql

-- 创建只读用户
CREATE USER 'read_only_user'@'%' IDENTIFIED BY 'password';
GRANT SELECT ON erp_database.* TO 'read_only_user'@'%';

-- 创建应用专用用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON erp_database.* TO 'app_user'@'%';

定期审计用户权限

sql

SELECT User, Host FROM mysql.user;
SHOW GRANTS FOR 'user'@'host';

删除未使用的账户
sql
```
DROP USER 'unused_user'@'%';
```
1

数据加密

透明数据加密 (MariaDB 10.1+)

sql

-- 检查是否支持加密
SHOW PLUGINS;

-- 创建加密表
CREATE TABLE encrypted_table (
   id INT,
   sensitive_data VARCHAR(100)
) ENCRYPTION='Y';

敏感数据字段加密

sql

-- 使用AES加密存储敏感数据
INSERT INTO users (username, password) 
VALUES ('user', AES_ENCRYPT('password', 'encryption_key'));

-- 解密数据
SELECT username, AES_DECRYPT(password, 'encryption_key') FROM users;

灾难恢复计划

制定灾难恢复策略

定义RPO (Recovery Point Objective)
- 确定可接受的数据丢失时间点
- 根据RPO设计备份频率
定义RTO (Recovery Time Objective)
- 确定可接受的恢复时间
- 根据RTO设计恢复方案
文档化恢复程序
- 详细记录恢复步骤
- 定期演练恢复过程

数据恢复流程

从逻辑备份恢复

bash

# MariaDB/MySQL
mysql -u root -p erp_database < erp_backup_20230101.sql

# PostgreSQL
pg_restore -U postgres -d erp_database erp_backup_20230101.dump

从物理备份恢复

bash

# 使用Percona XtraBackup恢复
xtrabackup --prepare --target-dir=/backup/full-backup-20230101
xtrabackup --copy-back --target-dir=/backup/full-backup-20230101

时间点恢复

bash

# 先恢复完整备份
mysql -u root -p erp_database < full_backup.sql

# 然后应用二进制日志到特定时间点
mysqlbinlog --stop-datetime="2023-01-01 12:00:00" /var/log/mysql/mariadb-bin.000001 | mysql -u root -p erp_database

常见问题与解决方案

数据库连接数过多

问题表现: 应用报错"Too many connections"

解决方案:

增加max_connections参数值
sql
```
SET GLOBAL max_connections = 500;
```
1
在配置文件中永久修改
ini
```
[mysqld]
max_connections = 500
```
1
2
优化应用连接池配置，避免连接泄漏

查询性能慢

问题表现: 某些查询执行时间过长

解决方案:

分析慢查询日志找出问题SQL
bash
```
pt-query-digest /var/log/mysql/slow.log
```
1

使用EXPLAIN分析执行计划

sql

EXPLAIN SELECT * FROM large_table WHERE non_indexed_column = 'value';

添加适当的索引

sql

CREATE INDEX idx_non_indexed_column ON large_table(non_indexed_column);

重写查询，避免全表扫描

磁盘空间不足

问题表现: 数据库服务停止，提示磁盘空间不足

解决方案:

清理二进制日志
sql
```
PURGE BINARY LOGS BEFORE '2023-01-01';
```
1
清理临时表和日志文件
bash
```
sudo find /tmp -name "*.tmp" -delete
```
1

压缩或归档旧数据

bash

# 使用pt-archiver工具归档旧数据
pt-archiver --source h=localhost,D=erp_database,t=old_data --dest h=archive_server,D=archive_db,t=old_data --where "create_date < '2022-01-01'" --limit 1000 --commit-each

扩展磁盘空间

结论

完善的数据库配置是ERP系统稳定运行的基础。通过合理的选型、配置、优化和维护，可以确保数据库系统提供高性能、高可用和高安全性的服务，最大限度地满足业务需求。

定期的性能监控、安全审计和备份验证是数据库管理的重要环节，应当建立规范的操作流程和应急预案，确保系统在各种情况下都能正常运行或快速恢复。

数据库配置 ​

数据库选型 ​

支持的数据库系统 ​

数据库服务器配置 ​

硬件推荐 ​

存储配置 ​

数据库安装与基础配置 ​

MariaDB安装（Ubuntu为例） ​

MariaDB基础配置 ​

PostgreSQL安装与配置 ​

数据库性能优化 ​

索引优化 ​

查询优化 ​

配置优化 ​

数据库备份策略 ​

备份方法 ​

备份计划 ​

自动化备份脚本 ​

数据库高可用设置 ​

主从复制（MariaDB/MySQL） ​

复制状态监控 ​

故障转移方案 ​

数据库监控和维护 ​

性能监控 ​

日常维护任务 ​

数据库安全加固 ​

网络安全 ​

权限管理 ​

数据加密 ​

灾难恢复计划 ​

制定灾难恢复策略 ​

数据恢复流程 ​

常见问题与解决方案 ​

数据库连接数过多 ​

查询性能慢 ​

磁盘空间不足 ​

结论 ​

数据库配置

数据库选型

支持的数据库系统

数据库服务器配置

硬件推荐

存储配置

数据库安装与基础配置

MariaDB安装（Ubuntu为例）

MariaDB基础配置

PostgreSQL安装与配置

数据库性能优化

索引优化

查询优化

配置优化

数据库备份策略

备份方法

备份计划

自动化备份脚本

数据库高可用设置

主从复制（MariaDB/MySQL）

复制状态监控

故障转移方案

数据库监控和维护

性能监控

日常维护任务

数据库安全加固

网络安全

权限管理

数据加密

灾难恢复计划

制定灾难恢复策略

数据恢复流程

常见问题与解决方案

数据库连接数过多

查询性能慢

磁盘空间不足

结论