mysqldump和mysqlbinlog实现完全备份和增量备份

一、数据备份基础：

1、备份类型：

完全备份：整个数据集；

部分部分：只备份数据子集；     

增量备份:仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据；

差异备份：仅备份最近一次完全备份以来变化的数据；

2、备份方式：

（1）、与存储引擎有关：热备份、温备份、冷备份:

      热备：读写操作均可执行；

      温备：读操作可执行；但写操作不成；

      冷备：读写操作均不可进行；

（2）、与存储引擎无关：物理备份、逻辑备份

      物理备份：直接复制数据文件进行备份；

      逻辑备份：从数据库中“导出”数据另存而进行的备份；

3、引擎支持的备份方式：

MyISAM:支持温备，不能热备

      InnoDB:支持热备          

4、备份时需要考虑的因素：

      持续时长、备份过程的时长、备份负载、恢复过程的时长        

5、备份什么

      数据

      二进制日志、innodb的事务日志

      代码（存储过程、存储函数、触发器、事件调度器）

      服务器的配置文件         

6、备份方案的设计：

      数据集：完全+增量；

      备份手段：物理、逻辑；     

7、备份工具：

mysqldump:逻辑备份工具，适用所有存储引擎，温备；完全备份、部分备份；对InnoDB存储引擎支持热备；

cp,tar等复制归档工具：物理备份工具，适用所有存储引擎；冷备：完全备份，部分备份；   

      mysql> SHOW BINARY|MASTER LOGS; 查看当前服务器所使用的二进制状态

      mysql> SHOW MASTER STATUS;查看当前正在使用的二进制状态

      mysql> SHOW BINLOG EVENTS IN '' FROMpos; 查看某一个指定的二进制日志中的文件内容

mysqlbinlog:查看二进制日志文件并利用其做增量备份

      mysqlbinlog [OPTION] log_file

           --start-position        从哪个位置开始做增量备份

           --stop-position        增量备份到哪个位置结束

           --start-datetime=    从哪个时间点开始做增量备份

           --stop-datetime=     增量备份到哪个时间点结束

二、备份工具详解：mysqldump、mysqlbinlog                      

1、逻辑备份工具：mysqldump，mydumper,phpMyAdmin

（1）、mysqldump：客户端命令，通过mysql协议连接至mysqld服务器

      man mysqldump

      mysqldump [options] [db_name [tbl_name...]]   

       shell> mysqldump [options] db_name[tbl_name ...]

            shell> mysqldump [options]--databases db_name ...

            shell> mysqldump [options]--all-databases

       备份的数据集：

           -A，--all-databases              备份所有数据库

           -B db_name,...                备份指定数据库

           --databases db_name,...       备份指定数据库

（2）引擎支持备份方式及实现：

MyISAM:只支持温备；必须锁定备份库，而后才能启动备份操作，

支持锁定的参数如下：

          --lock-all-tables, -x ：锁定所有库的所有表

            --lock-tables, -l：对于每个单独的数据库，在启动备份之前锁定其所有表；

           注意：这两个参数对InnoDB表一样生效，实现温备；

      InonoDB：支持热备，可以直接启用备份，无需锁定表。

           --single-transaction

（3）、其他选项：

      -E,--events:备份指定数据库相关的所有event scheduler;

      --routines, -R：备份指定数据库相关的所有存储过程和存储函数；  

      --triggers：备份表的相关的触发器；

      --master-data ：

           1)、记录为CHANGE MASTER TO语句，此语句不被注释；

           2)、记录为注释的CHANGE MASTER TO语句

            mysqldump--master-data=2 --all-databases > dumpfile

      --flush-logs:日志滚动

           锁定表完成后，执行flush logs命令；

2、备份建议：         

二进制日志文件不应该与数据文件放在同一磁盘；

建议还原时关闭二进制日志记录

      mysql> SHOW GLOBAL VARIABLES LIKE'%log%';

      sql_log_bin  ON

      mysql> SET sql_log_bin = OFF；

