Restore

Restore

To restore MariaDB Data, follow the steps below:

Restore Data on Windows

Automatic and Manual MariaDB Database Restore

Restore files from your backup destination and automatically apply them to the MariaDB database server in the original location or alternate location.

  1. ONLY FOR AUTOMATIC RESTORE: Log in to MariaDB Server using MariaDB Command Line Client and verify the database instance is running.

    
Enter password: ********
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 10.9.2-MariaDB maria.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| chinook |
| information_schema |
| mysql |
| nation |
| performance_schema |
| sys |
| test |
| world |
+--------------------+
7 rows in set (0.001 sec)

  2. In the AhsayOBM main interface, click the Restore icon.

    success

  3. Select the backup set and destination.

    success

    You can select the temporary directory for storing temporary files by clicking the Browse button.

    success

  4. FOR AUTOMATIC RESTORE: Select to restore the MariaDB database(s) from a specific backup job, then select the database(s) that you would like to restore. Click Next to proceed.

    success

    FOR MANUAL RESTORE: Select to restore the MariaDB database(s) from a specific backup job then select the database that you would like to restore and tick the Restore raw file option. Click Next to proceed.

    success

  5. Select to restore the MariaDB databases automatically or select the location path where you would like the raw file(s) to be saved for manual restore:

    success

    Automatic Restore

    Specify the host, port, username and password. The “Verify checksum” setting may also be modified, then click Next to proceed.

    success

    Specify a new name for the databases, then click Restore to start the automatic restoration of databases.

    success

    If the original name and the new name of the database is the same, the original database will be overwritten with the database to be restored and this warning message will be available.

    success

    If there are identical new name for the databases, the restoration will not be allowed to proceed and this error message will be available.

    success

    Restore Raw File

    ONLY FOR MANUAL RESTORE: Select the location on the local machine you wish to restore the MariaDB database files to. Click Restore to proceed.

    success

    If you would like to modify the Verify checksum of in-file delta files setting, click Show advanced option.

    success

    Verify checksum of in-file delta files during restore

    By enabling this option, the checksum of in-file delta files will be verified during the restore process. This will check the data for errors during the restore process and create a data summary of the in-file delta files which will be included in the report.

  6. “Restore Completed Successfully” will be displayed when the restoration is completed.

    success

  7. FOR AUTOMATIC RESTORE: Using MariaDB Command Line Client, you can list the restored databases and tables.

    Example: Listing the tables in the database using show tables.

    
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| chinook |
| collection |
| information_schema |
| mysql |
| nation |
| performance_schema |
| sys |
| world |
+--------------------+
8 rows in set (0.001 sec)
MariaDB [(none)]> show tables in nation;
+----------------------+
| Tables_in_nation |
+----------------------+
| continents |
| countries |
| country_languages |
| country_stats |
| guests |
| languages |
| region_areas |
| regions |
| vips |
+----------------------+
3 rows in set (0.001 sec)
MariaDB [(none)]> show tables in world;
+----------------+
| Tables_in_world |
+----------------+
| city |
| country |
| country language |
+----------------+
3 rows in set (0.001 sec)
MariaDB [(none)]>

    FOR MANUAL RESTORE: Check the location on the local machine to verify the MariaDB database files have been restored.

    Example: Using Windows File Explorer

    success

Recovering MariaDB Databases

  1. Log in to MariaDB Server using MariaDB Command Line Client and verify the database instance is running.

    
Enter password: ******
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 69
Server version: 10.9.2-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| chinook |
| collection |
| information_schema |
| mysql |
| nation |
| performance_schema |
| sys |
| world |
+--------------------+
8 rows in set (0.001 sec)
MariaDB [(none)]>

  2. Create the database names that needs to be recovered.

    Example: chinook, nation, and world.

    
MariaDB [(none)]> create database chinook;
Query OK, 1 row affected (0.002 sec)
MariaDB [(chinook)]> create database world;
Query OK, 1 row affected (0.001 sec)

  3. Recover Databases.

    Repeat the following steps for all databases you wish to restore.

    
