MySQL 从入门到精通：SQL、索引、优化与高可用

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

# 启动 MySQL 容器
docker run -d \
  --name mysql8 \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=root123 \
  -v /opt/mysql/data:/var/lib/mysql \
  -v /opt/mysql/conf:/etc/mysql/conf.d \
  mysql:8.0

# 进入容器连接 MySQL
docker exec -it mysql8 mysql -uroot -proot123

# Ubuntu / Debian
apt update
apt install mysql-server-8.0 -y
systemctl start mysql
systemctl enable mysql

# CentOS / Rocky Linux
yum install mysql-server -y
systemctl start mysqld
systemctl enable mysqld

# 安全初始化
mysql_secure_installation

-- 查看当前存储引擎
SHOW ENGINES;

-- 查看表引擎
SHOW TABLE STATUS WHERE Name = 'users';

-- 建表时指定引擎
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 修改已有表引擎
ALTER TABLE users ENGINE=InnoDB;

┌─────────────────────────────────────────┐
│              InnoDB 内存架构              │
├──────────────────┬──────────────────────┤
│   Buffer Pool    │    Change Buffer     │
│  (数据页 + 索引页) │  (二级索引变更缓存)    │
├──────────────────┴──────────────────────┤
│          Log Buffer (redo log 缓存)      │
├─────────────────────────────────────────┤
│           Adaptive Hash Index            │
└─────────────────────────────────────────┘

-- 查看缓冲池大小
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 设置缓冲池大小（单位：字节）
SET GLOBAL innodb_buffer_pool_size = 4 * 1024 * 1024 * 1024;  -- 4GB

-- 查看 redo log 配置
SHOW VARIABLES LIKE 'innodb_log_file_size';
SHOW VARIABLES LIKE 'innodb_log_files_in_group';

-- 查看 change buffer 大小
SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';  -- 默认25（占缓冲池的25%）

客户端连接 → 连接管理 → 查询缓存(8.0已移除)
    → 解析器(词法分析+语法分析) → 预处理器
    → 优化器(选择执行计划) → 执行器
    → 存储引擎 → 返回结果

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';

-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';

-- 查看慢查询日志状态
SHOW VARIABLES LIKE 'slow_query_log';

-- 正确用法：使用无符号 + AUTO_INCREMENT
CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    amount DECIMAL(10,2) NOT NULL
);

-- 注意：ZEROFILL 已废弃，不要使用
-- 显示宽度（INT(11)）不影响存储，仅用于 ZEROFILL
CREATE TABLE t (a INT(11));  -- 显示宽度无实际作用

-- FLOAT / DOUBLE：近似存储，存在精度损失
CREATE TABLE prices_approx (
    product_id INT,
    price FLOAT(10,2)   -- 不精确，避免用于金额
);

-- DECIMAL：精确存储，用于金额
CREATE TABLE prices_exact (
    product_id INT,
    price DECIMAL(10,2)   -- 精确，推荐用于金额
);

-- 演示精度问题
INSERT INTO prices_approx VALUES (1, 0.1);
INSERT INTO prices_exact VALUES (1, 0.1);

SELECT * FROM prices_approx WHERE price = 0.1;   -- 可能查不到！
SELECT * FROM prices_exact WHERE price = 0.1;    -- 正常查到

CREATE TABLE strings_demo (
    -- CHAR: 定长，适合固定长度的字段
    country_code CHAR(2) NOT NULL,        -- 'CN', 'US'

    -- VARCHAR: 变长，适合长度不固定的字段
    email VARCHAR(255) NOT NULL,
    username VARCHAR(64) NOT NULL,

    -- TEXT: 长文本，不设默认值
    bio TEXT,

    -- BLOB: 存储二进制数据（一般用文件系统替代）
    avatar BLOB,

    -- VARCHAR  vs  TEXT 选择原则：
    -- 1. 长度 < 255 且长度稳定 → CHAR
    -- 2. 长度 < 5000 且需要建立索引 → VARCHAR
    -- 3. 超长文本 → TEXT（但索引需要前缀索引）
    INDEX idx_bio (bio(100))   -- 前缀索引
);

