Mysql之备份工具
一、备份工具
1. mysqldump
mysql服务自带的备份工具；mysqldump备份方式是采用的逻辑备份，其最大的缺陷是备份和恢复速度较慢，如果数据库大于50G，mysqldump备份就不太适合；
InnoDB：热备；启动大事务。
MyISAM：温备；
备份单个数据库hellodb至指定文件
# mysqldump -uroot -h127.0.0.1 -p --databases hellodb >/root/hellodb.sql
Enter password:
备份多个数据库
# mysqldump -uroot -h127.0.0.1 -p --databases hellodb mydb >/root/hellodb-mydb.sql
Enter password:
备份所有数据库
# mysqldump -uroot -h127.0.0.1 -p --all-databases >/root/alldb.sql
Enter password:
2. Xtrabackup
Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。
MyISAM：温备，不支持增量备份；InnoDB：热备，增量；
特点：
(1)备份过程快速、可靠；
(2)备份过程不会打断正在执行的事务；
(3)能够基于压缩等功能节约磁盘空间和流量；
(4)自动实现备份检验；
(5)还原速度快；
Percona XtraBackup 8.0 支持MySQL8.0的版本；
mysql8.0之前的版本需要使用xtrabackup2.4备份和恢复.
- 安装
# yum install percona-xtrabackup-24-2.4.26-1.el7.x86_64.rpm
# rpm -ql percona-xtrabackup-24
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/lib64/xtrabackup/plugin/keyring_file.so
/usr/lib64/xtrabackup/plugin/keyring_vault.so
/usr/share/doc/percona-xtrabackup-24-2.4.26
/usr/share/doc/percona-xtrabackup-24-2.4.26/LICENSE
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz
二、备份和恢复
1. 完全备份（mysqldump）+二进制日志文件进行增量恢复
- 先做一次完全备份
# mysqldump -uroot -h127.0.0.1 -p --all-databases >/root/alldb.sql
Enter password:
- 在数据库进行一些修改操作
mysql> DESC students;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> INSERT INTO students (Name,Age,Gender,ClassID,TeacherID) VALUES ('Chao Gai',47,'m',3,7);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 21 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 27 | Jinjiao King | 100 | M | NULL | NULL |
| 28 | Chao Gai | 47 | M | 3 | 7 |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)
mysql> DELETE FROM students WHERE StuID=3;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 21 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 27 | Jinjiao King | 100 | M | NULL | NULL |
| 28 | Chao Gai | 47 | M | 3 | 7 |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)
恢复数据库
- 先用全备的备份文件导入数据库
# mysql -uroot -h127.0.0.1 -p < alldb.sql
Enter password:
- 查看全量备份时二进制文件的位置792019
# less alldb.sql
...
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=792019;
...
MASTER_LOG_POS=792019：记录当前二进制文件的位置，也就是从这个事件开始的事件都没有被备份
- 通过mysqlbin把全量备份之后的操作导出到文件
# mysqlbinlog --start-position=792019 /opt/mysql/data/mysql-bin.000001 > binlogbak.sql
# ls
binlogbak.sql
- 通过binlog文件进行增量恢复
# mysql -uroot -h127.0.0.1 -p < binlogbak.sql
Enter password:
- --set-gtid-purged参数
1.Mysqldump中sql_log_bin默认是关闭的。
如果数据要导入主库，可以通过--set-gtid-purged=off备份参数，不会在备份文件中记录SET @@GLOBAL.GTID_PURGED的值。不需要reset master可直接导入。
2.全备的情况下不添加，--set-gtid-purged 默认为ON(常用于重做主从)，部分备份时添加 --set-gtid-purged=off(可在主上做部分恢复，在从上不推荐使用，即便是通过SET @@SESSION.SQL_LOG_BIN= 0;source alldb.sql;的方式导入，之后的数据更新可能会导致复制出现数据已存在的异常。也不适应与备份文件较大的情况。)
`正确的操作是导入从库之后，主从数据可以保持一致，然后reset master;set @@global.gtid_purged='gtid段'; change master to重建复制。`
3.备份文件默认，库是不存在就创建，表是存在就删除重建。
4.对于需要导入主库的场景，建议开启set-gtid-purged=off参数，导入数据时，记录binlog(更新事务号和Position)，不影响复制。
5.对于需要导入从库的场景，建议保持默认或是不设置此参数，导入数据时，不记录binlog。
2. 完全备份（innobackupex）
- 确保表的存储引擎均为innodb
mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
Name: classes
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 8
Avg_row_length: 2048
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 9
Create_time: 2024-03-15 14:40:44
Update_time: 2024-03-15 14:40:44
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
- 确保每个表使用独立的表空间文件
mysql> SHOW GLOBAL VARIABLES LIKE '%innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
- 备份全部数据库（完全备份）
# mkdir /backups/
# innobackupex --user=root --password=PASSWORD --host=127.0.0.1 /backups/
xtrabackup: recognized server arguments: --datadir=/opt/dataengine/mysql/data --server-id=1 --log_bin=mysql-bin
xtrabackup: recognized client arguments:
240316 17:39:13 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!".
240316 17:39:13 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=127.0.0.1;mysql_socket=/opt/dataengine/temp/mysql_client.sock' as 'root' (using password: YES).
240316 17:39:13 version_check Connected to MySQL server
240316 17:39:13 version_check Executing a version check against the server...
240316 17:39:13 version_check Done.
240316 17:39:13 Connecting to MySQL server host: 127.0.0.1, user: root, password: set, port: not set, socket: /opt/dataengine/temp/mysql_client.sock
Using server version 5.7.26-log
Warning: option 'datadir' has different values:
'/opt/dataengine/mysql/data' in defaults file
'/opt/mysql/data/' in SHOW VARIABLES
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 /opt/dataengine/mysql/data
xtrabackup: open files limit requested 0, set to 1024
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 = ./
...
# ls /backups/
2024-03-16_17-37-28 2024-03-16_17-38-11 2024-03-16_17-39-13
- 数据恢复之前需要进行数据整理
# innobackupex --user=root --password=PASSWORD --host=127.0.0.1 --apply-log /backups/2024-03-16_17-39-13/
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
xtrabackup: recognized client arguments:
240316 17:48:11 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
innobackupex version 2.4.26 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 19de43b)
xtrabackup: cd to /backups/2024-03-16_17-39-13/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(752615560)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
...
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 752617000
240316 17:48:16 completed OK!
- 还原操作
被还原的数据库服务关闭
# innobackupex --user=root --password=Capinfo@123! --host=127.0.0.1 --copy-back /backups/2024-03-16_17-39-13/
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/backup/2024-03-16_08-17-53'
innobackupex: back to original InnoDB log directory '/mydata/data'
innobackupex: Finished copying back files.
3. 全部备份（innobackupex）+增量备份+恢复
- 创建表插入数据
mysql> CREATE TABLE testdb;
ERROR 1113 (42000): A table must have at least 1 column
mysql> CREATE TABLE testdb(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO testdb VALUES (1),(10),(99);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM testdb;
+------+
| id |
+------+
| 1 |
| 10 |
| 99 |
+------+
3 rows in set (0.00 sec)
- 全备
# innobackupex --user=root --password=PASSWORD! --host=127.0.0.1 /backups/
240316 18:04:53 Executing UNLOCK TABLES
240316 18:04:53 All tables unlocked
240316 18:04:53 [00] Copying ib_buffer_pool to /backups/2024-03-16_18-04-49/ib_buffer_pool
240316 18:04:53 [00] ...done
240316 18:04:53 Backup created in directory '/backups/2024-03-16_18-04-49/'
MySQL binlog position: filename 'mysql-bin.000001', position '792754'
240316 18:04:53 [00] Writing /backups/2024-03-16_18-04-49/backup-my.cnf
240316 18:04:53 [00] ...done
240316 18:04:53 [00] Writing /backups/2024-03-16_18-04-49/xtrabackup_info
240316 18:04:53 [00] ...done
xtrabackup: Transaction log of lsn (752634077) to (752634086) was copied.
240316 18:04:53 completed OK!
# ls /backups/
2024-03-16_18-04-49
# less /backups/2024-03-16_18-04-49/xtrabackup_checkpoints
- 插入数据
mysql> INSERT INTO testdb VALUES(44),(66);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM testdb;
+------+
| id |
+------+
| 1 |
| 10 |
| 99 |
| 44 |
| 66 |
+------+
5 rows in set (0.00 sec)
- 在全备的基础上做增量备份
# innobackupex --user=root --password=PASSWORD --host=127.0.0.1 --incremental /backups/ --incremental-basedir=/backups/2024-03-16_18-04-49
xtrabackup: recognized server arguments: --datadir=/opt/dataengine/mysql/data --server-id=1 --log_bin=mysql-bin
xtrabackup: recognized client arguments:
240316 18:14:25 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!".
240316 18:14:25 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=127.0.0.1;mysql_socket=/opt/dataengine/temp/mysql_client.sock' as 'root' (using password: YES).
240316 18:14:25 version_check Connected to MySQL server
240316 18:14:25 version_check Executing a version check against the server...
240316 18:14:25 version_check Done.
240316 18:14:25 Connecting to MySQL server host: 127.0.0.1, user: root, password: set, port: not set, socket: /opt/dataengine/temp/mysql_client.sock
Using server version 5.7.26-log
Warning: option 'datadir' has different values:
'/opt/dataengine/mysql/data' in defaults file
'/opt/mysql/data/' in SHOW VARIABLES
innobackupex version 2.4.26 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 19de43b)
incremental backup from 752634077 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /opt/dataengine/mysql/data
xtrabackup: open files limit requested 0, set to 1024
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
...
...
240316 18:14:29 Executing UNLOCK TABLES
240316 18:14:29 All tables unlocked
240316 18:14:29 [00] Copying ib_buffer_pool to /backups/2024-03-16_18-14-25/ib_buffer_pool
240316 18:14:29 [00] ...done
240316 18:14:29 Backup created in directory '/backups/2024-03-16_18-14-25/'
MySQL binlog position: filename 'mysql-bin.000001', position '793022'
240316 18:14:29 [00] Writing /backups/2024-03-16_18-14-25/backup-my.cnf
240316 18:14:29 [00] ...done
240316 18:14:29 [00] Writing /backups/2024-03-16_18-14-25/xtrabackup_info
240316 18:14:29 [00] ...done
xtrabackup: Transaction log of lsn (752640085) to (752640094) was copied.
240316 18:14:29 completed OK!
# less /backups/2024-03-16_18-14-25/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 752634077
to_lsn = 752640085
last_lsn = 752640094
compact = 0
recover_binlog_info = 0
flushed_lsn = 4320242
- 整理完全备份
# innobackupex --user=root --password=PASSWORD --host=127.0.0.1 --apply-log --redo-only /backups/2024-03-16_18-04-49/
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 752634095
InnoDB: Number of pools: 1
240316 18:21:51 completed OK!
- 先将增量备份整理合并到完全备份，然后做还原
# innobackupex --user=root --password=Capinfo@123! --host=127.0.0.1 --apply-log --redo-only /backups/2024-03-16_18-04-49/ --incremental-dir /backups/2024-03-16_18-14-25/
240316 18:23:53 [01] ...done
240316 18:23:53 [01] Copying /backups/2024-03-16_18-14-25/mysql/event.MYD to ./mysql/event.MYD
240316 18:23:53 [01] ...done
240316 18:23:53 [01] Copying /backups/2024-03-16_18-14-25/mysql/ndb_binlog_index.MYI to ./mysql/ndb_binlog_index.MYI
240316 18:23:53 [01] ...done
240316 18:23:53 [01] Copying /backups/2024-03-16_18-14-25/hive_h3ctest2/db.opt to ./hive_h3ctest2/db.opt
240316 18:23:53 [01] ...done
240316 18:23:53 [00] Copying /backups/2024-03-16_18-14-25//xtrabackup_binlog_info to ./xtrabackup_binlog_info
240316 18:23:53 [00] ...done
240316 18:23:53 [00] Copying /backups/2024-03-16_18-14-25//xtrabackup_info to ./xtrabackup_info
240316 18:23:53 [00] ...done
240316 18:23:53 completed OK!
- 还原
# innobackupex --user=root --password=Capinfo@123! --host=127.0.0.1 --copy-back /backups/2024-03-16_18-04-49/
240316 18:33:11 [00] ...done
240316 18:33:11 completed OK!
mysql> SELECT * FROM testdb;
+------+
| id |
+------+
| 1 |
| 10 |
| 99 |
| 44 |
| 66 |
+------+
5 rows in set (0.00 sec)
三、参考
https://www.cnblogs.com/xq-0226/p/11710139.html
https://zhuanlan.zhihu.com/p/448994764
