Migrate MySQL table schemas to OceanBase Database by using mysqldump

The mysqldump tool is provided by MySQL to export MySQL database objects and data. You can use the --help parameter to view the help documentation. This topic only describes the frequently used commands and parameters of mysqldump.

Export table schemas (without data) of a specified database

Run the following command to export the table schemas (without data) of a specified database:

mysqldump -h 127.1 -ur*** -P3306 -p******  -d TPCH --compact > tpch_ddl.sql

The exported script has the following characteristics:

• The script contains view definitions in the form of comments enclosed in / *!* /. If you do not need view definitions, delete them.

• The script contains syntaxes that are not supported in MySQL mode of OceanBase Database. You can replace these syntaxes, such as the sql_notes variable and the DEFINER clause.

  As shown in the following sample code, the exported script contains the MAX_ROWS= setting, which is not supported in MySQL mode of OceanBase Database. Execution of this script will trigger an error. To avoid this error, you can comment out the entire MAX_ROWS= field. For example, you can use :%s/MAX_ROWS=/; -- MAX_ROWS=/g in Vim.

  /*!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;
  

• Foreign keys may be included in the statements of the exported table schemas. When you import the script to a MySQL tenant of OceanBase Database, an error occurs if the table on which the foreign keys depends is not created. Therefore, before the import, you need to disable the foreign key check constraint as follows:

  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)
  

  After you disable the foreign key constraint, exit the session and log on again. Run the source command in OBClient to execute the external SQL script file.