Sidebar
public:module:mariadb_database:restore
Table of Contents
Restore MariaDB from Backup
Restore Options
The restore options available:
i. Original location – CloudBacko Pro will restore the database(s) from the backup destination and apply them to the original production MariaDB instance.
ii. Alternate location – CloudBacko Pro will restore the database(s) from the backup destination and apply them to the either the original MariaDB instance or another MariaDB instance on the production machine. This option can also be used to clone a database by changing the database name.
iii. Restore raw file – CloudBacko Pro will restore the database *.sql files to a location on the local machine. Which then can be copied to another MariaDB server on another machine for manual recovery.
1). Automatic MariaDB Database Restore
Restore files from your backup destination and automatically apply them to the MariaDB database server in the original location.
1. Login 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 76 Server version: 10.4.12-MariaDB mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. 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. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sportdb | | test | +--------------------+ 5 rows in set (0.001 sec) MariaDB [(none)]>
2. In the CloudBacko Pro's main interface, click the Restore icon.
3. Select the backup set that you would like to restore the MariaDB Database from.
 |
4. Select the storage destination that contains the MariaDB databases that you would like to restore from.
 |
5. Select to restore the MariaDB node from a specific backup job then select the files or folders that you would like to restore. Click Next to proceed.
 |
To restore to either original or alternate location please unselect the MariaDB data node and only select the databases only.
6. Select to restore the MariaDB Databases to the Original location and click Next to proceed.
 |
7. Confirm the temporary directory path is correct and then click Restore to proceed.
 |
8. After the MariaDB database(s) has been restored.
 |
9. 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 | | information_schema | | mysql | | nation | | performance_schema | | sportdb | | test | +--------------------+ 7 rows in set (0.001 sec) MariaDB [(none)]> show tables in chinook; +-------------------+ | Tables_in_chinook | +-------------------+ | album | | artist | | customer | | employee | | genre | | invoice | | invoiceline | | mediatype | | playlist | | playlisttrack | | track | +-------------------+ 11 rows in set (0.001 sec) MariaDB [(none)]> show tables in nation; +------------------+ | Tables_in_nation | +------------------+ | addresses | | affiliations | | awards | | continents | | countries | +------------------+ 5 rows in set (0.000 sec) MariaDB [(none)]>
2). Manual MariaDB Database Restore
To restore the MariaDB databases from your storage destination to a location on disk and manually recover the databases.
1. In the CloudBacko Pro's main interface, click the Restore icon.
2. Select the backup set that you would like to restore the MariaDB Database from.
 |
3. Select the storage destination that contains the MariaDB databases that you would like to restore from.
| |
4. Select to restore the MariaDB database(s) from a specific backup job then select the files or folders that you would like to restore and select the Restore raw file option. Click Next to proceed.
 |
5. Select the location on the local machine you wish to restore the MariaDB database files to. Click Next to proceed.
 |
6. Confirm the temporary directory path is correct and then click Restore to proceed.
 |
7. After the MariaDB database(s) has been restored.
 |
8. Check the location on the local machine to verify the MariaDB database files have been restored.
Example: Using Windows File Explorer
 |
