备份"/>
MYSQL数据库导出和备份
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 ]thenecho $base skip! >> $LogFileelsedest=$BakDir/$baseif (test -e $dest) #test -e用于检测目标文件是否存在,存在就写exist!到$LogFile去。thenecho $base exist! >> $LogFileelsecp $BinDir/$base $BakDirecho $base copying >> $LogFilefifi
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
转载链接:
更多推荐
MYSQL数据库导出和备份
发布评论