以Postgresql为主,再聊聊资料库 使用MySQL_FDW 抓取MySQL 範例资料库Sakila 并用PG13

MySQL Sakila Sample Databasehttps://dev.mysql.com/doc/sakila/en/sakila-installation.htmlhttps://dev.mysql.com/doc/index-other.html---------download , and tar -zxvf sakila-db.tar.gz (root) [(none)]> source sakila-schema.sql;(root) [sakila]> source sakila-data.sql;(root) [sakila]> SHOW FULL TABLES;+----------------------------+------------+| Tables_in_sakila           | Table_type |+----------------------------+------------+| actor                      | BASE TABLE || actor_info                 | VIEW       || address                    | BASE TABLE || category                   | BASE TABLE || city                       | BASE TABLE || country                    | BASE TABLE || customer                   | BASE TABLE || customer_list              | VIEW       || film                       | BASE TABLE || film_actor                 | BASE TABLE || film_category              | BASE TABLE || film_list                  | VIEW       || film_text                  | BASE TABLE || inventory                  | BASE TABLE || language                   | BASE TABLE || nicer_but_slower_film_list | VIEW       || payment                    | BASE TABLE || rental                     | BASE TABLE || sales_by_film_category     | VIEW       || sales_by_store             | VIEW       || staff                      | BASE TABLE || staff_list                 | VIEW       || store                      | BASE TABLE |+----------------------------+------------+23 rows in set (0.01 sec)(root) [sakila]> SELECT COUNT(*) FROM film;+----------+| COUNT(*) |+----------+|     1000 |+----------+(root) [sakila]> SELECT COUNT(*) FROM film_text;+----------+| COUNT(*) |+----------+|     1000 |+----------+------------------------grant all privileges on sakila.* to 'miku'@'%';flush privileges;------|=> mysql -u miku -p -D sakila(miku) [sakila]> show FULL TABLES;+----------------------------+------------+| Tables_in_sakila           | Table_type |+----------------------------+------------+| actor                      | BASE TABLE || actor_info                 | VIEW       || address                    | BASE TABLE || category                   | BASE TABLE || city                       | BASE TABLE || country                    | BASE TABLE || customer                   | BASE TABLE || customer_list              | VIEW       || film                       | BASE TABLE || film_actor                 | BASE TABLE || film_category              | BASE TABLE || film_list                  | VIEW       || film_text                  | BASE TABLE || inventory                  | BASE TABLE || language                   | BASE TABLE || nicer_but_slower_film_list | VIEW       || payment                    | BASE TABLE || rental                     | BASE TABLE || sales_by_film_category     | VIEW       || sales_by_store             | VIEW       || staff                      | BASE TABLE || staff_list                 | VIEW       || store                      | BASE TABLE |+----------------------------+------------+23 rows in set (0.00 sec)(miku) [sakila]> -------------------https://github.com/EnterpriseDB/mysql_fdw---------- create role and database for MySQL Sakila Sample Database-- create tablespace cd /usr/local/var mkdir sakila-- tablespace path : /usr/local/var/sakila -- login with superuser privilege create tablespace sakila location '/usr/local/var/sakila';create role sakila with login superuser password 'sakila';commit;select rolsuper     , rolpassword  from pg_authid where rolname = 'sakila'; rolsuper |             rolpassword             ----------+------------------------------------- t        | md50317187bad27817ed0bcc145db708d33create database sakila owner sakila tablespace sakila encoding UTF8;\l+ sakila                                                List of databases  Name  | Owner  | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace | Description --------+--------+----------+-------------+-------------+-------------------+---------+------------+------------- sakila | sakila | UTF8     | zh_TW.UTF-8 | zh_TW.UTF-8 |                   | 7849 kB | sakila     | ------------- install mysql_fdwunzip mysql_fdw-master.zip cd mysql_fdw-masterexport PATH=/usr/local/mysql/bin/:$PATHmake USE_PGXS=1make USE_PGXS=1 install----------- login as sakilaselect current_schema; current_schema ---------------- publicshow search_path;   search_path   ----------------- "$user", publiccreate schema sakila;create schema pakila;commit;alter database sakila set search_path="$user", public, pakila;alter role sakila set search_path="$user", public, pakila;commit;-- quit and login againshow search_path;       search_path       ------------------------- "$user", public, pakilaselect current_schema; current_schema ---------------- sakila-- create mysql_fdw extensioncreate extension mysql_fdw with schema public;commit;sakila[sakila]# \dx                            List of installed extensions   Name    | Version |   Schema   |                   Description                    -----------+---------+------------+-------------------------------------------------- mysql_fdw | 1.1     | public     | Foreign data wrapper for querying a MySQL server plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language(2 rows)---------------# in PostgreSQL# 先建立一个 连线的 server , 取名为 mysql_servercreate server mysql_serverforeign data wrapper mysql_fdwoptions (host '127.0.0.1', port '3306');commit;# 建立一个 mapping user, for 现在登入的 pg user# 密码以及 mysql 的 user, 要记得置换.create user mapping for sakilaserver mysql_serveroptions (username 'miku', password 'YourPasswd');commit;------------- import foreign schemaimport foreign schema sakilafrom server mysql_serverinto pakila;-- 因为 MySQL Sakila 有自定义资料型态或者新的 空间资料型态, 我们需要将这些table先排除掉.-- 后面再手动补上. geometry  是 SRID , 先用 bit(32) 以利传输, 但是实际上使用时资料会都是 0.-- view 我们也排除.https://dev.mysql.com/doc/refman/8.0/en/spatial-function-argument-handling.htmlimport foreign schema sakilaEXCEPT (address, film, film_list, nicer_but_slower_film_list)from server mysql_serverinto pakila;commit;\d                    List of relations Schema |          Name          |     Type      | Owner  --------+------------------------+---------------+-------- pakila | actor                  | foreign table | sakila pakila | actor_info             | foreign table | sakila pakila | category               | foreign table | sakila pakila | city                   | foreign table | sakila pakila | country                | foreign table | sakila pakila | customer               | foreign table | sakila pakila | customer_list          | foreign table | sakila pakila | film_actor             | foreign table | sakila pakila | film_category          | foreign table | sakila pakila | film_text              | foreign table | sakila pakila | inventory              | foreign table | sakila pakila | language               | foreign table | sakila pakila | payment                | foreign table | sakila pakila | rental                 | foreign table | sakila pakila | sales_by_film_category | foreign table | sakila pakila | sales_by_store         | foreign table | sakila pakila | staff                  | foreign table | sakila pakila | staff_list             | foreign table | sakila pakila | store                  | foreign table | sakila(19 rows)---CREATE TYPE rating_t AS enum('G','PG','PG-13','R','NC-17');commit;CREATE FOREIGN TABLE film (  film_id smallint NOT NULL,  title varchar(128) NOT NULL,  description text,  release_year smallint,  language_id smallint NOT NULL,  original_language_id smallint,  rental_duration smallint NOT NULL,  rental_rate decimal NOT NULL,  length smallint,  replacement_cost decimal NOT NULL,  rating rating_t,  special_features text,  last_update timestamp NOT NULL) SERVER mysql_server OPTIONS (dbname 'sakila', table_name 'film');commit;select * from film limit 1;-[ RECORD 1 ]--------+-------------------------------------------------------------------------------------------------film_id              | 1title                | ACADEMY DINOSAURdescription          | A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockiesrelease_year         | 2006language_id          | 1original_language_id | NULLrental_duration      | 6rental_rate          | 0.99length               | 86replacement_cost     | 20.99rating               | PGspecial_features     | Deleted Scenes,Behind the Sceneslast_update          | 2006-02-15 05:03:42CREATE FOREIGN TABLE address (  address_id smallint NOT NULL,  address varchar(50) NOT NULL,  address2 varchar(50),  district varchar(20) NOT NULL,  city_id smallint NOT NULL,  postal_code varchar(10),  phone varchar(20) NOT NULL,  location bit(32) NOT NULL,  last_update timestamp NOT NULL) SERVER mysql_server OPTIONS (dbname 'sakila', table_name 'address');commit;select * from address limit 1;-[ RECORD 1 ]--------------------address_id  | 1address     | 47 MySakila Driveaddress2    | NULLdistrict    | Albertacity_id     | 300postal_code | phone       | location    | 0last_update | 2014-09-25 22:30:27\d                    List of relations Schema |          Name          |     Type      | Owner  --------+------------------------+---------------+-------- pakila | actor                  | foreign table | sakila pakila | actor_info             | foreign table | sakila pakila | category               | foreign table | sakila pakila | city                   | foreign table | sakila pakila | country                | foreign table | sakila pakila | customer               | foreign table | sakila pakila | customer_list          | foreign table | sakila pakila | film_actor             | foreign table | sakila pakila | film_category          | foreign table | sakila pakila | film_text              | foreign table | sakila pakila | inventory              | foreign table | sakila pakila | language               | foreign table | sakila pakila | payment                | foreign table | sakila pakila | rental                 | foreign table | sakila pakila | sales_by_film_category | foreign table | sakila pakila | sales_by_store         | foreign table | sakila pakila | staff                  | foreign table | sakila pakila | staff_list             | foreign table | sakila pakila | store                  | foreign table | sakila sakila | address                | foreign table | sakila sakila | film                   | foreign table | sakila(21 rows)------------- pg_dump --include-foreign-datapg_dump -U sakila -W -d sakila --include-foreign-data=mysql_server -f mysakila.sql-- 会建立 FOREIGN SERVER ,以及各 table 的 schema, data.-- 以下为部分------------------- Name: country; Type: FOREIGN TABLE; Schema: pakila; Owner: sakila--CREATE FOREIGN TABLE pakila.country (    country_id smallint NOT NULL,    country character varying(50) NOT NULL,    last_update timestamp without time zone NOT NULL)SERVER mysql_serverOPTIONS (    dbname 'sakila',    table_name 'country');ALTER FOREIGN TABLE pakila.country OWNER TO sakila;----------------- 资料部分---- Data for Name: actor; Type: TABLE DATA; Schema: pakila; Owner: sakila--COPY pakila.actor (actor_id, first_name, last_name, last_update) FROM stdin;1       PENELOPE        GUINESS 2006-02-15 04:34:332       NICK    WAHLBERG        2006-02-15 04:34:333       ED      CHASE   2006-02-15 04:34:334       JENNIFER        DAVIS   2006-02-15 04:34:335       JOHNNY  LOLLOBRIGIDA    2006-02-15 04:34:336       BETTE   NICHOLSON       2006-02-15 04:34:337       GRACE   MOSTEL  2006-02-15 04:34:338       MATTHEW JOHANSSON       2006-02-15 04:34:339       JOE     SWANK   2006-02-15 04:34:3310      CHRISTIAN       GABLE   2006-02-15 04:34:3311      ZERO    CAGE    2006-02-15 04:34:3312      KARL    BERRY   2006-02-15 04:34:3313      UMA     WOOD    2006-02-15 04:34:3314      VIVIEN  BERGEN  2006-02-15 04:34:33---------------PostgreSQL 13 现在的功能更完整了!