稻草人的文档

Appearance

MySQL 数据库完全指南 ​

概述 ​

MySQL 是世界上最流行的开源关系型数据库管理系统之一，由瑞典 MySQL AB 公司开发，现在由 Oracle 公司维护。MySQL 以其高性能、可靠性和易用性而闻名，广泛应用于 Web 应用程序、企业级应用和云服务中。

核心特性 ​

  • 开源免费：基于 GPL 许可证，社区版完全免费
  • 跨平台：支持 Linux、Windows、macOS 等多种操作系统
  • 高性能：优化的查询引擎，支持大规模并发访问
  • 可扩展性：支持主从复制、分库分表等扩展方案
  • ACID 兼容：支持事务处理，保证数据一致性
  • 丰富的存储引擎：InnoDB、MyISAM、Memory 等多种存储引擎

版本演进 ​

  • MySQL 5.7：引入 JSON 数据类型、性能模式增强
  • MySQL 8.0：窗口函数、CTE、角色管理、文档存储
  • MySQL 8.1/8.2/8.3：持续性能优化和新特性

安装与配置 ​

官方安装包安装 ​

Linux (CentOS/RHEL) ​

bash
# 下载 MySQL 官方 Yum 仓库
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
sudo rpm -ivh mysql80-community-release-el7-3.noarch.rpm

# 安装 MySQL
sudo yum install mysql-server

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

# 获取临时密码
sudo grep 'temporary password' /var/log/mysqld.log

# 安全配置
sudo mysql_secure_installation

Ubuntu/Debian ​

bash
# 更新包列表
sudo apt update

# 安装 MySQL
sudo apt install mysql-server

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

# 安全配置
sudo mysql_secure_installation

macOS ​

bash
# 使用 Homebrew
brew install mysql

# 启动服务
brew services start mysql

# 或者下载官方 DMG 安装包
# https://dev.mysql.com/downloads/mysql/

Docker 安装 ​

bash
# 拉取 MySQL 镜像
docker pull mysql:8.0

# 运行 MySQL 容器
docker run --name mysql-server \
  -e MYSQL_ROOT_PASSWORD=your_password \
  -p 3306:3306 \
  -v mysql-data:/var/lib/mysql \
  -d mysql:8.0

# 连接到 MySQL
docker exec -it mysql-server mysql -u root -p

Docker Compose ​

yaml
# docker-compose.yml
version: '3.8'
services:
  mysql:
    image: mysql:8.0
    container_name: mysql-server
    environment:
      MYSQL_ROOT_PASSWORD: your_password
      MYSQL_DATABASE: myapp
      MYSQL_USER: appuser
      MYSQL_PASSWORD: apppassword
    ports:
      - "3306:3306"
    volumes:
      - mysql-data:/var/lib/mysql
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    command: --default-authentication-plugin=mysql_native_password

volumes:
  mysql-data:

基本配置 ​

配置文件位置 ​

  • Linux: /etc/mysql/mysql.conf.d/mysqld.cnf 或 /etc/my.cnf
  • Windows: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
  • macOS: /usr/local/etc/my.cnf

重要配置参数 ​

ini
[mysqld]
# 基本设置
port = 3306
bind-address = 0.0.0.0
socket = /var/run/mysqld/mysqld.sock

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

# InnoDB 设置
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1

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

# 连接设置
max_connections = 200
max_connect_errors = 10

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

# 二进制日志
log-bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7

数据类型 ​

数值类型 ​

sql
-- 整数类型
TINYINT     -- 1字节，-128到127
SMALLINT    -- 2字节，-32768到32767
MEDIUMINT   -- 3字节，-8388608到8388607
INT         -- 4字节，-2147483648到2147483647
BIGINT      -- 8字节，-9223372036854775808到9223372036854775807

-- 浮点类型
FLOAT(M,D)  -- 4字节单精度浮点
DOUBLE(M,D) -- 8字节双精度浮点
DECIMAL(M,D) -- 精确小数，用于货币计算

