MySQL数据的导入导出mysqldump、mysqlimport into outfile和load data
作者:mmseoamin日期:2023-12-14

0、概述

MySQL数据的导入导出方案通常是配套的,例如:

方案一:使用mysqldump导出数据,再使用mysql客户端导入数据

方案二:使用SELECT INTO OUTFILE命令导出数据,再使用LOAD DATA或mysqlimport导入数据

方案三:使用mysql程序的批处理模式导出数据,再使用LOAD DATA或mysqlimport导入数据

1、mysqldump导出数据+mysql导入数据

1.1、使用mysqldump导出数据

1.1.1、使用–tables导出指定表

# 语法
mysqldump  db_name --tables  tb1_name  tb2_name  >  filemname.sql
# 实例
mysqldump mytest --tables t1 t2 > t1_t2.sql

1.1.2、使用–tab选项将表定义文件和数据文件分开导出

# 语法
mysqldump  db_name  --tab=dir
# 实例
mysqldump mytest1 --tab=/home/mysql/__test

MySQL数据的导入导出mysqldump、mysqlimport into outfile和load data,第1张

1.1.3、使用–fields-terminated-by选项定义数据分隔符

以下导出时,数据值以逗号分隔

mysqldump mytest1 --tab=/home/mysql/__test   --fields-terminated-by=','

1.1.4、使用–databases选项导出整个库或多个库

