Importing Analytical Data into MariaDB SkySQL

This procedure applies to the following use cases:

How to Import Data

To import data from an existing analytics database to MariaDB SkySQL:

  1. Determine the connection information for your existing analytics database.

    The specific connection information will depend on what specific source database is used. If it is another MariaDB Server, then the information will be:

    Parameter

    Where to find it

    --host

    IP address or Fully Qualified Domain Name used by your existing MariaDB Server.

    --port

    Port used by your existing MariaDB Server.

    --ssl-ca

    "Certificate Authenticity Chain" used by your existing MariaDB Server, if applicable.

    --user

    User name of a user account on your existing MariaDB Server that has sufficient privileges to backup all databases.

    Password

    Password of the user account on your existing MariaDB Server.

  2. Determine which tables you want to migrate from your existing analytics database to your MariaDB SkySQL database service.

  3. For each table that you want to migrate, dump the table's schema.

    The specific command will depend on what specific source database is used. If it is another MariaDB Server, then the SHOW CREATE TABLE statement can be used:

    SHOW CREATE TABLE accounts.contacts;
    
  4. For each table that you want to migrate, dump the table's rows into a TSV or CSV file.

    The specific command will depend on what specific source database is used. If it is another MariaDB Server, then MariaDB Client can be used to create a TSV file:

    With MariaDB Client 10.4 and later, use the mariadb command:

    $ mariadb --host server.example.com --port 3307 \
          --user db_user --password \
          --ssl-verify-server-cert \
          --ssl-ca ~/trust_chain.pem \
          --default-character-set=utf8 \
          --batch \
          --skip-column-names \
          --execute='SELECT * FROM accounts.contacts;' \
          > contacts.tsv
    

    For MariaDB Client 10.3 and older, use the legacy command:

    $ mysql --host server.example.com --port 3307 \
          --user db_user --password \
          --ssl-verify-server-cert \
          --ssl-ca ~/trust_chain.pem \
          --default-character-set=utf8 \
          --batch \
          --skip-column-names \
          --execute='SELECT * FROM accounts.contacts;' \
          > contacts.tsv
    

    After the command is executed, it will prompt you for a password.

  5. Determine the connection information for your MariaDB SkySQL database service:

    Parameter

    Where to find it

    --host

    Fully Qualified Domain Name in the Service Details view

    --port

    Read-Write Port or Read-Only Port in the Service Details view

    skysql_chain.pem file

    "Certificate Authenticity Chain" download link in the Service Credentials view

    --user

    Default username in the Service Credentials view, or the username you created

    Password

    Default password in the Service Credentials view, the password you set on the default user, or the password for the user you created

  6. Connect to the database service and create any required database user accounts:

    CREATE USER "db_user"@"192.0.2.1" IDENTIFIED BY "passwd";
    
  7. For each table that you want to migrate, build your new ColumnStore-compatible schema or InnoDB-compatible schema (only for database services using MariaDB Platform for Smart Transactions), and save them all to a file called skysql_schema.sql:

    CREATE DATABASE accounts;
    
    CREATE TABLE accounts.contacts (
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       email_address VARCHAR(255),
       street VARCHAR(255),
       city VARCHAR(255),
       state VARCHAR(2),
       postal VARCHAR(10)
    ) ENGINE = ColumnStore;
    
    CREATE TABLE accounts.users (
       id int AUTO_INCREMENT PRIMARY KEY,
       username VARCHAR(50),
       password_hash VARCHAR(128),
       email_address VARCHAR(255)
    ) ENGINE=InnoDB;
    
  8. Use MariaDB Client with the connection information to import your ColumnStore-compatible schema into your MariaDB SkySQL database service:

    With MariaDB Client 10.4 and later, use the mariadb command:

    $ mariadb --host example.skysql.net --port 5001 \
          --user db_user --password \
          --ssl-verify-server-cert \
          --ssl-ca ~/Downloads/skysql_chain.pem \
          --default-character-set=utf8 \
          < skysql_schema.sql
    

    For MariaDB Client 10.3 and older, use the legacy command:

    $ mysql --host example.skysql.net --port 5001 \
          --user db_user --password \
          --ssl-verify-server-cert \
          --ssl-ca ~/Downloads/skysql_chain.pem \
          --default-character-set=utf8 \
          < skysql_schema.sql
    

    After the command is executed, it will prompt you for a password.

  9. Use MariaDB Client with the connection information to open a persistent connection to your MariaDB SkySQL database service, and be sure to include the --local-infile option, which is needed by the next step:

    With MariaDB Client 10.4 and later, use the mariadb command:

    $ mariadb --host example.skysql.net --port 5001 \
          --user db_user --password \
          --ssl-verify-server-cert \
          --ssl-ca ~/Downloads/skysql_chain.pem \
          --default-character-set=utf8 \
          --local-infile
    

    For MariaDB Client 10.3 and older, use the legacy command:

    $ mysql --host example.skysql.net --port 5001 \
          --user db_user --password \
          --ssl-verify-server-cert \
          --ssl-ca ~/Downloads/skysql_chain.pem \
          --default-character-set=utf8 \
          --local-infile
    

    After the command is executed, it will prompt you for a password.

  10. For each table that you want to migrate, use your MariaDB Client connection to execute the LOAD DATA LOCAL INFILE statement to import the data from the TSV or CSV file into your MariaDB SkySQL database service.

    For a TSV file:

    LOAD DATA LOCAL INFILE 'contacts.tsv'
    INTO TABLE accounts.contacts;
    

    For a CSV file:

    LOAD DATA LOCAL INFILE 'contacts.csv'
    INTO TABLE accounts.contacts
    FIELDS TERMINATED BY ',';
    
  11. Use MariaDB Client with the connection information to connect to your MariaDB SkySQL database service and create any database user accounts required by your applications:

    CREATE USER "db_user"@"192.0.2.1" IDENTIFIED BY "passwd";