MySQL（十五）之数据备份中mysqldump详解

前言

　　其实前面一篇数据备份已经是非常的详细了，这里我想单独的讲解一下mysqldump，相信很多程序员都是用过这个命令的！

一、MySQL数据库的备份与还原

1.1、MySQL数据库备份

　　1）语法

　　mysqldump -u username -p db_name table1 table2 ...> xxx.sql

　　　　含义：

　　　　　　db_name参数：表示数据库的名称；
　　　　　　table1和table2参数：表示需要备份的表的名称，为空则整个数据库备份；
　　　　　　xxx.sql参数：表设计备份文件的名称，文件名前面可以加上一个绝对路径。通常将数据库被分成一个后缀名为sql的文件；

　　2）备份多个数据库和所有数据库

　　mysqldump -u username -p --databases db_name1 db_name2 > xxx.sql
　　mysqldump -u username -p -all-databases > xxx.sql

1.2、MySQL数据库还原

　　1）语法　

　　mysql -u root -p [dbname] < xxx.sql　　　

　　　　含义：

　　　　　　[dbname]如果不指定的话，表示还原所有数据库。

注意：复制整个数据库目录也可以备份数据库，也是最直接有效的方式，但是只适用于使用了MyISAM引擎的数据库，不适用于使用了InnoDB引擎的数据库。　　

　　　数据库还原还可以使用：

　　　　mysql -uroot -p
　　　　source Backup.sql;

二、实战使用mysqldump

2.1、数据库备份

　　1）查看所有的数据库

　　　　

　　2）备份db_love、db_test

　　　　

2.2、数据库还原

　　1）删除数据库db_love、db_test

　　　　

　　　　

　　2）进行数据恢复

　　　　这里恢复db.sql中所有的数据库

　　　　

　　3）查询是否恢复

　　　　

三、MySQL数据备份脚本（一）

1）备份脚本

#!/bin/bash 
#created by yangqiqi 2017-08-24 

#创建备份用户
#grant select,lock tables,reload,super,file,show view on *.* to 'mysqlbackup'@'localhost' identified by 'mysql_ritto'; 
#grant execute on *.* to 'mysqlbackup'@'localhost' identified by 'mysql_ritto';  授予调用存储过程的权限
##flush privileges;
USERNAME=xxxxxx #备份的用户名 
PASSWORD=xxxxxx  #备份的密码
HOST=localhost #备份主机

DATE=`date +%Y-%m-%d`  #用来做备份文件名字的一部分
OLDDATE=`date +%Y-%m-%d -d '-10 days'`  #本地保存天数  

MYSQL=/usr/local/mysql/bin/mysql
MYSQLDUMP=/usr/local/mysql/bin/mysqldump
MYSQLADMIN=/usr/local/mysql/bin/mysqladmin

#创建备份的目录和文件
BACKDIR=/data/backup/db
[ -d ${BACKDIR} ] || mkdir -p ${BACKDIR}
[ -d ${BACKDIR}/${DATE} ] || mkdir ${BACKDIR}/${DATE}
[ ! -d ${BACKDIR}/${OLDDATE} ] || rm -rf ${BACKDIR}/${OLDDATE} #保存10天 多余的删除最前边的
#开始备份  列出备份的数库
for DBNAME in mysql test test1 test2 test3 ##依次罗列需要备份的数据库 
do
  ${MYSQLDUMP} -B -u${USERNAME} -p${PASSWORD} ${DBNAME} | gzip > ${BACKDIR}/${DATE}/${DBNAME}-backup-${DATE}.sql.gz # -B 备份存储过程
  logger "${DBNAME} has been backup successful - $DATE"
  /bin/sleep 5
done

2）备份样式

[root@xxx db]# ls
2017-08-25
[root@xxx db]# cd 2017-08-25/
[root@xxx 2017-08-25]# ls
mysql-backup-2017-08-25.sql.gz  test2-backup-2017-08-25.sql.gz  test-backup-2017-08-25.sql.gz
test1-backup-2017-08-25.sql.gz  test3-backup-2017-08-25.sql.gz

