基于mysqldump实现数据库的逻辑备份

 基于mysqldump实现数据库的逻辑备份

【备份类型】

根据备份时，数据库服务器是否在线：

冷备份：cold backup，服务器要离线，意味着我们的读写操作都不可以进行了，但是服务器离线比较困难。

温备份：warm backup，全局施加共享锁，只可读，不可写的备份叫温备份

热备份：hot backup，数据库不离线，读写操作都可以进行，通常是基于事务的存储引擎才能够完成的

根据备份时的数据集进行分类：

完全备份：full backup：指备份整个库，当下数据集的整个库的数据

部分备份：partial backup：只备份某张表或某张表的一部份数据，有时备份单张表是有必要的。

根据备份时的接口(直接备份数据文件还是通过mysql服务器导出数据)

物理备份：直接复制(归档)数据文件的备份方式；跨平台能力没有逻辑备份好，physucal backup。大数据集用这个比较好。

逻辑备份：把数据库中提出来保存为文本文件；通常使用的工具是mysqldump，logical backup，对于大容量数据不适用。恢复速度很慢，占据空间很大

【Mysqldump的工作原理】

Mysqldump命 令的工作原理很简单，它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换为一条INSTERT语句。这些 CREATE语句和INSTERT语句都是还原时使用的。还原数据时就可以使用其中的CREATE语句来创建表。使用其中的INSERT语句来还原数据。 它可以实现整个服务器备份，也可以实现单个或部分数据库、单个或部分表、表中的某些行、存储过程、存储函数、触发器的备份；并且能自动记录备份时刻的二进 制日志文件及相应的位置。对于InnoDB存储引擎来讲支持基于单事务模式实现热备，对于MyISAM则最多支持温备。

【mysqldump的使用选项】

-A   --all-database 备份所有的数据，温备使用的选项：

-l   --lock-tables；锁定备份的表

-x   --lock-all-tables :备份单个表使用，这是导致多个时间不一致，

InnoDB专用的选项:

这个选项是是专用在InnoDB的，对于我们的数据是混合的，是没办使用的。

--single-transaction:    单独备份InnoDB方式

-B ,--database db_name1 db_name2 备份指定的数据库

如果不加-B,那么数据恢复时，我们需要自己手动的创建库，但是如果加了-B，就会自动帮助我们创建库。

-C ，--compress:压缩传输

【代码备份的相关选项】

-E ,--events 备份事件调度器：

-R, --routines: 备份存储过程和存储函数

--triggers :备份触发器

--flush-logs ,-F 滚丁日志

