Port a database from MySQL to MemSQL

Because MemSQL is fully compatible with MySQL, It is very straightforward to migrate data from MySQL to MemSQL. You can use standard MySQL tools for migration.

In this example, we assume that both your MySQL and MemSQL run on the same machine, MySQL runs on port 3306, and MemSQL runs on port 3307, and both of them can be accessed through the root account without a password.

Use mysqldump

MysqldumpIt is one of the most common data backup tools for MySQL clients. It generates SQL statements for creating tables and inserting data in columns. Therefore, it is most convenient to restore a database.

When you are sure you want to migrate data to MemSQL, there are several considerations:

• Most MySQL storage engines use B-tree to store indexes, while MemSQL uses a one-way unlocked skip List or lockless hash table. Selecting the correct index data structure will significantly improve the application performance. Hash Tables are mainly suitable for key-value searches, while skip lists are especially suitable for scanning and sorting complex ranges (order ). Therefore, before performing the migration, You need to review your table definition and determine whether MemSQL exclusive optimization can be used. The default BTREE symbol is converted to the list of skips in ascending order. If you want to perform a two-way range scan on a column, you can consider adding both ascending and descending indexes. For more information about the MemSQL indexes, seeIndexes
  
• BecauseCode generationBecause MemSQL loads the database structure more slowly than MySQL for the first time. Because MemSQL loads the table structure for the first time, it generates and compiles code to implement the table architecture, including memory allocation, insertion, deletion, and iteration methods. Once the table is compiled, MemSQL directly uses the compiled code during the entire operation. The INSERT statement generated by mysqldump will also be compiled once.
• MysqldumpSome SQL statements not supported by MemSQL are generated. For example, UNIQUE_CHECKS is not supported. To better support mysqldump, MemSQL only generates warnings for these unsupported statements. You can adjust them.Warn_levelVariable to control the error level. For more information, seeUnsupported Features. Some create table statements may be completely blocked for execution. If you encounter this problem, you can only manually modify the definition SQL of the TABLE.
• If the machine running MemSQL does not have enough memory to load data, the server will report the out-of-memory error message for executing the INSERT statement, in this case, you can only install more memory. If you copy the existing memsqlbin directory to a new machine, MemSQL will continue to use compiled table definitions and INSERT statements. If you are using the MemSQL development edition and exceed the 10 Gb limit, you can refer to memsql.com/next to learn how to upgrade.
  

We recommend that you separate the database structure definition and data to store independent files, so that you can adjust the table structure as needed. You can use the following command to export the database structure and data files respectively:

$ mysqldump -h 127.0.0.1 -u root -B [database name] --no-data > schema.sql$ mysqldump -h 127.0.0.1 -u root -B [database name] --no-create-info > data.sql

Then import the data using the following method:

$ mysql -h 127.0.0.1 -u root -P 3307 < schema.sql$ mysql -h 127.0.0.1 -u root -P 3307 < data.sql

When running these steps, you can observeMemsql_tracelog(Also outputStderr) To view the unsupported features that are ignored during execution. Once the import is complete, you can connect to MemSQL and query the results.

Convert your application

It is very easy for applications to support MemSQL. You only need to change the connection configuration.

Note:

If you want to connect to MemSQL on the local machine, you should use 127.0.0.1 instead of localhost. Most MySQL clients can parse localhost and use the MySQL socket file for connection to ignore port settings. For details, seeThis page

Once you start running the program, you can monitorMemsql_tracelogSome unsupported SQL syntax will be displayed here. For more information aboutSQL Statements