-- 示例
CREATE TABLE numbers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    age TINYINT UNSIGNED,
    salary DECIMAL(10,2),
    score FLOAT(5,2)
);

字符串类型 ​

sql
-- 定长字符串
CHAR(n)     -- 固定长度，最大255字符

-- 变长字符串
VARCHAR(n)  -- 可变长度，最大65535字符

-- 文本类型
TINYTEXT    -- 最大255字符
TEXT        -- 最大65535字符
MEDIUMTEXT  -- 最大16777215字符
LONGTEXT    -- 最大4294967295字符

-- 二进制类型
BINARY(n)   -- 固定长度二进制
VARBINARY(n) -- 可变长度二进制
BLOB        -- 二进制大对象

-- 示例
CREATE TABLE content (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    summary TEXT,
    content LONGTEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

日期时间类型 ​

sql
-- 日期时间类型
DATE        -- 日期 'YYYY-MM-DD'
TIME        -- 时间 'HH:MM:SS'
DATETIME    -- 日期时间 'YYYY-MM-DD HH:MM:SS'
TIMESTAMP   -- 时间戳，自动更新
YEAR        -- 年份

-- 示例
CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE,
    start_time TIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

JSON 类型 (MySQL 5.7+) ​

sql
-- JSON 数据类型
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON,
    metadata JSON
);

-- 插入 JSON 数据
INSERT INTO products (name, attributes, metadata) VALUES
('iPhone 15', 
 '{"color": "blue", "storage": "128GB", "features": ["Face ID", "Wireless Charging"]}',
 '{"manufacturer": "Apple", "release_year": 2023}');

-- 查询 JSON 数据
SELECT name, 
       JSON_EXTRACT(attributes, '$.color') as color,
       JSON_EXTRACT(attributes, '$.storage') as storage
FROM products;

-- 使用 -> 操作符
SELECT name, 
       attributes->'$.color' as color,
       attributes->>'$.storage' as storage
FROM products;

基本操作 ​

数据库操作 ​

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

-- 查看数据库
SHOW DATABASES;

-- 使用数据库
USE myapp;

-- 删除数据库
DROP DATABASE myapp;

-- 查看当前数据库
SELECT DATABASE();

表操作 ​

sql
-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    age INT CHECK (age >= 0 AND age <= 150),
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_status (status)
);

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

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

-- 重命名表
RENAME TABLE users TO app_users;

-- 删除表
DROP TABLE users;

CRUD 操作 ​

插入数据 ​

sql
-- 单行插入
INSERT INTO users (username, email, password_hash, full_name, age) 
VALUES ('john_doe', 'john@example.com', 'hashed_password', 'John Doe', 30);

-- 多行插入
INSERT INTO users (username, email, password_hash, full_name, age) VALUES
('jane_smith', 'jane@example.com', 'hashed_password', 'Jane Smith', 25),
('bob_wilson', 'bob@example.com', 'hashed_password', 'Bob Wilson', 35),
('alice_brown', 'alice@example.com', 'hashed_password', 'Alice Brown', 28);

-- 插入或更新
INSERT INTO users (username, email, password_hash, full_name) 
VALUES ('john_doe', 'john@example.com', 'new_password', 'John Doe')
ON DUPLICATE KEY UPDATE 
password_hash = VALUES(password_hash),
updated_at = CURRENT_TIMESTAMP;

查询数据 ​

sql
-- 基本查询
SELECT * FROM users;
SELECT username, email, full_name FROM users;

-- 条件查询
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE status = 'active' AND age BETWEEN 20 AND 40;
SELECT * FROM users WHERE username LIKE 'john%';
SELECT * FROM users WHERE email IN ('john@example.com', 'jane@example.com');

-- 排序和限制
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY age ASC, username DESC;
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;

-- 聚合查询
SELECT COUNT(*) as total_users FROM users;
SELECT status, COUNT(*) as count FROM users GROUP BY status;
SELECT AVG(age) as average_age FROM users WHERE status = 'active';
SELECT MIN(age), MAX(age), AVG(age) FROM users;

