MySQL和PostgreSQL基础

课程目标

通过本课程的学习，您将掌握以下内容：

MySQL和PostgreSQL的基本概念和特点
数据库的安装和配置方法
SQL基础语法和常用命令
数据库设计和优化原则
用户权限管理和安全配置
数据库备份和恢复策略
常见问题的排查和解决方法

一、MySQL和PostgreSQL概述

1.1 MySQL简介

MySQL 是一种广泛使用的关系型数据库管理系统，由Oracle公司开发和维护。

主要特点：

开源免费：社区版完全免费，企业版提供更多高级功能
性能优异：针对Web应用优化，支持高并发
可靠性高：支持事务和ACID特性
易于使用：简单的安装和配置过程
生态丰富：大量的第三方工具和集成方案
跨平台：支持多种操作系统

适用场景：

Web应用：如电商网站、博客、内容管理系统
中小型企业应用：如企业资源规划(ERP)、客户关系管理(CRM)
数据仓库：用于数据分析和报表
嵌入式应用：如移动应用后端

1.2 PostgreSQL简介

PostgreSQL 是一种功能强大的开源关系型数据库管理系统，以其可靠性、稳定性和丰富的功能而闻名。

主要特点：

开源免费：完全开源，由全球社区开发和维护
功能丰富：支持高级数据类型、复杂查询、JSON支持等
可扩展性：支持自定义数据类型、函数和操作符
可靠性高：强大的事务支持和数据完整性保证
安全性好：细粒度的访问控制和加密功能
标准兼容：高度符合SQL标准
跨平台：支持多种操作系统

适用场景：

企业级应用：需要复杂数据模型和事务支持的应用
数据分析：需要高级查询功能和数据完整性的场景
地理信息系统：通过PostGIS扩展支持空间数据
金融应用：需要高可靠性和数据一致性的场景
科研应用：需要处理复杂数据结构的场景

1.3 MySQL vs PostgreSQL比较

特性	MySQL	PostgreSQL
性能	针对Web应用优化，读操作性能优异	复杂查询和写操作性能更好
功能	基本功能完善，企业版提供高级功能	功能丰富，支持更多高级特性
扩展性	插件系统相对简单	强大的扩展系统，支持自定义类型和函数
标准兼容性	部分支持SQL标准	高度符合SQL标准
数据类型	基本数据类型支持	支持更多高级数据类型（如JSON、数组、范围类型）
地理信息	通过插件支持	通过PostGIS扩展提供强大的地理信息功能
社区支持	大型活跃社区	专业社区，注重质量和标准
适用场景	Web应用、中小型企业应用	企业级应用、数据分析、科研应用

二、数据库的安装和配置

2.1 MySQL的安装和配置

在CentOS/RHEL上安装MySQL：

bash

# 安装MySQL YUM仓库
wget https://dev.mysql.com/get/mysql80-community-release-el9-1.noarch.rpm
sudo rpm -ivh mysql80-community-release-el9-1.noarch.rpm

# 安装MySQL服务器
sudo dnf install mysql-community-server

# 启动MySQL服务
sudo systemctl start mysqld
sudo systemctl enable mysqld

# 查看初始密码
sudo grep 'temporary password' /var/log/mysqld.log

# 安全配置向导
sudo mysql_secure_installation

# 登录MySQL
mysql -u root -p

在Ubuntu上安装MySQL：

bash

# 更新包列表
sudo apt update

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

# 启动MySQL服务
sudo systemctl start mysql
sudo systemctl enable mysql

# 安全配置向导
sudo mysql_secure_installation

# 登录MySQL
mysql -u root -p

基本配置：

bash

# 编辑配置文件
sudo vi /etc/my.cnf

# 主要配置项
[mysqld]
bind-address = 127.0.0.1  # 绑定地址
port = 3306              # 端口
user = mysql             # 运行用户
datadir = /var/lib/mysql  # 数据目录
pid-file = /var/run/mysqld/mysqld.pid  # PID文件

# 性能配置
max_connections = 1000   # 最大连接数
innodb_buffer_pool_size = 256M  # InnoDB缓冲池大小
query_cache_size = 16M    # 查询缓存大小

