How to export data files from Linux MySQL database?export csv statement command

Linux MySQL databaseHow to export data files?export csv statement command

MySQLexport data data

In MySQL you can useSELECT … INTO OUTFILEstatement to simply export data to a text file.

Export data using the SELECT ... INTO OUTFILE statement

In the following example we will export the data table to the /tmp/tutorials.txt file:

mysql > SELECT * FROM chenweiliang_tbl 
     - > INTO OUTFILE '/tmp/tutorials.txt' ;

You can set the specified format of data output through command options. The following example is for exporting CSV format:

mysql > SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.csv' - > FIELDS TERMINATED BY '，' ENCLOSED BY ''' - > LINES TERMINATED BY '\ r \ n' ;
    
    

In the following example, a file is generated with values ​​separated by commas.This format can be used by many programs.

SELECT a ，b ，a + b INTO OUTFILE '/tmp/result.text'FIELDS 
TERMINATED BY '，' OPTIONALLY ENCLOSED BY '''行
终止'\ 
n'FROM test_table ;

The SELECT ... INTO OUTFILE statement has the following attributes:

• LOAD DATA INFILE is the inverse operation of SELECT ... INTO OUTFILE, SELECT syntax.To write data from a database to a file, use SELECT ... INTO OUTFILE, and to read the file back into the database, use LOAD DATA INFILE.
• SELECT ... INTO OUTFILE A SELECT of the form 'file_name' can write the selected lines to a file.The file is created on the server host, so you must have FILE permission to use this syntax.
• The output cannot be an existing file.Prevent file data from being tampered with.
• Do you need to have an account to log in to the server to retrieve files.Otherwise SELECT ... INTO OUTFILE will have no effect.
• In UNIX, the file is created to be readable, and permissions are owned by the MySQL server.This means that while you can read the file, you may not be able to delete it.

Export table as raw data

It mainly produces an SQL script containing the commands CREATE TABLE INSERT etc. needed to recreate the database from scratch.

To export data using mysqldump, you need to use the –tab option to specify the directory specified by the export file, and the target must be writable.

The following example exports the data table chenweiliang_tbl to the /tmp directory:

$ mysqldump - u root - p - no - create - info
             - tab = / tmp chenweiliang chenweiliang_tbl
密码******

Export data in SQL format

Export data in SQL format to the specified file, as follows:

$ mysqldump - u root - p chenweiliang chenweiliang_tbl > dump 。文本 
密码******

The content of the file created by the above command is as follows:

- MySQL 转储8.23 - - 主机：localhost     数据库：chenweiliang
 ----------------------------------- ---------------------- - 服务器版本        3.23 。58   

 
 

- - 表结构为表`chenweiliang_tbl` -
 


CREATE TABLE chenweiliang_tbl （
  chenweiliang_id INT （11 ）NOT NULL的auto_increment ，
  chenweiliang_title VARCHAR （100 ）NOT NULL 默认'' ，
  chenweiliang_author VARCHAR （40 ）NOT NULL 默认'' ，
  submission_date日期默认NULL ，
  PRIMARY KEY   （chenweiliang_id ），
  UNIQUE KEY AUTHOR_INDEX （chenweiliang_author ））TYPE = MyISAM ;  


- - 转储数据的表`chenweiliang_tbl` -
 


INSERT INTO chenweiliang_tbl 
       VALUES （1 ，'Learn PHP' ，'John Poul' ，'2007-05-24' ）;
INSERT INTO chenweiliang_tbl 
       VALUES （2 ，'学习MySQL' ，'Abdul S' ，'2007-05-24' ）;
INSERT INTO chenweiliang_tbl 
       VALUES （3 ，'JAVA Tutorial' ，'Sanjay' ，'2007-05-06' ）;

If you need to export the data of the entire database, you can use the following command:

$ mysqldump - u root - p chenweiliang > database_dump 。文本
密码******

If you need to backup all databases, you can use the following command:

$ mysqldump - u root - p - all - databases > database_dump 。文本
密码******

The --all-databases option was added in MySQL 3.23.12 and later.

This method can be used to implement a database backup strategy.

Copy data tables and databases to other hosts

If you need to copy data to another MySQL server, you can specify the database name and table in the mysqldump command.

Execute the following command on the source host to back up the data to the dump.txt file:

$ mysqldump - u root - p database_name table_name > dump 。文本 
密码*****

If you back up the database in full, you don't need to use a specific table name.

If you need to import the backup database to the MySQL server, you can use the following command, using the following command you need to confirm that the database has been created:

$ mysql - u root - p database_name < dump 。文本 
密码*****

你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的：</ p > 
$ mysqldump的- ü根- p DATABASE_NAME \
        | mysql - h other - host 。com database_name

The above command uses a pipe to import the exported data to the specified remote host.