linux下改良版本mysqldump来备份MYSQL数据库

因为MYSQLDUMP会为整个库来加一个全局锁定。

如果单纯用MYSQLDUMP进行全库备份会造成以下三个方面的影响。

1、服务器CPU严重阻塞。

2、磁盘I/O直线增加。

3、所有的查询都成了慢查询。

我现在的网站数据库大概是5个G左右，而且每天都有增大。

表结构是MYISAM,INNODB,MEMORY三者混合。

所以单纯用HOTCOPY工具恐怕有点困难。所以我今天简单变了一下我上次写的关于用OUTFILE来备份MYSQL的脚本。

可以解决上面说的三个缺点。

1、备份脚本内容

[david_yeung@localhost ~]$ cat fast_backup

#!/bin/sh

#

# Created by david yeung.

#

# 20080707.

#

# Backup mysql's full data.

#

DBNAME=$1

BACKUPDIR=/home/david_yeung/backup_new

USERNAME=backup_user

PASSWD=123456

TARNAME="$BACKUPDIR"/backup"$1"`date '+%Y%m%d'`

# Add your own database name here.

case "$1" in

my_site)

*) exit

esac

# Get all the tables' name.

NUM=`/usr/local/mysql/bin/mysql -u$USERNAME -p$PASSWD -s -vv -e "show tables" -D $DBNAME|wc -l`

HEADNUM=`expr ${NUM} - 3`

TAILNUM=`expr ${NUM} - 7`

ARR1=`/usr/local/mysql/bin/mysql -u$USERNAME -p$PASSWD -s -vv -e "show tables" -D $DBNAME| head -n"$HEADNUM" | tail -n "$TAILNUM"`

ARR2=($ARR1)

i=0

while [ "$i" -lt "${#ARR2[@]}" ]

do

tmpFileName=${ARR2[$i]}

# The real dump process.

/usr/local/mysql/bin/mysqldump -u$USERNAME -p"$PASSWD" "$DBNAME" "$tmpFileName" >>"$TARNAME"

let "i++"

done

2、因为我们一直用存储过程，所以得单独备份出来。

[david_yeung@localhost ~]$ cat fast_sp

#!/bin/sh

# Created by david yeung 20080122.

#

# Backup site's routine.

TARNAME=/home/david_yeung/backup_new/spBackup"$1"`date '+%Y%m%d'`

/usr/local/mysql/bin/mysqldump -ubackup_user -p123456 -n -t -d -R my_site >"$TARNAME"

3、丢到计划任务里面去，就不管了。

[root@localhost backup_new]# crontab -l

0 01 * * * /home/david_yeung/fast_backup my_site

0 0 * * 5 /home/david_yeung/fast_sp

每天凌晨1点备份数据，每个周五凌晨备份存储过程。

1. mysqldump： 最早，也是最成熟的逻辑备份工具，是 MySQL 原生的用来备份整个数据库实例、单个数据库、单张表的逻辑备份工具， 上手简单，学习成本几乎为 0。备份简单，恢复也简单。

比如导出单个数据库 ytt: mysqldump ytt >/tmp/ytt.sql；

恢复也非常简单：mysql </tmp/ytt.sql

缺点是备份速度慢。在整个备份过程中，是单线程运行；备份出来的数据集要恢复的话同样也是单线程运行，恢复速度也慢。除非对同一时刻的所有表单独备份出来，自己写额外脚本进行多线程恢复。

2. mysqlpump：MySQL 5.7 GA 后推出的 mysqldump工具的增强版。可以对同一个数据集多个线程并发备份，备份速度很快。

其他缺点和 mysqldump 一样。

3. MySQL Shell UTIL 对象附带的备份工具：随 MySQL 8.0.21 最新版本捆绑发布，自带多线程备份以及多线程恢复功能， 可以直接替代 mysqldump/mysqlpump。

dump_instance/dumpInstance 用来多线程备份 MySQL 整个单机实例

dump_schemas/dumpSchemas 用来多线程备份 MySQL 单个数据库

load_dump/loadDump 用来多线程恢复之前两个工具导出的数据集文件

