MySQL数据备份之mysqldump使用__mysqldump导出数据

相关文章推荐

心软的钥匙扣 · mysql查询json数组不为空_mob64 ...· 1 月前 ·

宽容的凉茶 · Timer.Change 方法 ...· 1 年前 ·

从容的炒面 · Easy Install "Content ...· 1 年前 ·

重感情的煎鸡蛋 · Internationalization ...· 1 年前 ·

茫然的登山鞋 · excel生成递增的随机数-掘金· 1 年前 ·

1、各种用法说明

A. 最简单的用法：

mysqldump -uroot -pPassword [database name] > [dump file]

上述命令将指定数据库备份到某dump文件（转储文件）中，比如：

mysqldump -uroot -p123 test > test.dump

生成的test.dump文件中包含建表语句（生成数据库结构哦）和插入数据的insert语句。

B. --opt

如果加上--opt参数则生成的dump文件中稍有不同：

. 建表语句包含drop table if exists tableName

. insert之前包含一个锁表语句 lock tables tableName write，insert之后包含unlock tables

C. 跨主机备份

使用下面的命令可以将host1上的sourceDb复制到host2的targetDb，前提是host2主机上已经创建targetDb数据库：

mysqldump --host=host1 --opt sourceDb| mysql --host=host2 -C targetDb

-C指示主机间的数据传输使用数据压缩

D. 只备份表结构

mysqldump  --databases mydatabase1 mydatabase2 mydatabase3 > test.dump

将只备份表结构。 --databases指示主机上要备份的数据库。如果要备份某个MySQL主机上的所有数据库可以使用-- all-databases选项，如下：

mysqldump --all-databases> test.dump

E. 从备份文件恢复数据库

mysql [database name] < [backup file name]

2、结合Linux的cron命令实现定时备份

比如需要在每天凌晨1:30备份某个主机上的所有数据库并压缩dump文件为gz格式，那么可在/etc/crontab配置文件中加入下面代码行：

30 1 * * *

前面5个参数分别表示分钟、小时、日、月、年，星号表示任意。date '+%m-%d-%Y'得到当前日期的MM-DD-YYYY格式。

3、一个完整的Shell脚本备份MySQL数据库示例

#vi /backup/backup.sh
#!bin/bash
cd /backup
echo "You are in backup dir"
mv backup* /oldbackup
echo "Old dbs are moved to oldbackup folder"
echo "Your database backup successfully completed"

上面脚本文件保存为backup.sh，并且系统中已经创建两个目录/olcbackup和/backup。每次执行backup.sh时都会先将/backup目录下所有名称为backup开头的文件移到/oldbackup目录。

为上述脚本制定执行计划如下：

#crontab -e30 1 * * * /backup.sh

4、 mysqldump全量备份+mysqlbinlog二进制日志增量备份

从mysqldump备份文件恢复数据会丢失掉从备份点开始的更新数据，所以还需要结合mysqlbinlog二进制日志增量备

份。确保my.ini或者my.cnf中包含下面的配置以启用二进制日志，或者mysqld ---log-bin：

[mysqld]
log-bin=mysql-bin

mysqldump命令必须带上 --flush-logs选项以生成新的二进制日志文件：

mysqldump --single-transaction --flush-logs --master-data=2 > backup.sql

这样生成的增量二进制日志文件比如为mysql-bin.000003，那么恢复数据时如下：

1 2	`shell> mysql -uroot -pPwd < backup_sunday_1_PM.sql` `shell> mysqlbinlog mysql-bin.000003 \| mysql -uroot -pPwd`

此外mysqlbinlog还可以指定 --start-date、--stop-date、--start-position和--stop-position参数，用于精确恢复数据到某个时刻之前或者跳过中间某个出问题时间段恢复数据，直接摘录MySQL文档说明中相关内容如下：