MariaDB [(none)]> use world;
Database changed
MariaDB [world]> source E:\Restore\MariaDB\world.sql
Query OK, 0 rows affected (0.000 sec)
Query OK, 4079 rows affected (0.015 sec)
Records: 4079 Duplicates: 0 Warnings: 0
MariaDB [(none)]> use world-clone;
Database changed
MariaDB [nation]> source E:\Restore\MariaDB\world-clone.sql
Query OK, 0 rows affected (0.000 sec)
Query OK, 4079 rows affected (0.15 sec)
Records: 4079 Duplicates: 0 Warnings: 0

  4. Check the database status

    Example: Listing the tables in the database using "show tables".

    
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| chinook |
| information_schema |
| mysql |
| nation |
| performance_schema |
| sys |
| world |
| world-clone |
+--------------------+
8 rows in set (0.001 sec)
MariaDB [(none)]> show tables in world;
+----------------+
| Tables_in_world|
+----------------+
| city |
| country |
| country language |
+----------------+
3 rows in set (0.000 sec)
MariaDB [(none)]> show tables in world-clone;
+----------------------+
| Tables_in_world_clone |
+----------------------+
| city |
| country |
| country language |
+----------------------+
3 rows in set (0.001 sec)

Restoring Data on Linux

There are two options to restore a MariaDB Database Backup Set for Linux using the Restore.sh script.

  • Automatic MariaDB Database Restore

    This is recommended for restore of MariaDB databases on the original MariaDB server.

    For this option the RESTORE_TO=”” parameter in the Restore.sh script should be left blank or empty.

    The MariaDB database .sql files are restored from your backup destination to the MariaDB database server. The restored MariaDB database .sql files are then automatically applied to the MariaDB database server.

  • Manual MariaDB Database Restore

    This is recommended for restore of MariaDB databases on a different MariaDB server.

    For this option the RESTORE_TO=”” parameter in the Restore.sh script should contain a location on the MariaDB server.

    The MariaDB database .sql files are only restored from your backup destination to the location specified in RESTORE_TO=”” parameter, e.g. RESTORE_TO=”/restored_file”. The restored MariaDB database .sql files then need to be separately applied to the MariaDB database instance using the “source” command.

Automatic MariaDB Database Restore

  1. To restore files from your backup destination and automatically apply them to the MariaDB database server.

    You need to use the Restore.sh script by using a text editor like vi to configure the restore settings like:

    • Backup Set Name -> BACKUP_SET=""
    • Backup Destination -> DESTINATION=""
    • Files/Folders to be Restored -> RESTORE_FROM=""
    • Snapshot to be restored ->POINT_IN_TIME=""
    • Applying the original permission to the restore files ->RESTORE_PERMISSION=""
    • Verifying the in-file delta file checksum ->VERIFY_CHKSUM=""
    • RESTORE_TO="" settings must be blank
    • To configure the shell script, press Esc = I. You will see this -- INSERT -- on the lower left corner of the terminal.
    
