跳至主要內容

07.备份与恢复

12a2024/10/3大约 6 分钟数据库后端sql

07.备份与恢复

7.1 备份

  • 物理备份：备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比较大

    MySQL
    # xtrabackup https://www.percona.com/downloads

# 可以直接将数据文件复制，需要知道数据库文件位置
# Windows默认为:MySQL安装目录/MySQL Server/8.0/data
# linux默认为:/var/lib/mysql
# macOS默认为:/usr/local/mysql/data
# 备份前在MySQL连接中执行 FLUSH TABLES WITH READ LOCK;，确保修改写入磁盘
# 每个数据库的文件都在对应的名称为数据库名称的子文件夹中
# 备份 db1 数据库
cp -r /var/lib/mysql/db1 /backup
# 如果包含InnoDB表，需要全部备份，不能简单地备份一个文件夹，因为InnoDB会在系统表空间维护一些信息
cp -r /var/lib/mysql/ /backup

# 备份完毕记得在MySQL连接中解锁 UNLOCK TABLES;

  • 逻辑备份：对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空间小，更灵活。逻辑备份就是备份sql语句，在恢复的时候执行备份的sql语句实现数据库数据的重现

    MySQL
    # 备份单个数据库
# 备份也可以使用其他后缀存储，比如txt
# 可以使用 --ignore-table=dbname.tbname 排除一些表
# --no-data 只备份表结构 (简写为 -d)
# --no-create-info 只备份表数据 (简写为 -t)
# --rountines 备份存储过程和函数 (简写为 -R)
# --events 备份事件 (定时任务) (简写为 -E)
mysqldump -uroot -h127.0.0.1 -p123456 dbname > backup.sql

# 备份多个数据库
mysqldump -uroot -h127.0.0.1 -p123456 --databases dbname1 dbname2 > backup.sql

# 备份所有数据库
# 可以使用 --all-databases 全量备份 (简写为 -A)
mysqldump -uroot -h127.0.0.1 -p123456 -A > backup.sql

# 备份一个数据库部分表 (备份 table1 和 table2 表)
mysqldump -uroot -h127.0.0.1 -p123456 dbname table1 table2 > backup.sql

# 筛选，仅备份表不同数据
mysqldump -uroot -h127.0.0.1 -p123456 dbname table1 --where="id < 50" > backup.sql

7.2 恢复

  • 从备份恢复

    • 物理备份恢复

      MySQL
      # 将备份内容复制到数据库文件目录下
cp -r /backup /var/lib/mysql

# 修改文件夹权限，不然 mysql 无法写入
chown -R mysql.mysql /var/lib/mysql

    • 逻辑备份恢复

      MySQL
      # !!! 备份的文件内容主要包含删除对应表格和重新创建，填入数据的语句
# 数据表的原始内容将完全覆盖

# 如果不包含数据库创建和切换语句(单库备份)，需要指定数据库
# 也可以在连接中使用 source backup.sql 恢复
mysql -uroot -h127.0.0.1 -p123456 db1 < backup.sql

# 如果希望从全量备份中分离指定数据库的语句
sed -n '/^--Current Database:`db1`/,/^--Current Database: `/p' all_database.sql > db1.sql

# 如果希望从单库备份中分离指定表的语句
cat db1.sql | sed -e '/./(H;S!d;}' -e 'x;/CREATE TABLE `class`/!d;q' > tb1_structure.sql
cat db1.sql | grep --ignore-case 'insert into `class`' > tb1_data.sql

# 恢复某些表
mysql -uroot -h127.0.0.1 -p123456 db1 < tb1_structure.sql
mysql -uroot -h127.0.0.1 -p123456 db1 < tb1_data.sql

7.3 表的导出与导入

  • 可以将表的内容导出到其他类型的文件

    MySQL
    # 1.使用mysqldump导出
