Help Center/ Huawei Cloud Flexus_Huawei Cloud Flexus RDS/ User Guide/ Working with FlexusRDS for MySQL/ Data Migration/ Migrating Data to FlexusRDS for MySQL Using mysqldump
Updated on 2025-06-30 GMT+08:00
View PDF
Share

Migrating Data to FlexusRDS for MySQL Using mysqldump

Preparing for Data Migration

You can access your FlexusRDS for MySQL DB instance through an EIP or from a FlexusX instance.

  1. Prepare a FlexusX instance for accessing your FlexusRDS for MySQL DB instance or prepare a device for accessing your FlexusRDS for MySQL DB instance through an EIP.

    To connect to a FlexusRDS for MySQL instance through an EIP, bind an EIP to the instance.

  2. Install a MySQL client of the same version as your FlexusRDS for MySQL instance on the prepared FlexusX instance or device.

    A MySQL client will provide mysqldump and mysql.

    MySQL system databases mysql and sys cannot be imported to FlexusRDS for MySQL instances.

Exporting Data

Before migrating a database to FlexusRDS for MySQL, its data needs to be exported.

  • The export tool must match the DB engine version.
  • Database migration is performed offline. Before the migration, you have to stop all applications using the source database.
  1. Log in to the source database.
  2. Use the mysqldump tool to export the table structure to an SQL file.

    The mysql database is required for FlexusRDS for MySQL management. When exporting the table structure, do not specify --all-database. Otherwise, a database fault will occur.

    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 as prompted.

    Example:

    mysqldump --databases frdsdb --single-transaction --order-by-primary --hex-blob --no-data --routines --events --set-gtid-purged=OFF -u root -p -h 192.168.151.18 -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, a dump-defs.sql file will be generated as follows:

    [rds@localhost ~]$ ll dump-defs.sql 
-rw-r-----. 1 rds rds 2714 Sep 21 08:23 dump-defs.sql

  3. Use the mysqldump tool to export data to an SQL file.

    The mysql database is required for FlexusRDS for MySQL management. When exporting data, do not specify --all-database. Otherwise, a database fault will occur.

    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 as prompted.

    Example:

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

    After this command is executed, a dump-data.sql file will be generated as follows:

    [rds@localhost ~]$ ll dump-data.sql 
-rw-r-----. 1 rds rds 2714 Sep 21 08:23 dump-data.sql

Importing Data

You can connect your client to the FlexusRDS for MySQL instance and import exported SQL files into it.

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

  1. Log in to the FlexusX instance or device that can access the FlexusRDS for MySQL instance.
  2. Connect to the FlexusRDS for MySQL instance through a client.
  3. Import the table structure into the FlexusRDS for MySQL instance.

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

    • DB_ADDRESS indicates the IP address of the FlexusRDS for MySQL instance.
    • DB_PORT indicates the database port of the DB instance.
    • BACKUP_DIR indicates the directory where dump-defs.sql is stored.

    Example:

    # mysql -f -h 172.16.66.198 -P 3306 -u root -p < dump-defs.sql

    Enter password:

    If you intend to import SQL statements of a table to FlexusRDS, specify a database (mydb) in the command. Otherwise, the error message "No database selected" may be displayed. Example:

    # mysql -f -h 172.16.66.198 -P 3306 -u root -p mydb < dump-defs.sql

    Enter password:

  4. Import data into the FlexusRDS for MySQL instance.

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

    • DB_ADDRESS indicates the IP address of the FlexusRDS for MySQL instance.
    • DB_PORT indicates the database port of the DB instance.
    • BACKUP_DIR indicates the directory where dump-data.sql is stored.

    Example:

    # mysql -f -h 172.16.66.198 -P 3306 -u root -p < dump-data.sql

    Enter password:

    If you intend to import SQL statements of a table to FlexusRDS, specify a database (mydb) in the command. Otherwise, the error message "No database selected" may be displayed. Example:

    # mysql -f -h 172.16.66.198 -P 3306 -u root -p mydb < dump-defs.sql

    Enter password:

  5. View the import result.

    mysql> show databases;

    The following result indicates that database frdsdb has been imported.

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

Parent topic: Data Migration