Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to migrate the database from MySQL to MemSQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Migrate using the mysqldump tool

Mysqldump is one of the most commonly used data backup tools for MySQL clients. It generates SQL statements for creating tables and inserting data, so it is most convenient to restore a database.

Before you decide to migrate your data to MemSQL, there are a few considerations:

Most MySQL storage engines use B-tree to store indexes, while MemSQL uses one-way unlocked skip lists or unlocked hash tables. Choosing the right index data structure will significantly improve the performance of the application. Hash tables are mainly suitable for key-value lookups, while skip lists are particularly suitable for complex range scanning and sorting (ORDER BY). So before migrating, you need to re-examine your table definition and determine if you can use MemSQL-specific optimizations. The default BTREE symbol will be converted to an ascending skip list. If you need to do a bidirectional range scan on a column, you can consider adding both ascending and descending indexes. For more MemSQL index information, please see indexes.

Because of code generation, MemSQL first loads the database structure more slowly than MySQL, because when MemSQL first loads the table structure, it generates and compiles code to implement the schema of the table, including memory allocation, insertion, deletion, iteration methods, and so on. Once the table has been compiled, MemSQL will use the compiled code directly throughout the run. The INSERT statements generated by mysqldump will also be compiled specially.

Mysqldump will generate some SQL statements that MemSQL does not support, such as not supporting UNIQUE_CHECKS. In order to better support mysqldump,MemSQL, you can only warn these unsupported statements. You can control the error level by adjusting the warn_level variable. For more information, please see Unsupported Features. Some CREATE TABLE statements may be completely blocked, and if you encounter this problem, you can only manually modify the table definition SQL.

If the machine on which you are running MemSQL does not have enough memory to load data, the server will execute an error message out-of-memory to the INSERT statement, in which case you can only install more memory. If you copy the existing memsqlbin directory to the new machine, MemSQL will continue to use the compiled table definition and INSERT statements. If you are using the MemSQL development version and exceed the 10G limit, you can refer to memsql.com/next to learn how to upgrade.

It is recommended that you separate the database structure definition from the data in separate files, so that you can adjust the table structure when 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

How to migrate database from MySQL to MemSQL

Then import it in the following way:

$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 you run these steps, you can watch the memsql_tracelog (and also output to stderr) to see 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

Getting your application to support MemSQL is very simple, as long as you change the connection configuration.

Note: if you want to connect to the local MemSQL, you should use 127.0.0.1 instead of localhost. Most MySQL clients can parse localhost and connect using MySQL's socket file, ignoring the port setting. For more information, please see this page.

Once you start running the program, you can monitor memsql_tracelog. Some unsupported SQL grammars are shown here.

This is the content of "how to migrate database from MySQL to MemSQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.