# 重启MySQL服务
sudo systemctl restart mysqld

2.2 PostgreSQL的安装和配置

在CentOS/RHEL上安装PostgreSQL：

bash

# 安装PostgreSQL YUM仓库
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 安装PostgreSQL服务器
sudo dnf install -y postgresql15-server

# 初始化数据库
sudo /usr/pgsql-15/bin/postgresql-15-setup --initdb

# 启动PostgreSQL服务
sudo systemctl start postgresql-15
sudo systemctl enable postgresql-15

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

在Ubuntu上安装PostgreSQL：

bash

# 更新包列表
sudo apt update

# 安装PostgreSQL服务器
sudo apt install postgresql postgresql-contrib

# 启动PostgreSQL服务
sudo systemctl start postgresql
sudo systemctl enable postgresql

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

基本配置：

bash

# 编辑配置文件
sudo vi /var/lib/pgsql/15/data/postgresql.conf

# 主要配置项
listen_addresses = 'localhost'  # 监听地址
port = 5432                    # 端口
max_connections = 100          # 最大连接数
shared_buffers = 128MB         # 共享缓冲区大小
work_mem = 4MB                 # 工作内存大小
maintenance_work_mem = 64MB    # 维护工作内存大小

# 编辑pg_hba.conf文件（认证配置）
sudo vi /var/lib/pgsql/15/data/pg_hba.conf

# 添加本地用户认证
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

# 重启PostgreSQL服务
sudo systemctl restart postgresql-15

三、SQL基础语法和常用命令

3.1 SQL基础概念

SQL（Structured Query Language）是用于管理关系型数据库的标准语言，包括以下主要部分：

DDL（数据定义语言）：用于创建、修改和删除数据库对象
DML（数据操作语言）：用于查询、插入、更新和删除数据
DCL（数据控制语言）：用于管理用户权限和事务
TCL（事务控制语言）：用于管理事务

3.2 DDL语句

创建数据库：

sql

-- MySQL
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- PostgreSQL
CREATE DATABASE mydb WITH ENCODING='UTF8' OWNER=postgres;

创建表：

sql

-- MySQL
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- PostgreSQL
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

修改表：

sql

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 修改列
ALTER TABLE users MODIFY COLUMN age INT NOT NULL;

-- 删除列
ALTER TABLE users DROP COLUMN phone;

-- 添加索引
CREATE INDEX idx_users_email ON users(email);

删除表：

sql

DROP TABLE IF EXISTS users;

3.3 DML语句

插入数据：

sql

-- 插入单行数据
INSERT INTO users (name, email, age) VALUES ('John Doe', 'john@example.com', 30);

-- 插入多行数据
INSERT INTO users (name, email, age) VALUES
('Jane Smith', 'jane@example.com', 25),
('Bob Johnson', 'bob@example.com', 35);

查询数据：

sql

-- 查询所有列
SELECT * FROM users;

-- 查询指定列
SELECT id, name, email FROM users;

-- 带条件查询
SELECT * FROM users WHERE age > 25;

-- 排序查询
SELECT * FROM users ORDER BY age DESC;

-- 分组查询
SELECT age, COUNT(*) FROM users GROUP BY age;

-- 连接查询
SELECT u.name, o.order_date FROM users u
JOIN orders o ON u.id = o.user_id;

-- 子查询
SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);

更新数据：

sql

-- 更新单行数据
UPDATE users SET age = 31 WHERE id = 1;

-- 更新多行数据
UPDATE users SET age = age + 1 WHERE age < 30;

删除数据：

sql

-- 删除指定数据
DELETE FROM users WHERE id = 1;

-- 删除所有数据
DELETE FROM users;

-- 清空表（重置自增ID）
TRUNCATE TABLE users;

3.4 DCL语句

创建用户：

sql

-- MySQL
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

-- PostgreSQL
CREATE USER myuser WITH PASSWORD 'mypassword';

授权：

sql

-- MySQL
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;

-- PostgreSQL
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

撤销权限：

sql