CREATE TABLE events (
    id INT PRIMARY KEY,

    -- 创建时间：自动设为当前时间
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- 更新时间：每次修改自动更新
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- 生日：无时区概念，用 DATE
    birthday DATE,

    -- 会议时间：精确到秒，用 DATETIME
    meeting_time DATETIME(3),   -- 毫秒精度

    -- 仅时间：用 TIME
    daily_alarm TIME
);

-- 时区影响演示
SET time_zone = '+00:00';
INSERT INTO events (id, meeting_time) VALUES (1, '2024-09-12 10:00:00');

SET time_zone = '+08:00';
SELECT meeting_time FROM events WHERE id = 1;  -- TIMESTAMP 会转换，DATETIME 不变

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON,
    CHECK (JSON_VALID(attributes))
);

INSERT INTO products VALUES (1, '手机',
    '{"color": "黑色", "storage": "256GB", "price": 5999}');

-- JSON 查询
SELECT
    name,
    JSON_EXTRACT(attributes, '$.color') AS color,
    attributes->>'$.storage' AS storage
FROM products
WHERE JSON_EXTRACT(attributes, '$.price') > 5000;

-- JSON_TABLE 展开为行（8.0+）
SELECT p.name, jt.*
FROM products p,
JSON_TABLE(attributes, '$' COLUMNS (
    color VARCHAR(10) PATH '$.color',
    storage VARCHAR(10) PATH '$.storage',
    price INT PATH '$.price'
)) AS jt
WHERE jt.price > 5000;

ALTER TABLE products
ADD COLUMN price INT GENERATED ALWAYS AS (attributes->>'$.price') STORED,
ADD INDEX idx_price (price);

-- 好的设计
CREATE TABLE user_profile (
    id          INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    status      TINYINT UNSIGNED NOT NULL DEFAULT 1,     -- 1-正常 2-禁用
    gender      TINYINT UNSIGNED NOT NULL DEFAULT 0,     -- 0-未知 1-男 2-女
    age         TINYINT UNSIGNED NOT NULL DEFAULT 0,
    balance     DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    email       VARCHAR(255)    NOT NULL DEFAULT '',
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

B+Tree 结构示意（阶数=3，简化版）：

                    [50, 80]
                   /    |    \
            [10,30]  [60,70]  [90,100]
            /    |    /   |    |    \
          (1-9) (31-49) ...   ...   ...
           ↑ 叶子节点双向链表连接 ↑

-- 创建表时自动建立聚簇索引
CREATE TABLE users (
    id INT PRIMARY KEY,          -- 聚簇索引
    name VARCHAR(50),
    email VARCHAR(100),
    INDEX idx_name (name)        -- 二级索引
);

-- 查询流程对比
EXPLAIN SELECT * FROM users WHERE id = 1;
-- → type: const, key: PRIMARY（直接走聚簇索引，无需回表）

EXPLAIN SELECT * FROM users WHERE name = '张三';
-- → type: ref, key: idx_name, Extra: NULL（走二级索引，然后回表）

EXPLAIN SELECT id, name FROM users WHERE name = '张三';
-- → type: ref, key: idx_name, Extra: Using index（覆盖索引，无需回表）

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT NOT NULL,
    status TINYINT NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_user_status_time (user_id, status, created_at)
);

-- ✅ 走索引
EXPLAIN SELECT * FROM orders WHERE user_id = 100;               -- user_id
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1; -- user_id + status
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND created_at > '2024-01-01'; -- user_id
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1 AND created_at > '2024-01-01'; -- 全部

