GALAXY TECH
Chuyên gia Công nghệ
Thứ ba, 28/05/2019 | 00:00 GMT+7

Cách di chuyển database MySQL sang PostgreSQL bằng pgLoader

PostgreSQL , còn gọi là “Postgres,” là một hệ quản trị database quan hệ open-souce (RDBMS). Nó đã chứng kiến sự tăng trưởng mạnh mẽ về mức độ phổ biến trong những năm gần đây, với nhiều nhà phát triển và công ty chuyển dữ liệu của họ sang Postgres từ các giải pháp database khác.

Triển vọng di chuyển database có thể đáng sợ, đặc biệt là khi di chuyển từ hệ thống quản lý database này sang hệ thống quản lý database khác. pgLoader là một công cụ di chuyển database open-souce nhằm mục đích đơn giản hóa quá trình di chuyển sang PostgreSQL. Nó hỗ trợ di chuyển từ một số loại file và RBDMS - bao gồm MySQL và SQLite - sang PostgreSQL.

Hướng dẫn này cung cấp hướng dẫn về cách cài đặt pgLoader và sử dụng nó để di chuyển database MySQL từ xa sang PostgreSQL qua kết nối SSL. Ở gần cuối hướng dẫn, ta cũng sẽ đề cập ngắn gọn đến một số trường hợp di chuyển khác nhau mà pgLoader có thể hữu ích.

Yêu cầu

Để hoàn thành hướng dẫn này, bạn cần những thứ sau:

  • Truy cập vào hai server , mỗi server chạy Ubuntu 18.04. Cả hai server phải có firewall và user không phải root có quyền sudo được cấu hình . Để cài đặt những điều này, bạn có thể làm theo hướng dẫn Cài đặt Server Ban đầu của ta cho Ubuntu 18.04 .
  • MySQL được cài đặt trên một trong các server . Để cài đặt điều này, hãy làm theo các Bước 1, 2 và 3 trong hướng dẫn của ta về Cách cài đặt MySQL trên Ubuntu 18.04 . Xin lưu ý để hoàn thành tất cả các hướng dẫn yêu cầu được liên kết ở đây, bạn cần phải cấu hình user MySQL gốc của bạn để xác thực bằng password , như được mô tả trong Bước 3 của hướng dẫn cài đặt MySQL.
  • PostgreSQL được cài đặt trên server khác . Để cài đặt điều này, hãy hoàn thành Bước 1 của hướng dẫn Cách cài đặt và sử dụng PostgreSQL trên Ubuntu 18.04 .
  • Server MySQL của bạn cũng phải được cấu hình để chấp nhận các kết nối được mã hóa. Để cài đặt điều này, hãy hoàn thành mọi bước trong hướng dẫn của ta về Cách cấu hình SSL / TLS cho MySQL trên Ubuntu 18.04 , bao gồm cả Bước 6 tùy chọn. Khi bạn làm theo hướng dẫn này, hãy đảm bảo sử dụng server PostgreSQL của bạn làm client MySQL, vì bạn cần có thể kết nối với server MySQL từ máy Postgres của bạn để di chuyển dữ liệu bằng pgLoader.

Xin lưu ý trong suốt hướng dẫn này, server mà bạn đã cài đặt MySQL sẽ được gọi là “ server MySQL ” và bất kỳ lệnh nào sẽ được chạy trên máy này sẽ được hiển thị với nền màu xanh lam, như sau:

  •  

Tương tự, hướng dẫn này sẽ tham chiếu đến server khác là server “ PostgreSQL ” hoặc “ Postgres” và bất kỳ lệnh nào phải chạy trên máy đó sẽ được hiển thị với nền màu đỏ:

  •  

Hãy ghi nhớ những điều này khi bạn làm theo hướng dẫn này để tránh bất kỳ sự nhầm lẫn nào.

Bước 1 - (Tùy chọn) Tạo database mẫu và bảng trong MySQL

Bước này mô tả quá trình tạo database thử nghiệm và đưa nó vào dữ liệu giả. Ta khuyến khích bạn thực hành sử dụng pgLoader với trường hợp thử nghiệm này, nhưng nếu bạn đã có database muốn di chuyển, bạn có thể chuyển sang bước tiếp theo .

Bắt đầu bằng cách mở dấu nhắc MySQL trên server MySQL của bạn:

  • mysql -u root -p

Sau khi nhập password user MySQL gốc của bạn, bạn sẽ thấy dấu nhắc MySQL.

Từ đó, tạo một database mới bằng cách chạy lệnh sau. Bạn có thể đặt tên database của bạn bạn muốn , nhưng trong hướng dẫn này, ta sẽ đặt tên nó là source_db :

  • CREATE DATABASE source_db;

Sau đó chuyển sang database này bằng lệnh USE :

  • USE source_db;
Output
Database changed