-- 分组和过滤
SELECT status, COUNT(*) as count 
FROM users 
GROP BY status 
HAVING count > 5;

更新数据 ​

sql
-- 更新单行
UPDATE users 
SET full_name = 'John Smith', age = 31 
WHERE username = 'john_doe';

-- 批量更新
UPDATE users 
SET status = 'inactive' 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- 条件更新
UPDATE users 
SET age = age + 1 
WHERE MONTH(created_at) = MONTH(CURDATE());

删除数据 ​

sql
-- 删除特定记录
DELETE FROM users WHERE username = 'john_doe';

-- 批量删除
DELETE FROM users WHERE status = 'suspended';

-- 清空表
TRUNCATE TABLE users;

索引优化 ​

索引类型 ​

sql
-- 主键索引
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY
);

-- 唯一索引
CREATE UNIQUE INDEX idx_username ON users(username);
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- 普通索引
CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_status ON users(status);

-- 复合索引
CREATE INDEX idx_status_age ON users(status, age);
CREATE INDEX idx_name_email ON users(full_name, email);

-- 前缀索引
CREATE INDEX idx_email_prefix ON users(email(10));

-- 函数索引 (MySQL 8.0+)
CREATE INDEX idx_upper_username ON users((UPPER(username)));

索引管理 ​

sql
-- 查看索引
SHOW INDEX FROM users;
SHOW KEYS FROM users;

-- 删除索引
DROP INDEX idx_age ON users;
ALTER TABLE users DROP INDEX idx_status;

-- 重建索引
ALTER TABLE users DROP INDEX idx_status_age, ADD INDEX idx_status_age (status, age);

查询优化 ​

执行计划分析 ​

sql
-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE age > 25;
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE status = 'active';

-- 分析查询性能
EXPLAIN ANALYZE SELECT * FROM users WHERE age BETWEEN 20 AND 40;

索引选择性分析 ​

sql
-- 查看列数据分布比例，判断是否适合建索引
SELECT column_name, COUNT(1) as count
FROM information_schema.columns 
WHERE table_schema = 'myapp' AND table_name = 'users'
GROUP BY column_name 
ORDER BY count DESC;

-- 计算列的选择性
SELECT 
    COUNT(DISTINCT status) / COUNT(*) as status_selectivity,
    COUNT(DISTINCT age) / COUNT(*) as age_selectivity,
    COUNT(DISTINCT username) / COUNT(*) as username_selectivity
FROM users;

-- 分析索引使用情况
SELECT 
    table_schema,
    table_name,
    index_name,
    cardinality,
    ROUND(cardinality / (SELECT table_rows FROM information_schema.tables 
                        WHERE table_schema = s.table_schema 
                        AND table_name = s.table_name) * 100, 2) as selectivity
FROM information_schema.statistics s
WHERE table_schema = 'myapp' AND table_name = 'users'
ORDER BY selectivity DESC;

慢查询分析 ​

sql
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 查看慢查询状态
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 分析慢查询
-- 使用 mysqldumpslow 工具
-- mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

高级功能 ​

存储引擎 ​

InnoDB (默认) ​

