努努力教程

当前课程

标签

Docker 入门教程 Linux MacOS MySQL Nginx Redis Windows 内容电商 参考 命令大全 基础概念 安装配置 实战 容器 容器技术 抖音电商 架构 环境安装 电商基础 电商运营 直播带货 短视频营销 虚拟化 速查手册 镜像

第12课：数据库安装与管理

阅读时长约 7 分钟
浏览量 1234
【腾讯云】语音识别准确率高，支持多语种，多场景，限时特惠，最低14.9元起

推广

【腾讯云】语音识别准确率高，支持多语种，多场景，限时特惠，最低14.9元起

数据库安装与管理

MySQL/MariaDB数据库

1. MySQL安装

Ubuntu/Debian系统

# 更新包列表
sudo apt update

# 安装MySQL服务器
sudo apt install mysql-server -y

# 安全配置
sudo mysql_secure_installation

# 启动并启用MySQL
sudo systemctl start mysql
sudo systemctl enable mysql

CentOS/RHEL系统

# CentOS 8+ 安装MySQL
sudo dnf install mysql-server -y

# 或安装MariaDB (MySQL的开源替代)
sudo dnf install mariadb-server -y

# 启动并启用服务
sudo systemctl start mysqld      # MySQL
sudo systemctl start mariadb     # MariaDB
sudo systemctl enable mysqld
sudo systemctl enable mariadb

# 安全配置
sudo mysql_secure_installation

2. MySQL基本配置

配置文件位置

# 主配置文件
/etc/mysql/mysql.conf.d/mysqld.cnf    # Ubuntu/Debian
/etc/my.cnf                           # CentOS/RHEL

# 数据目录
/var/lib/mysql/

# 日志文件
/var/log/mysql/error.log              # Ubuntu/Debian
/var/log/mysqld.log                   # CentOS/RHEL

基本配置示例

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# 基本设置
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
datadir = /var/lib/mysql

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

# 连接设置
max_connections = 200
max_connect_errors = 10
table_open_cache = 2000
max_allowed_packet = 16M

# 缓冲区设置
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M

# 日志设置
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# 安全设置
bind-address = 127.0.0.1
skip-networking = 0

3. MySQL用户管理

连接MySQL

# 使用root用户连接
sudo mysql -u root -p

# 连接到特定数据库
mysql -u username -p database_name

# 连接到远程MySQL
mysql -h hostname -u username -p

用户管理命令

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

-- 授权
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'webuser'@'%';

-- 查看用户权限
SHOW GRANTS FOR 'newuser'@'localhost';

-- 撤销权限
REVOKE INSERT ON mydb.* FROM 'webuser'@'%';

-- 删除用户
DROP USER 'olduser'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

4. 数据库操作

数据库管理

-- 显示所有数据库
SHOW DATABASES;

-- 创建数据库
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 使用数据库
USE myapp;

-- 显示表
SHOW TABLES;

-- 删除数据库
DROP DATABASE olddb;

-- 查看数据库大小
SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables 
GROUP BY table_schema;

表操作示例

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 查看表结构
DESCRIBE users;
SHOW CREATE TABLE users;

-- 修改表结构
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users MODIFY COLUMN email VARCHAR(150);
ALTER TABLE users DROP COLUMN phone;

-- 创建索引
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_email ON users(email);

PostgreSQL数据库

1. PostgreSQL安装

Ubuntu/Debian系统

# 安装PostgreSQL
sudo apt update
sudo apt install postgresql postgresql-contrib -y

# 启动并启用服务
sudo systemctl start postgresql
sudo systemctl enable postgresql

# 检查状态
sudo systemctl status postgresql

CentOS/RHEL系统

# 安装PostgreSQL
sudo dnf install postgresql-server postgresql-contrib -y

# 初始化数据库
sudo postgresql-setup --initdb

# 启动并启用服务
sudo systemctl start postgresql
sudo systemctl enable postgresql

2. PostgreSQL配置

配置文件位置

# 主配置文件
/etc/postgresql/14/main/postgresql.conf    # Ubuntu/Debian
/var/lib/pgsql/data/postgresql.conf        # CentOS/RHEL

# 访问控制文件
/etc/postgresql/14/main/pg_hba.conf        # Ubuntu/Debian
/var/lib/pgsql/data/pg_hba.conf            # CentOS/RHEL

