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

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

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

 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.