=====Overview=====
As pretty much everything in MySQL, backup is also engine dependable. Even though there are tools, like mysqldump, which can perform backup regardless of the engine. However, bear in mind that the engine in MySQL STRONGLY affects many areas of the daily administration.
As we already explained, backups depends on backup type which was performed. As always there are 2 types of backups:

  * Physical - When we copy the actual data
  * Logical - When we generate script to re-create the data.

And then we have modes of each backup:

  * Online - When the data is fully available during backup.
  * Warm - When the data is available ONLY FOR READ during backup.
  * Cold - When the data is not available at all.

Different engines, have different backup options. For example, MyISAM cannot do online backups, since MyISAM isn't transnational engine anyway. So there isn't the concept of transactions. InnoDB has online physical backups, but only certain tools can do it, as we saw in the backup article.


=====Setup=====
Let's firstly create our database and table. We can also explicitly set the engine (in our case will be InnoDB) as we can see:

<sxh bash>

[root@mysqlmaster mysqldata]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> create database newDB;
Query OK, 1 row affected (0.24 sec)

mysql> use newDB;
Database changed
mysql>
mysql>
mysql>
mysql> create table newTable(id int, name varchar(20));
Query OK, 0 rows affected (0.21 sec)

mysql> drop table newTable;
Query OK, 0 rows affected (0.32 sec)

mysql>  create table newTable(id int, name varchar(20)) storage innodb;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'innodb' at line 1
mysql>   create table newTable(id int, name varchar(20)) engine=innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> show tables;
+-----------------+
| Tables_in_newDB |
+-----------------+
| newTable        |
+-----------------+
1 row in set (0.00 sec)

mysql> show table status where name='newTable'\G;
*************************** 1. row ***************************
           Name: newTable
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2018-06-04 08:05:06
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql>  insert into newTable values(1,'Julien');
Query OK, 1 row affected (0.08 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from newTable;
+------+--------+
| id   | name   |
+------+--------+
|    1 | Julien |
+------+--------+
1 row in set (0.00 sec)
</sxh>


At this stage, we have created a database with 1 table inside with 1 row inside, we can see the datafile associated with that table on the file system as well:


<sxh bash>
[root@mysqlmaster mysqldata]# cd newDB
[root@mysqlmaster newDB]# ls -alrt
total 120
-rw-r----- 1 mysql mysql    65 Jun  4 08:03 db.opt         <- Holds database structural data
-rw-r----- 1 mysql mysql  8586 Jun  4 08:05 newTable.frm   <- Holds table structural data
-rw-r----- 1 mysql mysql 98304 Jun  4 08:26 newTable.ibd   <- Holds table actual data
[root@mysqlmaster newDB]# pwd
/mysqldata/newDB                                           <- My Datadir
[root@mysqlmaster newDB]#
</sxh>

=====Logical Backup - Database=====
We will produce logical backup of our database as follows:

<sxh bash>
[root@mysqlmaster backup]# mysqldump -uroot -p newDB > newDBBackup.sql
Enter password:
[root@mysqlmaster backup]# ls -alrt
-rw-r--r--   1 root  root  1856 Jun  4 08:32 newDBBackup.sql
[root@mysqlmaster backup]# head newDBBackup.sql
-- MySQL dump 10.13  Distrib 5.7.21, for Linux (x86_64)
--
-- Host: localhost    Database: newDB
-- ------------------------------------------------------
-- Server version       5.7.21-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
[root@mysqlmaster backup]#
</sxh>

As we can see, the logical backup is nothing more than a SQL script. Once we have our logical backup, let's drop our table and re-create it:

<sxh bash>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| backup             |
| mysql              |
| newDB              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> use newDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql> show tables;
+-----------------+
| Tables_in_newDB |
+-----------------+
| newTable        |
+-----------------+
1 row in set (0.00 sec)

mysql> drop table newTable;
Query OK, 0 rows affected (0.22 sec)

mysql>
</sxh>


=====Logical Restore - Database=====
In order to restore the table via logical backup, you have simply execute the script generated in the previous step:


<sxh bash>
[root@mysqlmaster backup]# mysql -uroot -p newDB < newDBBackup.sql
Enter password:
[root@mysqlmaster backup]#
[root@mysqlmaster backup]#
[root@mysqlmaster backup]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> use newDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_newDB |
+-----------------+
| newTable        |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from newTable;
+------+--------+
| id   | name   |
+------+--------+
|    1 | Julien |
+------+--------+
1 row in set (0.00 sec)

mysql>
</sxh>

This will restore/recover all tables in the database, you can of course modify the script to restore a specific table.
Let's continue with Physical Backup/Restore now

=====Physical Backup - Database=====