BACKUP AND RECOVERY IN MySQL (Article -17).

Share via:

Post Views: 0

BACKUP AND RECOVERY IN MySQL.

What is backup ?

Backup is nothing copying the data into another location .

Why we need backup ?

If you’re storing anything in MySQL databases that you do not want to lose, it is very important to make regular backups of your data to protect it from loss. This tutorial will show you two easy ways to backup and restore the data in your MySQL database. You can also use this process to move your data to a new web server.

In MySQL we have two types of backup’s. They are

1.Logical Backup’s

2.Physical backup(Raws)

LOGICAL BACKUPS :

Logical backups save information represented as logical database structure (CREATE DATABASE, CREATE TABLE statements) and content (INSERT statements or delimited-text files). This type of backup is suitable for smaller amounts of data where you might edit the data values or table structure, or recreate the data on a different machine architecture.

Logical backup methods have these characteristics:
The backup is done by querying the MySQL server to obtain database structure and content information.
Backup is slower than physical methods because the server must access database information and convert it to logical format. If the output is written on the client side, the server must also send it to the backup program.
Output is larger than for physical backup, particularly when saved in text format.
Backup and restore granularity is available at the server level (all databases), database level (all tables in a particular database), or table level. This is true regardless of storage engine.
The backup does not include log or configuration files, or other database-related files that are not part of databases.
Backups stored in logical format are machine independent and highly portable.
Logical backups are performed with the MySQL server running. The server is not taken offline.
Logical backup tools include the mysqldump program and the SELECT … INTO OUTFILE statement. These work for any storage engine, even MEMORY.
To restore logical backups, SQL-format dump files can be processed using the mysql client. To load delimited-text files, use the LOAD DATA INFILE statement or the mysqlimport client.

PHYSICAL BACKUPS :

Physical backups consist of raw copies of the directories and files that store database contents. This type of backup is suitable for large, important databases that need to be recovered quickly when problems occur.
The backup consists of exact copies of database directories and files. Typically this is a copy of all or part of the MySQL data directory.
Physical backup methods are faster than logical because they involve only file copying without conversion.
Output is more compact than for logical backup.
Because backup speed and compactness are important for busy, important databases, the MySQL Enterprise Backup product performs physical backups. For an overview of the MySQL
Backup and restore granularity ranges from the level of the entire data directory down to the level of individual files. This may or may not provide for table-level granularity, depending on storage engine. For example, InnoDB tables can each be in a separate file, or share file storage with other InnoDB tables; each MyISAM table corresponds uniquely to a set of files.
In addition to databases, the backup can include any related files such as log or configuration files.
Data from MEMORY tables is tricky to back up this way because their contents are not stored on disk. (The MySQL Enterprise Backup product has a feature where you can retrieve data from MEMORY tables during a backup.)
Backups are portable only to other machines that have identical or similar hardware characteristics.
Backups can be performed while the MySQL server is not running. If the server is running, it is necessary to perform appropriate locking so that the server does not change database contents during the backup. MySQL Enterprise Backup does this locking automatically for tables that require it.
Physical backup tools include the mysqlbackup of MySQL Enterprise Backup for InnoDB or any other tables, or file system-level commands (such as cp,scp, tar, rsync) for MyISAM tables.

In this article will cover Logical Backups.

In Logical backups we have two ways to export the data in MySQL.

1.mysqldump

2.SELECT … INTO OUTFILE

1.mysqldump

The mysqldump program is used to copy or back up tables and databases. It can write the table output either as a Raw Datafile or as a set of INSERTstatements that recreate the records in the table.

General syntax of mysqldump :

mysqldump [connection parameters]  [backup switches] > <backup.sql>

1	mysqldump [connection parameters] [backup switches] > <backup.sql>

To take remote connections using mysqldump

Syntax :

mysqldump -uroot -p -h 10.23.45.67 -p3308 -all-databases >Alldb.sql