sql
-- 创建 InnoDB 表
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2),
    status ENUM('pending', 'paid', 'shipped', 'delivered'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- InnoDB 特性
-- 1. 支持事务 (ACID)
-- 2. 支持外键约束
-- 3. 行级锁定
-- 4. 崩溃恢复
-- 5. 多版本并发控制 (MVCC)

MyISAM ​

sql
-- 创建 MyISAM 表
CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message TEXT,
    level ENUM('info', 'warning', 'error'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM;

-- MyISAM 特性
-- 1. 表级锁定
-- 2. 不支持事务
-- 3. 不支持外键
-- 4. 读取速度快
-- 5. 占用空间小

Memory ​

sql
-- 创建内存表
CREATE TABLE sessions (
    session_id VARCHAR(128) PRIMARY KEY,
    user_id INT,
    data TEXT,
    expires_at TIMESTAMP
) ENGINE=MEMORY;

-- Memory 特性
-- 1. 数据存储在内存中
-- 2. 访问速度极快
-- 3. 服务器重启数据丢失
-- 4. 适合临时数据和缓存

事务处理 ​

sql
-- 基本事务
START TRANSACTION;

INSERT INTO orders (user_id, total_amount, status) 
VALUES (1, 99.99, 'pending');

UPDATE users SET last_order_at = NOW() WHERE id = 1;

COMMIT;

-- 事务回滚
START TRANSACTION;

INSERT INTO orders (user_id, total_amount, status) 
VALUES (1, 199.99, 'pending');

-- 发生错误时回滚
ROLLBACK;

-- 保存点
START TRANSACTION;

INSERT INTO orders (user_id, total_amount) VALUES (1, 50.00);
SAVEPOINT sp1;

INSERT INTO order_items (order_id, product_id, quantity) VALUES (LAST_INSERT_ID(), 1, 2);
SAVEPOINT sp2;

INSERT INTO order_items (order_id, product_id, quantity) VALUES (LAST_INSERT_ID(), 2, 1);

-- 回滚到保存点
ROLLBACK TO sp2;

COMMIT;

视图 ​

sql
-- 创建视图
CREATE VIEW active_users AS
SELECT id, username, email, full_name, created_at
FROM users 
WHERE status = 'active';

-- 复杂视图
CREATE VIEW user_order_summary AS
SELECT 
    u.id,
    u.username,
    u.email,
    COUNT(o.id) as total_orders,
    COALESCE(SUM(o.total_amount), 0) as total_spent,
    MAX(o.created_at) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email;

-- 使用视图
SELECT * FROM active_users WHERE username LIKE 'john%';
SELECT * FROM user_order_summary ORDER BY total_spent DESC LIMIT 10;

-- 更新视图
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email, full_name, age, created_at
FROM users 
WHERE status = 'active' AND age >= 18;

-- 删除视图
DROP VIEW active_users;

存储过程和函数 ​

sql
-- 存储过程
DELIMITER //

CREATE PROCEDURE GetUserOrders(
    IN user_id INT,
    IN limit_count INT DEFAULT 10
)
BEGIN
    SELECT 
        o.id,
        o.total_amount,
        o.status,
        o.created_at
    FROM orders o
    WHERE o.user_id = user_id
    ORDER BY o.created_at DESC
    LIMIT limit_count;
END //

DELIMITER ;

-- 调用存储过程
CALL GetUserOrders(1, 5);

-- 存储函数
DELIMITER //

CREATE FUNCTION CalculateAge(birth_date DATE)
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    RETURN YEAR(CURDATE()) - YEAR(birth_date) - 
           (DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT(birth_date, '%m%d'));
END //

DELIMITER ;

-- 使用函数
SELECT username, birth_date, CalculateAge(birth_date) as age
FROM users;

触发器 ​

sql
-- 创建触发器
DELIMITER //

CREATE TRIGGER update_user_stats
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE users 
    SET 
        total_orders = total_orders + 1,
        total_spent = total_spent + NEW.total_amount,
        last_order_at = NEW.created_at
    WHERE id = NEW.user_id;
END //

DELIMITER ;

-- 删除订单时的触发器
DELIMITER //

CREATE TRIGGER update_user_stats_on_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    UPDATE users 
    SET 
        total_orders = total_orders - 1,
        total_spent = total_spent - OLD.total_amount
    WHERE id = OLD.user_id;
END //

DELIMITER ;

-- 查看触发器
SHOW TRIGGERS;

-- 删除触发器
DROP TRIGGER update_user_stats;

性能优化 ​

查询优化技巧 ​

sql
-- 1. 使用合适的索引
-- 避免在 WHERE 子句中使用函数
-- 不好的例子
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 好的例子
SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

-- 2. 避免 SELECT *
-- 不好的例子
SELECT * FROM users WHERE status = 'active';

-- 好的例子
SELECT id, username, email FROM users WHERE status = 'active';

-- 3. 使用 LIMIT
SELECT id, username FROM users ORDER BY created_at DESC LIMIT 20;

-- 4. 优化 JOIN 查询
-- 使用合适的 JOIN 类型
SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id, u.username;

-- 5. 使用 EXISTS 代替 IN (大数据集)
-- 不好的例子
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total_amount > 100);

-- 好的例子
SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total_amount > 100
);

