数据库备份与恢复

最新推荐文章于 2024-05-11 11:00:05 发布

嗝~壁老黄

最新推荐文章于 2024-05-11 11:00:05 发布

阅读量411

点赞数 7

文章标签：数据库

本文链接：https://blog.csdn.net/m0_69852463/article/details/132703164

版权

数据库备份与恢复

mysql数据库备份与恢复

数据库常用备份方案：

全量备份: 全量备份就是对一个时间点上所有的数据进行备份，特点是数据恢复快，但是如果数据量大的话恢复数据的时间会很长

增量备份：增量备份是指上一次备份之后,对发生变化的数据进行的备份。增量备份的优点:没有重复的备份数据,因此每次增量备份的数据量不大,备份所需的时间很短,但是恢复数据时需要按照顺序恢复使用恢复时间长

差异备份：差异备份就是只备份上一次全备后发生便化或修改的文件，恢复时只恢复上一次全备和最后一次差备，因此备份时间和恢复时间都比较短

备份数据恢复的原理：备份数据就是把数据产生的方式写到一个文件里面，恢复数据时执行文件里面的恢复操作

mysql备份工具mysqldump

常用的选项有：
-u 指定数据库用户名
-h 使用ip地址指定服务器主机
-p 指定数据库用户的密码
-P 指定数据库监听的端口

全量备份：

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

mysql> 
mysql> use huangtianen;
Database changed
mysql> 
mysql> show tables;
+-----------------------+
| Tables_in_huangtianen |
+-----------------------+
| student1              |
| student2              |
| student3              |
+-----------------------+
3 rows in set (0.00 sec)
mysql> 
mysql> quit
Bye
[root@controller ~]# 
[root@controller ~]# mysqldump -uroot -huangtianen -h192.168.10.131 -P3306 --all-databases > all-2023-9-5.sql              
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]# ls
all-2023-9-5.sql  anaconda-ks.cfg  
[root@controller ~]#

备份huangtianen数据库里的student1表和student2表

[root@controller ~]# mysqldump -uroot -huangtianen -h192.168.10.131 -P3306 huangtianen student1 student2 > table-2023-9-5.sql              
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]# ls
all-2023-9-5.sql  anaconda-ks.cfg  table-2023-9-5.sql
[root@controller ~]#

备份huangtianen数据库

[root@controller ~]# mysqldump -uroot -huangtianen -h192.168.10.131 -P3306 --databases huangtianen > huangtianen.sql                            
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]# 
[root@controller ~]# ls
all-2023-9-5.sql  anaconda-ks.cfg  pass  table-2023-9-5.sql  xingge.sql
[root@controller ~]#

模拟误删数据库

[root@controller ~]# mysql -e 'drop database huangtianen;'
[root@controller ~]# 
[root@controller ~]# mysql -e 'show databases;'          
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@controller ~]#

mysql数据恢复

恢复被删xingge数据库里的

[root@controller ~]# mysql -uroot -huangtianen < huangtianen.sql                         
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]# 
[root@controller ~]# mysql -e 'show databases;'                             
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| huangtianen        |
+--------------------+
[root@controller ~]#

恢复huangtianen数据库的student1表和student2表

[root@controller ~]# mysql -e 'show tables from huangtianen;'   
+-----------------------+
| Tables_in_huangtianen |
+-----------------------+
| student1              |
| student2              |
| student3              |
+-----------------------+
[root@controller ~]# 
[root@controller ~]# ls
all-2023-9-5.sql  anaconda-ks.cfg  table-2023-9-5.sql  huangtianen.sql
[root@controller ~]# 
[root@controller ~]# mysql -e 'drop table huangtianen.student1;'
[root@controller ~]# mysql -e 'drop table huangtianen.student2;'
[root@controller ~]# 
[root@controller ~]# mysql -e 'show tables from huangtianen;'
+-----------------------+
| Tables_in_huangtianen |
+-----------------------+
| student3              |
+-----------------------+
[root@controller ~]# 
[root@controller ~]# mysql -uroot -huangtianen
mysql: [Warning] Using a password on the command line interface can be insecure.
...
mysql> 
mysql> use huangtianen;
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> 
mysql> source table-2023-9-5.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> 
mysql> show tables;
+-----------------------+
| Tables_in_huangtianen |
+-----------------------+
| student1              |
| student2              |
| student3              |
+-----------------------+
3 rows in set (0.00 sec)
mysql>

模拟删除整个数据库

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

mysql> drop database huangtianen;
Query OK, 3 rows affected, 2 warnings (0.00 sec)

mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
1 row in set (0.00 sec)
mysql> 
mysql> quit;
Bye
[root@controller ~]#

恢复整个数据库

[root@controller ~]# ls
all-2023-9-5.sql  anaconda-ks.cfg  table-2023-9-5.sql  huangtianen.sql
[root@controller ~]# 
[root@controller ~]# mysql -uroot -huangtianen < all-2023-9-5.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]# 
[root@controller ~]# mysql -e 'show databases;'            
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| huangtianen        |
+--------------------+
[root@controller ~]#