Trong database này, sử dụng lệnh sau để tạo bảng mẫu. Ở đây, ta sẽ đặt tên cho bảng này là sample_table nhưng hãy đặt tên khác cho nó:

  • CREATE TABLE sample_table (
  • employee_id INT PRIMARY KEY,
  • first_name VARCHAR(50),
  • last_name VARCHAR(50),
  • start_date DATE,
  • salary VARCHAR(50)
  • );

Sau đó điền vào bảng này một số dữ liệu mẫu về nhân viên bằng lệnh sau:

  • INSERT INTO sample_table (employee_id, first_name, last_name, start_date, salary)
  • VALUES (1, 'Elizabeth', 'Cotten', '2007-11-11', '$105433.18'),
  • (2, 'Yanka', 'Dyagileva', '2017-10-30', '$107540.67'),
  • (3, 'Lee', 'Dorsey', '2013-06-04', '$118024.04'),
  • (4, 'Kasey', 'Chambers', '2010-08-18', '$116456.98'),
  • (5, 'Bram', 'Tchaikovsky', '2018-09-16', '$61989.50');

Sau đây, bạn có thể đóng dấu nhắc MySQL:

  • exit

Đến đây bạn đã có một database mẫu được tải với dữ liệu giả, bạn có thể chuyển sang bước tiếp theo, trong đó bạn sẽ cài đặt pgLoader trên server PostgreSQL của bạn .

Bước 2 - Cài đặt pgLoader

pgLoader là một chương trình có thể tải dữ liệu vào database PostgreSQL từ nhiều nguồn khác nhau. Nó sử dụng lệnh COPY của PostgreSQL để sao chép dữ liệu từ database nguồn hoặc file - chẳng hạn như tệp giá trị được phân tách bằng dấu phẩy (CSV) - vào database PostgreSQL đích.

pgLoader có sẵn từ các repository Ubuntu APT mặc định và bạn có thể cài đặt nó bằng apt . Tuy nhiên, trong hướng dẫn này, ta sẽ tận dụng tùy chọn useSSL của useSSL , một tính năng cho phép di chuyển từ MySQL qua kết nối SSL. Tính năng này chỉ có sẵn trong version pgLoader mới nhất. Tính năng này chỉ có thể được cài đặt bằng cách sử dụng mã nguồn từ repository GitHub của nó.

Trước khi cài đặt pgLoader, bạn cần cài đặt các phụ thuộc của nó. Nếu gần đây bạn chưa làm như vậy, hãy cập nhật index gói của server Postgres của bạn :

  • sudo apt update

Sau đó cài đặt các gói sau:

  • sbcl : Một trình biên dịch Lisp chung
  • unzip : Một trình unzip cho các .zip
  • libsqlite3-dev : Tập hợp các file phát triển cho SQLite 3
  • gawk : Viết tắt của “GNU awk”, một ngôn ngữ xử lý và quét mẫu
  • curl : Một công cụ dòng lệnh để truyền dữ liệu từ một URL
  • make : Một tiện ích để quản lý việc biên dịch gói
  • freetds-dev : Một thư viện client cho database MS SQL và Sybase
  • libzip-dev : Một thư viện để đọc, tạo và sửa đổi các repository zip

Sử dụng lệnh sau để cài đặt các phần phụ thuộc này:

  • sudo apt install sbcl unzip libsqlite3-dev gawk curl make freetds-dev libzip-dev

Khi được yêu cầu , hãy xác nhận bạn muốn cài đặt các gói này bằng cách nhấn ENTER .

Tiếp theo, chuyển đến trang Bản phát hành của dự án pgLoader GitHub và tìm bản phát hành mới nhất. Đối với hướng dẫn này, ta sẽ sử dụng version mới nhất tại thời điểm viết bài này: phiên bản 3.6.1 . Cuộn xuống menu Nội dung và sao chép liên kết cho file tar.gz có nhãn Mã nguồn . Sau đó, dán liên kết vào lệnh wget sau. Thao tác này sẽ tải tarball xuống server của bạn:

  • wget https://github.com/dimitri/pgloader/archive/v3.6.1.tar.gz

Extract tarball:

  • tar xvf v3.6.1.tar.gz

Thao tác này sẽ tạo một số folder và file mới trên server của bạn. Điều hướng vào folder mẹ pgLoader mới:

  • cd pgloader-3.6.1/

Sau đó, sử dụng trình make để biên dịch pgloader binary pgloader :

  • make pgloader

Lệnh này sẽ mất một khoảng thời gian để tạo pgloader binary pgloader .

Di chuyển file binary vào folder /usr/local/bin , vị trí mà Ubuntu tìm kiếm các file thực thi:

  • sudo mv ./build/bin/pgloader /usr/local/bin/

Bạn có thể kiểm tra xem pgLoader đã được cài đặt đúng cách hay chưa bằng cách kiểm tra version của nó, như sau:

  • pgloader --version
Output
pgloader version "3.6.1"
compiled with SBCL 1.4.5.debian

