Mysql备份与恢复

Mysql备份与恢复

𝓓𝓸𝓷 Lv6
  2024-07-13 15:20:33 2024-07-13 15:20:33 Created   2025-01-24 10:36:22 2025-01-24 10:36:22 Updated    

Physical backups consist of raw copies of the directories and files that store database contents. This
type of backup is suitable for large, important databases that need to be recovered quickly when
problems occur.

Logical backups save information represented as logical database structure (CREATE DATABASE,
CREATE TABLE statements) and content (INSERT statements or delimited-text files). This type of
backup is suitable for smaller amounts of data where you might edit the data values or table structure,
or recreate the data on a different machine architecture.

一、Mysqldump备份与恢复

Mysql备份恢复前先开启二进制:
[mysqld]
log-bin=mysql-bin
server_id=1
binlog_format=row

1.备份
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
(1) 全备
[root@db01 ~]# mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction > /tmp/full.sql
[root@db01 ~]# mysqldump -uroot -p -A -R -E --triggers --flush-logs --master-data=2 --single-transaction > /tmp/full.sql
[root@db01 ~]# mysqldump --user=root -p -A -R -E --triggers --flush-logs --set-gtid-purged=off --master-data=2 --single-transaction > /tmp/full.sql

---mysqldump压缩
mysqldump --login-path=root -A -R -E --triggers --source-data=2 --single-transaction | gzip > /backup/full_mysql_$(date "+%Y%m%d_%H%M").sql.tar.gz

Mysqldump 8.0.26新版本引入新参数source-data,导出的时候会出现以下提醒:
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
解决方法:
[root@db01 ~]# mysqldump --user=root -p -A -R -E --triggers --flush-logs --set-gtid-purged=off --source-data=2 --single-transaction > /tmp/full.sql

使用mysql_config_editor绕过密码:
[root@db01 ~]# mysql_config_editor set --login-path=root --user=root --password --host=localhost

[root@db01 ~]# mysql_config_editor print --all
[client]
[root]
user = root
password = *****
host = localhost


[root@db01 ~]# mysqldump --login-path=root -A -R -E --triggers --master-data=2 --single-transaction > /tmp/full.sql

[root@db01 ~]# mysqldump --login-path=root -A -R -E --triggers --master-data=2 --single-transaction > /tmp/full_mysql_$(date "+%Y%m%d_%H%M").sql

(2) 单库备份
mysqldump --login-path=root -B -R -E --triggers --master-data=2 --single-transaction mydb > /tmp/mydb.sql

(3) 单表备份
mysqldump --login-path=root --tables -R -E --triggers --master-data=2 --single-transaction mydb t t1> /tmp/mydb.sql
或
mysqldump --login-path=root -R -E --triggers --master-data=2 --single-transaction mydb t t1> /tmp/mydb.sql

(4) 备份表结构 
mysqldump --login-path=root -R -E --triggers --no-data --master-data=2 --single-transaction mydb t t1> /tmp/mydb.sql

（5）导数据不导表结构 
mysqldump --login-path=root -R -E --triggers -t --master-data=2 --single-transaction mydb t t1> /tmp/mydb.sql

（6）切换日志
mysqldump --login-path=root -R -E --triggers --flush-logs --master-data=2 --single-transaction mydb t t1> /tmp/mydb.sql