配置优化 ​

ini
# my.cnf 性能优化配置
[mysqld]
# InnoDB 缓冲池大小 (建议设置为可用内存的 70-80%)
innodb_buffer_pool_size = 2G

# InnoDB 日志文件大小
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M

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

# 连接设置
max_connections = 500
max_connect_errors = 100
connect_timeout = 10
wait_timeout = 600
interactive_timeout = 600

# 表缓存
table_open_cache = 2000
table_definition_cache = 1000

# 临时表设置
tmp_table_size = 64M
max_heap_table_size = 64M

# 排序和分组
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
join_buffer_size = 2M

# 二进制日志
log-bin = mysql-bin
binlog_format = ROW
max_binlog_size = 100M
expire_logs_days = 7

# 慢查询日志
slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1

监控和分析 ​

sql
-- 查看数据库状态
SHOW STATUS;
SHOW STATUS LIKE 'Innodb%';
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Queries';

-- 查看进程列表
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- 查看表状态
SHOW TABLE STATUS;
SHOW TABLE STATUS LIKE 'users';

-- 分析表
ANALYZE TABLE users;
OPTIMIZE TABLE users;

-- 检查表
CHECK TABLE users;
REPAIR TABLE users;

-- 查看索引使用情况
SELECT 
    table_schema,
    table_name,
    index_name,
    cardinality,
    sub_part,
    packed,
    nullable,
    index_type
FROM information_schema.statistics
WHERE table_schema = 'myapp'
ORDER BY table_name, seq_in_index;

备份与恢复 ​

逻辑备份 ​

bash
# 备份单个数据库
mysqldump -u root -p myapp > myapp_backup.sql

# 备份多个数据库
mysqldump -u root -p --databases myapp testdb > multiple_backup.sql

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

# 只备份表结构
mysqldump -u root -p --no-data myapp > myapp_structure.sql

# 只备份数据
mysqldump -u root -p --no-create-info myapp > myapp_data.sql

# 备份特定表
mysqldump -u root -p myapp users orders > specific_tables.sql

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

# 恢复数据库
mysql -u root -p myapp < myapp_backup.sql

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

物理备份 ​

bash
# 使用 MySQL Enterprise Backup (商业版)
mysqlbackup --user=root --password --backup-dir=/backup/full backup

# 使用 Percona XtraBackup (开源)
# 安装 Percona XtraBackup
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 percona-xtrabackup-80

# 全量备份
xtrabackup --user=root --password=your_password --backup --target-dir=/backup/full

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

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

# 增量备份
xtrabackup --user=root --password=your_password --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full

自动化备份脚本 ​

bash
#!/bin/bash
# mysql_backup.sh

# 配置变量
DB_USER="backup_user"
DB_PASS="backup_password"
DB_NAME="myapp"
BACKUP_DIR="/backup/mysql"
DATE=$(date +"%Y%m%d_%H%M%S")
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql"
LOG_FILE="${BACKUP_DIR}/backup.log"

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

# 执行备份
echo "$(date): Starting backup of $DB_NAME" >> $LOG_FILE
mysqldump -u $DB_USER -p$DB_PASS $DB_NAME > $BACKUP_FILE

if [ $? -eq 0 ]; then
    echo "$(date): Backup completed successfully" >> $LOG_FILE
    
    # 压缩备份文件
    gzip $BACKUP_FILE
    
    # 删除7天前的备份
    find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete
    
    echo "$(date): Old backups cleaned up" >> $LOG_FILE
else
    echo "$(date): Backup failed" >> $LOG_FILE
    exit 1
fi

# 添加到 crontab
# 0 2 * * * /path/to/mysql_backup.sh