5.9.3.1. 指定恢复时间
对于MySQL 4.1.4，可以在mysqlbinlog语句中通过--start-date和--stop-date选项指定DATETIME格式的起止时间。举例说明，假设在今天上午10:00(今天是2005年4月20日)，执行SQL语句来删除一个大表。要想恢复表和数据，你可以恢复前晚上的备份，并输入：
该命令将恢复截止到在--stop-date选项中以DATETIME格式给出的日期和时间的所有数据。如果你没有检测到几个小时后输入的错误的SQL语句，可能你想要恢复后面发生的活动。根据这些，你可以用起使日期和时间再次运行mysqlbinlog：
在该行中，从上午10:01登录的SQL语句将运行。组合执行前夜的转储文件和mysqlbinlog的两行可以将所有数据恢复到上午10:00前一秒钟。你应检查日志以确保时间确切。下一节介绍如何实现。
5.9.3.2. 指定恢复位置
也可以不指定日期和时间，而使用mysqlbinlog的选项--start-position和--stop-position来指定日志位置。它们的作用与起止日选项相同，不同的是给出了从日志起的位置号。，特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号，可以运行mysqlbinlog寻找执行了不期望的事务的时间范围，但应将结果重新指向文本文件以便进行检查。操作方法为：
该命令将在/tmp目录创建小的文本文件，将显示执行了错误的SQL语句时的SQL语句。你可以用文本编辑器打开该文件，寻找你不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作，应进行注释。用log_pos加一个数字来标记位置。使用位置号恢复了以前的备份文件后，你应从命令行输入下面内容：
上面的第1行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为mysqlbinlog的输出包括每个SQL语句记录之前的SET TIMESTAMP语句，恢复的数据和相关MySQL日志将反应事务执行的原时间。

1.mysqldump的几种常用方法：

（1）导出整个数据库(包括数据库中的数据）

mysqldump -u username -p dbname > dbname.sql

（2）导出数据库结构（不含数据）

mysqldump -u username -p -d dbname > dbname.sql

（3）导出数据库中的某张数据表（包含数据）

mysqldump -u username -p dbname tablename > tablename.sql

（4）导出数据库中的某张数据表的表结构（不含数据）

mysqldump -u username -p -d dbname tablename > tablename.sql

2.mysqldump常用参数说明：

–all-databases , -A 导出全部数据库 mysqldump -uroot -p –all-databases

–all-tablespaces , -Y 导出全部表空间。

mysqldump -uroot -p –all-databases –all-tablespaces

–no-tablespaces , -y不导出任何表空间信息。

mysqldump -uroot -p –all-databases –no-tablespaces

–add-drop-database 每个数据库创建之前添加drop数据库语句。

mysqldump -uroot -p –all-databases –add-drop-database

–add-drop-table 每个数据表创建之前添加drop数据表语句。(默认为打开状态，使用–skip-add-drop-table取消选项 )

mysqldump -uroot -p –all-databases (默认添加drop语句)

mysqldump -uroot -p –all-databases –skip-add-drop-table (取消drop语句)

–add-locks在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态，使用– skip-add-locks取消选项 )

mysqldump -uroot -p –all-databases (默认添加LOCK语句)

mysqldump -uroot -p –all-databases –skip-add-locks (取消LOCK语句)

–comments附加注释信息。默认为打开，可以用–skip-comments取消

mysqldump -uroot -p –all-databases (默认记录注释)

mysqldump -uroot -p –all-databases –skip-comments (取消注释)

–compact导出更少的输出信息(用于调试)。去掉注释和头尾等结构。可以使用选项：–skip-add-drop-table –skip-add-locks –skip-comments –skip-disable-keysmysqldump -uroot -p –all-databases –compact

–complete-insert, -c使用完整的insert语句(包含列名称)。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。

mysqldump -uroot -p –all-databases –complete-insert

–compress, -C在客户端和服务器之间启用压缩传递所有信息

mysqldump -uroot -p –all-databases –compress

