逻辑备份
mysqldump
mysqldump备份工具最初由Igor Romanenko编写完成，通常用来完成转存（dump）数据库的备份以及不同数据库之间的移植，例如从低版本的MySQL数据库升级到高版本的MySQL数据库，或者从MySQL数据库移植到Oracle和SQL Server等数据库等。
mysqldump的语法如下：
mysqldump [arguments] ＞ file_name
如果想要备份所有的数据库，可以使用--all-databaes选项：
mysqldump --all -databases ＞ dump.sql
如果想要备份指定的数据库，可以使用--databases选项：
mysqldump --databases db1 db2 db3 ＞ dump.sql
如果想要对test这个架构进行备份，可以使用如下语句：
mysqldump --single -transaction test ＞ test_backup.sql
我们使用--single-transaction选项来保证备份的一致性，备份出的test_backup.sql是文本文件，通过命令cat就可以查看文件的内容：cat test_backup.sql
可以看到，备份出的文件内容就是表结构和数据，所有这些都是用SQL语句表示的。文件开始和结束处的注释是用来设置MySQL数据库的各项参数的，一般用来使还原工作能更有效和准确的进行。之后的部分先是CREATE TABLE语句，之后就是INSERT语句了。
mysqldump的参数选项很多，可以通过mysqldump -help命令来查看所有的参数，有些参数有缩写，如--lock-tables的缩写为-l，重点介绍一些比较重要的参数。
--single-transaction：在备份开始前，先执行START TRANSACTION命令，以此来获得备份的一致性，当前该参数只对InnoDB存储引擎有效。当启用该参数并进行备份时，确保没有其他任何的DDL语句执行，因为一致性读并不能隔离DDL语句。
--lock-tables（-l）：在备份中，依次锁住每个架构下的所有表。一般用于MyISAM存储引擎，备份时只能对数据库进行读取操作，不过备份依然可以保证一致性。对于InnoDB存储引擎，不需要使用该参数，用--single-transaction即可，并且-lock-tables和-single-transaction是互斥（exclusive）的，不能同时使用。如果你的MySQL数据库中既有MyISAM存储引擎的表，又有InnoDB存储引擎的表，那么这时你的选择只有--lock-tables了。另外，前面说了，--lock-tables选项是依次对每个架构中的表上锁的，因此只能保证每个架构下表备份的一致性，而不能保证所有架构下表的一致性。
--lock-all-tables（-x）：在备份过程中，对所有架构中的所有表上锁。这可以避免之前提及的--lock-tables参数不能同时锁住所有表的问题。
--add-drop-database：在CREATE DATABASE前先运行DROP DATABASE。这个参数需要和-all-databases或者-databases选项一起使用。默认情况下，导出的文本文件中并不会有CREATE DATABASE，除非你指定了这个参数，因此可能会看到如下内容：
mysqldump --single -transaction --add -drop -database --databases test ＞ test_backup.sql
cat test_backup.sql
--master-data[=value]：通过该参数产生的备份转存文件主要用来建立一个slave replication。当value的值为1时，转存文件中记录CHANGE MASTER语句；当value的值为2时，CHANGE MASTER语句被写成SQL注释。默认情况下，value的值为空。
当value值为1时，在备份文件中会看到：
mysqldump --single -transaction --add -drop -database --master -data=1 --databases test＞test_backup.sql
cat test_backup.sql
CHANGE MASTER TO MASTER_LOG_FILE='xen-server-bin.000006'，MASTER_LOG_POS=8095；
当value为2时，在备份文件中会看到CHANGE MASTER语句被注释了：
mysqldump --single -transaction --add -drop -database --master -data=1 --databases test＞test_backup.sql
cat test_backup.sql
--Position to start replication or point-in-time recovery from
--master-data会自动忽略-lock-tables选项。如果没有使用-single-transaction选项，则会自动使用-lock-all-tables选项。
--events（-E）：备份事件调度器。
--routines（-R）：备份存储过程和函数。
--triggers：备份触发器。
--hex-blob：将BINARY、VARBINARY、BLOG、BIT列类型备份为十六进制的格式。mysqldump导出的文件一般是文本文件，但是，如果导出的数据中有上述这些类型，文本文件模式下可能有些字符不可见，若添加-he-blob选项，结果会以十六进制的方式显示，如：
mysqldump --single -transaction --add -drop -database --master -data=2 --no -autocommit --databases test3 ＞ test3_backup.sql
cat test3_backup.sql
LOCK TABLES'a'WRITE；
set autocommit=0；
INSERT INTO'a'VALUES（0x61000000000000000000）；
UNLOCK TABLES；
可以看到，这里用0x61000000000000000000（十六进制的格式）来导出数据。
--tab=path（-T path）：产生TAB分割的数据文件。对于每张表，mysqldump创建一个包含CREATE TABLE语句的table_name.sql文件和包含数据的tbl_name.txt。可以使用--fields-terminated-by=……，--fields-enclosed-by=……，--fields-optionally-enclosed-by=……，--fields-escaped-by=……，--lines-terminated-by=……来改变默认的分割符、换行符等，如：
mysqldump --single -transaction --add -drop -database --tab="/usr/local/mysql/data/test" test
大多数DBA喜欢用SELECT……INTO OUTFILE的方式来导出一张表，但是通过mysqldump一样可以完成工作，而且可以一次完成多张表的导出，并且保证导出数据的一致性。
--where='where_condition'（-w 'where_condition'）：导出给定条件的数据。
例如，导出b架构下的表a，并且表a的数据大于2，如下所示。
mysqldump --single -transaction --where='b＞2' test a ＞ a.sql
SELECT……INTO OUTFILE
SELECT……INTO语句也是一种逻辑备份的方法，或者更准确地说是导出一张表中的数据。
SELECT……INTO的语法如下：
SELECT [column 1], [column 2]……
INTO
OUTFILE 'file_name'
[{FIELDS|COLUMNS}
[TERMINATED BY'string']
[[OPTIONALLY]ENCLOSED BY'char']
[ESCAPED BY'char']
]
[LINES
[STARTING BY'string']
[TERMINATED BY'string']
]
FROM TABLE WHERE……
字段[TERMINATED BY 'string']表示每个列的分隔符，
[[OPTIONALLY]ENCLOSED BY'char']表示对于字符串的包含符，
[ESCAPED BY'char']表示转义符，
[STARTING BY'string']表示每行的开始符号，
TERMINATED BY'string'表示每行的结束符号。
如果没有指定任何FIELDS和LINES的选项，默认使用以下的设置：
FIELDS TERMINATED BY '\t'
ENCLOSED BY ''
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
STARTING BY ''
file_name表示导出的文件，但文件所在的路径的权限必须是mysql:mysql，否则MySQL会报告没有权限导出：
select * into outfile '/root/a.txt' from a;
ERROR 1（HY000）：Can't create/write to file'/root/a.txt'（Errcode：13）
查看通过SELECT INTO导出的表a文件：
select * into outfile '/home/mysql/a.txt' from a;
quit
cat /home/mysql/a.txt
1 a
2 b
可以发现，默认导出的文件是以TAB进行列分割的，如果想要使用其他分割符，如“，”，则可以使用FIELDS TERMINATED BY'string'选项，如：
mysql test -e "select * into outfile '/home/mysql/a.txt' fields terminated by','from a";
cat /home/mysql/a.txt
1，a
2，b
在Windows平台下，因为其换行符是“\r\n”，因此在导出时可能需要指定LINES TERMINATED BY选项，如：
mysql test -e "select * into outfile '/home/mysql/a.txt' fields terminated by',' lines terminated by '\r\n' from a";
od -c a.txt
0000000 1，a\r\n 2，b\r\n 3
0000017
逻辑备份的恢复
mysqldump的恢复
mysqldump的恢复操作比较简单，因为备份的文件就是导出的SQL语句，一般只需要执行这个文件就可以了，可以通过以下的方法：
mysql -uroot -p123456＜test_backup.sql
如果在导出时包含了创建和删除数据库的SQL语句，则必须确保删除架构时架构目录下没有其他与数据库无关的文件，否则可能会出现以下的错误：
drop database test;
ERROR 1010（HY000）：Error dropping database（can't rmdir'./test'，errno：39）
因为逻辑备份的文件是由SQL语句组成的，所以也可以通过SOURCE命令来执行导出的逻辑备份文件，如下所示：
source /home/mysql/test_backup.sql;
Query OK，0 rows affected（0.00 sec）
……
Query OK，0 rows affected（0.00 sec）
通过mysqldump可以恢复数据库，但是常发生的一个问题是mysqldump可以导出存储过程、触发器、事件、数据，但是却不能导出视图。因此，如果你的数据库中还使用了视图，那么在用mysqldump备份完数据库后还需要导出视图的定义，或者保存视图定义的frm文件，并在恢复时进行导入，这样才能保证mysqldump数据库的完全恢复。
LOAD DATA INFILE
若是通过mysqldump --tab或SELECT INTO OUTFILE导出的数据需要恢复时，这时需要通过LOAD DATA INFILE命令来进行导入。
LOAD DATA INFILE的语法如下所示：
LOAD DATA [LOW_PRIORITY|CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE|IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[{FIELDS|COLUMNS}
[TERMINATED BY'string']
[[OPTIONALLY]ENCLOSED BY'char']
[ESCAPED BY'char']
]
[LINES
[STARTING BY'string']
[TERMINATED BY'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,……)]
[SET col_name=expr,……]
要对服务器文件使用LOAD DATA INFILE，必须拥有FILE权，其中导入格式的选项和之前介绍的SELECT INTO OUTFILE命令完全一样。IGNORE number LINES选项可以忽略导入的前几行。
下面来看一个用LOAD DATA INFILE命令导入文件的示例，并忽略第一行的导入：
load data infile '/home/mysql/a.txt' into table a;
为了加快InnoDB存储引擎的导入，你可能希望导入过程忽略对外键的检查，因此可以使用如下方式。
set @@foreign_key_checks=0;
load data infile '/home/mysql/a.txt' into table a;
set @@foreign_key_checks=1；
可以针对指定的列进行导入，如将数据导入列a、b，而c列等于a、b列之和：
create table b(a int,b int,c int,primary key(a))engine=innodb;
load data infile '/home/mysql/a.txt' into table b fields terminated by ',' (a,b) set c=a+b;
LOAD DATA INFILE命令可以用来导入数据，但同时可以完成对Linux操作系统的监控。如果需要监控CPU的使用情况，可以通过加载/proc/stat来完成。
首先我们需要建立一张监控CPU的表cpu_stat，其结构如下所示：
CREATE TABLE IF NOT EXISTS DBA.cpu_stat(
id bigint auto_increment primary key,
value char(25) NOT NULL,
user bigint,
nice bigint,
system bigint,
idle bigint,
iowait bigint,
irq bigint,
softirq bigint,
steal bigint,
guest bigint,
other bigint,
time datetime
);
接着可以通过用LOAD DATA INFILE命令来加载/proc/stat文件，但需要对其中一些数值进行转化，命令如下所示：
LOAD DATA INFILE '/proc/stat'
IGNORE INTO TABLE DBA.cpu_stat
FIELDS TERMINATED BY''
(@value,@val1,@val2,@val3,@val4,@val5,@val6,@val7,@val8,@val9,@val10)
SET
value=@value,
user=IF(@value NOT LIKE 'cpu%', NULL,IF(@value！='cpu',IFNULL(@val1,0),IFNULL(@val2,0))),
nice=IF(@value NOT LIKE 'cpu%',NULL,IF(@value！='cpu',IFNULL(@val2,0),IFNULL(@val3,0))),
system=IF(@value NOT LIKE 'cpu%',NULL,IF(@value！='cpu',IFNULL(@val3,0),IFNULL(@val4,0))),
idle=IF(@value NOT LIKE 'cpu%',NULL,IF(@value！='cpu',IFNULL(@val4,0),IFNULL(@val5,0))),
iowait=IF(@value NOT LIKE 'cpu%',NULL,IF(@value！='cpu',IFNULL(@val5,0),IFNULL(@val6,0))),
irq=IF(@value NOT LIKE 'cpu%',NULL,IF(@value！='cpu',IFNULL(@val6,0),IFNULL(@val7,0))),
softirq=IF(@value NOT LIKE 'cpu%',NULL,IF(@value！='cpu',IFNULL(@val7,0),IFNULL(@val8,0))),
steal=IF(@value NOT LIKE 'cpu%',NULL,IF(@value！='cpu',IFNULL(@val8,0),IFNULL(@val9,0))),
guest=IF(@value NOT LIKE 'cpu%',NULL,IF(@value！='cpu',IFNULL(@val9,0),IFNULL(@val10,0))),
other=IF（@value like 'cpu%',user+nice+system+idle+iowait+irq+softirq+steal+guest,@val1),
time=now();
接着查看表cpu_stat，可以看到类似如下的内容：
select * from cpu_stat\G
***************************1.row***************************
id：1
value：cpu
user：2632896
nice：67761
system：688488
idle：4136329105
iowait：1468238
irq：0
softirq：106303
steal：148300
guest：0
other：4141441091
time：2010-09-10 12：01：04
***************************14.row***************************
id：14
value：procs_running
user：NULL
nice：NULL
system：NULL
idle：NULL
iowait：NULL
irq：NULL
softirq：NULL
steal：NULL
guest：NULL
other：1
time：2010-09-10 12：01：04
***************************15.row***************************
id：15
value：procs_blocked
user：NULL
nice：NULL
system：NULL
idle：NULL
iowait：NULL
irq：NULL
softirq：NULL
steal：NULL
guest：NULL
other：0
time：2010-09-10 12：01：04
15 rows in set（0.00 sec）
接着可以设置一个定时器来让MySQL数据库自动地运行上述LOAD DATA INFILE语句，这样就会有每个时间点的CPU信息被记录到表cpu_stat。
执行下述语句就可以得到每个时间点上CPU的使用情况：
select
100*((new.user-old.user)/(new.other-old.other))user,
100*((new.nice-old.nice)/(new.other-old.other))nice,
100*((new.system-old.system)/(new.other-old.other))system,
100*((new.idle-old.idle)/(new.other-old.other))idle,
100*((new.iowait-old.iowait)/(new.other-old.other))iowait,
100*((new.irq-old.irq)/(new.other-old.other))irq,
100*((new.softirq-old.softirq)/(new.other-old.other))softer,
100*((new.steal-old.steal)/(new.other-old.other))steal,
100*((new.guest-old.guest)/(new.other-old.other))guest,
new.time
from DBA.cpu_stat old,
DBA.cpu_stat new
where new.id-15=old.id
and old.value='cpu'
and new.value=old.value\G;
同样，我们还可以对/proc/diskstat文件执行如上述所示的操作，这样就又可以对磁盘进行监控操作了。
mysqlimport
mysqlimport是MySQL数据库提供的一个命令行程序，从本质上来说，是LOAD DATA INFILE的命令接口，而且大多数的选项都和LOAD DATA INFILE语法相同。其语法格式如下：
mysqlimport [options] db_name textfile1[textfile2……]
与LOAD DATA INFILE不同的是，mysqlimport命令是可以导入多张表的，并且通过--user-thread参数来并发导入不同的文件。这里的并发是指并发导入多个文件，并不是指mysqlimport可以并发地导入一个文件，这是有区别的，并且并发地对同一张表进行导入，效果一般都不会比串行的方式好。
通过mysqlimport并发地导入两张表。
mysqlimport --use -threads=2 test /home/mysql/t.txt /home/mysql/s.txt
如果在上述命令运行的过程中查看MySQL的数据库线程列表，应该可以看到类似如下的内容：
show full processlist\G
***************************1.row***************************
Id：46
User：rep
Host：www.dao.com：1028
db：NULL
Command：Binlog Dump
Time：37651
State：Master has sent all binlog to slave；waiting for binlog to be updated
Info：NULL
***************************2.row***************************
Id：77
User：root
Host：localhost
db：test
Command：Query
Time：0
State：NULL
Info：show full processlist
***************************3.row***************************
Id：83
User：root
Host：localhost
db：test
Command：Query
Time：73
State：NULL
Info：LOAD DATA INFILE '/home/mysql/t.txt' INTO TABLE 't' IGNORE 0 LINES
***************************4.row***************************
Id：84
User：root
Host：localhost
db：test
Command：Query
Time：73
State：NULL
Info：LOAD DATA INFILE '/home/mysql/s.txt' INTO TABLE 's' IGNORE 0 LINES
4 rows in set（0.00 sec）
mysqlimport实际上是同时执行了2条LOAD DTA INFILE语句来完成并发导入操作。
