本文使用的数据库软件版本为：
mariadb-10.0.13.tar.gz
mysqldump备份软件版本为：
# mysqldump --version
mysqldump Ver 10.15 Distrib 10.0.13-MariaDB, for Linux (x86_64)
一.mysqldump简介
mysqldump客户端可用来转储数据库或搜集数据库进行备份或将数据转移到另一个SQL服务器(不一定是一个MySQL服务器)。转储包含创建表和/或装载表的SQL语句。
如果你在服务器上进行备份，并且表均为MyISAM表，应考虑使用mysqlhotcopy，因为可以更快地进行备份和恢复。
支持完整/全备份：备份指定数据集中的所有数据
二.mysqldump命令语法格式：
备份单个库或单个库中的指定表或多个表：
mysqldump [OPTIONS] database [tables]
备份一个或多个库：
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
备份所有库：
mysqldump [OPTIONS] --all-databases [OPTIONS]
如果没有指定任何表或使用了---database或--all--database选项，则转储整个数据库。
要想获得你的版本的mysqldump支持的选项，执行mysqldump --help。
如果运行mysqldump没有--quick或--opt选项，mysqldump在转储结果前将整个结果集装入内存。
如果转储大数据库可能会出现问题。该选项默认启用，但可以用--skip-opt禁用。
如果使用最新版本的mysqldump程序生成一个转储重装到很旧版本的MySQL服务器中，不应使用--opt或-e选项。
三.mysqldump常用命令选项：
注意：生产环境中二进制日志文件和数据文件不应该放置在同一磁盘或存储；
四.实际案例演示
1.完全备份的实现
# mysqldump -A -uroot -hlocalhost -p /tmp/all1.sql
到备份目录查看：
[root@node1 tmp]# du -sh all1.sql524K all1.sql
备份完成后可以将备份文件压缩；
[root@node1 tmp]# xz all1.sql[root@node1 tmp]# lsall1.sql.xz ks-script-t3nzeD ks-script-t3nzeD.log mysql.sock yum.log[root@node1 tmp]# du -sh all1.sql.xz108K all1.sql.xz
我们将用户名和密码写入my.cnf配置文件，实现mysqldump命令输入时不用输入用户名和密码及主机名：
[root@node1 ~]# vim .my.cnf[client]user=roothost=localhostpassword=oracle [mysqldump]user=roothost=localhostpassword=oracle
2.MyISAM引擎的温备的实现
我们备份时需要锁定所有表，
-x, --lock-all-tables
也可以备份那张表就锁定那张表：
-l, --lock-tables
可能造成时间点不一致，如备份一张表的时候锁定了表，备份后跟其他表的时间点就不一致了，那么数据就会不一致；
我们生产环境中就使用锁定所有表的选项；除非是只备份单张表；
实例：温备实现对指定数据库的备份；
[root@node1 ~]# mysqldump -B mysql--lock-all-tables > /tmp/warmbackup.sql[root@node1 ~]# ls -lh /tmptotal 128K-rw-r--r-- 1 root root 105K Jan 22 15:52 all1.sql.xzsrwxrwxrwx 1 mysql mysql 0 Jan 22 00:22 mysql.sock-rw-r--r-- 1 root root 10K Jan 22 16:20 warmbackup.sql
备份单个数据库时参数-B的有无是有区别的：
#mysqldump -B mysql --lock-all-tables会自动添加create database语句进行，备份的数据库我们将来恢复时会自动创建空库； #mysqldump mysql --lock-all-tables不会自动添加create database语句，备份的数据库我们将来进行恢复时需要手动创建空库；
3.InnoDB引擎的热备的实现：
先启动一个大的单一事物来备份，InnoDB支持MVCC多版本并发控制，在备份之前申请启动一个事物，那么其它用户无论怎么更改数据，此事物中的数据都不会更改的，通过此事物看见的数据是一致的。
而对于非InnoDB存储引擎，指定如下选项无更多意义，
--single-transaction
对于混合存储引擎可能不会实现热备；
所有数据库的存储引擎都是InnoDB就能实现热备；
如果我们只需要备份单个数据库，且数据库的存储引擎是InnoDB，那么热备就能实现；
如果备份指定数据库就使用如下指令：
-B, --databases
实例：热备实现对InnoDB存储引擎的数据库备份；
[root@node1 ~]# mysqldump -B hellodb --single-transaction > /tmp/hotbackup.sql
4.mysqldump全量备份+mysqlbinlog二进制日志增量备份的实现
创建备份文件夹：
[root@node1 ~]# mkdir /backup
实现对指定数据库hellodb的完全备份：
[root@node1 ~]# mysqldump -B hellodb --lock-all-tables --master-data=2 > /backup/hellodb-`date +%F`.sql[root@node1 ~]# ls /backup/hellodb-2015-01-22.sql
为了实现增量备份，我们连入mysql，做一些更改操作；
[root@node1 ~]# mysqlWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 16Server version: 10.0.13-MariaDB-log Source distribution Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use hellodb;Database changed MariaDB [hellodb]> show tables;+-------------------+| Tables_in_hellodb |+-------------------+| classes || coc || courses || myisam_table || scores || students || teachers || toc || v1 |+-------------------+9 rows in set (0.00 sec) MariaDB [hellodb]> create table tb1 (id int);Query OK, 0 rows affected (0.14 sec) MariaDB [hellodb]> insert into tb1 values (1),(2),(22);Query OK, 3 rows affected (0.12 sec)Records: 3 Duplicates: 0 Warnings: 0 MariaDB [hellodb]> \qBye
增量备份备份的开始位置如下：
[root@node1 ~]# vim /backup/hellodb-2015-01-22.sql-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=518;
如图中：位置是518，
那么增量备份就是备份从518开始到结束的位置中间的内容；
我们需要连接数据库执行FLUSH TABLES READ LOCK; 或者我们在这里执行FLUSH LOGS;即可；
[root@node1 ~]# mysqlWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 17Server version: 10.0.13-MariaDB-log Source distribution Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> flush logs;Query OK, 0 rows affected (0.06 sec) MariaDB [(none)]> show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 20181 || mysql-bin.000002 | 2185 || mysql-bin.000003 | 1098 || mysql-bin.000004 | 1311 || mysql-bin.000005 | 345 || mysql-bin.000006 | 856 || mysql-bin.000007 | 365 |+------------------+-----------+7 rows in set (0.00 sec) MariaDB [(none)]> \qBye
或者我们增量备份根据时间范围来备份：
[root@node1 ~]# mysqlbinlog --start-datetim '2015-01-22 16:20:00' --stop-datetime '2015-01-22 16:43:00' /mydata/data/mysql-bin.* > /backup/increment-`date +%F`.sql
第一次增量备份就实现了。
假如我们再次更改了数据库，并且不小心删除了hellodb数据库，我们如何恢复数据库？
此时，二进制日志就至关重要了，我们之前备份的二进制日志是刚才插入的那条数据未备份，是增量备份到删除数据库之间的那段时间的内容未备份；我们可以导出这个阶段的二进制日志，但是需要过滤掉删除数据库这条指令；不然你恢复所有数据库后它还会将数据库hellodb删除；
我们将二进制日志文件备份到临时目录中：
我们先查看二进制日志文件中drop语句执行的时间点：
[root@node1 ~]# mysqlbinlog /mydata/data/mysql-bin.000007略…# at 564#150122 16:58:26 server id 1 end_log_pos 651 Query thread_id=18 exec_time=0 error_code=0SET TIMESTAMP=1421917106/*!*/;drop database hellodb/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
我们知道了drop删除数据库指令的语句在564这个时间点，我们可以使用--stop-position=564只备份到时间点564之前的二进制日志：
[root@node1 ~]# mysqlbinlog --stop-position=564 /mydata/data/mysql-bin.000007 > /tmp/a.sql
备份至此就完成了，下面就是还原恢复数据库至删除数据库前状态的过程了。
还原完全备份：
[root@node1 ~]# mysql < /backup/hellodb-2015-01-22.sql
现在登录数据库查看，我们创建的表tb1是不存在的；
[root@node1 ~]# mysqlWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 20Server version: 10.0.13-MariaDB-log Source distribution Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use hellodb;Database changedMariaDB [hellodb]> show tables;+-------------------+| Tables_in_hellodb |+-------------------+| classes || coc || courses || myisam_table || scores || students || teachers || toc || v1 |+-------------------+9 rows in set (0.00 sec) MariaDB [hellodb]> \qBye
恢复增量备份内容：
[root@node1 ~]# mysql < /backup/increment-2015-01-22.sql
现在我们创建的表tb1存在了，但是数据还是不全，丢失了我们插入的那条数据；
[root@node1 ~]# mysqlWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 22Server version: 10.0.13-MariaDB-log Source distribution Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use hellodb;Database changedMariaDB [hellodb]> select * from tb1;+------+| id |+------+| 1 || 2 || 22 |+------+3 rows in set (0.00 sec) MariaDB [hellodb]> \qBye
进行时间点还原，恢复从增量备份至删除数据库之前这段时间内的数据：
[root@node1 ~]# mysql < /tmp/a.sql
恢复完成后我们查看数据库，我们插入的数据恢复了：
[root@node1 ~]# mysqlWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 24Server version: 10.0.13-MariaDB-log Source distribution Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use hellodb;Database changedMariaDB [hellodb]> select * from tb1;+------+| id |+------+| 1 || 2 || 22 || 9 || 20 |+------+5 rows in set (0.00 sec) MariaDB [hellodb]> \qBye
五.如何使用shell脚本实现mysql全量，增量备份。
增量备份在周一-周六凌晨3点，会复制mysql-bin.00000*到指定目录；
而全量备份则使用mysqldump将所有的数据库导出，每周日凌晨3点执，并会删除上周留下的mysq-bin.00000*。然后对mysql的备份操作会保留在bak.log文件中。
实现脚本创建：
1.编写全量备份脚本
[root@node1 ~]# vim DBfullybak.sh#!/bin/bash# Program# use mysqldump to Fully backup mysql data per week!# History# 2015-01-22 guo# PathBakDir=/backupLogFile=/backup/bak.logDate=`date +%Y%m%d`Begin=`date +"%Y年%m月%d日 %H:%M:%S"`cd $BakDirDumpFile=$Date.sqlGZDumpFile=$Date.sql.tgz/usr/local/mysql/bin/mysqldump -uroot -poracle --quick --all-databases --flush-logs --delete-master-logs --single-transaction > $DumpFile/bin/tar czvf $GZDumpFile $DumpFile/bin/rm $DumpFileLast=`date +"%Y年%m月%d日 %H:%M:%S"`echo 开始:$Begin 结束:$Last $GZDumpFile succ >> $LogFilecd $BakDir/dailyrm -rf *
2.编写增量备份脚本
[root@node1 ~]# vim DBdailybak.sh#!/bin/bash# Program# use cp to backup mysql data everyday!# History#2015-01-22 guo# PathBakDir=/backup/dailyBinDir=/mydata/dataLogFile=/backup/bak.logBinFile=/mydata/data/mysql-bin.index/usr/local/mysql/bin/mysqladmin -uroot -poracle flush-logs#这个是用于产生新的mysql-bin.00000*文件Counter=`wc -l $BinFile |awk '{print $1}'`NextNum=0#这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的。for file in `cat $BinFile`do base=`basename $file` #basename用于截取mysql-bin.00000*文件名，如去掉./mysql-bin.000005前面的./ NextNum=`expr $NextNum + 1` if [ $NextNum -eq $Counter ] then echo $base skip! >> $LogFile else dest=$BakDir/$base if (test -e $dest) #test -e用于检测目标文件是否存在，存在就写exist!到$LogFile去。 then echo $base exist! >> $LogFile else cp $BinDir/$base $BakDir echo $base copying >> $LogFile fi fidoneecho `date +"%Y年%m月%d日 %H:%M:%S"` $Next Bakup succ! >> $LogFile
3.设置计划任务每天执行
# crontab -l //内容为下#每个星期日凌晨3:00执行完全备份脚本0 3 * * 0 /root/DBfullybak.sh >/dev/null 2>&1#周一到周六凌晨3:00做增量备份0 3 * * 1-6 /root/DBdailybak.sh >/dev/null 2>&1
总结：逻辑备份不适用于数据量特别大的生产环境，耗时长，我们在执行较大数据集的备份是可以执行物理备份，基于数据库创建快照，使用rsync拷贝数据库数据到远程服务器。
本文出自 “飞雪连天射白鹿” 博客，请务必保留此出处http://sohudrgon.blog.51cto.com/3088108/1607396