300字范文 > MYSQL数据库导出和备份----mysqldump

MYSQL数据库导出和备份----mysqldump

时间：2021-07-17 10:27:48

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日志功能，在f中加入

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/binlogBinFile=/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 >> $LogFilefifidoneecho `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.sqlGZDumpFile=$Date.sql.tarcd $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 $DumpFileLast=`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>&10 1 * * 1-6 /bin/bash -x /opt/data/mysql/sh/mysql_dailyBak.sh >/dev/null 2>&1