-- ❌ 不走索引（跳过了最左列）
EXPLAIN SELECT * FROM orders WHERE status = 1;                  -- 无 user_id
EXPLAIN SELECT * FROM orders WHERE created_at > '2024-01-01';   -- 无 user_id

-- 排序优化
CREATE INDEX idx_user_time ON orders (user_id, created_at);

EXPLAIN SELECT * FROM orders
WHERE user_id = 100
ORDER BY created_at DESC;
-- Extra: Using index condition（无 filesort，排序利用索引）

-- 表结构
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    INDEX idx_dept_name (department, name, salary)
);

-- 覆盖索引查询：所需列全部在 idx_dept_name 中
EXPLAIN SELECT department, name, salary
FROM employees
WHERE department = '研发部';
-- Extra: Using index（无需回表）

-- 非覆盖索引查询：所需列不在索引中，需要回表
EXPLAIN SELECT department, name, salary, id
FROM employees
WHERE department = '研发部';
-- Extra: Using index（id是主键，也在二级索引叶子节点中，所以仍是覆盖索引）

-- 非覆盖：需要回表
EXPLAIN SELECT *
FROM employees
WHERE department = '研发部';
-- Extra: NULL（需要回表读取完整数据行）

CREATE TABLE people (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    INDEX idx_name_age (name, age)
);

-- 无 ICP：先根据 name 查出所有记录（回表），再用 age 过滤
-- 有 ICP：在索引遍历时同时用 name 和 age 过滤，减少回表

EXPLAIN SELECT * FROM people
WHERE name LIKE '张%' AND age = 30;
-- Extra: Using index condition（表示使用了 ICP）

EXPLAIN SELECT u.name, o.amount, o.created_at
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 1
ORDER BY o.created_at DESC
LIMIT 10;

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;          -- 超过2秒的查询
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未用索引的查询

-- 慢查询日志文件位置
SHOW VARIABLES LIKE 'slow_query_log_file';

# 使用 mysqldumpslow 分析慢查询日志
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s t: 按查询时间排序
# -t 10: 显示前10条

# 使用 pt-query-digest（Percona Toolkit）
pt-query-digest /var/log/mysql/slow.log

-- ❌ 糟糕：OFFSET 越大越慢
SELECT * FROM orders ORDER BY id DESC LIMIT 10 OFFSET 1000000;
-- MySQL 会扫描 1000010 行，然后丢弃前 1000000 行

-- ✅ 第一次查询
SELECT id, order_no, amount, created_at
FROM orders
ORDER BY id DESC
LIMIT 10;

-- ✅ 后续查询：带上上一页最后一条的 id
SELECT id, order_no, amount, created_at
FROM orders
WHERE id < 100   -- 上一页最后一条记录的 id
ORDER BY id DESC
LIMIT 10;

-- ✅ 利用覆盖索引减少回表
SELECT *
FROM orders
WHERE id >= (
    SELECT id FROM orders
    ORDER BY id
    LIMIT 1000000, 1
)
ORDER BY id
LIMIT 10;

-- ✅ JOIN 方式
SELECT o.*
FROM orders o
JOIN (
    SELECT id FROM orders
    ORDER BY id
    LIMIT 1000000, 10
) AS tmp ON o.id = tmp.id;

-- InnoDB 中 COUNT(*) 需要扫描行数，无法像 MyISAM 那样直接读取

-- ❌ 慢：扫描大量数据
SELECT COUNT(*) FROM orders;

-- ✅ 使用二级索引覆盖扫描（比聚簇索引小）
EXPLAIN SELECT COUNT(id) FROM orders;
EXPLAIN SELECT COUNT(*) FROM orders;
-- 优化器会自动选择最小的二级索引

-- 不同条件统计
SELECT COUNT(*) FROM orders WHERE status = 1;      -- 需要过滤
SELECT COUNT(*) FROM orders WHERE status = 2;      -- 需要过滤