# cd/usr/local/obm/bin
# vi Restore.sh
# !/bin/sh
############################## Restore.sh ###############################
#You can use this shell script to restore backup files using command-line. #
#Just customize the "User Define Section" below with values for your restore #
#action. #
###############################################################################
#
###################### Start: User Defined Section #####################
#------------------------------- BACKUP_SET -----------------------------
# | The name or ID of the backup set that you want to restore. |
# | If backup set name is not in English, please use ID instead. |
# | e.g. BACKUP_SET="1119083740107" |
# | or BACKUP_SET="FileBackupSet-1" |
# | |
# | You can leave this parameter blank if you have only 1 backup set. |
#--------------------------------------------------------------------------
BACKUP_SET="MariaDB Database Backup Set 1”
#------------------------------- DESTINATION -----------------------------
# | The name or ID of the backup destination that you want to restore from. |
# | If backup destination name is not in English, please use ID instead. |
# | e.g. DESTINATION="1740107119083" |
# | or DESTINATION="Destination-1" |
# | |
NOTE
RESTORE_TO=”” settings must be blank
To configure the shell script, press Esc + I. You will see this -- INSERT -- on the lower left corner of
the terminal.
# | You can leave this parameter blank if you have only 1 destination. |
#--------------------------------------------------------------------------
DESTINATION="Local-1"
#------------------------------- RESTORE_TO -----------------------------
# | Directory to where you want files to be restored |
# | set to "" to restore files to original location |
# | e.g. RESTORE_TO="/tmp" |
#--------------------------------------------------------------------------
RESTORE_TO=""
#------------------------------ RESTORE_FROM ----------------------------
# | File/Directory on the backup server that you would like to restore |
# | e.g. RESTORE_FROM="/Data" |
# --------------------------------------------------------------------------
RESTORE_FROM="MariaDB"
#----------------------------- POINT_IN_TIME ----------------------------
# | The point-in-time snapshot (successful backup) that you want to restore |
# | from the backup server. Use "Current" for the latest backup snapshot |
# | e.g. POINT_IN_TIME="2006-10-04-12-57-13" |
# | or POINT_IN_TIME="Current" |
# | |
# | You can retrieve the point in time by using the ListBackupJob.sh |
#--------------------------------------------------------------------------
POINT_IN_TIME="Current"
#-------------------------- RESTORE_PERMISSION ------------------------------
# | set to "Y" if you want to restore file permissions |
# | set to "N" if you do NOT want to restore file permissions |
# --------------------------------------------------------------------------
RESTORE_PERMISSION="N"
#---------------------------- SKIP_INVALID_KEY --------------------------
# | set to "Y" if you want to skip restore file with invalid key |
# | set to "N" if you want to prompt user to input a correct key |
# --------------------------------------------------------------------------
SKIP_INVALID_KEY="N"
#------------------------------ SYNC_OPTION -----------------------------
# | Delete extra files |
# ------------------------------ SYNC_OPTION -----------------------------
# | Delete extra files |
# | set to "Y" if you want to enable sync option |
# | set to "N" if you do NOT want to enable sync option |
# | set to "" to prompt for selection |
#-----------------
---------------------------------------------------------
SYNC_OPTION="N"
# ------------------------- REPLACE_EXISTING_FILE ------------------------
# | set to "--all" to replace all existing file(s) of the same filename |
# | set to "--none" to skip all existing file(s) with the same filename |
# | set to "" to prompt for selection |
# --------------------------------------------------------------------------
REPLACE_EXISTING_FILE="--all"
#------------------------------ SETTING_HOME ----------------------------
# | Directory to your setting home. |
# | Default to ${HOME}/.obm when not set. |
# | e.g. SETTING_HOME="${HOME}/.obm" |
# --------------------------------------------------------------------------
SETTING_HOME=""
#--------------------------------- FILTER -------------------------------
# | Filter out what files you want to restore |
# | -Pattern=xxx-Type=yyy-Target=zzz |
# | where xxx is the filter pattern, |
# | yyy is the filter type, whice can be one of the following: |
# | exact | exactMatchCase | contains | containsMatchCase| |
# | startWith | startWithMatchCase | endWith| endWithMatchCase] |
# | zzz is the filter target, which can be one of the following: |
# | [toFile | toFileDir | toDir] |
# | e.g. FILTER="-Pattern=.txt-Type=exact-Target=toFile" |
# --------------------------------------------------------------------------
FILTER=""
#-------------------------------- TEMP_DIR ------------------------------
# | Directory to where you want to store restore files temporarily |
# | set to "" to use the temporary directory in the backup set |
# | e.g. TEMP_DIR="/tmp" |
#--------------------------------------------------------------------------
TEMP_DIR="/temp"
#----------------------------- VERIFY_CHKSUM ----------------------------
# | set to "Y" if you want to verify in-file delta file checksum during restore|
# | set to "N" if you do NOT want to verify in-file delta file checksum during
|
# | restore |
#--------------------------------------------------------------------------
VERIFY_CHKSUM="N"
####################### END: User Defined Section #########################
############################################################################
# R E T R I E V E A P P _ H O M E P A T H # ###############################################################################
#####

  2. After the Restore.sh script is configured, the files can be restored automatically to the MariaDB Database Server.

    
