Migrating Data with mysqldump

mysqldump is a popular tool packaged with the MySQL client infrastructure. It queries the database to produce a series of CREATE TABLE and INSERT statements that can be replayed to restore the database.

For more information on loading data from MySQL using mysqldump, refer to Load Data from MySQL using mysqldump.

While moving your data from MySQL to SingleStore Helios, there are a few considerations to keep in mind:

• For most MySQL storage engines (MyISAM, InnoDB, etc), indexes are stored as B-trees. In SingleStore Helios, 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 (ORDER BY). While transferring your schema, you should audit your table definitions and investigate whether your indexes can be optimized for SingleStore Helios. The default BTREE notation is converted into a skip list (see Skip List Indexes) .

• mysqldump will generate a few queries that are unsupported by SingleStore Helios. For example, SingleStore Helios does not support disabling UNIQUE_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 warn_level variable. See Unsupported MySQL Features for more details. Some components of a CREATE TABLE statement might be blocked completely. If you run into this issue while loading a schema into SingleStore Helios, you can manually massage the schema definition into something supported by SingleStore Helios.

• If the machine running SingleStore Helios does not have enough memory to support the data you’re loading, the server will issue an error on offending INSERT statements indicating its out-of-memory state. In this case, you should upgrade your machine to one with more memory. If you copy your existing memsqlbin directory to the new machine, SingleStore Helios will be able to reuse the schema definitions and INSERT statements 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 not exist 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. To produce a dump of your database, run something like:

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 SingleStore Helios 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.log file to see which unsupported features have been ignored. After the import is completed, you can connect to SingleStore Helios and start querying the tables directly.