9. Recovering MariaDB Databases
9.1. Login 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 90 Server version: 10.4.12-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 | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) MariaDB [(none)]>
9.2. Create the database names that need to be recovered.
Example: chinook, nation, and sportdb.
MariaDB [(none)]> create database chinook; Query OK, 1 row affected (0.003 sec) MariaDB [(none)]> create database nation; Query OK, 1 row affected (0.003 sec) MariaDB [(none)]> create database sportdb; Query OK, 1 row affected (0.003 sec)
9.3. Recover Databases
Repeat the following steps for all databases you wish to restore.
MariaDB [(none)]> use chinook; Database changed MariaDB [chinook]> source c:\restored\MariaDB\chinook.sql Query OK, 0 rows affected (0.001 sec) Query OK, 110 rows affected (0.00 sec) Records: 110 Duplicates: 0 Warnings: 0 MariaDB [(none)]> use nation; Database changed MariaDB [nation)]> source c:\restored\MariaDB\nation.sql Query OK, 0 rows affected (0.01 sec) Query OK, 148 rows affected (1.9 sec) Records: 148 Duplicates: 0 Warnings: 0 MariaDB [(none)]> use sportdb; Database changed MariaDB [sportdb]> source c:\restored\MariaDB\sportdb.sql Query OK, 0 rows affected (0.00 sec) Query OK, 4079 rows affected (0.03 sec) Records: 4079 Duplicates: 0 Warnings: 0
9.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 | | sportdb | | test| +--------------------+ 7 rows in set (0.06 sec) MariaDB [(none)]> show tables in chinook; +-------------------+ | Tables_in_chinook | +-------------------+ | album | | artist | | customer | | employee | | genre | | invoice | | invoiceline | | mediatype | | playlist | | playlisttrack | | track | +-------------------+ 11 rows in set (0.001 sec) MariaDB [(none)]> show tables in nation; +---------------------------------------+ | Tables_in_nation | +---------------------------------------+ | addresses | | affiliation_phases | | affiliations | | affiliations_documents | | affiliations_events | | affiliations_media | | american_football_action_participants | | american_football_action_plays | | american_football_defensive_stats | | american_football_down_progress_stats | | american_football_event_states | | american_football_fumbles_stats | | american_football_offensive_stats | | american_football_passing_stats | | american_football_penalties_stats | | american_football_rushing_stats | | american_football_sacks_against_stats | | american_football_scoring_stats | | american_football_special_teams_stats | | american_football_team_stats | | awards | | baseball_action_contact_details | | baseball_action_pitches | | baseball_action_plays | | baseball_action_substitutions | | baseball_defensive_group | | baseball_defensive_players | | baseball_defensive_stats | | baseball_event_states | | baseball_offensive_stats | | baseball_pitching_stats | | basketball_defensive_stats | | basketball_event_states | | basketball_offensive_stats | | basketball_rebounding_stats | | basketball_team_stats | | bookmakers | | continents | | core_stats | | countries | | country_languages | | country_stats | | db_info | | display_names | | document_classes | | document_contents | | document_fixtures | | document_fixtures_events | | document_package_entry | | document_packages | | documents | | documents_media | | event_action_fouls | | event_action_participants | | event_action_penalties | | event_action_plays | | event_action_substitutions | | event_states | | events | | events_documents | | events_media | | events_sub_seasons | | guests | | ice_hockey_action_participants | | ice_hockey_action_plays | | ice_hockey_defensive_stats | | ice_hockey_event_states | | ice_hockey_offensive_stats | | ice_hockey_player_stats | | injury_phases | | key_aliases | | key_roots | | languages | | latest_revisions | | locations | | media | | media_captions | | media_contents | | media_keywords | | motor_racing_event_states | | motor_racing_qualifying_stats | | motor_racing_race_stats | | outcome_totals | | participants_events | | penalty_stats | | periods | | person_event_metadata | | person_phases | | persons | | persons_documents | | persons_media | | positions | | publishers | | rankings | | records | | region_areas | | regions | | roles | | seasons | | sites | | soccer_action_fouls | | soccer_action_participants | | soccer_action_penalties | | soccer_action_plays | | soccer_action_substitutions | | soccer_defensive_stats | | soccer_event_states | | soccer_foul_stats | | soccer_offensive_stats | | standing_subgroups | | standings | | stats | | sub_periods | | sub_seasons | | team_phases | | teams | | teams_documents | | teams_media | | tennis_action_points | | tennis_action_volleys | | tennis_event_states | | tennis_player_stats | | tennis_return_stats | | tennis_service_stats | | tennis_set_stats | | tennis_team_stats | | vips | | wagering_moneylines | | wagering_odds_lines | | wagering_runlines | | wagering_straight_spread_lines | | wagering_total_score_lines | | weather_conditions | +---------------------------------------+ 133 rows in set (0.004 sec) MariaDB [(none)]> show tables in sportdb; +---------------------------------------+ | Tables_in_sportdb | +---------------------------------------+ | addresses | | affiliation_phases | | affiliations | | affiliations_documents | | affiliations_events | | affiliations_media | | american_football_action_participants | | american_football_action_plays | | american_football_defensive_stats | | american_football_down_progress_stats | | american_football_event_states | | american_football_fumbles_stats | | american_football_offensive_stats | | american_football_passing_stats | | american_football_penalties_stats | | american_football_rushing_stats | | american_football_sacks_against_stats | | american_football_scoring_stats | | american_football_special_teams_stats | | american_football_team_stats | | awards | | baseball_action_contact_details | | baseball_action_pitches | | baseball_action_plays | | baseball_action_substitutions | | baseball_defensive_group | | baseball_defensive_players | | baseball_defensive_stats | | baseball_event_states | | baseball_offensive_stats | | baseball_pitching_stats | | basketball_defensive_stats | | basketball_event_states | | basketball_offensive_stats | | basketball_rebounding_stats | | basketball_team_stats | | bookmakers | | core_stats | | db_info | | display_names | | document_classes | | document_contents | | document_fixtures | | document_fixtures_events | | document_package_entry | | document_packages | | documents | | documents_media | | event_action_fouls | | event_action_participants | | event_action_penalties | | event_action_plays | | event_action_substitutions | | event_states | | events | | events_documents | | events_media | | events_sub_seasons | | ice_hockey_action_participants | | ice_hockey_action_plays | | ice_hockey_defensive_stats | | ice_hockey_event_states | | ice_hockey_offensive_stats | | ice_hockey_player_stats | | injury_phases | | key_aliases | | key_roots | | latest_revisions | | locations | | media | | media_captions | | media_contents | | media_keywords | | motor_racing_event_states | | motor_racing_qualifying_stats | | motor_racing_race_stats | | outcome_totals | | participants_events | | penalty_stats | | periods | | person_event_metadata | | person_phases | | persons | | persons_documents | | persons_media | | positions | | publishers | | rankings | | records | | roles | | seasons | | sites | | soccer_action_fouls | | soccer_action_participants | | soccer_action_penalties | | soccer_action_plays | | soccer_action_substitutions | | soccer_defensive_stats | | soccer_event_states | | soccer_foul_stats | | soccer_offensive_stats | | standing_subgroups | | standings | | stats | | sub_periods | | sub_seasons | | team_phases | | teams | | teams_documents | | teams_media | | tennis_action_points | | tennis_action_volleys | | tennis_event_states | | tennis_player_stats | | tennis_return_stats | | tennis_service_stats | | tennis_set_stats | | tennis_team_stats | | wagering_moneylines | | wagering_odds_lines | | wagering_runlines | | wagering_straight_spread_lines | | wagering_total_score_lines | | weather_conditions | +---------------------------------------+ 124 rows in set (0.004 sec)
3). Automatic MariaDB Database Restore (Alternative Location)
1. Login 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 90 Server version: 10.4.12-MariaDB mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. 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. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | chinook | | information_schema | | mysql | | nation | | performance_schema | | sportdb | | test | +--------------------+ 7 rows in set (0.00 sec) MariaDB [(none)]>
2. In the CloudBacko Pro's main interface, click the Restore icon.
| |
3. Select the backup set that you would like to restore the MariaDB Database from.
 |