3）Logger分析

logger 是一个shell 命令接口，可以通过该接口使用Syslog的系统日志模块，还可以从命令行直接向系统日志文件写入一行信息。
日志的级别
日志的级别分为七级，从紧急程度由高到底：
emerg 系统已经不可用，级别为紧急 
alert 警报，需要立即处理和解决 
crit 既将发生，得需要预防。事件就要发生 
warnig 警告 
err 错误信息，普通的错误信息 
notice 提醒信息，很重要的信息 
info 通知信息，属于一般信息 
debug 这是调试类信息

4）上边的脚本备份完毕后  就可以在   看到每个数据库备份的信息  logger的作用

[root@xxx 2017-08-25]# tail -f /var/log/messages
Aug 25 09:30:06 xxx root: test has been backup successful - 2017-08-25
Aug 25 09:30:11 xxx root: test1 has been backup successful - 2017-08-25
Aug 25 09:30:16 xxx root: test2 has been backup successful - 2017-08-25
Aug 25 09:30:21 xxx root: test3 has been backup successful - 2017-08-25
Aug 25 09:40:02 xxx root: mysql has been backup successful - 2017-08-25
Aug 25 09:40:07 xxx root: test has been backup successful - 2017-08-25
Aug 25 09:40:12 xxx root: test1 has been backup successful - 2017-08-25
Aug 25 09:40:17 xxx root: test2 has been backup successful - 2017-08-25
Aug 25 09:40:22 xxx root: test3 has been backup successful - 2017-08-25

　　注意：

　　　　调用存储过程时报了下面的错误
　　　　　　ERROR 1370 (42000): execute command denied to user backupAccount@'localhost' for routine 'databaseName.spName'

　　　　解决方法：

　　　　　　grant execute on *.* to 'mysqlbackup'@'localhost' identified by 'mysql_ritto'; 

5）数据库备份完毕后，可能有 需要把备份的文件传输到一个专门用来放备份文件的服务器上 。（sync：实现远程同步功能的软件） 

　　举例：　

　　　　每天凌晨1点把 A服务器上/data/backup/db/下的数据备份文件放到 B服务器里的/data/backup/db_192.168.1.11/目录下 

　　　　不是累加 是增量更新 （--delete）

　　A服务器：
　　　　数据源服务器

　　　　yum install rsync

 　　　　打开防火墙 关闭seLinux

 -A INPUT -p tcp -m state --state NEW -m tcp --dport 873 -j ACCEPT

　　B服务器：

 ssh-keygen

　　　　把公钥文件里的id_rsa.pub内容复制到 A服务器里的authorized_keys 
　　　　文件中准备脚本文件  

#!/bin/bash
usr/bin/rsync -avz --delete -e  "ssh -p 4396"  root@192.168.1.11:/data/backup/db  /data/backup/db_192.168.1.11/
logger "Successful backup file transfer - $DATE"

　　　　采用的是 拉push的政策在B服务器上将需要的数据源从A服务器上拉下来

四、MySQL数据备份脚本（二）

4.1、结合Linux的cron命令实现定时备份

　　比如需要在每天凌晨1:30备份某个主机上的所有数据库并压缩dump文件为gz格式，那么可在/etc/crontab配置文件中加入下面代码行：

30 1 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_`date '+%m-%d-%Y'`.sql.gz

　　前面5个参数分别表示分钟、小时、日、月、年，*号表示任意。 date '+%m-%d-%Y'得到当前日期的MM-DD-YYYY格式。

4.2、一个完整的Shell脚本备份MySQL数据库示例　

#vi /backup/backup.sh

#!bin/bash
cd /backup
echo "You are in backup dir"
mv backup* /oldbackup
echo "Old dbs are moved to oldbackup folder"
File = backup-$Now.sql
mysqldump -u user -p password database-name > $File
echo "Your database backup successfully completed"