2.备份参数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
参数说明:
  -A, --all-databases Dump all the databases. This will be same as --databases
                      with all databases selected
                      
  -B, --databases     Dump several databases. Note the difference in usage; in
                      this case no tables are given. All name arguments are
                      regarded as database names. 'USE db_name;' will be
                      included in the output.
                    
  -E, --events        Dump events.
  
 
  --log-error=name    Append warnings and errors to given file.
  
  
  -R, --routines      Dump stored routines (functions and procedures).
  
  
  --skip-opt          Disable --opt. Disables --add-drop-table, --add-locks,
                      --create-options, --quick, --extended-insert,
                      --lock-tables, --set-charset, and --disable-keys.

  --tables            Overrides option --databases (-B).
  
  --triggers          Dump triggers for each dumped table.
                      (Defaults to on; use --skip-triggers to disable.)
       
  -l, --lock-tables   Lock all tables for read.
                      (Defaults to on; use --skip-lock-tables to disable.)
                     
  --max-allowed-packet=# 
                      The maximum packet length to send to or receive from
                      server.
                      
  -i, --comments      Write additional information.
                      (Defaults to on; use --skip-comments to disable.)
                      
  --add-drop-database Add a DROP DATABASE before each create.
  
  --add-drop-table    Add a DROP TABLE before each create.
                      (Defaults to on; use --skip-add-drop-table to disable.)
  --add-drop-trigger  Add a DROP TRIGGER before each create.
  
  
  
  -t, --no-create-info 
                      Don't write table creation info.
                      
  -d, --no-data       No row information.
  
                      
  --master-data[=#]   This causes the binary log position and filename to be
                      appended to the output. If equal to 1, will print it as a
                      CHANGE MASTER command; if equal to 2, that command will
                      be prefixed with a comment symbol. This option will turn
                      --lock-all-tables on, unless --single-transaction is
                      specified too (in which case a global read lock is only
                      taken a short time at the beginning of the dump; don't
                      forget to read about --single-transaction below). In all
                      cases, any action on logs will happen at the exact moment
                      of the dump. Option automatically turns --lock-tables
                      off.
                      
  --single-transaction 
                      Creates a consistent snapshot by dumping all tables in a
                      single transaction. Works ONLY for tables stored in
                      storage engines which support multiversioning (currently
                      only InnoDB does); the dump is NOT guaranteed to be
                      consistent for other storage engines. While a
                      --single-transaction dump is in process, to ensure a
                      valid dump file (correct table contents and binary log
                      position), no other connection should use the following
                      statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
                      TRUNCATE TABLE, as consistent snapshot is not isolated
                      from them. Option automatically turns off --lock-tables.
                      
                     
  -F, --flush-logs    Flush logs file in server before starting dump. Note that
                      if you dump many databases at once (using the option
                      --databases= or --all-databases), the logs will be
                      flushed for each database dumped. The exception is when
                      using --lock-all-tables or --master-data: in this case
                      the logs will be flushed only once, corresponding to the
                      moment all tables are locked. So if you want your dump
                      and the log flush to happen at the same exact moment you
                      should use --lock-all-tables or --master-data with
                      --flush-logs.
3.备份脚本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
#!/bin/bash


export PATH=/usr/local/mysql/bin:$PATH

mount_point=/backup
backup_path=$mount_point/mysqldump
backup_keep_days=15
date_time=$(date "+%Y%m%d_%H%M")




if ! mount | grep -q " on ${mount_point} type "; then

    echo "`date +%Y%m%d` Please check if the directory $backup_path exists!" > /tmp/mysql_backup_${date_time}.log
    exit 1

fi


[ ! -d $backup_path ] && mkdir -p $backup_path

mysqldump --login-path=root -A -R -E --triggers --source-data=2 --single-transaction | gzip > $backup_path/full_mysql_${date_time}.sql.tar.gz


reserved_num=$backup_keep_days
file_num=$(find $backup_path/* -type f|wc -l)
	
delete(){

	for i in `find $backup_path/* -type f`;do

		if [ $file_num -gt $reserved_num ]; then
			rm -rf $i
			echo "$date_time $i has been deleted."
		fi
		let "file_num--"
		
	done

}

delete >> $backup_path/delete_backup.log

4.恢复
  • 全库恢复
1
2
3
4
5
6
7
8
9
10
11
全库备份:
[root@db01 ~]# mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction > /tmp/full.sql

全库恢复:
[root@db01 ~]# mysql -uroot -p < /tmp/full.sql 
或
mysql> set sql_log_bin=0;

mysql> source /tmp/full.sql

mysql> set sql_log_bin=1;

全库恢复会将所有的库做一次恢复，如果只想恢复某一个库，这样就影响恢复速度及影响其它库的数据，添加–one-database恢复单库

  • 单库恢复
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
方法一:使用--one-database恢复单库

[root@db01 ~]# mysql --login-path=root --one-database mydb < /tmp/full.sql
或
[root@db01 ~]# mysql --login-path=root -D mydb -o < /tmp/full.sql


如果mydb已经被drop,恢复会提示数据库不存在:
[root@db01 ~]# mysql --login-path=root --one-database mydb < /tmp/full.sql
ERROR 1049 (42000): Unknown database 'mydb'

解决办法:
从备份中找出创建mydb数据库语句，然后手工创建，然后重新导入:
[root@db01 ~]# grep -i "^create database" /tmp/full.sql
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

[root@db01 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 66
Server version: 5.7.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
Query OK, 1 row affected (0.00 sec)


[root@db01 ~]# mysql --login-path=root --one-database mydb < /tmp/full.sql

mysql> use mydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| t              |
| t1             |
| test           |
+----------------+
3 rows in set (0.00 sec)


方法二:从备份文件中提取单库数据恢复

(1) 生成mydb脚本
[root@db01 ~]# sed -n '/^-- Current Database: `mydb`/,/^-- Current Database: `/p' /tmp/full.sql > /tmp/mydb.sql


(2) 添加时区
[root@db01 ~]# vim /tmp/mydb.sql
-- Current Database: `mydb`
--

set session TIME_ZONE='+00:00';

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

USE `mydb`;

......


(3) 恢复mydb数据库
mysql> set sql_log_bin=0;

mysql> source /tmp/mydb.sql

mysql> set sql_log_bin=1;

  • 单表恢复
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
(1) 生成建表语句
[root@db01 ~]# cat /tmp/full.sql | sed -n -e '/^CREATE DATABASE.*`mydb`/,/^CREATE DATABASE/ p' | sed -e '$d' | sed -n '/-- Table structure for table `t`/,/UNLOCK TABLES;/p' > /tmp/create_table_t.sql 
或
[root@db01 ~]# cat /tmp/full.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `test_tb`/!d;q' > /tmp/create_table_t.sql 
[root@db01 ~]# cat /tmp/full.sql | grep --ignore-case  'insert into `test_tb`' > /tmp/table_t_data.sql

(2) 添加时区
-- Table structure for table `t`
--

set session TIME_ZONE='+00:00';

DROP TABLE IF EXISTS `t`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

......

(3) 恢复t表
mysql> use mydb
mysql> set sql_log_bin=0;
mysql> source /tmp/create_table_t.sql 
mysql> set sql_log_bin=1;

5.Mysqlbinlog Position恢复

恢复步骤: 先使用全库备份进行恢复，然后再使用二进制日志恢复全备之后产生的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
(1) 全备
[root@db01 ~]# mysqldump -uroot -p -A -R -E --triggers --flush-logs --master-data=2 --single-transaction > /tmp/full.sql

(2) 模拟数据丢失
mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
+------+
7 rows in set (0.00 sec)

mysql> insert into t values (8);
Query OK, 1 row affected (0.00 sec)

mysql> delete from t;
Query OK, 8 rows affected (0.00 sec)


mysql> flush logs;

mysql> insert into t values (9);
Query OK, 1 row affected (0.00 sec)


mysql> select * from t;
+------+
| id   |
+------+
|    9 |
+------+
1 row in set (0.00 sec)


mysql> drop table t;


(3) 将表恢复drop之前状态

查找drop之前的lsn开始及结束位置:

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000021 |      585 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show binlog events in '/data/mysql/data/mysql-bin.000021';
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                 |
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------+
| mysql-bin.000021 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4                |
| mysql-bin.000021 | 123 | Previous_gtids |         6 |         154 |                                                      |
| mysql-bin.000021 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                 |
| mysql-bin.000021 | 219 | Query          |         6 |         291 | BEGIN                                                |
| mysql-bin.000021 | 291 | Table_map      |         6 |         335 | table_id: 922 (mydb.t)                               |
| mysql-bin.000021 | 335 | Write_rows     |         6 |         375 | table_id: 922 flags: STMT_END_F                      |
| mysql-bin.000021 | 375 | Xid            |         6 |         406 | COMMIT /* xid=10383 */                               |
| mysql-bin.000021 | 406 | Anonymous_Gtid |         6 |         471 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                 |
| mysql-bin.000021 | 471 | Query          |         6 |         585 | use `mydb`; DROP TABLE `t` /* generated by server */ |
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------+
9 rows in set (0.00 sec)


mysql> show binlog events in '/data/mysql/data/mysql-bin.000020';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000020 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000020 | 123 | Previous_gtids |         6 |         154 |                                       |
| mysql-bin.000020 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000020 | 219 | Query          |         6 |         291 | BEGIN                                 |
| mysql-bin.000020 | 291 | Table_map      |         6 |         335 | table_id: 922 (mydb.t)                |
| mysql-bin.000020 | 335 | Write_rows     |         6 |         375 | table_id: 922 flags: STMT_END_F       |
| mysql-bin.000020 | 375 | Xid            |         6 |         406 | COMMIT /* xid=10380 */                |
| mysql-bin.000020 | 406 | Anonymous_Gtid |         6 |         471 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000020 | 471 | Query          |         6 |         543 | BEGIN                                 |
| mysql-bin.000020 | 543 | Table_map      |         6 |         587 | table_id: 922 (mydb.t)                |
| mysql-bin.000020 | 587 | Delete_rows    |         6 |         662 | table_id: 922 flags: STMT_END_F       |
| mysql-bin.000020 | 662 | Xid            |         6 |         693 | COMMIT /* xid=10381 */                |
| mysql-bin.000020 | 693 | Rotate         |         6 |         740 | mysql-bin.000021;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
13 rows in set (0.00 sec)


导出数据:
[root@db01 ~]# mysqlbinlog -uroot -p --start-position=219 --stop-position=693 /data/mysql/data/mysql-bin.000020 > /tmp/000020.sql
[root@db01 ~]# mysqlbinlog -uroot -p --start-position=219 --stop-position=471 /data/mysql/data/mysql-bin.000021 > /tmp/000021.sql


先从全备中恢复t表:
[root@db01 ~]# mysql -uroot -p --one-database mydb < /tmp/full.sql 
此步不理想，对mydb的其它表都做了恢复操作，最好使用full.sql脚本中提取t表数据，然后再导入

再从二进制日志中恢复全备后产生的数据:
mysql> use mydb
mysql> set sql_log_bin=0;
mysql> source /tmp/000020.sql
mysql> source /tmp/000021.sql
mysql> set sql_log_bin=1;


mysql> select * from t;
+------+
| id   |
+------+
|    9 |
+------+
1 row in set (0.00 sec)

6.Mysqlbinlog GTID恢复

GTID开启:
gtid_mode=on
enforce_gtid_consistency=1
log_slave_updates=1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
(1) 全备
[root@db01 ~]# mysqldump --user=root -p -A -R -E --triggers --flush-logs --master-data=2 --single-transaction > /tmp/full.sql

(2) 模拟数据丢失
mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
+------+
7 rows in set (0.00 sec)

mysql> insert into t values (8);
Query OK, 1 row affected (0.00 sec)

mysql> delete from t;
Query OK, 8 rows affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)


mysql> insert into t values (9);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    9 |
+------+
1 row in set (0.00 sec)

mysql> drop table t;
Query OK, 0 rows affected (0.01 sec)

(3) 将表恢复至drop前状态，同时恢复误delete数据

查看drop前gtid:

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000015 |      625 |              |                  | d5d29a37-ace6-11eb-9949-000c29bdd3e5:1-38 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)


