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日志将反应事务执行的原时间。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏idba

如何确保应用程序运行的唯一性

一 简介 相信大家在开发脚本或者写程序的时候 ,大多会遇到如何判断已经有程序在运行的情况。比如设计备份binlog ,由于某个实例产生的binlog 数量大...

902
来自专栏喵了个咪的博客空间

基于PhalApi2的Redis拓展

基于PhalApi2的Redis拓展 ? 前言 Redis在PHP开发中运用场景已经无处不在,小到简单缓存大到数据库或消息队列都可以使用Redis来进行实现,基...

3125
来自专栏张戈的专栏

zabbix agentd客户端插件Shell一键自动安装脚本

这次生产环境上线了多台 Linux 服务器,需要全部纳入 Zabbix 监控范畴,一台一台的去装 Zabbix Agentd 插件那就太苦逼了,所幸 Zabbi...

3435
来自专栏杨建荣的学习笔记

MySQL备份恢复第二篇(r5笔记第6天)

MySQL中的数据恢复功能相比Oracle来说还是要单薄一些,而Oracle中的数据恢复相对来说自动化的程度要高一些。不过Mysql的二进制日志提供的信息很丰富...

3625
来自专栏北京马哥教育

64位CentOS6安装MySQL-5.7.13-linux-glibc2.5-x86_64.tar.gz

作者:云上之山 来源:http://blog.csdn.net/nengyu/article/details/51615836 1.从官网下载 mysql-5....

3558
来自专栏破晓之歌

Restful API实战 原

举例:需要请求获取服务器允许我对服务器进行哪些操作,可以用OPTIONS请求。还有类似访问频率参数

1682
来自专栏技术博文

在Linux环境下mysql的root密码忘记解决方法

方法一: 1.首先确认服务器出于安全的状态,也就是没有人能够任意地连接MySQL数据库。 因为在重新设置MySQL的root密码的期间,MySQL数据库完全出于...

3475
来自专栏散尽浮华

Mysql主从同步(1)-主从/主主环境部署梳理

Mysql复制概念说明 Mysql内建的复制功能是构建大型,高性能应用程序的基础。将Mysql的数据分布到多个系统上去,这种分布的机制,是通过将Mysql的某一...

3745
来自专栏张善友的专栏

AggregateCacheDependency、CacheDependency、SqlCacheDependency Asp.net 2.0和Sql Server的缓存管理和使用ObjectBuil

       这两天 PetShop is Evil?等对PetShop 的讨论很多,我在这里也发一篇凑凑热闹。我下面主要是对Asp.net 2.0新增的缓存管...

2078
来自专栏小狼的世界

重新认识wget

Wget是平时经常会用到的一个工具,这个工具其实有着非常丰富的参数和配置,能够实现很强大的功能。比较常用的一个就是抓站或者为网站做镜像。

1021

扫码关注云+社区

领取腾讯云代金券