MariaDB Xpand Schema Management

Schema management with MariaDB Xpand varies depending on which topology is deployed, Xpand Performance or Xpand Storage Engine topology

Xpand Performance Topology

Xpand Performance topology is composed of MariaDB MaxScale with three or more odd number of Xpand nodes. MariaDB MaxScale routes queries directly to Xpand nodes.

Xpand Performance Topology

For additional information, see "MariaDB Xpand Topologies".

In an Xpand Performance topology, schema management can be performed using the following procedure:

  1. Use the MariaDB Client to connect to the listener port on the MaxScale node:

    $ mariadb --host=192.0.2.10 \
       --port=3307 \
       --user=xpand_app_user \
       --password
    

    The listener port (Configure a Listener) should be specified by the --port option. When the listener uses TCP port 3306, you do not need to specify the port to the client.

    The user account (Create Application Database User) should be specified by the --user option.

    MaxScale's Read/Write Split Router (Configure Read/Write Split Router) will route the connection to one of the Xpand nodes (Configure Server Objects).

  2. Execute your DDL statement:

    CREATE DATABASE IF NOT EXISTS test;
    
    CREATE TABLE test.t1(
       id INT PRIMARY KEY AUTO_INCREMENT,
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       email VARCHAR(100)
    );
    

    MaxScale routes the CREATE TABLE statement through to an Xpand node. MariaDB Xpand generates the relevant database objects for the table. Xpand automatically slices data added to tables and generates replicas of table data to ensure high availability and fault tolerance.

Xpand Storage Engine Topology

Xpand Storage Engine topology is composed of MariaDB MaxScale, one or more MariaDB Enterprise Servers, and three or more odd number of Xpand nodes.

Xpand Storage Engine Topology

For additional information, see "MariaDB Xpand Topologies".

In an Xpand Storage Engine topology, schema management can be performed using the following procedure:

  1. Use the MariaDB Client to connect to the listener port for the Read/Write Split service on the MaxScale node:

    $ mariadb --host=192.0.2.10 \
       --port=3307 \
       --user=xpand_app_user \
       --password
    

    The listener port (Configure a Listener) should be specified by the --port option. When the listener uses TCP port 3306, you do not need to specify the port to the client.

    The user account (Create Application Database User) should be specified by the --user option.

    MaxScale's Read/Write Split Router (Configure Read/Write Split Router) will route the connection to one of the Xpand nodes (Configure Server Objects).

  2. Execute your DDL statement:

    CREATE DATABASE IF NOT EXISTS test;
    
    CREATE TABLE test.t1(
       id INT PRIMARY KEY AUTO_INCREMENT,
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       email VARCHAR(100)
    ) ENGINE=Xpand;
    

    MaxScale routes the CREATE TABLE statement to the primary ES node. Since ENGINE=Xpand is specified, the ES node will route the operation to a backend Xpand node. Xpand generates the relevant database objects for the table. Xpand automatically slices data added to this table by index and generates replicas of table data to ensure high availability and fault tolerance.

Note

ES nodes require a replication solution, such as MariaDB Replication to synchronize several types of database objects, such as:

  • Databases

  • Tables that use other storage engines

  • Views

  • User accounts and privileges

  • Stored procedures and functions

  • Events