高可用性 ​

主从复制 ​

主服务器配置 ​

ini
# 主服务器 my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON

# 创建复制用户
CREATE USER 'replication'@'%' IDENTIFIED BY 'replication_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;

# 查看主服务器状态
SHOW MASTER STATUS;

从服务器配置 ​

ini
# 从服务器 my.cnf
[mysqld]
server-id = 2
relay-log = relay-bin
read_only = 1
gtid_mode = ON
enforce_gtid_consistency = ON

# 配置主从复制
CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='replication',
    MASTER_PASSWORD='replication_password',
    MASTER_AUTO_POSITION=1;

# 启动从服务器
START SLAVE;

# 查看从服务器状态
SHOW SLAVE STATUS\G

读写分离 ​

python
# Python 读写分离示例
import pymysql
from pymysql.cursors import DictCursor

class DatabaseManager:
    def __init__(self):
        # 主数据库连接 (写)
        self.master = pymysql.connect(
            host='master_host',
            user='app_user',
            password='app_password',
            database='myapp',
            cursorclass=DictCursor
        )
        
        # 从数据库连接 (读)
        self.slave = pymysql.connect(
            host='slave_host',
            user='app_user',
            password='app_password',
            database='myapp',
            cursorclass=DictCursor
        )
    
    def execute_write(self, sql, params=None):
        """执行写操作"""
        with self.master.cursor() as cursor:
            cursor.execute(sql, params)
            self.master.commit()
            return cursor.lastrowid
    
    def execute_read(self, sql, params=None):
        """执行读操作"""
        with self.slave.cursor() as cursor:
            cursor.execute(sql, params)
            return cursor.fetchall()

# 使用示例
db = DatabaseManager()

# 写操作使用主库
user_id = db.execute_write(
    "INSERT INTO users (username, email) VALUES (%s, %s)",
    ('john_doe', 'john@example.com')
)

# 读操作使用从库
users = db.execute_read("SELECT * FROM users WHERE status = %s", ('active',))

MySQL Cluster (NDB) ​

ini
# MySQL Cluster 配置示例
# config.ini (管理节点)
[ndbd default]
NoOfReplicas=2
DataMemory=80M
IndexMemory=18M

[ndbd]
hostname=node1.example.com
datadir=/var/lib/mysql-cluster

[ndbd]
hostname=node2.example.com
datadir=/var/lib/mysql-cluster

[mysqld]
hostname=sql1.example.com

[mysqld]
hostname=sql2.example.com

[ndb_mgmd]
hostname=mgm.example.com
datadir=/var/lib/mysql-cluster

安全性 ​

用户管理 ​

sql
-- 创建用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';
CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_password';

-- 授权
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_user'@'localhost';
GRANT ALL PRIVILEGES ON myapp.* TO 'admin_user'@'localhost';

-- 查看权限
SHOW GRANTS FOR 'app_user'@'localhost';

-- 撤销权限
REVOKE DELETE ON myapp.* FROM 'app_user'@'localhost';

-- 修改密码
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'new_password';

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

-- 刷新权限
FLUSH PRIVILEGES;

角色管理 (MySQL 8.0+) ​

sql
-- 创建角色
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- 给角色授权
GRANT SELECT ON myapp.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON myapp.* TO 'app_write';
GRANT ALL PRIVILEGES ON myapp.* TO 'app_admin';

-- 将角色分配给用户
GRANT 'app_read', 'app_write' TO 'app_user'@'localhost';

-- 设置默认角色
SET DEFAULT ROLE 'app_read', 'app_write' TO 'app_user'@'localhost';

-- 激活角色
SET ROLE 'app_read', 'app_write';

-- 查看当前角色
SELECT CURRENT_ROLE();

SSL/TLS 配置 ​

ini
# my.cnf 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
require_secure_transport=ON

[client]
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/client-cert.pem
ssl-key=/etc/mysql/ssl/client-key.pem
sql
-- 要求用户使用 SSL
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'password' REQUIRE SSL;
ALTER USER 'existing_user'@'%' REQUIRE SSL;

