Upgrade seekdb using mysqldump

You can upgrade seekdb by using mysqldump for a logical migration.

info

You can also use OceanBase OBLOADER/OBDUMPER for a logical migration. For details, see OceanBase OBLOADER/OBDUMPER.

About mysqldump

mysqldump is the standard MySQL tool for logical backup and restore. It connects to the database over the client protocol, runs queries to read the data you want to back up, and writes that data as INSERT statements (logical export).

To restore, you run the SQL in the dump file against the target database (logical import). Because the output is plain SQL, you can use it to move data between different databases or versions.

Before you start

• New seekdb is deployed: The target instance must already be running the new seekdb version.

• mysqldump is installed: Use a compatible MySQL client. Recommended versions:

  Platform	Version
  Linux	mysqldump Ver 10.13 Distrib 5.6.37, for Linux (x86_64)
  macOS	mysqldump Ver 10.13 Distrib 5.7.21, for macos10.13 (x86_64)

Upgrade steps

1. Back up data

1. Connect to the old seekdb instance.

   mysql -h127.0.0.1 -uroot -P2881 -p****** -A

   Use the host and port of the old seekdb instance.

2. Increase session timeouts so long-running export queries do not get cut off.

   SET GLOBAL ob_trx_timeout=1000000000,GLOBAL ob_query_timeout=1000000000;

3. Export the data with mysqldump.

   The following example writes the dump to backup.sql:

   mysqldump -h`host` -P`port` -uroot  --skip-triggers --databases `database-name` --skip-extended-insert > ./backup.sql

   tip

   Options prefixed with --skip turn off the corresponding default behavior.

   Parameters:

   Parameter	Short form	Required	Description	Example
   --host	-h	Yes	Server IP	-h127.0.0.1
   --port	-P	Yes	Server port	-P2881
   --user	-u	Yes	User name	-uroot
   --password	-p	No	Password	-p
   --databases	—	Yes	Database(s) to export. Use --all-databases to export every database (not recommended); prefer listing databases explicitly.	test
   --skip-triggers	—	No	Do not export triggers.	--skip-triggers
   --skip-extended-insert	—	No	Emit one INSERT per row instead of multi-row INSERT INTO t VALUES (...),(...),.	--skip-extended-insert
   >	—	Yes	Output file path.	> /backup/mysqldump/alldb.sql

   Other useful options:

   Option	Short form	Required	Description	Example
   -d	—	No	Schema only (no data).	-d
   -t	—	No	Data only (no CREATE TABLE).	-t
   --compact	—	No	Less verbose output.	--compact
   --comments	—	No	Include comments.	--comments
   --complete-insert	—	No	Use full column lists in INSERT statements.	--complete-insert
   --force	—	No	Continue after errors.	--force
   --lock-tables	—	No	Lock tables during dump. --skip-lock-tables: do not lock. In the current seekdb version, tables are not locked during export regardless of this option.	--lock-tables

   For more options, run mysqldump --help.

   Example commands

   • Export all databases:

     mysqldump -h127.0.0.1 -P2881 -uroot -p --all-databases > /backup/mysqldump/alldb.sql

   • Export the test database:

     mysqldump -h127.0.0.1 -P2881 -uroot -p test > /backup/mysqldump/test.sql

   • Export specific tables (table1, table2) from database mysql:

     mysqldump -h127.0.0.1 -P2881 -uroot -p mysql table1 table2 > /backup/mysqldump/table2.sql

   • Export database test but exclude certain tables:

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

2. Restore data

If the new seekdb instance is reachable from the same machine as the old one:

1. Connect to the new seekdb instance:

   mysql -h`new-host` -P`new-port` -uroot -Ac

2. Load the dump:

   source backup.sql;

If the new seekdb instance is on a different machine:

1. Copy the backup file to the machine where the new seekdb runs.

2. On that machine, connect to the new seekdb instance:

   mysql -h`new-host` -P`new-port` -uroot -Ac

3. Load the dump:

   source backup.sql;

Troubleshooting

Query timeout during export (Error 4012)

When exporting a large amount of data, mysqldump may hit the query timeout and fail with:

mysqldump: Error 4012: Timeout, query has reached the maximum query timeout: 10000000(us), maybe you can adjust the session variable ob_query_timeout or query_timeout hint, and try again.

Connect to the database and increase the timeout limits before running mysqldump again. You can change them back after the export finishes.

SET GLOBAL ob_trx_timeout=1000000000,GLOBAL ob_query_timeout=1000000000;