pgLoader hiện đã được cài đặt, nhưng trước khi có thể bắt đầu quá trình di chuyển, bạn cần thực hiện một số thay đổi cấu hình cho cả hai version PostgreSQL và MySQL của bạn . Trước tiên, ta sẽ tập trung vào server PostgreSQL.

Bước 3 - Tạo role và database PostgreSQL

Lệnh pgloader hoạt động bằng cách sao chép dữ liệu nguồn, từ file hoặc trực tiếp từ database và chèn nó vào database PostgreSQL. Vì lý do này, bạn phải chạy pgLoader với quyền là user Linux có quyền truy cập vào database Postgres của bạn hoặc bạn phải chỉ định một role PostgreSQL với các quyền thích hợp trong lệnh tải của bạn.

PostgreSQL quản lý quyền truy cập database thông qua việc sử dụng các role . Tùy thuộc vào cách cấu hình role , nó có thể được coi là một user database hoặc một group user database . Trong hầu hết các RDBMS, bạn tạo user bằng CREATE USER SQL. Tuy nhiên, Postgres được cài đặt với một script tiện dụng có tên là createuser . Tập lệnh này đóng role như một shell bọc cho CREATE USER SQL CREATE USER mà bạn có thể chạy trực tiếp từ dòng lệnh.

Lưu ý: Trong PostgreSQL, bạn xác thực như một người sử dụng database bằng cách sử dụng giao thức xác định , hoặc ident, phương pháp thẩm định theo mặc định, chứ không phải với một password . Điều này liên quan đến việc PostgreSQL lấy tên user Ubuntu của khách hàng và sử dụng nó làm tên user database Postgres được phép. Điều này cho phép bảo mật cao hơn trong nhiều trường hợp, nhưng nó cũng có thể gây ra sự cố trong trường hợp bạn muốn một chương trình bên ngoài kết nối với một trong các database của bạn.

pgLoader có thể tải dữ liệu vào database Postgres thông qua một role xác thực với phương thức nhận dạng miễn là role đó có cùng tên với profile user Linux ban hành lệnh pgloader . Tuy nhiên, để giữ cho quá trình này rõ ràng nhất có thể, hướng dẫn này mô tả việc cài đặt một role PostgreSQL khác xác thực bằng password chứ không phải bằng phương thức nhận dạng.

Chạy lệnh sau trên server Postgres của bạn để tạo một role mới. Lưu ý -P cờ, mà nói createuser để nhắc bạn nhập password cho role mới:

  • sudo -u postgres createuser --interactive -P

Trước tiên, bạn có thể được yêu cầu nhập password sudo của bạn . Sau đó, tập lệnh sẽ nhắc bạn nhập tên cho role mới. Trong hướng dẫn này, ta sẽ gọi role này là pgloader_pg :

Output
Enter name of role to add: pgloader_pg

Sau đó, createuser sẽ nhắc bạn nhập và xác nhận password cho role này. Hãy nhớ ghi lại password này, vì bạn cần password để thực hiện di chuyển trong Bước 5:

Output
Enter password for new role: 
Enter it again:

Cuối cùng, kịch bản sẽ hỏi bạn xem liệu role mới có nên được gọi là siêu user hay không. Trong PostgreSQL, kết nối với database với role siêu user cho phép bạn phá vỡ tất cả các kiểm tra quyền của database , ngoại trừ quyền đăng nhập. Do đó, quyền siêu user không nên được sử dụng nhẹ nhàng và tài liệu PostgreSQL khuyên bạn hầu hết database của bạn hoạt động như một role không phải user cấp cao. Tuy nhiên, vì pgLoader cần các quyền rộng rãi để truy cập và tải dữ liệu vào các bảng, bạn có thể an toàn cấp quyền user siêu user role mới này. Làm như vậy bằng lệnh y rồi nhấn ENTER :

Output
. . .
Shall the new role be a superuser? (y/n) y

PostgreSQL đi kèm với một tập lệnh hữu ích khác cho phép bạn tạo database từ dòng lệnh. Vì pgLoader cũng cần một database đích mà nó có thể tải dữ liệu nguồn vào đó, hãy chạy lệnh sau để tạo một database . Ta sẽ đặt tên cho database này là new_db nhưng hãy sửa đổi nếu bạn muốn:

  • sudo -u postgres createdb new_db

Nếu không có bất kỳ lỗi nào, lệnh này sẽ hoàn thành mà không có bất kỳ kết quả nào.

Đến đây bạn có một user PostgreSQL chuyên dụng và một database trống để bạn có thể tải dữ liệu MySQL của bạn vào đó, bạn chỉ cần thực hiện một số thay đổi nữa trước khi thực hiện di chuyển. Bạn cần tạo một user MySQL chuyên dụng có quyền truy cập vào database nguồn của bạn và thêm các certificate phía client của bạn vào kho certificate tin cậy của Ubuntu.

Bước 4 - Tạo user chuyên dụng trong MySQL và quản lý certificate

