Migration from MySQL and PostgreSQL: Difference between revisions

From MemCP
Jump to navigation Jump to search
No edit summary
(Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference)
Line 1: Line 1:
[[File:Ports.svg|thumb|none|640x640px]]
<!-- Copyright (C) 2026 Carl-Philip Haensch -->
===Connection to MemCP via MySQL Connector===
MemCP can be connected with any compatible MySQL connector:
$db = new \PDO<code>"mysql:host=localhost;port=3307;dbname=system", 'root', 'admin');</code>
echo $db->query("SELECT 'it works'")->fetchColumn();
// outouts: it works


=== Connection to MemCP from PostgreSQL ===
<!-- SPDX-License-Identifier: GPL-3.0-or-later -->
For postgresql, just switch over to a mysql connector (odbc and PDO both support multiple databases) and send<code>SET syntax = 'postgresql'</code>at the very beginning of your session


Or, switch over to the  [[SQL over REST|REST connector]].
<span id="migration-from-mysql-and-postgresql"></span>
= Migration from MySQL and PostgreSQL =


===Import data from MySQL and PostgreSQL===
Migration is a compatibility and operations project, not only a data copy. MemCP can import through live MySQL/PostgreSQL connections or supported dump formats, while applications connect through the MySQL protocol or the HTTP SQL endpoints. It does not expose a PostgreSQL wire-protocol server and does not claim complete syntax, metadata, type, or administration compatibility with either source system.
To import data from MySQL into MemCP, you have to perform the following command in[[MemCP Console|<code>memcp</code>console]]:
(load_sql "database" (stream "dump.sql")) /* for PostgreSQL mode, use load_psql instead */
When the sqldump is zipped, use:
(load_sql "database" (gzip (stream "dump.sql"))) /* for PostgreSQL mode, use load_psql instead */
or:
(load_sql "database" (xz (stream "dump.sql"))) /* for PostgreSQL mode, use load_psql instead */
The dumps can be created with <code>mysqldump</code>or <code>pg_dump</code>.


=== Further Reading ===
Keep the source database authoritative until schema translation, row counts, constraints, representative queries, writes, timezone behavior, and restart recovery have been validated. Measure import duration and application latency with successful results, retain a rollback path, and separately design ongoing change capture when the source continues receiving writes during a staged migration.


* [[Deployment]]
[[File:Ports.svg|thumb|none|640px|MemCP client and import connection paths]]
 
<span id="live-import"></span>
== Live import ==
 
<syntaxhighlight lang="scheme">; MySQL: host/port nil use 127.0.0.1:3306
(mysql_import nil nil "import_user" "secret" "source_db" "target_db")
 
; PostgreSQL: database, source schema, target database
(psql_import nil nil "postgres" "secret" "source_db" "public" "target_db")</syntaxhighlight>
Optional trailing arguments select or rename individual tables. Use a least-privilege read-only source account and protect credentials from shell and process listings.
 
<span id="dumps"></span>
== Dumps ==
 
<code>load_sql</code> reads MySQL SQL; <code>load_psql</code> reads PostgreSQL SQL and supported pg_dump/archive inputs. A compressed input must be decompressed (<code>zcat</code>/<code>xzcat</code>), not passed through the <code>gzip</code>/<code>xz</code> compression functions.
 
<syntaxhighlight lang="bash">gzip -dk dump.sql.gz
# Then in the MemCP console:
# (load_sql "target_db" (stream "dump.sql"))</syntaxhighlight>
 
For large or live migrations, prefer the importer functions above rather than assuming every vendor-specific dump statement is accepted.
 
<span id="application-connection"></span>
== Application connection ==
 
<syntaxhighlight lang="php">$db = new PDO(
    'mysql:host=127.0.0.1;port=3307;dbname=target_db',
    'root',
    'strong-password'
);
echo $db->query("SELECT 'it works'")->fetchColumn();</syntaxhighlight>
PostgreSQL SQL syntax is available over <code>/psql/&lt;database&gt;</code>; MemCP does not expose a PostgreSQL wire-protocol port.
 
<span id="validation-checklist"></span>
== Validation checklist ==
 
* compare schemas, types, defaults, indexes, constraints, triggers, and views;
* compare counts and checksums per table;
* replay representative reads and writes against both systems;
* verify AUTO_INCREMENT and timezone behavior;
* restart MemCP and repeat checks;
* measure import duration, query latency, errors, and CDC lag if live replication is used;
* retain a tested rollback path until cutover gates pass.

Revision as of 11:59, 28 August 2026


Migration from MySQL and PostgreSQL

Migration is a compatibility and operations project, not only a data copy. MemCP can import through live MySQL/PostgreSQL connections or supported dump formats, while applications connect through the MySQL protocol or the HTTP SQL endpoints. It does not expose a PostgreSQL wire-protocol server and does not claim complete syntax, metadata, type, or administration compatibility with either source system.

Keep the source database authoritative until schema translation, row counts, constraints, representative queries, writes, timezone behavior, and restart recovery have been validated. Measure import duration and application latency with successful results, retain a rollback path, and separately design ongoing change capture when the source continues receiving writes during a staged migration.

MemCP client and import connection paths

Live import

<syntaxhighlight lang="scheme">; MySQL: host/port nil use 127.0.0.1:3306 (mysql_import nil nil "import_user" "secret" "source_db" "target_db")

PostgreSQL
database, source schema, target database

(psql_import nil nil "postgres" "secret" "source_db" "public" "target_db")</syntaxhighlight> Optional trailing arguments select or rename individual tables. Use a least-privilege read-only source account and protect credentials from shell and process listings.

Dumps

load_sql reads MySQL SQL; load_psql reads PostgreSQL SQL and supported pg_dump/archive inputs. A compressed input must be decompressed (zcat/xzcat), not passed through the gzip/xz compression functions.

<syntaxhighlight lang="bash">gzip -dk dump.sql.gz

  1. Then in the MemCP console:
  2. (load_sql "target_db" (stream "dump.sql"))</syntaxhighlight>

For large or live migrations, prefer the importer functions above rather than assuming every vendor-specific dump statement is accepted.

Application connection

<syntaxhighlight lang="php">$db = new PDO( 

   'mysql:host=127.0.0.1;port=3307;dbname=target_db',
   'root',
   'strong-password'

); echo $db->query("SELECT 'it works'")->fetchColumn();</syntaxhighlight> PostgreSQL SQL syntax is available over /psql/<database>; MemCP does not expose a PostgreSQL wire-protocol port.

Validation checklist

  • compare schemas, types, defaults, indexes, constraints, triggers, and views;
  • compare counts and checksums per table;
  • replay representative reads and writes against both systems;
  • verify AUTO_INCREMENT and timezone behavior;
  • restart MemCP and repeat checks;
  • measure import duration, query latency, errors, and CDC lag if live replication is used;
  • retain a tested rollback path until cutover gates pass.
Retrieved from "https://www.memcp.org/index.php?title=Migration_from_MySQL_and_PostgreSQL&oldid=345"