Migrating Data to GaussDB(for MySQL) Using mysqldump

• To connect to a GaussDB(for MySQL) instance through a private network, create an ECS first.

  For details on how to create and log in to an ECS, see descriptions about creating an ECS and logging in an ECS in the Elastic Cloud Server User Guide.

• To connect to a GaussDB(for MySQL) instance through an EIP, you must:
  1. Bind an EIP to the instance..
  2. Ensure that the local device can access the EIP.

MySQL databases are required for GaussDB(for MySQL) 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.xx.xx.xx -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.

MySQL databases are required for GaussDB(for MySQL) 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.

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

• DB_ADDRESS indicates the IP address of the GaussDB(for MySQL) instance.
• DB_PORT indicates the port of the GaussDB(for MySQL) 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:

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

• DB_ADDRESS indicates the IP address of the GaussDB(for MySQL) instance.
• DB_PORT indicates the port of the GaussDB(for MySQL) 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:

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)