EXPORT

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.