This topic describes two methods to export data from PolarDB-X databases.
mysqldump
PolarDB-X supports the data export tool mysqldump provided by the native MySQL. For more information about mysqldump commands, see mysqldump.
- Syntax
mysqldump -h ip -P port -u user -ppassword --default-character-set=char-set --net_buffer_length=10240 --no-create-db --skip-add-locks --skip-lock-tables --skip-tz-utc --set-charset [--hex-blob] [--no-data] database [table1 table2 table3...] > dump.sql
- Parameter description
Parameter Description Required ip The IP address of the PolarDB-X instance. Yes port The port of the PolarDB-X instance. No user The username of the PolarDB-X instance. Yes password The password of the PolarDB-X instance. Note The password contains the -p prefix. No spaces are specified between -p and the password.Yes char-set The character set. Yes --hex-blob Exports binary string fields in the hexadecimal format. If you want to export binary string fields, this parameter is required. The binary string fields can be of the BINARY, VARBINARY, or BLOB data type. No --no-data Does not export data. No table Specifies the tables that you want to export. You can specify one or more tables. If you do not configure this parameter, the command exports all tables from the specified database. No
SELECT ... INTO OUTFILE
- Syntax
SELECT ... INTO OUTFILE 'file_name' [CHARACTER SET charset_name] [export_options] export_options: [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string']
- Example
SELECT customer_id, firstname, surname INTO OUTFILE '/exportdata/customers.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM customers;
- Limit
The syntax of OUTFILE in PolarDB-X is the same as that of the mysql command. The exported files are stored on the PolarDB-X compute node on which the session of the SQL statement performed.