mysql数据库备份与恢复

最新推荐文章于 2024-03-26 14:45:28 发布

Mingzi540

最新推荐文章于 2024-03-26 14:45:28 发布

阅读量274

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/Mingzi540/article/details/125546280

版权

1.数据库备份方案：

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

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

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> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+


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

//此时mysql库有一个数据库＋2张表格

备份整个数据库

[root@mingzi ~]# mysqldump -uroot -pmingzi540.. --all-databases > all-$(date '+%Y%m%d%H%M%S').sql
//后面备注年月日时间

[root@mingzi ~]# vim all-20220630190213.sql

-- Current Database: `mysql`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `mysql`;

--
-- Table structure for table `columns_priv`
--

DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `columns_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
//使用vim可以查看备份文件的结构

备份school库里面的其中一张表

[root@mingzi ~]# mysqldump -uroot -pmingzi540.. school tb_course > tb_course$(date '+%Y%m%d%H%M%S').sql

//备份表格

[root@mingzi ~]# ls
all-20220630190213.sql  anaconda-ks.cfg  tb_course20220630190650.sql

只备份school这个数据库

[root@mingzi ~]# mysqldump -uroot -pmingzi540.. --databases school > school$(date '+%Y%m%d%H%M%S').sql

[root@mingzi ~]# ls
all-20220630190213.sql  anaconda-ks.cfg  school20220630191251.sql  tb_course20220630190650.sql

mysql数据恢复

误删数据库，用备份文件恢复

mysql> drop database school;
Query OK, 2 rows affected (0.02 sec)


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)
//删掉

恢复整个数据库

[root@mingzi ~]# mysql -uroot -pmingzi540.. < all-20220630190213.sql

//选定备份的整个数据库文件

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

恢复数据库中的某一张表格

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


先删除一张表格

接下来进行恢复

[root@mingzi ~]# mysql -uroot -pmingzi540.. school < tb_course20220630190650.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.01 sec)

多表删除进行恢复

一个数据库包含多张表格统一删除进行统一恢复我们选择备份的哪一个库进行恢复

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



//此时school库中没有表格

进行恢复

[root@mingzi ~]# ls
all-20220630190213.sql  anaconda-ks.cfg  school20220630191251.sql  tb_course20220630190650.sql
[root@mingzi ~]# mysql -uroot -pmingzi540.. school < school20220630191251.sql
mysql: [Warning] Using a password on the command line interface can be insecure.


//选择school库进行恢复


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

mysql差异备份

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

[root@mingzi ~]# vim /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=1
log-bin=mysql_bin

//加上  server-id=1
        log-bin=mysql_bin


[root@mingzi ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
//重启一下

差异备份所存放的位置

[root@mingzi data]# ll
total 123000
-rw-r-----. 1 mysql mysql       56 Jun 28 14:04 auto.cnf
-rw-------. 1 mysql mysql     1676 Jun 28 14:04 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jun 28 14:04 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jun 28 14:04 client-cert.pem
-rw-------. 1 mysql mysql     1680 Jun 28 14:04 client-key.pem
-rw-r-----  1 mysql mysql      808 Jun 30 19:29 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jun 30 19:30 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jun 30 19:30 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jun 28 14:04 ib_logfile1
-rw-r-----  1 mysql mysql 12582912 Jun 30 19:30 ibtmp1
-rw-r-----. 1 mysql mysql    40686 Jun 30 19:30 mingzi.err
drwxr-x---. 2 mysql mysql     4096 Jun 30 19:17 mysql
-rw-r-----  1 mysql mysql      154 Jun 30 19:30 mysql_bin.000001
-rw-r-----  1 mysql mysql       19 Jun 30 19:30 mysql_bin.index
-rw-r-----  1 mysql mysql        5 Jun 30 19:30 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Jun 28 14:04 performance_schema
-rw-------. 1 mysql mysql     1676 Jun 28 14:04 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jun 28 14:04 public_key.pem
drwxr-x---  2 mysql mysql      118 Jun 30 19:25 school
-rw-r--r--. 1 mysql mysql     1112 Jun 28 14:04 server-cert.pem
-rw-------. 1 mysql mysql     1680 Jun 28 14:04 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jun 28 14:04 sys

//重启之后会多出我们所添加的文件
-rw-r-----  1 mysql mysql      154 Jun 30 19:30 mysql_bin.000001
-rw-r-----  1 mysql mysql       19 Jun 30 19:30 mysql_bin.index

差异备份完全备份

[root@mingzi ~]# mysqldump -uroot -pmingzi540.. --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@mingzi ~]# ls
all-20220630190213.sql  anaconda-ks.cfg           tb_course20220630190650.sql
all-20220630194357.sql  school20220630191251.sql


//all-20220630194357.sql  用时间来区别差异备份的文件


[root@mingzi ~]# ll
total 1732
-rw-r--r--  1 root root 877661 Jun 30 19:02 all-20220630190213.sql
-rw-r--r--  1 root root 877813 Jun 30 19:43 all-20220630194357.sql
-rw-------. 1 root root   1092 May 23 13:13 anaconda-ks.cfg
-rw-r--r--  1 root root   3240 Jun 30 19:12 school20220630191251.sql
-rw-r--r--  1 root root   1981 Jun 30 19:06 tb_course20220630190650.sql
//或者用大小来区分


[root@mingzi ~]# vimdiff all-20220630190213.sql all-20220630194357.sql
  --                                               |  --
                                                   |  
  CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` |  CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql`
                                                   |  
  USE `mysql`;                                     |  USE `mysql`;
+ +--309 lines: -----------------------------------|+ +--309 lines: -----------------------------------
  --                                               |  --
  -- Dumping data for table `innodb_index_stats`   |  -- Dumping data for table `innodb_index_stats`
  --                                               |  --
                                                   |  
  LOCK TABLES `innodb_index_stats` WRITE;          |  LOCK TABLES `innodb_index_stats` WRITE;
  /*!40000 ALTER TABLE `innodb_index_stats` DISABLE|  /*!40000 ALTER TABLE `innodb_index_stats` DISABLE
  INSERT INTO `innodb_index_stats` VALUES ('mysql',|  INSERT INTO `innodb_index_stats` VALUES ('mysql',
  /*!40000 ALTER TABLE `innodb_index_stats` ENABLE |  /*!40000 ALTER TABLE `innodb_index_stats` ENABLE 
  UNLOCK TABLES;                                   |  UNLOCK TABLES;
                                                   |  
all-20220630190213.sql           32,1            2% all-20220630194357.sql           31,0-1          3%
"all-20220630194357.sql" 1005L, 877813C
//也可以vimdiff

现在我们新增表里的内容模拟误删操作来恢复

mysql> create table info(id int not null primary key auto_increment,name varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert info(name) values('xiaoxi'),('xinxin'),('Axin');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0


//创建一张表格
mysql> select * from info;
+----+--------+
| id | name   |
+----+--------+
|  1 | xiaoxi |
|  2 | xinxin |
|  3 | Axin   |
+----+--------+
3 rows in set (0.00 sec)


mysql> update tb_students_info set age = 17 where id = 7;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tb_students_info;
+----+--------+------+------+--------+-----------+
| 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   |   17 | M    |    163 |         4 |
|  8 | Susan  |   23 | M    |    170 |         5 |
|  9 | Thomas |   22 | F    |    178 |         5 |
| 10 | Tom    |   23 | F    |    165 |         5 |
| 11 | LiMing |   22 | F    |    180 |         7 |
+----+--------+------+------+--------+-----------+
11 rows in set (0.00 sec)

//把lily的年龄改成17岁

mysql差异备份恢复

模拟误删数据

mysql> drop database school;
Query OK, 3 rows affected (0.01 sec)

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

mysql> 


//删除school库

下面进行恢复

[root@mingzi data]# ll
-rw-r-----  1 mysql mysql     1147 Jun 30 19:54 mysql_bin.000002
-rw-r-----  1 mysql mysql       19 Jun 30 19:43 mysql_bin.index

//此时可以看到我们二进制日志文件变动成000002  这里面保存这刚才对数据库的一系列操作，包括删除的操作


刷新创建新的二进制日志
[root@mingzi ~]# mysqladmin -uroot -pmingzi540.. flush-logs

-rw-r-----  1 mysql mysql     1194 Jun 30 20:01 mysql_bin.000002
-rw-r-----  1 mysql mysql      154 Jun 30 20:01 mysql_bin.000003
//此时在观察这里的文件变成00003和00002

恢复完全备份

[root@mingzi ~]# mysql -uroot -pmingzi540.. < all-20220630194357.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)
//可以看到恢复成功，但是只有2张表 新增的内容并没有

检查误操作在那个环节

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    |         1 |         123 | Server ver: 5.7.37-log, Binlog ver: 4                                                        |
| mysql_bin.000002 |  123 | Previous_gtids |         1 |         154 |                                                                                              |
| mysql_bin.000002 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                         |
| mysql_bin.000002 |  219 | Query          |         1 |         375 | use `school`; create table info(id int not null primary key auto_increment,name varchar(10)) |
| mysql_bin.000002 |  375 | Anonymous_Gtid |         1 |         440 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                         |
| mysql_bin.000002 |  440 | Query          |         1 |         514 | BEGIN                                                                                        |
| mysql_bin.000002 |  514 | Table_map      |         1 |         566 | table_id: 142 (school.info)                                                                  |
| mysql_bin.000002 |  566 | Write_rows     |         1 |         635 | table_id: 142 flags: STMT_END_F                                                              |
| mysql_bin.000002 |  635 | Xid            |         1 |         666 | COMMIT /* xid=480 */                                                                         |
| mysql_bin.000002 |  666 | Anonymous_Gtid |         1 |         731 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                         |
| mysql_bin.000002 |  731 | Query          |         1 |         805 | BEGIN                                                                                        |
| mysql_bin.000002 |  805 | Table_map      |         1 |         875 | table_id: 141 (school.tb_students_info)                                                      |
| mysql_bin.000002 |  875 | Update_rows    |         1 |         953 | table_id: 141 flags: STMT_END_F                                                              |
| mysql_bin.000002 |  953 | Xid            |         1 |         984 | COMMIT /* xid=484 */                                                                         |
| mysql_bin.000002 |  984 | Anonymous_Gtid |         1 |        1049 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                         |
| mysql_bin.000002 | 1049 | Query          |         1 |        1147 | drop database school                                                                         |
| mysql_bin.000002 | 1147 | Rotate         |         1 |        1194 | mysql_bin.000003;pos=4                                                                       |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------+
17 rows in set (0.00 sec)

找到后进行恢复

[root@mingzi ~]#  mysqlbinlog --stop-position=1049 /opt/data/mysql_bin.000002 |mysql -uroot -pmingzi540..


mysql> use school;
Database changed
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info             |
| tb_course        |
| tb_students_info |
+------------------+
3 rows in set (0.00 sec)
//新增的内容都在

mysql> select * from info;
+----+--------+
| id | name   |
+----+--------+
|  1 | xiaoxi |
|  2 | xinxin |
|  3 | Axin   |
+----+--------+
3 rows in set (0.00 sec)

Mingzi540

关注

0
点赞
踩
0

收藏

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

1.数据库备份方案：备份整个数据库备份school库里面的其中一张表只备份school这个数据库mysql数据恢复误删数据库，用备份文件恢复恢复整个数据库恢复数据库中的某一张表格接下来进行恢复多表删除进行恢复一个数据库包含多张表格统一删除进行统一恢复我们选择备份的哪一个库进行恢复进行恢复开启MySQL服务器的二进制日志功能差异备份所存放的位置差异备份完全备份现在我们新增表里的内容模拟误删操作来恢复 mysql
复制链接

扫一扫