Use mydumper and myloader to migrate data from a MySQL database to OceanBase Database

This topic describes how to migrate data from a MySQL database to OceanBase Database in MySQL mode by using mydumper and myloader.

Introduction to mydumper and myloader

• mydumper is an open-source multi-threaded backup tool designed for MySQL databases. It allows you to back up an entire database, a single table, or multiple tables, and supports multi-threaded backup to accelerate the data backup process. mydumper also supports the compression and encryption of backup data. You can restore data from backup files. mydumper is a command-line tool. To use the tool, you must install it and run its commands as needed.

• myloader is a tool designed for you to restore data from the backup files created by mydumper. myloader allows you to quickly restore a MySQL database by using the database backup files without creating a database first. It also allows you to restore data to a specified point in time. You can specify the data tables and rows to restore. myloader is a command-line tool. To use the tool, you must install it and run its commands as needed.

mydumper and myloader are efficient, secure, and reliable open-source tools that support multiple backup methods and the compression and encryption of backup data.

Procedure

1. Prepare the environment.

2. Perform backup operations.

   Here is a sample mydumper command used to back up data in a MySQL database:

   mydumper -h xx.xx.xx.xx -P3306 -u username -p ****** -B test_db -o /data/backup/mysql/
   

   The following table describes frequently-used options in the mydumper command.

   Option	Full name of the option	Description
   -h	--host	The name or IP address of the host to connect.
   -P	--port	The port number used for connection.
   -u	--user	The username used for backup.
   -p	--password	The user password used for backup.
   -B	--database	The name of the database to back up. If you do not specify this parameter, all databases on the connected host are backed up by default.
   -T	--tables-list	The list of tables to back up. Separate the table names with commas (,).
   -i	--ignore-engines	The list of storage engines to ignore during the backup process. Separate the engine names with commas (,).
   -m	--no-schemas	Specifies not to back up table schemas.
   -t	--threads	The number of backup threads executed in parallel. The default value is 4.
   -c	--compress	Specifies to compress the exported files.
   -o	--outputdir	The directory to which the backup files are exported.

3. Perform restore operations.

   After the data is backed up, you can run the source command or the myloader command to restore the data.

   • Use the source command to restore the data.

     If you cannot directly restore a table, you can find the SQL file of the table, and run the source command on the CLI to restore the table. The syntax is as follows:

     source <table name>-schema.sql # Restore the table schema.
     
     source <table name>.sql # Restore the table data.
     

   • Here is a sample myloader command used to restore data in an OceanBase database:

     myloader -h xx.xx.xx.xx -P2883 -u 'user@tenantname#clustenamer' -p ****** -o -d /data/backup/mysql/
     

     The following table describes frequently-used options in the myloader command.

     Option	Full name of the option	Description
     -h	--host	The name of the host to connect.
     -P	--port	The port number used for connection.
     -u	--user	The username used for restore.
     -p	--password	The user password used for restore.
     -B	--database	The name of the database to restore.
     -d	--directory	The directory where the backup files are stored.
     -o	--overwrite-tables	Specifies to drop the table first if the table to restore already exists. This parameter takes effect only if you have backed up the table schema.
     -B	--database	The name of the database to restore.

Step 1: Prepare the environment

1. Download the installation package.

   Go to the download page, and download the installation package as needed.

2. Decompress the installation package to the host where the database is deployed.

   Here is a sample command to decompress the mydumper-0.12.7-2-zstd.el7.x86_64.rpm package:

   rpm2cpio mydumper-0.12.7-2-zstd.el7.x86_64.rpm | cpio -div
   

3. Verify the installation.

   ./usr/bin/mydumper --help
   

   The metadata file and the corresponding table schema or table data files are generated in the backup destination directory.

Step 2: Perform backup operations

Back up all databases

mydumper -h xx.xx.xx.xx -P3306 -u username -p ****** -o /data/backup/mysql/

Back up the test database

mydumper -h xx.xx.xx.xx -P3306 -u username -p ******  -B test -o /data/backup/mysql/

Back up the t1 and t2 tables in the test database

mydumper -h xx.xx.xx.xx -P3306 -u username -p ****** -B test -T t1,t2 -o /data/backup/mysql/

Back up only the table data of the t1 table

mydumper -h xx.xx.xx.xx -P3306 -u username -p ****** -B test -T t1 -m -o /data/backup/mysql/

Back up the t1 table, with parallel threads and data compression enabled

mydumper -h xx.xx.xx.xx -P3306 -u username -p ****** -B test -T t1 -t 6 -c -o /data/backup/mysql/

Step 3: Perform restore operations

Disable FOREIGN KEY constraint check

Statements in the backup table schema may contain FOREIGN KEY constraints. When you import the table schema to a MySQL tenant of OceanBase Database, an error occurs if the table referenced by a foreign key does not exist. Therefore, you must disable the FOREIGN KEY constraint check before the import.

MySQL [oceanbase]> SET GLOBAL foreign_key_checks=off;
Query OK, 0 rows affected

MySQL [oceanbase]> SHOW GLOBAL VARIABLES LIKE '%foreign%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
1 row in set

After the data is backed up, you can run the source command or the myloader command to restore the data.

Use the source command to restore the data

If you cannot directly restore a table, you can find the SQL file of the table, and run the source command on the CLI to restore the table. The syntax is as follows:

source test.t1-schema.sql # Restore the table schema.

source test.t1.00000.sql # Restore the table data.

Use the myloader command to restore the data

• Import the table schema and table data.

  myloader -h xx.xx.xx.xx -P2883 -u 'user@tenantname#clustenamer' -p ******  -B test -o -d /data/backup/mysql/
  

• Import the database. If the database does not exist, a new database is created:

  myloader -h xx.xx.xx.xx -P2883 -u 'user@tenantname#clustenamer' -p ******  -B test -s test1 -o -d /data/backup/mysql/
  

For more information about mydumper and myloader, visit https://github.com/mydumper/mydumper.