mysqldump备份介绍
mysqldump是mysql用于转存储数据库的实用程序。它主要产生一个sql脚本，其中包含从头重新创建数据库所必需的命令create table insert等，适用于备份数据量不大的数据库。
优点：备份简单，恢复容易。
备份缺点：schema和数据存储在一起，巨大的sql语句、单个巨大的备份文件（备份的库和表都在一个文件中）。
mysqldump: 是一个mysql客户端命令，通过mysql协议连接至mysqld，实现数据库备份
命令的语法格式： mysqldump [options] database [tables]：备份单个库，或库指定的一个或多个表
mysqldump[options] --databases [options] db1 [db2 db3...]：备份一个或多个库
mysqldump[options] --all-databases [options]：备份所有库
常用选项： -a,--all-databases #备份所有数据库
innodb:
--single-transaction：启动一个大的单一事务实现备份
-b,--databases db_name1 db_name2 ...：备份指定的数据库
-c,--compress：压缩传输；
-x, --lock-all-tables：锁定所有表
-l, --lock-tables：锁定备份的表
--flush-logs,-f：锁定表之后执行flush logs命令；
其它选项：
-e,--events：备份指定库的事件调度器；
-r,--routines：备份存储过程和存储函数；
--triggers：备份触发器
--master-data[=#]：
1：记录change master to语句；此语句未被注释；
2：记录为注释语句；
特别说明：mariadb的数据恢复严重依赖与bin-log日志，所以为了防止磁盘故障导致数据文件和bin-log文件一起丢失，所以最好将bin-log日志存放到共享存储中。
设置方法：修改mariadb配置文件，将日志文件存放位置指向本地挂载网络存储的路径，然后重启mariadb服务即可。
[root@mariadb ~]# vim /etc/my.cnflog-bin=/backup/bin-log/mysql-bin.x
验证：重启服务之后/backup/bin-log/目录下就有了mysql的二进制日志文件和日志索引文件。
[root@mariadb ~]# ll /backup/bin-log/mysql-bin.*-rw-rw---- 1 mysql mysql 245 jun 16 00:00/backup/bin-log/mysql-bin.000001-rw-rw---- 1 mysql mysql 33 jun 16 00:00/backup/bin-log/mysql-bin.index
单个库实现备份恢复数据库中有hellodb表一个，需要做对hellodb表的备份，以实现数据库故障或者发生误删除操作时可以及时恢复数据库。
备份方案为：每周日完全备份一次数据库，周一到周六增量备份数据库
备份过程如下第一天备份：完全备份hellodb数据库，并且在完全备份的时候锁定表和滚动二进制日志
[root@mariadb~]# mysqldump -b hellodb -u root -p --lock-all-tables --flush-logs --master-data=2 > /backup/hellodb-`date+%f`.sqlenterpassword: [root@mariadb~]# ll /backup/total8-rw-r--r--1 root root 7950 jun 16 11:59 hellodb-2015-06-16.sql
由于在做完全备份的时候滚动了二进制日志，所以在做一些关于数据库更改的操作都会记录到新的二进制日志中，从查看中得知后续日志会记录到mysql-bin.000002中。
mariadb [(none)]> show master logs;+------------------+-----------+| log_name | file_size |+------------------+-----------+| mysql-bin.000001 | 288 || mysql-bin.000002 | 245 |+------------------+-----------+2 rows in set (0.00 sec)
备份完成之后的一天时间里，创建了一张表，并且向表中插入了一些数据
mariadb[(none)]> use hellodb;mariadb[hellodb]> create table tb1 (id int);mariadb[hellodb]> insert into tb1 values (1),(2),(3);mariadb[hellodb]> select * from tb1;+------+|id |+------+| 1 || 2 || 3 |+------+
第一天增量备份：当天的所有对数据库进行更改的语句都会记录到二进制日志文件中，只需要滚动二进制日志，把二进制日志进行备份即可，二进制滚动之后，第二天的所有对数据库进行更改的语句，都会记录到新的二进制日志文件中。
mariadb[hellodb]> flush logs;mariadb [hellodb]> show master logs;+------------------+-----------+| log_name | file_size |+------------------+-----------+| mysql-bin.000001 | 288 || mysql-bin.000002 | 577 || mysql-bin.000003 | 245 |+------------------+-----------+3 rows in set (0.00 sec)
将二进制日志文件转换为sql文件
[root@mariadb ~]# mysqlbinlog/backup/bin-log/mysql-bin.000002 > /backup/1.sql
第二天操作：继续向tb1中插入数据
mariadb[hellodb]> insert into tb1 values (21),(22),(23);mariadb[hellodb]> select * from tb1;+------+|id |+------+| 1 || 2 || 3 || 21 || 22 || 23 |+------+
故障模拟
hellodb数据库遭到误删除：
mariadb[hellodb]> drop database hellodb;
恢复前的准备查看二进制日志是记录到了第三个日志
mariadb[(none)]> show master logs;+------------------+-----------+|log_name | file_size |+------------------+-----------+|mysql-bin.000001 | 288 ||mysql-bin.000002 | 577 ||mysql-bin.000003 | 533 | #当前日志的记录位置+------------------+-----------+3rows in set (0.00 sec)mariadb[(none)]> show binlog events in 'mysql-bin.000003'\g;***************************5. row *************************** log_name: mysql-bin.000005 pos: 446 event_type: query server_id: 1end_log_pos:533 info: drop database hellodb #记录的删除语句5rows in set (0.00 sec) error:no query specified
由于是整个hellodb数据库遭到误删除，所以需要用一开始的完全备份文件，以及第一天的增量备份文件，恢复hellodb数据库和第一天对数据库进行修改的内容。
第二天对数据库进行修改的内容恢复：如果直接把当天的二进制日志导入到数据库，由于数据库中包含了删除语句，所以数据库还是会被删除；所以在导入第二天二进制日志时，需要删除日志中的drop语句。
将第二天的二进制日志文件转换成sql文件，放到backup目录下
[root@mariadb~]# mysqlbinlog /backup/bin-log/mysql-bin.000003 > /backup/2.sql
打开2.sql文件可以看到drop语句，删除这个语句
# at446#15061612:15:22 server id 1 end_log_pos 533 query thread_id=20 exec_time=0 error_code=0settimestamp=1434428122/*!*/;drop database hellodb #删除或者注释这一行
恢复过程：恢复之前为了避免产生没有用的二进制日志，可以关闭二进制日志的记录
mariadb[(none)]> set session sql_log_bin=0;
将第一次的完全备份数据导入到数据库
mariadb [hellodb]> source /backup/hellodb-2015-06-16.sql;
查看数据库已经导入，但是tb1表不存在
mariadb[(none)]> use hellodb;databasechangedmariadb[hellodb]> show tables;+-------------------+|tables_in_hellodb |+-------------------+|classes ||coc ||courses ||scores ||students ||teachers ||toc |+-------------------+
导入第一天增量备份的1.sql文件。
mariadb [hellodb]> source /backup/1.sql;
查看tb1表，发现第一天插入的数据都存在
mariadb[hellodb]> select * from tb1;+------+|id |+------+| 1 || 2 || 3 |+------+
导入当天的2.sql文件
mariadb [hellodb]> source /backup/2.sql;
查看数据已经完全恢复
mariadb[hellodb]> select * from tb1;+------+|id |+------+| 1 || 2 || 3 || 21 || 22 || 23 |+------+
恢复完成，启用记录二进制日志
mariadb[hellodb]> set session sql_log_bin=1;
特别说明：如果在恢复中关闭二进制日志，导入数据必须在mariadb命令行导入，如果在shell命令行导入还是会记录二进制日志的。
全库的备份恢复备份第一次完全备份，备份时锁定表，并滚动二进制日志
[root@mariadb ~]# mysqldump -a -u root -p--lock-all-tables --flush-logs --master-data=2 > /backup/all-`date +%f`.sqlenter password:[root@mariadb ~]# ll /backup/total 532-rw-r--r-- 1 root root 1980 jun 16 00:46 1.sql-rw-r--r-- 1 root root 1957 jun 16 00:52 2.sql-rw-r--r-- 1 root root 521774 jun 16 01:04all-2015-06-16.sqldrwxr-xr-x 2 mysql mysql 4096 jun 16 01:04 bin-log-rw-r--r-- 1 root root 7950 jun 16 00:43hellodb-2015-06-16.sql
由于备份的时候滚动了二进制日志，所以备份之后所有对数据库产生更改的操作都会记录到mysql-bin.000004中。
mariadb [hellodb]> show master logs;+------------------+-----------+| log_name | file_size |+------------------+-----------+| mysql-bin.000001 | 288 || mysql-bin.000002 | 577 || mysql-bin.000003 | 8833 || mysql-bin.000004 | 245 |+------------------+-----------+4 rows in set (0.00 sec)
备份完成的当天做了一些操作。
mariadb[hellodb]> delete from tb1 where id=21;mariadb[hellodb]> delete from tb1 where id=22;mariadb[hellodb]> select * from tb1;+------+|id |+------+| 1 || 2 || 3 || 23 |+------+
第一天增量备份
当天的所有对数据库进行更改的语句都会记录到二进制日志文件中，只需要滚动二进制日志，把二进制日志进行备份即可，二进制滚动之后，第二天的所有对数据库进行更改的语句，都会记录到新的二进制日志文件中。
mariadb [hellodb]> flush logs;mariadb [hellodb]> show master logs;+------------------+-----------+| log_name | file_size |+------------------+-----------+| mysql-bin.000001 | 288 || mysql-bin.000002 | 577 || mysql-bin.000003 | 8833 || mysql-bin.000004 | 670 || mysql-bin.000005 | 245 |+------------------+-----------+5 rows in set (0.00 sec)
将第一天的二进制日志文件转换为sql文件
[root@mariadb ~]# mysqlbinlog /backup/bin-log/mysql-bin.000004> /backup/all.1.sql
第二天对数据进行了一些操作
mariadb[hellodb]> insert into tb1 values (1000),(9000);mariadb[hellodb]> select * from tb1;+------+|id |+------+| 1 || 2 || 3 || 23 ||1000 ||9000 |+------+
模拟故障删除数据目录下的所有文件，模拟数据库故障：
[root@mariadb ~]# rm -rf /mydata/data/*
数据库这个时候还可以登录，但是数据库都不再了
mariadb [hellodb]> show databases;+--------------------+| database |+--------------------+| information_schema |+--------------------+
恢复前的准备发现数据库故障先关闭数据库，但是数据库无法正常关闭，只能关闭进程
[root@mariadb ~]# service mysqld stopmysql server pid file could not be found! [failed][root@mariadb ~]# killall mysqld
由于数据库数据目录的所有内容被删除，就算是导入了完全备份文件也是缺少一些文件，解决方法就是重新初始化一下数据库。
[root@mariadb ~]# cd /usr/local/mysql/[root@mariadb mysql]# scripts/mysql_install_db--user=mysql --datadir=/mydata/data/
初始化完成之后，一些基本的文件都存在了
[root@mariadb mysql]# ll /mydata/data/total 36-rw-rw---- 1 mysql mysql 16384 jun 16 01:22aria_log.00000001-rw-rw---- 1 mysql mysql 52 jun 16 01:22 aria_log_control-rw-r----- 1 mysql root 80 jun 16 01:18 mariadb.errdrwx------ 2 mysql root 4096 jun 16 01:22 mysqldrwx------ 2 mysql mysql 4096 jun 16 01:22 performance_schemadrwx------ 2 mysql root 4096 jun 16 01:22 test
将第二天的二进制日志文件转换为sql文件
[root@mariadb ~]# mysqlbinlog/backup/bin-log/mysql-bin.000005 > /backup/all.2.sql
恢复过程恢复之前启动mysql服务，否则无法导入备份文件
[root@mariadb ~]# service mysqld start
导入第一天的完全备份文件，由于刚刚初始化完成数据，这个时候的数据库还没有密码
[root@mariadb ~]# mysql show databases;+--------------------+| database |+--------------------+| information_schema || hellodb || mysql || performance_schema || test |+--------------------+5 rows in set (0.00 sec)mariadb [hellodb]> select * from tb1;+------+| id |+------+| 1 || 2 || 3 || 21 || 22 || 23 |+------+6 rows in set (0.00 sec)
导入第一天的增量备份文件
[root@mariadb ~]# mysql -u root -p select * from tb1;+------+| id |+------+| 1 || 2 || 3 || 23 |+------+4 rows in set (0.00 sec)
导入第二天的二进制日志转换后的sql文件。
[root@mariadb ~]# mysql -u root -p select * from tb1;+------+| id |+------+| 1 || 2 || 3 || 23 || 1000 || 9000 |+------+6 rows in set (0.00 sec)