4. Select the storage destination that contains the MariaDB databases that you would like to restore from.
 |
5. Select to restore the MariaDB node from a specific backup job then select the files or folders that you would like to restore. Click Next to proceed.
 |
To restore to either original or alternate location please unselect the MariaDB data node and only select the databases only.
6. Select to restore the MariaDB Databases to the alternate location and click Next to proceed.
 |
7. Confirm the MariaDB database details such as Database name, Host, Port, Username, and Password.
Example: To restore and clone a copy of the chinook database on the original server with new name chinook_clone.
 |
8. Confirm the temporary directory path is correct and then click Restore to proceed.
 |
9. After the MariaDB database(s) has been restored.
| |
10. Using MariaDB Command Line Client, you can list the restored databases and tables.
Example: Listing the tables in the restore cloned database using show tables
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | chinook | | chinook_clone | | information_schema | | mysql | | nation | | performance_schema | | sportdb | | test | +--------------------+ 8 rows in set (0.001 sec) MariaDB [(none)]> show tables in chinook_clone; +-------------------------+ | Tables_in_chinook_clone | +-------------------------+ | album | | artist | | customer | | employee | | genre | | invoice | | invoiceline | | mediatype | | playlist | | playlisttrack | | track | +-------------------------+ 11 rows in set (0.001 sec)
public/module/mariadb_database/restore.txt · Last modified: 2021/02/02 15:21 by ronnie.chan