-- MySQL
REVOKE ALL PRIVILEGES ON mydb.* FROM 'myuser'@'localhost';
FLUSH PRIVILEGES;

-- PostgreSQL
REVOKE ALL PRIVILEGES ON DATABASE mydb FROM myuser;

删除用户：

sql

-- MySQL
DROP USER IF EXISTS 'myuser'@'localhost';

-- PostgreSQL
DROP USER IF EXISTS myuser;

3.5 TCL语句

事务控制：

sql

-- 开始事务
START TRANSACTION;

-- 执行操作
INSERT INTO accounts (user_id, balance) VALUES (1, 1000);
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
INSERT INTO transactions (user_id, amount, type) VALUES (1, 100, 'withdrawal');

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 保存点
SAVEPOINT my_savepoint;
-- 回滚到保存点
ROLLBACK TO my_savepoint;

四、数据库设计和优化

4.1 数据库设计原则

1. 范式设计：

第一范式（1NF）：确保每列都是原子的，不可再分
第二范式（2NF）：消除部分依赖，确保非主键列完全依赖于主键
第三范式（3NF）：消除传递依赖，确保非主键列不依赖于其他非主键列
BCNF（Boyce-Codd范式）：进一步消除异常

2. 表设计原则：

命名规范：使用有意义的表名和列名
主键设计：选择合适的主键类型，优先使用自增ID
数据类型：选择合适的数据类型，避免使用过大的类型
约束：合理使用NOT NULL、UNIQUE、FOREIGN KEY等约束
索引：为常用查询字段添加索引

3. 关系设计：

一对一关系：使用外键或共享主键
一对多关系：在多的一方添加外键
多对多关系：使用中间表连接两个表

4.2 索引优化

索引类型：

B-Tree索引：最常用的索引类型，适用于等值查询和范围查询
Hash索引：适用于等值查询，不支持范围查询
全文索引：用于全文搜索
空间索引：用于地理空间数据

创建索引的原则：

为常用查询字段添加索引：如WHERE、JOIN、ORDER BY子句中的字段
为唯一性约束添加索引：UNIQUE、PRIMARY KEY等
避免过度索引：索引会增加写操作的开销
考虑索引列的顺序：在复合索引中，将选择性高的列放在前面
定期维护索引：删除无用的索引，重建碎片化的索引

索引使用技巧：

避免在索引列上使用函数：会导致索引失效
使用前缀索引：对于长字符串，使用前缀索引减少索引大小
考虑覆盖索引：包含查询所需的所有列，减少回表操作
使用索引提示：在复杂查询中，使用FORCE INDEX等提示

4.3 查询优化

查询优化的原则：

只查询需要的列：避免使用SELECT *
使用LIMIT限制结果集：避免返回过多数据
合理使用JOIN：避免过多的JOIN操作
使用EXPLAIN分析查询：了解查询执行计划
避免在WHERE子句中使用函数：会导致索引失效
使用参数化查询：避免SQL注入，提高缓存命中率
合理使用子查询：对于复杂查询，考虑使用子查询

EXPLAIN分析：

sql

-- MySQL
EXPLAIN SELECT * FROM users WHERE age > 25 ORDER BY created_at;

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25 ORDER BY created_at;

查询优化示例：

sql

-- 优化前
SELECT * FROM users WHERE SUBSTRING(email, LOCATE('@', email) + 1) = 'example.com';

-- 优化后
SELECT * FROM users WHERE email LIKE '%@example.com';

-- 进一步优化：添加索引
CREATE INDEX idx_users_email ON users(email);

4.4 数据库参数优化

MySQL参数优化：

ini

[mysqld]
# 内存配置
innodb_buffer_pool_size = 512M  # InnoDB缓冲池大小，建议为服务器内存的50-80%
key_buffer_size = 64M           # MyISAM索引缓冲区大小
query_cache_size = 32M          # 查询缓存大小

# 连接配置
max_connections = 500           # 最大连接数
wait_timeout = 600              # 连接超时时间

# 日志配置
slow_query_log = 1               # 开启慢查询日志
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2              # 慢查询阈值（秒）

