Search code examples
mysqlmyisam

mysqldump on a merge table

I can't seem to use mysqldump to dump a table that has been created as a MyISAM merge table.

When I attempt to, it simply dumps a file that has the create table syntax in it (correct) but not any of the data within the underlying tables.

Is this expected or a known bug? How can I replicate the behavior of mysqldump on a MRG_MyISAM table?

Solution

  • That should be expected. The MERGE table is just a mapping of MyISAM tables.

    You must dump the underlying tables.

    If you want some kind of emulation to mysqldump a MERGE table (for a Table called mydb.mymergetable whose storage engine is Mrg_MyISAM)

    USE mydb
CREATE TABLE myhardtable LIKE mymergetable;
ALTER TABLE myhardtable ENGINE=MyISAM;
INSERT INTO myhardtable SELECT * FROM mymergetable;

    Now, just do this:

    mysqldump -u... -p... mydb myhardtable > /root/MyMergeData.sql

    You could also do this

    SELECT * INTO OUTFILE '/root/MyMergeData.sql'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
FROM mydb.myhardtable;