mysql数据库备份与恢复

最新推荐文章于 2024-05-05 09:41:45 发布

qq_58328312

最新推荐文章于 2024-05-05 09:41:45 发布

阅读量172

点赞数

文章标签：大数据

本文链接：https://blog.csdn.net/qq_58328312/article/details/125549960

版权

1.数据库备份方案：

全量备份
增量备份
差异备份

备份方案	特点
全量备份	全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝,数据恢复快,备份时间长
增量备份	增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。没有重复的备份数,备份时间短,恢复数据时必须按一定的顺序进行
差异备份	备份上一次的完全备份后发生变化的所有文件。差异备份是指在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复

备份的概念：

1.全量备份：把所有数据或应用都备份一遍数据恢复快但是备份时间长

2.增量备份：当备份或增量备份后在进行增量备份，只会备份和上次不同的数据（增加修改的数据）备份时间短但是恢复数据需要按顺序

3.差异备份：和上一次备份做对比备份发生变化的文件恢复数据时只需要对第一次全量备份和最后一次差异备份进行恢复

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 |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.07 sec)

mysql> use school;
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)
 
//此时mysql库有一个数据库＋2张表格，备份全库
[root@localhost ~]# mysqldump -uroot -p'runtime123!' --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@localhost ~]# ls
all-20220630045011.sql  initial-setup-ks.cfg

//备份表，可指定库内的某个表
[root@localhost ~]# mysqldump -uroot -p'runtime123!' school tb_course > tb_course.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20220630045011.sql  anaconda-ks.cfg  tb_course.sql

//备份一个指定的库
[root@localhost ~]# mysqldump -uroot -p'runtime123!' school > school.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20220630045011.sql    school.sql    anaconda-ks.cfg                           tb_course.sql

mysql数据恢复

删除库，使用备份文件恢复

//删除school库
mysql> drop database school;
Query OK, 2 rows affected (0.71 sec)

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


//使用备份文件恢复删除的school库
[root@localhost ~]# mysql -uroot -p'runtime123!' < all-20220630045011.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

[root@localhost ~]# mysql -uroot -p'runtime123!' -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

删除表，并且使用备份文件将表恢复到指定库中

//删除表
mysql> drop table tb_course;
Query OK, 0 rows affected (0.01 sec)

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


//恢复表并且可以把表恢复到指定库中
[root@localhost ~]# mysql -uroot -p'runtime123!' school < tb_course.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

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

差异备份

开启mysql服务器的二进制日志功能

 //开启MySQL服务器的二进制日志功能
[root@localhost local]# vim /etc/my.cnf
[root@localhost local]# 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

server-id=1
log-bin=mysql_bin  //文件尾部添加这两行

[root@localhost ~]# systemctl restart mysqld  //重启mysql服务让其生效

进行完全备份

//进行完全备份
[root@localhost ~]# mysqldump -uroot -p'runtime123!' --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-$(date '+%Y%m%d%H%M%S').sql


//向表内添加新数据
mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | Html        |
+----+-------------+
6 rows in set (0.01 sec)

mysql> insert tb_course (course_name) values ('Linux');
Query OK, 1 row affected (0.00 sec)


mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | Html        |
|  7 | Linux       |
+----+-------------+
7 rows in set (0.00 sec)

//更新数据
mysql> update tb_course  set course_name = 'Lamp' where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Lamp        |
|  5 | C++         |
|  6 | Html        |
|  7 | Linux       |
+----+-------------+
7 rows in set (0.00 sec)



//删除表
mysql> drop table tb_students_info;
Query OK, 0 rows affected (0.10 sec)

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

删除数据库

mysql> drop database school;
Query OK, 1 row affected (0.10 sec)

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

查看新的二进制日志文件

[root@localhost data]# mysqladmin -uroot -p'runtime123!' flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

[root@localhost data]# ll
-rw-r-----. 1 mysql mysql     1119 6月  30 10:36 mysql_bin.000004
-rw-r-----. 1 mysql mysql      154 6月  30 10:36 mysql_bin.000005

恢复完全备份

[root@localhost ~]# mysql -uroot -pruntime123! < all-20220630094428.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.


//恢复到未修改之前
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 |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

恢复差异备份

//找到删除库的位置
mysql> show binlog events in 'mysql_bin.000004';
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                  |
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
| mysql_bin.000004 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.37-log, Binlog ver: 4                                 |
| mysql_bin.000004 |  123 | Previous_gtids |         1 |         154 |                                                                       |
| mysql_bin.000004 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                  |
| mysql_bin.000004 |  219 | Query          |         1 |         293 | BEGIN                                                                 |
| mysql_bin.000004 |  293 | Table_map      |         1 |         350 | table_id: 176 (school.tb_course)                                      |
| mysql_bin.000004 |  350 | Write_rows     |         1 |         396 | table_id: 176 flags: STMT_END_F                                       |
| mysql_bin.000004 |  396 | Xid            |         1 |         427 | COMMIT /* xid=959 */                                                  |
| mysql_bin.000004 |  427 | Anonymous_Gtid |         1 |         492 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                  |
| mysql_bin.000004 |  492 | Query          |         1 |         566 | BEGIN                                                                 |
| mysql_bin.000004 |  566 | Table_map      |         1 |         623 | table_id: 176 (school.tb_course)                                      |
| mysql_bin.000004 |  623 | Update_rows    |         1 |         680 | table_id: 176 flags: STMT_END_F                                       |
| mysql_bin.000004 |  680 | Xid            |         1 |         711 | COMMIT /* xid=961 */                                                  |
| mysql_bin.000004 |  711 | Anonymous_Gtid |         1 |         776 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                  |
| mysql_bin.000004 |  776 | Query          |         1 |         909 | use `school`; DROP TABLE `tb_students_info` /* generated by server */ |
| mysql_bin.000004 |  909 | Anonymous_Gtid |         1 |         974 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                  |
| mysql_bin.000004 |  974 | Query          |         1 |        1072 | drop database school                                                  |
| mysql_bin.000004 | 1072 | Rotate         |         1 |        1119 | mysql_bin.000005;pos=4                                                |
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
17 rows in set (0.00 sec)



//使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysqlbinlog --stop-position=974 /opt/data/mysql_bin.000004 | mysql -uroot -p'runtime123!'
mysql: [Warning] Using a password on the command line interface can be insecure.


//数据恢复到修改之后的状态
mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Lamp        |
|  5 | C++         |
|  6 | Html        |
|  7 | Linux       |
+----+-------------+
7 rows in set (0.00 sec)

qq_58328312

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql数据库备份与恢复

1.数据库备份方案：备份的概念：1.全量备份：把所有数据或应用都备份一遍数据恢复快但是备份时间长2.增量备份：当备份或增量备份后在进行增量备份，只会备份和上次不同的数据（增加修改的数据）备份时间短但是恢复数据需要按顺序3.差异备份：和上一次备份做对比备份发生变化的文件恢复数据时只需要对第一次全量备份和最后一次差异备份进行恢复全量备份mysql数据恢复删除库，使用备份文件恢复删除表，并且使用备份文件将表恢复到指定库中差异备份开启mysql服务器的二进制日志功能
复制链接

扫一扫