MySQL备份与恢复

目录

一、备份分类

二、逻辑备份与恢复

三、热备

四、在线备份 - xtraBackup示例

1. 安装yum源

2. 安装xtraBackup

3. 检查是否安装成功

4. 热备 - 全备份

五、参考资料

---

一、备份分类

        如下表所示，备份的分类总结。重点关注热备、裸文件备份、日志备份。

总分类	分类内容	特点
备份方式	热备 （Hot Backup）	1.数据库运行中直接备份，也称“在线备份”（online）； 2.备份时，对操作没有任何影响。
	冷备 （Cold Backup）	数据库停止时备份，也称“离线备份”
	温备 （Warm Backup）	1.数据库运行中直接备份； 2.备份时，对操作有影响，加全局读锁保证数据一致性。
文件内容	逻辑备份	1.可读的文本内容，备份SQL语句或实际数据； 2.方法：mysqldump、select...into outfile； 3.优点：观察导出文件；   缺点：恢复时间长。
	裸文件备份	1.复制物理文件，数据库运行或停止； 2.方法：ibbackup、xtrabackup； 3.优点：恢复时间较短。
备份内容	完全备份	当前内容全部备份
	增量备份	上次完整备份 + 更新的数据备份
	日志备份	利用二进制日志备份

二、逻辑备份与恢复

        如下表所示，显示逻辑备份的方式及相应恢复。mysqlimport实际上并发执行多条load data infile指令语句，来达到并发导入多张表。

备份方式	备份	恢复
mysqldump	1.适用：数据库升级或迁移； 2.导出指定库指定表； 3.文件以.sql结尾的文件，可读文件； 4.不能导出视图，需要备份视图的定义。	source命令
select...into outfile	1.导出指定表的某些字段符合条件的数据； 2.文件以.txt结尾的文件。	1.load data infile：导入单张表； 2.mysqlimport：并发导入多张表。

三、热备

        热备的工作原理如下：

• step1：备份开始时，记录重做日志文件检查点的LSN（备份前LSN）；
• step2：复制共享、独立表空间文件；
• step3：复制完成后，记录记录重做日志文件检查点的LSN（备份后LSN）；
• step4：复制在备份时产生的重做日志。

        热备的工具如下：

• ibbackup：同时备份InnoDB与MyISAM；收费软件
• xtraBackup：支持InnoDB备份；开源；支持增量备份（详细使用见下章节）

四、在线备份 - xtraBackup示例

1. 安装yum源

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

2. 安装xtraBackup

yum install -y percona-xtrabackup-24.x86_64

3. 检查是否安装成功

rpm -qa | grep xtrabackup

4. 热备 - 全备份

innobackupex --defaults-file=/home/MySQL5.7/mysql-5.7.35/data/my.cnf --user=root --password=123456 --socket=/tmp/mysql.sock /tmp/

        备份打印内容如下： 

[root@4b5e33d68f58 bin]# innobackupex --defaults-file=/home/MySQL5.7/mysql-5.7.35/data/my.cnf --user=root --password=123456 --socket=/tmp/mysql.sock /tmp/
xtrabackup: recognized server arguments: --datadir=/home/MySQL5.7/mysql-5.7.35/data --innodb_file_per_table=1 --server-id=100 --log_bin=mysql-bin 
xtrabackup: recognized client arguments: 
221019 11:56:22 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