Bảo vệ dữ liệu khỏi những kẻ rình mò là một trong những phần quan trọng nhất trong công việc của bất kỳ administrator database nào. Việc di chuyển dữ liệu từ máy này sang máy khác mở ra cơ hội cho những kẻ độc hại đánh hơi các gói truyền qua kết nối mạng nếu nó không được mã hóa. Trong bước này, bạn sẽ tạo một user MySQL chuyên dụng mà pgLoader sẽ sử dụng để thực hiện việc di chuyển qua kết nối SSL.

Bắt đầu bằng cách mở dấu nhắc MySQL của bạn:

  • mysql -u root -p

Từ dấu nhắc MySQL, sử dụng CREATE USER sau để tạo user MySQL mới. Ta sẽ đặt tên cho user này là pgloader_my . Vì user này sẽ chỉ truy cập MySQL từ server PostgreSQL của bạn, hãy đảm bảo thay thế your_postgres_server_ip bằng địa chỉ IP công cộng của server PostgreSQL của bạn. Ngoài ra, thay thế password bằng password hoặc passphrase (password bảo vệ) an toàn:

  • CREATE USER 'pgloader_my'@'your_postgres_server_ip' IDENTIFIED BY 'password' REQUIRE SSL;

Lưu ý điều khoản REQUIRE SSL ở cuối lệnh này. Điều này sẽ hạn chế user pgloader_my chỉ truy cập database thông qua kết nối SSL an toàn.

Tiếp theo, cấp cho user pgloader_my quyền truy cập vào database đích và tất cả các bảng của nó. Ở đây, ta sẽ chỉ định database mà ta đã tạo trong Bước 1 tùy chọn, nhưng nếu bạn có database của riêng mình mà bạn muốn di chuyển, hãy sử dụng tên của nó thay cho source_db :

  • GRANT ALL ON source_db.* TO 'pgloader_my'@'your_postgresql_server_ip';

Sau đó, chạy lệnh FLUSH PRIVILEGES để reload các bảng-cấp quyền , cho phép các thay đổi quyền :

  • FLUSH PRIVILEGES;

Sau đó, bạn có thể đóng dấu nhắc MySQL:

  • exit

Bây giờ quay lại terminal server Postgres của bạn và cố gắng đăng nhập vào server MySQL với quyền là user pgloader_my mới. Nếu bạn đã làm theo hướng dẫn tiên quyết về cách cấu hình SSL / TLS cho MySQL thì bạn đã cài đặt mysql-client trên server PostgreSQL của bạn và bạn có thể kết nối bằng lệnh sau:

  • mysql -u pgloader_my -p -h your_mysql_server_ip

Nếu lệnh thành công, bạn sẽ thấy dấu nhắc MySQL:

  •  

Sau khi xác nhận user pgloader_my của bạn có thể kết nối thành công, hãy tiếp tục và đóng dấu nhắc :

  • exit

Đến đây, bạn có một user MySQL chuyên dụng có thể truy cập database nguồn từ máy Postgres của bạn. Tuy nhiên, nếu bạn cố gắng di chuyển database MySQL của bạn bằng SSL thì nỗ lực sẽ không thành công.

Lý do cho điều này là pgLoader không thể đọc các file cấu hình của MySQL và do đó không biết tìm certificate CA hoặc certificate ứng dụng client mà bạn đã sao chép vào server PostgreSQL của bạn ở đâu trong hướng dẫn cấu hình SSL / TLS yêu cầu . Tuy nhiên, thay vì bỏ qua các yêu cầu SSL, pgLoader yêu cầu sử dụng các certificate tin cậy trong các trường hợp cần SSL để kết nối với MySQL. Theo đó, bạn có thể giải quyết vấn đề này bằng cách thêm ca.pem và client-cert.pem vào kho lưu trữ certificate tin cậy của Ubuntu .

Để thực hiện việc này, hãy sao chép các ca.pem và client-cert.pem vào folder /usr/local/share/ca-certificates/ . Lưu ý bạn cũng phải đổi tên các file này để chúng có phần mở rộng là file .crt . Nếu bạn không đổi tên chúng, hệ thống của bạn sẽ không thể nhận ra rằng bạn đã thêm các certificate mới này:

  • sudo cp ~/client-ssl/ca.pem /usr/local/share/ca-certificates/ca.pem.crt
  • sudo cp ~/client-ssl/client-cert.pem /usr/local/share/ca-certificates/client-cert.pem.crt

Sau đó, chạy lệnh update-ca-certificates . Chương trình này tìm kiếm các certificate trong /usr/local/share/ca-certificates , thêm bất kỳ /usr/local/share/ca-certificates mới nào vào folder /etc/ssl/certs/ và tạo danh sách các certificate SSL tin cậy - ca-certificates.crt - dựa trên nội dung của folder /etc/ssl/certs/ :

  • sudo update-ca-certificates