差异备份与恢复

mysql差异备份

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

编辑my.cnf配置文件设置服务器标识符，开启二进制日志功能

[root@controller ~]# vim /etc/my.cnf 
[mysqld]
...
server-id = 10
log-bin = mysql_bin
[root@controller ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@controller ~]#

先对数据库进行完全备份

[root@controller ~]# mysqldump -uroot -huangtianen --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202309051036.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]# 
[root@controller ~]# ls
all-202309051036.sql  all-2023-9-5.sql  anaconda-ks.cfg  pass  table-2023-9-5.sql  huangtianen.sql
[root@controller ~]#

增加新内容

mysql> select * from student1;
+----+-------------+------+
| 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 | chenshou    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.01 sec)
mysql> 
mysql> insert student1(name,age) values('laohuang',66);
Query OK, 1 row affected (0.00 sec)
mysql> 
mysql> select * from student1;
+----+-------------+------+
| 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 | chenshou    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
| 12 | laohuang    |   66 |
+----+-------------+------+
12 rows in set (0.00 sec)
mysql> 
mysql> update student1 set age = null where name = 'laohuang';    
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> 
mysql> select * from student1;
+----+-------------+------+
| 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 | chenshou    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
| 12 | laohuang    | NULL |
+----+-------------+------+
12 rows in set (0.00 sec)
mysql>

mysql差异备份恢复

模拟误删数据

mysql> drop database huangtianen;
Query OK, 3 rows affected, 2 warnings (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@controller ~]#

恢复数据

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@controller ~]# 
[root@controller ~]# ls
all-202309051036.sql  all-2023-9-5.sql  anaconda-ks.cfg  table-2023-9-5.sql  huangtianen.sql
[root@controller ~]# 
[root@controller ~]# mysql -uroot -huangtianen < all-202309051036.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]# 
[root@controller ~]# mysql -uroot -huangtianen -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| huangtianen        |
+--------------------+
[root@controller ~]# 
[root@controller ~]# mysql -uroot -huangtianen -e 'show tables from huangtianen;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------------+
| Tables_in_huangtianen |
+-----------------------+
| student1              |
| student2              |
| student3              |
+-----------------------+
[root@controller ~]#

恢复差异备份

检查误删数据库的位置在什么地方

[root@controller data]# ls
auto.cnf    client-cert.pem  ib_buffer_pool  ib_logfile1  mysql_bin.000005  mysql.pid           public_key.pem   sys
ca-key.pem  client-key.pem   ibdata1         ibtmp1       mysql_bin.000006  performance_schema  server-cert.pem  huangtianen
ca.pem      controller.err   ib_logfile0     mysql        mysql_bin.index   private_key.pem     server-key.pem
[root@controller data]# 
[root@controller data]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
mysql> 
mysql> show binlog events in 'mysql_bin.000005';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000005 |   4 | Format_desc    |        10 |         123 | Server ver: 5.7.39-log, Binlog ver: 4 |
| mysql_bin.000005 | 123 | Previous_gtids |        10 |         154 |                                       |
| mysql_bin.000005 | 154 | Anonymous_Gtid |        10 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000005 | 219 | Query          |        10 |         293 | BEGIN                                 |
| mysql_bin.000005 | 293 | Table_map      |        10 |         350 | table_id: 141 (xingge.student1)       |
| mysql_bin.000005 | 350 | Write_rows     |        10 |         395 | table_id: 141 flags: STMT_END_F       |
| mysql_bin.000005 | 395 | Xid            |        10 |         426 | COMMIT /* xid=504 */                  |
| mysql_bin.000005 | 426 | Anonymous_Gtid |        10 |         491 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000005 | 491 | Query          |        10 |         565 | BEGIN                                 |
| mysql_bin.000005 | 565 | Table_map      |        10 |         622 | table_id: 141 (xingge.student1)       |
| mysql_bin.000005 | 622 | Update_rows    |        10 |         679 | table_id: 141 flags: STMT_END_F       |
| mysql_bin.000005 | 679 | Xid            |        10 |         710 | COMMIT /* xid=506 */                  |
| mysql_bin.000005 | 710 | Anonymous_Gtid |        10 |         775 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000005 | 775 | Query          |        10 |         873 | drop database xingge                  |
| mysql_bin.000005 | 873 | Rotate         |        10 |         920 | mysql_bin.000006;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
mysql>

使用mysqlbinlog恢复差异备份

[root@controller data]# mysqlbinlog --stop-position=775 /opt/data/mysql_bin.000005 |mysql -uroot -huangtianen     
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@controller data]# 
[root@controller data]# mysql -uroot -huangtianen -e 'select * from huangtianen.student1;' 
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+------+
| 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 | chenshou    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
| 12 | laohuang    | NULL |
+----+-------------+------+
[root@controller data]#