Linux进阶之数据库管理

数据库是现代应用程序的核心，Linux 平台支持多种数据库管理系统（DBMS），如 MySQL 和 PostgreSQL。本章将详细讲解如何在 Linux 上安装与配置 MySQL 和 PostgreSQL，介绍基本管理任务、性能优化，以及安全性与备份策略。

---

一、MySQL 数据库管理

1、MySQL 简介

MySQL 是一种流行的开源关系型数据库管理系统，适用于多种规模的应用。

---

2、安装与配置 MySQL

安装 MySQL
在 Ubuntu 上安装：

sudo apt update
sudo apt install mysql-server -y

启动并启用 MySQL 服务：

sudo systemctl start mysql
sudo systemctl enable mysql

安全初始化
运行以下命令设置 root 密码并移除默认设置中的安全风险：

sudo mysql_secure_installation

根据提示完成配置。

---

3、基本管理操作

连接 MySQL
使用以下命令登录：

sudo mysql -u root -p

创建数据库

CREATE DATABASE mydb;

创建用户并授予权限

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;

备份与恢复
使用 mysqldump 备份：

mysqldump -u root -p mydb > mydb_backup.sql

恢复数据库：

mysql -u root -p mydb < mydb_backup.sql

---

4、性能优化

配置文件调优
编辑配置文件 /etc/mysql/my.cnf，优化以下参数：

[mysqld]
innodb_buffer_pool_size = 1G
query_cache_size = 64M
max_connections = 200

监控性能
通过 SHOW STATUS 查看实时状态：

SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Connections';

---

二、PostgreSQL 数据库管理

1、PostgreSQL 简介

PostgreSQL 是一个功能强大的开源对象关系型数据库，支持复杂查询和事务处理。

---

2、安装与配置 PostgreSQL

安装 PostgreSQL
在 Ubuntu 上安装：

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

启动并启用 PostgreSQL 服务：

sudo systemctl start postgresql
sudo systemctl enable postgresql

切换到 PostgreSQL 用户

sudo -i -u postgres
psql

---

3、基本管理操作

创建数据库与用户

CREATE DATABASE mydb;
CREATE USER myuser WITH PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

备份与恢复
使用 pg_dump 备份：

pg_dump mydb > mydb_backup.sql

恢复数据库：

psql mydb < mydb_backup.sql

---

4、性能优化

配置文件调优
编辑配置文件 /etc/postgresql/<version>/main/postgresql.conf，调整以下参数：

shared_buffers = 256MB
work_mem = 4MB
maintenance_work_mem = 64MB

查看查询性能
启用慢查询日志：

SET log_min_duration_statement = 1000;

---

三、安全性与备份策略

1、MySQL 和 PostgreSQL 的安全性设置

限制用户访问
仅允许特定用户访问数据库：

REVOKE ALL PRIVILEGES ON DATABASE mydb FROM PUBLIC;

强制使用加密连接
配置 SSL 加密连接，编辑 MySQL 或 PostgreSQL 配置文件，启用 ssl = on。

---

2、自动备份策略

使用 cron 定期备份
在 /etc/crontab 添加以下任务：

0 2 * * * mysqldump -u root -p'yourpassword' mydb > /backup/mydb_$(date +\%F).sql
0 3 * * * pg_dump -U postgres mydb > /backup/mydb_$(date +\%F).sql

---

四、小结

通过本章学习，你掌握了以下内容：

1. 安装与配置 MySQL 和 PostgreSQL：包括数据库创建、用户权限管理。
2. 数据库优化与性能监控：调整配置文件，使用查询性能工具。
3. 数据库安全与备份策略：启用 SSL，加密连接，定期自动备份。

无论是小型应用还是企业级系统，使用 MySQL 或 PostgreSQL 结合性能调优和备份策略，都能构建高效、安全的数据库服务。