# InnoDB配置
innodb_log_file_size = 64M       # InnoDB日志文件大小
innodb_file_per_table = 1        # 每个表使用独立的表空间
innodb_flush_log_at_trx_commit = 1  # 事务提交时刷新日志

PostgreSQL参数优化：

# 内存配置
shared_buffers = 256MB           # 共享缓冲区大小，建议为服务器内存的25%
work_mem = 4MB                   # 工作内存大小
maintenance_work_mem = 64MB      # 维护工作内存大小

# 查询配置
effective_cache_size = 512MB     # 有效的缓存大小
random_page_cost = 4.0           # 随机页面成本
seq_page_cost = 1.0              # 顺序页面成本

# 连接配置
max_connections = 100            # 最大连接数
idle_in_transaction_session_timeout = 60000  # 事务空闲超时时间

# 日志配置
log_min_duration_statement = 1000  # 记录执行时间超过1秒的语句
log_statement = 'ddl'            # 记录DDL语句

# 写入配置
wal_buffers = 16MB               # WAL缓冲区大小
checkpoint_completion_target = 0.9  # 检查点完成目标

五、用户权限管理和安全配置

5.1 用户管理

MySQL用户管理：

sql

-- 创建用户
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'strongpassword';
CREATE USER 'appuser'@'%' IDENTIFIED BY 'apppassword';

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

-- 查看权限
SHOW GRANTS FOR 'appuser'@'%';

-- 撤销权限
REVOKE DELETE ON mydb.* FROM 'appuser'@'%';

-- 修改密码
ALTER USER 'appuser'@'%' IDENTIFIED BY 'newpassword';

-- 删除用户
DROP USER IF EXISTS 'appuser'@'%';

PostgreSQL用户管理：

sql

-- 创建用户
CREATE USER admin WITH PASSWORD 'strongpassword';
CREATE USER appuser WITH PASSWORD 'apppassword';

-- 创建角色
CREATE ROLE readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT readonly TO appuser;

-- 授权
GRANT ALL PRIVILEGES ON DATABASE mydb TO admin;
GRANT CONNECT ON DATABASE mydb TO appuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;

-- 查看权限
\dp

-- 撤销权限
REVOKE DELETE ON ALL TABLES IN SCHEMA public FROM appuser;

-- 修改密码
ALTER USER appuser WITH PASSWORD 'newpassword';

-- 删除用户
DROP USER IF EXISTS appuser;

5.2 安全配置

MySQL安全配置：

修改默认端口：

ini

[mysqld]
port = 3307

限制绑定地址：

ini

[mysqld]
bind-address = 127.0.0.1  # 只允许本地访问
# 或
bind-address = 192.168.1.100  # 只允许指定IP访问

禁用远程root登录：

sql

DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
FLUSH PRIVILEGES;

移除匿名用户：

sql

DELETE FROM mysql.user WHERE User='';
FLUSH PRIVILEGES;

移除测试数据库：

sql

DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\_%';
FLUSH PRIVILEGES;

使用SSL连接：

ini

[mysqld]
ssl-ca=/path/to/ca-cert.pem
ssl-cert=/path/to/server-cert.pem
ssl-key=/path/to/server-key.pem

PostgreSQL安全配置：

修改默认端口：

port = 5433

限制监听地址：

listen_addresses = 'localhost'  # 只允许本地访问
# 或
listen_addresses = '192.168.1.100'  # 只允许指定IP访问

配置认证方式：

# pg_hba.conf
local   all             postgres                                peer
local   all             all                                     md5
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
host    all             all             192.168.1.0/24          scram-sha-256

使用SSL连接：

ssl = on
ssl_cert_file = '/path/to/server.crt'
ssl_key_file = '/path/to/server.key'
ssl_ca_file = '/path/to/root.crt'

限制最大连接数：

max_connections = 50

5.3 密码策略

MySQL密码策略：

sql

-- 查看密码策略
SHOW VARIABLES LIKE 'validate_password%';

