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
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
clientThe
mariadb
client provides a command-line interface (CLI)The
mariadb
client is available for Linux and WindowsThe
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 haveDEFINER
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 theDEFINER
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 haveDEFINER
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 theDEFINER
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
.