–databases, -B导出几个数据库。参数后面所有名字参量都被看作数据库名。

mysqldump -uroot -p –databases test mysql

–debug输出debug信息，用于调试。默认值为：d:t:o,/tmp/mysqldump.tracemysqldump -uroot -p –all-databases –debug

mysqldump -uroot -p –all-databases –debug=” d:t:o,/tmp/debug.trace”

–debug-info输出调试信息并退出mysqldump -uroot -p –all-databases –debug-info

–default-character-set设置默认字符集，默认值为utf8

mysqldump -uroot -p –all-databases –default-character-set=latin1

–delayed-insert采用延时插入方式（INSERT DELAYED）导出数据

mysqldump -uroot -p –all-databases –delayed-insert

–events, -E导出事件。mysqldump -uroot -p –all-databases –events

–flush-logs开始导出之前刷新日志。请注意：假如一次导出多个数据库(使用选项–databases或者–all-databases)，将会逐个数据库刷新日志。除使用–lock-all-tables或者–master-data外。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。因此，

如果打算同时导出和刷新日志应该使用–lock-all-tables 或者–master-data 和–flush-logs 。

mysqldump -uroot -p –all-databases –flush-logs

–flush-privileges在导出mysql数据库之后，发出一条FLUSH PRIVILEGES 语句。为了正确恢复，该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。

mysqldump -uroot -p –all-databases –flush-privileges

–force在导出过程中忽略出现的SQL错误。mysqldump -uroot -p –all-databases –force

–host, -h需要导出的主机信息 mysqldump -uroot -p –host=localhost –all-databases

–ignore-table不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：–ignore-table=database.table1 –ignore-table=database.table2 …… mysqldump -uroot -p –host=localhost –all-databases –ignore-table=mysql.user

–lock-all-tables, -x提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭–single-transaction 和–lock-tables 选项。

mysqldump -uroot -p –host=localhost –all-databases –lock-all-tables

–lock-tables, -l开始导出前，锁定所有表。用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，–single-transaction是一个更好的选择，因为它根本不需要锁定表。请注意当导出多个数据库时，–lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。mysqldump -uroot -p –host=localhost –all-databases –lock-tables

–no-create-db, -n只导出数据，而不添加CREATE DATABASE 语句。

mysqldump -uroot -p –host=localhost –all-databases –no-create-db

–no-create-info, -t只导出数据，而不添加CREATE TABLE 语句。

mysqldump -uroot -p –host=localhost –all-databases –no-create-info

–no-data, -d不导出任何数据，只导出数据库表结构。

mysqldump -uroot -p –host=localhost –all-databases –no-data

–password, -p连接数据库密码

–port, -P连接数据库端口号

–user, -u指定连接的用户名。

3.mysqldump常用实例：

mysqldump常用于数据库的备份与还原，在备份的过程中我们可以根据自己的实际情况添加以上任何参数，假设有数据库test_db，执行以下命令，即可完成对整个数据库的备份：

mysqldump -u root -p test_db > test_db.sql

如要对数据进行还原，可执行如下命令：

mysql -u username -p test_db < test_db.sql

还原数据库操作还可以使用以下方法：

mysql> sourcetest_db.sql

数据库的基本导入\导出的命令是 mysqldump 和 source

在linux下直接用命令行操作就可以在windows下一般情况下有两种方法一个也是用命令行另一个是用phpmyadmin

先来说说 phpmyadmin 这个工具导出和导入很简单而且导入时无需建库直接还原成原来的数据库

用source 也可以还原但他导入文件时有大小限制不能超过2M

再来说说 mysqldump 和 source 用命令操作很快但是想把导出的文件再导入时必须先建立一个数据库(这个库可以随便起名) 然后进入数据库后才能导入用phpmyadmin导入mysqldump 导出的文件也得需要这步

下面是从前辈那copy来的命令具体使用方法

1.导出整个数据库

