Replikasi dari Percona Server untuk MySQL ke PostgreSQL menggunakan alat pg_chameleon

Replikasi adalah salah satu fitur terkenal yang memungkinkan Anda membuat salinan database yang identik. Ini didukung di hampir semua sistem manajemen basis data relasional (RDBMS). Replikasi memberikan manfaat yang signifikan, terutama ketersediaan tinggi dan penyeimbangan beban. Tetapi bagaimana jika replikasi diperlukan antara dua database dengan struktur yang berbeda, seperti MySQL dan PostgreSQL? Apakah mungkin untuk terus-menerus meniru perubahan dari database MySQL ke database PostgreSQL? Jawaban atas pertanyaan ini adalah alat replikasi pg_chameleon .

Untuk replikasi perubahan berkelanjutan, pg_chameleon menggunakan perpustakaan replikasi MySQL, yang memungkinkan Anda untuk mendapatkan salinan logis dari baris dari database MySQL, yang dikonversi ke objek jsonb. Fungsi pl / pgsql di Postgres mendekodekan objek jsonb dan mereproduksi perubahan dalam database Postgres. Untuk mengkonfigurasi jenis replikasi ini, variabel binlog_format untuk database MySQL harus diatur ke ROW (string).

Beberapa hal yang perlu diketahui sebelum menyiapkan alat ini:

1. Tabel yang perlu direplikasi harus memiliki kunci utama.
2. Alat ini berfungsi dalam versi PostgreSQL di atas 9,5 dan sistem MySQL di atas 5,5
3. Untuk mengkonfigurasi replikasi ini, variabel binlog_format harus disetel ke ROW.
4. Versi python harus lebih tinggi dari 3.3

Saat memulai replikasi, pg_chameleon mengambil data dari MySQL dalam format CSV dengan pengelompokan menjadi grup dengan panjang tertentu untuk menghindari kelebihan memori. Data ini memerah ke Postgres dengan perintah COPY. Jika penyalinan gagal, perintah INSERT dijalankan, yang dapat memperlambat proses. Jika perintah INSERT gagal, baris hilang.

Untuk mereplikasi perubahan dari MySQL, pg_chameleon mengemulasi perilaku replika MySQL (slave). Ini menciptakan skema di Postgres, melakukan pemuatan data awal, menghubungkan ke protokol replikasi MySQL, dan menyalin baris dalam tabel Postgres. Pada saat yang sama, fungsi Postgres yang sesuai memberikan decoding string dan perubahan. Ini mirip dengan menyimpan log transfer dalam tabel Postgres dan menerapkannya pada skema Postgres. Tidak diperlukan membuat skema basis data Postgres menggunakan bahasa deskripsi data. Untuk tabel yang ditentukan saat mengonfigurasi replikasi, alat pg_chameleon melakukan ini secara otomatis. Jika Anda perlu mengonversi jenis apa pun dengan cara tertentu, Anda dapat menentukan ini di file konfigurasi.

Berikut ini adalah latihan yang dapat Anda coba. Gunakan opsi yang disediakan jika sepenuhnya memenuhi persyaratan Anda. Kami melakukan tes ini pada CentOS Linux versi 7.4.

Persiapan lingkungan

Konfigurasikan Server Percona untuk MySQL

Instal MySQL versi 5.7 dan tambahkan opsi yang sesuai untuk replikasi.

Dalam latihan ini, saya menginstal Percona Server untuk MySQL versi 5.7 menggunakan repositori YUM.

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm yum install Percona-Server-server-57 echo "mysql ALL=(ALL) NOPASSWD: ALL" >> /etc/sudoers usermod -s /bin/bash mysql sudo su - mysql 

Untuk pg_chameleon, parameter berikut ini harus dikonfigurasi dalam file my.cnf (file dengan parameter server MySQL). Anda dapat menambahkan opsi berikut ke file /etc/my.cnf

 binlog_format= ROW binlog_row_image=FULL log-bin = mysql-bin server-id = 1 

Sekarang, setelah memasukkan parameter di atas dalam file my.cnf, mulai server MySQL.

 $ service mysql start 

Dapatkan kata sandi sementara untuk akun root dari file mysqld.log dan setel ulang kata sandi root menggunakan perintah mysqladmin.

 $ grep "temporary" /var/log/mysqld.log $ mysqladmin -u root -p password 'Secret123!' 

Sekarang terhubung ke instance Anda sendiri dari database MySQL dan buat contoh skema / tabel. Saya juga membuat tabel emp untuk verifikasi.

 $ wget http://downloads.mysql.com/docs/sakila-db.tar.gz $ tar -xzf sakila-db.tar.gz $ mysql -uroot -pSecret123! < sakila-db/sakila-schema.sql $ mysql -uroot -pSecret123! < sakila-db/sakila-data.sql $ mysql -uroot -pSecret123! sakila -e "create table emp (id int PRIMARY KEY, first_name varchar(20), last_name varchar(20))" 