mysql> show binlog events in '/data/mysql/data/mysql-bin.000015';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                               |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000015 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4                              |
| mysql-bin.000015 | 123 | Previous_gtids |         6 |         194 | d5d29a37-ace6-11eb-9949-000c29bdd3e5:1-36                          |
| mysql-bin.000015 | 194 | Gtid           |         6 |         259 | SET @@SESSION.GTID_NEXT= 'd5d29a37-ace6-11eb-9949-000c29bdd3e5:37' |
| mysql-bin.000015 | 259 | Query          |         6 |         331 | BEGIN                                                              |
| mysql-bin.000015 | 331 | Table_map      |         6 |         375 | table_id: 172 (mydb.t)                                             |
| mysql-bin.000015 | 375 | Write_rows     |         6 |         415 | table_id: 172 flags: STMT_END_F                                    |
| mysql-bin.000015 | 415 | Xid            |         6 |         446 | COMMIT /* xid=1321 */                                              |
| mysql-bin.000015 | 446 | Gtid           |         6 |         511 | SET @@SESSION.GTID_NEXT= 'd5d29a37-ace6-11eb-9949-000c29bdd3e5:38' |
| mysql-bin.000015 | 511 | Query          |         6 |         625 | use `mydb`; DROP TABLE `t` /* generated by server */               |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
9 rows in set (0.00 sec)