# 数据目录
/var/lib/postgresql/14/main/               # Ubuntu/Debian
/var/lib/pgsql/data/                       # CentOS/RHEL

基本配置

# 编辑postgresql.conf
sudo nano /etc/postgresql/14/main/postgresql.conf

# 重要配置项
listen_addresses = 'localhost'
port = 5432
max_connections = 100
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 4MB
maintenance_work_mem = 64MB

# 日志配置
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'all'

访问控制配置

# 编辑pg_hba.conf
sudo nano /etc/postgresql/14/main/pg_hba.conf

# 配置示例
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    mydb            webuser         192.168.1.0/24          md5

3. PostgreSQL用户管理

连接PostgreSQL

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

# 连接到特定数据库
psql -U username -d database_name

# 连接到远程PostgreSQL
psql -h hostname -U username -d database_name

用户管理

-- 创建用户
CREATE USER newuser WITH PASSWORD 'password';
CREATE USER webuser WITH PASSWORD 'strongpassword' CREATEDB;

-- 创建角色
CREATE ROLE admin WITH LOGIN PASSWORD 'adminpass' SUPERUSER;

-- 修改用户权限
ALTER USER webuser CREATEDB;
ALTER USER webuser WITH SUPERUSER;

-- 查看用户
\du

-- 删除用户
DROP USER olduser;

4. PostgreSQL数据库操作

数据库管理

-- 显示所有数据库
\l

-- 创建数据库
CREATE DATABASE myapp OWNER webuser ENCODING 'UTF8';

-- 连接数据库
\c myapp

-- 显示表
\dt

-- 删除数据库
DROP DATABASE olddb;

-- 查看数据库大小
SELECT 
    datname AS database_name,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
WHERE datistemplate = false;

Redis缓存数据库

1. Redis安装

Ubuntu/Debian系统

# 安装Redis
sudo apt update
sudo apt install redis-server -y

# 启动并启用Redis
sudo systemctl start redis-server
sudo systemctl enable redis-server

# 测试Redis
redis-cli ping

CentOS/RHEL系统

# 安装Redis
sudo dnf install redis -y

# 启动并启用Redis
sudo systemctl start redis
sudo systemctl enable redis

# 配置防火墙
sudo firewall-cmd --permanent --add-port=6379/tcp
sudo firewall-cmd --reload

2. Redis配置

配置文件

# 编辑Redis配置
sudo nano /etc/redis/redis.conf

# 重要配置项
bind 127.0.0.1                    # 绑定地址
port 6379                         # 端口
requirepass strongpassword        # 设置密码
maxmemory 1gb                     # 最大内存
maxmemory-policy allkeys-lru      # 内存策略

# 持久化配置
save 900 1                        # 900秒内至少1个key变化时保存
save 300 10                       # 300秒内至少10个key变化时保存
save 60 10000                     # 60秒内至少10000个key变化时保存

# 重启Redis应用配置
sudo systemctl restart redis-server

Redis基本操作

# 连接Redis
redis-cli
redis-cli -a password

# 基本命令
SET key value                     # 设置键值
GET key                          # 获取值
DEL key                          # 删除键
EXISTS key                       # 检查键是否存在
KEYS *                           # 列出所有键
FLUSHALL                         # 清空所有数据

# 查看信息
INFO                             # 服务器信息
INFO memory                      # 内存信息
CONFIG GET *                     # 配置信息

数据库备份与恢复

1. MySQL备份恢复

备份数据库

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

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

# 备份特定表
mysqldump -u root -p database_name table_name > table_backup.sql

# 压缩备份
mysqldump -u root -p database_name | gzip > backup.sql.gz

# 自动化备份脚本
#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
mysqldump -u root -p$MYSQL_PASSWORD myapp > /backup/myapp_$DATE.sql
gzip /backup/myapp_$DATE.sql
find /backup -name "myapp_*.sql.gz" -mtime +7 -delete

恢复数据库

# 恢复数据库
mysql -u root -p database_name < backup.sql

# 恢复压缩备份
gunzip < backup.sql.gz | mysql -u root -p database_name

# 创建数据库并恢复
mysql -u root -p -e "CREATE DATABASE newdb;"
mysql -u root -p newdb < backup.sql

2. PostgreSQL备份恢复

备份数据库