--master-data[=#] :这里面的#可以取值为0,1,2

0：表示备份的时候不记录。

1，表示备份的时候，记录当时的二进制日志的文件位置，但是这个语句是启用的。

2：表示备份的时候，记录当时的二进制日志的位置，但是语句设备注释的，（这是我们的需要的）。

###这个完全备份的时候吧，会把二进制的日志进行到那记下来。

实验环境：Centos6.6

【完全备份】

[root@director2 tmp]# mysqldump -uroot -p --lock-all-table --flush-logs --all-database
--master-data=2 > /tmp/all.1.sql
Enter password:  
###########################查看备份数据滚动的位置#############################
-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000006‘, MASTER_LOG_POS=245;
##这里面显示是备份到了mysql-bin.000006 ，位置是245.
###################删除这个之前的滚动日志：###################################
mysql> show binary logs; #######先查看一下这个是，在复制比较好。
+------------------+--------------------+
|Log_name          | File_size          |
+------------------+-------------- -----+
| mysql-bin.000001 |        1492        |
| mysql-bin.000002 |        7968        |
| mysql-bin.000003 |         288        |
| mysql-bin.000004 |         288        |
| mysql-bin.000005 |      7785          |
| mysql-bin.000006 |       245          | 
mysql> purge binary logs to ‘mysql-bin.000006‘;
Query OK, 0 rows
affected (0.07 sec)
###########################删除完再次查看，只剩下最新得了#############################
mysql> show binary logs;
+------------------+-----------------+
| Log_name         | File_size       |
+------------------+-----------------+
| mysql-bin.000006 |       245       |
+------------------+-----------------+

#########################################查看库中的一个表数据######################################

mysql> use hellodb;                        ####使用这个库
Database changed
mysql> select * from teachers;    ####查找这个表
+-----+---------------+-----+----------------+
| TID | Name          | Age | Gender         |
+-----+---------------+-----+----------------+
|   1 | Song Jiang    |  45 | M              |
|   2 | Zhang Sanfeng |  94 | M              |
|   3 | Miejue Shitai |  77 | F              |
|   4 | Lin Chaoying  |  93 | F              |
+-----+---------------+-----+----------------+
4 rows in set (0.00 sec)
mysql> delete from teachers;      #####删除这个表
Query OK, 4 rows affected (0.06 sec)
mysql> select * from teachers;    ####再次查看为空
Empty set (0.00 sec)

#############################################假设一天过去了########################################

##############################################我们要做增量备份了###################################

################################################滚动日志###########################################

mysql> flush logs;
Query OK, 0 rows
affected (0.06 sec)
mysql> show binary logs;     #####此时已经是差生了新的日志
+------------------+-------------------+
| Log_name         | File_size         |
+------------------+-------------------+
| mysql-bin.000006 |       472         |
| mysql-bin.000007 |       245         |
+------------------+-------------------+

################################################备份日志文件############################################

[root@director2 tmp ]# cd /mydata/  #####这是我二进制文件的初始化文位置
[root@director2 mydata ]# cp mysql-bin.000006 /tmp

########################################假设第二天，我又创建一个表######################################

mysql> create table if not exists teatch(id tinyint unsigned auto_increment unique key,name
varchar(20) not null primary key);
Query OK, 0 rows affected (0.07 sec)

注意：此时我没有备份

[root@director2 mydata ]# cp mysql-bin.000007 /tmp/  ###
把这个二进制拷贝出去，然后销毁二级制文件，因为我们是假设

二进制文件和数据库不是放在同一个地方的。

[root@director2 mydata ]# rm -rf *        ####删除整个数据库
[root@director2 ~ ]# service mysqld stop    
#####停掉服务显然已经没办法停掉了。

[root@director2 ~ ]# killall mysqld     ####kill掉mysqld服务
###########################再次初始化####################################
[root@director2mysql ]# scripts/mysql_install_db --user=mysql --datadir=/mydata/

[root@director2 bin ]# ./mysql_secure_installation #####再进行一次安全初始化，把密码改成我们原来的。
# service mysqld start

#############################现在我们来还原我们的状态############################

首先，我们要恢复的是我们的最初备份完全备份

mysql> use hellodb;                               
Database changed
mysql> select * from teachers;       ######之前删除的表现在，也恢复了，但是我创建的表teatch表，却没有恢复。下面就需要我们的二进制日志了。

+-----+---------------+-----+----------------+
| TID | Name          | Age | Gender         |
+-----+---------------+-----+----------------+
|   1 | Song Jiang    |  45 | M              |
|   2 | Zhang Sanfeng |  94 | M              |
|   3 | Miejue Shitai |  77 | F              |
|   4 | Lin Chaoying  |  93 |  F             |
+-----+---------------+-----+----------------+
4 rows in set (0.01
sec)

############################恢复第一和第二次日志增量备份##########################

mysql> set sql_log_bin=0;     ######关闭这次二进制日志记录，这次记录没有意义。
Query OK, 0 rows affected (0.00 sec)
[root@director2 tmp ]# mysqlbinlog mysql-bin.000006 > one.sql  ####把二进制文件转化成我们的二进制文件
[root@director2 tmp ]# mysqlbinlog mysql-bin.000007 > two.sql ####把二进制文件转化成我们的二进制文件
[root@director2 tmp ]# mysql -uroot -p < one.sql       ####导入我们的二进制文件
Enter password:
[root@director2 tmp ]# mysql -uroot -p < two.sql       ####导入我们的二进制文件
Enter
password:

##############################3再次查看我们的表teatch已经存在了##########

而且我们的那个teachers已经被删除了

注意：在恢复文件时，是没必要记录二进制日志的。

mysql> set sql_log_bin=0;     ######关闭这次二进制日志记录，这次记录没有意义。

mysql> set sql_log_bin=1;                                    ######重新启用二进制日志。  

Query OK, 0 rows affected (0.00 sec)

至此mysqldump的全备份和增量备份全部实验完毕！！！！！！！！

本文出自 “我和Linux的那些年” 博客，请务必保留此出处http://guanqianjian.blog.51cto.com/9652236/1608183