MariaDB Dump

MariaDB Dump is a command-line tool for creating logical backups of MariaDB Servers, including MariaDB database services on MariaDB SkySQL.

For additional information, see command-line options for mariadb and mysql.

Compatibility

MariaDB Dump can be used with:

  • MariaDB Platform for Transactions

  • MariaDB Platform for Smart Transactions (InnoDB)

Installing MariaDB Dump

Installation of MariaDB Dump varies by operating system.

Installing MariaDB Dump via YUM (RHEL/CentOS)

  1. Configure YUM package repositories:

    $ sudo yum install wget
    
    $ wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
    
    $ echo "32e01fbe65b4cecc074e19f04c719d1a600e314236c3bb40d91e555b7a2abbfc mariadb_repo_setup" \
        | sha256sum -c -
    
    $ chmod +x mariadb_repo_setup
    
    $ sudo ./mariadb_repo_setup \
       --mariadb-server-version="mariadb-10.5"
    
  2. Install MariaDB Dump and package dependencies:

    $ sudo yum install MariaDB-client
    

Installing MariaDB Dump via APT (Debian/Ubuntu)

  1. Configure APT package repositories:

    $ sudo apt install wget
    
    $ wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
    
    $ echo "32e01fbe65b4cecc074e19f04c719d1a600e314236c3bb40d91e555b7a2abbfc mariadb_repo_setup" \
        | sha256sum -c -
    
    $ chmod +x mariadb_repo_setup
    
    $ sudo ./mariadb_repo_setup \
       --mariadb-server-version="mariadb-10.5"
    
    $ sudo apt update
    
  2. Install MariaDB Dump and package dependencies:

    $ sudo apt install mariadb-client
    

Installing MariaDB Dump via ZYpp (SLES)

  1. Configure ZYpp package repositories:

    $ sudo zypper install wget
    
    $ wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
    
    $ echo "32e01fbe65b4cecc074e19f04c719d1a600e314236c3bb40d91e555b7a2abbfc mariadb_repo_setup" \
        | sha256sum -c -
    
    $ chmod +x mariadb_repo_setup
    
    $ sudo ./mariadb_repo_setup \
       --mariadb-server-version="mariadb-10.5"
    
  2. Install MariaDB Dump and package dependencies:

    $ sudo zypper install MariaDB-client
    

Installing MariaDB Dump via MSI (Windows)

  1. Go to the MariaDB Platform section / MariaDB Community Server tab of the MariaDB download page:

  2. In the "Version" drop-down, select the version you want to download.

  3. In the "OS" drop-down, select either "MS Windows (64-bit)" or "MS Windows (32-bit)", depending on whether you need a 64-bit or 32-bit client.

  4. Click the "Download" button to download the MSI package.

  5. When the MSI package finishes downloading, run it.

  6. On the first screen, click "Next" to start the Setup Wizard.

  7. On the second screen, click the license agreement checkbox, and then click "Next".

  8. On the third screen, select the components you want to install. If you only want the standard MariaDB Client tools:

    • Deselect "Database instance".

    • Deselect "Backup utilities".

    • Deselect "Development Components".

    • Deselect "Third party tools".

    • When only "Client programs" is selected, click "Next".

  9. On the next screen, click "Install".

  10. When the installation process completes, click "Finish".

Creating a Backup with MariaDB Dump

MariaDB Dump command-line parameters vary by operating system.

Creating a Backup with MariaDB Dump on Linux

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

    Download

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

    Note

    MariaDB SkySQL does not use the standard port for client connections, so you must specify the correct port when connecting to a database service.

  2. Use MariaDB Dump with the connection information to create a backup of your MariaDB SkySQL database service.

    Databases, tables, views, sequences, indexes, and triggers will be automatically included in the backup. However, the --events option must be specified to include events, and the --routines option must be specified to include stored procedures and stored functions.

    $ mariadb-dump --host example.skysql.net --port 5001 \
          --user db_user --password \
          --ssl-verify-server-cert \
          --ssl-ca ~/Downloads/skysql_chain.pem \
          --all-databases \
          --ignore-database=mysql \
          --single-transaction \
          --events \
          --routines \
          --default-character-set=utf8mb4 \
          > skysql_dump.sql
    

    After the command is executed, you will be prompted for the 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.

