mysqldump详解

mysqldump用来逻辑导出数据库，基本用法：

[root@mysql1 ~]# mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

For more options, use mysqldump --help

注意：使用mysqldump备份数据库的时候会锁库和锁表，因此使用的时候要特别注意

mysqldump可以在客户端使用，当在客户端使用的时候生产的备份文件存放在客户端

下面是一些基本使用方法

一、备份brent数据库、使用socket的连接方式，如果不写，默认就是sock方式：

注意：如果不显试的指定--databases参数，那么生成的sql语句中没有create database语句，

如果使用了--databases参数，那么生成的sql语句中包含create database语句

[root@mysql1 ~]# mysqldump -u root -S /tmp/mysql.sock brent >brent.sql

或者使用IP、端口的方式:

[root@mysql1 ~]# mysqldump -u root -p -h 127.0.0.1 -P 3306 --default-character-set=gbk brent >brent.sql

二、备份多个数据库指定--databases参数

[root@mysql1 ~]# mysqldump -u root -h 127.0.0.1 -P 3306 --default-character-set=gbk  --databases brent test >bt.sql

三、备份所有数据库，默认情况下information_schema是不会被导出的

[root@mysql1 ~]# mysqldump -u root -h 127.0.0.1 -P 3306 --default-character-set=gbk--all-databases >all.sql

四、单独备份brent数据库下的t2表，多表直接在后面写上

[root@mysql1 ~]# mysqldump -u root -h 127.0.0.1 -P 3306 --default-character-set=gbk--tables brent t2>t2.sql

五、单独备份brent数据库下的t2表结构

[root@mysql1 ~]# mysqldump -u root -h 127.0.0.1 -P 3306 --default-character-set=gbk--no_data --tables brent t2 >t2.sql

六、只导出数据，不导出结构

[root@mysql1 ~]# mysqldump -u root -h 127.0.0.1 -P 3306 --default-character-set=gbk --no-create-info --tables brent t4  >t4.sql

七 、只备份brent数据库下的t2表的部分数据

[root@mysql1 ~]# mysqldump -u root -h 127.0.0.1 -P 3306 --default-character-set=gbk --no-create-db --no-create-info --tables brent t4 --where "id=3" >t4.sql

八、备份存储过程和函数

[root@mysql1 ~]# mysqldump -u root -h 127.0.0.1 -P 3306 -R --all-databases >all.sql               --R即为routines

九、将导出的数据自定义输出，其实就是调用的into outfile语句

将会在备份目录下单独生成表的结构语句和数据语句，结构语句文件名为表名.sql、数据文件名为表名.txt

[root@mysql1 ~]# mysqldump -u root --tab=/mydata/test brent

[root@mysql1 test]# pwd
/mydata/test
[root@mysql1 test]# ls

t1.sql  t1.txt  t2.sql  t2.txt  t3.sql  t3.txt  t4.sql  t4.txt

由于调用的outfile，因此也支持outfile的一些命令选项：

--fields-terminated-by：指定分界符，默认tab

--fields-enclosed-by：指定列的包括符

--fields-optionally-enclosed-by：指定非数字列的包括符

--fields-escaped-by：指定转义符，默认\

--lines-terminated-by：指定换行符

例如备份brent.t4这张表，并且指定备份出来的格式：

[root@mysql1 ~]# mysqldump -u root --tab=/mydata/test brent t4 --fields-terminated-by=',' --fields-enclosed-by='"'

[root@mysql1 test]# cat t4.txt
"1","a"
"2","b"
"3","c"

"4","d"

其它的参数：

-f --force：遇到sql错误也继续执行

-F --flush-logs：导出之前先刷新日志，一般全库导出的时候先刷新日志文件

-e --extended-insert：使用多行insert语句，这种sql并不通用，如果要将sql支持其它数据库中运行，将此参数设置为false

 [root@mysql1 ~]# mysqldump -u root -h 127.0.0.1 -P 3306 --default-character-set=gbk --no-create-info--extended-insert=false --tables brent t4  >t4.sql

-l --lock-tables：锁定读取表对象，默认启用

-single-transaction：支持innodb，导出时建立一致性快照

-x --lock-all-tables：锁定所有表，相当于一个全局锁，备份期间所有读写都无法操作，慎用！

-k --disable-keys：导出的文件中包含两段disable key和enable key，目的是导入的时候先禁用索引，导入后再启用，默认此参数是启用

-n --no-create-db：不生成建库的脚本，即使指定的--databases和--all-databases参数

--ignore-table：指定表对象不导出，如果有多个要指定多个参数

[root@mysql1 ~]# mysqldump -u root -p --databases brent --ignore-table brent.t1 --ignore-table brent.t2 >brent.sql    

全库的备份常用命令：

mysqldump -uroot -p -A -R -x --default-character-set=utf8 |gzip > /data/dbfull_`date  "+%Y%m%d%H%M%S"`.sql.gz

这个执行期间会锁全库，或者使用

mysqldump -uroot -p -A -R --single-transaction --default-character-set=utf8 |gzip > /data/dbfull_`date  "+%Y%m%d%H%M%S"`.sql.gz

恢复数据库

1.使用mysql的重定向命令，将生成的sql脚本重定向到mysql命令行中

[root@mysql1 ~]# mysql -u root -p <all.sql

2.使用source命令，类似于oracle sqlplus中的@

(root@localhost)[(none)]> source all.sql