Output
Updating certificates in /etc/ssl/certs...
2 added, 0 removed; done.
Running hooks in /etc/ca-certificates/update.d...
done.

Như vậy, bạn đã sẵn sàng di chuyển database MySQL của bạn sang PostgreSQL.

Bước 5 - Di chuyển dữ liệu

Đến đây bạn đã cấu hình quyền truy cập từ xa từ server PostgreSQL đến server MySQL của bạn , bạn đã sẵn sàng để bắt đầu di chuyển.

Lưu ý: Điều quan trọng là phải backup database của bạn trước khi thực hiện bất kỳ hành động nào có thể ảnh hưởng đến tính toàn vẹn của dữ liệu. Tuy nhiên, điều này không cần thiết khi thực hiện di chuyển với pgLoader, vì nó không xóa hoặc biến đổi dữ liệu; nó chỉ sao chép nó.

Điều đó nói rằng, nếu bạn cảm thấy thận trọng và muốn backup dữ liệu của bạn trước khi di chuyển nó, bạn có thể làm như vậy với tiện ích mysqldump . Xem tài liệu MySQL chính thức để biết chi tiết.

pgLoader cho phép user di chuyển toàn bộ database bằng một lệnh duy nhất. Để di chuyển từ database MySQL sang database PostgreSQL trên một server riêng biệt, lệnh sẽ có cú pháp sau:

  • pgloader mysql://mysql_username:password@mysql_server_ip_/source_database_name?option_1=value&option_n=value postgresql://postgresql_role_name:password@postgresql_server_ip/target_database_name?option_1=value&option_n=value

Điều này bao gồm lệnh pgloader và hai chuỗi kết nối , chuỗi đầu tiên dành cho database nguồn và chuỗi thứ hai dành cho database đích. Cả hai chuỗi kết nối này đều bắt đầu bằng cách khai báo loại DBMS mà chuỗi kết nối trỏ tới, tiếp theo là tên user và password có quyền truy cập vào database (phân tách bằng dấu hai chấm), địa chỉ server của server nơi database được cài đặt, tên của database mà pgLoader nên nhắm đến và các tùy chọn khác nhau ảnh hưởng đến hành vi của pgLoader.

Sử dụng các tham số được định nghĩa đó trong hướng dẫn này, bạn có thể di chuyển database MySQL của bạn bằng lệnh có cấu trúc sau. Đảm bảo thay thế mọi giá trị được đánh dấu để phù hợp với cài đặt của bạn :

  • pgloader mysql://pgloader_my:mysql_password@mysql_server_ip/source_db?useSSL=true postgresql://pgloader_pg:postgresql_password@localhost/new_db

Lưu ý lệnh này bao gồm tùy chọn useSSL trong chuỗi kết nối MySQL. Bằng cách đặt tùy chọn này thành true , pgLoader sẽ kết nối với MySQL qua SSL. Điều này là cần thiết, vì bạn đã cấu hình server MySQL của bạn để chỉ chấp nhận các kết nối an toàn.

Nếu lệnh này thành công, bạn sẽ thấy một bảng kết quả mô tả quá trình di chuyển diễn ra như thế nào:

Output
             table name     errors       rows      bytes      total time
-----------------------  ---------  ---------  ---------  --------------
        fetch meta data          0          2                     0.111s
         Create Schemas          0          0                     0.001s
       Create SQL Types          0          0                     0.005s
          Create tables          0          2                     0.017s
         Set Table OIDs          0          1                     0.010s
-----------------------  ---------  ---------  ---------  --------------
 source_db.sample_table          0          5     0.2 kB          0.048s
-----------------------  ---------  ---------  ---------  --------------
COPY Threads Completion          0          4                     0.052s
 Index Build Completion          0          1                     0.011s
         Create Indexes          0          1                     0.006s
        Reset Sequences          0          0                     0.014s
           Primary Keys          0          1                     0.001s
    Create Foreign Keys          0          0                     0.000s
        Create Triggers          0          0                     0.000s
       Install Comments          0          0                     0.000s
-----------------------  ---------  ---------  ---------  --------------
      Total import time          ✓          5     0.2 kB          0.084s

Để kiểm tra xem dữ liệu đã được di chuyển đúng cách hay chưa, hãy mở dấu nhắc PostgreSQL:

  • sudo -i -u postgres psql

Từ đó, kết nối với database mà bạn đã tải dữ liệu vào đó:

  • \c new_db

Sau đó, chạy truy vấn sau để kiểm tra xem dữ liệu đã di chuyển có được lưu trữ trong database PostgreSQL của bạn hay không:

  • SELECT * FROM source_db.sample_table;

Lưu ý: Lưu ý mệnh đề FROM trong truy vấn này chỉ định sample_table được giữ trong lược đồ source_db :

  • . . . FROM source_db.sample_table;

