MySQL之——基于mysqldump全量备份还原

转载请注明出处：http://blog.csdn.net/l1028386804/article/details/78118980

SQL格式备份还原
不带--tab选项时，mysqldump输出sql格式的标准输出，包含CREATE及INSERT语句。
基本格式为：

mysqldump [arguments] > file_name

1、 导出全部数据库及表

[root@localhost /]# mysqldump --all-databases > /tmp/dump.sql

导出部分数据库及表

[root@localhost /]# mysqldump --databases db1 db2 db3 > /tmp/dump.sql

导出单个数据库可以用如下两种格式

[root@localhost /]# mysqldump --databases test > /tmp/dump.sql
[root@localhost /]# mysqldump test > /tmp/dump.sql

区别是使用--databases在导出数据时会加上CREATE DATABASE 或 USE 语句，不使用--databases参数在导入数据时需要先指定一个存在的数据库作为默认数据库导入数据。
导出指定表

[root@localhost /]# mysqldump test t1 t3 t5 > /tmp/dump.sql
[root@localhost /]# mysqldump --tables test t1 t3 t5 > /tmp/dump.sql

2、导入SQL格式备份文件

如果导出文件时指定了--all-databases 或 --databases选项，备份文件会包含CREATE DATABASE 和 USE语句，我们直接导入即可，无需预先创建库和指定库。以下两种方式均可。

[root@localhost /]# mysql < /tmp/dump.sql (方法1：shell命令行操作)
mysql> source /tmp/dump.sql              （方法2：进入mysql数据库操作）

如果导出文件时未指定了--all-databases 或 --databases选项，备份文件不包含CREATE DATABASE 和 USE语句，导入数据前我们需要预先创建库和指定库。
方法1：shell命令行下操作

[root@localhost /]# mysqladmin create db1
[root@localhost /]# mysql db1 < /tmp/dump.sql

方法2：进入mysql数据库操作

mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> source dump.sql

3、文件分隔符格式备份还原

带--tab选项时，mysqldump为每个表输出两种文件到tab指定的路径下，分别是带CREATE TABLE语句的形如tbl_name.sql的文件，存放表结构，以及制表符分割的形如tbl_name.txt的数据文件。数据文件本质上是通过SELECT ... INTO OUTFILE语句写入数据到文件的。
该备份方式最好在本地执行，如果client在远程执行的话，client端和server端需要有相同路径的备份目录，且.sql文件记录在client端，.txt文件记录在server端。

3.1 导出tab格式备份文件

默认格式导出
这种方式导出的文件，以制表符（tab空格）作为列的分割，且列字段没有引号包裹，以换行作为一行的结束。

[root@localhost /]# mysqldump --tab=/tmp db1

指定格式导出
我们可以通过参数控制导出数据文件的格式，参数分别为：
--fields-terminated-by=str：隔开字段的字符，默认为tab制表符。
--fields-enclosed-by=char：包裹字段的符号（包裹所有字段），默认没有符号。
--fields-optionally-enclosed-by=char：包裹字段的符号（包裹除数字类型之外的所有字段），默认没有符号。
--fields-escaped-by=char：指定字符对特殊字符进行转义，例如--fields-enclosed-by指定为双引号，而字段内容包含双引号，就需要一个字符如#或其他，对字段内的双引号进行转义。
--lines-terminated-by=str：一行的终止，默认为换行符。
例如：需要导出的表信息为

mysql> select * from db.dbtable;
+------+-------------+--------------+
| c1   | c2          | c3           |
+------+-------------+--------------+
|    1 | test, comma | test " quote |
+------+-------------+--------------+

导出指定格式数据表命令

[root@localhost /]# mysqldump --tab=/tmp --fields-terminated-by=',' --fields-enclosed-by=0x22 --fields-escaped-by='#' --lines-terminated-by='\n' db1
[root@localhost tmp]# cat dbtable.txt 
"1","test, comma","test #" quote"

或者

[root@localhost /]# mysqldump --tab=/tmp --fields-terminated-by=',' --fields-optionally-enclosed-by='"' --fields-escaped-by='#' --lines-terminated-by='\n' db1
[root@localhost tmp]# cat dbtable.txt 
1,"test, comma","test #" quote"

注：我们可以用十六进制代替参数所指定的值。--fields-optionally-enclosed-by选项对数字不包裹。

3.2导入tab格式备份文件

大体步骤：先导入sql文件，再导入txt文件。
对默认格式的导入：

[root@localhost /]# mysql db1 < /tmp/t1.sql
[root@localhost /]# mysqlimport db1 /tmp/t1.txt

