SingleStore Managed Service

Load Data from MySQL

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 root --databases foo > foodump.sql

You can also dump specific tables by using the --tables option:

$ mysqldump -h 127.0.0.1 -u root --databases foo --tables tbl_name > foodump.sql

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 DB.

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:

Note

If using SingleStore Managed Service, replace 127.0.0.1 in the example below with the address of the admin endpoint.

mysql -h 127.0.0.1 -u root < foodump.sql

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