1
# pg_basebackup: 全量基础备份
2
pg_basebackup -h localhost -U replicator \
3
  -D /backup/base -Ft -z -P --wal-method=stream
4

5
# 配置 WAL 归档（postgresql.conf）— PITR 前提
6
# wal_level = replica
7
# archive_mode = on
8
# archive_command = 'cp %p /backup/wal_archive/%f'
9

10
# PITR: 时间点恢复
11
pg_ctl stop -D /var/lib/postgresql/data
12
tar -xzf /backup/base/base.tar.gz -C /var/lib/postgresql/data
13

14
cat > /var/lib/postgresql/data/postgresql.auto.conf << 'EOF'
15
restore_command = 'cp /backup/wal_archive/%f %p'
16
recovery_target_time = '2026-05-09 03:30:00'
17
recovery_target_action = 'promote'
18
EOF
19
touch /var/lib/postgresql/data/recovery.signal
20
pg_ctl start -D /var/lib/postgresql/data

1
# 备份策略调度器（简化版）
2
import subprocess, datetime, os
3

4
class BackupScheduler:
5
    """数据库备份策略调度器"""
6

7
    def __init__(self, db_type="mysql", backup_root="/backup"):
8
        self.db_type = db_type
9
        self.backup_root = backup_root
10
        self.today = datetime.date.today().isoformat()
11

12
    def full_backup(self):
13
        """每周日凌晨全量备份"""
14
        path = f"{self.backup_root}/full/{self.today}"
15
        os.makedirs(path, exist_ok=True)
16
        if self.db_type == "mysql":
17
            cmd = f"xtrabackup --backup --target-dir={path} --user=root"
18
        else:
19
            cmd = f"pg_basebackup -h localhost -U replicator -D {path} -Ft -z"
20
        result = subprocess.run(cmd, shell=True, capture_output=True, text=True)
21
        if result.returncode != 0:
22
            self._alert(f"备份失败: {result.stderr}")
23
        return path
24

25
    def incremental_backup(self, base_path):
26
        """每日增量备份"""
27
        path = f"{self.backup_root}/incr/{self.today}"
28
        if self.db_type == "mysql":
29
            cmd = (f"xtrabackup --backup --target-dir={path} "
30
                   f"--incremental-basedir={base_path} --user=root")
31
            subprocess.run(cmd, shell=True, check=True)
32
        # PostgreSQL 增量通过 WAL 归档实现
33
        return path
34

35
    def _alert(self, msg):
36
        print(f"[ALERT] {msg}")

1
# MySQL 异步复制配置（异地灾备）
2
SET GLOBAL binlog_format = ROW;
3
SET GLOBAL binlog_row_image = FULL;
4

5
CHANGE MASTER TO
6
  MASTER_HOST='primary-site-db.internal',
7
  MASTER_PORT=3306,
8
  MASTER_USER='repl',
9
  MASTER_AUTO_POSITION=1,     -- GTID 自动定位
10
  MASTER_CONNECT_RETRY=10,
11
  MASTER_RETRY_COUNT=86400;   -- 网络中断时持续重试
12
START SLAVE;
13
-- 监控: SHOW SLAVE STATUS\G 关注 Seconds_Behind_Master

1
# MHA 配置文件 /etc/mha/app1.cnf
2
[server default]
3
manager_workdir=/var/log/mha/app1
4
manager_log=/var/log/mha/app1/manager.log
5
user=mha_monitor
6
password=mha_password
7
repl_user=repl
8
repl_password=repl_password
9
ping_interval=3
10
secondary_check_script=masterha_secondary_check -s remote_host1 -s remote_host2
11
master_ip_failover_script=/usr/local/bin/master_ip_failover
12

13
[server1]
14
hostname=192.168.1.101
15
port=3306
16
candidate_master=1
17

18
[server2]
19
hostname=192.168.1.102
20
port=3306
21
candidate_master=1
22

23
[server3]
24
hostname=192.168.1.103
25
port=3306
26
no_master=1
27

28
# 启动 MHA Monitor
29
masterha_manager --conf=/etc/mha/app1.cnf &

1
# sentinel.conf
2
port 26379
3
sentinel monitor mymaster 192.168.1.101 6379 2
4
sentinel down-after-milliseconds mymaster 5000
5
sentinel failover-timeout mymaster 60000
6
sentinel parallel-syncs mymaster 1
7

8
# 查询主节点地址
9
redis-cli -p 26379 SENTINEL get-master-addr-by-name mymaster

