Importing Transactional Data into MariaDB SkySQL

This procedure applies to the following use cases:

How to Import Data

  1. Determine the connection information for your existing MariaDB Server:

    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. Use MariaDB Dump with the connection information to backup your databases on the existing MariaDB Server:

    With MariaDB Dump 10.4 and later, use the mariadb-dump command:

    $ mariadb-dump --host server.example.com --port 3307 \
          --user db_user --password \
          --ssl-verify-server-cert \
          --ssl-ca ~/trust_chain.pem \
          --all-databases \
          --ignore-database=mysql \
          --single-transaction \
          --events \
          --routines \
          --default-character-set=utf8mb4 \
          > mariadb_dump.sql
    

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

    $ mysqldump --host server.example.com --port 3307 \
          --user db_user --password \
          --ssl-verify-server-cert \
          --ssl-ca ~/trust_chain.pem \
          --all-databases \
          --ignore-database=mysql \
          --single-transaction \
          --events \
          --routines \
          --default-character-set=utf8mb4 \
          > mariadb_dump.sql
    

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

    The mysql database is excluded from the backup with the --ignore-database option, because SkySQL user accounts do not have sufficient privileges to restore that database.

  3. 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

  4. If your backup contains any CREATE VIEW, CREATE FUNCTION, CREATE PROCEDURE, or CREATE EVENT statements, edit the DEFINER clause in the dump file to refer to the database user account that will be performing the restore in the next step.

    On Linux, you can use grep to search your backup file for statements that have DEFINER clauses:

    $ grep -C 2 "DEFINER=" mariadb_dump.sql
    

    On Windows, you can use findstr to search your backup file for statements that have DEFINER clauses:

    $ findstr "DEFINER=" mariadb_dump.sql
    

    For example, if your dump file contains a view definition like this:

    /*!50001 CREATE ALGORITHM=UNDEFINED */
    /*!50013 DEFINER=`myappuser`@`%` SQL SECURITY DEFINER */
    /*!50001 VIEW `tab_view` AS select `tab`.`id` AS `id`,`tab`.`str` AS `str` from `tab` */;
    

    And if you are importing the dump file as db_user@%, then you would change the DEFINER clause in the dump file to the following:

    /*!50001 CREATE ALGORITHM=UNDEFINED */
    /*!50013 DEFINER=`db_user`@`%` SQL SECURITY DEFINER */
    /*!50001 VIEW `tab_view` AS select `tab`.`id` AS `id`,`tab`.`str` AS `str` from `tab` */;
    
  5. Use MariaDB Client with the connection information to import your databases 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=utf8mb4 \
          < mariadb_dump.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=utf8mb4 \
          < mariadb_dump.sql
    

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

  6. 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";