Migrating Data with mysqldump
mysqldump is a popular tool packaged with the MySQL client infrastructure.
CREATE TABLE and
INSERT statements that can be replayed to restore the database.
mysqldump reference page here.
While moving your data from MySQL to SingleStoreDB Cloud, there are a few considerations to keep in mind:
For most MySQL storage engines (MyISAM, InnoDB, etc), indexes are stored as B-trees.
In SingleStoreDB Cloud, indexes can be unidirectional lock-free skip lists (ascending or descending) or lock-free hash tables. Picking the right data structure for your index can have a significant impact on the performance of your application. While hash tables are optimized for key-value look ups, skip lists are extremely flexible for complex range scans and sorts (
While transferring your schema, you should audit your table definitions and investigate whether your indexes can be optimized for SingleStoreDB Cloud. The default
BTREEnotation is converted into a skip list (see Skip List Indexes) .
mysqldumpwill generate a few queries that are unsupported by SingleStoreDB Cloud.
For example, SingleStoreDB Cloud does not support disabling
CHECKS To make it easier to work with
mysqldump, unsupported features are by default reported as warnings instead of errors.
This functionality can be controlled by adjusting the
level See Unsupported MySQL Features for more details. Some components of a
CREATE TABLEstatement might be blocked completely.
If you run into this issue while loading a schema into SingleStoreDB Cloud, you can manually massage the schema definition into something supported by SingleStoreDB Cloud.
If the machine running SingleStoreDB Cloud does not have enough memory to support the data you’re loading, the server will issue an error on offending
INSERTstatements indicating its out-of-memory state.
In this case, you should upgrade your machine to one with more memory. If you copy your existing
memsqlbindirectory to the new machine, SingleStoreDB Cloud will be able to reuse the schema definitions and
INSERTstatements that have already compiled.
Definer error (error:1227) gets triggered when MySQL attempts to create an object under a database user, and that database user does notexist on the destination database.
Refer https://aws. amazon. com/premiumsupport/knowledge-center/definer-error-mysqldump/ for a work around.
You should separate your schema and data into separate files, so that you can easily review and modify your schema if necessary.
mysqldump -h 127.0.0.1 -u root -B [database name] --no-data -r schema.sql
mysqldump -h 127.0.0.1 -u root -B [database name] --no-create-info -r data.sql
You can then replay these files directly into SingleStoreDB Cloud by running:
mysql -h 127.0.0.1 -u root -P 3307 < schema.sql
mysql -h 127.0.0.1 -u root -P 3307 < data.sql
While this step runs, you can observe the
memsql. file to see which unsupported features have been ignored.
Last modified: April 6, 2023