逻辑备份

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 --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=http://www.mamicode.com/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=http://www.mamicode.com/1 --databases test＞test_backup.sql

cat test_backup.sql

--Position to start replication or point-in-time recovery from

大多数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的恢复

接着查看表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=http://www.mamicode.com/‘cpu‘
　　and new.value=http://www.mamicode.com/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语句来完成并发导入操作。

逻辑备份,mysqldump,SELECT…INTO OUTFILE,恢复