-- 修改密码策略
SET GLOBAL validate_password_policy = 'STRONG';
SET GLOBAL validate_password_length = 12;
SET GLOBAL validate_password_number_count = 1;
SET GLOBAL validate_password_special_char_count = 1;
SET GLOBAL validate_password_mixed_case_count = 1;

-- 创建用户时使用强密码
CREATE USER 'secureuser'@'localhost' IDENTIFIED BY 'My$tr0ngP@ssw0rd123';

PostgreSQL密码策略：

sql

-- 使用pgcrypto扩展生成密码哈希
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- 创建用户时使用强密码
CREATE USER secureuser WITH PASSWORD 'My$tr0ngP@ssw0rd123';

-- 配置密码有效期
ALTER USER secureuser VALID UNTIL '2024-12-31';

六、数据库备份和恢复

6.1 备份策略

备份类型：

完全备份：备份整个数据库
增量备份：只备份自上次备份以来更改的数据
差异备份：只备份自上次完全备份以来更改的数据
逻辑备份：使用SQL语句备份数据
物理备份：直接备份数据文件

备份频率：

完全备份：每周或每日
增量备份：每小时
差异备份：每日

备份存储：

本地存储：快速恢复，但存在单点故障
远程存储：防止本地灾难，如NFS、FTP、云存储
多副本：保存多个备份副本
异地备份：防止区域性灾难

6.2 MySQL备份和恢复

使用mysqldump备份：

bash

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

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

# 备份数据库结构
mysqldump -u root -p --no-data mydb > mydb_schema.sql

# 备份数据库数据
mysqldump -u root -p --no-create-info mydb > mydb_data.sql

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

# 备份并排除某些表
mysqldump -u root -p mydb --ignore-table=mydb.logs > mydb_backup.sql

使用mysql恢复：

bash

# 恢复单个数据库
mysql -u root -p mydb < mydb_backup.sql

# 从压缩文件恢复
zcat mydb_backup.sql.gz | mysql -u root -p mydb

# 恢复特定表
mysql -u root -p mydb < table_backup.sql

使用xtrabackup备份：

bash

# 安装xtrabackup
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
apt update
apt install percona-xtrabackup-80

# 完全备份
xtrabackup --backup --target-dir=/backup/mysql/full/$(date +%Y%m%d)

# 准备备份
xtrabackup --prepare --target-dir=/backup/mysql/full/20240101

# 恢复备份
systemctl stop mysql
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/mysql/full/20240101
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

6.3 PostgreSQL备份和恢复

使用pg_dump备份：

bash

# 备份单个数据库
pg_dump -U postgres mydb > mydb_backup.sql

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

# 备份数据库结构
pg_dump -U postgres --schema-only mydb > mydb_schema.sql

# 备份数据库数据
pg_dump -U postgres --data-only mydb > mydb_data.sql

# 压缩备份
pg_dump -U postgres mydb | gzip > mydb_backup.sql.gz

# 备份特定表
pg_dump -U postgres mydb -t users > users_backup.sql

使用psql恢复：

bash

# 恢复单个数据库
psql -U postgres -d mydb < mydb_backup.sql

# 从压缩文件恢复
zcat mydb_backup.sql.gz | psql -U postgres -d mydb

# 恢复所有数据库
psql -U postgres < all_databases_backup.sql

使用pg_basebackup备份：

bash

# 完全备份
pg_basebackup -h localhost -U postgres -D /backup/postgresql/full/$(date +%Y%m%d) -F t -X stream

