Product Solutions Support
Try MemSQL

Transitioning from MySQL to MemSQL

Because MemSQL is wire-compatible with MySQL, transitioning your data and application from MySQL to MemSQL is very straightforward. Once you migrate your data via standard MySQL tools, all it takes to get your app running is to point it at MemSQL.

For simplicity, this guide assumes that both MySQL and MemSQL are running on your local machine and that MySQL is running on the standard port (3306) while MemSQL runs on 3307. Furthermore, it also assumes that both are accessible without a password by the root user.

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.

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

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 MemSQL 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 MemSQL and start querying the tables directly.

Transitioning Your Application

To transition your application, simply change the connection credentials to point to MemSQL.

One thing to keep in mind is MemSQL Code Generation. Code generation makes loading a schema into MemSQL for the first time is slower than with MySQL. The first time MemSQL encounters a table schema, it generates and compiles code that implements infrastructure around the table (memory allocation, inserts, deletes, iterations, etc.). Once a table is compiled, MemSQL will be able to reuse it for the lifetime of your application — even if you restart the server or drop (and recreate) the table. The INSERT queries generated by mysqldump also have to be compiled exactly once.

Here are a couple of recommendations when transitioning your application:

Was this article useful?