• MySQL数据库的备份与恢复

    MySQL数据库的备份与恢复

    文章目录

    1. 常用的备份方案

    • 全量备份，数据库内容全部备份
    • 增量备份，在全量备份的基础上把新增的数据备份
    • 差异备份，
    备份方案特点
    全量备份对某一段时间的所有数据进行完全拷贝，数据恢复快，备份时间长。
    增量备份在第一次全量备份或者增量备份后，每次备份只需备份前一次相比增加。没有重复的备份数据，备份时间短，恢复数据时必须按照一定的顺序进行
    差异备份备份上次完全备份后发生改变的所有文件数据，在进行第一次全备份后进行差异备份的这段时间内增加，修改文件的备份。进行恢复时，只需要对第一次全备和最后一次差异备份进行恢复

    2. MySQL备份工具，mysqldump

    mysqldump 选项 --all-databases 全备
mysqldump 选项 database [tables]
mysqldump 选项 --databases db1 [2][3]

选项：
-uusername    指定数据库用户名
-hhost        指定主机，IP地址
-ppassword    数据库用户密码
-P            数据库端口号

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    3. 进行全备，与恢复

    1.全备
[root@129a ~]# mysqldump -uroot -prun123456 --all-databases > all-$(date '+%Y%m%d%H%M%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@129a ~]# ls
all-20220630170137.sql  anaconda-ks.cfg  passwd  test  web
[root@129a ~]# file all-20220630170137.sql
all-20220630170137.sql: UTF-8 Unicode text, with very long lines
[root@129a ~]# 

2.误删
mysql> drop database school;
  
3.恢复
[root@129a ~]# mysql -uroot -prun123456 < all-20220630170137.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@129a ~]# 
mysql> show tables from school;
+------------------+
| Tables_in_school |
+------------------+
| tb_coursse       |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>   

    • 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

    4. 备份一张表，数据库

    备份表
[root@129a ~]# mysqldump -uroot -prun123456 school tb_coursse > course.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@129a ~]# ls
all-20220630170137.sql  anaconda-ks.cfg  course.sql  test  web
[root@129a ~]# 

备份数据库
[root@129a ~]# mysqldump -uroot -prun123456 school > school.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@129a ~]# ls
all-20220630170137.sql  course.sql  test
anaconda-ks.cfg         school.sql  web
[root@129a ~]# 

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

    5. 删除某张表，并且恢复

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

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| tb_students_info |
+------------------+
1 row in set (0.00 sec)

mysql> 

在数据库里面恢复
[root@129a ~]# ls
all-20220630170137.sql  course.sql  test
anaconda-ks.cfg         school.sql  web
[root@129a ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> use school;
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> source course.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)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (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)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.10 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)

Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

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, 1 warning (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)

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_school |
+------------------+
| tb_coursse       |
| tb_students_info |
+------------------+
2 rows in set (0.01 sec)

mysql> select * from tb_coursse;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | java        |
|  2 | mysql       |
|  3 | python      |
|  4 | go          |
|  5 | c++         |
|  6 | html        |
+----+-------------+
6 rows in set (0.00 sec)

      
mysql> drop table tb_coursse;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| tb_students_info |
+------------------+
1 row in set (0.00 sec)

mysql> 
      
在数据库外边恢复
[root@129a ~]# mysql -uroot -prun123456 school < course.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@129a ~]# 

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| tb_coursse       |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from tb_coursse;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | java        |
|  2 | mysql       |
|  3 | python      |
|  4 | go          |
|  5 | c++         |
|  6 | html        |
+----+-------------+
6 rows in set (0.00 sec)

mysql>         

    • 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
    • 149
    • 150
    • 151
    • 152
    • 153
    • 154
    • 155
    • 156
    • 157

    6. MySQL的二进制备份与恢复

    6.1 开启MySQL的二进制日志功能

    1.修改文件内容 /etc/my.cnf
[root@129a ~]# vim /etc/my.cnf 
[root@129a ~]# cat /etc/my.cnf 
[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@129a ~]#

