1. 介绍

在日常维护工作当中经常会需要对数据进行导出操作，而mysqldump是导出数据过程中使用非常频繁的一个工具。 它自带的功能参数非常多，文章中会列举出一些常用的操作，在文章末尾会将所有的参数详细说明列出来。在日常运维工作中，对mysql数据库的备份是万分重要的，以防在数据库表丢失或损坏情况出现，可以及时恢复数据。下面我将分成三节来讲解mysql数据库备份和恢复的相关操作：2.常见mysql导出用法；3：mysql增量备份和全量备份定时用法；4：数据恢复和导入方法。

 

2. 常见用法

2.1. 导出所有数据库

该命令会导出包括系统数据库在内的所有数据库。

mysqldump -uroot -proot --all-databases >/tmp/all.sql

 

2.2. 导出db1、db2两个数据库的所有数据

mysqldump -uroot -proot --databases db1 db2 >/tmp/user.sql

 

2.3. 导出db1中的a1、a2表

注意： 
导出指定表只能针对一个数据库进行导出，且导出的内容中和导出数据库也不一样，导出指定表的导出文本中没有创建数据库的判断语句，只有删除表-创建表-导入数据。

mysqldump -uroot -proot --databases db1 --tables a1 a2  >/tmp/db1.sql

 

2.4. 条件导出，导出db1表a1中id=1的数据

如果多个表的条件相同可以一次性导出多个表。

  • 字段是整形
mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1'  >/tmp/a1.sql
  • 字段是字符串，并且导出的sql中不包含drop table，create table 
mysqldump -uroot -proot --no-create-info --databases db1 --tables a1 --where="id='a'"  >/tmp/a1.sql

2.5. 生成新的binlog文件

有时候会希望导出数据之后生成一个新的binlog文件，只需要加上-F参数即可。

mysqldump -uroot -proot --databases db1 -F >/tmp/db1.sql

2.6. 只导出表结构不导出数据--no-data 

mysqldump -uroot -proot --no-data --databases db1 >/tmp/db1.sql

2.7. 跨服务器导出导入数据

mysqldump --host=h1 -uroot -proot --databases db1 |mysql --host=h2 -uroot -proot db2

将h1服务器中的db1数据库的所有数据导入到h2中的db2数据库中，db2的数据库必须存在否则会报错。

mysqldump --host=192.168.80.137 -uroot -proot -C --databases test |mysql --host=192.168.80.133 -uroot -proot test

注意： 
加上-C参数可以启用压缩传递。

3.数据库备份

线上数据库备份场景：每周日执行一次全量备份，然后每天晚上12点执行MySQLdump增量备份.

下面对这种备份方案详细说明下：

格式：mysqldump -h链接ip -P(大写)端口 -u用户名 -p密码 数据库名>XX.sql
  • 1.MySQLdump增量备份配置

执行增量备份的前提条件是MySQL打开binlog日志功能，在my.cnf中加入

log-bin=/opt/data/binlog/mysql-bin
“log-bin=”后的字符串为日志记载目录，一般建议放在不同于MySQL数据目录的磁盘上。
  • 2.MySQLdump增量备份

假定星期日下午1点执行全量备份，适用于MyISAM存储引擎。

[root@test-huanqiu ~]# MySQLdump --lock-all-tables --flush-logs --master-data=2 -u root -p test > backup_sunday_1_PM.sql

对于InnoDB将--lock-all-tables替换为--single-transaction
--flush-logs为结束当前日志，生成新日志文件；
--master-data=2 选项将会在输出SQL中记录下完全备份后新日志文件的名称，

用于日后恢复时参考，例如输出的备份SQL文件中含有：

CHANGE MASTER TO MASTER_LOG_FILE=’MySQL-bin.000002′, MASTER_LOG_POS=106;
  • 3.MySQLdump增量备份其他说明：

如果MySQLdump加上–delete-master-logs 则清除以前的日志，以释放空间。但是如果服务器配置为镜像的复制主服务器，用MySQLdump –delete-master-logs删掉MySQL二进制日志很危险，因为从服务器可能还没有完全处理该二进制日志的内容。在这种情况下，使用 PURGE MASTER LOGS更为安全。

每日定时使用 MySQLadmin flush-logs来创建新日志，并结束前一日志写入过程。并把前一日志备份，例如上例中开始保存数据目录下的日志文件 MySQL-bin.000002 , ...

  • 1.恢复完全备份
mysql -u root -p < backup_sunday_1_PM.sql
  • 2.恢复增量备份
mysqlbinlog MySQL-bin.000002 … | MySQL -u root -p

注意此次恢复过程亦会写入日志文件，如果数据量很大，建议先关闭日志功能

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------以下是在研究的时候所用的定时周一至周六的增量备份，每周日全量备份的脚本分享给大家：

增量备份脚本（mysql_dailyBak.sh）

#!/bin/sh
#增量文件存放目录每天统计
BakDir=/opt/data/mysql/backup/daily
#mysql数据存放目录
BinDir=/opt/data/binlog
BinFile=/opt/data/binlog/mysql-bin.index  #mysql数据目录下的index文件
LogFile=/opt/data/mysql/backup/bak.log  #备份日志存放目录
/usr/local/mysql/bin/mysqladmin -uroot -padmin123 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
	fi
done
echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next Bakup succ! >> $LogFile

全量备份脚本（mysql_fullBak.sh）

#!/bin/bash
# Program
#use mysqldump to Fully backup mysql data per week!
BakDir=/opt/data/mysql/backup   #备份文件保存目录
LogFile=/opt/data/mysql/backup/bak.log  #备份日志文件
Date=`date +%Y%m%d`
Begin=`date +"%Y年%m月%d日 %H:%M:%S"`
DumpFile=$Date.sql
GZDumpFile=$Date.sql.tar
cd $BakDir
/usr/local/mysql/bin/mysqldump -uroot -padmin123 --quick --databases guns --flush-logs --delete-master-logs --single-transaction > $DumpFile
/bin/tar czvf $GZDumpFile $DumpFile
/bin/rm $DumpFile
Last=`date +"%Y年%m月%d日 %H:%M:%S"`
echo 开始:$Begin 结束:$Last $GZDumpFile success >> $LogFile   #记录备份日志
rm -f $BakDir/daily/*

注意：在Windows下编辑的脚本需要转换格式：由docs转换为unix的格式，不然会报各种语法不对的问题；修改格式的方法linux中编辑vim文件时，可以用 :set ff 查看文件的格式，如果是docs就需要转换成unix，方式:set ff=unix

:set ff=unix

编写完后的脚本可以采用sh -n file.sh查看脚本语法是否正确;以上备份文件时需要提前创建目录/opt/data/mysql/backup/daily；并且赋予目录操作权限；简单的命名：chmod -R 777 backup

sh -n mysql_fullBak.sh

设置定时linux定时器crontab

crontab -e

定时任务内容编辑：每周日一点全量备份一次，每周一至周六一点增量备份一次；

0 1 * * 0 /bin/bash -x /opt/data/mysql/sh/mysql_fullBak.sh >/dev/null 2>&1
0 1 * * 1-6 /bin/bash -x /opt/data/mysql/sh/mysql_dailyBak.sh >/dev/null 2>&1

 

 

4.导入数据库
数据库的导入有三种方式：source命令、mysql、gunzip命令

（1）source 命令

mysql -u root -p
mysql>use 数据库
mysql>source /home/work/db/bkdb.sql

（2）mysql

mysql -hhostname -uusername -ppassword databasename < backupfile.sql

（3）gunzip

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename