MySQL的备份和恢复

最新推荐文章于 2022-07-06 12:04:02 发布

置顶 bug--maker

最新推荐文章于 2022-07-06 12:04:02 发布

阅读量296

点赞数 1

分类专栏： Mysql 文章标签： Mysql基于LVM备份

本文链接：https://blog.csdn.net/qq_36294875/article/details/80285457

版权

Mysql 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

MySQL的备份和还原
备份的类型:
- 服务器是否在线:
  - 热备份:读写操作可以继续进行,不受影响;
  - 温备份:可以读,但是不能够执行写操作;
  - 冷备份:离线备份,读写操作均不可以进行;
- 是否需要导出文件:
  - 物理备份:复制数据文件,速度快,不需要借助于数据库服务器自身;
  - 逻辑文件:将数据导出到文本文件中,速度慢,需要MySQ服务器参与,降低服务器性能,但是可能降低浮点数精度;
  - 特点:可以使用文本处理工具进行二次处理;可移植性强, 可以跨MySQL服务器的版本;
- 是否包含所有内容:
  - 完全备份: 备份所有数据;
  - 增量备份: 仅仅备份上次完全备份或者上次增量备份以来发生变化的数据;
  - 差异备份: 仅仅备份上次完全备份以来变化的数据;
- 备份之后需要进行还原测试:否则有可能不能够进行还原,导致数据发生损坏;
- 这三种备份方式完全是独立的,可以相互结合使用;
通常需要备份的数据包括:数据,配置文件,备份二进制日志(即使点还原),事务日志;
热备份需要的技术复杂度是最高的,对于MyISAM来说实现热备份是不可能的,几乎热备份,使用快照来进行备份,但是可以使用温备份;
InnoDB:可以实现热备份,不影响当前事务的请求,热备份工具:xtrabackup(percona提供),MySQL提供的mysqldump也可以实现热备份;
备份的策略
- 完全+增量
- 完全+差异
备份工具
MySQL自带的备份工具:
- mysqlhotcopy:物理备份工具,需要执行FLUSH TABLES, LOCK TABLES操作,本质上是一个温备,甚至是冷备工具;
- mysqldump:逻辑备份工具,对于MyISAM(温) INnoDB(热备份工具);
文件系统备份工具:
- cp:冷备份,借助于逻辑卷,可以实现几乎热备份;
- lvm:逻辑卷的块照功能,用于实现几乎热备份;
- 需要注意以下的问题:
  - 1.需要首先进行刷新表mysql>FLUSH TABLES;,并且执行锁表操作mysql>LOCK TABLES;
  - 2.对于InnoDB:创建快照,而后需要确保缓冲区的内容同步到磁盘上面,而后才可以复制数据;这个过程,比想象中麻烦:首先是事务执行过程中的日志,需要在磁盘上面进行重放,其次是还有可能都是已经提交的事务正在从事务向内存中同步;所以必须确定缓冲区的内容都已经同步到磁盘上面;
  - 3.MyISAM: 执行FLUSH TABLES;之后数据就会立即写入磁盘;
商业工具:
- ibbackup:商业备份工具,价格昂贵;
- xtrabackup :开源工具免费;
- autoMySQLBackup: 这个可以及时试试https://www.ytyzx.org/index.php/%E4%BD%BF%E7%94%A8AutoMySQLBackup%E5%B7%A5%E5%85%B7%E8%87%AA%E5%8A%A8%E5%A4%87%E4%BB%BDMySQL%E6%95%B0%E6%8D%AE%E5%BA%93
- mk-parallel-backup perl脚本成为percona tools:支持所有的存储引擎,支持温备,备份速度中等;
- SELECT INTO OUTFILE:仅仅备份数据,不备份表结构,并且备份成为文本文件,支持所有的存储引擎,支持温备,备份速度中等,还原速度中等,可控性强;
各个备份工具的比较
使用mysqldump对InnoDB进行温备,对MyISAM进行热备
MysqlDump是逻辑备份,可以使用mysqldump(完全备份)+二进制日志,也可以使用完全+增量的方式进行备份;
Mysqldump的使用方式 :
- db_name [t1] [tb2]:这种备份并不包含创建数据库的命令,所以需要手动创建数据库,将表中的数据批量备份为插入的SQL语句;所以进行还原时,需要手动创建数据库;
  备份的过程:
备份单张表:
上面是属于逻辑备份,服务器在线,这种备份方式是不正确的会导致时间点的不一致,是应该进行锁表操作的;
- --master-data=n:表示是否记录二进制日志的位置
  - 0:表示不记录二进制日志文件以及事件位置;
  - 1:表示使用CHANGE MASTER TO的方式执行记录,并且可以用于直接启动从服务器;
  - 2:以CHANGE MASTER TO 的放置记录位置,但是默认为注释的;
- --lock-alltables :表示备份之前锁定所有的表;
- --flush-logs:表示在锁表之后执行日志滚动,如果指定表中的表类型都是InnoDB,可以使用--single-transaction用于启动热备份,这个选项可以自动完成锁表,释放锁等操作,所以不要和上面的选项一块使用;
- --all-databases:表示备份所有库;
- --databases DB_NAME,DB_NAME,..:表示备份指定库可以自动创建库,不需手动创建库;
- --events:表示用于备份事件的;
- --routines:表示备份簇出过程和存储函数的;
- --triggers:表示备份触发器的;
Mysqldump对于MyISAM只能够执行温备份;
- --lock-all-tables; 以及选项--lock-tables;
InnoDB :热备份
- --single-transaction:会启动一个大事务来进行一致性备份,建议对于InnoDB执行热备文件;
首先应该执行的是锁表的操作:

mysql> FLUSH TABLES WITH READ LOCK; //这里施加的是读锁;
Query OK, 0 rows affected (0.00 sec)

同时为了防止在锁表的过程中存在插入数据的操作,并且为了方便定义二进制日志的位置,还需要执行的是刷新日志的操作

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000003 |       330 |
| mysql-bin.000004 |       456 |
| mysql-bin.000005 |       126 |
| mysql-bin.000006 |      2813 |
| mysql-bin.000007 |      7627 |
| mysql-bin.000008 |      1538 |
| mysql-bin.000009 |      2948 |
| mysql-bin.000010 |       390 |
| mysql-bin.000011 |       107 |
| mysql-bin.000012 |       107 |
| mysql-bin.000013 |      5872 |
| mysql-bin.000014 |       107 |
+------------------+-----------+

并且需要重新打开一个中断进行备份操作,因为如果退出当前终端,施加的锁,就会自动释放;

[root@server60 ~]# mysqldump -uroot -p --master-data=2[以CHANGE MASTER TO 的放置记录位置,但是默认为注释的;] studb > /root/studb`date +%F-%H-%S`.sql
Enter password: 
[root@server60 man]# ll /mnt/jiaowu.sql
-rw-r--r-- 1 root root 6649 05-11 21:36 /mnt/jiaowu.sql

在释放读锁之前的数据操作会直接显示错误

mysql> INSERT INTO courses (Cname) values('xixiao');
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock  //数据可以进行查询,但是插入数据会出错;

然后进行读锁的释放操作

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

查看这个文件的内容:

-- MySQL dump 10.13  Distrib 5.5.12, for Linux (x86_64)
--
-- Host: localhost    Database: jiaowu
-- ------------------------------------------------------
-- Server version       5.5.12-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000014', MASTER_LOG_POS=107;
//上面最后一行明确说明了当前使用的二进制日志的文件,以及位置;
.....
.....

在进行数据库还原时,必须进行创建数据库的操作;
还原的过程
首先删除数据库jiaowu

mysql> DROP DATABASE jiaowu;
Query OK, 5 rows affected (0.33 sec)

如果不创建数据库直接进行还原,就会出现下面的错误

[root@server60 man]# mysql < /mnt/jiaowu.sql
ERROR 1046 (3D000) at line 22: No database selected

所以需要首先创建数据库

mysql> CREATE DATABASE jiaowu;
Query OK, 1 row affected (0.09 sec)

然后执行备份脚本:

[root@server60 man]# mysql jiaowu < /mnt/jiaowu.sql

连接上去查看内容:

mysql> show tables;
+------------------+
| Tables_in_jiaowu |
+------------------+
| courses          |
| scores           |
| sct              |
| students         |
| tutors           |
+------------------+
5 rows in set (0.00 sec)

使用完全备份+每日增量
完全备份:mysqldump
增量备份:备份二进制日志文件
通过上述机制来实现即时点还原;
这里通过指定选项的方式来执行刷新日志,锁定表的过程

[root@server60 ~]# mysqldump -uroot -p --master-data=2 --flush-logs --all-databases --lock-all-tables > ~/alldatabases.sql
Enter password:

这里可以根据实际删除不需要的一些日志

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=107;

删除mysql-bin.000015之前的日志文件