# 备份单个数据库
pg_dump -U postgres database_name > backup.sql

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

# 压缩备份
pg_dump -U postgres database_name | gzip > backup.sql.gz

# 二进制格式备份
pg_dump -U postgres -Fc database_name > backup.dump

恢复数据库

# 恢复SQL格式备份
psql -U postgres database_name < backup.sql

# 恢复二进制格式备份
pg_restore -U postgres -d database_name backup.dump

# 恢复压缩备份
gunzip < backup.sql.gz | psql -U postgres database_name

3. Redis备份恢复

Redis备份

# 手动保存
redis-cli BGSAVE

# 复制RDB文件
sudo cp /var/lib/redis/dump.rdb /backup/redis_backup_$(date +%Y%m%d).rdb

# 导出数据
redis-cli --rdb backup.rdb

# 备份脚本
#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
redis-cli BGSAVE
sleep 5
cp /var/lib/redis/dump.rdb /backup/redis_$DATE.rdb

Redis恢复

# 停止Redis服务
sudo systemctl stop redis-server

# 替换RDB文件
sudo cp /backup/redis_backup.rdb /var/lib/redis/dump.rdb
sudo chown redis:redis /var/lib/redis/dump.rdb

# 启动Redis服务
sudo systemctl start redis-server

数据库性能优化

1. MySQL性能优化

配置优化

# my.cnf优化配置
[mysqld]
# InnoDB优化
innodb_buffer_pool_size = 1G          # 设置为内存的70-80%
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1

# 查询缓存
query_cache_type = 1
query_cache_size = 128M

# 连接优化
max_connections = 200
thread_cache_size = 16
table_open_cache = 2000

# 临时表优化
tmp_table_size = 64M
max_heap_table_size = 64M

索引优化

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

-- 分析查询
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- 创建索引
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_name_email ON users(name, email);

-- 查看索引使用情况
SHOW INDEX FROM users;

2. PostgreSQL性能优化

配置优化

# postgresql.conf优化
shared_buffers = 256MB                 # 设置为内存的25%
effective_cache_size = 1GB             # 设置为内存的75%
work_mem = 4MB
maintenance_work_mem = 64MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100

查询优化

-- 分析查询计划
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

-- 创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_name_email ON users(name, email);

-- 更新统计信息
ANALYZE users;

-- 查看索引使用情况
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch 
FROM pg_stat_user_indexes;

数据库安全

1. 访问控制

MySQL安全配置

-- 删除匿名用户
DELETE FROM mysql.user WHERE User='';

-- 删除测试数据库
DROP DATABASE IF EXISTS test;

-- 禁用远程root登录
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- 设置强密码策略
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
SET GLOBAL validate_password.policy = STRONG;

PostgreSQL安全配置

# 修改默认端口
port = 5433

# 限制连接
listen_addresses = 'localhost'
max_connections = 50

# SSL配置
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

2. 数据加密

传输加密

# MySQL SSL配置
[mysqld]
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

# PostgreSQL SSL配置
ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'

总结

数据库管理要点：

  1. 数据库选择：根据应用需求选择合适的数据库系统
  2. 安装配置：正确安装和配置数据库服务
  3. 用户管理：合理设置用户权限，遵循最小权限原则
  4. 备份策略：建立定期备份机制，确保数据安全
  5. 性能优化：调整配置参数，优化查询性能
  6. 安全加固：实施访问控制和数据加密措施

下一课预告

在下一课中，我们将学习Linux安全配置，包括：

  • 系统安全加固
  • 防火墙配置
  • 入侵检测
  • 安全审计

💡 小贴士：数据库是应用系统的核心组件。建议定期备份数据，监控数据库性能，及时应用安全补丁，确保数据库稳定可靠运行。

Vue3 + TypeScript 企业级项目实战

课程推荐

Vue3 + TypeScript 企业级项目实战
Python 全栈开发工程师培训

热门课程

Python 全栈开发工程师培训

📚 文章对你有帮助？请关注我的公众号，万分感谢！

获取更多优质技术文章，第一时间掌握最新技术动态

关注公众号

关注公众号

第一时间获取最新技术文章

添加微信

添加微信

技术交流 · 问题答疑 · 学习指导

🙏 感谢你的支持，让我们一起成长！

评论讨论

欢迎留下你的想法和建议