对指定格式的导入：

[root@localhost /]# mysql db1 < /tmp/t1.sql    
[root@localhost /]# mysqlimport --fields-terminated-by=',' --fields-enclosed-by='"' --fields-escaped-by='#' --lines-terminated-by='\n' db1 t1.txt

或者

mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1 FIELDS FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '#'  LINES TERMINATED BY '\n';

3.3 填充数据至另一数据库

[root@localhost /]# mysqldump  db1 | mysql -h192.168.1.100 -P3306 -ujesse -pjesse  -C db2

4 mysqldump一些使用技巧

4.1 在线热备份

使用参数 --single-transaction，适用于InnoDB表，与--lock-tables参数互斥，备份期间不锁表。为确保得到有效的备份文件，使用该参数备份期间应避免使用DDL（ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE.）语句，因为连续性的读并没有对这些语句进行隔离，备份期间使用这些DDL语句会导致潜在的select获取到的返回的数据不一致或错误，比如数据读出一半表被删了。
参考实例：

[root@localhost /]# mysqldump --all-databases --single-transaction --master-data=1 --flush-logs --events > /tmp/dump.sql

 --flush-logs：全备前刷新所有日志到binlog文件，并创建一个新的binlog文件，用于增备。
--master-data：该参数有两个值1和2，默认为1，mysqldump导出数据时，当这个参数的值为1的时候，mysqldump出来的文件就会包括CHANGE MASTER TO这个语句，CHANGE MASTER TO后面紧接着就是file和position的记录，在slave上导入数据时就会执行这个语句，salve就会根据指定这个文件位置从master端复制binlog。当这个值是2的时候，chang master to也是会写到dump文件里面去的，但是这个语句是被注释的状态。

4.2 备份存储过程触发器及事件

--events: 事件调度，默认未开启，显示关闭使用--skip-events
--routines: 存储过程及函数, 默认未开启，显示关闭使用--skip-routines
--triggers: 触发器，默认为开启，关闭使用--skip-triggers

4.3 只备份表结构或数据

--no-data：只导出建表语句。
--no-create-info：只导出数据，不导出表结构。

[root@localhost /]# mysqldump --no-data --routines --events testdb > /tmp/dump.sql
[root@localhost /]# mysqldump --no-create-info  testdb > /tmp/dump.sql

4.4 备份压缩

参考实例：
压缩备份文件，并以当前时间戳命名

[root@localhost /]# mysqldump --all-databases --single-transaction --master-data=1 --flush-logs --events | gzip > /tmp/dump_`date '+%Y-%m-%d_%H:%M:%S'`.sql.gz

5 常见问题及注意事项

5.1 对备份目录没有写权限

[root@localhost tmp]# mysqldump -uroot -p******  --tab=/tmp/test/  db1 
mysqldump: Got error: 1: Can't create/write to file '/tmp/test/dump.txt' (Errcode: 13 - Permission denied) when executing 'SELECT INTO OUTFILE'

 解决办法：    
让other用户也能够写。

[root@localhost /]# chmod 757 /tmp/test/ 

或者把目标目录修改为mysql组所有

[root@localhost tmp]# chown mysql:mysql /tmp/test/ 

5.2 执行备份时磁盘空间不足

[root@localhost /]# mysqldump --tab=/tmp/test -uroot -p****** db1
mysqldump: Error: 'Got error 28 from storage engine' when trying to dump tablespaces
mysqldump: Couldn't execute 'show fields from `handler_table`': Got error 28 from storage engine (1030)

出现此问题的原因：临时空间不够，无法执行此SQL语句
实际案例：
查了一下，数据库文件所在的盘应该没事，应该是数据库用的临时目录空间不够

[root@localhost /]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                       64G   64G     0 100% /
tmpfs                 128G     0  128G   0% /dev/shm
/dev/sda1             190M   68M  113M  38% /boot

解决办法：清理磁盘空间。

5.3 远程备份，client端与server端目录不一致

client端（没有/tmp/test目录）

[root@localhost /]# mysqldump --tab=/tmp/test -uroot -p -h192.168.1.200 -P3355 test
Enter password: 
mysqldump: Can't create/write to file '/tmp/test/handler_table.sql' (Errcode: 2)
mysqldump: Can't create/write to file '/tmp/test/handler_table.sql' (Errcode: 2)

 解决办法：
在client端创建目录/tmp/test（使用mysqldump进行tab格式远程备份时，client端存放sql表定义文件，server端存放列式数据文件，两端要存在相同的备份目录）