Đây được gọi là tên đủ điều kiện . Bạn có thể đi xa hơn và chỉ định tên đủ điều kiện bằng cách bao gồm tên database cũng như tên của schemas và bảng:

  • . . . FROM new_db.source_db.sample_table;

Khi bạn chạy các truy vấn trong database PostgreSQL, bạn không cần phải xác định cụ thể này nếu bảng được giữ trong schemas public mặc định. Lý do bạn phải làm như vậy ở đây là khi pgLoader tải dữ liệu vào Postgres, nó sẽ tạo và nhắm đến một schemas mới được đặt tên theo database root - trong trường hợp này là source_db . Đây là hành vi mặc định của pgLoader đối với việc di chuyển MySQL sang PostgreSQL. Tuy nhiên, bạn có thể sử dụng file tải để hướng dẫn pgLoader thay đổi schemas của bảng thành public sau khi tải xong dữ liệu. Xem bước tiếp theo để biết ví dụ về cách thực hiện việc này.

Nếu dữ liệu thực sự được tải đúng cách, bạn sẽ thấy bảng sau trong kết quả của truy vấn:

Output
 employee_id | first_name |  last_name  | start_date |   salary   
-------------+------------+-------------+------------+------------
           1 | Elizabeth  | Cotten      | 2007-11-11 | $105433.18
           2 | Yanka      | Dyagileva   | 2017-10-30 | $107540.67
           3 | Lee        | Dorsey      | 2013-06-04 | $118024.04
           4 | Kasey      | Chambers    | 2010-08-18 | $116456.98
           5 | Bram       | Tchaikovsky | 2018-09-16 | $61989.50
(5 rows)

Để đóng dấu nhắc Postgres, hãy chạy lệnh sau:

  • \q

Bây giờ ta đã xem qua cách di chuyển database MySQL qua mạng và tải nó vào database PostgreSQL, ta sẽ xem xét một số tình huống di chuyển phổ biến khác mà pgLoader có thể hữu ích.

Bước 6 - Khám phá các tùy chọn di chuyển khác

pgLoader là một công cụ có tính linh hoạt cao, có thể hữu ích trong nhiều trường hợp. Ở đây, ta sẽ xem xét nhanh một số cách khác mà bạn có thể sử dụng pgLoader để di chuyển database MySQL sang PostgreSQL.

Di chuyển bằng file tải pgLoader

Trong ngữ cảnh của pgLoader, một tệp tải hoặc tệp lệnh , là một file cho pgLoader biết cách thực hiện di chuyển. Tệp này có thể bao gồm các lệnh và tùy chọn ảnh hưởng đến hành vi của pgLoader, cho phép bạn kiểm soát tốt hơn nhiều cách dữ liệu được tải vào PostgreSQL và cho phép bạn thực hiện các quá trình di chuyển phức tạp.

Tài liệu của pgLoader cung cấp hướng dẫn toàn diện về cách sử dụng và mở rộng các file này để hỗ trợ một số kiểu di chuyển, vì vậy ở đây ta sẽ làm việc thông qua một ví dụ tương đối thô sơ. Ta sẽ thực hiện quá trình di chuyển tương tự như ta đã chạy ở Bước 5, nhưng cũng sẽ bao gồm ALTER SCHEMA để thay đổi giản đồ của database new_db từ source_db thành public .

Để bắt đầu, hãy tạo một file tải mới trên server Postgres bằng editor bạn muốn :

  • nano pgload_test.load

Sau đó, thêm nội dung sau, đảm bảo cập nhật các giá trị được đánh dấu để phù hợp với cấu hình của bạn :

pgload_test.load
LOAD DATABASE
     FROM      mysql://pgloader_my:mysql_password@mysql_server_ip/source_db?useSSL=true
     INTO pgsql://pgloader_pg:postgresql_password@localhost/new_db

 WITH include drop, create tables

ALTER SCHEMA 'source_db' RENAME TO 'public'
;

