Load Data from MySQL using mysqldump

The popular open source tool mysqldump is a straightforward way to load data from MySQL into SingleStore. For example, log into a machine running a MySQL database (named foo in the following examples), and issue the following commands:

mysqldump -h 127.0.0.1 -u username -p foo > foodump.sql

Note

If using SingleStore, you need to specify the endpoint (you can find this in the Cloud Portal) instead of the host IP address and specify the admin user instead of root.

mysqldump -h admin_endpoint -u admin -p foo > foodump.sql

You can omit the -h parameter from this request if this command is run on the localhost. You can also dump specific tables by using the --tables option:

mysqldump -h 127.0.0.1 -u username -p foo --tables tbl_name > foo_tabledump.sql

Note

Error: Unknown collation: ‘utf8mb4-0900_ai_ci'

This error may occur because mysqldump creates the .sql file using the utf8mb4-0900_ai_ci collation, but SingleStore uses the utf8_general_ci collation. To resolve this error, change the following line in the foodump.sql file

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

to:

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Since SingleStore is a distributed database, you need to tell SingleStore how to shard the data across the cluster. If all your tables have primary keys, SingleStore will automatically use the primary keys to shard them, so you can skip this next step. Otherwise, you need to open up foodump.sql in your favorite editor and either add shard keys or declare the tables as reference tables (for more details, see Porting Tables to SingleStore.

For instance, suppose foodump.sql contains:

CREATE TABLE users (
id bigint(20) NOT NULL,
username varchar(11) DEFAULT NULL,
address varchar(10)
);

To shard this table on the id column, use the following statement:

CREATE TABLE users (
id bigint(20) NOT NULL,
username varchar(11) DEFAULT NULL,
address varchar(10),
SHARD KEY(id)
);

For more information on choosing a shard key, see Primary Key as the Shard Key.

Once all your tables have either shard key or primary key declarations, you can run the following on the master aggregator:

mysql -u root -h 127.0.0.1 --default-auth=mysql_native_password -p foo < foodump.sql

On SingleStore:

mysql -u admin -h <endpoint> --default-auth=mysql_native_password -p foo < foodump.sql

All tables and data from users will now be loaded into SingleStore.

Last modified: March 28, 2024

Was this article helpful?