-- ✅ 使用汇总表实时缓存
CREATE TABLE order_stats (
    status TINYINT PRIMARY KEY,
    cnt BIGINT UNSIGNED NOT NULL
);

-- 通过触发器或应用层在 INSERT/DELETE 时更新
INSERT INTO order_stats (status, cnt) VALUES (1, 0)
ON DUPLICATE KEY UPDATE cnt = cnt + 1;

-- 查询时直接读取汇总表
SELECT cnt FROM order_stats WHERE status = 1;

-- ✅ 8.0.12+：秒级完成，不锁表
ALTER TABLE orders ADD COLUMN remark VARCHAR(200) DEFAULT '',
ALGORITHM=INSTANT;

-- 不支持 INSTANT 的操作会回退到 INPLACE 或 COPY

# 安装 Percona Toolkit
apt install percona-toolkit

# 在线修改表结构
pt-online-schema-change --alter "ADD COLUMN remark VARCHAR(200) DEFAULT ''" \
  D=db_name,t=orders \
  --execute \
  --check-replication-filters

-- 1. 创建新表
CREATE TABLE orders_new LIKE orders;
ALTER TABLE orders_new ADD COLUMN remark VARCHAR(200) DEFAULT '';

-- 2. 分批迁移数据（使用工具或手动）
INSERT INTO orders_new SELECT * FROM orders WHERE id BETWEEN 1 AND 10000;
-- ... 继续分批
INSERT INTO orders_new SELECT * FROM orders WHERE id > 9990000;

-- 3. 锁表并交换
LOCK TABLES orders WRITE, orders_new WRITE;
-- 检查数据一致性
ALTER TABLE orders RENAME TO orders_old;
ALTER TABLE orders_new RENAME TO orders;
UNLOCK TABLES;

锁分类：
├── 共享锁（S Lock）：读锁，允许多个事务同时读
├── 排他锁（X Lock）：写锁，互斥
├── 意向共享锁（IS Lock）：表级锁，标记事务准备加 S 锁
├── 意向排他锁（IX Lock）：表级锁，标记事务准备加 X 锁
├── 行锁（Record Lock）：锁定单行记录
├── 间隙锁（Gap Lock）：锁定范围，防止幻读
└── 临键锁（Next-Key Lock）：行锁 + 间隙锁的组合

-- 共享锁（S锁）：SELECT ... LOCK IN SHARE MODE（8.0 前）/ FOR SHARE（8.0+）
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR SHARE;  -- S锁，其他事务可读不可写
-- COMMIT;

-- 排他锁（X锁）：SELECT ... FOR UPDATE
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;  -- X锁，其他事务不可读也不可写
UPDATE users SET name = '新名字' WHERE id = 1;
-- COMMIT;

