MySQL的备份和恢复

缺乏、安全感 2022-05-25 09:28 221阅读 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][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`:仅仅备份数据,不备份表结构,并且备份成为文本文件,支持所有的存储引擎,支持温备,备份速度中等,还原速度中等,可控性强;
 *  **各个备份工具的比较**  
    ![这里写图片描述][70]
 *  **使用mysqldump对InnoDB进行温备,对MyISAM进行热备**
 *  `MysqlDump`是逻辑备份,可以使用`mysqldump(完全备份)`\+二进制日志,也可以使用完全+增量的方式进行备份;
 *  `Mysqldump`的使用方式 :  
    
    
     *  `db_name [t1] [tb2]`:这种备份并不包含创建数据库的命令,所以需要手动创建数据库,将表中的数据批量备份为插入的`SQL`语句;所以进行还原时,需要手动创建数据库;  
        备份的过程:  
        ![这里写图片描述][70 1]
 *  备份单张表:  
    ![这里写图片描述][70 2]
 *  上面是属于逻辑备份,服务器在线,这种备份方式是不正确的会导致时间点的不一致,是应该进行锁表操作的;
    
     *  `--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

*  连接上去查看内容:

*  **使用完全备份+每日增量** 
 *  完全备份:`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> 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

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

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)

*  查看日志信息已经更新

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

[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)

![这里写图片描述][70 4]

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

![这里写图片描述][70 5]

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

*  可以看到插入数据的信息也已经保存  
    ![这里写图片描述][70 6]
 *  保存上面的信息为二进制日志文件信息

[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  ]

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

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

*  查看之后的内容更改情况,这里包含了事务日志,以及事务提交之后的内容  
    ![这里写图片描述][70 9]
 *  还原二进制日志的记录过程

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

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

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)

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

[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]: 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
[70]: /images/20220525/6b0a2b07c2f847ddb1f4cfc1ad133fa3.png
[70 1]: /images/20220525/f3af04217d9a4df79e3532f4eca5d9eb.png
[70 2]: /images/20220525/64c670568e5b4cc1871e3b002fcea5cd.png
[70 3]: /images/20220525/b722c41283f14540b2e1a5e860b03e60.png
[70 4]: /images/20220525/93fcdfa3a4b448e180094a772c6182b2.png
[70 5]: /images/20220525/006d4bcf01a84cd5a746d667c06a3e45.png
[70 6]: /images/20220525/be9a1f1cfe7b421bb4e9d5f382d08a87.png
[70 7]: /images/20220525/42ca837c0b97492dbc2c528746e366de.png
[70 8]: /images/20220525/2e86f0d3013747dbaf2b88b45939e7c0.png
[70 9]: /images/20220525/f774be4dc9884a3a91ed36165be1876c.png
[70 10]: /images/20220525/c4233098425b4a36939270c6d63b04f8.png