Creating a Backup with MariaDB Dump on Windows

  1. Configure the executable search path.

    On Windows, MariaDB Dump is not typically found in the executable search path by default. You must find its installation path, and add that path to the executable search path:

    $ SET "PATH=C:\Program Files\MariaDB 10.5\bin;%PATH%"
    
  2. 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

    Download

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

    Note

    MariaDB SkySQL does not use the standard port for client connections, so you must specify the correct port when connecting to a database service.

  3. Use MariaDB Dump with the connection information to create a backup of your MariaDB SkySQL database service.

    Databases, tables, views, sequences, indexes, and triggers will be automatically included in the backup. However, the --events option must be specified to include events, and the --routines option must be specified to include stored procedures and stored functions.

    $ mariadb-dump --host example.skysql.net --port 5001 \
          --user db_user --password \
          --ssl-verify-server-cert \
          --ssl-ca ~/Downloads/skysql_chain.pem \
          --all-databases \
          --ignore-database=mysql \
          --single-transaction \
          --events \
          --routines \
          --default-character-set=utf8mb4 \
          > skysql_dump.sql
    

    After the command is executed, you will be prompted for the 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.

Restoring a Backup from MariaDB Dump

The steps to restore backups created by MariaDB Dump vary by operating system.

Restoring a Backup from MariaDB Dump on Linux

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

    Download

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

    Note

    MariaDB SkySQL does not use the standard port for client connections, so you must specify the correct port when connecting to a database service.

  2. If your backup contains any CREATE VIEW, CREATE FUNCTION, CREATE PROCEDURE, CREATE TRIGGER, or CREATE EVENT statements, you must 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.

    You can use grep to search your backup file for statements that have DEFINER clauses:

    $ grep -C 2 "DEFINER=" skysql_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@%, 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` */;
    
  3. Use MariaDB Client with the connection information to import your databases into your MariaDB SkySQL database service:

    $ 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 \
          < skysql_dump.sql
    

    After the command is executed, you will be prompted for the password.

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

Restoring a Backup from MariaDB Dump on Windows

  1. Fix your executable search path.

    On Windows, MariaDB Client is not typically found in the executable search path by default. You must find its installation path, and add that path to the executable search path:

    $ SET "PATH=C:\Program Files\MariaDB 10.5\bin;%PATH%"
    
  2. 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

    Download

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

    Note

    MariaDB SkySQL does not use the standard port for client connections, so you must specify the correct port when connecting to a database service.

  3. If your backup contains any CREATE VIEW, CREATE FUNCTION, CREATE PROCEDURE, CREATE TRIGGER, or CREATE EVENT statements, you must 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.

    You can use findstr to search your backup file for statements that have DEFINER clauses:

    $ findstr "DEFINER=" skysql_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@%, 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` */;
    
  4. Use MariaDB Client with the connection information to import your databases into your MariaDB SkySQL database service:

    $ mariadb --host example.skysql.net --port 5001 \
          --user db_user --password \
          --ssl-verify-server-cert \
          --ssl-ca skysql_chain.pem \
          --default-character-set=utf8mb4 \
          < skysql_dump.sql
    

    After the command is executed, you will be prompted for the password.

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

MariaDB Dump 10.3 and Older

With MariaDB Client 10.4 and later, use the mariadb-dump command as documented above.

For MariaDB Client 10.3 and older, use the legacy mysqldump command.

On Linux:

$ mysqldump --host example.skysql.net --port 5001 \
      --user db_user --password \
      --ssl-verify-server-cert \
      --ssl-ca ~/Downloads/skysql_chain.pem \
      --all-databases \
      --ignore-database=mysql \
      --single-transaction \
      --events \
      --routines \
      --default-character-set=utf8mb4 \
      > skysql_dump.sql

On Windows:

$ mysqldump --host example.skysql.net --port 5001 \
      --user db_user --password \
      --ssl-verify-server-cert \
      --ssl-ca ~/Downloads/skysql_chain.pem \
      --all-databases \
      --ignore-database=mysql \
      --single-transaction \
      --events \
      --routines \
      --default-character-set=utf8mb4 \
      > skysql_dump.sql

MariaDB Client 10.3 and Older

With MariaDB Client 10.4 and later, use the mariadb command as documented above.

For MariaDB Client 10.3 and older, use the legacy mysql command.

On Linux:

$ 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 \
      < skysql_dump.sql

On Windows:

$ mysql --host example.skysql.net --port 5001 \
      --user db_user --password \
      --ssl-verify-server-cert \
      --ssl-ca skysql_chain.pem \
      --default-character-set=utf8mb4 \
      < skysql_dump.sql