Restore a MySQL Database
To restore a MySQL Database backup set, follow the steps below:
- MySQL Database Automatic and Manual Restore on Windows
- MySQL Database Automatic Restore on Linux
- MySQL Database Manual Restore on Linux
- Recovering Databases
MySQL Database Automatic and Manual Restore on Windows
Login to MySQL Server using MySQL Command Line Client and verify the database instance is running.
Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 5.7.17-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sakila | | sys | | world | +--------------------+ 6 rows in set (0.00 sec) mysql>This step is applicable for Automatic Restore only.
In the AhsayOBM main interface, click the Restore icon.
Select the backup set and destination.
You can select the temporary directory for storing temporary files by clicking the Browse button.
Select the database(s) you would like to restore.
For Automatic Restore, select the MySQL node from a specific backup job then select the database(s) that you would like to restore. Click Next to proceed when you are done with the selection.
For Manual Restore, tick Restore raw file then click Next to proceed.
LIMITATIONS
If you would like to restore database to an alternate SQL server with the Restore raw file option, make sure you have checked the Restore raw file option.
Select to restore the MySQL Databases automatically or select the location path where you would like the raw file(s) to be saved for manual restore:
Automatic Restore
Specify the host, port, username and password. The “Verify checksum” setting may also be modified, then click Next to proceed.
Specify a new name for the databases, then click Restore to start the automatic restoration of databases.
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.
If there are identical new name for the databases, the restoration will not be allowed to proceed and this error message will be available.
Restore Raw File
Choose the location path where you would like the raw file(s) to be restored to. Click Restore to proceed.
If you would like to modify the “Verify checksum of in-file delta files during restore” setting, click Show advanced option.
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.
Once restoration is finished, “Restore Completed Successfully” will be displayed.
Verify the restore.
For Automatic Restore
Using MySQL Command Line Client, you can list the restored databases and tables. Example: Listing the tables in the database using show tables
mysql> show databases; +--------------------+ | Database | +--------------------+ | books | | classicmodels | | information_schema | | mysql | | performance_schema | | sakila | | sys | | world | +--------------------+ 8 rows in set (0.00 sec) mysql> show tables in books; +-----------------+ | Tables_in_books | +-----------------+ | author | | title | +-----------------+ 2 rows in set (0.08 sec) mysql> show tables in sakila; +----------------------------+ | Tables_in_sakila | +----------------------------+ | actor | | actor_info | | address | | category | | city | | country | | customer | | customer_list | | film | | film_actor | | film_category | | film_list | | film_text | | inventory | | language | | nicer_but_slower_film_list | | payment | | rental | | sales_by_film_category | | sales_by_store | | staff | | staff_list | | store | +----------------------------+ 23 rows in set (0.04 sec) mysql> show tables in world; +-----------------+ | Tables_in_world | +-----------------+ | city | | country | | countrylanguage | +-----------------+ 3 rows in set (0.00 sec) mysql>For Manual Restore
Check the location on the local machine to verify the MySQL database files have been restored.
Example: Using Windows File Explorer
MySQL Database Automatic and Manual Restore on Linux
To restore files from your backup destination and automatically apply them to the MySQL 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 backupset. | # -------------------------------------------------------------------------- BACKUP_SET="MySQL Database 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. | 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. # | e.g. DESTINATION="1740107119083" | # | or DESTINATION="Destination-1" | # | | # | You can leave this parameter blank if you have only 1 destination. | # -------------------------------------------------------------------------- DESTINATION="" # ------------------------------- 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="MySQL" # ----------------------------- 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 | # | 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="/tmp" # ----------------------------- 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 # ############################################################################After the Restore.sh script is configured, the files can be restored automatically to the MySQL Database Server.
# cd /usr/local/obm/bin # sh Restore.sh Using APP_HOME: : /usr/local/obm Using BACKUP_SET : MySQL Database 1 Using RESTORE_FROM : MySQL Using RESTORE_TO : Using POINT_IN_TIME : Current Using RESTORE_PERMISSION : N Using TEMP_DIR : /tmp Filter Pattern not set, filter would not apply to restore [2023-03-21 09:47:46] Start [ AhsayOBM v9.5.2.0 ] [2023-03-21 09:47:46] OS: Linux 3.10.0-1062.12.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: 44.8 MB (Current) / 1.8 GB (Maximum); Physical Memory: 116.6 MB (Free) / 3.7 GB (Total) [2023-03-21 09:47:46] start,Start [ AhsayOBM v9.5.2.0 ],0,0,0,,0,0 [2023-03-21 09:47:46] Selected job: 2023-03-21-09-47-34 [2023-03-21 09:47:46] Selected source: [MySQL] [2023-03-21 09:47:46] Info: [followLink=false marshalTargetPath=false deleteForSync=false skipFaultKey=false verifyDeltaFileChecksum=false ignoreSegmentInfoCorruptedData=true restorePermission=false [RestoreLocation] type=ORIGINAL path=[null]] [2023-03-21 09:47:48] Creating new directory... "/tmp/RestoreSet/1679282248469/RestoreDatabase/MySQL" [2023-03-21 09:47:48] [1693540983][SharedBlockRestore] MySQL/test.sql (4040) [2023-03-21 09:47:48] [1693540983][SharedBlockRestore] MySQL/world.sql (3124) [2023-03-21 09:47:48] Downloading... "/tmp/RestoreSet/1679282248469/RestoreDatabase/MySQL/world.sql" (Total 3.05 K bytes) [2023-03-21 09:47:48] Downloading... "/tmp/RestoreSet/1679282248469/RestoreDatabase/MySQL/test.sql" (Total 3.95 K bytes) [2023-03-21 09:47:48] file,/tmp/RestoreSet/1679282248469/RestoreDatabase/MySQL/world.sql,1072,3124,167 9338824000,,1679363268531,1679363268536 [2023-03-21 09:47:48] file,/tmp/RestoreSet/1679282248469/RestoreDatabase/MySQL/test.sql,1120,4040,1679 338824000,,1679363268533,1679363268537 [2023-03-21 09:47:50] Start restore files to MySQL Server... "world" [2023-03-21 09:47:50] Restoring to MySQL Server Database... "world" [2023-03-21 09:47:51] End restore files to MySQL Server... "world" [2023-03-21 09:47:51] Start restore files to MySQL Server... "test" [2023-03-21 09:47:51] Restoring to MySQL Server Database... "test" [2023-03-21 09:47:52] End restore files to MySQL Server... "test" [2023-03-21 09:47:54] Restore Completed Successfully [2023-03-21 09:47:54] end,RESTORE_STOP_SUCCESS,0,0,0,,0,0Log in to the MySQL server to check the database status.
Example: Listing the tables in the database using show tables.
mysql>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | | world | +--------------------+ 6 rows in set (0.00 sec) mysql>show tables in test; +----------------+ | Tables_in_test | +----------------+ | table1 | | table2 | | table3 | | table4 | | table5 | +----------------+ 5 rows in set (0.01 sec) mysql>show tables in world; +-----------------+ | Tables_in_world | +-----------------+ | city | | country | | countrylanguage | +-----------------+ 3 rows in set (0.00 sec) mysql>
MySQL Database Manual Restore on Linux
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=""
# cd /usr/local/obm/bin
# sh Restore.sh
Using APP_HOME: : /usr/local/obm
Using BACKUP_SET : MySQL Database 1
Using RESTORE_FROM : MySQL
Using RESTORE_TO : /root/MySQL
Using POINT_IN_TIME : Current
Using RESTORE_PERMISSION : N
Using TEMP_DIR : /tmp
Filter Pattern not set, filter would not apply to restore
[2023-03-21 09:47:46] Start [ AhsayOBM v9.5.2.0 ]
[2023-03-21 09:47:46] OS: Linux 3.10.0-1062.12.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: 44.8 MB (Current) / 1.8 GB (Maximum); Physical Memory: 116.6 MB
(Free) / 3.7 GB (Total)
[2023-03-21 09:47:46] start,Start [ AhsayOBM v9.5.2.0 ],0,0,0,,0,0
[2023-03-21 09:47:46] Selected job: 2023-03-21-09-47-34
[2023-03-21 09:47:46] Selected source: [MySQL]
[2023-03-21 09:47:46] Info: [followLink=false marshalTargetPath=false
deleteForSync=false skipFaultKey=false verifyDeltaFileChecksum=false
ignoreSegmentInfoCorruptedData=true restorePermission=false [RestoreLocation]
type=ORIGINAL path=[null]]
[2023-03-21 09:47:48] Creating new directory...
"/tmp/RestoreSet/1679282248469/RestoreDatabase/MySQL"
[2023-03-21 09:47:48] [1693540983][SharedBlockRestore] MySQL/test.sql (4040)
[2023-03-21 09:47:48] [1693540983][SharedBlockRestore] MySQL/world.sql (3124)
[2023-03-21 09:47:48] Downloading...
"/tmp/RestoreSet/1679282248469/RestoreDatabase/MySQL/world.sql" (Total 3.05 K
bytes)
[2023-03-21 09:47:48] Downloading...
"/tmp/RestoreSet/1679282248469/RestoreDatabase/MySQL/test.sql" (Total 3.95 K
bytes)
[2023-03-21 09:47:48]
file,/tmp/RestoreSet/1679282248469/RestoreDatabase/MySQL/world.sql,1072,3124,
1679338824000,,1679363268531,1679363268536
[2023-03-21 09:47:48]
file,/tmp/RestoreSet/1679282248469/RestoreDatabase/MySQL/test.sql,1120,4040,1
679338824000,,1679363268533,1679363268537
[2023-03-21 09:47:50] Start restore files to MySQL Server... "world"
[2023-03-21 09:47:50] Restoring to MySQL Server Database... "world"
[2023-03-21 09:47:51] End restore files to MySQL Server... "world"
[2023-03-21 09:47:51] Start restore files to MySQL Server... "test"
[2023-03-21 09:47:51] Restoring to MySQL Server Database... "test"
[2023-03-21 09:47:52] End restore files to MySQL Server... "test"
[2023-03-21 09:47:54] Restore Completed Successfully
[2023-03-21 09:47:54] end,RESTORE_STOP_SUCCESS,0,0,0,,0,0
Verify the files are restored on the machine
# ls -la /root/MySQL
total 1004
dr-xr-x---. 5 root root 73 Mar 21 11:21 .
dr-xr-x---. 22 root root 4096 Mar 21 11:11 ..
drwxr-xr-x. 4 root root 90 Mar 20 15:18 1679282248469
dr-xr-x---. 2 root root 39 Mar 21 11:21 MySQL
-rwxr-xr-x. 1 root root 1023657 Feb 24 2020 mysql.sql
drwxr-xr-x. 2 root root 6 Mar 20 13:21 settings
#ls -la MySQL
total 8
dr-xr-x---. 2 root root 39 Mar 21 11:21 .
dr-xr-x---. 5 root root 73 Mar 21 11:21 ..
-rw-r--r--. 1 root root 4040 Mar 21 03:00 test.sql
-rw-r--r--. 1 root root 3124 Mar 21 03:00 world.sql
Recovering MySQL Databases
Login to MySQL Server using MySQL Command Line Client and verify the database instance is running.
Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.6.31-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rightsreserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | sakila | | performance_schema | | sys | | world | +--------------------+ 6 rows in set (0.00 sec) mysql>Create the database names that needs to be recovered.
Example: sakila and world
mysql> create database sakila; Query OK, 1 row affected (0.00 sec) mysql> create database world; Query OK, 1 row affected (0.00 sec)- Recover Databases
Repeat the following steps for all databases you with to restore.
mysql> use sakila; mysql> source D:\restored\MySQL\sakila.sql Query OK, 0 rows affected (0.01 sec) Query OK, 148 rows affected (1.9 sec) Records: 148 Duplicates: 0 Warnings: 0 mysql> use world; mysql> source D:\restored\MySQL\world.sql Query OK, 0 rows affected (0.00 sec) Query OK, 4079 rows affected (0.03 sec) Records: 4079 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.01 sec)Check the database status
Example: Listing the tables in the database using show tables
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | classicmodels | | mysql | | performance_schema | | sakila | | world | +--------------------+ 6 rows in set (0.00 sec) mysql> show tables in world; +-----------------+ | Tables_in_world | +-----------------+ | city | | country | | countrylanguage | +-----------------+ 3 rows in set (0.00 sec) mysql> show tables in sakila; +----------------------------+ | Tables_in_sakila | +----------------------------+ | actor | | actor_info | | address | | category | | city | | country | | customer | | customer_list | | film | | film_actor | | film_category | | film_list | | film_text | | inventory | | language | | nicer_but_slower_film_list | | payment | | rental | | sales_by_film_category | | sales_by_store | | staff | | staff_list | | store | +----------------------------+ 23 rows in set (0.00 sec)
Backup and Restore