mysql> PURGE BINARY LOGS TO 'mysql-bin.000015';
Query OK, 0 rows affected (0.64 sec)

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000015 |       107 |
+------------------+-----------+
1 row in set (0.00 sec)

这些二进制日志文件需要保存在合适的位置之后,然后删除不需要的文件;
连接数据库进行操作,也就是需要自动的更改一些文件

mysql> use jiaowu;
Database changed
mysql> SELECT * FROM tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|  11 | HuFei        | M      |   31 |
|  12 | ZhangWuji    | M      |   10 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   22 |
|  15 | jerry        | M      |   50 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)

删除年龄大于80的行

mysql> DELETE FROM tutors WHERE Age>80;
Query OK, 2 rows affected (0.04 sec)
删除之后的数据:
mysql> SELECT * FROM tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|  11 | HuFei        | M      |   31 |
|  12 | ZhangWuji    | M      |   10 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   22 |
|  15 | jerry        | M      |   50 |
+-----+--------------+--------+------+
10 rows in set (0.00 sec)

对于上面产生的二进制日志进行增量备份,首先滚动日志

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.03 sec)

找到增量备份文件

[root@server60 mydata]# cp mysql-bin.000015 ~/
[root@server60 mydata]# mysqlbinlog ~/mysql-bin.000015 > /root/mon-incremental.sql

接下来模拟第二天的数据改变,插入一些数据

mysql> INSERT INTO tutors (Tname) Values('atu123');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|  11 | HuFei        | M      |   31 |
|  12 | ZhangWuji    | M      |   10 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   22 |
|  15 | jerry        | M      |   50 |
|  16 | atu123       | M      | NULL |
+-----+--------------+--------+------+
11 rows in set (0.00 sec)

模拟发生故障,数据硬盘损坏,但是二进制日志文件正常,所以首先将二进制日志文件拷贝走

[root@server60 mydata]# cp mysql-bin.000016 ~/
[root@server60 mydata]# rm -fr ./*

这时服务是无法正常停止的

[root@server60 mydata]# service mysqld stop 
MySQL server PID file could not be found!                  [FAILED]

删除所有关于mysqld的进程

[root@server60 man]# killall mysqld

首先进行数据库的初始化

[root@server60 mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/mysql/mydata
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h server60.com password 'new-password'

Alternatively you can run:
./bin/mysql_secure_installation

接下来启动Mysqld服务

[root@server60 mysql]# service mysqld start
Starting MySQL..                                           [  OK  ]

首先使用完全备份进行还原

[root@server60 mysql]# mysql -uroot -p <  ~/alldatabases.sql 
Enter password:  //因为i进行了初始化,默认密码为空,所以这里需要指定用户和密码,而不是使用之前的密码文件;

接下来使用增量备份进行还原
第一次增量备份之前的数据

mysql> SELECT * FROM tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|  11 | HuFei        | M      |   31 |
|  12 | ZhangWuji    | M      |   10 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   22 |
|  15 | jerry        | M      |   50 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)

执行第一次增量备份,就会发现年龄大于80的就会消失了

[root@server60 mysql]# mysql -uroot -p < ~/mon-incremental.sql 
Enter password:

连接数据库查看,内容是否存在

mysql> SELECT * FROM tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|  11 | HuFei        | M      |   31 |
|  12 | ZhangWuji    | M      |   10 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   22 |
|  15 | jerry        | M      |   50 |
+-----+--------------+--------+------+
10 rows in set (0.00 sec)

年龄大于80的已经被删除,接下来继续还原增量备份

[root@server60 mysql]# mysqlbinlog ~/mysql-bin.000016 > tmp.sql
[root@server60 mysql]# mysql -uroot -p < tmp.sql
Enter password:

连接Mysql查看数据恢复情况,新添加的用户已经恢复;

mysql> SELECT * FROM tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|  11 | HuFei        | M      |   31 |
|  12 | ZhangWuji    | M      |   10 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   22 |
|  15 | jerry        | M      |   50 |
|  16 | atu123       | M      | NULL |
+-----+--------------+--------+------+
11 rows in set (0.00 sec)

在进行数据还原的过程中,由于数据还原的这些操作涉及数据库内容的改变,所以二进制日志是会记录这些内容的,所以应该关闭二进制日志信息,否则会降低性能;

mysql> SET sql_log_bin=0; //修改会话变量
Query OK, 0 rows affected (0.00 sec)

在服务上线之前,手动的恢复二进制日志的功能
恢复SQL状态,但是需要关闭二进制日志