mysql> show binlog events in '/data/mysql/data/mysql-bin.000014';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                               |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000014 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4                              |
| mysql-bin.000014 | 123 | Previous_gtids |         6 |         194 | d5d29a37-ace6-11eb-9949-000c29bdd3e5:1-34                          |
| mysql-bin.000014 | 194 | Gtid           |         6 |         259 | SET @@SESSION.GTID_NEXT= 'd5d29a37-ace6-11eb-9949-000c29bdd3e5:35' |
| mysql-bin.000014 | 259 | Query          |         6 |         331 | BEGIN                                                              |
| mysql-bin.000014 | 331 | Table_map      |         6 |         375 | table_id: 172 (mydb.t)                                             |
| mysql-bin.000014 | 375 | Write_rows     |         6 |         415 | table_id: 172 flags: STMT_END_F                                    |
| mysql-bin.000014 | 415 | Xid            |         6 |         446 | COMMIT /* xid=1318 */                                              |
| mysql-bin.000014 | 446 | Gtid           |         6 |         511 | SET @@SESSION.GTID_NEXT= 'd5d29a37-ace6-11eb-9949-000c29bdd3e5:36' |
| mysql-bin.000014 | 511 | Query          |         6 |         583 | BEGIN                                                              |
| mysql-bin.000014 | 583 | Table_map      |         6 |         627 | table_id: 172 (mydb.t)                                             |
| mysql-bin.000014 | 627 | Delete_rows    |         6 |         702 | table_id: 172 flags: STMT_END_F                                    |
| mysql-bin.000014 | 702 | Xid            |         6 |         733 | COMMIT /* xid=1319 */                                              |
| mysql-bin.000014 | 733 | Rotate         |         6 |         780 | mysql-bin.000015;pos=4                                             |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
13 rows in set (0.00 sec)

