• MySQL知识库 :: cluster
  • Moving an in-memory table to disk-based table.

  • Discussion

    Suppose that you want to recreate an in-memory NDB table as a disk-based NDB table. There are a few basic steps to be able to do this. First, it is important to make sure that you make backups of you databases before doing the following and go into single user mode—these are not online operations. Once your backups have been made and verified, create table1_disk from the table1 table using the appropriated TABLESPACE table option. You would then issue the following commands:

    INSERT INTO table1_disk 
    SELECT * FROM table1;
    
    ALTER TABLE table1
    RENAME table1_memory;
    
    ALTER TABLE table1_disk
    RENAME table1;

    Check data in the table1 table and if everything is fine, you could then drop table1_memory. However, one problem is that older versions of MySQL Cluster, ALTER TABLE does not have an option in which you can move an in-memory NDB table to a disk-based table. As of recent versions, ALTER TABLE table1 TABLESPACE tablespace1 STORAGE DISK ENGINE=NDB is available. However, if upgrading is not an option, the explanation below provides a work-around.

    Work Around

    To work around the options missing from ALTER TABLE, you'll need to first create a new table from the DDL of the table that you want to move with the proper table option. Instead, you'll have to create new tables with the desired settings. Below is an example of how this might be done:

    CREATE TABLE table1 
    (col0 INT(11) NOT NULL AUTO_INCREMENT,
    col1 VARCHAR(20) DEFAULT NULL,
    PRIMARY KEY (col0) ) 
    ENGINE=ndbcluster DEFAULT CHARSET=latin1;

    Now add the correct TABLESPACE table options like this:

    CREATE TABLE table1_disk 
    (col0 INT(11) NOT NULL AUTO_INCREMENT,
    col1 VARCHAR(20) DEFAULT NULL,
    PRIMARY KEY (col0)) 
    ENGINE=ndbcluster DEFAULT CHARSET=latin1 
    TABLESPACE ts_1 STORAGE DISK;

    Once you have copied all of the data to the disk-based table, it's good to verify that you have everything. Then you only need to rename the tables using the ALTER TABLE ... RENAME statement. Again, it's important that you make backups before you perform the steps above and that you make sure you actually can restore them. Also, since you are making a copy of data, it's best that you do this in single-user mode. The rename operation is done online. Keep in mind that between these operations, some transactions might fail.