Đây là những gì mỗi mệnh đề này làm:

  • LOAD DATABASE : Dòng này hướng dẫn pgLoader tải dữ liệu từ một database riêng biệt, thay vì một file hoặc repository dữ liệu.
  • FROM : Mệnh đề này chỉ định database nguồn. Trong trường hợp này, nó trỏ đến chuỗi kết nối cho database MySQL mà ta đã tạo ở Bước 1 .
  • INTO : Tương tự như vậy, dòng này chỉ định database PostgreSQL mà pgLoader sẽ tải dữ liệu vào đó.
  • WITH : Mệnh đề này cho phép bạn xác định các hành vi cụ thể cho pgLoader. Bạn có thể tìm thấy danh sách đầy đủ các tùy chọn WITH tương thích với di chuyển MySQL tại đây . Trong ví dụ này, ta chỉ bao gồm hai tùy chọn:
    • include drop : Khi tùy chọn này được sử dụng, pgLoader sẽ thả bất kỳ bảng nào trong database PostgreSQL đích cũng xuất hiện trong database MySQL nguồn. Nếu bạn sử dụng tùy chọn này khi di chuyển dữ liệu sang database PostgreSQL hiện có, bạn nên backup toàn bộ database để tránh mất bất kỳ dữ liệu nào.
    • create tables : Tùy chọn này yêu cầu pgLoader tạo các bảng mới trong database PostgreSQL đích dựa trên metadata có trong database MySQL. Nếu tùy chọn ngược lại, create no tables được sử dụng, thì các bảng đích phải đã tồn tại trong database Postgres đích trước khi di chuyển.
  • ALTER SCHEMA : Sau mệnh đề WITH , bạn có thể thêm các lệnh SQL cụ thể như thế này để hướng dẫn pgLoader thực hiện các hành động bổ sung. Ở đây, ta hướng dẫn pgLoader thay đổi schemas của database Postgres mới từ source_db thành public , nhưng chỉ sau khi nó đã tạo schemas . Lưu ý bạn cũng có thể lồng các lệnh như vậy trong các mệnh đề khác - chẳng hạn như BEFORE LOAD DO - để hướng dẫn pgLoader thực hiện các lệnh đó tại các điểm cụ thể trong quá trình di chuyển.

Đây là một ví dụ minh họa về những gì bạn có thể đưa vào file tải để sửa đổi hành vi của pgLoader. Danh sách đầy đủ các mệnh đề mà người ta có thể thêm vào file tải và những gì chúng làm có thể tìm thấy trong tài liệu chính thức của pgLoader .

Lưu file tải sau khi bạn đã hoàn tất việc thêm nội dung này. Để sử dụng nó, hãy bao gồm tên của file làm đối số cho lệnh pgloader :

  • pgloader pgload_test.load

Để kiểm tra xem quá trình di chuyển có thành công hay không, hãy mở dấu nhắc Postgres:

  • sudo -u postgres psql

Sau đó kết nối với database :

  • \c new_db

Và chạy truy vấn sau:

  • SELECT * FROM sample_table;
Output
 employee_id | first_name |  last_name  | start_date |   salary   
-------------+------------+-------------+------------+------------
           1 | Elizabeth  | Cotten      | 2007-11-11 | $105433.18
           2 | Yanka      | Dyagileva   | 2017-10-30 | $107540.67
           3 | Lee        | Dorsey      | 2013-06-04 | $118024.04
           4 | Kasey      | Chambers    | 2010-08-18 | $116456.98
           5 | Bram       | Tchaikovsky | 2018-09-16 | $61989.50
(5 rows)

Đầu ra này xác nhận pgLoader đã di chuyển dữ liệu thành công và ALTER SCHEMA mà ta đã thêm vào file tải hoạt động như mong đợi, vì ta không cần chỉ định giản đồ source_db trong truy vấn để xem dữ liệu.

Lưu ý nếu bạn định sử dụng file tải để di chuyển dữ liệu được lưu giữ trên database này sang database khác nằm trên một máy riêng biệt, bạn sẽ vẫn cần điều chỉnh mọi luật mạng và firewall có liên quan để quá trình di chuyển thành công.

Di chuyển database MySQL sang PostgreSQL local

Bạn có thể sử dụng pgLoader để di chuyển database MySQL sang database PostgreSQL được đặt trên cùng một máy. Tất cả những gì bạn cần là chạy lệnh di chuyển từ profile user Linux có quyền truy cập vào user MySQL gốc :

  • pgloader mysql://root@localhost/source_db pgsql://sammy:postgresql_password@localhost/target_db

Thực hiện di chuyển local như vậy nghĩa là bạn không phải thực hiện bất kỳ thay đổi nào đối với cấu hình mạng mặc định của MySQL hoặc các luật firewall của hệ thống .

Di chuyển từ file CSV

Bạn cũng có thể tải database PostgreSQL với dữ liệu từ file CSV.

Giả sử bạn có file CSV của dữ liệu có tên load.csv , lệnh tải file đó vào database Postgres có thể trông giống như sau:

  • pgloader load.csv pgsql://sammy:password@localhost/target_db

Vì định dạng CSV không được chuẩn hóa hoàn toàn, nên có khả năng bạn sẽ gặp sự cố khi tải dữ liệu trực tiếp từ file CSV theo cách này. May mắn là bạn có thể sửa lỗi bất thường bằng cách bao gồm các tùy chọn khác nhau với các tùy chọn dòng lệnh của pgLoader hoặc bằng cách chỉ định chúng trong một file tải. Xem tài liệu pgLoader về chủ đề này để biết thêm chi tiết.

Di chuyển sang Database PostgreSQL được quản lý

Cũng có thể thực hiện di chuyển từ database tự quản lý sang database PostgreSQL được quản lý. Để minh họa kiểu di chuyển này có thể trông như thế nào, ta sẽ sử dụng server MySQL và Database PostgreSQL được quản lý DigitalOcean. Ta cũng sẽ sử dụng database mẫu mà ta đã tạo ở Bước 1 , nhưng nếu bạn đã bỏ qua bước đó và có database của riêng mình mà bạn muốn di chuyển, bạn có thể trỏ đến database đó để thay thế.

