• mysql 物理备份及恢复

    一、物理复制的基本概念

    物理备份:直接复制数据库文件，适用于大型的数据库环境，不受存储引擎的限制，但不能恢复到不同的mysql版本

    完整备份：也叫完全备份，每次将所有数据（不管自第一次备份有没有修改过），进行一次完整的复制，备份后会清楚文件的存档属性，方便日后增量备份或者差异备份进行版本比较。

    特点：占用空间大，备份速度慢，但是恢复时一次恢复到位，恢复速度快

    增量备份：每次备份上一次备份到现在产生的数据

    在第一次完整备份后，第二次开始每次都添加了存档属性，并在备份后将存档属性清除（为了在下一次备份时文档是否有变化，因为用户在每次备份以后修改清除存档属性的文件，存档属性就会自动加上，告诉系统这些文件有变化，下一次备份这些文件，这就是增加备份的工作机制）

    特点：备份体积小，备份速度快，但是恢复的时候，需要按备份的时间顺序，逐个备份版本进行恢复，恢复时间长。

    差异备份：只备份和完整备份不一样的

    特点：占用空间的增量备份大，比完整备份小，恢复时仅需恢复第一个完整版和最后一个差异版，恢复速度介于完整备份和增量备份之间

    二、安装xtrabackup

    下载安装包并解压安装

    三、完全备份和数据恢复

    1.创建备份目录

    [root@localhost ~]# mkdir /xt/full -p

    2.备份

    语法：innobackupx --user=用户 --password='密码'   备份目录

    1. [root@localhost yum.repos.d]# innobackupex --user=root --password='123' /xtrabackup/full
    2. xtrabackup: recognized server arguments: --server-id=1 --datadir=/var/lib/mysql --log_bin=/var/lib/mysql/mysql-bin.log 
    3. xtrabackup: recognized client arguments: 
    4. 231006 11:46:47 innobackupex: Starting the backup operation
    5.  
    6. IMPORTANT: Please check that the backup run completes successfully.
    7.            At the end of a successful backup run innobackupex
    8.            prints "completed OK!".
    9.  
    10. 231006 11:46:48  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
    11.  
    12. .
    13. .
    14. .
    15.  
    16. 231006 11:46:49 [00]        ...done
    17. xtrabackup: Transaction log of lsn (3698047) to (3698056) was copied.
    18. 231006 11:46:49 completed OK!

    3.查看备份文件

    1. [root@localhost ~]# cd /xt/full
    2. [root@localhost full]# ls
    3.  2023-10-06_11-46-47
    4. [root@localhost full]# cd 2023-10-06_11-46-47/
    5. [root@localhost 2023-10-06_11-46-47]# ls
    6. backup-my.cnf  hf              mysql               sys                     xtrabackup_info
    7. db1            ib_buffer_pool  performance_schema  xtrabackup_binlog_info  xtrabackup_logfile
    8. db3            ibdata1         school              xtrabackup_checkpoints

    4.完全备份恢复数据

    （1）关闭数据库

    1. [root@localhost ~]# systemctl stop mysqld
    2. [root@localhost ~]# rm -rf /var/lib/mysql/*

    （2）恢复之前的验证

    1. [root@localhost ~]# innobackupex --apply-log /xt/full/2023-10-06_11-46-47/
    2. xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --
    3. .
    4. innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1 
    5. xtrabackup: recognized client arguments: 
    6. 231006 12:35:55 innobackupex: Starting the apply-log operation
    7.  
    8. IMPORTANT: Please check that the apply-log run completes successfully.
    9.            At the end of a successful apply-log run innobackupex
    10.            prints "completed OK!".
    11.  
    12. innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
    13. xtrabackup: cd to /xt/full/2023-10-06_11-46-47/
    14. .
    15. .
    16. 231006 12:35:59 completed OK!

    （3）查看配置文件，确认数据库恢复目录

    1. [root@localhost ~]# vim /etc/my.cnf
    2.  
    3. datadir=/var/lib/mysql

    （4）恢复数据

    1. [root@localhost ~]# innobackupex --copy-back /xt/full/2023-10-06_11-46-47/
    2. xtrabackup: recognized server arguments: --server-id=1 --datadir=/var/lib/mysql --log_bin=/var/lib/mysql/mysql-bin.log 
    3. xtrabackup: recognized client arguments: 
    4. 231006 12:38:02 innobackupex: Starting the copy-back operation
    5.  
    6. IMPORTANT: Please check that the copy-back run completes successfully.
    7.            At the end of a successful copy-back run innobackupex
    8.            prints "completed OK!".
    9.  
    10. innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
    11. 231006 12:38:02 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
    12. .
    13. .
    14. 231006 12:38:02 [01]        ...done
    15. 231006 12:38:02 completed OK!

    （5）修改权限

    # chown mysql.mysql  /var/lib/mysql -R

    （6）启动数据库

    # systemctl start mysqld

    四、增量备份和数据恢复

    1.先完整备份（周一）

    语法：innobackupex --user=用户 --password='密码' 备份路径

    1. [root@localhost log]# innobackupex --user=root --password='123' /opt/full
    2. xtrabackup: recognized server arguments: --server-id=1 --log_bin=/opt/log/mysql-bin.log --datadir=/var/lib/mysql 
    3. xtrabackup: recognized client arguments: 
    4. 231007 21:05:07 innobackupex: Starting the backup operation
    5.  
    6. IMPORTANT: Please check that the backup run completes successfully.
    7.            At the end of a successful backup run innobackupex
    8.            prints "completed OK!".
    9.  
    10. 231007 21:05:07  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
    11. 231007 21:05:07  version_check Connected to MySQL server
    12. 231007 21:05:07  version_check Executing a version check against the server...
    13. 231007 21:05:07  version_check Done.
    14. 231007 21:05:07 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
    15. Using server version 5.7.43-log
    16. innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
    17. xtrabackup: uses posix_fadvise().
    18. .
    19. .
    20. xtrabackup: Transaction log of lsn (3080362) to (3080371) was copied.
    21. 231007 21:05:08 completed OK!
    22.  
    23. [root@localhost notfull]# cd /opt/full
    24. [root@localhost full]# ls
    25. 2023-10-07_21-05-07

    2.增量备份(周二)

    语法： innobackupex --user=用户 --password='密码'  --incremental 备份路径 --incremental-basedir=完整备份的文件（周一）

    1. [root@localhost log]# innobackupex --user=root --password='123' --incremental /opt/notfull --incremental-basedir=/opt/full/2023-10-07_21-05-07/
    2. xtrabackup: recognized server arguments: --server-id=1 --log_bin=/opt/log/mysql-bin.log --datadir=/var/lib/mysql 
    3. xtrabackup: recognized client arguments: 
    4. 231007 21:16:51 innobackupex: Starting the backup operation
    5.  
    6. IMPORTANT: Please check that the backup run completes successfully.
    7.            At the end of a successful backup run innobackupex
    8.            prints "completed OK!".
    9.  
    10. 231007 21:16:51  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
    11. 231007 21:16:51  version_check Connected to MySQL server
    12. 231007 21:16:51  version_check Executing a version check against the server...
    13. 231007 21:16:51  version_check Done.
    14. 231007 21:16:51 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
    15. Using server version 5.7.43-log
    16. innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
    17. incremental backup from 3080362 is enabled.
    18. xtrabackup: uses posix_fadvise().
    19. .
    20. .
    21. xtrabackup: Transaction log of lsn (3080717) to (3080726) was copied.
    22. 231007 21:16:52 completed OK!
    23.  
    24. [root@localhost log]# cd /opt/notfull
    25. [root@localhost notfull]# ls
    26. 2023-10-07_21-16-51

    3.增量备份（周三）

    语法：innobackupex --user=用户 --password='密码' --incremental 备份的路径 --incremental-basedir=上次备份的文件（周二）

    1. [root@localhost full]# innobackupex --user=root --password='123' --incremental /opt/notfull --incremental-basedir=/opt/notfull/2023-10-07_21-16-51/
    2. xtrabackup: recognized server arguments: --server-id=1 --log_bin=/opt/log/mysql-bin.log --datadir=/var/lib/mysql 
    3. xtrabackup: recognized client arguments: 
    4. 231007 21:22:21 innobackupex: Starting the backup operation
    5.  
    6. IMPORTANT: Please check that the backup run completes successfully.
    7.            At the end of a successful backup run innobackupex
    8.            prints "completed OK!".
    9.  
    10. 231007 21:22:21  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
    11. 231007 21:22:21  version_check Connected to MySQL server
    12. 231007 21:22:21  version_check Executing a version check against the server...
    13. 231007 21:22:21  version_check Done.
    14. 231007 21:22:21 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
    15. Using server version 5.7.43-log
    16. innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
    17. incremental backup from 3080717 is enabled.
    18. xtrabackup: uses posix_fadvise()
    19. .
    20. .
    21. xtrabackup: Transaction log of lsn (3081072) to (3081081) was copied.
    22. 231007 21:22:22 completed OK!
    23.  
    24. [root@localhost full]# cd /opt/notfull
    25. [root@localhost notfull]# ls
    26. 2023-10-07_21-16-51  2023-10-07_21-22-21

    4.增量备份恢复流程

    1.关闭数据库

    [root@localhost ~]# systemctl stop mysqld

    2.清理环境（生产环境不可用）

    [root@localhost ~]# rm -rf /var/lib/mysql/*

    3.依次重演回滚

    （1）重演周一
    1. innobackupex --apply-log --redo-only /opt/full/2023-10-07_21-05-07/
    2. xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1 
    3. xtrabackup: recognized client arguments: 
    4. 231007 21:26:59 innobackupex: Starting the apply-log operation
    5.  
    6. IMPORTANT: Please check that the apply-log run completes successfully.
    7.            At the end of a successful apply-log run innobackupex
    8.            prints "completed OK!".
    9.  
    10. innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
    11. xtrabackup: cd to /opt/full/2023-10-07_21-05-07/
    12. xtrabackup: This target seems to be not prepared yet.
    13. InnoDB: Number of pools: 1
    14. xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(3080362)
    15. xtrabackup: using the following InnoDB configuration for recovery:
    16. .
    17. .
    18. InnoDB: Number of pools: 1
    19. 231007 21:27:00 completed OK!
    (2)重演周二
    1. [root@localhost ~]# innobackupex --apply-log --redo-only /opt/full/2023-10-07_21-05-07/ --incremental-dir=/opt/notfull/2023-10-07_21-16-51/
    2. xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1 
    3. xtrabackup: recognized client arguments: 
    4. 231007 21:27:49 innobackupex: Starting the apply-log operation
    5.  
    6. IMPORTANT: Please check that the apply-log run completes successfully.
    7.            At the end of a successful apply-log run innobackupex
    8.            prints "completed OK!".
    9.  
    10. innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
    11. incremental backup from 3080362 is enabled.
    12. xtrabackup: cd to /opt/full/2023-10-07_21-05-07/
    13. xtrabackup: This target seems to be already prepared with --apply-log-only.
    14. InnoDB: Number of pools: 1
    15. xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(3080717)
    16. xtrabackup: using the following InnoDB configuration for recovery:
    17. xtrabackup:   innodb_data_home_dir = .
    18. .
    19. .
    20. 231007 21:27:51 [00]        ...done
    21. 231007 21:27:51 completed OK!
    (3)回滚周三

    1. [root@localhost ~]# innobackupex --apply-log --redo-only /opt/full/2023-10-07_21-05-07/ --incremental-dir=/opt/notfull/2023-10-07_21-22-21/
    2. xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1 
    3. xtrabackup: recognized client arguments: 
    4. 231007 21:28:19 innobackupex: Starting the apply-log operation
    5.  
    6. IMPORTANT: Please check that the apply-log run completes successfully.
    7.            At the end of a successful apply-log run innobackupex
    8.            prints "completed OK!".
    9.  
    10. innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
    11. incremental backup from 3080717 is enabled.
    12. xtrabackup: cd to /opt/full/2023-10-07_21-05-07/
    13. xtrabackup: This target seems to be already prepared with --apply-log-only.
    14. InnoDB: Number of pools: 1
    15. xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(3081072)
    16. xtrabackup: using the following InnoDB configuration for recovery:
    17. xtrabackup:   innodb_data_home_dir = .
    18. xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
    19. xtrabackup:   innodb_log_group_home_dir = /opt/notfull/2023-10-07_21-22-21/
    20. xtrabackup:   innodb_log_files_in_group = 1
    21. xtrabackup:   innodb_log_file_size = 8388608
    22. xtrabackup: Generating a list of tablespaces
    23. .
    24. .
    25. 231007 21:28:20 [00]        ...done
    26. 231007 21:28:20 completed OK!
    (4)重演，恢复数据
    1. [root@localhost ~]# innobackupex --copy-back /opt/full/2023-10-07_21-05-07/
    2. xtrabackup: recognized server arguments: --server-id=1 --log_bin=/opt/log/mysql-bin.log --datadir=/var/lib/mysql 
    3. xtrabackup: recognized client arguments: 
    4. 231007 21:28:57 innobackupex: Starting the copy-back operation
    5.  
    6. IMPORTANT: Please check that the copy-back run completes successfully.
    7.            At the end of a successful copy-back run innobackupex
    8.            prints "completed OK!".
    9.  
    10. innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
    11. 231007 21:28:57 [01] Copying ibdata1 to /var/lib/mysql/ibdata1
    12. 231007 21:28:57 [01]        ...done
    13.  
    14. .
    15. .
    16. 231007 21:28:57 [01]        ...done
    17. 231007 21:28:57 completed OK!

    4.修改权限

    [root@localhost ~]# chown -R mysql.mysql /var/lib/mysql/*

    5.重启mysqld

    [root@localhost ~]# systemctl start mysqld

    五、差异备份和数据恢复

      环境准备

    1. mysql> create table db3(id int,name varchar(30),time varchar(30));
    2. Query OK, 0 rows affected (0.01 sec)
    3.  
    4. mysql> insert into db3 values(1,'xiaoliu','星期一');
    5. Query OK, 1 row affected (0.00 sec)
    6.  
    7. [root@localhost ~]# rm -rf /opt/full

    1.完整备份（周一）

    语法：innobackupex --user=用户 --password=密码  完整备份的路径

    1. [root@localhost ~]# innobackupex --user=root --password=123 /opt/full
    2. xtrabackup: recognized server arguments: --server-id=1 --log_bin=/opt/log/mysql-bin.log --datadir=/var/lib/mysql 
    3. xtrabackup: recognized client arguments: 
    4. 231007 23:06:28 innobackupex: Starting the backup operation
    5.  
    6. IMPORTANT: Please check that the backup run completes successfully.
    7.            At the end of a successful backup run innobackupex
    8.            prints "completed OK!".
    9.  
    10. 231007 23:06:28  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
    11. 231007 23:06:28  version_check Connected to MySQL server
    12. 231007 23:06:28  version_check Executing a version check against the server...
    13. 231007 23:06:28  version_check Done.
    14. 231007 23:06:28 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
    15. Using server version 5.7.43-log
    16. innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
    17. xtrabackup: uses posix_fadvise().
    18. .
    19. .
    20. xtrabackup: Transaction log of lsn (3085687) to (3085696) was copied.
    21. 231007 23:06:30 completed OK!

    2.差异备份（周二）

    mysql> insert into db.db3 values (2,'xiaozhao','星期二');

    语法：innobackupex --user=root --password='密码' --incremental  备份路径 --incremental-basedir=完整备份的路径（第一天）

    1. [root@localhost ~]# innobackupex --user=root --password=123 --incremental /opt/diff --incremental-basedir=/opt/full/2023-10-07_23-06-28/
    2. .
    3. .
    4. xtrabackup: Transaction log of lsn (3086067) to (3086076) was copied.
    5. 231007 23:12:31 completed OK!

    3.差异备份（周三）

    1. mysql> insert into db.db3 values(3,'xiaozhang','星期三');
    2. Query OK, 1 row affected (0.00 sec)

    语法：innobackupex --user=用户 --password='密码' --incremental 备份路径 --incremental-basedir=完整备份的路径（周一）

    1. [root@localhost ~]# innobackupex --user=root --password='123' --incremental /opt/diff --incremental-basedir=/opt/full/2023-10-07_23-06-28/
    2. .
    3. .
    4. xtrabackup: Transaction log of lsn (3086448) to (3086457) was copied.
    5. 231007 23:17:24 completed OK!

     4.数据恢复

    1. mysql> drop table db3;
    2. Query OK, 0 rows affected (0.01 sec)

    （1）停止数据库

    [root@localhost ~]# systemctl stop mysqld

    （2）清理环境

    [root@localhost ~]# rm -rf /var/lib/mysql/*

    （3）重演

    重演周一
    [root@localhost ~]# innobackupex --apply-log --redo-only /opt/full/2023-10-07_23-06-28/
    重演周三
    1. [root@localhost ~]# innobackupex --apply-log --redo-only /opt/full/2023-10-07_23-06-28/ --incremental-dir /opt/diff/2023-10-07_23-12-29/
    2. .
    3. .
    4. 231007 23:40:37 [00]        ...done
    5. 231007 23:40:37 completed OK!
    回滚
    1. [root@localhost ~]# innobackupex --copy-back /opt/full/2023-10-07_23-06-28/
    2. .
    3.  
    4. .
    5. 231007 23:41:07 [01]        ...done
    6. 231007 23:41:07 completed OK!

    (4)修改权限

    [root@localhost ~]# chown -R mysql.mysql /var/lib/mysql/*

    (5)重启服务

    [root@localhost ~]# systemctl start mysqld

  • 相关阅读:
    CTF-include
    机器学习8-人工神经网络
    聊聊 QianKun JS 沙箱的那些事
    Linux ubuntu磁盘扩容
    代码随想录第42天 | ● 309.最佳买卖股票时机含冷冻期 ● 714.买卖股票的最佳时机含手续费
    基于SSM的高校教师科研信息展示网站的设计与实现
    day16-测试自动化之selenium的PO模式
    CCF CSP认证历年题目自练Day46
    （Typora图床）阿里云oss搭建图床+Picgo上传图片详细教程
    Hutool 工具类之日期时间工具-DateUtil mysql日期字段
  • 原文地址：https://blog.csdn.net/2301_78315274/article/details/133610969