关闭MySQL的二进制日志功能,并且执行数据的完全备份导入
* 首先关闭二进制日志

mysql> SET sql_log_bin=0;

然后导入jiaowu.sql,之后重新打开二进制日志记录

mysql> SET sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

文本备份工具
SELECT INTO OUTFILE

mysql> SELECT * INTO OUTFILE '/tmp/tutors.txt' from tutors; //目录需要有访问权限;
    Query OK, 9 rows affected (0.00 sec)

查看备份的文件

[root@server60 mysql]# cat /tmp/tutors.txt
1   HongQigong  M   93
2   HuangYaoshi M   63
3   Miejueshitai    F   72
4   OuYangfeng  M   76
5   YiDeng  M   90
6   YuCanghai   M   56
7   Jinlunfawang    M   67
8   HuYidao M   42
9   NingZhongze F   49

直接是表中的数据,可以最大空间的节省空间,恢复时,首先需要创建表的格式

mysql> CREATE TABLE tutor LIKE tutors;

mysql> LOAD DATA INFILE '/tmp/tutors.txt' INTO TABLE tutor;
Query OK, 9 rows affected (0.97 sec)
Records: 9  Deleted: 0  Skipped: 0  Warnings: 0

这里写图片描述
* 备份比较块,但是管理麻烦;比较适合用来进行单张表的备份,不用来进行完全备份;

mysql> SELECT * INTO OUTFILE '/tmp/age.txt' from tutor WHERE Age > 20;
Query OK, 9 rows affected (0.00 sec)

进行还原的过程

mysql> LOAD DATA INFILE '/tmp/tutors.txt' INTO TABLE tutor;
Query OK, 9 rows affected (0.97 sec)
Records: 9  Deleted: 0  Skipped: 0  Warnings: 0

对于上述恢复数据的过程,因为引起了数据改变,所以肯定会记录到二进制日志里面;
这个过程会被记录进入二进制日志里面,因为发生了数据的更改,但是没有SQL语句;
基于LVM快照来实现数据库备份
用于实现接近于热备份的逻辑备份:
1.数据文件必须在逻辑卷上面;
2.次逻辑卷所在的卷组必须有足够的空间使用快照卷,如果超出快照卷的大小,就会导致数据库崩溃;
3.如果需要基于快照卷进行备份,那么事务日志(InnoDB存储引擎)必须和数据文件在同一个卷上面;
- 如果不再那么对于两个卷[事务日志和数据文件]独立进行快照的时间点,有可能是不匹配的,这回导致数据差异;
- 在执行快照的过程中,事务日志,很有可能还在将数据同步到数据文件中,再快照执行完成之后,MySQL服务器会进行自动将事务日志数据同步到数据文件中这个过程类似于掉电崩溃,Mysql服务器需要进行自动修修复;
4.如果二进制日志文件的事件跨文件了,那么就使用时间进行限定;
5.基于LVM进行了逻辑备份,但是对于数据还原的过程,仍然依赖于二进制日志,二进制日志的安全性十分重要;
会话1首先启动事务,并且插入数据

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tutors(Tname) VALUES('0001');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tutors(Tname) VALUES('0002');
施加读锁之后阻塞;

会话2施加读锁

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.04 sec)

保存刷新日志之后的日志的位置

[root@server60 ~]# mysql -e 'SHOW MASTER STATUS;\G' > ~/backup/master.`date +%F`.info
[root@server60 ~]# cat ~/backup/master.`date +%F`.info
File    Position    Binlog_Do_DB    Binlog_Ignore_DB
mysql-bin.000005    107     
[root@server60 ~]#

然后创建LVM逻辑卷

[root@server60 ~]# lvcreate -L 50M -s -p r -n mydata-snap /dev/mysqlvg/mysqldate 
  Rounding up size to full physical extent 52.00 MB
  Logical volume "mydata-snap" created

在创建块照卷之后,会话2可以释放锁

mysql> UNLOCK TABLE;
Query OK, 0 rows affected (0.01 sec)

会话1的事务插入操作执行成功

mysql> INSERT INTO tutors(Tname) VALUES('0002');
Query OK, 1 row affected (4 min 25.55 sec)

会话1提交事务,二进制日志信息会同步更新

mysql> COMMIT;
Query OK, 0 rows affected (0.08 sec)

查看日志信息已经更新

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |      470 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

新插入的行在日志的备份文件里面肯定是没有的,这些信息记录在二进制日志文件里面;
- 然后挂载块照卷