-- 检查 SSL 状态
SHOW STATUS LIKE 'Ssl%';
SHOW VARIABLES LIKE 'have_ssl';

监控与运维 ​

性能监控 ​

sql
-- 查看连接信息
SHOW STATUS LIKE 'Connections';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';

-- 查看查询统计
SHOW STATUS LIKE 'Questions';
SHOW STATUS LIKE 'Queries';
SHOW STATUS LIKE 'Slow_queries';

-- 查看 InnoDB 状态
SHOW ENGINE INNODB STATUS;

-- 查看表锁信息
SHOW STATUS LIKE 'Table_locks%';

-- 查看查询缓存
SHOW STATUS LIKE 'Qcache%';

-- 性能模式查询
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

SELECT * FROM performance_schema.file_summary_by_instance
WHERE file_name LIKE '%ibd'
ORDER BY sum_timer_read DESC LIMIT 10;

日志分析 ​

bash
# 错误日志分析
tail -f /var/log/mysql/error.log
grep -i error /var/log/mysql/error.log

# 慢查询日志分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 二进制日志分析
mysqlbinlog mysql-bin.000001
mysqlbinlog --start-datetime="2023-01-01 00:00:00" --stop-datetime="2023-01-02 00:00:00" mysql-bin.000001

监控脚本 ​

bash
#!/bin/bash
# mysql_monitor.sh

MYSQL_USER="monitor"
MYSQL_PASS="monitor_password"
LOG_FILE="/var/log/mysql_monitor.log"

# 检查 MySQL 服务状态
check_mysql_service() {
    if systemctl is-active --quiet mysql; then
        echo "$(date): MySQL service is running" >> $LOG_FILE
    else
        echo "$(date): MySQL service is down!" >> $LOG_FILE
        # 发送告警
        echo "MySQL service is down on $(hostname)" | mail -s "MySQL Alert" admin@example.com
    fi
}

# 检查连接数
check_connections() {
    CONNECTIONS=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW STATUS LIKE 'Threads_connected';" | awk 'NR==2{print $2}')
    MAX_CONNECTIONS=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW VARIABLES LIKE 'max_connections';" | awk 'NR==2{print $2}')
    
    USAGE_PERCENT=$((CONNECTIONS * 100 / MAX_CONNECTIONS))
    
    if [ $USAGE_PERCENT -gt 80 ]; then
        echo "$(date): High connection usage: $USAGE_PERCENT%" >> $LOG_FILE
    fi
}

# 检查慢查询
check_slow_queries() {
    SLOW_QUERIES=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW STATUS LIKE 'Slow_queries';" | awk 'NR==2{print $2}')
    echo "$(date): Slow queries: $SLOW_QUERIES" >> $LOG_FILE
}

# 执行检查
check_mysql_service
check_connections
check_slow_queries

# 添加到 crontab
# */5 * * * * /path/to/mysql_monitor.sh

实际应用案例 ​

电商系统数据库设计 ​

sql
-- 用户表
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    phone VARCHAR(20),
    birth_date DATE,
    gender ENUM('M', 'F', 'Other'),
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- 商品表
CREATE TABLE products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    category_id INT NOT NULL,
    brand_id INT,
    sku VARCHAR(100) UNIQUE NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    cost_price DECIMAL(10,2),
    stock_quantity INT DEFAULT 0,
    min_stock_level INT DEFAULT 0,
    weight DECIMAL(8,2),
    dimensions JSON,
    attributes JSON,
    status ENUM('active', 'inactive', 'discontinued') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_category (category_id),
    INDEX idx_brand (brand_id),
    INDEX idx_sku (sku),
    INDEX idx_price (price),
    INDEX idx_status (status),
    FULLTEXT idx_name_desc (name, description)
);