1	mysqldump -uroot -p -h 10.23.45.67 -p3308 -all-databases >Alldb.sql

To check what options available in mysqldump.

[root@ip-172-31-21-159 ~]# mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

[root@ip-172-31-21-159 ~]# mysqldump

Usage: mysqldump [OPTIONS] database [tables]

OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

OR mysqldump [OPTIONS] --all-databases [OPTIONS]

Let’s check the databases we have

[root@ip-172-31-21-159 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.24-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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ctg                |
| mysql              |
| performance_schema |
| sys                |
| trg                |
| trg2               |
+--------------------+
7 rows in set (0.00 sec)

[root@ip-172-31-21-159 ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.7.24-log MySQL Community Server (GPL)

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 |

| ctg |

| mysql |

| performance_schema |

| sys |

| trg |

| trg2 |

+--------------------+

7 rows in set (0.00 sec)

Check objects available in trg database .

mysql> use trg;
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_trg |
+---------------+
| TRG           |
| test3         |
| trg           |
| tt            |
| tt1           |
+---------------+
5 rows in set (0.00 sec)

mysql> use trg;

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

+---------------+

| TRG |

| test3 |

| trg |

| tt |

| tt1 |

+---------------+

5 rows in set (0.00 sec)

Create a directory to store backup.

[root@ip-172-31-21-159 ~]# mkdir backups
[root@ip-172-31-21-159 ~]# cd backups/
[root@ip-172-31-21-159 backups]# ls -la
total 0
drwxr-xr-x. 2 root root   6 Nov 10 02:43 .
dr-xr-x---. 6 root root 284 Nov 10 02:43 ..

[root@ip-172-31-21-159 ~]# mkdir backups

[root@ip-172-31-21-159 ~]# cd backups/

[root@ip-172-31-21-159 backups]# ls -la

total 0

drwxr-xr-x. 2 root root 6 Nov 10 02:43 .

dr-xr-x---. 6 root root 284 Nov 10 02:43 ..

Now will take backup using mysqldump

mysqldump stores sql statements.

dr-xr-x---. 6 root root 284 Nov 10 02:43 ..
[root@ip-172-31-21-159 backups]# mysqldump -uroot -p trg > trg_09nov.sql
Enter password:
[root@ip-172-31-21-159 backups]# ll
total 4
-rw-r--r--. 1 root root 3844 Nov 10 02:44 trg_09nov.sql

dr-xr-x---. 6 root root 284 Nov 10 02:43 ..

[root@ip-172-31-21-159 backups]# mysqldump -uroot -p trg > trg_09nov.sql

Enter password:

[root@ip-172-31-21-159 backups]# ll

total 4

-rw-r--r--. 1 root root 3844 Nov 10 02:44 trg_09nov.sql

Now the backup has completed.Whenever we take backup using mysqldump it takes sql statements in alphabetical order.

Lets read the content under backup file.

[root@ip-172-31-21-159 backups]# view trg_09nov.sql

1	[root@ip-172-31-21-159 backups]# view trg_09nov.sql

For better understanding we can grep what table structure available in dumpfile.

[root@ip-172-31-21-159 backups]# grep "CREATE TABLE" trg_09nov.sql
CREATE TABLE `TRG` (
CREATE TABLE `test3` (
CREATE TABLE `trg` (
CREATE TABLE `tt` (
CREATE TABLE `tt1` (

[root@ip-172-31-21-159 backups]# grep "CREATE TABLE" trg_09nov.sql

CREATE TABLE `TRG` (

CREATE TABLE `test3` (

CREATE TABLE `trg` (

CREATE TABLE `tt` (

CREATE TABLE `tt1` (

If we want to take multiple databases we use -databases option.

[root@ip-172-31-21-159 backups]# mysqldump -uroot -p --databases trg trg2  > trg_trg2_9nov.sql
Enter password:
[root@ip-172-31-21-159 backups]# ll
total 12
-rw-r--r--. 1 root root 3844 Nov 10 02:55 trg_09nov.sql
-rw-r--r--. 1 root root 5201 Nov 10 03:03 trg_trg2_9nov.sql

[root@ip-172-31-21-159 backups]# mysqldump -uroot -p --databases trg trg2 > trg_trg2_9nov.sql

Enter password:

[root@ip-172-31-21-159 backups]# ll

total 12

-rw-r--r--. 1 root root 3844 Nov 10 02:55 trg_09nov.sql

-rw-r--r--. 1 root root 5201 Nov 10 03:03 trg_trg2_9nov.sql

Lets read the content inside the backup file.

Whenever we are taking backup of multiple databases using mysqldump it takes in alphabetical order.

Once the first database completed the only it will start second database backup.

If want to know whether backup was successfully completed or not we should check the last line in backup file .

If it shows DUMP COMPLETED ON DATE AND TIME then only your backup successfully completed.

If it doesn’t shows not completed .

Your mysqldump can failure due to network issues.

If we want to take all the databases backup we use -all option.

[root@ip-172-31-21-159 backups]# mysqldump -uroot -p --all-databases > Alldb.sql
Enter password:
[root@ip-172-31-21-159 backups]# ll
total 808
-rw-r--r--. 1 root root 813536 Nov 10 04:13 Alldb.sql
-rw-r--r--. 1 root root   3844 Nov 10 02:55 trg_09nov.sql
-rw-r--r--. 1 root root   5201 Nov 10 03:03 trg_trg2_9nov.sql

[root@ip-172-31-21-159 backups]# mysqldump -uroot -p --all-databases > Alldb.sql

Enter password:

[root@ip-172-31-21-159 backups]# ll

total 808

-rw-r--r--. 1 root root 813536 Nov 10 04:13 Alldb.sql

-rw-r--r--. 1 root root 3844 Nov 10 02:55 trg_09nov.sql

-rw-r--r--. 1 root root 5201 Nov 10 03:03 trg_trg2_9nov.sql

How to take single table backup ?

we use tablename followed by databasename .

[root@ip-172-31-21-159 backups]# mysqldump -uroot -p  trg test3 > trg_test3.sql
Enter password:
[root@ip-172-31-21-159 backups]# ll
total 20
-rw-r--r--. 1 root root 3509 Nov 10 04:15 Alldb.sql
-rw-r--r--. 1 root root 3844 Nov 10 02:55 trg_09nov.sql
-rw-r--r--. 1 root root 1789 Nov 10 04:23 trg_test3.sql
-rw-r--r--. 1 root root 5201 Nov 10 03:03 trg_trg2_9nov.sql

[root@ip-172-31-21-159 backups]# mysqldump -uroot -p trg test3 > trg_test3.sql

Enter password:

[root@ip-172-31-21-159 backups]# ll

total 20

-rw-r--r--. 1 root root 3509 Nov 10 04:15 Alldb.sql

-rw-r--r--. 1 root root 3844 Nov 10 02:55 trg_09nov.sql

-rw-r--r--. 1 root root 1789 Nov 10 04:23 trg_test3.sql

-rw-r--r--. 1 root root 5201 Nov 10 03:03 trg_trg2_9nov.sql

How to take only structure of a database ?

We have an option called –no-data.

[root@ip-172-31-21-159 backups]# mysqldump -uroot -p --no-data trg > trg.str
Enter password:
[root@ip-172-31-21-159 backups]# ll
total 24
-rw-r--r--. 1 root root 3509 Nov 10 04:15 Alldb.sql
-rw-r--r--. 1 root root 3844 Nov 10 02:55 trg_09nov.sql
-rw-r--r--. 1 root root 3352 Nov 10 04:50 trg.str
-rw-r--r--. 1 root root 1789 Nov 10 04:23 trg_test3.sql
-rw-r--r--. 1 root root 5201 Nov 10 03:03 trg_trg2_9nov.sql