[root@server60 mnt]# mkdir ~/backup/full-backup-`date +%F`
[root@server60 mnt]# cp -a ./* ~/backup/full-backup-2018-05-12/
[root@server60 /]# umount /mnt/

块照卷可以删除

[root@server60 /]# lvremove --force /dev/mysqlvg/mydata-snap 
  Logical volume "mydata-snap" successfully removed

删除不需要的文件,通过现在这个文件来恢复,是不包含事务执行时,新添加的文件的;

[root@server60 full-backup-2018-05-12]# rm -f mysql-bin.*

通过刷新日志,并且插入数据,可能形成新的日志文件,可能需要对于这些日志文件进行新的备份

mysql> USE jiaowu;
Database changed
mysql> INSERT INTO tutors(Tname) VALUES('stu0003');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO tutors(Tname) VALUES('stu0004');
Query OK, 1 row affected (0.00 sec)

这里写图片描述

备份数据目录里面的日志文件,这个日志文件可能是存在多个,如果需要结合多个二进制日志文件,就需要指定事件开始的时间

这里写图片描述

[root@server60 ~]# mysqlbinlog --start-datetime='2018-05-12 21:41:24' /mysql/mydata/mysql-bin.000005 /mysql/mydata/mysql-bin.000006

可以看到插入数据的信息也已经保存
保存上面的信息为二进制日志文件信息

[root@server60 ~]# mysqlbinlog --start-datetime='2018-05-12 21:41:24' /mysql/mydata/mysql-bin.000005 /mysql/mydata/mysql-bin.000006  > ~/backup/incremnet-`date +%F-%H-%M-%S`.info.sql

模拟数据库损坏,数据丢失

[root@server60 ~]# rm -fr /mysql/mydata/*

停止MySQL服务器

[root@server60 ~]# /etc/init.d/mysqld stop 
Shutting down MySQL....                                    [  OK  ]

这里是不需要初始化数据库的,因为里面的内容块照卷里面都是存在的

[root@server60 ~]# cp -a ~/backup/full-backup-2018-05-12/* /mysql/mydata/

现在启动MySQL服务器

root@server60 ~]# /etc/init.d/mysqld start
Starting MySQL..                                           [  OK  ]

在服务器启动的过程中,会自动创建新二进制日志索引文件以及二进制日志文件;
连接MySQL查看数据信息,这里不包含事务提交的信息,以及之后的二进制日志文件记录的信息;
还原二进制日志记录的内容,首先,停止MySQL的二进制日志文件的记录

mysql> source ~/backup/incremnet-2018-05-12-22-26-31.info.sql

查看之后的内容更改情况,这里包含了事务日志,以及事务提交之后的内容
还原二进制日志的记录过程

mysql> SET sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

二进制日志记录的位置并没有发生改变
在进行数据备份的过程中,需要保证缓冲区里面没有了写操作才可以进行写操作
查看存储器状态的命令

mysql> show innodb status\G;
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
180808 17:53:57 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 5, signal count 5
Mutex spin waits 0, rounds 40, OS waits 2
RW-shared spins 6, OS waits 3; RW-excl spins 1, OS waits 0
------------
TRANSACTIONS
------------
Trx id counter 0 1797
Purge done for trx's n:o < 0 0 undo n:o < 0 0
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 2189, OS thread id 139906687706880
MySQL thread id 9, query id 122 localhost root
show innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
29 OS file reads, 7 OS file writes, 7 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 17393, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 55199
Log flushed up to   0 55199
Last checkpoint at  0 55199
0 pending log writes, 0 pending chkp writes
10 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 20360776; in additional pool allocated 737536
Dictionary memory allocated 49000
Buffer pool size   512
Free buffers       489
Database pages     23
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 23, created 0, written 1
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 2189, id 139906540078848, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 53
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.00 sec)

在实际的备份过程中,通过事务结合事务的隔离级别,在备份的过程中启动一个大的事务,可以保证热备数据的一致性;

bug--maker

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL的备份和恢复

MySQL的备份和还原备份的类型: 服务器是否在线: 热备份:读写操作可以继续进行,不受影响;温备份:可以读,但是不能够执行写操作;冷备份:离线备份,读写操作均不可以进行;是否需要导出文件: 物理备份:复制数据文件,速度快,不需要借助于数据库服务器自身;逻辑文件:将数据导出到文本文件中,速度慢,需要MySQL服务器参与,降低服务器性能,但是可能降低浮点数精度; 特点:可以使用文...
复制链接

扫一扫