MySQL和PostgreSQL：数据库复制和故障恢复技巧
1. MySQL数据库复制技巧
1.1 MySQL主从复制
MySQL主从复制是指将一个MySQL主服务器上的数据复制到一个或多个MySQL从服务器，因此保证了业务的高可用性以及数据备份恢复性。
MySQL主从复制可以分成以下两种方式：
- 基于语句的复制：主库将SQL语句发送到从库执行，该方式需要从库执行与主库完全一致的SQL语句。
- 基于行的复制：主库只将修改信息传输给从库，从库执行这些修改。该方式确保主库与从库数据一致，无需从库与主库完全一致。
以下是MySQL基于语句的复制的实现过程：
首先，开启MySQL主从复制，具体命令如下：
# 在Master服务器上创建一个新用户
CREATE USER 'repl'@'%.%.%.%' IDENTIFIED BY 'replpassword';
# 给用户授权复制Master的数据
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.%.%.%';
# 在Master服务器上设置binlog，启用binary logs功能
server-id=1
log-bin=mysql-bin
binlog-do-db=mydb
接下来，在从服务器上设置复制：
server-id=2
relay-log=mysql-relay-bin
log-slave-updates
replicate-do-db=mydb
# 针对Master服务器上的复制账号进行授权以便让从库连接Master服务器进行数据复制操作
change master to
master_host='master_ip',
master_user='repl',
master_password='replpassword',
master_log_file='mysql-bin.000001',
master_log_pos=4;
接下来，启动MySQL从服务器：
$ mysql -u username -p mydb最后，在Master服务器上插入新数据即可：
INSERT INTO mydb VALUES ('hello world');1.2 MySQL复制故障恢复
MySQL主从复制中，当复制故障时的恢复方式主要包括以下两个方面：
- 在从库上进行修复
- 在Master上进行修复
下面分别进行介绍：
1.2.1 从库上的复制故障
从库上的故障主要包括无法连接到Master服务器，主服务器关闭且已损坏等情况。当从库故障时，可以进行以下几个步骤来恢复：
- 通过show slave status命令判断与Master的连接是否正常，如果连接正常，查看Slave_IO_Running和Slave_SQL_Running字段是否为YES，确认是否处于正常复制状态。
mysql> SHOW SLAVE STATUS\G;- 如果处于停止或无法连接状态，需要重启从库，或者更换新的从库实例。
- 确认从主库二进制日志中已经mysql-bin文件，如果不存在mysql-bin文件，则确认主服务器配置是否启用了binary logging，并且是否启用了binlog-do-db参数指定了要复制的数据库。
- 确认从主库二进制文件和复制位置（即master_log_file和master_log_pos）。
1.2.2 主库上的复制故障
主服务器上的复制故障主要包括，无法连接到从服务器，从服务器已经关闭且已损坏等情况。当主服务器发生故障时，可以进行以下几个步骤来恢复：
- 使用show processlist命令查看当前在运行什么查询，并在需要升级主服务器前停止这些查询。
mysql> SHOW PROCESSLIST;- 如果主服务器无法访问，则在从数据库中使用system命令来查看主服务器是否有相应的存档文件，并将存档文件保存到本地。
mysql> SYSTEM mysqlbinlog --read-from-remote-server --host=master_host --user=repl --password=password mysql-bin.000001 > master_archives.sql- 尝试修复主服务器上的复制故障，如果无法修复，则将存档文件应用到新主服务器中。
mysql> SQL_SLAVE_SKIP_COUNTER = number;例如，将跳过下一条错误SQL语句的存储过程：
mysql> SET SQL_LOG_BIN=0;
mysql> call sp_name(args);
mysql> SET SQL_LOG_BIN=1;
2. PostgreSQL数据库复制技巧
2.1 PostgreSQL主从复制
PostgreSQL中的主从复制技术同样可以用来构建高可用性和数据备份/恢复性解决方案。其中，主从复制的实现过程与MySQL中的主从复制相似。这里不再赘述。
2.2 PostgreSQL故障恢复
PostgreSQL故障恢复主要包括物理备份和逻辑备份两种方式。
2.2.1 物理备份
物理备份是指对数据库文件系统完整备份，从而备份了所有的数据、索引、表和视图等。物理备份主要通过POSTGRESQL xlog传输日志实现。在进行物理备份时，需要了解以下三个关键概念：
- WAL（Write-Ahead Logging）
- Checkpoint
- Base Backup
需要注意的是，在进行物理备份的同时，仍需要对数据库进行活动备份。
以下是基于pg_basebackup进行备份的具体过程：
首先，启动PostgreSQL并连接到数据库：
$ pg_ctl start
$ psql -d mydb
接下来，创建物理备份目录，确保PostgreSQL用户有对该目录的读写权限：
PGDATA=${PGDATA:-/var/lib/pgsql/10/data} # 数据库目录路径
BACKUP_DIR=/mnt/postgres/backup # 备份目录路径
mkdir -p $BACKUP_DIR
然后，执行备份操作：
pg_basebackup -D $BACKUP_DIR -Ft -z -P
# 备份目录将保存以.tar.gz为扩展名的文件并以Base Backup标注文件名。
2.2.2 逻辑备份
逻辑备份是指将数据库内容以文本格式输出，保存到文本文件中，方便进行人工阅读和编辑。
以下是基于pg_dump进行备份的操作过程：
首先，连接至PostgreSQL，并指定要备份的数据库：
psql mydatabase然后，运行以下命令：
pg_dump mydatabase > /path/to/mybackup.sql这样，备份文件就会被保存到指定的位置。
2.3 PostgreSQL数据修复
PostgreSQL中，当发生故障时，可以采用以下两种方法进行数据修复：
2.3.1 通过备份恢复数据
通过备份恢复数据是指使用pg_restore命令将备份文件加载到数据库系统中。需要注意的是，该备份文件必须是恰当的，并且可以考虑恢复到发生故障的系统状态。
以下是使用pg_restore命令进行备份文件恢复的过程：
首先，执行以下命令以删除当前的数据库和用户：
DROP USER myname;
DROP DATABASE mydatabase;
CREATE DATABASE mydatabase;
CREATE ROLE myname WITH LOGIN PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myname;
然后，还原备份文件：
gunzip -c backup.tar.gz | pg_restore -U myname -d mydatabase2.3.2 通过日志恢复
另一种数据修复方式是使用PostgreSQL WAL日志进行恢复，这种方式可以最大限度地减少丢失的数据量。需要注意的是，该方法仅适用于企业级应用程序或IT环境。
以下是基于时间点和LSN的恢复过程：
pg_controldata /var/lib/pgsql/10/data/ # 找到last checkpoint信息
ls -lah /var/lib/pgsql/10/data/pg_xlog/RECOVERYXLOG # 找到历史日志列表
# 应用历史日志
cd /var/lib/pgsql/10/data
rm -rf xlog/*
cp -r /mnt/xlog_archive/* xlog/
pg_resetxlog /var/lib/pgsql/10/data
pg_start_recovery -D /var/lib/pgsql/10/data
tail -f /var/lib/pgsql/10/data/pg_log/postgresql-Mon.log.1
到目前为止，我们已经了解了MySQL和PostgreSQL数据库复制和故障恢复技巧，这对一些企业级应用程序和IT环境是十分有用的。