# 恢复备份
systemctl stop postgresql
rm -rf /var/lib/postgresql/15/main/*
tar -xzf /backup/postgresql/full/20240101/base.tar.gz -C /var/lib/postgresql/15/main/
touch /var/lib/postgresql/15/main/recovery.signal
systemctl start postgresql

6.4 备份验证

验证备份文件：

bash

# 检查备份文件大小
ls -lh mydb_backup.sql

# 检查备份文件内容
head -n 20 mydb_backup.sql

# 测试恢复
mysql -u root -p -e "CREATE DATABASE test_restore;"
mysql -u root -p test_restore < mydb_backup.sql
mysql -u root -p -e "SELECT COUNT(*) FROM test_restore.users;"
mysql -u root -p -e "DROP DATABASE test_restore;"

自动化备份脚本：

bash

#!/bin/bash

# 配置
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_USER="root"
DB_PASS="password"
DB_NAME="mydb"

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

# 执行备份
mysqldump -u $DB_USER -p$DB_PASS $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# 保留最近7天的备份
find $BACKUP_DIR -name "${DB_NAME}_*.sql.gz" -mtime +7 -delete

# 记录日志
echo "Backup completed at $(date)" >> $BACKUP_DIR/backup.log

七、常见问题的排查和解决

7.1 连接问题

MySQL连接问题：

症状：Can't connect to MySQL server on 'localhost' (10061)

排查步骤：

检查MySQL服务状态：systemctl status mysqld
检查端口是否开放：netstat -tlnp | grep 3306
检查防火墙配置：firewall-cmd --list-ports
检查绑定地址：查看my.cnf中的bind-address配置
检查用户权限：确保用户有正确的主机权限

解决方案：

启动MySQL服务：systemctl start mysqld
修改绑定地址：bind-address = 0.0.0.0
添加防火墙规则：firewall-cmd --add-port=3306/tcp --permanent
创建正确的用户：CREATE USER 'user'@'%' IDENTIFIED BY 'password';

PostgreSQL连接问题：

症状：psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory

排查步骤：

检查PostgreSQL服务状态：systemctl status postgresql
检查端口是否开放：netstat -tlnp | grep 5432
检查监听地址：查看postgresql.conf中的listen_addresses配置
检查认证配置：查看pg_hba.conf中的认证规则
检查用户权限：确保用户有正确的权限

解决方案：

启动PostgreSQL服务：systemctl start postgresql
修改监听地址：listen_addresses = '*'
添加认证规则：在pg_hba.conf中添加相应的规则
切换到postgres用户：sudo -u postgres psql

7.2 性能问题

MySQL性能问题：

症状：查询执行缓慢，服务器负载高

排查步骤：

查看慢查询日志：tail -f /var/log/mysql/slow-query.log
使用EXPLAIN分析查询：EXPLAIN SELECT * FROM users WHERE age > 25;
查看服务器状态：SHOW GLOBAL STATUS;
查看连接数：SHOW PROCESSLIST;
检查索引使用情况：SHOW INDEX FROM users;

解决方案：

添加索引：CREATE INDEX idx_users_age ON users(age);
优化查询：重写复杂查询
调整参数：修改my.cnf中的性能参数
限制连接数：设置max_connections
清理慢查询：杀死长时间运行的查询

PostgreSQL性能问题：

症状：查询执行缓慢，服务器负载高

排查步骤：

查看慢查询：SELECT * FROM pg_stat_activity WHERE state = 'active';
使用EXPLAIN分析查询：EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;
查看服务器状态：SELECT * FROM pg_stat_database;
查看连接数：SELECT count(*) FROM pg_stat_activity;
检查索引使用情况：SELECT * FROM pg_indexes WHERE tablename = 'users';

解决方案：

添加索引：CREATE INDEX idx_users_age ON users(age);
优化查询：重写复杂查询
调整参数：修改postgresql.conf中的性能参数
限制连接数：设置max_connections
清理慢查询：SELECT pg_cancel_backend(pid);

7.3 数据一致性问题

症状：数据丢失或不一致

排查步骤：

检查事务日志：查看MySQL的binlog或PostgreSQL的WAL日志
检查备份状态：确保备份正常执行
检查应用代码：查找可能导致数据不一致的代码
检查服务器状态：查看是否有硬件故障或电源问题

解决方案：

从备份恢复：使用最近的备份恢复数据
修复数据：手动修复不一致的数据
优化事务：确保事务正确提交和回滚
加强监控：设置数据一致性检查

7.4 空间不足问题

症状：ERROR 1114 (HY000): The table 'users' is full

排查步骤：

检查磁盘空间：df -h
检查数据库大小：SELECT table_schema "DB Name", SUM(data_length + index_length) / 1024 / 1024 "Size in MB" FROM information_schema.tables GROUP BY table_schema;
检查大表：SELECT table_name, data_length / 1024 / 1024 "Size in MB" FROM information_schema.tables WHERE table_schema = 'mydb' ORDER BY data_length DESC LIMIT 10;

解决方案：

清理数据：删除不必要的数据
归档数据：将旧数据移至归档表
增加磁盘空间：扩展存储
优化表结构：使用合适的数据类型
启用分区：对大表使用分区

八、课程总结

本课程详细介绍了MySQL和PostgreSQL的基础知识、安装配置、SQL语法、数据库设计优化、权限管理、备份恢复以及常见问题的排查和解决。通过学习本课程，您应该能够：

理解MySQL和PostgreSQL的基本概念和特点
熟练安装和配置数据库服务器
掌握SQL基础语法和常用命令
应用数据库设计和优化原则
管理用户权限和配置安全设置
制定和执行数据库备份策略
排查和解决常见的数据库问题

MySQL和PostgreSQL都是功能强大的关系型数据库管理系统，各有其优势和适用场景。在实际应用中，您可以根据具体需求选择合适的数据库系统，或者在不同场景中使用不同的数据库系统。

课后练习

安装MySQL和PostgreSQL数据库服务器
创建测试数据库和表结构
插入、查询、更新和删除数据
为常用查询字段添加索引
设计一个简单的应用数据库模型
创建用户并配置不同的权限
制定并执行备份策略
模拟常见问题并练习排查和解决
优化数据库性能参数
编写自动化备份脚本

通过这些练习，您将更加熟悉MySQL和PostgreSQL的使用和管理，为实际项目中的数据库应用打下坚实的基础。

MySQL和PostgreSQL基础 ​

课程目标 ​

一、MySQL和PostgreSQL概述 ​

1.1 MySQL简介 ​

1.2 PostgreSQL简介 ​

1.3 MySQL vs PostgreSQL比较 ​

二、数据库的安装和配置 ​

2.1 MySQL的安装和配置 ​

2.2 PostgreSQL的安装和配置 ​

三、SQL基础语法和常用命令 ​

3.1 SQL基础概念 ​

3.2 DDL语句 ​

3.3 DML语句 ​

3.4 DCL语句 ​

3.5 TCL语句 ​

四、数据库设计和优化 ​

4.1 数据库设计原则 ​

4.2 索引优化 ​

4.3 查询优化 ​

4.4 数据库参数优化 ​

五、用户权限管理和安全配置 ​

5.1 用户管理 ​

5.2 安全配置 ​

5.3 密码策略 ​

六、数据库备份和恢复 ​

6.1 备份策略 ​

6.2 MySQL备份和恢复 ​

6.3 PostgreSQL备份和恢复 ​

6.4 备份验证 ​

七、常见问题的排查和解决 ​

7.1 连接问题 ​

7.2 性能问题 ​

7.3 数据一致性问题 ​

7.4 空间不足问题 ​

八、课程总结 ​

课后练习 ​

评论区

MySQL和PostgreSQL基础

课程目标

一、MySQL和PostgreSQL概述

1.1 MySQL简介

1.2 PostgreSQL简介

1.3 MySQL vs PostgreSQL比较

二、数据库的安装和配置

2.1 MySQL的安装和配置

2.2 PostgreSQL的安装和配置

三、SQL基础语法和常用命令

3.1 SQL基础概念

3.2 DDL语句

3.3 DML语句

3.4 DCL语句

3.5 TCL语句

四、数据库设计和优化

4.1 数据库设计原则

4.2 索引优化

4.3 查询优化

4.4 数据库参数优化

五、用户权限管理和安全配置

5.1 用户管理

5.2 安全配置

5.3 密码策略

六、数据库备份和恢复

6.1 备份策略

6.2 MySQL备份和恢复

6.3 PostgreSQL备份和恢复

6.4 备份验证

七、常见问题的排查和解决

7.1 连接问题

7.2 性能问题

7.3 数据一致性问题

7.4 空间不足问题

八、课程总结

课后练习