221019 11:56:23  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'root'  (using password: YES).
221019 11:56:23  version_check Connected to MySQL server
221019 11:56:23  version_check Executing a version check against the server...
221019 11:56:23  version_check Done.
221019 11:56:23 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: /tmp/mysql.sock
Using server version 5.7.35-log
innobackupex version 2.4.26 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 19de43b)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/MySQL5.7/mysql-5.7.35/data
xtrabackup: open files limit requested 0, set to 1048576
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
221019 11:56:23 >> log scanned up to (2830389448)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 324 for test_mysql/FTS_0000000000000123_00000000000001e4_INDEX_6, old maximum was 0
221019 11:56:23 [01] Copying ./ibdata1 to /tmp/2022-10-19_11-56-22/ibdata1
221019 11:56:23 [01]        ...done
221019 11:56:23 [01] Copying ./test_mysql/FTS_0000000000000123_00000000000001e4_INDEX_6.ibd to /tmp/2022-10-19_11-56-22/test_mysql/FTS_0000000000000123_00000000000001e4_INDEX_6.ibd
221019 11:56:23 [01]        ...done
221019 11:56:23 [01] Copying ./test_mysql/FTS_0000000000000123_BEING_DELETED.ibd to /tmp/2022-10-19_11-56-22/test_mysql/FTS_0000000000000123_BEING_DELETED.ibd
221019 11:56:23 [01]        ...done
221019 11:56:23 [01] Copying ./test_mysql/test_partition_playbill#P#p2021.ibd to /tmp/2022-10-19_11-56-22/test_mysql/test_partition_playbill#P#p2021.ibd
......
221019 11:56:23 [01]        ...done
221019 11:56:23 [01] Copying ./test_mysql/t_parent.ibd to /tmp/2022-10-19_11-56-22/test_mysql/t_parent.ibd
221019 11:56:23 [01]        ...done
221019 11:56:23 [01] Copying ./test_mysql/test_partition_playbill#P#p2020.ibd to /tmp/2022-10-19_11-56-22/test_mysql/test_partition_playbill#P#p2020.ibd
221019 11:56:24 >> log scanned up to (2830389448)
221019 11:56:24 [01]        ...done
221019 11:56:24 [01] Copying ./test_mysql/test_range_partition#P#p0.ibd to /tmp/2022-10-19_11-56-22/test_mysql/test_range_partition#P#p0.ibd
221019 11:56:24 [01]        ...done
221019 11:56:24 [01] Copying ./test_mysql/test_hash_partition#P#p0.ibd to /tmp/2022-10-19_11-56-22/test_mysql/test_hash_partition#P#p0.ibd
221019 11:56:24 [01]        ...done
......
221019 11:56:24 [01] Copying ./test_mysql/test_range_partition#P#p2.ibd to /tmp/2022-10-19_11-56-22/test_mysql/test_range_partition#P#p2.ibd
221019 11:56:24 [01]        ...done
221019 11:56:24 [01] Copying ./test_mysql/test_partition_playbill#P#p2019.ibd to /tmp/2022-10-19_11-56-22/test_mysql/test_partition_playbill#P#p2019.ibd
221019 11:56:24 [01]        ...done
221019 11:56:25 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
221019 11:56:25 Executing FLUSH TABLES WITH READ LOCK...
221019 11:56:25 Starting to backup non-InnoDB tables and files
221019 11:56:25 [01] Copying ./performance_schema/status_by_host.frm to /tmp/2022-10-19_11-56-22/performance_schema/status_by_host.frm
221019 11:56:25 [01]        ...done
221019 11:56:25 [01] Copying ./performance_schema/events_statements_history.frm to /tmp/2022-10-19_11-56-22/performance_schema/events_statements_history.frm
221019 11:56:25 [01]        ...done
......
221019 11:56:25 [01] Copying ./performance_schema/events_transactions_history_long.frm to /tmp/2022-10-19_11-56-22/performance_schema/events_transactions_history_long.frm
221019 11:56:25 [01]        ...done
221019 11:56:25 Finished backing up non-InnoDB tables and files
221019 11:56:25 [00] Writing /tmp/2022-10-19_11-56-22/xtrabackup_binlog_info
221019 11:56:25 [00]        ...done
221019 11:56:25 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2830389439'
xtrabackup: Stopping log copying thread.
.221019 11:56:25 >> log scanned up to (2830389448)

221019 11:56:25 Executing UNLOCK TABLES
221019 11:56:25 All tables unlocked
221019 11:56:25 [00] Copying ib_buffer_pool to /tmp/2022-10-19_11-56-22/ib_buffer_pool
221019 11:56:25 [00]        ...done
221019 11:56:25 Backup created in directory '/tmp/2022-10-19_11-56-22/'
MySQL binlog position: filename 'mysql-bin.000052', position '154'
221019 11:56:25 [00] Writing /tmp/2022-10-19_11-56-22/backup-my.cnf
221019 11:56:25 [00]        ...done
221019 11:56:25 [00] Writing /tmp/2022-10-19_11-56-22/xtrabackup_info
221019 11:56:25 [00]        ...done
xtrabackup: Transaction log of lsn (2830389439) to (2830389448) was copied.
221019 11:56:26 completed OK!

五、参考资料

搭建基于Docker的Mysql主从复制_爱我所爱0505的博客-CSDN博客_基于docker的mysql主从复制搭建Centos7 Mysql5.7 Xtrabackup的安装与使用_妖四灵.Shuen的博客-CSDN博客 

About Percona XtraBackup - Percona XtraBackup

MYSQL主从同步错误（1）主库删除数据导致主从失败的二般解决办法_冬城霜的博客-CSDN博客