参数说明如下:

  • –complete-insert:导出的dump文件里,每条INSERT语句都包含列名。
  • –force:即使出现错误,也要继续执行导出操作,会打印出错误。
  • –insert-ignore:生成的INSERT语句是INSERT IGNORE的形式,如果导入此文件,即使出错了也仍然可以继续导入数据(当作警告)。
  • –databases:类似–tables,后面可以跟多个值,即多个数据库名
  • –compatible=name:导出的文件和其他数据库更兼容(但不确保),name的值可以是ANSI、MYSQL323、MYSQL40、POSTGRESQL、ORACLE、MSSQL、DB2、MAXDB、NO_KEY_OPTIONS、NO_TABLE_OPTIONS或NO_FIELD_OPTIONS。
    mysqldump --complete-insert --force --add-drop-database --insert-ignore --hex-blob --databases mytest > mytest_db.sql
    

    1.1.5、使用–all-databases选项导出所有数据库

    mysqldump --all-databases --add-drop-database > db.sql
    

    1.1.6、使用–xml选项实现导出格式为XML

    mysqldump  --xml  mytest1 >  /tmp/mytest1.xml
    

    1.1.7、使用–ignore-table选项实现导出时忽略指定表

    导出时可以选择忽略哪些表,即不导出哪些表,只需加上参数–ignore-table=db_name.tbl_name1、–ignore-table=db_name.tbl_name2。

    mysqldump --databases=mytest,mytest1  --ignore-table=mytest.tb1,mytest1.tb2
    

    1.1.8、使用mysql客户端配合mysqldump实现通配符匹配表名的导出

    mysqldump不支持直接利用通配符导出多个表,但可以先用SELECT加通配符查询要导出的多张表的表名,将表名写到文件中,然后再用mysqldump读出表名再导出这些表。

    #  1. 获得表名,写入文件
    mysql -N information_schema -e "select table_name from tables where table_name like 'prefix_%' " > tbs.txt
    #  2. 读取包含表名的文件,导出表
    mysqldump db 'cat tbs.txt' > dump.sql 
    

    1.1.9、使用mysqldump导出数据的优化方式

    1. 选择MySQL服务器的I/O活动低的时候导出数据。
    2. I/O分离(数据盘和备份盘I/O分离)。
    3. 输出到管道压缩(gzip)。
    4. –quick跳过内存缓冲(–opt默认启用)。
    5. 从数据保留策略上想办法,把不需要修改的大量数据放到历史表中,而不是每次都备份。

    1.2、使用mysql导入数据

    1.2.1、基本导入方法

    • mysqldump导出的SQL转储文件,可以用如下的形式将数据导入到数据库中:
      mysql db_name < db_name.sql
      

      1.2.2、乱码问题

      • 字符集问题:转储文件(dump文件)里面一般指定了set names utf8,所以我们在导入的时候不再需要指定特殊的字符集。例外的情况是,有一些特殊的场合,SQL文件是以其他的字符集导出的,这个时候导入要注意保持文件的字符集、客户端字符集和连接的字符集的一致性。–default-character-set的意思是,客户端和连接都默认使用charset_name字符集。
        # 语法
        mysql --default-character-set=charset_name database_name < import_table.sql 
        # 示例
        mysql --default-character-set=gbk  < import_table.sql
        
        • 如果mysql客户端输出的数据是乱码,那么请检查下客户端、连接的字符集配置。例如,我们使用SSH工具securecrt登录主机,然后使用mysql命令行工具连接MySQL服务器,mysql连接的默认配置可能是latin1,那么此时显示utf8的数据将会是乱码。这种情况下,可以在客户端运行set names utf8,并确认securecrt的字符编码是UTF-8,这样就可以正常显示utf8字符集的数据了。

          2、SELECT INTO OUTFILE导出数据+LOAD DATA或mysqlimport导入数据

          2.1、使用SELECT INTO OUTFILE导出数据

          • 如果想要进行SQL级别的表备份,可以使用SELECT INTO OUTFILE命令语句。对于SELECT INTO OUTFILE,输出的文件不能先于输出存在。
            SELECT * INTO OUTFILE '/tmp/testfile.txt' FROM t1;
            SELECT * INTO OUTFILE '/tmp/t1.txt'  FIELDS TERMINATED BY ':' OPTIONALLY ENCLOSED BY '+' ESCAPED BY '!'    FROM t1;
            

            2.2、使用LOAD DATA导入数据

            2.2.1、使用LOAD DATA导入数据的基本导出方法

            1. 一般来说,只要导出导入操作中使用的选项完全一致,用SELECT…INTO OUTFILE命令导出的文本文件就可以用LOAD DATA命令导入到数据表里去,不会发生任何变化。
            2. SELECT…INTO OUTFILE可以筛选记录,导出表数据到一个文件中,而LOAD DATA INFILE则是相反的操作,是读取这个文件导入表中。
            3. 如果LOAD DATA命令导入的文件不在MySQL服务器上,而是想导入客户端所在的本地文件系统的文件时,则需要使用语法变体LOAD DATA…LOCAL INFILE…,也就是说,如果指定LOCAL关键词,则表明从客户主机读文件。如果没指定LOCAL,那么文件必须位于MySQL服务器上。
            mysql> load data infile '/tmp/t2.txt' into table t2; 
            

            2.2.2、导出导入csv格式文件

            • 导出导入csv格式的文本文件。csv格式的文件,即逗号分隔的数据文件。
              # 生成csv文件
              mysql> select field_list from table_name  into outfile '/home/garychen/tmp/table_name_2.csv' fields terminated by ','  optionally enclosed by '"' lines terminated by '\n';
              # 导入文件
              mysql>  load data local infile '/home/garychen/tmp/table_name_2.csv' into table table_name fields terminated by ',' lines terminated by '\n'(field1,field2,field3);
              

              2.2.3、SELECT INTO OUTFILE导出+LOAD DATA导入方案的优势

              1. 相较于普通的mysql命令,LOAD DATA执行SQL文件导入的方式要快得多,一般可以达到每秒几万条记录的插入速度。
              2. 如果有很多表,那么使用mysqldump会更简单。如果是导入个别大表,而且对于时间有很高的要求,那么LOAD DATA未尝不可。mysqldump默认的导出文件,其实已经包含了一些优化了,会有禁用key、启用key的操作,而且是一条INSERT语句包括多行记录的。

              2.2.4、LOAD DATA的优化

              • 将innodb_buf fer_pool_size设置得更大些。
              • 将innodb_log_file_size设置得更大些,如256MB。
              • 设置忽略二级索引的唯一性约束,SET UNIQUE_CHECKS=0。
              • 设置忽略外键约束,SET FOREIGN_KEY_CHECKS=0。
              • 设置不记录二进制日志,SET sql_log_bin=0。
              • 按主键顺序导入数据。由于InnoDB使用了聚集索引,如果是顺序自增ID的导入,那么导入将会更快,我们可以把要导入的文件按照主键顺序先排好序再导入。
              • 对于InnoDB引擎的表,可以在导入前,先设置autocommit=0
              • 可以将大的数据文件切割为更小的多个文件,例如使用操作系统命令split切割文件,然后再并行导入数据。
              • 由于唯一索引(约束)对于我们导入数据的影响比较大,尤其对于大表导入,我们需要留意这一点。不要在大表上创建太多的唯一索引,主键、唯一索引不要包含太多列,否则导入数据将会很慢。

                2.3、使用mysqlimport导入数据

                • mysqlimport命令的语法格式如下:
                  # 语法
                  mysqlimport databasename tablename.txt
                  # 实例
                  mysqlimport mytest /tmp/t2.txt 
                  

                  3、使用mysql程序的批处理模式导出数据

                  使用mysql程序的批处理模式,支持比较灵活的导出数据,因为可以利用SQL语句。

                  1. 可以基于mysql的批处理模式,做语句级别的导出,以下两种方式等价:
                  # 方式一,-e选项
                  mysql --batch --default-character-set=utf8 -e "SELECT * FROM t2;" mytest > t2.txt
                  # 方式二,--execute
                  mysql --batch --default-character-set=utf8 "--execute=SELECT * FROM t2;" mytest > t3.txt
                  
                  1. vertical选项,将查询结果按纵向导出:
                  mysql --batch --default-character-set=utf8  --vertical  -e "SELECT * FROM t2;" mytest > t2.txt
                  
                  1. html选项:将查询结果按html格式导出:
                  mysql --batch --default-character-set=utf8  --html  -e "SELECT * FROM t2;" mytest > t2.txt
                  
                  1. xml选项,将查询结果按xml格式导出:
                  mysql --batch --default-character-set=utf8  --xml  -e "SELECT * FROM t2;" mytest > t2.txt
                  

                  4、使用Linux的split切割文件,加速导入数据

                  • split命令的作用是切割文件,如果不加入任何参数,默认情况下是以1000行的大小来分割的。
                    split [OPTION] [INPUT [PREFIX]]
                    
                    • 以下以每个文件10000行记录进行切割,生成的文件名以test_spl i t_sub_为前缀,因为文件有15万多条记录,最后且分为16个文件
                      split -l 10000 /tmp/t1.txt t1_split_sub_
                      

                      MySQL数据的导入导出mysqldump、mysqlimport into outfile和load data,第2张

                      5、总结

                      MySQL导出导入数据(即数据转储)主要有以下三种方式:

                      1. mysqldump导出+mysql导入:这种方式下导出的是SQL语句而非数据本身,所以导入时效率相对较低,但是胜在可以整库甚至多个库、多个表一起导出,适合整库的转储。
                      2. SELECT INTO OUTFILE导出+LOAD DATA或mysqlimport导入:这种方式下导出的是纯数据,所以导入时效率会很高。适合单个大表的转储。
                      3. mysql批处理模式导出+LOAD DATA或mysqlimport导入:这种方式下导出的也是纯数据,所以导入时效率会很高。优点除了导入效率高,由于是用SQL语句选择数据,所以很灵活,缺点则是使用门槛高。

                      注:设置MYSQL数据导出与导入,secure_file_priv参数设置

                      通过show variables like ‘%secure_file_priv%’;

                      secure_file_priv参数说明

                      这个参数用来限制数据导入和导出操作的效果,例如执行LOAD DATA、SELECT … INTO OUTFILE语句和LOAD_FILE()函数。这些操作需要用户具有FILE权限。
                      secure_file_priv=null 不允许导入导出

                      secure_file_priv=具体文件路径 只允许从这个路径导入导出

                      secure_file_priv=空 可以从任意路径导入导出。

                      此时修改secure_file_priv的参数只需要修改mysql

                      此时,需要修改:

                      /etc/mysql/mysql.conf.d/mysqld.cnf 文件,如图

                      在末尾添加一句secure_file_priv=”/”即可将数据导出到任意目录