MySQL – How to Copy Table from One Server to Another

dumpexportMySQLpostgresql

Is there a way to copy or export a large table from MySQL server to postgres without affecting servers performance.

I mean the table is very large and using mysqldump or any select query has bad consequences.

Overall my question is: if there is a way to get the copy of the production database or table without performance problems on production.

Best Answer

If you can ignore changes that might happen while the copy is taking place...

Use the PRIMARY KEY to walk through the source table, fetch chunks of 100-1000 rows, copy them. More on chunking, but adapt for just fetching, not deleting or updating.

Related Solutions

Sql-server – How to export a single huge table from SQL Server

I'd of thought BCP, with a format file rather than defaults, would be your best bet.

Creating a format file

Mysql – How to migrate large blob table from thesql to postgresql

I think that simplest way is to use that --hex-blob switch on mysqldump and restore by psql, with decode(string text, type text). However it's not that simple, because you need to change a little produced dump (sed, awk), adding that decode function. For example:

mysqldump -u root -p --skip-quote-names --hex-blob --skip-triggers \
    --compact --no-create-info mysql samples > prepg.dump

sed "s/0x\([0-9,A-F]*\))/decode('\1','hex'))/g" prepg.dump > pg.dump

psql session:

CREATE TABLE samples
(
    file_id integer PRIMARY KEY,
    file bytea
);

\i 'path/to/pg.dump'

Best Answer

Related Solutions

Sql-server – How to export a single huge table from SQL Server

Mysql – How to migrate large blob table from thesql to postgresql

Related Question