Shulou(Shulou.com)06/02 Report--

This article mainly explains "the process of mysql migrating mpp database Greenplum". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let the editor take you to learn "the process of mysql migrating mpp database Greenplum".

1. Scene description

Because mysql in the brother project is a bit unbearable and has to do sql optimization, but the business is a little complicated and it is a bit troublesome to optimize (sql nesting is a bit too much), I want to use the Mpp database Greenplum to test, to see how the performance and complexity are, and to wade.

two。 Solution

The initial idea is: because both mysql and postgresql (Greenplum is based on postgresql, iSmim software Lao Wang) use the standard sql, directly build the table statement of mysql on the side of Greenplum, import the data to test it, and it should be done in half a day.

2.1 Greenplum table creation

Export the table structure of mysql through navicat for mysql (only the table structure is exported in navivat, as shown below), but it is found that the exported structure is in the

Cannot be executed in Greenplum, ddl statement in mysql:

1 `CONFIG_ ID`varchar (36) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Software Lao Wang'

Solution.

(1) I found the java code from mysql to postgresql on the Internet, which is not too comprehensive. I still have some problems after changing it a few times. Give up.

(2) asked dba, Navicat Premium 12 can be transferred, URL: https://www.navicat.com.cn/

Navicat Premium can operate on multiple databases at the same time, including mysql and greenplum (postgresql). Previously, using navicat for mysql can only operate on mysql databases, and navicat for postgresql can only operate on postgresql.

2.2 Export data structures

Use Navicat Premium, as shown below:

Select mysql on the left, select greenplum on the right, and remove the creation record from the option to create the table in Greenplum. (first create all the tables, the amount of data is too large, we only import the data of a few tables for testing)

2.3 Import data. 2.3.1 preliminary ideas

The initial idea is to import directly through Navicat, and use the above Navicat Premium12 to import Greenplum data directly from mysql. However, after importing a few small tables, a table of more than 300,000 has been imported for more than 20 minutes and less than 40% has been imported. The cpu of greenplum's master node is a little high, and there are several million-level data behind. This efficiency is about to be introduced into the years and months.

2.3.2 external table mode

(1) first of all, you need to start the external table program fdisk in the master node and create a new directory to store the files exported from mysql. I exported the csv format.

1 [gpadmin@ software Lao Wang] $mkdir script

2 [gpadmin@ software Lao Wang] $nohup gpfdist-d / home/gpadmin/script/-p 8081-l / home/gpadmin/script/ruanjianlaowang.log &

(2) create an external table

1Murray, Mutual

2CREATE EXTERNAL TABLE "public". "t_laowang_ex" (

3 laowang_type numeric (3)

4 "laowang_id" varchar (36)

5)

6location ('gpfdist://10.192.0.168:8081/laowang.csv') format' csv' (DELIMITER',') encoding 'utf8'

Description:

(a) you can directly take out the ddl statement of the original table and add a _ ex to the name (when you create an external table, you will find that not null is not available and should be replaced with an empty one)

(B) where the ip address is the master address of greenplum, laowang is the name of the csv file, and the csv file is exported through the navicat right-click.

1gpfdist://10.192.0.168:8081/laowang.csv

(3) data is loaded into greenplum table

1insert into t_laowang select * from t_laowang_ex

Lao Wang of 2i'm software

This completes the migration of data from mysql to greenplum, and the comparison of specific test results will not be discussed here.

2.4 General conclusion

The implementation of the scheme is more complex than expected: first, the table sql of the two databases is different, and then solved through the latest Navicat Premium 12; second, it is imported directly through navicat, and there is a problem in efficiency, which can be solved by the way of external table, which can be imported in 2-3 seconds; third, when you create a new external table, it is slightly different from the original table, and you should also pay attention to it.

At this point, I believe you have a deeper understanding of "the process of mysql migrating mpp database Greenplum". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.