Buat pengguna untuk mengonfigurasi replikasi menggunakan alat pg_chameleon dan berikan hak yang sesuai dengan mengikuti langkah-langkah ini:

 $ mysql -uroot -p create user 'usr_replica'@'%' identified by 'Secret123!'; GRANT ALL ON sakila.* TO 'usr_replica'@'%'; GRANT RELOAD, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'usr_replica'@'%'; FLUSH PRIVILEGES; 

Saat membuat pengguna di server MySQL ('usr_replica' @ '%'), mungkin perlu mengganti karakter "%" dengan alamat IP yang sesuai atau nama host dari server yang menjalankan pg_chameleon.

Konfigurasikan PostgreSQL

Instal PostgreSQL dan jalankan salinan database.

Ikuti langkah-langkah ini untuk menginstal PostgreSQL versi 10.x.

 yum install https://yum.postgresql.org/10/redhat/rhel-7.4-x86_64/pgdg-centos10-10-2.noarch.rpm yum install postgresql10* su - postgres $/usr/pgsql-10/bin/initdb $ /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data start 

Seperti yang Anda lihat dari log-log berikut, Anda perlu membuat pengguna di PostgreSQL yang dengannya pg_chameleon dapat menulis data yang dimodifikasi ke PostgreSQL. Juga buat database target.

 postgres=# CREATE USER usr_replica WITH ENCRYPTED PASSWORD 'secret'; CREATE ROLE postgres=# CREATE DATABASE db_replica WITH OWNER usr_replica; CREATE DATABASE 

Langkah-langkah untuk menginstal dan mengkonfigurasi replikasi menggunakan pg_chameleon

Tahap 1. Dalam latihan ini, saya menginstal interpreter Python versi 3.6 dan pg_chameleon versi 2.0.8 dengan mengikuti langkah-langkah ini. Jika Anda sudah menginstal versi interpreter Python yang diperlukan, Anda dapat melewati langkah-langkah untuk menginstalnya. Kita dapat membuat lingkungan virtual jika OS tidak menyertakan Python versi 3.x secara default.

 yum install gcc openssl-devel bzip2-devel wget cd /usr/src wget https://www.python.org/ftp/python/3.6.6/Python-3.6.6.tgz tar xzf Python-3.6.6.tgz cd Python-3.6.6 ./configure --enable-optimizations make altinstall python3.6 -m venv venv source venv/bin/activate pip install pip --upgrade pip install pg_chameleon 

Tahap 2. Alat ini membutuhkan file konfigurasi tempat informasi tentang sumber dan server target akan disimpan, dan folder untuk menyimpan log. Untuk meminta pg_chameleon membuat template file konfigurasi dan folder yang sesuai, gunakan perintah berikut.

 $ chameleon set_configuration_files 

Ketika perintah ini dijalankan, hasil berikut ditampilkan. Mereka menunjukkan bahwa perintah ini membuat beberapa folder dan file di tempat Anda menjalankannya.

 creating directory /var/lib/pgsql/.pg_chameleon creating directory /var/lib/pgsql/.pg_chameleon/configuration/ creating directory /var/lib/pgsql/.pg_chameleon/logs/ creating directory /var/lib/pgsql/.pg_chameleon/pid/ copying configuration example in /var/lib/pgsql/.pg_chameleon/configuration//config-example.yml 

Salin file konfigurasi sampel ke file lain, misalnya default.yml

 $ cd .pg_chameleon/configuration/ $ cp config-example.yml default.yml 

Seperti inilah file default.yml saya setelah memasukkan semua parameter yang diperlukan di dalamnya. Dalam file ini, jika perlu, Anda dapat menentukan konversi tipe data, tabel yang harus dilewati selama replikasi, dan peristiwa bahasa manipulasi data yang harus diabaikan untuk daftar tabel yang dipilih.

 --- #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: "localhost" port: "5432" user: "usr_replica" password: "secret" database: "db_replica" charset: "utf8" sources: mysql: db_conn: host: "localhost" port: "3306" user: "usr_replica" password: "Secret123!" charset: 'utf8' connect_timeout: 10 schema_mappings: sakila: sch_sakila limit_tables: # - delphis_mediterranea.foo skip_tables: # - delphis_mediterranea.bar grant_select_to: - usr_readonly lock_timeout: "120s" my_server_id: 100 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: No type: mysql skip_events: insert: # - delphis_mediterranea.foo #skips inserts on the table delphis_mediterranea.foo delete: # - delphis_mediterranea #skips deletes on schema delphis_mediterranea update: 

Langkah 3. Buat replika (database target) menggunakan perintah:

 $ chameleon create_replica_schema --debug 