# 可选选项
# 字段间隔，默认制表符\t --fields-terminated-by=','
# 字符串左右标识，默认空 --fields-optionally-enclosed-by='\"'
mysqldump -uroot -p123456 -T '/var/lib/mysql-files' db1 tb1 --fields-terminated-by=',' --fields-optionally-enclosed-by='\"'
# 会生成 tb1.sql 结构文件和 tb1.txt 数据文件
# 数据文件中不包含表头

# 2.在数据库连接中导出
# 限制文件导出位置的变量，为空表示不限制，不然必须导出到对应位置
# 默认为 /var/lib/mysql-files
# SHOW VARIABLES LIKE 'secure_file_priv';

# 支持字符串左右标识 ENCLOSED BY，支持字段间隔 SEPARATED BY
# SELECT * FROM db1.tb1 INTO OUTFILE '/var/lib/mysql-files/tb1.txt' ENCLOSED BY '\"' SEPARATED BY ','
# 数据文件中不包含表头

# 3.通过mysql命令导出
# 可以添加 --vertical 选项，将数据以 \G 垂直显示的格式输出
# 可以添加 --xml 选项，以 XML 格式导出，导出文件中可看见查询的SQL语句
mysql -uroot -p123456 --execute="SELECT * FROM tb1;" db1 > "/var/lib/mysql-files/tb1.txt"
# 数据文件包含表头

  • 通过导入其他数据文件恢复数据

    MySQL
    # 1.通过mysqlimport命令导入
# 如果希望从本地读取文件，需要添加 --local 简写为 -L
mysqlimport -uroot -p123456 db1 "/var/lib/mysql-files/tb1.txt" --fields-terminated-by=',' --field-optionally-enclosed-by='\"'
# 其他选项
# --fields-terminated-by=',' 字段分隔符
# --field-optionally-enclosed-by='\"' 字符串左右标识
# --ignore-lines=1 忽略第一行
# --coulumns="id,name,age" 指定文件中对应的字段顺序
# -d 导入前清空表

# 2.通过数据库连接导入
# FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' 指定字段分隔符和字符串左右标识
# LINE TERMINATED BY '\r\n' 默认行终止符为 \n，如果需要导入Windows生成的文件，则需要修改行终止符为 \r\n
# IGNORE 5 LINES 忽略前5行数据
# LOAD DATA INFILE '/var/lib/mysql-files/tb1.txt' INTO TABLE tb1;

7.4 迁移

  • 数据库迁移主要使用的方法就是逻辑备份，但要注意：
    • 数据库版本可能造成字符集等差异，需要转换
    • 如果是不同数据库之间迁移，要比较数据库定义的差异，比如时间类型在不同数据库的关键字不同；不同数据库的语句规范也有差异，比如SQL Server实现包含非标准的SQL语句
      • 这些差异只是实现的技术壁垒，迁移也不是完全不可能的，比如使用MyODBC实现MySQL和SQL Server之间的迁移；MySQL Migration Toolkit实现MySQL和其他数据文件之间的迁移转换；也可以手动修改导出的文件，进行迁移

7.5 误删恢复

  • 如果delete误删除了部分数据行，可以使用Flashback工具进行恢复
    • MySQL：要求binglog_format为ROW且binlog_row_image为FULL，会将binlog反过来执行，恢复数据
  • 如果truncate/drop误删除了表，由于删除表产生的日志不包含其中的数据，要求表有定期备份，可以通过最近的备份恢复出一个临时库，然后通过近期的增量日志，比如binlog，恢复除了误删以外的近期操作，最后恢复到主库
    • 建议不要直接删除表，可以在表后面添加标识to_be_deleted，观察对业务的影响再删除
    • 建议做好权限控制，避免一个账号可以误删所有表
    • 可以设置备份库，设置好延迟，比如1小时，方便恢复
  • 如果误删整个节点的数据，一般情况下，集群中有其他节点可以成为主节点，本节点需要恢复之后再重新接入，如果整个集群都被误删，需要考虑跨机箱甚至跨城市的备份
最近更新