Gunakan ekstensi mysql_fdw untuk membaca data dari dan menulis data ke database MySQL - ApsaraDB RDS

Topik ini menjelaskan cara menggunakan ekstensi mysql_fdw dari ApsaraDB RDS for PostgreSQL untuk membaca data dari dan menulis data ke database MySQL. Database MySQL dapat berupa database pada instance ApsaraDB RDS for MySQL atau database MySQL yang dikelola sendiri.

Prasyarat

Instance RDS harus menjalankan PostgreSQL 10 atau versi lebih baru dengan SSD standar atau SSD yang ditingkatkan (ESSD).
Catatan
- Jika instance RDS Anda menjalankan PostgreSQL 14, versi mesin minor dari instance RDS Anda harus 20221030 atau lebih baru.
- Jika instance RDS Anda menjalankan PostgreSQL 17, versi mesin minor dari instance RDS Anda harus 20241030 atau lebih baru.
Untuk informasi lebih lanjut tentang cara melihat dan memperbarui versi mesin minor, lihat Perbarui Versi Mesin Minor.
Blok CIDR dari VPC tempat instance RDS berada harus ditambahkan ke daftar putih alamat IP dari database MySQL agar instance RDS dapat berkomunikasi dengan database MySQL. Contoh blok CIDR adalah 172.xx.xx.xx/16.
Catatan
Anda dapat melihat blok CIDR di halaman Database Connection dari instance RDS.

Informasi latar belakang

PostgreSQL 9.6 dan versi lebih baru mendukung komputasi paralel. PostgreSQL 11 dapat menyelesaikan kueri join pada hingga 1 miliar catatan data dalam hitungan detik. Sejumlah besar pengguna menggunakan PostgreSQL untuk membangun gudang data berskala kecil dan memproses permintaan akses dengan tingkat konkurensi tinggi.

Ekstensi mysql_fdw dapat membuat koneksi dan menyinkronkan data antara instance RDS dan database MySQL.

Prosedur

Buat ekstensi mysql_fdw.
```
postgres=> create extension mysql_fdw;  
CREATE EXTENSION  
```
Catatan
Hanya akun istimewa yang memiliki izin untuk mengeksekusi pernyataan tersebut.

Tentukan server MySQL.

postgres=> CREATE SERVER <Nama server MySQL>  
postgres->      FOREIGN DATA WRAPPER mysql_fdw
postgres->      OPTIONS (host '<Titik akhir server MySQL>', port '<Nomor port server MySQL>');  
CREATE SERVER

Catatan

Nilai dari host harus menjadi titik akhir internal dari server MySQL. Nilai dari port harus menjadi nomor port internal dari server MySQL.

Contoh:

postgres=> CREATE SERVER mysql_server  
postgres->      FOREIGN DATA WRAPPER mysql_fdw
postgres->      OPTIONS (host 'rm-xxx.mysql.rds.aliyuncs.com', port '3306');  
CREATE SERVER

Peta server MySQL ke akun yang dibuat pada instance RDS. Gunakan akun tersebut untuk membaca data dari dan menulis data ke database MySQL pada server MySQL.

postgres=> CREATE USER MAPPING FOR <Nama pengguna akun yang dipetakan ke server MySQL>   
SERVER <Nama server MySQL>  
OPTIONS (username '<Nama pengguna akun yang digunakan untuk terhubung ke database MySQL>', password '<Kata sandi akun sebelumnya>');  
CREATE USER MAPPING

Contoh:

postgres=> CREATE USER MAPPING FOR pgtest 
SERVER mysql_server  
OPTIONS (username 'mysqltest', password 'Test1234!');  
CREATE USER MAPPING

Buat tabel MySQL asing menggunakan akun yang telah dipetakan ke server MySQL pada langkah sebelumnya.

Catatan

Nama bidang dalam tabel MySQL asing harus sama dengan nama bidang dalam tabel di database MySQL. Anda dapat memilih untuk hanya membuat bidang yang ingin Anda kueri. Misalnya, jika tabel di database MySQL berisi bidang ID, NAME, dan AGE, Anda dapat membuat hanya bidang ID dan NAME dalam tabel MySQL asing.

postgres=> CREATE FOREIGN TABLE <Nama tabel MySQL asing> (<Nama bidang 1> <Tipe data bidang 1>,<Nama bidang 2> <Tipe data bidang 2>...) server <Nama server MySQL> options (dbname '<Nama database MySQL>', table_name '<Nama tabel di database MySQL>');  
CREATE FOREIGN TABLE

Contoh:

postgres=> CREATE FOREIGN TABLE ft_test (id1 int, name1 text) server mysql_server options (dbname 'test123', table_name 'test');  
CREATE FOREIGN TABLE

Apa yang harus dilakukan selanjutnya

Gunakan tabel MySQL asing untuk memeriksa kinerja operasi baca dan tulis pada database MySQL.

Catatan

Data hanya dapat ditulis ke tabel di database MySQL jika tabel tersebut memiliki kunci utama. Jika tabel tidak memiliki kunci utama, kesalahan berikut akan dikembalikan:

ERROR:  kolom pertama dari tabel remote harus unik untuk operasi INSERT/UPDATE/DELETE.

postgres=> select * from ft_test ;  

postgres=> insert into ft_test values (2,'abc');  
INSERT 0 1  

postgres=> insert into ft_test select generate_series(3,100),'abc';  
INSERT 0 98  
postgres=> select count(*) from ft_test ;  
 count   
-------  
    99  
(1 row)

Lihat rencana eksekusi untuk memeriksa bagaimana permintaan yang dikirim oleh instance RDS untuk mengkueri data dari database MySQL dieksekusi.

postgres=> explain verbose select count(*) from ft_test ;  
                                  QUERY PLAN                                     
-------------------------------------------------------------------------------  
 Aggregate  (cost=1027.50..1027.51 rows=1 width=8)  
   Output: count(*)  
   ->  Foreign Scan on public.ft_test  (cost=25.00..1025.00 rows=1000 width=0)  
         Output: id, info  
         Remote server startup cost: 25  
         Remote query: SELECT NULL FROM `test123`.`test`  
(6 rows)  

postgres=> explain verbose select id from ft_test where id=2;  
                               QUERY PLAN                                  
-------------------------------------------------------------------------  
 Foreign Scan on public.ft_test  (cost=25.00..1025.00 rows=1000 width=4)  
   Output: id  
   Remote server startup cost: 25  
   Remote query: SELECT `id` FROM `test123`.`test` WHERE ((`id` = 2))  
(4 rows)