Perintah di atas membuat skema dan sembilan tabel dalam database PostgreSQL yang ditentukan dalam file .pg_chameleon / configuration / default.yml. Tabel-tabel ini diperlukan untuk mengontrol replikasi dari sumber database ke target. Hal yang sama dapat dilihat di majalah berikutnya.

 db_replica=# \dn List of schemas Name | Owner ---------------+------------- public | postgres sch_chameleon | target_user (2 rows) db_replica=# \dt sch_chameleon.t_* List of relations Schema | Name | Type | Owner ---------------+------------------+-------+------------- sch_chameleon | t_batch_events | table | target_user sch_chameleon | t_discarded_rows | table | target_user sch_chameleon | t_error_log | table | target_user sch_chameleon | t_last_received | table | target_user sch_chameleon | t_last_replayed | table | target_user sch_chameleon | t_log_replica | table | target_user sch_chameleon | t_replica_batch | table | target_user sch_chameleon | t_replica_tables | table | target_user sch_chameleon | t_sources | table | target_user (9 rows) 

Langkah 4. Tambahkan data basis data sumber ke pg_chameleon menggunakan perintah berikut. Tentukan nama sumber database seperti yang ditentukan dalam file konfigurasi. Dalam contoh ini, nama database sumber adalah mysql, dan targetnya adalah database Postgres, didefinisikan sebagai pg_conn.

 $ chameleon add_source --config default --source mysql --debug 

Setelah menjalankan perintah yang ditentukan, Anda akan melihat bahwa data dari database asli ditambahkan ke tabel t_sources.

 db_replica=# select * from sch_chameleon.t_sources; -[ RECORD 1 ]-------+---------------------------------------------- i_id_source | 1 t_source | mysql jsb_schema_mappings | {"sakila": "sch_sakila"} enm_status | ready t_binlog_name | i_binlog_position | b_consistent | t b_paused | f b_maintenance | f ts_last_maintenance | enm_source_type | mysql v_log_table | {t_log_replica_mysql_1,t_log_replica_mysql_2} $ chameleon show_status --config default Source id Source name Type Status Consistent Read lag Last read Replay lag Last replay ----------- ------------- ------ -------- ------------ ---------- ----------- ------------ ------------- 1 mysql mysql ready Yes N/AN/A 

Langkah 5. Inisialisasi replika (database target) menggunakan perintah berikut. Tentukan basis data sumber dari mana perubahan direplikasi ke database PostgreSQL.

 $ chameleon init_replica --config default --source mysql --debug 

Inisialisasi mencakup tugas-tugas berikut di server MySQL (sumber).

1. Bersihkan cache tabel dan atur kunci read-only
2. Dapatkan koordinat dari basis data sumber
3. Salin data
4. Buka kunci

Perintah di atas secara otomatis membuat skema database target Postgres.
Dalam file default.yml, kami menyebutkan pemetaan skema berikut (schema_mappings).

 schema_mappings: sakila: sch_sakila 

Skema scott baru sekarang telah dibuat di database target db_replica.

 db_replica=# \dn List of schemas Name | Owner ---------------+------------- public | postgres sch_chameleon | usr_replica sch_sakila | usr_replica (3 rows) 

Langkah 6. Sekarang mulai replikasi menggunakan perintah berikut.

 $ chameleon start_replica --config default --source mysql 

Langkah 7. Verifikasi status replikasi dan kesalahan menggunakan perintah berikut.

 $ chameleon show_status --config default $ chameleon show_errors 

Seperti inilah bentuk replikasi:

 $ chameleon show_status --source mysql Source id Source name Type Status Consistent Read lag Last read Replay lag Last replay ----------- ------------- ------ -------- ------------ ---------- ----------- ------------ ------------- 1 mysql mysql running No N/AN/A == Schema mappings == Origin schema Destination schema --------------- -------------------- sakila sch_sakila == Replica status == --------------------- --- Tables not replicated 0 Tables replicated 17 All tables 17 Last maintenance N/A Next maintenance N/A Replayed rows Replayed DDL Skipped rows 

Sekarang Anda dapat melihat bahwa perubahan terus-menerus direplikasi dari database MySQL ke database PostgreSQL.

Langkah 8. Untuk verifikasi, Anda bisa memasukkan catatan ke dalam tabel database MySQL yang kami buat untuk memeriksa replikasi di database Postgres.

 $ mysql -u root -pSecret123! -e "INSERT INTO sakila.emp VALUES (1,'avinash','vallarapu')" mysql: [Warning] Using a password on the command line interface can be insecure. $ psql -d db_replica -c "select * from sch_sakila.emp" id | first_name | last_name ----+------------+----------- 1 | avinash | vallarapu (1 row) 

Dapat dilihat dari log di atas bahwa catatan yang dimasukkan ke dalam tabel MySQL direplikasi ke tabel dalam database Postgres.

Anda juga dapat menambahkan beberapa basis data replikasi sumber ke database target Postgres.

Tautan

Dari dokumentasi ini Anda dapat mempelajari tentang banyak fitur tambahan yang tersedia di pg_chameleon.