三、利用mysqldump做完全备份还原

1、mysqldump做部分备份和完全备份：                    

[root@www ~]# cphellodb.sql /root/

cp:"hellodb.sql" 与"/root/hellodb.sql" 为同一文件

[root@www ~]#file hellodb.sql

hellodb.sql:ASCII text, with very long lines

[root@www ~]#less hellodb.sql

-- MySQL dump10.13  Distrib 5.5.33, for Linux (x86_64)

--

-- CurrentDatabase: `hellodb`

CREATE DATABASE/*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `hellodb`;

-- Tablestructure for table `classes`

DROP TABLE IFEXISTS `classes`;

/*!40101 SET@saved_cs_client     =@@character_set_client */;

/*!40101 SETcharacter_set_client = utf8 */;

CREATE TABLE`classes` (

  `ClassID` tinyint(3) unsigned NOT NULLAUTO_INCREMENT,

  `Class` varchar(100) DEFAULT NULL,

  `NumOfStu` smallint(5) unsigned DEFAULT NULL,

  PRIMARY KEY (`ClassID`)

) ENGINE=MyISAMAUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

/*!40101 SETcharacter_set_client = @saved_cs_client */;

 

#修改MyISAM引擎为InnoDB，使其支持热备

[root@www ~]#sed -i 's/MyISAM/InonoDB/g' hellodb.sql

[root@www ~]#sed -i 's/InonoDB/InnoDB/g' hellodb.sql

[root@www ~]#less hellodb.sql

-- MySQL dump10.13  Distrib 5.5.33, for Linux (x86_64)

--

-- Host:localhost    Database: hellodb

--------------------------------------------------------

-- Serverversion       5.5.33-log

 

-- MySQL dump10.13  Distrib 5.5.33, for Linux (x86_64)

--

-- Host:localhost    Database: hellodb

--------------------------------------------------------

-- Serverversion       5.5.33-log

 

DROP TABLE IFEXISTS `classes`;

/*!40101 SET@saved_cs_client     =@@character_set_client */;

/*!40101 SETcharacter_set_client = utf8 */;

CREATE TABLE`classes` (

  `ClassID` tinyint(3) unsigned NOT NULLAUTO_INCREMENT,

  `Class` varchar(100) DEFAULT NULL,

  `NumOfStu` smallint(5) unsigned DEFAULT NULL,

  PRIMARY KEY (`ClassID`)

) ENGINE=InnoDBAUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

/*!40101 SETcharacter_set_client = @saved_cs_client */;

--

-- Dumping datafor table `classes`

--

mysql> SHOWDATABASES;

+--------------------+

| Database          |

+--------------------+

|information_schema |

| hellodb             |

| mysql               |

| test                  |

| testdb              |

+--------------------+

5 rows in set(0.04 sec)

 

mysql> usehellodb

Reading tableinformation for completion of table and column names

You can turn offthis feature to get a quicker startup with -A

Database changed

mysql> SHOWTABLES;

+-------------------+

|Tables_in_hellodb |

+-------------------+

| classes            |

| coc                 |

| courses           |

| scores             |

| students          |

| teachers          |

| toc                  |

+-------------------+

7 rows in set(0.00 sec)

 

mysql> SELECT* FROM students;

+-------+---------------+-----+--------+---------+-----------+

| StuID |Name          | Age | Gender | ClassID |TeacherID |

+-------+---------------+-----+--------+---------+-----------+

|     1 | Shi Zhongyu   |  22| M      |       2 |         3 |

|     2 | Shi Potian    |  22| M      |       1 |         7 |

|     3 | Xie Yanke     | 53 | M      |       2 |        16 |

|     4 | Ding Dian     | 32 | M      |       4 |         4 |

|     5 | Yu Yutong     | 26 | M      |       3 |         1 |

|     6 | Shi Qing      | 46 | M      |       5 |     NULL |

|     7 | Xi Ren        | 19 | F      |