2.查看内容
[root@129a ~]# cd /opt/data/
[root@129a data]# ls
129a.example.com.err  ib_logfile0         private_key.pem
auto.cnf              ib_logfile1         public_key.pem
ca-key.pem            ibdata1             school
ca.pem                ibtmp1              server-cert.pem
client-cert.pem       mysql               server-key.pem
client-key.pem        mysql.pid           sys
ib_buffer_pool        performance_schema
[root@129a data]# 

3.重启mysql
[root@129a ~]# systemctl restart mysqld
[root@129a ~]# cd /opt/data/
[root@129a data]# ls
129a.example.com.err  ib_logfile1         private_key.pem
auto.cnf              ibdata1             public_key.pem
ca-key.pem            ibtmp1              school
ca.pem                mysql               server-cert.pem
client-cert.pem       mysql.pid           server-key.pem
client-key.pem        mysql_bin.000001    sys
ib_buffer_pool        mysql_bin.index
ib_logfile0           performance_schema
[root@129a data]#
[root@129a data]# cat mysql_bin.index 
./mysql_bin.000001
[root@129a data]# 

mysql_bin.index //当前正在记录日志文件是哪个？
mysql_bin.000001

    • 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

    6.2 对数据库进行完全备份

    • –single-transaction 单独的事务
    • –flush-logs 刷新日志
    • –master-data=2 版本号
    • –all-databases 所有的数据库
    • –delete-master-logs 删除master的日志
    --single-transaction  单独的事务
--flush-logs  刷新日志
--master-data=2 版本号
--all-databases 所有的数据库
--delete-master-logs 删除master的日志

[root@129a ~]# mysqldump -uroot -prun123456 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-$(date '+%Y%m%d%H%M%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@129a ~]# ls
all-20220630170137.sql  anaconda-ks.cfg  school.sql  web
all-20220630175624.sql  course.sql       test
[root@129a ~]# cd /opt/data/
[root@129a data]# ls
129a.example.com.err  ib_logfile1         private_key.pem
auto.cnf              ibdata1             public_key.pem
ca-key.pem            ibtmp1              school
ca.pem                mysql               server-cert.pem
client-cert.pem       mysql.pid           server-key.pem
client-key.pem        mysql_bin.000002    sys
ib_buffer_pool        mysql_bin.index
ib_logfile0           performance_schema
[root@129a data]# 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 用vimdiff查看不同内容，使用qa!退出

    6.3 新增数据，创建新表，修改数据，删除数据

    1.新增数据
mysql> insert tb_coursse(course_name) values('linux'),('lishi');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tb_coursse;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | java        |
|  2 | mysql       |
|  3 | python      |
|  4 | go          |
|  5 | c++         |
|  6 | html        |
|  7 | linux       |
|  8 | lishi       |
+----+-------------+
8 rows in set (0.00 sec)

mysql> 


2.创建新表
mysql> create table info(id int not null primary key auto_increment,name varchar(10));
Query OK, 0 rows affected (0.15 sec)

mysql> insert info(name) values('zhang'),('wang'),('li'),('ying');
Query OK, 4 rows affected (0.23 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from info;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhang |
|  2 | wang  |
|  3 | li    |
|  4 | ying  |
+----+-------+
4 rows in set (0.00 sec)

mysql> 

3.修改数据
mysql> update tb_students_info set height = 177 where name = 'green'; 
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

            
4.删除数据
mysql> delete from tb_students_info where name = 'lily';
Query OK, 1 row affected (0.13 sec)

mysql> select * from tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | danny  |   25 | M    |    160 |         1 |
|  2 | green  |   23 | M    |    177 |         2 |
|  3 | henry  |   23 | F    |    185 |         1 |
|  4 | jane   |   23 | M    |    162 |         3 |
|  5 | jim    |   22 | F    |    175 |         2 |
|  6 | john   |   21 | F    |    172 |         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 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)

mysql>               

    • 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

    6.4 误删数据库

    mysql> drop database school;
