热门标签
热门文章
- 1Android网络请求完成之后更新UI的几种方法_android 主线程怎么启动一个阻塞的网络请求,根据结果更新ui
- 2Swift 两种方式实现 async/await 并发模型中任务超时（timeout）的取消_swift withthrowingtaskgroup timeout
- 3github的token使用方法_github token
- 4使用JSP+Servlet+MySQL实现登录注册功能_jsp+servlet+mysql登录页面
- 5python中flag的用法_Python cv2.EVENT_FLAG_LBUTTON属性代码示例
- 6JVM插桩之三：javaagent介绍及javassist介绍_javaagent和javassist
- 7mybatis常用的3种批量更新方法_mybatis批量更新
- 8Verilog求相反数_verilog取相反数
- 9torchaudio 加载wav报错 Couldn‘t find appropriate backend to handle uri *** and format None_couldn't find appropriate backend to handle uri ./
- 10数据采集的三大要点、数据源、方法和网络数据采集
当前位置: article > 正文
mysql数据库（完全备份，增量备份，差异备份）_mysql 增量备份
作者：羊村懒王 | 2024-04-12 10:31:51
赞
踩
mysql 增量备份
mysql数据库（完全备份，增量备份，差异备份）
1. mysql数据库备份与恢复
1.1 数据库常用备份方案
数据库备份方案：
- 全量备份
- 增量备份
- 差异备份
| 备份方案 | 特点 |
|---|---|
| 全量备份 | 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长 |
| 增量备份 | 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象 是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行 |
| 差异备份 | 备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。 |
1.2 mysql备份工具mysqldump
//语法： mysqldump [OPTIONS] database [tables ...] mysqldump [OPTIONS] --all-databases [OPTIONS] mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] //常用的OPTIONS： -uUSERNAME //指定数据库用户名 -hHOST //指定服务器主机，请使用ip地址 -pPASSWORD //指定数据库用户的密码 -P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307 //备份整个数据库(全备) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | FTX | | ftx | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) mysql> use ftx; Database changed mysql> show tables; +---------------+ | Tables_in_ftx | +---------------+ | course | | student | +---------------+ 2 rows in set (0.00 sec) [root@ftx ~]# ls anaconda-ks.cfg 在做备份操作之前需要先将指定ip授权 mysql> grant all on *.* to 'root'@192.168.195.136 identified by '12345678'; Query OK, 0 rows affected, 1 warning (0.00 sec) 重新读取授权表 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) [root@ftx ~]# mysqldump -uroot -p12345678 -h192.168.195.136 --all-databases > all-2023-09-04-19-55.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. //如果在命令行中输入了密码，则会出现此行告警，告诉我们在命令行界面输入密码是不安全的 我们应该用这种方式安全输入密码 [root@ftx ~]# mysqldump -uroot -p -h192.168.195.136 --all-databases > all-2023-09-04-19-55.sql Enter password: [root@ftx ~]# ls all-2023-09-04-19-55.sql anaconda-ks.cfg //备份ftx库的student表和course表 [root@ftx ~]# mysqldump -uroot -p -h192.168.195.136 ftx student course > table-2023-09-04-20-45.sql Enter password: [root@ftx ~]# ls all-2023-09-04-19-55.sql table-2023-09-04-20-45.sql anaconda-ks.cfg //备份ftx库 [root@ftx ~]# mysqldump -uroot -p -h192.168.195.136 --databases ftx > ftx-2023-09-04-23-16.sql Enter password: [root@ftx ~]# ls all-2023-09-04-19-55.sql ftx-2023-09-04-23-16.sql table-2023-09-04-20-45.sql anaconda-ks.cfg //模拟误删ftx数据库 mysql> drop database ftx; Query OK, 2 rows affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | FTX | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
- 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
1.3 mysql数据恢复
//恢复ftx数据库 [root@ftx ~]# ls all-2023-09-04-19-55.sql anaconda-ks.cfg ftx-2023-09-04-23-16.sql table-2023-09-04-20-45.sql [root@ftx ~]# mysql -uroot -p -h192.168.195.136 < ftx-2023-09-04-23-16.sql Enter password: mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | FTX | | ftx | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) //恢复ftx数据库的student表和course表 mysql> use ftx; Database changed mysql> show tables; Empty set (0.00 sec) mysql> source table-2023-09-04-20-45.sql //此操作在库里面进行执行 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) ...... ...... mysql> show tables; +---------------+ | Tables_in_ftx | +---------------+ | course | | student | +---------------+ 2 rows in set (0.00 sec) //同时我们也可以将备份的数据库中的表恢复到其他的数据库当中 首先我们新创建一个数据库 mysql> create database yyr; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | FTX | | ftx | | mysql | | performance_schema | | sys | | yyr | +--------------------+ 7 rows in set (0.00 sec) [root@ftx ~]# mysql -uroot -p yyr < table-2023-09-04-20-45.sql //此操作在命令行执行 Enter password: mysql> use yyr Database changed mysql> show tables; +---------------+ | Tables_in_yyr | +---------------+ | course | | student | +---------------+ 2 rows in set (0.00 sec) //模拟删除整个数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | FTX | | ftx | | mysql | | performance_schema | | sys | | yyr | +--------------------+ 7 rows in set (0.00 sec) 在此之前我们新建了一个yyr的数据库，所以我们重新做一次完全备份 [root@ftx ~]# mysqldump -uroot -p -h192.168.195.136 --all-databases > all-databases.sql Enter password: [root@ftx ~]# ls all-2023-09-04-19-55.sql ftx-2023-09-04-23-16.sql all-databases.sql table-2023-09-04-20-45.sql anaconda-ks.cfg mysql> drop database FTX; Query OK, 1 row affected (0.00 sec) mysql> drop database ftx; Query OK, 2 rows affected (0.01 sec) mysql> drop database mysql; Query OK, 31 rows affected, 2 warnings (0.01 sec) mysql> drop database performance_schema; Query OK, 88 rows affected, 2 warnings (0.00 sec) mysql> drop database sys; Query OK, 101 rows affected, 2 warnings (0.03 sec) mysql> drop database yyr; Query OK, 2 rows affected, 2 warnings (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) //恢复整个数据库 [root@localhost ~]# ls all-201808131500.sql table-201808131500.sql anaconda-ks.cfg wq-201808131500.sql [root@ftx ~]# mysql -uroot -p -h192.168.195.136 < all-databases.sql Enter password: [root@ftx ~]# mysql -e 'show databases;' +--------------------+ | Database | +--------------------+ | information_schema | | FTX | | ftx | | mysql | | yyr | +--------------------+ 由于performance_schema和sys这两个库在备份的文件中没有，所以在恢复数据库的时候就不会恢复这两个库，想要恢复这两个库的话，可以初始化数据库，但我们那些有数据的数据库则又会删除，同时由于这两个库没有对我的数据造成任何影响，所以我们可以继续下面的操作
- 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
- 146
- 147
- 148
1.4 差异备份与恢复
1.4.1. mysql差异备份
开启MySQL服务器的二进制日志功能
[mysqld] basedir = /usr/local/mysql datadir = /opt/data socket = /tmp/mysql.sock port = 3306 pid-file = /opt/data/mysql.pid user = mysql skip-name-resolve sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION server-id = 10 //设置服务器标识符 log-bin = mysql_bin //开启二进制日志功能 [root@ftx ~]# service mysqld restart Shutting down MySQL.... SUCCESS! Starting MySQL. SUCCESS!
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
对数据库进行完全备份
[root@localhost ~]# mysql -uroot -pwangqing123! mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.22-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | wangqing | +--------------------+ 5 rows in set (0.00 sec) mysql> show tables from wangqing; +--------------------+ | Tables_in_wangqing | +--------------------+ | runtime | | student | +--------------------+ 2 rows in set (0.01 sec) mysql> select * from wangqing.runtime; +------+-------+------+ | id | name | age | +------+-------+------+ | 1 | tom | 10 | | 2 | jerry | 30 | +------+-------+------+ 2 rows in set (0.01 sec) mysql> select * from wangqing.student; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 1 | tom | 20 | | 2 | jerry | 23 | | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangshan | 26 | | 6 | zhangshan | 20 | | 7 | lisi | NULL | | 8 | chenshuo | 10 | | 9 | wangwu | 3 | | 10 | qiuyi | 15 | | 11 | qiuxiaotian | 20 | +----+-------------+------+ 11 rows in set (0.00 sec) //完全备份 [root@ftx ~]# mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202309051400.sql Enter password: [root@ftx ~]# ls all-2023-09-04-19-55.sql anaconda-ks.cfg ftx-2023-09-04-23-16.sql table-2023-09-04-20-45.sql all-202309051340.sql all-202309051400.sql all-databases.sql //增加新内容 mysql> use yyr 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_yyr | +---------------+ | course | | student | +---------------+ 2 rows in set (0.00 sec) mysql> select * from student; +----+--------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+--------+------+------+--------+-----------+ | 1 | Dany | 25 | m | 160 | 1 | | 2 | Green | 23 | m | 158 | 2 | | 3 | Henry | 23 | f | 185 | 1 | | 4 | Jane | 22 | m | 162 | 3 | | 5 | Jim | 24 | f | 175 | 2 | | 6 | John | 21 | f | 172 | 4 | | 7 | Lily | 22 | m | 165 | 4 | | 8 | Susan | 23 | m | 170 | 5 | | 9 | Thomas | 22 | f | 178 | 5 | | 10 | Tom | 23 | f | 165 | 5 | | 11 | LiMing | 22 | m | 180 | 7 | +----+--------+------+------+--------+-----------+ 11 rows in set (0.00 sec) mysql> insert student(name,age,sex,height,course_id) value('yyr',18,'f',160,8); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+--------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+--------+------+------+--------+-----------+ | 1 | Dany | 25 | m | 160 | 1 | | 2 | Green | 23 | m | 158 | 2 | | 3 | Henry | 23 | f | 185 | 1 | | 4 | Jane | 22 | m | 162 | 3 | | 5 | Jim | 24 | f | 175 | 2 | | 6 | John | 21 | f | 172 | 4 | | 7 | Lily | 22 | m | 165 | 4 | | 8 | Susan | 23 | m | 170 | 5 | | 9 | Thomas | 22 | f | 178 | 5 | | 10 | Tom | 23 | f | 165 | 5 | | 11 | LiMing | 22 | m | 180 | 7 | | 12 | yyr | 18 | f | 160 | 8 | +----+--------+------+------+--------+-----------+ 12 rows in set (0.00 sec) mysql> update student set course_id = 6 where name = 'LiMing'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from student; +----+--------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+--------+------+------+--------+-----------+ | 1 | Dany | 25 | m | 160 | 1 | | 2 | Green | 23 | m | 158 | 2 | | 3 | Henry | 23 | f | 185 | 1 | | 4 | Jane | 22 | m | 162 | 3 | | 5 | Jim | 24 | f | 175 | 2 | | 6 | John | 21 | f | 172 | 4 | | 7 | Lily | 22 | m | 165 | 4 | | 8 | Susan | 23 | m | 170 | 5 | | 9 | Thomas | 22 | f | 178 | 5 | | 10 | Tom | 23 | f | 165 | 5 | | 11 | LiMing | 22 | m | 180 | 6 | | 12 | yyr | 18 | f | 160 | 8 | +----+--------+------+------+--------+-----------+ 12 rows in set (0.00 sec)
- 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
- 146
- 147
- 148
1.4.2. mysql差异备份恢复
应用场景：误删数据
[root@ftx ~]# mysql -uroot -e 'drop database yyr;'
[root@ftx ~]# mysql -uroot -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| FTX |
| ftx |
| mysql |
| performance_schema |
| sys |
+--------------------+
注：当我们误删数据库后，应该立即设置读锁，不允许再有人往数据库当中写东西，因为在我们恢复数据库的时候是将数据库恢复到我们删除数据库前的时间，在此期间如果还有人在写入的话，那他写入的东西就会在我们恢复数据库的同时消失。
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
刷新创建新的二进制日志
[root@ftx ~]# ll /opt/data total 123036 -rw-r-----. 1 mysql mysql 56 Sep 5 13:40 auto.cnf -rw-------. 1 mysql mysql 1676 Sep 5 13:40 ca-key.pem -rw-r--r--. 1 mysql mysql 1112 Sep 5 13:40 ca.pem -rw-r--r--. 1 mysql mysql 1112 Sep 5 13:40 client-cert.pem -rw-------. 1 mysql mysql 1680 Sep 5 13:40 client-key.pem drwxr-x---. 2 mysql mysql 94 Sep 5 13:40 ftx drwxr-x---. 2 mysql mysql 58 Sep 5 13:40 FTX -rw-r-----. 1 mysql mysql 74003 Sep 5 14:09 ftx.err -rw-r-----. 1 mysql mysql 486 Sep 5 13:52 ib_buffer_pool -rw-r-----. 1 mysql mysql 12582912 Sep 5 14:23 ibdata1 -rw-r-----. 1 mysql mysql 50331648 Sep 5 14:23 ib_logfile0 -rw-r-----. 1 mysql mysql 50331648 Sep 5 13:40 ib_logfile1 -rw-r-----. 1 mysql mysql 12582912 Sep 5 14:09 ibtmp1 drwxr-x---. 2 mysql mysql 4096 Sep 5 13:39 mysql -rw-r-----. 1 mysql mysql 895 Sep 5 14:21 mysql_bin.000006 //在没有刷新二进制日志之前执行的所有动作都在这个文件里面 -rw-r-----. 1 mysql mysql 19 Sep 5 14:09 mysql_bin.index //这个文件中写的是我们的日志都写到那个地方去了 -rw-r-----. 1 mysql mysql 5 Sep 5 13:53 mysql.pid drwxr-x---. 2 mysql mysql 8192 Sep 5 13:39 performance_schema -rw-------. 1 mysql mysql 1680 Sep 5 13:40 private_key.pem -rw-r--r--. 1 mysql mysql 452 Sep 5 13:40 public_key.pem -rw-r--r--. 1 mysql mysql 1112 Sep 5 13:40 server-cert.pem -rw-------. 1 mysql mysql 1680 Sep 5 13:40 server-key.pem drwxr-x---. 2 mysql mysql 8192 Sep 5 13:39 sys [root@ftx ~]# cat /opt/data/mysql_bin.index ./mysql_bin.000006 //刷新创建新的二进制日志 [root@ftx ~]# mysqladmin -uroot -p flush-logs Enter password: [root@ftx ~]# ll /opt/data total 123040 -rw-r-----. 1 mysql mysql 56 Sep 5 13:40 auto.cnf -rw-------. 1 mysql mysql 1676 Sep 5 13:40 ca-key.pem -rw-r--r--. 1 mysql mysql 1112 Sep 5 13:40 ca.pem -rw-r--r--. 1 mysql mysql 1112 Sep 5 13:40 client-cert.pem -rw-------. 1 mysql mysql 1680 Sep 5 13:40 client-key.pem drwxr-x---. 2 mysql mysql 94 Sep 5 13:40 ftx drwxr-x---. 2 mysql mysql 58 Sep 5 13:40 FTX -rw-r-----. 1 mysql mysql 74003 Sep 5 14:09 ftx.err -rw-r-----. 1 mysql mysql 486 Sep 5 13:52 ib_buffer_pool -rw-r-----. 1 mysql mysql 12582912 Sep 5 14:23 ibdata1 -rw-r-----. 1 mysql mysql 50331648 Sep 5 14:23 ib_logfile0 -rw-r-----. 1 mysql mysql 50331648 Sep 5 13:40 ib_logfile1 -rw-r-----. 1 mysql mysql 12582912 Sep 5 14:09 ibtmp1 drwxr-x---. 2 mysql mysql 4096 Sep 5 13:39 mysql -rw-r-----. 1 mysql mysql 942 Sep 5 14:34 mysql_bin.000006 -rw-r-----. 1 mysql mysql 154 Sep 5 14:34 mysql_bin.000007 //刷新二进制日志之后则会生成一个新的这样的文件，我们后续的动作也都会存放在这个文件中 -rw-r-----. 1 mysql mysql 38 Sep 5 14:34 mysql_bin.index -rw-r-----. 1 mysql mysql 5 Sep 5 13:53 mysql.pid drwxr-x---. 2 mysql mysql 8192 Sep 5 13:39 performance_schema -rw-------. 1 mysql mysql 1680 Sep 5 13:40 private_key.pem -rw-r--r--. 1 mysql mysql 452 Sep 5 13:40 public_key.pem -rw-r--r--. 1 mysql mysql 1112 Sep 5 13:40 server-cert.pem -rw-------. 1 mysql mysql 1680 Sep 5 13:40 server-key.pem drwxr-x---. 2 mysql mysql 8192 Sep 5 13:39 sys [root@ftx ~]# cat /opt/data/mysql_bin.index ./mysql_bin.000006 ./mysql_bin.000007
- 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
恢复完全备份
[root@ftx ~]# mysql -uroot -p < all-202309051400.sql Enter password: [root@ftx ~]# mysql -uroot -e 'show databases;' +--------------------+ | Database | +--------------------+ | information_schema | | FTX | | ftx | | mysql | | performance_schema | | sys | | yyr | +--------------------+ [root@ftx ~]# mysql -uroot -e 'show tables from yyr;' +---------------+ | Tables_in_yyr | +---------------+ | course | | student | +---------------+ [root@ftx ~]# mysql -uroot -e 'select * from yyr.course;' +----+-------------+ | id | course_name | +----+-------------+ | 1 | Java | | 2 | MYSQL | | 3 | Python | | 4 | Go | | 5 | C++ | | 6 | HTML | +----+-------------+ [root@ftx ~]# mysql -uroot -e 'select * from yyr.student;' +----+--------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+--------+------+------+--------+-----------+ | 1 | Dany | 25 | m | 160 | 1 | | 2 | Green | 23 | m | 158 | 2 | | 3 | Henry | 23 | f | 185 | 1 | | 4 | Jane | 22 | m | 162 | 3 | | 5 | Jim | 24 | f | 175 | 2 | | 6 | John | 21 | f | 172 | 4 | | 7 | Lily | 22 | m | 165 | 4 | | 8 | Susan | 23 | m | 170 | 5 | | 9 | Thomas | 22 | f | 178 | 5 | | 10 | Tom | 23 | f | 165 | 5 | | 11 | LiMing | 22 | m | 180 | 7 | +----+--------+------+------+--------+-----------+
- 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
恢复差异备份
[root@ftx ~]# ll /opt/data total 123884 -rw-r-----. 1 mysql mysql 56 Sep 5 13:40 auto.cnf -rw-------. 1 mysql mysql 1676 Sep 5 13:40 ca-key.pem -rw-r--r--. 1 mysql mysql 1112 Sep 5 13:40 ca.pem -rw-r--r--. 1 mysql mysql 1112 Sep 5 13:40 client-cert.pem -rw-------. 1 mysql mysql 1680 Sep 5 13:40 client-key.pem drwxr-x---. 2 mysql mysql 94 Sep 5 19:40 ftx drwxr-x---. 2 mysql mysql 58 Sep 5 19:40 FTX -rw-r-----. 1 mysql mysql 74277 Sep 5 19:39 ftx.err -rw-r-----. 1 mysql mysql 486 Sep 5 13:52 ib_buffer_pool -rw-r-----. 1 mysql mysql 12582912 Sep 5 19:41 ibdata1 -rw-r-----. 1 mysql mysql 50331648 Sep 5 19:41 ib_logfile0 -rw-r-----. 1 mysql mysql 50331648 Sep 5 13:40 ib_logfile1 -rw-r-----. 1 mysql mysql 12582912 Sep 5 14:09 ibtmp1 drwxr-x---. 2 mysql mysql 4096 Sep 5 19:40 mysql -rw-r-----. 1 mysql mysql 942 Sep 5 14:34 mysql_bin.000006 -rw-r-----. 1 mysql mysql 867770 Sep 5 19:40 mysql_bin.000007 -rw-r-----. 1 mysql mysql 38 Sep 5 14:34 mysql_bin.index -rw-r-----. 1 mysql mysql 5 Sep 5 13:53 mysql.pid drwxr-x---. 2 mysql mysql 8192 Sep 5 13:39 performance_schema -rw-------. 1 mysql mysql 1680 Sep 5 13:40 private_key.pem -rw-r--r--. 1 mysql mysql 452 Sep 5 13:40 public_key.pem -rw-r--r--. 1 mysql mysql 1112 Sep 5 13:40 server-cert.pem -rw-------. 1 mysql mysql 1680 Sep 5 13:40 server-key.pem drwxr-x---. 2 mysql mysql 8192 Sep 5 13:39 sys drwxr-x---. 2 mysql mysql 94 Sep 5 19:40 yyr //检查误删数据库的位置在什么地方 [root@ftx ~]# mysql -uroot -p12345678 mysql> show binlog events in 'mysql_bin.000006'; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql_bin.000006 | 4 | Format_desc | 10 | 123 | Server ver: 5.7.39-log, Binlog ver: 4 | | mysql_bin.000006 | 123 | Previous_gtids | 10 | 154 | | | mysql_bin.000006 | 154 | Anonymous_Gtid | 10 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql_bin.000006 | 219 | Query | 10 | 290 | BEGIN | | mysql_bin.000006 | 290 | Table_map | 10 | 348 | table_id: 182 (yyr.student) | | mysql_bin.000006 | 348 | Write_rows | 10 | 403 | table_id: 182 flags: STMT_END_F | | mysql_bin.000006 | 403 | Xid | 10 | 434 | COMMIT /* xid=1062 */ | | mysql_bin.000006 | 434 | Anonymous_Gtid | 10 | 499 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql_bin.000006 | 499 | Query | 10 | 570 | BEGIN | | mysql_bin.000006 | 570 | Table_map | 10 | 628 | table_id: 182 (yyr.student) | | mysql_bin.000006 | 628 | Update_rows | 10 | 710 | table_id: 182 flags: STMT_END_F | | mysql_bin.000006 | 710 | Xid | 10 | 741 | COMMIT /* xid=1064 */ | | mysql_bin.000006 | 741 | Anonymous_Gtid | 10 | 806 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql_bin.000006 | 806 | Query | 10 | 895 | drop database yyr | //可以看到此行就是误删yyr数据库的动作 | mysql_bin.000006 | 895 | Rotate | 10 | 942 | mysql_bin.000007;pos=4 | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 15 rows in set (0.00 sec) //使用mysqlbinlog恢复差异备份，此处我们要将执行动作恢复到执行删除数据库之前，也就是上表的806之前 [root@ftx ~]# mysqlbinlog --stop-position=806 /opt/data/mysql_bin.000006 | mysql -uroot -p12345678 mysql: [Warning] Using a password on the command line interface can be insecure. [root@ftx ~]# mysql -uroot -e 'select * from yyr.student;' +----+--------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+--------+------+------+--------+-----------+ | 1 | Dany | 25 | m | 160 | 1 | | 2 | Green | 23 | m | 158 | 2 | | 3 | Henry | 23 | f | 185 | 1 | | 4 | Jane | 22 | m | 162 | 3 | | 5 | Jim | 24 | f | 175 | 2 | | 6 | John | 21 | f | 172 | 4 | | 7 | Lily | 22 | m | 165 | 4 | | 8 | Susan | 23 | m | 170 | 5 | | 9 | Thomas | 22 | f | 178 | 5 | | 10 | Tom | 23 | f | 165 | 5 | | 11 | LiMing | 22 | m | 180 | 6 | | 12 | yyr | 18 | f | 160 | 8 | +----+--------+------+------+--------+-----------+ //我们可以看到，表中数据恢复到了我们完全备份后再次修改的时候，证明恢复差异备份成功
- 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
在此之后我们的执行动作则都会存放在刷新的二进制日志里面
//可以通过此命令进行查询 mysql> show binlog events in 'mysql_bin.000007'\G 举例说明： mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | FTX | | ftx | | mysql | | performance_schema | | sys | | test | | yyr | +--------------------+ 8 rows in set (0.00 sec) mysql> show binlog events in 'mysql_bin.000007'\G ...... ...... *************************** 472. row *************************** Log_name: mysql_bin.000007 Pos: 868416 Event_type: Query Server_id: 10 End_log_pos: 868510 Info: create database test 472 rows in set (0.00 sec) mysql> drop database test; Query OK, 0 rows affected (0.00 sec) mysql> show binlog events in 'mysql_bin.000007'\G ...... ...... *************************** 474. row *************************** Log_name: mysql_bin.000007 Pos: 868575 Event_type: Query Server_id: 10 End_log_pos: 868660 Info: drop database test 474 rows in set (0.00 sec)
- 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
错误排查报告
[root@ftx ~]# mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202309051320.sql Enter password: mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'': Table 'performance_schema.session_variables' doesn't exist (1146) 出现此类状况是因为我们将performance_schema这个库给删了 //解决方法： 初始化数据库 [root@ftx ~]# service mysqld stop ##首先关闭服务 Shutting down MySQL.. SUCCESS! [root@ftx ~]# cd /opt/data/ ##进入到我们存放数据库的目录当中 [root@ftx data]# ls auto.cnf client-key.pem ib_buffer_pool ibtmp1 mysql_bin.index server-cert.pem ca-key.pem ftx ibdata1 mysql mysql.pid server-key.pem ca.pem FTX ib_logfile0 mysql_bin.000001 private_key.pem yyr client-cert.pem ftx.err ib_logfile1 mysql_bin.000002 public_key.pem [root@ftx data]# mkdir /opt/backup ##创建一个新目录 [root@ftx data]# mv * /opt/backup/ ##将我们之前恢复的数据全部移到新建的目录中存放 [root@ftx data]# ls [root@ftx data]# cd [root@ftx ~]# mysqld --initialize --user mysql --datadir /opt/data ##初始化数据库 2023-09-05T05:39:00.864770Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2023-09-05T05:39:01.408258Z 0 [Warning] InnoDB: New log files created, LSN=45790 2023-09-05T05:39:01.491798Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2023-09-05T05:39:01.565573Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 849294ee-4bae-11ee-a3c9-000c29d9f7e8. 2023-09-05T05:39:01.566011Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2023-09-05T05:39:01.736074Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher. 2023-09-05T05:39:01.736101Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher. 2023-09-05T05:39:01.736420Z 0 [Warning] CA certificate ca.pem is self signed. 2023-09-05T05:39:01.849120Z 1 [Note] A temporary password is generated for root@localhost: rdiA_7lB=Sp; ##此处生成了一个临时密码，然而我们之前恢复的文件数据中，有我们设置好了的密码，所以我们只需要将我们之前恢复的数据全部覆盖到/opt/data目录中就可以使用我们之前设置的密码登录了 [root@ftx ~]# cd /opt/data [root@ftx data]# ls auto.cnf client-cert.pem ibdata1 mysql performance_schema server-cert.pem ca-key.pem client-key.pem ib_logfile0 mysql_bin.000001 private_key.pem server-key.pem ca.pem ib_buffer_pool ib_logfile1 mysql_bin.index public_key.pem sys [root@ftx data]# \cp -r /opt/backup/* . ##cp前加上\是避免需要我们手动确认覆盖相同的文件 [root@ftx data]# ls auto.cnf client-key.pem ib_buffer_pool ibtmp1 mysql_bin.index public_key.pem yyr ca-key.pem ftx ibdata1 mysql mysql.pid server-cert.pem ca.pem FTX ib_logfile0 mysql_bin.000001 performance_schema server-key.pem client-cert.pem ftx.err ib_logfile1 mysql_bin.000002 private_key.pem sys [root@ftx ~]# service mysqld start Starting MySQL. ERROR! The server quit without updating PID file (/opt/data/mysql.pid). //此时发现服务无法启动 原因在于/opt/data/目录下的某些文件属于root，而并非mysql [root@ftx ~]# ll /opt/data total 123024 -rw-r-----. 1 mysql mysql 56 Sep 5 13:40 auto.cnf -rw-------. 1 mysql mysql 1676 Sep 5 13:40 ca-key.pem -rw-r--r--. 1 mysql mysql 1112 Sep 5 13:40 ca.pem -rw-r--r--. 1 mysql mysql 1112 Sep 5 13:40 client-cert.pem -rw-------. 1 mysql mysql 1680 Sep 5 13:40 client-key.pem drwxr-x---. 2 root root 94 Sep 5 13:40 ftx drwxr-x---. 2 root root 58 Sep 5 13:40 FTX -rw-r-----. 1 root root 61409 Sep 5 13:40 ftx.err -rw-r-----. 1 mysql mysql 887 Sep 5 13:40 ib_buffer_pool -rw-r-----. 1 mysql mysql 12582912 Sep 5 13:40 ibdata1 -rw-r-----. 1 mysql mysql 50331648 Sep 5 13:40 ib_logfile0 -rw-r-----. 1 mysql mysql 50331648 Sep 5 13:40 ib_logfile1 -rw-r-----. 1 root root 12582912 Sep 5 13:40 ibtmp1 drwxr-x---. 2 mysql mysql 4096 Sep 5 13:39 mysql -rw-r-----. 1 mysql mysql 201 Sep 5 13:40 mysql_bin.000001 -rw-r-----. 1 root root 154 Sep 5 13:40 mysql_bin.000002 -rw-r-----. 1 mysql mysql 38 Sep 5 13:40 mysql_bin.index -rw-r-----. 1 root root 5 Sep 5 13:40 mysql.pid drwxr-x---. 2 mysql mysql 8192 Sep 5 13:39 performance_schema -rw-------. 1 mysql mysql 1680 Sep 5 13:40 private_key.pem -rw-r--r--. 1 mysql mysql 452 Sep 5 13:40 public_key.pem -rw-r--r--. 1 mysql mysql 1112 Sep 5 13:40 server-cert.pem -rw-------. 1 mysql mysql 1680 Sep 5 13:40 server-key.pem drwxr-x---. 2 mysql mysql 8192 Sep 5 13:39 sys drwxr-x---. 2 root root 94 Sep 5 13:40 yyr 解决方法： 将/opt/data/目录下的所有文件的属主、属组都改为mysql [root@ftx ~]# chown -R mysql.mysql /opt/data/ [root@ftx ~]# service mysqld start Starting MySQL. SUCCESS! [root@ftx ~]# ss -antl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 0.0.0.0:22 0.0.0.0:* LISTEN 0 128 [::]:22 [::]:* LISTEN 0 80 *:3306 *:* mysql> show databases; //再次查看，数据库全部恢复 +--------------------+ | Database | +--------------------+ | information_schema | | FTX | | ftx | | mysql | | performance_schema | | sys | | yyr | +--------------------+ 7 rows in set (0.00 sec)
- 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
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/羊村懒王/article/detail/410291
推荐阅读
相关标签
