使用mysqldump对数据库备份

er2121e

mysqldump是一个简单而有效的数据库备份程序，它会将需要备份的数据库的结构转换为mysql语言组成的一个文本文件，这个文本文件包含了创建这个数据库所进行的所有mysql语句，所以当我们恢复时，便是程序读取这些语句重新创建数据库、创建表以及插入数据的过程，但因为其是单线程的，所以对于比较大的数据库操作会比较慢。mysqldump支持InnoDB热备，MyISAM的温备，可以基于二进制日志进行增量备份，根据二进制文件文件中记录的操作点进行有选择的恢复。

mysqldump命令介绍
语法：
        mysqldump [OPTIONS] database [tables]：备份单个库，或库指定的一个或多个表
        mysqldump [OPTIONS] –databases [OPTIONS] DB1 [DB2 DB3…]：备份一个或多个库
        mysqldump [OPTIONS] –all-databases [OPTIONS]：备份所有库
其他选项：
        -x, –lock-all-tables：锁定所有表
         -l, –lock-tables：锁定备份的表，这两个选项可用于MyISAM和InnoDB的温备
        –single-transaction：启动一个大的单一事务实现备份，实现对InnoDB的热备

        –flush-logs：备份前，请求到锁之后滚动日志，可用于实现备份后二进制日志写入到新的日志文件，便于进行二进制日志备份时寻找恢复点

        –master-data=[0|1|2]:用于确定是否在备份文件中添加CHANGE MASTER语句，0：不记录；1：记录为CHANGE MASTER语句；2：记录为注释的CHANGE MASTER语句；CHANGE MASTER语句记录了备份的操作记录处于哪个日志文件的哪个位置上，加上此选项备份时将打开–lock-all-tables选项，备份完成会自动关闭–lock-all-tables选项。
mysqldump备份以及恢复步骤
请求锁：可使用–lock-all-tables,如果使用了–master-data=1|2，则不须指定此项，如果为InnoDB则使用–single-transaction选项实现热备
滚动日志：可使用选项–flush-logs
实验过程：
修改mariadb的配置文件，将日志文件单独存放在日志目录

创建所需要的目录
mkdir /mydata/binlog[iyunv@node1 ]# chown -R mysql.mysql /mydata/mkdir /backup导入实验用数据库
[iyunv@node1 ~]# mysql  < /tem/hellodb.sql


MariaDB [(none)]> USE hellodb;Database changedMariaDB [hellodb]> SHOW TABLES;+-------------------+| Tables_in_hellodb |+-------------------+| classes           || coc               || courses           || scores            || students          || teachers          || toc               |+-------------------+7 rows in set (0.00 sec)备份数据库
[iyunv@node1 ~]# mysqldump --database hellodb --flush-logs --master-data=2 > /backup/backup1.sql备份完之后我们在对数据进行操作

• MariaDB [hellodb]> CREATE TABLE city (id int auto_increment not null primary key,code varchar(30));
  
• Query OK, 0 rows affected (0.21 sec)
  
• 
  
• MariaDB [hellodb]> SHOW TABLES;
  
• +-------------------+
  
• | Tables_in_hellodb |
  
• +-------------------+
  
• | city              |
  
• | classes           |
  
• | coc               |
  
• | courses           |
  
• | scores            |
  
• | students          |
  
• | teachers          |
  
• | toc               |
  
• +-------------------+
  
• 8 rows in set (0.01 sec)
  
  

新的表已经添加到数据库中，我们模拟删除hellodb数据库

• MariaDB [hellodb]> DROP DATABASE hellodb;
  
• Query OK, 8 rows affected (0.20 sec)
  
• 
  
• MariaDB [(none)]> SHOW DATABASES;
  
• +--------------------+
  
• | Database           |
  
• +--------------------+
  
• | information_schema |
  
• | mysql              |
  
• | performance_schema |
  
• | test               |
  
• +--------------------+
  
• 4 rows in set (0.01 sec)
  
  

        如果现在就恢复我们备份的文件是不能恢复备份之后进行的操作的，所以现在我们需要二进制文件里进行定点恢复，为了确定恢复点，首先导出全部的二进制文件，这里所使用的二进制文件是在我们备份之后产生的，因为我们备份是使用的–flush-logs，所以日志文件在备份之后重新生成了一个
[iyunv@node1 binlog]# mysqlbinlog mysql-bin.000003 > /backup/bin3.sql[iyunv@node1 binlog]# vim /backup/bin3.sql
由此可以看出我们删除数据库是在586这个点，所以我们就可以以586作为导出二进制文件的停止点
[iyunv@node1 binlog]# mysqlbinlog --stop-position=586 /mydata/binlog/mysql-bin.000003 > /backup/bin586.sql现在我们开始恢复数据库
[iyunv@node1 ~]# mysql < /backup/backup1.sql查看数据库
MariaDB [(none)]> USE hellodb;Database changedMariaDB [hellodb]> SHOW TABLES;+-------------------+| Tables_in_hellodb |+-------------------+| classes           || coc               || courses           || scores            || students          || teachers          || toc               |+-------------------+7 rows in set (0.01 sec)此时并没有我们创建的city表
下面就可以使用二进制文件进行恢复操作了
[iyunv@node1 ~]# mysql < /backup/bin586.sql再来看一下，表city已经回来了

• MariaDB [(none)]> USE hellodb;
  
• Database changed
  
• MariaDB [hellodb]> SHOW DATABASES;
  
• +--------------------+
  
• | Database           |
  
• +--------------------+
  
• | hellodb            |
  
• | information_schema |
  
• | mysql              |
  
• | performance_schema |
  
• | test               |
  
• +--------------------+
  
• 5 rows in set (0.00 sec)
  
• 
  
• MariaDB [hellodb]> SHOW TABLES;
  
• +-------------------+
  
• | Tables_in_hellodb |
  
• +-------------------+
  
• | city              |
  
• | classes           |
  
• | coc               |
  
• | courses           |
  
• | scores            |
  
• | students          |
  
• | teachers          |
  
• | toc               |
  
• +-------------------+
  
• 8 rows in set (0.01 sec)
  
  

这样我们就完成了一次数据误删除并根据备份以及二进制文件恢复的过程，从这里可以看出二进制文件是很重要的，所以需要将二进制文件单独放在一个存储的地方，尽量不要和数据库放在一个磁盘中，当然备份的数据也要专门存放，这样数据库出现意外或者数据库磁盘损坏，就可以通过备份和二进制文件恢复了。