# cd /usr/local/obm/bin
# sh Restore.sh
Using APP_HOME: : /usr/local/obm
Using BACKUP_SET : MariaDB Database Backup Set 1
Using RESTORE_FROM : MariaDB
Using RESTORE_TO :
Using POINT_IN_TIME : Current
Using RESTORE_PERMISSION : N
Using TEMP_DIR : /temp
Filter Pattern not set, filter would not apply to restore
[2023-04-19 08:12:09] Start [ AhsayOBM v9.5.2.0 ]
[2023-04-19 08:12:09] OS: Linux 3.10.0-1160.76.1.el7.x86_64
(localhost.localdomain); CPU Model: VMware-Intel(R) Xeon(R) Gold 5218R CPU @
2.10GHz,Intel(R) Xeon(R) Gold 5218R CPU @ 2.10GHz; Number of Processors: 4;
Heap Size: 20.8 MB (Current) / 1.8 GB (Maximum); Physical Memory: 121.5 MB
(Free) / 3.7 GB (Total)
[2023-04-19 08:12:09] start,Start [ AhsayOBM v9.5.2.0 ],0,0,0,,0,0
[2023-04-19 08:12:09] Selected job: 2023-04-19-08-11-57
[2023-04-19 08:12:09] Selected source: [MariaDB]
[2023-04-19 08:12:09] Info: [followLink=false marshalTargetPath=false
deleteForSync=false skipFaultKey=false verifyDeltaFileChecksum=false
ignoreSegmentInfoCorruptedData=true restorePermission=false [RestoreLocation]
type=ORIGINAL path=[null]]
^[[2023-04-19 08:12:11] Creating new directory...
"/temp/RestoreSet/1679893995891/RestoreDatabase/MariaDB"
[2023-04-19 08:12:11] [1153256202][SharedBlockRestore] MariaDB/collection.sql
(1376)
[2023-04-19 08:12:11] [1153256202][SharedBlockRestore] MariaDB/mysql.sql
(2082840)
[2023-04-19 08:12:11] [1153256202][SharedBlockRestore] MariaDB/test.sql (1358)
[2023-04-19 08:12:11] Downloading...
"/temp/RestoreSet/1679893995891/RestoreDatabase/MariaDB/test.sql" (Total 1.33 K
bytes)
[2023-04-19 08:12:11] Downloading...
"/temp/RestoreSet/1679893995891/RestoreDatabase/MariaDB/collection.sql" (Total
1.34 K bytes)
[2023-04-19 08:12:11]
file,/temp/RestoreSet/1679893995891/RestoreDatabase/MariaDB/test.sql,624,1358,1
680202826000,,1681863131546,1681863131549
[2023-04-19 08:12:11]
file,/temp/RestoreSet/1679893995891/RestoreDatabase/MariaDB/collection.sql,624,
1376,1680202825000,,1681863131547,1681863131551
[2023-04-19 08:12:11] Downloading...
"/temp/RestoreSet/1679893995891/RestoreDatabase/MariaDB/mysql.sql" (Total 1.99
M bytes)
[2023-04-19 08:12:11]
file,/temp/RestoreSet/1679893995891/RestoreDatabase/MariaDB/mysql.sql,782928,20
82840,1680202826000,,1681863131659,1681863131663
[2023-04-19 08:12:11] Start restore files to MariaDB... "test"
[2023-04-19 08:12:12] Restoring to MariaDB Database... "test"
[2023-04-19 08:12:12] End restore files to MariaDB... "test"
[2023-04-19 08:12:12] Start restore files to MariaDB... "collection"
[2023-04-19 08:12:12] Restoring to MariaDB Database... "collection"
[2023-04-19 08:12:12] End restore files to MariaDB... "collection"
[2023-04-19 08:12:12] Start restore files to MariaDB... "mysql"
[2023-04-19 08:12:12] Restoring to MariaDB Database... "mysql"
[2023-04-19 08:12:13] End restore files to MariaDB... "mysql"
[2023-04-19 08:12:14] Restore Completed Successfully
[2023-04-19 08:12:14] end,RESTORE_STOP_SUCCESS,0,0,0,,0,0

  3. Login to the MariaDB server to check the database status.

    Example: Listing the tables in the database using "show tables".

    
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| collection |
| image |
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
7 rows in set (0.001 sec)
MariaDB [(none)]> show tables in mysql;
+----------------------+
| Tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| db |
| event |
| func |
| general_log |
| global_priv |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| index_stats |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| roles_mapping |
| servers |
| slow_log |
| table_stats |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| transaction_registry |
| user |
+---------------------------+
31 rows in set (0.000 sec)
MariaDB [(none)]> show tables in test;
+----------------+
| Tables_in_test |
+----------------+
| table1 |
| table2 |
| table3 |
| table4 |
| table5 |
+----------------+
5 rows in set (0.00 sec)

Manual MariaDB Database Restore

To restore files that have been backed up from your backup destination, you need to use the Restore.sh script by using a text editor like vi to configure the restore settings like:

  • Backup Set Name -> BACKUP_SET=""
  • Backup Destination -> DESTINATION=""
  • Location of Restored Files -> RESTORE_TO=""
  • Files/Folders to be Restored -> RESTORE_FROM=""
  • Snapshot to be restored ->POINT_IN_TIME=""
  • Applying the original permission to the restore files ->RESTORE_PERMISSION=""
  • Verifying the in-file delta file checksum ->VERIFY_CHKSUM=""

# sh Restore.sh
Using APP_HOME: : /usr/local/obm
Using BACKUP_SET : MariaDB Database Backup Set 1
Using RESTORE_FROM : MariaDB
Using RESTORE_TO : /root/restored
Using POINT_IN_TIME : Current
Using RESTORE_PERMISSION : N
Using TEMP_DIR : /temp
Filter Pattern not set, filter would not apply to restore
[2023-04-19 08:56:47] Start [ AhsayOBM v9.5.2.0 ]
[2023-04-19 08:56:47] OS: Linux 3.10.0-1160.76.1.el7.x86_64
(localhost.localdomain); CPU Model: VMware-Intel(R) Xeon(R) Gold 5218R CPU
@ 2.10GHz,Intel(R) Xeon(R) Gold 5218R CPU @ 2.10GHz; Number of Processors:
4; Heap Size: 26.7 MB (Current) / 1.8 GB (Maximum); Physical Memory: 120.1
MB (Free) / 3.7 GB (Total)
[2023-04-19 08:56:47] start,Start [ AhsayOBM v9.5.2.0 ],0,0,0,,0,0
[2023-04-19 08:56:47] Selected job: 2023-04-19-08-56-35
[2023-04-19 08:56:47] Selected source: [MariaDB]
[2023-04-19 08:56:47] Info: [followLink=false marshalTargetPath=false
deleteForSync=false skipFaultKey=false verifyDeltaFileChecksum=false
ignoreSegmentInfoCorruptedData=true restorePermission=false
[RestoreLocation] type=RAW path=[/root/restored]]
[2023-04-19 08:56:49] [520830206][SharedBlockRestore]
MariaDB/collection.sql (1376)
[2023-04-19 08:56:49] [520830206][SharedBlockRestore] MariaDB/mysql.sql
(2082840)
[2023-04-19 08:56:49] [520830206][SharedBlockRestore] MariaDB/test.sql
(1358)
[2023-04-19 08:56:50] Downloading...
"/root/restored/MariaDB/collection.sql" (Total 1.34 K bytes)
[2023-04-19 08:56:50] Existing item found (overwrite)...
/root/restored/MariaDB/test.sql
[2023-04-19 08:56:50] Downloading... "/root/restored/MariaDB/test.sql"
(Total 1.33 K bytes)
[2023-04-19 08:56:50]
file,/root/restored/MariaDB/collection.sql,624,1376,1680202825000,,1681865
810015,1681865810019
[2023-04-19 08:56:50]
file,/root/restored/MariaDB/test.sql,624,1358,1680202826000,,1681865810017
,1681865810020
[2023-04-19 08:56:50] Existing item found (overwrite)...
/root/restored/MariaDB/mysql.sql
[2023-04-19 08:56:50] Downloading... "/root/restored/MariaDB/mysql.sql"
(Total 1.99 M bytes)
[2023-04-19 08:56:50]
file,/root/restored/MariaDB/mysql.sql,782928,2082840,1680202826000,,168186
5810103,1681865810106
[2023-04-19 08:56:51] Restore Completed Successfully
[2023-04-19 08:56:51] end,RESTORE_STOP_SUCCESS,0,0,0,,0,0

Verify the files are restored on the machine.


#ls -la /root/restored/MariaDB
total 2044
dr-xr-x---. 2 root root 61 Apr 19 08:56 .
dr-xr-x---. 3 root root 21 Feb 7 15:52 ..
-rw-r--r--. 1 root root 1376 Mar 31 03:00 collection.sql
-rw-r--r--. 1 root root 2082840 Mar 31 03:00 mysql.sql
-rw-r--r--. 1 root root 1358 Mar 31 03:00 test.sql

To recover MariaDB Databases on Linux, please refer to the Recovering MariaDB Databases segment of this section.

AhsayOBM