Export and import data by using mysqldump - ApsaraDB for SelectDB

If you want to back up or migrate data in an ApsaraDB for SelectDB instance, you can use the mysqldump tool to export the database schemas and data to a text file. The text file contains the SQL statements for creating the databases and tables and inserting data to ensure data integrity and consistency.

Examples

Export data

Export the test_table table in the test_db database.

mysqldump -h127.0.0.1 -P9030 -uroot --no-tablespaces --databases test_db --tables test_table

Export the schema of the test_table table in the test_db database.

mysqldump -h127.0.0.1 -P9030 -uroot --no-tablespaces --databases test_db --tables test_table --no-data

Export all tables in the test_db1 and test_db2 databases.

mysqldump -h127.0.0.1 -P9030 -uroot --no-tablespaces --databases test_db1 test_db2

Export all databases and tables.

mysqldump -h127.0.0.1 -P9030 -uroot --no-tablespaces --all-databases

Import data

You can redirect the data that is exported by using the mysqldump tool to a file, and then import the exported data to an ApsaraDB for SelectDB instance by running the source command on the MySQL client.

Export data to the specified file.

mysqldump -h127.0.0.1 -P9030 -uroot --no-tablespaces --all-databases > all_databases.sql

Import the exported data to an ApsaraDB for SelectDB instance.
```
source all_databases.sql
```

Important

ApsaraDB for SelectDB does not support tablespaces that are available in MySQL. Therefore, you must specify the --no-tablespaces parameter when you use the mysqldump tool.
You can use the mysqldump tool to export data and schemas only for development and testing or to export a small amount of data. Do not use the mysqldump tool in the production environment in which a large amount of data is involved.

References

For more information about mysqldump, see mysqldump.