Query OK, 3 rows affected (0.07 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> 

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

    6.5 刷新创建新的二进制日志， mysqladmin -uroot -prun123456 flush-logs

    • 刷新二进制日志是为了产生新的日志文件，他人的数据内容会在新的日志文件中记录
    [root@129a ~]# cd /opt/data/
[root@129a data]# ls
129a.example.com.err  ib_logfile0       mysql_bin.index
auto.cnf              ib_logfile1       performance_schema
ca-key.pem            ibdata1           private_key.pem
ca.pem                ibtmp1            public_key.pem
client-cert.pem       mysql             server-cert.pem
client-key.pem        mysql.pid         server-key.pem
ib_buffer_pool        mysql_bin.000002  sys
[root@129a data]#

刷新
[root@129a ~]# mysqladmin -uroot -prun123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@129a ~]# cd /opt/data/
[root@129a data]# ls
129a.example.com.err  ib_logfile1       performance_schema
auto.cnf              ibdata1           private_key.pem
ca-key.pem            ibtmp1            public_key.pem
ca.pem                mysql             server-cert.pem
client-cert.pem       mysql.pid         server-key.pem
client-key.pem        mysql_bin.000002  sys
ib_buffer_pool        mysql_bin.000003
ib_logfile0           mysql_bin.index
[root@129a data]# 
[root@129a data]# cat mysql_bin.index
./mysql_bin.000002
./mysql_bin.000003
[root@129a data]# 

    • 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

    6.6 恢复完全备份

    [root@129a ~]# mysql -uroot -prun123456 < all-20220630175624.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@129a ~]# 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use school;
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_school |
+------------------+
| tb_coursse       |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from tb_coursse;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | java        |
|  2 | mysql       |
|  3 | python      |
|  4 | go          |
|  5 | c++         |
|  6 | html        |
+----+-------------+
6 rows in set (0.00 sec)

mysql> 

    • 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

    6.7 检查误删数据在什么位置 show binlog events in 'mysql_bin.000002';

    events 事件 pos开始位置 Event_type事件类型 End_log_pos 结束位置 
进入数据库，show binlog events in 'mysql_bin.000002';找到第二相同数值的第二个数值


mysql> show binlog events in 'mysql_bin.000002';
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                         |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------+
| mysql_bin.000002 |    4 | Format_desc    |        10 |         123 | Server ver: 5.7.37-log, Binlog ver: 4                                                        |
| mysql_bin.000002 |  123 | Previous_gtids |        10 |         154 |                                                                                              |
| mysql_bin.000002 |  154 | Anonymous_Gtid |        10 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                         |
| mysql_bin.000002 |  219 | Query          |        10 |         375 | use `school`; create table info(id int not null primary key auto_increment,name varchar(10)) |
| mysql_bin.000002 |  375 | Anonymous_Gtid |        10 |         440 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                         |
| mysql_bin.000002 |  440 | Query          |        10 |         514 | BEGIN                                                                                        |
| mysql_bin.000002 |  514 | Table_map      |        10 |         566 | table_id: 142 (school.info)                                                                  |
| mysql_bin.000002 |  566 | Write_rows     |        10 |         640 | table_id: 142 flags: STMT_END_F                                                              |
| mysql_bin.000002 |  640 | Xid            |        10 |         671 | COMMIT /* xid=484 */                                                                         |
| mysql_bin.000002 |  671 | Anonymous_Gtid |        10 |         736 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                         |
| mysql_bin.000002 |  736 | Query          |        10 |         810 | BEGIN                                                                                        |
| mysql_bin.000002 |  810 | Table_map      |        10 |         868 | table_id: 140 (school.tb_coursse)                                                            |
| mysql_bin.000002 |  868 | Write_rows     |        10 |         925 | table_id: 140 flags: STMT_END_F                                                              |
| mysql_bin.000002 |  925 | Xid            |        10 |         956 | COMMIT /* xid=488 */                                                                         |
| mysql_bin.000002 |  956 | Anonymous_Gtid |        10 |        1021 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                         |
| mysql_bin.000002 | 1021 | Query          |        10 |        1095 | BEGIN                                                                                        |
| mysql_bin.000002 | 1095 | Table_map      |        10 |        1165 | table_id: 141 (school.tb_students_info)                                                      |
| mysql_bin.000002 | 1165 | Update_rows    |        10 |        1245 | table_id: 141 flags: STMT_END_F                                                              |
| mysql_bin.000002 | 1245 | Xid            |        10 |        1276 | COMMIT /* xid=493 */                                                                         |
| mysql_bin.000002 | 1276 | Anonymous_Gtid |        10 |        1341 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                         |
| mysql_bin.000002 | 1341 | Query          |        10 |        1415 | BEGIN                                                                                        |
| mysql_bin.000002 | 1415 | Table_map      |        10 |        1485 | table_id: 141 (school.tb_students_info)                                                      |
| mysql_bin.000002 | 1485 | Delete_rows    |        10 |        1541 | table_id: 141 flags: STMT_END_F                                                              |
| mysql_bin.000002 | 1541 | Xid            |        10 |        1572 | COMMIT /* xid=494 */                                                                         |
| mysql_bin.000002 | 1572 | Anonymous_Gtid |        10 |        1637 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                         |
| mysql_bin.000002 | 1637 | Query          |        10 |        1735 | drop database school                                                                         |
| mysql_bin.000002 | 1735 | Rotate         |        10 |        1782 | mysql_bin.000003;pos=4                                                                       |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------+
27 rows in set (0.00 sec)

mysql> 

    • 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

    6.8 恢复数据

    1.不加|是查看
[root@129a ~]# mysqlbinlog --stop-position=1637 /opt/data/mysql_bin.000002

2.恢复
[root@129a ~]# mysqlbinlog --stop-position=1637 /opt/data/mysql_bin.000002 | mysql -uroot -prun123456
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@129a ~]# 


