Full Migration
Function Description
Chameleon is a real-time replication tool written in Python3 to migrate data from MySQL to openGauss. It supports full replication of initial data and real-time online replication of subsequent incremental data. Chameleon pulls all MySQL data to openGauss in read-only mode through initial configuration. Data can be concurrently migrated between tables in the same snapshot.
Full migration supports tables, table data, views, triggers, user-defined functions, and stored procedures.
Feature Advantages
Based on the sysbench test model, on the Kunpeng-920 2P server, when the data volume of 10 tables in the MySQL database is greater than 3 million, Chameleon uses 10 concurrent data migration tasks to migrate data to openGauss. The overall full migration performance can reach 300 MB/s or higher.
Environment Preparation
ARM+openEuler 20.03 or x86+CentOS 5.7
Installing Chameleon
Installing using WHL
Download the installation package. Download address: https://opengauss.obs.cn-south-1.myhuaweicloud.com/latest/chameleon/chameleon-1.0.0-py3-none-any.whl. After the installation package is downloaded, install it in the Python virtual environment.
Run the following commands to create and activate the Python virtual environment:
python3 -m venv venv source venv/bin/activate
Run the pip command to install the Chameleon tool:
pip3 install ./chameleon-1.0.0-py3-none-any.whl
NOTE:
- During the installation, other libraries on which the tool depends are automatically installed. Ensure that the pip of the local host can download and install related dependencies. The dependent libraries and versions are as follows:
- PyMySQL>=0.10.0, <1.0.0
- argparse>=1.2.1
- mysql-replication>=0.22
- py-opengauss>=1.3.1
- PyYAML>=5.1.2
- tabulate>=0.8.1
- daemonize>=2.4.7
- rollbar>=0.13.17
- geomet>=0.3.0
- mysqlclient>=2.1.1
- To install MySQL client, you need to use yum to install mysql-devel. You can directly run the yum install mysql-devel command.
Installing using source code
Run the git command to download the source code:
git clone git@gitee.com:opengauss/openGauss-tools-chameleon.git
Create and activate a Python virtual environment.
python3 -m venv venv source venv/bin/activate
Go to the code directory and run the python install command to install the Python.
cd openGauss-tools-chameleon python3 setup.py install
After the installation is complete, do not exit the Python virtual environment. You can use the chameleon tool.
Full Migration
Create a configuration file directory.
Go to the Python virtual environment and install the chameleon tool.
Run the following command to create the chameleon configuration file directory:
chameleon set_configuration_files
After this command is executed, the default configuration file template is created in the ~/.pg_chameleon/configuration directory.
Run the following command to copy the default configuration file template and save it as default.yml:
cd ~/.pg_chameleon/configuration cp config-example.yml default.yml
Modify the default.yml configuration file as required. Example content of the configuration file:
# global settings pid_dir: '~/.pg_chameleon/pid/' log_dir: '~/.pg_chameleon/logs/' log_dest: file log_level: info log_days_keep: 10 rollbar_key: '' rollbar_env: '' \# type_override allows the user to override the default type conversion \# into a different one. type_override: "tinyint(1)": override_to: boolean override_tables: \- "*" \# postgres destination connection pg_conn: host: "1.1.1.1" port: "5432" user: "opengauss_test" password: "password_123" database: "opengauss_database" charset: "utf8" sources: mysql: readers: 4 writers: 4 db_conn: host: "1.1.1.1" port: "3306" user: "mysql_test" password: "password123" charset: 'utf8' connect_timeout: 10 schema_mappings: mysql_database:sch_mysql_database limit_tables: skip_tables: grant_select_to: \- usr_migration lock_timeout: "120s" my_server_id: 1 replica_batch_size: 10000 replay_max_rows: 10000 batch_retention: '1 day' copy_max_memory: "300M" copy_mode: 'file' out_dir: /tmp sleep_loop: 1 on_error_replay: continue on_error_read: continue auto_maintenance: "disabled" gtid_enable: false type: mysql keep_existing_schema: No migrate_default_value: Yes
The meanings of the parameters in the preceding configuration files are as follows:
- During data migration, the user name and password used by the MySQL database are mysql_test and password123, respectively. The IP address and port number of the MySQL server are 1.1.1.1 and 3306, respectively. The database to be migrated is mysql_database.
- The user name and password used on openGauss are opengauss_test and password_123, respectively. The IP address and port number of the openGauss server are 1.1.1.1 and 5432, respectively. The target database is opengauss_database. The sch_mysql_database schema is created in opengauss_database, all tables to be migrated are in this schema.
Note that the user must have the permission to remotely connect to MySQL and openGauss as well as the read and write permissions on the corresponding databases. For openGauss, the host where Chameleon runs must be in the remote access whitelist of openGauss. For MySQL, the user must have the RELOAD, REPLICATION CLIENT, and REPLICATION SLAVE permissions.
Initialize the migration process.
chameleon create_replica_schema --config default chameleon add_source --config default --source mysql
In this step, an auxiliary schema and table are created for the replication process in openGauss.
Copy basic data.
chameleon init_replica --config default --source mysql
After this step is complete, the current full data in the MySQL database is copied to openGauss. You can view the replication result in openGauss.
Copy database objects.
Chameleon can migrate views, triggers, user-defined functions, and stored procedures from MySQL to openGauss. If logs do not need to be output to the console, delete the --debug parameter.
Copy a view.
chameleon start_view_replica --config default --source mysql --debug
Copy a trigger.
chameleon start_trigger_replica --config default --source mysql --debug
Copy a user-defined function.
chameleon start_func_replica --config default --source mysql --debug
Copy a stored procedure.
chameleon start_proc_replica --config default --source mysql --debug
You can view the records of migrated objects in the object migration information table sch_chameleon.t_replica_object. The following table describes columns in the t_replica_object table.
Table 1 Columns in the sch_chameleon.t_replica_object table
Type of the migrated object. The value can be VIEW, TRIGGER, FUNC, or PROC.
Migration status. **true** indicates that the migration is successful, and **false** indicates that the migration fails.
Translated statement. If the translation fails or an error occurs during the translation, the columns that are not supported by openGauss are commented out.
Note:You can view the translation of database objects in either of the following ways:
- Go to the openGauss-tools-sql-translator repository to learn about the translation of database objects.
- You can learn about the translation of database objects through Chameleon logs. Chameleon logs include logs generated during translation and migration.
End the replication process and clear resources.
chameleon stop_replica --config default --source mysql chameleon detach_replica --config default --source mysql chameleon drop_replica_schema --config default
The creation and verification of foreign keys and the reset sequence(serial) are performed in the detach_replica phase.
Uninstalling Chameleon
This tool is written in Python. You only need to delete the corresponding source code and venv environment to uninstall the tool.
Example
Example of migrating user-defined functions.
--Create two user-defined functions on the MySQL database.
create function mysql_func1(s char(20)) returns char(50) deterministic return concat('mysql_func1, ',s,'!')
create function mysql_func2(x smallint unsigned, y smallint unsigned) returns smallint deterministic BEGIN DECLARE a, b SMALLINT UNSIGNED DEFAULT 10; SET a = x, b = y; RETURN a+b; END;
--Migrate user-defined functions.
chameleon start_func_replica --config default --source mysql --debug
--The user-defined functions on the MySQL database have been successfully migrated. Query the sch_chameleon.t_replica_object table to obtain the migration information about the user-defined function. Note that the value of en_object_type is in uppercase.
select * from sch_chameleon.t_replica_object where en_object_type='FUNC';