Restore a Manual Backup

This page is part of MariaDB's Documentation.

The parent of this page is: Backup/Restore

Topics on this page:

Overview
Compatibility
Restore a Backup

Overview

MariaDB SkySQL customers can manually create a backup of a SkySQL service using the mariadb-dump utility:

The manual backups can be restored using the mariadb client
The mariadb client provides a command-line interface (CLI)
The mariadb client is available for Linux and Windows
The mariadb client supports many command-line options

Compatibility

Distributed Transactions
Replicated Transactions
Single Node Transactions

Restore a Backup

The procedure to restore a backup depends on the operating system.

The mariadb client must be installed to perform the following procedure.

Linux

Determine the connection parameters for your MariaDB SkySQL service.
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` */;
```
Use MariaDB Client with the connection information to import your databases into your MariaDB SkySQL database service:
```
$ mariadb --host FULLY_QUALIFIED_DOMAIN_NAME --port TCP_PORT \
      --user DATABASE_USER --password \
      --ssl-verify-server-cert \
      --ssl-ca ~/PATH_TO_PEM_FILE \
      --default-character-set=utf8mb4 \
      < skysql_dump.sql
```
- Replace FULLY_QUALIFIED_DOMAIN_NAME with the Fully Qualified Domain Name of your service.
- Replace TCP_PORT with the read-write or read-only port of your service.
- Replace DATABASE_USER with the default username for your service, or the username you created.
- Replace ~/PATH_TO_PEM_FILE with the path to the certificate authority chain (.pem) file.
After the command is executed, you will be prompted for a password. Enter the default password for your default user, the password you set for the default user, or the password for the database user you created.
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";
```

Windows

Fix your 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.6\bin;%PATH%"
```
Determine the connection parameters for your MariaDB SkySQL service.
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` */;
```
Use MariaDB Client with the connection information to import your databases into your MariaDB SkySQL database service:
```
$ mariadb --host FULLY_QUALIFIED_DOMAIN_NAME --port TCP_PORT \
      --user DATABASE_USER --password \
      --ssl-verify-server-cert \
      --ssl-ca ~/PATH_TO_PEM_FILE \
      --default-character-set=utf8mb4 \
      < skysql_dump.sql
```
- Replace FULLY_QUALIFIED_DOMAIN_NAME with the Fully Qualified Domain Name of your service.
- Replace TCP_PORT with the read-write or read-only port of your service.
- Replace DATABASE_USER with the default username for your service, or the username you created.
- Replace ~/PATH_TO_PEM_FILE with the path to the certificate authority chain (.pem) file.
After the command is executed, you will be prompted for a password. Enter the default password for your default user, the password you set for the default user, or the password for the database user you created.
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 Client 10.3 and Older

The instructions provided above are written for MariaDB Client 10.4 and later, which uses the binary filename of mariadb.

For MariaDB Client 10.3 and older, the binary filename was mysql. The instructions can be adapted for MariaDB Client 10.3 and older by executing mysql rather than mariadb.