3.查看数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use school;
Database changed
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info             |
| tb_coursse       |
| tb_students_info |
+------------------+
3 rows in set (0.00 sec)

mysql> 
mysql> select * from info;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhang |
|  2 | wang  |
|  3 | li    |
|  4 | ying  |
+----+-------+
4 rows in set (0.00 sec)

mysql> select * from tb_coursse;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | java        |
|  2 | mysql       |
|  3 | python      |
|  4 | go          |
|  5 | c++         |
|  6 | html        |
|  7 | linux       |
|  8 | lishi       |
+----+-------------+
8 rows in set (0.00 sec)

mysql> select * from tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | danny  |   25 | M    |    160 |         1 |
|  2 | green  |   23 | M    |    177 |         2 |
|  3 | henry  |   23 | F    |    185 |         1 |
|  4 | jane   |   23 | M    |    162 |         3 |
|  5 | jim    |   22 | F    |    175 |         2 |
|  6 | john   |   21 | F    |    172 |         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 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)

mysql> 

    • 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

    7. MySQL数据库的三大类

    7.1 按备份系统的准备程度，可将其分为 冷备份、温备份和热备份三大类 :

    • 冷备份 : 离线备份 数据库服务停止

    • 优点 : 设备投资较少，节省通信费用，通信环境要求不高

    • 缺点 : 恢复时间较长，一般要数天至1周，数据完整性与一致性较差

    • 温备份 : 将备份系统已安装配置成与当前使用的系统相同或相似的系统和网络运行环境，安装应用系统业务定期备份数据。一旦发生灾难，直接使用定期备份数据，手工逐笔或自动批量追补孤立数据或将终端用户通过通讯线路切换到备份系统，恢复业务运行

    • 优点 : 设备投资较少，通信环境要求不高

    • 缺点 : 恢复时间长，一般要十几个小时至数天，数据完整性与一致性较差

    • 热备份 : 备份处于联机状态，在线备份，数据库没有停止服务

    • 优点 : 恢复时间短，一般几十分钟到数小时，数据完整性与一致性最好，数据丢失可能性最小

    • 缺点 : 设备投资大，通信费用高，通信环境要求高，平时运行管理较复杂

  • 相关阅读:
    第十八章 Nacos注册中心详解-入门案例
    LInux驱动开发笔记（十）SPI子系统及其驱动
    数据库笔记
    2023谷歌开发者大会直播详细脚本
    vue3中如何掉用子組件的方法
    【附源码】Python计算机毕业设计旅游组团管理系统
    RK3568平台开发系列讲解（视频篇）视频编码的工作原理
    Discourse 可以支持的存储类型
    【力扣每日一题】2023.10.13 避免洪水泛滥
    如何连接到sqlplus
  • 原文地址：https://blog.csdn.net/mushuangpanny/article/details/125548502