Overview

Backup in MySQL is Engine Dependent, each agent has different possibilities. In that regard, the engines can have one, two or all three possible types of backups:

Hot: The only utility which can provide hot backup without affecting the end users is: IB BACKUP, any other way will corrupt the files. However ibbackup is part of the mysqlbackup utility is Enterprise, therefore paid.

Warm: Warm backup in InnoDB is achieved by using community edition utility: mysqldump with a READ LOCK. Therefore a transactions aren't possible for a particular table, database or entire server.

Cold: As the name stands, InnoDB and NDB require the entire server to be shut down in order to perform cold backup. Furthermore, the cold backup is physical since the server should be down.

In a nutshell, database engines support the following types of backups:

InnoDB

Hot backup: With mysqlbackup, ibbackup or percona backup
Warm: Via mysqldump
Cold: With OS Backup

MyISAM

Warm: Via mysqldump
Cold: Via OS Backup

NDB Cluster

Hot: Via NDB management node(ALWAYS DO BACKUP FROM THE Management node, since data nodes host only their own data)
Warm: Via mysqldump
Cold: Via OS Backup

CSV/Archive

Warm: Via mysqldump
Cold: Via OS Backup (or with shared lock + OS backup)

Let's get a little bit more details with InnoDB, although most of the procedures below, can be used for other engines as well.

As we already discussed online physical backup with InnoDB is possible only with Oracle Enterprise Backup, Percona Backup or ibbackup:

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:


[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)

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:

[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]#

We will produce logical backup of our database as follows:

[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]#

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:

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>

In order to restore the table via logical backup, you have simply execute the script generated in the previous step: Remember, this backup will meaningless for PIT or FULL recovery WITHOUT LOG position information

[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>

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

As we already discussed, warm backup is performed when the data is available to the end users, BUT only for reading. Warm backup is possible for all engines. Furthermore, it is mainly way of backup for MyISAM tables.

As we already discussed, MyISAM can ONLY do: Warm and Cold backup. Therefore, logical backup by mysqldump is normal way to do backup for MyISAM tables: Worm backup, or any kind of a backup, with mysql is good to be scripted. Apart from the flexibility which the script provides, it is important to also record the log position and lock the tables. Simple script for export can be created as follows:

[root@mysqlslave ~]# ./backup_mysql.sh mysql /backup/warmbackup02112018.bkp
Enter password:
The database which was backed up is: mysql
The dumpfile location is: /backup/warmbackup02112018.bkp
Enter password:
Finished export, and unlocked !
[root@mysqlslave ~]# ls -lart /backup/warmbackup02112018.bkp
-rw-r--r-- 1 root root 1106625 Feb 11 07:01 /backup/warmbackup02112018.bkp
[root@mysqlslave ~]#

Script can be found below:

[root@mysqlslave ~]# cat backup_mysql.sh
#!/bin/bash

DB=$1
DUMP_FILE=$2

# Lock the database and sleep in background task
mysql -uroot -p $DB -e "FLUSH TABLES WITH READ LOCK; DO SLEEP(3600);" &
sleep 3

echo "The database which was backed up is: $DB"
echo "The dumpfile location is: $DUMP_FILE"

# Export the database while it is locked
mysqldump -uroot -p --master-data=2 --opt $DB > $DUMP_FILE

# When finished, kill the previous background task to unlock
kill $! 2>/dev/null
wait $! 2>/dev/null

echo "Finished export, and unlocked !"

This script will record also the log position as comment (value of 2) in the backup file:

-- MySQL dump 10.13  Distrib 5.7.21, for Linux (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- 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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysqlslave-bin.000003', MASTER_LOG_POS=154;   <- This line is IMPORTANT

--
-- Table structure for table `columns_priv`
--

DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;

Physical Warm backup is mainly for tables on CSV and Archive engines. Simply by putting a read lock, we are able to take OS backup of the data files.

Cold backup requires the object (table, database) to be not accessible to the user. Therefore the locks has been released and no threads to the datafiles are present. Since there aren't any locks or threads, we are able to perform OS backup simply by cp, scp, tar and so on.

Cold backup cannot be logical, since logical requires the database to be running.

Cold backup is particularly easy to be taken, just remember to take your log position:

mysql> show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| mysqlslave-bin.000002 |      154 |              |                  |                   |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

After a position is taken, feel free to shutdown the server and archive the directory in different location:

[root@mysqlslave log]# service mysqld stop
Redirecting to /bin/systemctl stop mysqld.service
[root@mysqlslave log]# tar -zcvf /backup/mysqlbkp021118.tar.gz /mysqldata/
tar: Removing leading `/' from member names
/mysqldata/
/mysqldata/master.info
/mysqldata/ibdata1
/mysqldata/server-cert.pem
/mysqldata/sys/
/mysqldata/sys/x@0024latest_file_io.frm
/mysqldata/sys/io_global_by_wait_by_latency.frm
/mysqldata/sys/io_global_by_file_by_latency.frm
/mysqldata/sys/innodb_buffer_stats_by_schema.frm
/mysqldata/sys/user_summary_by_file_io.frm
/mysqldata/sys/innodb_buffer_stats_by_table.frm
/mysqldata/sys/sys_config_update_set_user.TRN
/mysqldata/sys/x@0024io_by_thread_by_latency.frm
/mysqldata/sys/user_summary_by_stages.frm
/mysqldata/sys/x@0024schema_table_lock_waits.frm
/mysqldata/sys/statements_with_sorting.frm
/mysqldata/sys/sys_config.TRG
/mysqldata/sys/host_summary_by_file_io_type.frm
/mysqldata/sys/ps_check_lost_instrumentation.frm
----------------------------------------------------------------
/mysqldata/mysqlslave-relay-bin.000001
/mysqldata/private_key.pem
/mysqldata/ib_logfile1
/mysqldata/server-key.pem
[root@mysqlslave log]# ls -lart /backup/
total 3720
dr-xr-xr-x. 19 root  root      275 Feb 11 04:18 ..
drwxr-xr-x   2 mysql mysql      53 Feb 11 04:59 .
-rw-r--r--   1 mysql  mysql  2697898 Feb 11 04:59 mysqlbkp021118.tar.gz <- Our backup
[root@mysqlslave log]#

Once you are done, restart the server:

[root@mysqlslave log]# service mysqld start
Redirecting to /bin/systemctl start mysqld.service
[root@mysqlslave log]# service mysqld status
Redirecting to /bin/systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2018-02-11 05:00:54 EST; 3s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 1364 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 1347 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 1368 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─1368 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Feb 11 05:00:53 mysqlslave systemd[1]: Starting MySQL Server...
Feb 11 05:00:54 mysqlslave systemd[1]: Started MySQL Server.
[root@mysqlslave log]#

Overview

InnoDB

MyISAM

NDB Cluster

CSV/Archive

Hot(Online)

Physical

Logical

Warm(Read-Only)

Logical Backup

Physical

Cold(Offline)

Logical

Physical

My wiki