MySQL Shell UTIL 备份恢复工具具备如下特性：

1. 压缩导出（默认压缩算法 zstd）

2. 数据集分块导出，每块 32M

3. 默认导出存储过程、存储函数、触发器、事件、用户、视图

4. 默认 4 个线程导出

5. 默认进度展示

6. 默认字符集 utf8mb4

7. 默认开启一致性导出

8. 默认表，视图等定义导出为 SQL 语句

9. 默认数据导出为 TSV 格式数据

mysqldump命令

mysql数据库导出要用到MySQL的mysqldump工具，基本用法是：

shell>mysqldump [OPTIONS] database [tables]

如果你不给定任何表，整个数据库将被导出。

通过执行mysqldump --help，你能得到你mysqldump的版本支持的选项表。

注意，如果你运行mysqldump没有--quick或--opt选项，mysqldump将在导出结果前装载整个结果集到内存中，如果你正在导出一个大的数据库，这将可能是一个问题。

1.1、mysqldump支持下列选项：

--add-locks

在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(为了使得更快地插入到MySQL)。

--add-drop-table

在每个create语句之前增加一个drop table。

--allow-keywords

允许创建是关键词的列名字。这由表名前缀于每个列名做到。

-c, --complete-insert

使用完整的insert语句(用列名字)。

-C, --compress

如果客户和服务器均支持压缩，压缩两者间所有的信息。

--delayed

用INSERT DELAYED命令插入行。

-e, --extended-insert

使用全新多行INSERT语法。（给出更紧缩并且更快的插入语句）

-#, --debug[=option_string]

跟踪程序的使用(为了调试)。

--help

显示一条帮助消息并且退出。

LOAD DATA INFILE

--fields-terminated-by=...

--fields-enclosed-by=...

--fields-optionally-enclosed-by=...

--fields-escaped-by=...

--fields-terminated-by=...

这些选择与-T选择一起使用，并且有相应的LOAD DATA INFILE子句相同的含义。

LOAD DATA INFILE语法。

-F, --flush-logs

在开始导出前，洗掉在MySQL服务器中的日志文件。

-f, --force,

即使我们在一个表导出期间得到一个SQL错误，继续。

-h, --host=..

从命名的主机上的MySQL服务器导出数据。缺省主机是localhost。

-l, --lock-tables.

为开始导出锁定所有表。

-t, --no-create-info

不写入表创建信息(CREATE TABLE语句）

-d, --no-data

不写入表的任何行信息。如果你只想得到一个表的结构的导出，这是很有用的！

--opt

同--quick --add-drop-table --add-locks --extended-insert --lock-tables。

应该给你为读入一个MySQL服务器的尽可能最快的导出。

-pyour_pass, --password[=your_pass]

与服务器连接时使用的口令。如果你不指定“=your_pass”部分，mysqldump需要来自终端的口令。

-P port_num, --port=port_num

与一台主机连接时使用的TCP/IP端口号。（这用于连接到localhost以外的主机，因为它使用 Unix套接字。）

-q, --quick

不缓冲查询，直接导出至stdout；使用mysql_use_result()做它。

-S /path/to/socket, --socket=/path/to/socket

与localhost连接时（它是缺省主机)使用的套接字文件。

-T, --tab=path-to-some-directory

对于每个给定的表，创建一个 table_name.sql文件，它包含SQL CREATE 命令，和一个table_name.txt文件，它包含数据。 注意：这只有在mysqldump运行在mysqld守护进程运行的同一台机器上的时候才工作。.txt文件的格式根据--fields-xxx和 --lines--xxx选项来定。

-u user_name, --user=user_name

与服务器连接时，MySQL使用的用户名。缺省值是你的Unix登录名。

-O var=option, --set-variable var=option设置一个变量的值。可能的变量被列在下面。

-v, --verbose

冗长模式。打印出程序所做的更多的信息。

-V, --version

打印版本信息并且退出。

-w, --where=''''where-condition''''

只导出被选择了的记录；注意引号是强制的！

"--where=user=''''jimf''''" "-wuserid>1" "-wuserid<1"

1.2、最常见的mysqldump的一个备份：

