Migrate data by using mysqldump

mysqldump is a logical import and export tool frequently used in MySQL for minor compactions of databases.

Working principle

mysqldump retrieves the data to be backed up after mysqldump is connected to the database over the corresponding protocol, and converts the retrieved data into the corresponding INSERT statements for logical export.

To restore data, you can execute the file export SQL statements for logical import. By using this method, you can migrate data between tenants and between databases.

OceanBase Database is compatible with MySQL. Therefore, you can use mysqldump to back up data in OceanBase Database.

Notice

You can use mysqldump to export only data in OceanBase Database instances in MySQL mode.

Recommended version

Export data

Syntax

mysqldump -hxx.xx.xx.xx -P2881 -u'user@tenantname#clustername' -ppassword --skip-triggers --databases db1 db2 db3 --skip-extended-insert > /tmp/data.sql

Note

For some default options, use --skip before a parameter to disable or cancel the corresponding effect.

The following table describes some other frequently used parameters:

Import data

Log on to the database and run the following command to import data:

source /tmp/data.sql

Example

OceanBase Database migration examples

• Use MyDumper to migrate tables from a MySQL database to OceanBase Database

  Export only the schemas of the specified database

  mysqldump -h 127.1 -uroot -P2881 -p****** --skip-triggers -d TPCH --compact > tpch_ddl.sql
  

  In the exported script:

  • Definitions of views are included as comments (/! /). You can disregard and delete the definitions of the views.

  • Some MySQL syntax is not supported in the MySQL mode of OceanBase Database. You can replace such syntax without affecting the actual use.

  Examples:

  /*!40101 SET character_set_client = @saved_cs_client */;
  /*!40101 SET @saved_cs_client     = @@character_set_client */;
  /*!40101 SET character_set_client = utf8 */;
  CREATE TABLE `NATION` (
    `N_NATIONKEY` int(11) NOT NULL,
    `N_NAME` char(25) COLLATE utf8_unicode_ci NOT NULL,
    `N_REGIONKEY` int(11) NOT NULL,
    `N_COMMENT` varchar(152) COLLATE utf8_unicode_ci DEFAULT NULL,
    PRIMARY KEY (`N_NATIONKEY`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci MAX_ROWS=4294967295;
  

  In the sample code, MAX_ROWS= is unique to MySQL. In MySQL mode of OceanBase Database, it does not exist and is not required or supported. You need to comment out MAX_ROWS= and the content following it. You can batch replace the related content. For example, you can use :%s/MAX_ROWS=/; -- MAX_ROWS=/g in Vim.

  Notice

  The name of the exported SQL table is in uppercase. This means that the source MySQL table is likely to be case-sensitive. Therefore, the same setting must be used in the destination MySQL tenant of OceanBase Database.

  Foreign keys may be included in the statement of the exported schemas. 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 (0.01 sec)
  
  MySQL [oceanbase]> show global variables like '%foreign%';
  +--------------------+-------+
  | Variable_name      | Value |
  +--------------------+-------+
  | foreign_key_checks | OFF   |
  +--------------------+-------+
  1 row in set (0.00 sec)
  

  Run the source command to execute the external SQL script file in OBClient.

• Export only the table data of the specified database

  mysqldump -h 127.1 -uroot -P2881 -p****** -t TPCH  > tpch_data.sql
  

  Before the table data is exported, the table is locked by the data initialization SQL statement to prevent data writes in other sessions. Then, execute the INSERT statement to write data. The value parameter following each INSERT includes many values. The INSERT is a batch INSERT.

  Notice

  The current version of OceanBase Database supports input of the LOCK TABLES syntax, but does not implement database locking. Other sessions can still write data to the database.

  LOCK TABLES `t1` WRITE;
  /*!40000 ALTER TABLE `t1` DISABLE KEYS */;
  INSERT INTO `t1` VALUES ('a'),('Chinese');
  /*!40000 ALTER TABLE `t1` ENABLE KEYS */;
  UNLOCK TABLES;
  /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
  

Common examples

• Back up all databases:

  mysqldump -uroot -p****** --all-databases > /backup/mysqldump/alldb.sql
  

• Back up the test database:

  mysqldump -uroot -p****** test > /backup/mysqldump/test.sql
  

• Back up the specified tables table1 and table2 (separate the tables with spaces):

  mysqldump -uroot -p******  mysql table1 table2 > /backup/mysqldump/2table.sql
  

• Back up the database with specified tables excluded:

  mysqldump -uroot -p****** test --ignore-table=test.t1 --ignore-table=test.t2 > /backup/mysqldump/test2.sql
  

FAQ

How can I prevent data processing timeout?

A TIMEOUT 4012 error may be returned when you export a large amount of data by using mysqldump. To prevent this error, log on to the database as the tenant administrator and run the following command to modify the system parameters. After the data export is completed, you can restore the system parameters to the original values.

obclient> SET GLOBAL ob_trx_timeout=1000000000,GLOBAL ob_query_timeout=1000000000;