数据库配置指南

MySQL、PostgreSQL安装配置

本指南将帮助您在VPS上安装和配置数据库服务器。

MySQL数据库

安装MySQL

# Ubuntu/Debian
apt update
apt install mysql-server

# CentOS/AlmaLinux
yum install mysql-server
systemctl enable mysqld
systemctl start mysqld

安全配置

# 运行安全配置脚本
mysql_secure_installation

配置选项说明：

设置root密码
删除匿名用户
禁止root远程登录
删除test数据库

基本操作

-- 登录MySQL
mysql -u root -p

-- 创建数据库
CREATE DATABASE mywebsite;

-- 创建用户
CREATE USER 'webuser'@'localhost' IDENTIFIED BY 'password';

-- 授权
GRANT ALL PRIVILEGES ON mywebsite.* TO 'webuser'@'localhost';
FLUSH PRIVILEGES;

-- 查看数据库
SHOW DATABASES;

-- 使用数据库
USE mywebsite;

配置文件优化

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# 基本设置
bind-address = 127.0.0.1
port = 3306

# 性能优化
innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M
max_connections = 100
query_cache_size = 32M

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

PostgreSQL数据库

安装PostgreSQL

# Ubuntu/Debian
apt update
apt install postgresql postgresql-contrib

# CentOS/AlmaLinux
yum install postgresql-server postgresql-contrib
postgresql-setup initdb
systemctl enable postgresql
systemctl start postgresql

PostgreSQL基本操作

# 切换到postgres用户
sudo -u postgres psql

# 创建数据库
CREATE DATABASE mywebsite;

# 创建用户
CREATE USER webuser WITH PASSWORD 'password';

# 授权
GRANT ALL PRIVILEGES ON DATABASE mywebsite TO webuser;

# 退出
\q

配置远程访问

# 编辑配置文件
vim /etc/postgresql/14/main/postgresql.conf

# 修改监听地址
listen_addresses = 'localhost'

# 编辑访问控制
vim /etc/postgresql/14/main/pg_hba.conf

# 添加访问规则
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5

数据库备份

MySQL备份

# 备份单个数据库
mysqldump -u root -p mywebsite > mywebsite_backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_databases.sql

# 恢复数据库
mysql -u root -p mywebsite < mywebsite_backup.sql

PostgreSQL备份

# 备份数据库
pg_dump -U postgres mywebsite > mywebsite_backup.sql

# 备份所有数据库
pg_dumpall -U postgres > all_databases.sql

# 恢复数据库
psql -U postgres mywebsite < mywebsite_backup.sql

数据库监控

MySQL监控

-- 查看进程列表
SHOW PROCESSLIST;

-- 查看状态
SHOW STATUS;

-- 查看变量
SHOW VARIABLES;

-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query_log';

PostgreSQL监控

-- 查看活动连接
SELECT * FROM pg_stat_activity;

-- 查看数据库大小
SELECT pg_size_pretty(pg_database_size('mywebsite'));

-- 查看表大小
SELECT schemaname,tablename,pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size 
FROM pg_tables ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

性能优化

MySQL优化

# 内存优化
innodb_buffer_pool_size = 70% of RAM
key_buffer_size = 256M

# 连接优化
max_connections = 100
thread_cache_size = 16

# 查询优化
query_cache_type = 1
query_cache_size = 64M

PostgreSQL优化

# postgresql.conf
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 4MB
maintenance_work_mem = 64MB

安全设置

防火墙配置

# 只允许本地连接
ufw deny 3306  # MySQL
ufw deny 5432  # PostgreSQL

用户权限管理

-- MySQL: 创建只读用户
CREATE USER 'readonly'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON mywebsite.* TO 'readonly'@'localhost';

-- PostgreSQL: 创建只读用户
CREATE USER readonly WITH PASSWORD 'password';
GRANT CONNECT ON DATABASE mywebsite TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;