1
# pt-table-checksum: 校验主从数据一致性
2
pt-table-checksum \
3
  --host=master-host \
4
  --user=checksum_user \
5
  --password=checksum_pwd \
6
  --databases=mydb \
7
  --replicate=mydb.checksums \
8
  --no-check-binlog-format \
9
  --chunk-size=5000
10

11
# 查看校验结果
12
SELECT db, tbl, chunk, this_crc, master_crc,
13
       IF(this_crc = master_crc, 'OK', 'MISMATCH') AS status
14
FROM mydb.checksums
15
WHERE this_crc != master_crc;
16

17
# pt-table-sync: 修复不一致数据
18
pt-table-sync --execute --databases=mydb \
19
  --replicate=mydb.checksums \
20
  h=master-host,u=sync_user,p=sync_pwd

1
-- 自定义校验查询：对比主从节点的行数和校验和
2
SELECT
3
    schemaname,
4
    tablename,
5
    COUNT(*) AS row_count,
6
    md5(string_agg(md5(t::text), ',' ORDER BY id)) AS data_hash
7
FROM my_schema.my_table t
8
GROUP BY schemaname, tablename;
9

10
-- pg_checksums: 启用数据页校验（PG 12+，需停库）
11
-- pg_checksums --enable -D /var/lib/postgresql/data

1
# 数据库混沌实验框架（简化版）
2
import time, subprocess
3

4
class DatabaseChaosExperiment:
5
    """数据库混沌实验框架"""
6

7
    def __init__(self, experiment_name, target_host):
8
        self.name = experiment_name
9
        self.target = target_host
10
        self.results = []
11

12
    def inject_primary_failure(self):
13
        """实验: 主节点故障注入 — 假设 RTO < 30s"""
14
        print(f"[实验] {self.name}: 注入主节点故障")
15
        start = time.time()
16
        subprocess.run(f"ssh {self.target} 'systemctl stop mysql'", shell=True)
17

18
        # 观察故障转移
19
        for i in range(60):
20
            time.sleep(1)
21
            r = subprocess.run("mysql -h new-primary -e 'SELECT 1'",
22
                               shell=True, capture_output=True)
23
            if r.returncode == 0:
24
                elapsed = time.time() - start
25
                self.results.append({
26
                    "experiment": "primary_failure",
27
                    "actual_rto": round(elapsed, 1),
28
                    "passed": elapsed < 30,
29
                })
30
                break
31

32
        # 恢复原主节点
33
        subprocess.run(f"ssh {self.target} 'systemctl start mysql'", shell=True)
34

35
    def inject_network_partition(self, duration=120):
36
        """实验: 网络分区注入 — 假设系统自动降级为只读"""
37
        cmd = (f"ssh {self.target} 'tc qdisc add dev eth0 root "
38
               f"netem delay 500ms loss 50%'")
39
        subprocess.run(cmd, shell=True)
40
        time.sleep(duration)
41
        subprocess.run(f"ssh {self.target} 'tc qdisc del dev eth0 root'",
42
                       shell=True)
43

44
    def report(self):
45
        for r in self.results:
46
            status = "PASS" if r["passed"] else "FAIL"
47
            print(f"  {status} {r['experiment']}: RTO={r['actual_rto']}s")

1
# CockroachDB 多地域部署配置
2
cockroach start \
3
  --locality=region=us-east,zone=us-east-1a \
4
  --store=path=/data/cockroach \
5
  --join=node1:26257,node2:26257,node3:26257 \
6
  --advertise-addr=node1:26257 --background
7

8
# 配置地域亲和性
9
ALTER DATABASE mydb CONFIGURE ZONE USING \
10
  num_replicas = 5,
11
  constraints = '[+region=us-east:2, +region=eu-west:2, +region=ap-south:1]';
12

13
# 读优化：Lease 亲和
14
ALTER TABLE users CONFIGURE ZONE USING \
15
  lease_preferences = '[[+region=us-east]]';

1
-- MySQL 多地域读写分离（ProxySQL 路由规则）
2
-- 写走主
3
INSERT INTO mysql_query_rules (rule_id, active, match_pattern,
4
    destination_hostgroup, apply)
5
VALUES (1, 1, '^SELECT.*FOR UPDATE$', 10, 1);
6

7
-- 读走本地从
8
INSERT INTO mysql_query_rules (rule_id, active, match_pattern,
9
    destination_hostgroup, apply)
10
VALUES (2, 1, '^SELECT', 20, 1);
11

12
-- HG 10: 主站点主节点 / HG 20: 本地域从节点