-- 订单表
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    user_id BIGINT NOT NULL,
    status ENUM('pending', 'paid', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    total_amount DECIMAL(12,2) NOT NULL,
    shipping_amount DECIMAL(8,2) DEFAULT 0,
    tax_amount DECIMAL(8,2) DEFAULT 0,
    discount_amount DECIMAL(8,2) DEFAULT 0,
    payment_method ENUM('credit_card', 'debit_card', 'paypal', 'bank_transfer'),
    shipping_address JSON,
    billing_address JSON,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_order_number (order_number),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
);

-- 订单项表
CREATE TABLE order_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(12,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id),
    
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);

-- 购物车表
CREATE TABLE shopping_cart (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_user_product (user_id, product_id),
    INDEX idx_user_id (user_id),
    INDEX idx_product_id (product_id),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

分库分表策略 ​

sql
-- 按用户 ID 分表的订单表
CREATE TABLE orders_0 LIKE orders;
CREATE TABLE orders_1 LIKE orders;
CREATE TABLE orders_2 LIKE orders;
CREATE TABLE orders_3 LIKE orders;

-- 分表路由函数
DELIMITER //

CREATE FUNCTION GetOrderTableName(user_id BIGINT)
RETURNS VARCHAR(20)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE table_suffix INT;
    SET table_suffix = user_id % 4;
    RETURN CONCAT('orders_', table_suffix);
END //

DELIMITER ;

-- 应用层分表逻辑 (Python 示例)
# def get_order_table(user_id):
#     return f"orders_{user_id % 4}"
# 
# def insert_order(user_id, order_data):
#     table_name = get_order_table(user_id)
#     sql = f"INSERT INTO {table_name} (user_id, ...) VALUES (%s, ...)"
#     cursor.execute(sql, (user_id, ...))

最佳实践 ​

数据库设计原则 ​

  1. 规范化设计

    • 遵循第三范式，避免数据冗余
    • 合理使用反规范化提升查询性能
    • 使用合适的数据类型和长度

  2. 索引策略

    • 为经常查询的列创建索引
    • 避免过多索引影响写入性能
    • 使用复合索引优化多条件查询
    • 定期分析和优化索引

  3. 表设计

    • 使用有意义的表名和列名
    • 设置合适的主键策略
    • 使用外键约束保证数据完整性
    • 合理使用枚举类型

性能优化 ​

  1. 查询优化

    • 避免 SELECT *
    • 使用 LIMIT 限制结果集
    • 优化 JOIN 查询
    • 使用合适的索引

  2. 配置优化

    • 调整 InnoDB 缓冲池大小
    • 优化连接参数
    • 配置查询缓存
    • 调整日志参数

  3. 硬件优化

    • 使用 SSD 存储
    • 增加内存容量
    • 使用多核 CPU
    • 优化网络配置

安全性 ​

  1. 访问控制

    • 使用最小权限原则
    • 定期审查用户权限
    • 使用强密码策略
    • 启用 SSL/TLS 加密

  2. 数据保护

    • 定期备份数据
    • 测试恢复流程
    • 加密敏感数据
    • 审计数据库操作

运维管理 ​

  1. 监控

    • 监控服务器资源使用
    • 监控数据库性能指标
    • 设置告警机制
    • 分析慢查询日志

  2. 维护

    • 定期更新 MySQL 版本
    • 优化表结构
    • 清理无用数据
    • 维护索引统计信息

总结 ​

MySQL 作为世界上最流行的开源关系型数据库，具有以下优势：

  • 成熟稳定：经过多年发展，功能完善，稳定性高
  • 性能优秀：优化的查询引擎，支持大规模并发
  • 生态丰富：丰富的工具和社区支持
  • 易于使用：简单的安装配置，友好的管理界面
  • 扩展性强：支持多种扩展方案，适应不同规模需求

通过合理的设计、优化和运维，MySQL 能够满足从小型应用到大型企业级系统的各种需求。掌握 MySQL 的核心概念、高级特性和最佳实践，是每个开发者和 DBA 的必备技能。

无论是 Web 应用开发、数据分析还是企业级系统建设，MySQL 都是一个值得信赖的选择。随着云计算和微服务架构的发展，MySQL 也在不断演进，为现代应用提供更好的支持。