mysqldump -u 用户名 -p 数据库名 > 导出的文件名

mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql

2.导出一个表

mysqldump -u 用户名 -p 数据库名表名> 导出的文件名

mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql

3.导出一个数据库结构

mysqldump -u wcnc -p -d --add-drop-table smgp_apps_wcnc >d:wcnc_db.sql

-d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table

备忘: mysqldump在linux下可直接使用在windows下有时需要进入mysql/bin中使用因为有时用的是类似appserv的套装软件这样的话命令就不能直接使用因为这些命令没在环境变量的目录里而且用 mysqldump导出的备份必须先建立数据库才能导入

4.导入数据库常用source 命令

进入mysql数据库控制台，

如mysql -u root -p

mysql>use 数据库

然后使用source命令，后面参数为脚本文件(如这里用到的.sql)

mysql>source d:wcnc_db.sql

存疑: phpmyadmin导入有大小限制不知道source导入时有没限制而且导出时是不可以限制文件大小并且分数个文件导出

wk_ad_begin({pid : 21});wk_ad_after(21, function(){$('.ad-hidden').hide();}, function(){$('.ad-hidden').show();});

mysqldump是备份mysql数据库的一种好工具。它相对于用phpmyadmin等备份工具更加快速，又避免受php.ini等的限制，在windows系统下还可以结合计划任务实现定时远程备份数据库。

操作用mysqldump备份数据库的用户需要的权限比较小，只需要select和lock table的权限就足够了。所以可以创建一个备份数据库的用户，专门用于备份数据库，这样可以避免数据库受到意外的破坏。在备份数据库之前我们要做足准备功夫，首先创建了执行备份操作的数据库用户——backup，密码为：123456。其次，确定我们要备份哪个数据库，放在哪里。然后，确定数据库的备份文件要放到哪里。假设我们的任务是要备份一个位于192.168.1.2服务器上，名称为backup_test的数据库，并把备份文件放到D:\bak\的文件夹中。又假设我们的操作系统为windows xp，于是我们可以用以下的方法执行备份操作：

我们可以使用windows自带的命令提示符（调用命令提示符的方法有多种，最常用的就是点击开始菜单，选择―运行‖，输入cmd的命令就可以启动了），在命令提示符中输入以下命令：

mysqldump -ubackup -p123456 -h 192.168.1.2 backup_test > D:\bak\bakcup.sql 在这段命令中：

mysqldump 是myqldump的程序名了；

-u 是数据库的用户名，后面紧跟着用户名backup；

-p 是数据库的密码，后面同样紧跟着密码，注意是-p和密码之间不能有空格； -h 是数据库的地址，如果没有此项表明是备份本地的数据库； backup_test 要备份的数据库名称；

> 表明该数据库备份到后面的文件，紧跟当然是备份文件的地址了，注意要用绝对文件路径，文件的后缀也可以用.txt。

一旦执行该命令后，mysqldump立即执行备份操作。远程备份数据库要似乎网络的情况，一般远程备份需要一定的时间的。

======================================= 备份MySQL数据库的命令

mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql

备份MySQL数据库为带删除表的格式

备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库。

mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql

直接将MySQL数据库压缩备份

mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

备份MySQL数据库某个(些)表

mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql

同时备份多个MySQL数据库

mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql

仅仅备份数据库结构

mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql

备份服务器上所有数据库

mysqldump –all-databases > allbackupfile.sql

还原MySQL数据库的命令

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

还原压缩的MySQL数据库

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

将数据库转移到新服务器

mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename ======================================================

这种更灵活,导出

select * from table_name into outfile 'c:\\path\\filename.txt '

导入:LOAD DATA LOCAL INFILE 'file_name.txt' INTO TABLE tbl_name

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=..

wk_ad_begin({pid : 21});wk_ad_after(21, function(){$('.ad-hidden').hide();}, function(){$('.ad-hidden').show();});

从命名的主机上的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

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