This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
public:module:mysql_database:restore [2019/09/02 15:37] ronnie.chan created |
public:module:mysql_database:restore [2019/09/19 18:16] ronnie.chan [Manually recover the databases] |
||
---|---|---|---|
Line 1: | Line 1: | ||
====== Restore ====== | ====== Restore ====== | ||
+ | ---- | ||
There 3 restore options available for MySQL database: | There 3 restore options available for MySQL database: | ||
- **Original location** – CloudBacko Pro will restore the database(s) from the backup destination and apply them to the original production MySQL instance. | - **Original location** – CloudBacko Pro will restore the database(s) from the backup destination and apply them to the original production MySQL instance. | ||
Line 5: | Line 6: | ||
- **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 MySQL server on another machine for recovery. | - **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 MySQL server on another machine for recovery. | ||
- | ---- | + | {{INLINETOC}} \\ |
- | ===== 1. To Original Location ===== | + | ===== To Original Location ===== |
+ | ---- | ||
Restore files from your backup destination and automatically apply them to the MySQL database server in the original location. These are the steps. | Restore files from your backup destination and automatically apply them to the MySQL database server in the original location. These are the steps. | ||
Line 130: | Line 132: | ||
</code> | </code> | ||
+ | \\ | ||
+ | |||
+ | ===== To Alternate Location ===== | ||
---- | ---- | ||
- | |||
- | ===== 2. To Alternate Location ===== | ||
1. Login to MySQL Server using MySQL Command Line Client and verify the database instance is running. | 1. Login to MySQL Server using MySQL Command Line Client and verify the database instance is running. | ||
Line 212: | Line 215: | ||
mysql> | mysql> | ||
</code> | </code> | ||
+ | |||
+ | \\ | ||
+ | |||
+ | ===== Restore Raw Files (Manual Restore) ===== | ||
+ | ---- | ||
+ | Manual restore involves 2 steps: 1). Restore the MySQL databases from your storage destination to a location on disk, and 2). manually recover the databases. | ||
+ | |||
+ | <WRAP indent> | ||
+ | ==== Restore the MySQL databases from your storage destination to a location on disk ==== | ||
+ | ---- | ||
+ | |||
+ | 1. In the CloudBacko Pro main interface, click the Restore icon. \\ {{public:mysql-00d.png?200}} | ||
+ | |||
+ | 2. Select the backup set that you would like to restore the MySQL Database from. \\ {{public:mysql-27.png?640}} | ||
+ | |||
+ | 3. Select the storage destination that contains the MySQL databases that you would like to restore from. \\ {{public:mysql-28.png?640}} | ||
+ | |||
+ | 4. Select to restore the MySQL 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:mysql-29.png?640}} | ||
+ | |||
+ | 5. Select the location on the local machine you wish to restore the MySQL database files to. Click Next to proceed. \\ {{public:mysql-30.png?640}} | ||
+ | |||
+ | 6. Confirm the temporary directory path is correct and then click Restore to proceed. \\ {{public:mysql-31.png?640}} | ||
+ | |||
+ | 7. After the MySQL database(s) has been restored. \\ {{public:mysql-32.png?640}} | ||
+ | |||
+ | 8. Check the location on the local machine to verify the MySQL database files have been restored. | ||
+ | Example: Using Windows File Explorer \\ {{public:mysql-33.png?640}} | ||
+ | |||
+ | ==== Manually recover the databases ==== | ||
+ | ---- | ||
+ | |||
+ | 1. Login to MySQL Server using MySQL Command Line Client and verify the database instance is running. | ||
+ | <code> | ||
+ | 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 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 | | ||
+ | +--------------------+ | ||
+ | 3 rows in set (0.00 sec) | ||
+ | |||
+ | mysql> | ||
+ | </code> | ||
+ | |||
+ | 2. Create the database names that need to be recovered. | ||
+ | Example: classicmodels, sakila, and world. | ||
+ | <code> | ||
+ | mysql> create database classicmodels; | ||
+ | Query OK, 1 row affected (0.02 sec) | ||
+ | |||
+ | mysql> create database sakila; | ||
+ | Query OK, 1 row affected (0.00 sec) | ||
+ | |||
+ | mysql> create database world; | ||
+ | Query OK, 1 row affected (0.00 sec) | ||
+ | </code> | ||
+ | |||
+ | 3. Recover Databases - Repeat the following steps for all databases you wish to restore. | ||
+ | <code> | ||
+ | mysql> use classicmodels; | ||
+ | mysql> source d:\restored\MySQL\classicmodels.sql | ||
+ | Query OK, 0 rows affected (0.01 sec) | ||
+ | |||
+ | Query OK, 7 rows affected (0.00 sec) | ||
+ | Records: 7 Duplicates: 0 Warnings: 0 | ||
+ | |||
+ | Query OK, 110 rows affected (0.00 sec) | ||
+ | Records: 110 Duplicates: 0 Warnings: 0 | ||
+ | |||
+ | Query OK, 122 rows affected (0.00 sec) | ||
+ | Records: 122 Duplicates: 0 Warnings: 0 | ||
+ | |||
+ | mysql> use sakila; | ||
+ | mysql> source /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 /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) | ||
+ | </code> | ||
+ | |||
+ | 4. Check the database status | ||
+ | Example: Listing the tables in the database using show tables | ||
+ | <code> | ||
+ | mysql> show databases; | ||
+ | +--------------------+ | ||
+ | | Database | | ||
+ | +--------------------+ | ||
+ | | information_schema | | ||
+ | | classicmodels | | ||
+ | | mysql | | ||
+ | | performance_schema | | ||
+ | | sakila | | ||
+ | | world | | ||
+ | +--------------------+ | ||
+ | 7 rows in set (0.06 sec) | ||
+ | |||
+ | mysql> show tables in world; | ||
+ | +-----------------+ | ||
+ | | Tables_in_world | | ||
+ | +-----------------+ | ||
+ | | city | | ||
+ | | country | | ||
+ | | countrylanguage | | ||
+ | | departments | | ||
+ | | dept_emp | | ||
+ | | dept_manager | | ||
+ | | employees | | ||
+ | | salaries | | ||
+ | | titles | | ||
+ | +-----------------+ | ||
+ | 9 rows in set (0.00 sec) | ||
+ | |||
+ | mysql> show tables in classicmodels; | ||
+ | +----------------------------+ | ||
+ | | Tables_in_classicmodels | | ||
+ | +----------------------------+ | ||
+ | | actor | | ||
+ | | actor_info | | ||
+ | | address | | ||
+ | | category | | ||
+ | | city | | ||
+ | | country | | ||
+ | | countrylanguage | | ||
+ | | customer | | ||
+ | | customer_list | | ||
+ | | customers | | ||
+ | | departments | | ||
+ | | dept_emp | | ||
+ | | dept_manager | | ||
+ | | employees | | ||
+ | | film | | ||
+ | | film_actor | | ||
+ | | film_category | | ||
+ | | film_list | | ||
+ | | film_text | | ||
+ | | inventory | | ||
+ | | language | | ||
+ | | nicer_but_slower_film_list | | ||
+ | | offices | | ||
+ | | orderdetails | | ||
+ | | orders | | ||
+ | | payment | | ||
+ | | payments | | ||
+ | | productlines | | ||
+ | | products | | ||
+ | | rental | | ||
+ | | salaries | | ||
+ | | sales_by_film_category | | ||
+ | | sales_by_store | | ||
+ | | staff | | ||
+ | | staff_list | | ||
+ | | store | | ||
+ | | titles | | ||
+ | +----------------------------+ | ||
+ | 37 rows in set (0.00 sec) | ||
+ | </code> | ||
+ | </WRAP> |