虽然mysqldump支持的命令有很多，对于大多数人而言，我们只需要使用-opt这个命令就已经足够了，为你的数据库做一个完整的备份：

mysqldump --opt database >backup-file.sql

但是它对用来自于一个数据库的信息充实另外一个MySQL数据库也是有用的：

mysqldump --opt database | mysql --host=remote-host -C database

1.3、使用mysqldump导出的文件恢复数据库

由于mysqldump导出的是完整的SQL语句，所以用mysql客户程序很容易就能把数据导入了：

shell>mysqladmin create target_db_name

shell>mysql target_db_name <backup-file.sql

就是

shell>mysql 库名 <文件名

二、使用mysqldump定时备份数据库的脚本

2.1、备份脚本

使用脚本每天定期执行数据库备份 *** 作，对每个使用mysql数据库的人来说都很有必要，这样的脚本网上有很多，这里摘抄一个朋友的脚本 dbbackup：

这个脚本每天最多只执行一次，而且只保留最近五天的备份在服务器上。

dbbackup代码:

#!/bin/bash

#This is a ShellScript For Auto DB Backup

#Powered by aspbiz

#2004-09

#Setting

#设置数据库名，数据库登录名，密码，备份路径，日志路径，数据文件位置，以及备份方式

#默认情况下备份方式是tar，还可以是mysqldump,mysqldotcopy

#默认情况下，用root(空)登录mysql数据库，备份至/root/dbxxxxx.tgz

DBName=mysql

DBUser=root

DBPasswd=

BackupPath=/root/

LogFile=/root/db.log

DBPath=/var/lib/mysql/

#BackupMethod=mysqldump

#BackupMethod=mysqlhotcopy

#BackupMethod=tar

#Setting End

NewFile="$BackupPath"db$(date +%y%m%d).tgz

DumpFile="$BackupPath"db$(date +%y%m%d)

OldFile="$BackupPath"db$(date +%y%m%d --date=''''5 days ago'''').tgz

echo "-------------------------------------------" >>$LogFile

echo $(date +"%y-%m-%d %H:%M:%S") >>$LogFile

echo "--------------------------" >>$LogFile

#Delete Old File

if [ -f $OldFile ]

then

rm -f $OldFile >>$LogFile 2>&1

echo "[$OldFile]Delete Old File Success!" >>$LogFile

else

echo "[$OldFile]No Old Backup File!" >>$LogFile

fi

if [ -f $NewFile ]

then

echo "[$NewFile]The Backup File is exists,Can''''t Backup!" >>$LogFile

else

case $BackupMethod in

mysqldump)

if [ -z $DBPasswd ]

then

mysqldump -u $DBUser --opt $DBName >$DumpFile

else

mysqldump -u $DBUser -p$DBPasswd --opt $DBName >$DumpFile

fi

tar czvf $NewFile $DumpFile >>$LogFile 2>&1

echo "[$NewFile]Backup Success!" >>$LogFile

rm -rf $DumpFile

mysqlhotcopy)

rm -rf $DumpFile

mkdir $DumpFile

if [ -z $DBPasswd ]

then

mysqlhotcopy -u $DBUser $DBName $DumpFile >>$LogFile 2>&1

else

mysqlhotcopy -u $DBUser -p $DBPasswd $DBName $DumpFile >>$LogFile 2>&1

fi

tar czvf $NewFile $DumpFile >>$LogFile 2>&1

echo "[$NewFile]Backup Success!" >>$LogFile

rm -rf $DumpFile

*)

/etc/init.d/mysqld stop >/dev/null 2>&1

tar czvf $NewFile $DBPath$DBName >>$LogFile 2>&1

/etc/init.d/mysqld start >/dev/null 2>&1

echo "[$NewFile]Backup Success!" >>$LogFile

esac

fi

echo "-------------------------------------------" >>$LogFile

2.2、放入crontab定期执行dbbackup

假定dbbackup在/root目录下。我们通过使用crontab命令，设置每天0点10分执行/root/dbbakup脚本。

1、 使用 crontab –e编辑crontab

2、 在crontab中加入：

#back for jabber database

10 0 * * * /root/dbbackup

---