从全备文件/tmp/full.sql中 SET @@GLOBAL.GTID_PURGED='d5d29a37-ace6-11eb-9949-000c29bdd3e5:1-34'，可知1-34事务已全备
从binlog分析可知，GTID:35和GTID:37需要恢复的数据，而GTID:36是delete数据，需要跳过此步:
SET @@SESSION.GTID_NEXT= 'd5d29a37-ace6-11eb-9949-000c29bdd3e5:35' 
SET @@SESSION.GTID_NEXT= 'd5d29a37-ace6-11eb-9949-000c29bdd3e5:37'
SET @@SESSION.GTID_NEXT= 'd5d29a37-ace6-11eb-9949-000c29bdd3e5:36'

导出数据:
[root@db01 ~]# mysqlbinlog --skip-gtids --include-gtids=d5d29a37-ace6-11eb-9949-000c29bdd3e5:35-37 --exclude-gtids=d5d29a37-ace6-11eb-9949-000c29bdd3e5:36 /data/mysql/data/mysql-bin.000014 /data/mysql/data/mysql-bin.000015 > /tmp/gtid.sql

先从全备中恢复t表:
[root@db01 ~]# mysql -uroot -p --one-database mydb < /tmp/full.sql 
Enter password: 
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

解决办法:
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

[root@db01 ~]# mysql -uroot -p --one-database mydb < /tmp/full.sql 



