Restore MariaDB from Backup []

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== 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.

<code>
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)]>
</code>
 
2. In the CloudBacko Pro's main interface, click the Restore icon.

{{public:module:mariadb_database:image118.png}}
 
3. Select the backup set that you would like to restore the MariaDB Database from.

| {{public:module:mariadb_database:image120.png?800}} |
 
4. Select the storage destination that contains the MariaDB databases that you would like to restore from. 

| {{public:module:mariadb_database:image122.png?800}} |
 
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.

| {{public:module:mariadb_database:image124.png?800}} |

<WRAP info>
To restore to either original or alternate location please unselect the MariaDB data node and only select the databases only.
</WRAP>

6. Select to restore the MariaDB Databases to the Original location and click **Next** to proceed.

| {{public:module:mariadb_database:image126.png?800}} |
 
7. Confirm the temporary directory path is correct and then click **Restore** to proceed.

| {{public:module:mariadb_database:image128.png?800}} |
 
8. After the MariaDB database(s) has been restored.

| {{public:module:mariadb_database:image130.png?800}} |
 
9. Using MariaDB Command Line Client, you can list the restored databases and tables.
Example: Listing the tables in the database using **show tables**

<code>
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)]>
</code>

===== 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.

{{public:module:mariadb_database:image118.png}}
 
2. Select the backup set that you would like to restore the MariaDB Database from.

| {{public:module:mariadb_database:image132.png?800}} |
 
3. Select the storage destination that contains the MariaDB databases that you would like to restore from.

| {{public:module:mariadb_database:image122.png?800}} |
 
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.

| {{public:module:mariadb_database:image134.png?800}} |

5. Select the location on the local machine you wish to restore the MariaDB database files to.  Click **Next** to proceed.  

| {{public:module:mariadb_database:image136.png?800}} |
 
6. Confirm the temporary directory path is correct and then click **Restore** to proceed.

| {{public:module:mariadb_database:image138.png?800}} |
 
7. After the MariaDB database(s) has been restored.

| {{public:module:mariadb_database:image140.png?800}} |
 
8. Check the location on the local machine to verify the MariaDB database files have been restored.

Example: Using Windows File Explorer

| {{public:module:mariadb_database:image142.png?800}} |


9. Recovering MariaDB Databases

<WRAP indent>

9.1. Login to MariaDB Server using MariaDB Command Line Client and verify the database instance is running.
<code>
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)]>
</code>

9.2. Create the database names that need to be recovered.

Example: chinook, nation, and sportdb.

<code>
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)
</code>

9.3. Recover Databases

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

<code>
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
</code>

9.4. Check the database status

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

<code>
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)
</code>
</WRAP>

===== 3). Automatic MariaDB Database Restore (Alternative Location) =====
----

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

<code>
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)]>
</code>

2. In the CloudBacko Pro's main interface, click the Restore icon.

| {{public:module:mariadb_database:image118.png}} | 
 
3. Select the backup set that you would like to restore the MariaDB Database from.

| {{public:module:mariadb_database:image146.png?800}} |
 
4. Select the storage destination that contains the MariaDB databases that you would like to restore from. 

| {{public:module:mariadb_database:image148.png?800}} |

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.

| {{public:module:mariadb_database:image150.png?800}} |
 
<WRAP info>
To restore to either original or alternate location please unselect the MariaDB data node and only select the databases only.
</WRAP>

6. Select to restore the MariaDB Databases to the alternate location and click **Next** to proceed.

| {{public:module:mariadb_database:image152.png?800}} |
 
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**.

| {{public:module:mariadb_database:image154.png?800}} |
 
8. Confirm the temporary directory path is correct and then click **Restore** to proceed.

| {{public:module:mariadb_database:image156.png?800}} |
 
9. After the MariaDB database(s) has been restored.

| {{public:module:mariadb_database:image140.png?800}} |
 
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**

<code>
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)
</code>
User Tools

Site Tools

Page Tools
