First, export only data

In MySQL, if you only want to export data, you can use SEELCT… The INTO OUTFILE statement exports the queried data to the specified disk file. The syntax is as follows:

selectcolum1,colum2... columNfrom table_name into outfile 'Full path to export file'
fileds terminated by 'Field separator in output file'
enclosed by 'Field value closure in output file'
lines terminated by 'Line spacer in output file';
Copy the code

After using select… If “secure-file-priv option so it cannot execute this statement” is displayed when exporting data in the into outfile statement, set secure_file_priv. Secure_file_priv can be:

  1. Null: imports and exports are not allowed.
  2. Empty: indicates that there is no restriction.
  3. Specify path: Import or export can be performed only in a specified path.

Export only data and column names

Normally we connect to the database using the mysql command, which has a -e option to execute the specified SQL statement, combined with the ODS redirection operator > to export the query results to a file. The syntax is as follows:

Mysql -h mysql database address -u user name -p -d Database to be operated -e "SELECT statement" > Complete path to the exported fileCopy the code

3. Export data and structure

Mysqldump is a utility used by MySQL to dump databases. It mainly generates a SQL script that contains the NECESSARY SQL statements for creating databases, tables, and inserting data. As follows:

#Export database (containing data)Mysqldump -h mysql database address -u login name -p Exported database > Full path of exported file
#Export database (without data)Mysqldump -h mysql database address -u login name -p exported database --no-data > Full path of exported file
#Exports the specified tableMysqldump -h mysql database address -u login name -p Database table name > Full path of the exported file
#Export the database and ignore a tableMysqldump -h mysql database address -u login name -p exported database --ignore-table Exported database Ignored table > full path to export fileCopy the code