-- 行锁演示
CREATE TABLE demo_lock (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=InnoDB;

INSERT INTO demo_lock VALUES (1, 'A'), (2, 'B'), (3, 'C');

-- 事务1
BEGIN;
UPDATE demo_lock SET name = 'A1' WHERE id = 1;  -- 锁住 id=1

-- 事务2
BEGIN;
UPDATE demo_lock SET name = 'B1' WHERE id = 2;  -- ✅ 可以执行，不同行不冲突
UPDATE demo_lock SET name = 'A2' WHERE id = 1;  -- ❌ 阻塞，等待事务1释放

CREATE TABLE t_lock (
    id INT PRIMARY KEY,
    val INT,
    INDEX idx_val (val)
) ENGINE=InnoDB;

INSERT INTO t_lock VALUES (1, 10), (3, 30), (5, 50);

-- 事务1
BEGIN;
SELECT * FROM t_lock WHERE val BETWEEN 20 AND 40 FOR UPDATE;
-- 锁定范围：(10, 30], (30, 50]  即 val=30 的行锁 + 两边的间隙锁

-- 事务2：以下操作均被阻塞（间隙锁阻止插入）
INSERT INTO t_lock VALUES (2, 20);  -- ❌ val=20 在(10,30)间隙内
INSERT INTO t_lock VALUES (4, 40);  -- ❌ val=40 在(30,50)间隙内

-- 事务1
COMMIT;  -- 释放锁，事务2的插入才继续执行

-- 模拟死锁

-- 事务1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 锁住 id=1
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 等待 id=2

-- 事务2
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;  -- 锁住 id=2
UPDATE accounts SET balance = balance + 100 WHERE id = 1;  -- 等待 id=1
-- ❌ 死锁！InnoDB 会自动检测并回滚其中一个事务

-- 查看死锁日志
SHOW ENGINE INNODB STATUS\G
-- 查看最近一次死锁的详细信息

-- 1. 统一访问顺序
-- ✅ 所有事务都按 id 从小到大更新
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 2. 缩短事务时间
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- ... 避免在事务中做耗时操作（RPC、文件读写等）
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 3. 降低隔离级别（如果可以接受）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- RC 级别没有间隙锁，减少死锁概率

-- 4. 合理选择锁范围
-- 用 UPDATE ... WHERE id = ? 代替 SELECT ... FOR UPDATE + UPDATE

-- 查看当前隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';

-- 设置会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 演示 MVCC（需要两个会话）

-- 会话 A
BEGIN;  -- 开启事务，事务ID=100
SELECT * FROM accounts WHERE id = 1;  -- 看到 balance=1000

-- 会话 B
BEGIN;  -- 事务ID=101
UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;

-- 会话 A
SELECT * FROM accounts WHERE id = 1;
-- REPEATABLE READ: 依然看到 balance=1000（读取的是事务100的快照）
-- READ COMMITTED: 看到 balance=500（读取的是最新已提交数据）
COMMIT;

Master → binlog dump thread → binlog
                              ↓  网络传输
Slave  → I/O thread → relay log → SQL thread → 数据更新

# master 配置 (my.cnf)
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=ROW                # ROW 格式推荐（最精确）
binlog-do-db=myapp               # 要复制的数据库
expire-logs-days=7               # 日志保留7天
max-binlog-size=500M             # 每个 binlog 文件大小

# slave 配置 (my.cnf)
[mysqld]
server-id=2
relay-log=relay-bin
read-only=1                      # 从库只读
log-replica-updates=ON           # 记录从库自己的 binlog（级联复制需要）

// Spring Boot + ShardingSphere-JDBC，代码零侵入
// application.yml
spring:
  shardingsphere:
    datasource:
      names: write-ds,read-ds-1,read-ds-2
      write-ds:
        url: jdbc:mysql://master:3306/myapp
        username: root
        password: root123
      read-ds-1:
        url: jdbc:mysql://slave1:3306/myapp
        username: root
        password: root123
      read-ds-2:
        url: jdbc:mysql://slave2:3306/myapp
        username: root
        password: root123
    rules:
      readwrite-splitting:
        data-sources:
          myapp:
            write-data-source-name: write-ds
            read-data-source-names: read-ds-1,read-ds-2
            load-balancer-name: round_robin

# 使用 ProxySQL
docker run -d --name proxysql -p 6033:6033 -p 6032:6032 proxysql/proxysql

# 配置读写分离
mysql -uadmin -padmin -h127.0.0.1 -P6032

INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, 'master', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, 'slave1', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, 'slave2', 3306);

INSERT INTO mysql_query_rules (rule_id, active, match_digit, match_pattern, destination_hostgroup)
VALUES (1, 1, 1, '^SELECT.*', 1);    -- SELECT 走读库
INSERT INTO mysql_query_rules (rule_id, active, match_digit, match_pattern, destination_hostgroup)
VALUES (2, 1, 1, '.*', 0);            -- 其他走写库

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

# MGR 配置（每个节点）
[mysqld]
server-id=1
gtid_mode=ON
enforce_gtid_consistency=ON
log-bin=mysql-bin
binlog-format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE

# MGR 特定配置
plugin_load_add='group_replication.so'
group_replication_group_name='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee'
group_replication_start_on_boot=off
group_replication_bootstrap_group=off
group_replication_local_address='192.168.1.10:33061'
group_replication_group_seeds='192.168.1.10:33061,192.168.1.11:33061,192.168.1.12:33061'
group_replication_ip_whitelist='192.168.1.0/24'
group_replication_single_primary_mode=ON   # 单主模式

-- 在第一个节点上启动组
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

-- 在后续节点上加入组
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='Repl@123' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;

-- 查看组状态
SELECT * FROM performance_schema.replication_group_members;
SELECT * FROM performance_schema.replication_group_member_stats\G

-- 取模分片（简单但扩容困难）
-- 创建两张分表
CREATE TABLE orders_0 LIKE orders;
CREATE TABLE orders_1 LIKE orders;

-- 应用层根据 user_id % 2 决定写入哪张表
-- 或用 ShardingSphere 自动路由

-- 范围分片（适合有自然范围的数据）
CREATE TABLE orders_2024_q1 LIKE orders;
CREATE TABLE orders_2024_q2 LIKE orders;

# ShardingSphere-JDBC 配置
spring:
  shardingsphere:
    rules:
      sharding:
        tables:
          orders:
            actual-data-nodes: ds$->{0..1}.orders_$->{0..1}
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: db-mod
            table-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: table-mod
        sharding-algorithms:
          db-mod:
            type: MOD
            props:
              sharding-count: 2
          table-mod:
            type: MOD
            props:
              sharding-count: 2

# 备份单个数据库
mysqldump -uroot -p --single-transaction --routines --triggers --events myapp > myapp.sql

# 备份所有数据库
mysqldump -uroot -p --single-transaction --all-databases --master-data=2 > all.sql

# 备份特定表
mysqldump -uroot -p myapp users orders > user_orders.sql

# 只导出表结构，不导数据
mysqldump -uroot -p --no-data myapp > schema.sql

# 只导出数据，不导表结构
mysqldump -uroot -p --no-create-info myapp > data.sql

# 压缩备份
mysqldump -uroot -p --single-transaction myapp | gzip > myapp_$(date +%F).sql.gz

# 恢复备份
mysql -uroot -p myapp < myapp.sql
# 或从压缩文件恢复
gunzip < myapp_2024-09-12.sql.gz | mysql -uroot -p myapp

# 1. 查看 binlog 列表
mysql -uroot -p -e "SHOW BINARY LOGS;"

# 2. 将 binlog 导出为 SQL
mysqlbinlog --start-datetime="2024-09-12 10:00:00" \
            --stop-datetime="2024-09-12 12:00:00" \
            /var/lib/mysql/mysql-bin.000005 > recovery.sql

# 或通过位置恢复
mysqlbinlog --start-position=1000 \
            --stop-position=5000 \
            /var/lib/mysql/mysql-bin.000005 > recovery.sql

# 3. 恢复
mysql -uroot -p myapp < recovery.sql

# 完整恢复流程（从全量备份恢复到指定时间点）
# 1) 恢复最近一次全量备份
mysql -uroot -p < full_backup.sql

# 2) 应用之后的 binlog（到灾难发生前一刻）
mysqlbinlog --stop-datetime="2024-09-12 14:30:00" \
  /var/lib/mysql/mysql-bin.000005 \
  /var/lib/mysql/mysql-bin.000006 | mysql -uroot -p

每日计划：
  凌晨 02:00  - XtraBackup 全量备份（周日）或增量备份（周一~周六）
  每小时      - binlog 定时同步到备份服务器
  实时        - binlog 保留至少 7 天

每周验证：
  在测试环境恢复最近一次全量 + binlog，验证数据完整性

灾难恢复 SLA：
  全量备份 + binlog → RPO < 1 小时，RTO < 4 小时