mysql数据库备份及数据的导入导出

常用的备份方式及特性

  • mysqldump (备份方式灵活、恢复时业务不停、备份速度慢)
  • xtrabackup（物理、恢复时业务需停、备份速度快）
  • mydumper（逻辑）
  • mysqlbackup Enterprise (no lock)

mysqldump

  1. 备份的方式的粒度（库、表）
  2. 备份需要注意的事项
    备份锁表，影响业务
    对I/O,CPU,内存要求高
    备份会不一致(一定要加single-transaction参数)
  3. 缺点
    花费时间比物理备份的二进制文件复制长
    需要的存储空间更大
    数据量大时恢复时间长
    如果经常要备份，并希望在系统出现故障后快速恢复，或者需要通过网络传输备份文件，那么时间成本会非常大

使用mysqldump可以备份所有数据库，特定的数据库，特定的数据集合，甚至还可以备份指定数据库中的某些表

shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> mysqldump [options] --all-databases

选项

作用

–add-drop-database

在每个数据库前包含一个 DROP DATABASE 语句

–add-drop-table

在每个表之前包含一个 DROP TABLE 语句

–add-locks

在被包含的表前面加上 LOCK TABLES ，后面加上UNLOCK TABLES

–all-databases

包含所有数据库

–create-options

在create table语句中包含所有特定于mysql的表选项

–databases

只包含数据库的列表

–delete-master-logs

在master上，在执行备份后删除二进制日志

–events

备份被包含的数据库中的事件

–extend-insert

另一种将所有的记录作为VALUES字句的insert语法

–flush-logs

在开始备份前刷新日志文件

–flush-privileges

在备份mysql数据库后包含一个flush privileges 语句

–ignore-table

不备份指定的表

–lock-all-tables

在转储过程中锁定所有数据库中的所有表

–lock-tables

锁定所有表

–log-error=filename

将警告和错误追加到指定文件中

–replace

重复数据先删除在插入

–no-create-db

不要创建数据库语句

–no-create-info

不要编写重新创建每个转储表的CREATE TABLE语句

–set-gtid-purged

恢复时报错，将gtid忽略

–skip-add-drop-table

不要在每个CREATE TABLE语句之前添加DROP TABLE语句

–no-data

不写入任何表的行信息（只包含CREATE TABLE语句）

–quick

–skip-quick

业务不繁忙，数据量小，基本没有差别

–single-transaction

在备份开始时发出begin语句，指示innodb存储引擎以一致性读的方式读取表，数据备份时被冻结，不能使用DDL语句

–master-data=2

结果中包含二进制日志文件名及其位置（CHANGE master），1的时候语句就不是注释状态

–skip-lock-tables

–result-file

结果输出到指定文件

–triggers

包含触发器

–routines

包含存储程序（过程和函数）

–where=‘condition’

只包含在condition中被选中的行

–xml

生成XML输出结果

注意
由于mysqldump备份是逻辑的，还原数据库或者表的时候，有可能数据太大导致报错
sqlcmd
sqldumpsplitter这个工具可以将data数据切分成多个，在导入即可

mysqldump备份脚本

#!/bin/bash
USER=""	#数据库用户
PASS=""	#数据库用户密码
HOST=""	#数据库主机IP

DEST="" #备份路径

# 删除几天之前的备份文件
DAYS=3

# 命令位置
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"

# 格式化输出时间格式
NOW="$(date +"%Y-%m-%d_%s")"

# 创建目录
MBD="$DEST/$NOW/mysql"
install -d $MBD

# 跳过哪些数据库的备份
SKIP="information_schema
mysql"

# 得到目标机器上所有数据的名字
DBS="$($MYSQL -h $HOST -u $USER -p$PASS -Bse 'show databases')"

# 备份数据库,循环比对数据库名字在不在$SKIP中，如在其中就不备份
for db in $DBS
do
    skipdb=-1
    if [ "$SKIP" != "" ];
    then
		for i in $SKIP
		do
			[ "$db" == "$i" ] && skipdb=1 || :
		done
    fi

    if [ "$skipdb" == "-1" ] ; then
    	FILE="$MBD/$db.sql"
		$MYSQLDUMP -h $HOST -u $USER -p$PASS $db > $FILE
    fi
done

# 压缩备份目录
cd $DEST
tar -cf $NOW.tar $NOW
$GZIP -9 $NOW.tar

rm -rf $NOW

# 删除$DAYS天前的数据
find $DEST -mtime +$DAYS -exec rm -f {} \;