再从二进制日志中恢复全备后产生的数据:
mysql> use mydb
mysql> set sql_log_bin=0;
mysql> source /tmp/gtid.sql
mysql> set sql_log_bin=1;

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
+------+
9 rows in set (0.00 sec)

二、Xtrabackup备份与恢复

https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/

https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.23/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm

1.下载

image-20210712135025908

1
[root@server ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.23/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm
2.安装依赖包
Library name Description
libev A high-performance event-loop library used in asynchronous network applications.
libgcrypt A general-purpose cryptographic library providing encryption, decryption, and hashing algorithms.
openssl A robust library for implementing SSL/TLS encryption and cryptographic functions.
zlib A compression library that supports data compression and decompression using the DEFLATE algorithm.
libaio A library for asynchronous I/O operations, providing non-blocking I/O functionality.

If yum reports missing dependencies, it may offer to install them automatically. If not, you can install these dependencies manually using the following example.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

[root@server soft]# yum install libev libgcrypt openssl zlib libaio
Trying other mirror.
No package libev available.
Package libgcrypt-1.5.3-14.el7.x86_64 already installed and latest version
Package matching 1:openssl-1.0.2k-16.el7.x86_64 already installed. Checking for update.
Package zlib-1.2.7-18.el7.x86_64 already installed and latest version
Package libaio-0.3.109-13.el7.x86_64 already installed and latest version
Nothing to do

---手工下载并安装libev依赖包
wget http://mirror.centos.org/centos/7/extras/x86_64/Packages/libev-4.15-7.el7.x86_64.rpm
centos镜像源已失效，可通过阿里云镜像源下载:
wget https://mirrors.aliyun.com/centos/7.9.2009/extras/x86_64/Packages/libev-4.15-7.el7.x86_64.rpm

[root@server soft]# rpm -ivh libev-4.15-7.el7.x86_64.rpm
3.安装Xtrabackup
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[root@server soft]# yum install -y percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm

[root@server soft]# rpm -qa|grep percona-xtrabackup
percona-xtrabackup-24-2.4.23-1.el7.x86_64

[root@server soft]# rpm -ql percona-xtrabackup-24-2.4.23-1.el7.x86_64
/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.23
/usr/share/doc/percona-xtrabackup-24-2.4.23/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


温馨提示:
安装percona-xtrabackup-80-8.0.32-26版本，提示缺失zstd依赖包:
Error: Package: percona-xtrabackup-80-8.0.34-29.1.el7.x86_64 (/percona-xtrabackup-80-8.0.34-29.1.el7.x86_64)
           Requires: zstd
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

zstd包通常在EPEL（Extra Packages for Enterprise Linux）存储库中提供，安装EPEL存储库:
yum install epel-release
yum install zstd

zstd官网github:
它在CentOS等资源的默认yum源中并不存在，不能通过 yum install zstd 的方式一键安装
访问https://github.com/facebook/zstd，在右下角的Releases栏目中选择您想要的版本并下载
4.备份
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
(1) 全备
innobackupex -uroot -p123456 /tmp/xtrabackup/full
可以使用--no-timestamp参数不生成日期目录

使用mysql_config_editor绕过密码:
[root@db01 ~]# mysql_config_editor set --login-path=root --user=root --password --host=localhost --socket=/tmp/mysql.sock

[root@db01 ~]# mysql_config_editor print --all
[client]
[root]
user = root
password = *****
host = localhost
socket = /tmp/mysql.sock

[root@db01 ~]# innobackupex --login-path=root --no-timestamp /tmp/xtrabackup/full

查看备份:
[root@db01 ~]# ll /tmp/xtrabackup/full/2021-07-14_09-23-46
total 77876
-rw-r-----. 1 root root      487 Jul 14 09:23 backup-my.cnf
drwxr-x---. 2 root root       48 Jul 14 09:23 binlog
drwxr-x---. 2 root root       46 Jul 14 09:23 gtid
-rw-r-----. 1 root root      359 Jul 14 09:23 ib_buffer_pool
-rw-r-----. 1 root root 79691776 Jul 14 09:23 ibdata1
drwxr-x---. 2 root root      118 Jul 14 09:23 mydb
drwxr-x---. 2 root root     4096 Jul 14 09:23 mysql
drwxr-x---. 2 root root     8192 Jul 14 09:23 performance_schema
drwxr-x---. 2 root root     8192 Jul 14 09:23 sys
drwxr-x---. 2 root root       48 Jul 14 09:23 test
-rw-r-----. 1 root root       63 Jul 14 09:23 xtrabackup_binlog_info
-rw-r-----. 1 root root      141 Jul 14 09:23 xtrabackup_checkpoints
-rw-r-----. 1 root root      538 Jul 14 09:23 xtrabackup_info
-rw-r-----. 1 root root     2560 Jul 14 09:23 xtrabackup_logfile


备份报错处理:
[root@db01 ~]# innobackupex --user=root --password=123456 --no-timestamp /tmp/xtrabackup/full
xtrabackup: recognized server arguments: --datadir=/data/mysql/data --server-id=6 --log_bin=mysql-bin 
xtrabackup: recognized client arguments: 
210713 16:00:37 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!".

210713 16:00:37  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup','root',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at - line 1314.
210713 16:00:37 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Failed to connect to MySQL server: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

解决办法:
[root@db01 ~]# innobackupex --user=root --password=123456 --no-timestamp --host=127.0.0.1 /tmp/xtrabackup/full
[root@db01 ~]# innobackupex --user=root --password=123456 --no-timestamp -S /tmp/mysql.sock /tmp/xtrabackup/full

[root@db01 ~]# vi /etc/my.cnf
[client]
socket=/tmp/mysql.sock



(3) 增量备份
[root@db01 ~]# innobackupex --login-path=root --no-timestamp --incremental /tmp/xtrabackup/incr1 --incremental-basedir=/tmp/xtrabackup/full

5.恢复
1
2
3
4
5
innobackupex --apply-log --read-only /tmp/xtrabackup/full 

https://www.modb.pro/db/43686

https://www.percona.com/doc/percona-xtrabackup/2.4/howtos/recipes_ibkx_local.html
  • Title: Mysql备份与恢复
  • Author: 𝓓𝓸𝓷
  • Created at : 2024-07-13 15:20:33
  • Updated at : 2025-01-24 10:36:22
  • Link: https://www.zhangdong.me/mysql-backup-recovery.html
  • License: This work is licensed under CC BY-NC-SA 4.0.
推荐文章
mysql8.0 gtid主从安装 mysql8.0 gtid主从安装 Mysql8.0主从复制安装 Mysql8.0主从复制安装 Linux安装Mysql5.6 Linux安装Mysql5.6
推荐文章
mysql8.0 gtid主从安装 mysql8.0 gtid主从安装 Mysql8.0主从复制安装 Mysql8.0主从复制安装
评论
  1. 一、Mysqldump备份与恢复
    1. 1.备份
    2. 2.备份参数
    3. 3.备份脚本
    4. 4.恢复
    5. 5.Mysqlbinlog Position恢复
    6. 6.Mysqlbinlog GTID恢复
  2. 二、Xtrabackup备份与恢复
    1. 1.下载
    2. 2.安装依赖包
    3. 3.安装Xtrabackup
    4. 4.备份
    5. 5.恢复