How to port a database from MySQL to MemSQL

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

Mysqldump is one of the most commonly used 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, see indexes.

Because of code generation, 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.

Mysqldump generates some SQL statements that are not supported by MemSQL, such as UNIQUE_CHECKS. To better support mysqldump, MemSQL only warns of these unsupported statements, you can adjust the warn_level variable to control the error level. For more information, see Unsupported Features. some create table statements may be completely blocked for execution. If you encounter this problem, you can only manually modify the TABLE definition SQL.

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 observe memsql_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 the local MemSQL server, you should use 127.0.0.1 instead of localhost. Most MySQL clients can resolve localhost and connect to the MySQL socket file to ignore port settings. For details, see this page.

Once you start running the program, you can monitor memsql_tracelog. Some unsupported SQL syntax will be displayed here. More SQL statements for MemSQL

Connection: http://www.linuxeden.com/html/database/20120619/126001.html

Edit recommendations]

1. Summary of SQL database data types BOOL/BOOLEAN and TINYINT
2. MySQL Server encoding settings
3. MySQL source code learning: MDL dictionary lock
4. Simple deployment of MySQL Cluster Development Environment
5. Analysis of four different MySQL queries