Lưu ý: Để biết hướng dẫn về cách cài đặt Dịch vụdatabase DigitalOcean, vui lòng tham khảo hướng dẫn Bắt đầu nhanh database được quản lý của ta .

Đối với việc di chuyển này, ta sẽ không cần tùy chọn useSSL của useSSL vì nó chỉ hoạt động với database MySQL từ xa và ta sẽ chạy quá trình di chuyển này từ database MySQL local . Tuy nhiên, ta sẽ sử dụng tùy chọn sslmode=require khi ta tải và kết nối với database DigitalOcean Managed PostgreSQL, điều này sẽ đảm bảo dữ liệu luôn được bảo vệ.

Bởi vì ta không sử dụng useSSL lần này, bạn có thể sử dụng apt để cài đặt pgLoader cùng với gói postgresql-client , gói này sẽ cho phép bạn truy cập Database PostgreSQL được quản lý từ server MySQL của bạn:

  • sudo apt install pgloader postgresql-client

Sau đó, bạn có thể chạy lệnh pgloader để di chuyển database . Để thực hiện việc này, bạn cần chuỗi kết nối cho Dịch vụdatabase.

Đối với Dịch vụdatabase DigitalOcean, bạn có thể sao chép chuỗi kết nối từ Control panel cloud . Đầu tiên, nhấp vào Database trong menu thanh bên bên trái và chọn database mà bạn muốn di chuyển dữ liệu. Sau đó, cuộn xuống phần Chi tiết kết nối . Nhấp vào trình đơn thả xuống và chọn Chuỗi kết nối . Sau đó, nhấp vào nút Sao chép để sao chép chuỗi vào clipboard của bạn và paste vào lệnh di chuyển sau, thay thế chuỗi kết nối PostgreSQL mẫu được hiển thị ở đây. Điều này sẽ di chuyển database MySQL của bạn vào defaultdb database PostgreSQL như doadmin PostgreSQL role :

  • pgloader mysql://root:password@localhost/source_db postgres://doadmin:password@db_host/defaultdb?sslmode=require

Sau đó, bạn có thể sử dụng cùng một chuỗi kết nối làm đối số cho psql để kết nối với database PostgreSQL được quản lý và xác nhận quá trình di chuyển đã thành công:

  • psql postgres://doadmin:password@db_host/defaultdb?sslmode=require

Sau đó, chạy truy vấn sau để kiểm tra xem pgLoader đã di chuyển dữ liệu một cách chính xác hay chưa:

  • SELECT * FROM source_db.sample_table;
Output
 employee_id | first_name |  last_name  | start_date |   salary   
-------------+------------+-------------+------------+------------
           1 | Elizabeth  | Cotten      | 2007-11-11 | $105433.18
           2 | Yanka      | Dyagileva   | 2017-10-30 | $107540.67
           3 | Lee        | Dorsey      | 2013-06-04 | $118024.04
           4 | Kasey      | Chambers    | 2010-08-18 | $116456.98
           5 | Bram       | Tchaikovsky | 2018-09-16 | $61989.50
(5 rows)

Điều này xác nhận pgLoader đã di chuyển thành công database MySQL của bạn sang version PostgreSQL được quản lý của bạn.

Kết luận

pgLoader là một công cụ linh hoạt có thể thực hiện di chuyển database trong một lệnh duy nhất. Với một vài chỉnh sửa cấu hình, nó có thể di chuyển toàn bộ database từ máy vật lý này sang máy vật lý khác bằng cách sử dụng kết nối SSL / TLS an toàn. Ta hy vọng rằng theo hướng dẫn này, bạn sẽ hiểu rõ hơn về các khả năng của pgLoader và các trường hợp sử dụng tiềm năng.

Sau khi di chuyển dữ liệu sang PostgreSQL, bạn có thể thấy các hướng dẫn sau đây đáng quan tâm:

Tags:

Các tin liên quan

Cách cấu hình SSL / TLS cho MySQL trên Ubuntu 18.04
2019-05-17
Cách thiết lập WordPress với MySQL trên Kubernetes bằng Helm
2019-05-07
Cách cho phép truy cập từ xa vào MySQL
2019-03-07
Cách sửa chữa bảng bị hỏng trong MySQL
2019-03-07
Cách khắc phục sự cố lỗi socket trong MySQL
2019-03-07
Cách giải quyết sự cố trong MySQL
2019-03-07
Cách khắc phục sự cố truy vấn MySQL
2019-03-07
Giới thiệu về Truy vấn trong MySQL
2018-10-17
Cách cài đặt MySQL mới nhất trên Debian 9
2018-09-05
Cách đặt lại mật khẩu gốc MySQL hoặc MariaDB của bạn trên Ubuntu 18.04
2018-09-04