Migrating Data to TaurusDB Using mysqldump

Precautions

Database migration is performed offline. Before the migration, you must stop any applications using the source database.

Preparing for the Migration

1. Prepare an ECS in the same VPC and subnet as the TaurusDB instance or bind an EIP to the TaurusDB instance.
   • To connect to an instance through a private network, an ECS has to be created first.

     Purchase an ECS and log in to the ECS.

   • To connect to an instance through an EIP, you must:
     1. Bind the EIP to the instance. For details, see Procedure.
     2. Ensure that the local device can access the EIP that has been bound to the instance.
2. Install the mysql client on the prepared ECS or device that can access the TaurusDB instance.
   

   • Install the mysql client by following the instructions provided in How Can I Install the mysql Client?
   • Ensure that the mysql client version is the same as or later than that installed on the TaurusDB instance. The MySQL database or client provides mysqldump and mysql by default.

Exporting Data

Before migrating data from the source database to the TaurusDB instance, you need to export data from the source database first.

mysqldump must match the DB engine version.

1. Log in to the prepared ECS or device that can access the TaurusDB instance.
2. Use mysqldump to export metadata into an SQL file.
   

   MySQL databases are required for TaurusDB management. When exporting metadata, do not specify --all-database, or the databases will be unavailable.

   mysqldump --databases <DB_NAME> --single-transaction --order-by-primary --hex-blob --no-data --routines --events --set-gtid-purged=OFF -u <DB_USER> -p -h <DB_ADDRESS> -P <DB_PORT> |sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' -e 's/DEFINER[ ]*=.*FUNCTION/FUNCTION/' -e 's/DEFINER[ ]*=.*PROCEDURE/PROCEDURE/' -e 's/DEFINER[ ]*=.*TRIGGER/TRIGGER/' -e 's/DEFINER[ ]*=.*EVENT/EVENT/' > <BACKUP_FILE>

   • <DB_NAME> indicates the name of the database to be migrated.
   • <DB_USER> indicates the database username.
   • <DB_ADDRESS> indicates the database address.
   • <DB_PORT> indicates the database port.
   • <BACKUP_FILE> indicates the name of the file to which the data will be exported.

   Enter the database password when prompted.

   Example:

   mysqldump --databases gaussdb --single-transaction --order-by-primary --hex-blob --no-data --routines --events --set-gtid-purged=OFF -u root -p -h 192.*.*.* -P 3306 |sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' -e 's/DEFINER[ ]*=.*FUNCTION/FUNCTION/' -e 's/DEFINER[ ]*=.*PROCEDURE/PROCEDURE/' -e 's/DEFINER[ ]*=.*TRIGGER/TRIGGER/' -e 's/DEFINER[ ]*=.*EVENT/EVENT/' > dump-defs.sql

   Enter password:

   After this command is executed, the dump-defs.sql file will be generated.

3. Use mysqldump to export data into an SQL file.
   

   MySQL databases are required for TaurusDB management. When exporting metadata, do not specify --all-database, or the databases will be unavailable.

   mysqldump --databases <DB_NAME> --single-transaction --hex-blob --set-gtid-purged=OFF --no-create-info --skip-triggers -u <DB_USER> -p -h <DB_ADDRESS> -P <DB_PORT> -r <BACKUP_FILE>

   For details on the parameters in the preceding command, see 2.

   Enter the database password when prompted.

   Example:

   mysqldump --databases gaussdb --single-transaction --hex-blob --set-gtid-purged=OFF --no-create-info --skip-triggers -u root -p -h 192.*.*.* -P 3306 -r dump-data.sql

   After this command is executed, the dump-data.sql file will be generated.

Importing Data

You can use a client to connect to the TaurusDB instance through an ECS or device that can access the TaurusDB instance and then import the exported SQL files into that instance.

If the source database calls triggers, stored procedures, functions, or events, you must set log_bin_trust_function_creators to ON for the destination database before importing data.

1. Import metadata into the TaurusDB instance.

   mysql -f -h <DB_ADDRESS> -P <DB_PORT> -u root -p < <BACKUP_DIR>/dump-defs.sql

   • <DB_ADDRESS> indicates the IP address of the TaurusDB instance.
   • <DB_PORT> indicates the port of the TaurusDB instance.
   • <BACKUP_DIR> indicates the directory where dump-defs.sql will be stored.

   Example:

   mysql -f -h 172.*.*.* -P 3306 -u root -p < dump-defs.sql

   Enter password:

2. Import data into the TaurusDB instance.

   mysql -f -h <DB_ADDRESS> -P <DB_PORT> -u root -p < <BACKUP_DIR>/dump-data.sql

   • <DB_ADDRESS> indicates the IP address of the TaurusDB instance.
   • <DB_PORT> indicates the port of the TaurusDB instance.
   • <BACKUP_DIR> indicates the directory where dump-data.sql will be stored.

   Example:

   mysql -f -h 172.*.*.* -P 3306 -u root -p < dump-data.sql

   Enter password:

3. Use the MySQL tool to connect to the TaurusDB instance and view the results.

   mysql> show databases;

   In this example, the database named my_db has been imported.

   mysql> show databases;
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | my_db              |
   | mysql              |
   | performance_schema |
   +--------------------+
   4 rows in set (0.00 sec)