MySQL to MogDB Evaluation

MogDB Environment Preparation

You need to prepare your own MogDB/openGauss database environment for the repository and the target database, and the same environment is used by default for the repository and the target database.

Object Structure Migration

Structure Migration: You need to create the target database in the prepared MogDB/openGauss environment and migrate the data structures from the source database to the newly created target database.
Data Migration: If you want to perform SQL performance comparison evaluation, then in addition to migrating the database structure, you also need to migrate the table data to ensure that the source MySQL and the target MogDB/openGauss database data size is the same for the performance comparison to be meaningful.

Related Tool:

MTK: Structure and data migration can be done by MTK, for details please refer to https://mogdb.io/mtk

MySQL Data Collection

Required Permissions

The permissions required for the collection user are as follows.

root

Enable Slow SQL Logging

You need to enable slow logging in advance to ensure that the complete business cycle data is recorded in the slow log, it is recommended to enable slow logging one week in advance
Set slow SQL threshold interval to 0

SET GLOBAL slow_query_log=ON;
SET GLOBAL long_query_time=0.001;
set global log_output='FILE';      -- log_output='TABLE' is also supported, but the content is relatively small

Data Collection

It is recommended to execute the data collection command after running a full business cycle with slow logging enabled.

1). Execute on MySQL DB Server

Because of slow log is located in MySQL DB Server, we need to read it, so we recommanded you to run collection command on MySQL DB Server to collect data

./sca_linux_x86_64 -T MC -h <host> -p <port> -n <source-mysql-db> -u <mysql-user> -e <mysql-password>

# Command options:
# -h/p/t/u/e specify the connection method of the source MySQL database

2). Execute on Other Server

If you cannot run command on MySQL DB Server, then you need to get slow log back to sca server manually, and collecting using command below.

./sca_linux_x86_64 -T MC -h <host> -p <port> -n <source-mysql-db> -u <mysql-user> -e <mysql-password> --slow-log <full_path_of_local_slow_log>

# Command options:
# -h/p/t/u/e specify the connection method of the source MySQL database, for structure collection
# --slow-log specify the full path of the slow log in local server, which you have got from DB server manually, for SQL collection

Collection Results

When the collection is complete, a zip packet is generated which can be copied to the target database and unpacked into a data directory.

Subsequent compatibility analysis relies on the data in this packet and does not require another connection to the source MySQL database.

Repository Initialization

# Use the MogDB/openGauss user with administrator privileges for repository initialization
# Repository initialization will create a database with the name sca_db by default
# You can specify the repository name by -N, the repository user name by -U, and the repository user password by -E

./sca_linux_x86_64 -T i -H <host> -P <port> -N sca_db -U sca_repo -E 'SCA@password' --user <super-user> --password <super-password>

Perform Analysis Tasks

Compatibility Analysis

To do SQL compatibility analysis only, use the following command.

# If the repository name, username, password are not the default, you need to use the -N, -U, -E options to specify

./sca_linux_x86_64 -T MI -H <host> -P <port> -d <unzipped data directory>

When the analysis is complete, a report will be generated in the directory specified by -d, which can be downloaded offline for viewing.