mysql 备份

1. 使用mysqldump 工具

mysqldump -h hostname -u root -p db table > mysqlbak.sql 

--add-drop-table

这个选项将会在每一个表的前面加上DROP TABLE IF EXISTS语句，这样可以保证导回MySQL数据库的时候不会出错，因为每次导回的时候，都会首先检查表是否存在，存在就删除

--add-locks

这个选项会在INSERT语句中捆上一个LOCK TABLE和UNLOCK TABLE语句。这就防止在这些记录被再次导入数据库时其他用户对表进行的操作 

--full 这个选项把附加信息也加到CREATE TABLE的语句中

-l or -lock-tables 使用这个选项，导出表的时候服务器将会给表加锁。

-t or --no-create-info 

mysqldump命令不创建CREATE TABLE语句，这个选项在您只需要数据而不需要DDL（数据库定义语句）时很方便

-d or -no-data 这个选项使的mysqldump命令不创建INSERT语句。在您只需要DDL语句，不需要数据的时候，可以使用这个选项。 

--opt 此选项将打开所有会提高文件导出速度和创造一个可以更快导入的文件的选项。

等同于同时添加 –add-drop-tables –add-locking –create-option –disable-keys –extended-insert –lock-tables –quick –set-charset 选项。

–add-locking 插入语句加锁

--disable-keys 告诉 mysqldump 在 INSERT 语句的开头和结尾增加 /*!40000 ALTER TABLE table DISABLE KEYS */; 和 /*!40000 ALTER TABLE table ENABLE KEYS */; 语句，这能大大提高插入语句的速度，因为它是在插入完所有数据后才重建索引的。该选项只适合 MyISAM 表。

–extended-insert 使用包括几个VALUES列表的多行INSERT语法。这样使转储文件更小，重载文件时可以加速插入。

–quick 该选项在导出大表时很有用，它强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中

–routines 导出存储过程定义

–single-transaction 该选项在导出数据之前提交一个 BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如 InnoDB 和BDB。本选项和 –lock-tables 选项是互斥的，因为 LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用 –quick 选项。

–flush-logs 开始转储前刷新MySQL服务器日志文件。该选项要求RELOAD权限。请注意如果结合–all–database(或-A)选项使用该选项，根据每个转储的数据库刷新日志。例外情况是当使用–lock-all-tables或–master-data的时候：在这种情况下，日志只刷新一次，在所有表被锁定后刷新。如果你想要同时转储和刷新日志，应使用–flush-logs连同–lock-all-tables或–master-data。

–master-data 该选项将二进制日志的位置和文件名写入到输出中。该选项要求有RELOAD权限，并且必须启用二进制日志。如果该选项值等于1，位置和文件名被写入CHANGE MASTER语句形式的转储输出，如果你使用该SQL转储主服务器以设置从服务器，从服务器从主服务器二进制日志的正确位置开始。如果选项值等于 2，CHANGE MASTER语句被写成SQL注释。如果value被省略，这是默认动作。–master-data选项启用–lock-all-tables，除非还指定–single-transaction(在这种情况下，只在刚开始转储时短时间获得全局读锁定。又见–single-transaction。在任何一种情况下，日志相关动作发生在转储时。该选项自动关闭–lock-tables。

–force 在表转储过程中，即使出现SQL错误也继续。

备份数据库：

mysqldump -uroot -p123456 --all-databases > all.sql 

备份所有数据库

使用-tab选项将数据和表结构分开备份

mysqldump -uroot -p123456 --tab=/usr/dbak xscj

备份xscj数据库到/usr/dbak目录下，表结构和数据将被分开

mysqldump -uroot -p123456 xscj > /usr/dbak/xscj.sql

备份整个xscj数据库 数据和表结构放在一个sql文件中

mysql -uroot -p123456 xscj < /usr/dbak/xscj.sql

恢复xscj数据库

mysql -uroot -p123456 xscj < /usr/dbak/xs.sql

恢复 xscj数据库 中 xs表的结构，将会清空表中的内容

2.使用sql语句备份和恢复表数据

select into outfile   ,将数据写到一个文本文件中

use xscj;

select * from kc into outfile '/usr/local/dbak/kcbak.txt'

                  fields terminated by ',' 

                  optionally enclosed by '"'  

                  lines terminated by '\n'   

执行完后，kc表的数据就被导出到kcbak.txt 文件中

恢复时:

load data local infile '/usr/local/dbak/kcbak.txt' into table kc 

                             fields terminated by ','

                             optionally enclosed by '"'

                             lines terminated by '\n'

即可恢复数据到kc表中

在使用load data...infile 语句时注意以下几个选项：

low_priority:若指定它，则延迟语句的执行

concurrent:若指定它，则当load data正在执行时，其他线程可以同时使用该表的数据。

local:若指定它，则必须给导入的数据文件指定一个绝对路径；若未指定，则文件必须位于数据库服务器上，并且被服务器直接读取。最好放在数据库的目录下面。

replace:如果指定了它，则当文件中出现与原有行相同的唯一关键字时候，输入行会替换原有行。

ignore:如果指定它，则把与原有行有相同的唯一关键字的值的输入行跳过。

ignore number lines: 表示忽略指定文件的前几行。例如：ignore 1 lines，表示忽略第一行。

相关解释：

fields 子句有三个亚子句:

terminated by: 用来指定字段值之间的符号，例如：terminated by ',',指定逗号作为两个字段值之间的标志

enclosed by:指定包裹文件中字符值的符号。例如：enclosed by '"',表示文件中字符值放在双引号之间，若加上optionally,表示所有的值都放在双引号之间。

escaped by:用来指定转义字符。例如：escaped by'*',将*指定为转义字符，取代 “\”。如空格将表示为“*N”.