【MySQL技术内幕】59-逻辑备份_mysql select into already exists-CSDN博客

1、mysqldump

mysqldump备份工具最初由Igor Romanenko编写完成,通常用来完成转存(dump)数据库的备份及不同数据库之间的移植,如从MySQL低版本数据库升级到 MySQL高版本数据库,又或者从 MySQL数据库移植到 Oracle、 Microsoft SQL Server数据库等。
mysqldump的语法如下:

mysqldump [arguments] >file name

如果想要备份所有的数据库,可以使用--all-databases选项:

mysqldump --all-databases >dump. sql

如果想要备份指定的数据库,可以使用--databases选项:

mysqldump --databases db1 db2 db3 >dump. sql

如果想要对test这个架构进行备份,可以使用如下语句:

mysqldump --single-transaction test >test_backup.sql

上述操作产生了一个对test架构的备份,使用--single-transaction选项来保证备的一致性。备份出的 test_backup. sql是文本文件,通过文本查看命令cat就可以得到文件的内容。

备份出的文件内容就是表结构和数据,所有这些都是用SQL语句方式表示。文件开始和结束的注释部分是用来设置 MySQL数据库的各项参数,一般用来使还原工作更有效和准确地进行。之后的部分先是 CREATE TABLE语句,接着就是 INSERT的SQL语句了。
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-dop-database:在 CREATE DATABASE前先运行 DROP DATABASE。这个参数需要和--all-databases或者--databases选项一起使用。在默认情况下,导出的文本文件中并不会有 CREATE DATABASE,除非指定了这个参数。
--master-data[=value]:通过该参数产生的备份转存文件主要用来建立一个replication。当value的值为1时,转存文件中记录CHANGE MASTER语句。当value的值为2时, CHANGE MASTER语句被写出SQL注释。在默认情况下,value的值为空。当 value值为1时,在备份文件中会看到:CHANGE MASTER TO MASTER_LOG_FILE='xen-server-bin.000006',MASTER_LOG_POS=8095;当 value为2时,在备份文件中会看到 CHANGE MASTER语句被注释了。
--master-data:会自动忽略--lock-tables选项。如果没有使用--single-transaction选项,则会自动使用--lock-all-tables选项。
--events(-E):备份事件调度器。
--routines(-R):备份存储过程和函数。
--triggers:备份触发器。
--hex-blob:将 BINARY、 VARBINARY、BLOG和BIT列类型备份为十六进制的格式。 mysqldump导出的文件一般是文本文件,但是如果导出的数据中有上述这些类型,在文本文件模式下可能有些字符不可见,若添加--hex-blob选项,结果会以十六进制的方式显示。
--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=....来改变默认的分割符、换行符等。

我发现大多数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

2、SELECT...INTO OUTFILE

SELECT...INTO语句也是一种逻辑备份的方法,更准确地说是导出一张表中的数据。 SELECT...INTO的语法如下:

其中FIELDS[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会报没有权限导出:
mysql> select into outfile '/root/a. txt' from a;
ERROR 1(HY000): Can't create/write to file '/root/a. txt (Errcode: 13)
若已经存在该文件,则同样会报错:
mysql test -e "select into outfile '/home/mysql/a. txt' fields terminated by ',' from a
ERROR 1086(HYooo)at line 1: File '/home/mysql/a. txt already exists
查看通过 SELECT INTO导出的表a文件:
mysql> select * into outfile ' /home/mysql/a. txt from a;
Query OK,3 rows affected (0.02 sec)

可以发现,默认导出的文件是以TAB进行列分割的,如果想要使用其他分割符,如",",则可以使用 FIELDS TERMINATED BY 'string'选项,如

在 Windows平台下,由于换行符是“\r\n”,因此在导出时可能需要指定LINES TERMINATED BY选项,如:

3、逻辑备份的恢复

mysqldump的恢复操作比较简单,因为备份的文件就是导出的SQL语句,一般只需要执行这个文件就可以了,可以通过以下的方法
mysql -uroot -p <test_backup.sql
如果在导出时包含了创建和删除数据库的SαL语句,那必须确保删除架构时,架构目录下没有其他与数据库相关的文件,否则可能会得到以下的错误:
mysql> drop database testi
ERROR 1010 (HY000): Error dropping database (can't rmdir ./test, errno: 39)
因为逻辑备份的文件是由SQL语句组成的,也可以通过SOURCE命令来执行导出的逻辑备份文件,如下:
mysql> source /home/mysql/test backup sql;
通过 mysqldump可以恢复数据库,但是经常发生的一个问题是, mysqldump可以导出存储过程、导出触发器、导出事件、导出数据,但是却不能导出视图。因此,如果用户的数据库中还使用了视图,那么在用 mysqldump备份完数据库后还需要导出视图的定义,或者备份视图定义的frm文件,并在恢复时进行导人,这样才能保证 mysqldump数据库的完全恢复。

4、LOAD DATA INFILE

若通过 mysqldump --tab,或者通过 SELECT INTO OUTFILE导出的数据需要恢复,
这时可以通过命令 LOAD DATA INFILE来进行导入。 LOAD DATA INFILE的语法如下:

LOAD DATA INTO TABLE a IGNORE 1 LINES INFILE ' /home/mysql/a.txt'

要对服务器文件使用 LOAD DATA INFILE,必须拥有FILE权。其中对于导入格式的选项和之前介绍的 SELECT INTO OUTFILE命令完全一样。 IGNORE number LINES选项可以忽略导入的前几行。下面显示一个用 LOAD DATA INFILE命令导入文件的示例,并忽略第一行的导入:
mysql> load data infile ' /home/mysql/a. txt into table a;
Query oK, 3 rows affected (0.00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
为了加快 InnoDB存储引擎的导入,可能希望导入过程忽略对外键的检查,因此可以使用如下方式:

mysql>SET @@foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)
mysql>LOAD DATA INFILE ' /home/mysql/a.txt' INTO TABLE a;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
mysql>SET @foreign_key_checks=1;
Query OK, 0 rows affected (0.00 sec)

另外可以针对指定的列进行导入,如将数据导人列a、b,而c列等于a、b列之和:

5、mysqlimport

mysqlimport是 MySQL数据库提供的一个命令行程序,从本质上来说,是LOAD DATA INFILE的命令接口,而且大多数的选项都和 LOAD DATA INFILE语法相同。其语法格式如下:
shell>mysqlimport [options] db_name textfile1 [textfile2 ...]
和 LOAD DATA INFILE不同的是, mysqlimport命令可以用来导入多张表。并且通过--user-thread参数并发地导入不同的文件。这里的并发是指并发导人多个文件,而不是指 mysqlimport可以并发地导入一个文件,这是有明显区别的。此外,通常来说并发地对同一张表进行导入,其效果一般都不会比串行的方式好。下面通过 mysqlimport并发地导人2张表:
[rootexen-servermysql]# mysqlimport --use-threads=2 test /home/mysql/t.txt /home/mysql/s.txt
test.s:Records: 5000000 Deleted: 0 Skipped: 0 Warnings: 0
test.t:Records: 5000000 Deleted: 0 Skipped: 0 Warnings: 0
如果在上述命令的运行过程中,查看 MySQL的数据库线程列表,应该可以看到类似内容如下：