Migrating Tables from InnoDB to MariaDB Xpand

MariaDB Enterprise Server allows you to move data to use different storage engines as your application needs develop. When using the Xpand Storage Engine topology, you can migrate your table from the InnoDB storage engine to the Xpand storage engine, so you can take advantage of Xpand's built-in support for high availability and fault tolerance.

Overview

When using the Xpand Storage Engine topology, InnoDB tables can easily be migrated to Xpand:

When using the Xpand Performance topology, InnoDB is not available. In the Xpand Performance topology, ENGINE=InnoDB is ignored, and all tables use Xpand.

Examples

Migrate an InnoDB Table to Xpand

When using the Xpand Storage Engine topology, use the ALTER TABLE statement with the ENGINE=Xpand table option to migrate an InnoDB table to Xpand:

ALTER TABLE ha_sales.invoices ENGINE=Xpand;

Migration Considerations

The Xpand storage engine does not support all of the same features as InnoDB. Before migrating a table from InnoDB to Xpand consider the following?:

  • Does the table use any data types that are not supported by Xpand?

  • Does the table use any index types that are not supported by Xpand?

  • Does the table use any other features that are not supported by Xpand?

Converting InnoDB Tables to Xpand

When using the Xpand Storage Engine topology, use the ALTER TABLE statement with the ENGINE=Xpand table option to migrate an InnoDB table to Xpand:

  1. Connect to an ES node using MariaDB Client:

    $ mariadb --user=root
    
  2. Use the CREATE DATABASE statement to create the database:

    CREATE DATABASE ha_sales;
    

    Note

    When using the Xpand Storage Engine topology, MariaDB Enterprise Server does not automatically synchronize databases and other objects on all ES nodes. ES nodes require a replication solution, such as MariaDB Replication to synchronize databases and other objects. Without replication, databases need to be created separately on each ES node.

  3. Use the CREATE TABLE statement to create the InnoDB table:

    CREATE TABLE hq_sales.invoices (
       invoice_id BIGINT PRIMARY KEY AUTO_INCREMENT,
       branch_id INT NOT NULL,
       customer_id INT,
       invoice_date DATETIME(6),
       invoice_total DECIMAL(13, 2),
       payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
    ) ENGINE=InnoDB;
    
  4. Confirm that the table uses InnoDB by querying the information_schema.TABLES table:

    SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA='hq_sales'
      AND TABLE_NAME='invoices';
    
    +--------------+------------+--------+
    | TABLE_SCHEMA | TABLE_NAME | ENGINE |
    +--------------+------------+--------+
    | hq_sales     | invoices   | InnoDB |
    +--------------+------------+--------+
    
  5. On a single ES node, migrate the InnoDB table to Xpand using the ALTER TABLE statement with the ENGINE=Xpand table option:

    ALTER TABLE ha_sales.invoices ENGINE=Xpand;
    
  6. Confirm that the table uses Xpand by querying the information_schema.TABLES table:

    SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA='hq_sales'
      AND TABLE_NAME='invoices';
    
    +--------------+------------+--------+
    | TABLE_SCHEMA | TABLE_NAME | ENGINE |
    +--------------+------------+--------+
    | hq_sales     | invoices   | Xpand  |
    +--------------+------------+--------+
    

Queries made to this table now retrieve data from the Xpand storage engine.

Copying InnoDB Data to Xpand Tables

When using the Xpand Storage Engine topology, use the INSERT SELECT statement to copy the table data to Xpand without altering the existing InnoDB table.

To copy data from InnoDB to Xpand:

  1. Connect to an ES node using MariaDB Client:

    $ mariadb --user=root
    
  2. Use the CREATE DATABASE statement to create the database:

    CREATE DATABASE ha_sales;
    

    Note

    When using the Xpand Storage Engine topology, MariaDB Enterprise Server does not automatically synchronize databases and other objects on all ES nodes. ES nodes require a replication solution, such as MariaDB Replication to synchronize databases and other objects. Without replication, databases need to be created separately on each ES node.

  3. Use the CREATE TABLE statement to create the InnoDB table:

    CREATE TABLE hq_sales.contacts_innodb (
       id INT PRIMARY KEY AUTO_INCREMENT,
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       company VARCHAR(100),
       email VARCHAR(100)
    ) ENGINE=InnoDB;
    
  4. Add sample data to the table:

    INSERT INTO ha_sales.contacts_innodb (first_name, last_name, company, email)
    VALUES ("Charles", "Dickens", "Great Expectations, Ltd.", "c.dickens@example.com"),
       ("Leo", "Tolstoy", "Forged Coupons, Inc.", "leo.tolstoy@example.com"),
       ("Samuel", "Beckett", "Godot Waits", "s.beckett@example.com");
    
  5. Use the CREATE TABLE statement to create the Xpand table with the same schema that was used for the InnoDB table:

    CREATE TABLE hq_sales.contacts_xpand (
       id INT PRIMARY KEY AUTO_INCREMENT,
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       company VARCHAR(100),
       email VARCHAR(100)
    ) ENGINE=Xpand;
    
  6. Use an INSERT SELECT statement to add data from one table into the other:

    INSERT INTO hq_sales.contacts_innodb
       SELECT * FROM hq_sales.contacts_xpand;
    
  7. Check that the data was correctly copied over:

    SELECT * FROM hq_sales.contacts_xpand;
    
    +----+------------+-----------+--------------------------+-------------------------+
    | id | first_name | last_name | company                  | email                   |
    +----+------------+-----------+--------------------------+-------------------------+
    | 1  | Charles    | Dickens   | Great Expectations, Ltd. | c.dickes@example.com    |
    | 2  | Leo        | Tolstoy   | Forged Coupons, Inc.     | leo.tolstoy@example.com |
    | 3  | Samuel     | Beckett   | Godot Waits              | s.beckett@example.com   |
    +----+------------+-----------+--------------------------+-------------------------+
    

Once the data has been moved to the new table, you can update your application to query data from Xpand rather than InnoDB, taking advantage of Xpand's built-in fault tolerance and high availability.

Resources

MariaDB Knowledge Base

Reference

SQL Statements