　　上面脚本文件保存为backup.sh，并且系统中已经创建两个目录/olcbackup和/backup。每次执行backup.sh时都会先将/backup目录下所有名称为backup开头的文件移到/oldbackup目录。　　　

　　为上述脚本制定执行计划如下：

#crontab -e
30 1 * * * /backup.sh

4.3、mysqldump全量备份+mysqlbinlog二进制日志增量备份

　　从mysqldump备份文件恢复数据会丢失掉从备份点开始的更新数据，所以还需要结合mysqlbinlog二进制日志增量备份。

　　确保my.ini或者my.cnf中包含下面的配置以启用二进制日志，或者mysqld ---log-bin：

[mysqld]
log-bin=mysql-bin

　　mysqldump命令必须带上--flush-logs选项以生成新的二进制日志文件：

mysqldump --single-transaction --flush-logs --master-data=2 > backup.sql

　　这样生成的增量二进制日志文件比如为mysql-bin.000003，那么恢复数据时如下：

shell> mysql -uroot -pPwd < backup_sunday_1_PM.sql
shell> mysqlbinlog mysql-bin.000003 | mysql -uroot -pPwd

　　此外mysqlbinlog还可以指定--start-date、--stop-date、--start-position和--stop-position参数，

　　用于精确恢复数据到某个时刻之前或者跳过中间某个出问题时间段恢复数据，直接摘录MySQL文档说明中相关内容如下：

　　　　5.9.3.1. 指定恢复时间 对于MySQL 4.1.4，可以在mysqlbinlog语句中通过--start-date和--stop-date选项指定DATETIME格式的起止时间。

　　　　举例说明，假设在今天上午10:00(今天是2005年4月20日)，执行SQL语句来删除一个大表。要想恢复表和数据，你可以恢复前晚上的备份，并输入： mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456
　　　　| mysql -u root -pmypwd 该命令将恢复截止到在--stop-date选项中以DATETIME格式给出的日期和时间的所有数据。如果你没有检测到几个小时后输入的错误的SQL语句，可能你想要恢复后面发生的活动。

　　　　根据这些，你可以用起使日期和时间再次运行mysqlbinlog：
　　　　mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456
　　　　| mysql -u root -pmypwd
　　　　在该行中，从上午10:01登录的SQL语句将运行。组合执行前夜的转储文件和mysqlbinlog的两行可以将所有数据恢复到上午10:00前一秒钟。你应检查日志以确保时间确切。下一节介绍如何实现。
　　　　5.9.3.2. 指定恢复位置 也可以不指定日期和时间，而使用mysqlbinlog的选项--start-position和--stop-position来指定日志位置。它们的作用与起止日选项相同，不同的是给出了从日志起的位置号。

　　　　使用日志位置是更准确的恢复方法，特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号，可以运行mysqlbinlog寻找执行了不期望的事务的时间范围，但应将结果重新指向文本文件以便进行检查。

　　　　操作方法为： mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00"
　　　　/var/log/mysql/bin.123456 /tmp/mysql_restore.sql 该命令将在/tmp目录创建小的文本文件，将显示执行了错误的SQL语句时的SQL语句。你可以用文本编辑器打开该文件，寻找你不要想重复的语句。

　　　　如果二进制日志中的位置号用于停止和继续恢复操作，应进行注释。用log_pos加一个数字来标记位置。使用位置号恢复了以前的备份文件后，你应从命令行输入下面内容：
　　　　　　mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456
　　　　　　| mysql -u root -pmypwd
　　　　　　mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456
　　　　| mysql -u root -pmypwd 上面的第1行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。

　　　　因为mysqlbinlog的输出包括每个SQL语句记录之前的SET TIMESTAMP语句，恢复的数据和相关MySQL日志将反应事务执行的原时间。