BACKUP AND RECOVERY IN MySQL USING MYSQLDUMP UTILITY (Article -18).

Share via:

Post Views: 0

BACKUP AND RECOVERY IN MySQL USING MYSQLDUMP UTILITY.

Please Refer the previous article.

BACKUP AND RECOVERY IN MySQL (Article -17).

Using mysqldump utility we can take the backup of only data(ignoring structure )

Check the databases list.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ctg                |
| mysql              |
| performance_schema |
| sys                |
| trg                |
| trg2               |
+--------------------+
7 rows in set (0.00 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| ctg |

| mysql |

| performance_schema |

| sys |

| trg |

| trg2 |

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

7 rows in set (0.00 sec)

To take the backup of only data we use –no-create-info

[root@ip-172-31-21-159 backups]# mysqldump -uroot -p --no-create_info trg > trg_str.sql
Enter password:
[root@ip-172-31-21-159 backups]# ll
total 28
-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 2425 Nov 16 17:34 trg.str
-rw-r--r--. 1 root root 2425 Nov 16 17:35 trg_str.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 --no-create_info trg > trg_str.sql

Enter password:

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

total 28

-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 2425 Nov 16 17:34 trg.str

-rw-r--r--. 1 root root 2425 Nov 16 17:35 trg_str.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

Note :

When ever backup is started using mysqldump automatically the tables has been locked and data will be copied to sql file .

Once the backup backup completed locks has been released.

Sequentially tables has been locked when your trying to take multiple tables.

To know all the options in MySQLDUMP we can use help command.

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

1	[root@ip-172-31-21-159 backups]# mysqldump --help

Here will discuss few more options in mysqldump.

–single-transaction[InnoDB]

It’s only works for InnoDB Storage engines.

Creates a consistent snapshot by dumping all tables in a single transaction. Works ONLY for tables stored in storage engines which support multi-versioning

(currently only InnoDB does). The dump is NOT guaranteed to be consistent for other storage engines. While a –single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log position), no other connection should use the following statements: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, as consistent snapshot is not isolated from them. Option automatically turns off –lock-tables.

–master-data[=#]

This causes the binary log position and filename to be appended to the output. If equal to 1, will print it as a CHANGE MASTER command; if equal to 2, that command will be prefixed with a comment symbol. This option will turn –lock-all-tables on, unless –single-transaction is specified too (in which case a global read lock is only taken a short time at the beginning of the dump; don’t forget to read about –single-transaction below). In all cases, any action on logs will happen at the exact moment of the dump. Option automatically turns –lock-tables
off.

Note : When we use both single-transaction with master-data then tables will not be locked that means Transaction still running on server.

[root@ip-172-31-21-159 backups]# mysqldump -uroot -p --single-transaction  --master-data  trg >trg_single.sql
Enter password:

1 2	[root@ip-172-31-21-159 backups]# mysqldump -uroot -p --single-transaction --master-data trg >trg_single.sql Enter password:

Lets check the content inside the file.

[root@ip-172-31-21-159 backups]# ll
total 36
-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 4671 Nov 18 13:58 trg_single.sql
-rw-r--r--. 1 root root 2425 Nov 16 17:34 trg.str
-rw-r--r--. 1 root root 2425 Nov 16 17:35 trg_str.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

-- MySQL dump 10.13  Distrib 5.7.24, for Linux (x86_64)
--
-- Host: localhost    Database: trg
-- ------------------------------------------------------
-- Server version       5.7.24-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='binlog.000006', MASTER_LOG_POS=154;

--
-- Table structure for table `TRG`
--

DROP TABLE IF EXISTS `TRG`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `TRG` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
"trg_single.sql" 166L, 4671C
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `TRG`
--

LOCK TABLES `TRG` WRITE;
/*!40000 ALTER TABLE `TRG` DISABLE KEYS */;
/*!40000 ALTER TABLE `TRG` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `honey`
--

DROP TABLE IF EXISTS `honey`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `honey` (
  `Hallticket` int(11) DEFAULT NULL,
  `Name` varchar(10) DEFAULT NULL,
  `marks` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

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

total 36

-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 4671 Nov 18 13:58 trg_single.sql

-rw-r--r--. 1 root root 2425 Nov 16 17:34 trg.str

-rw-r--r--. 1 root root 2425 Nov 16 17:35 trg_str.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

-- MySQL dump 10.13 Distrib 5.7.24, for Linux (x86_64)

-- Host: localhost Database: trg

-- ------------------------------------------------------

-- Server version 5.7.24-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='binlog.000006', MASTER_LOG_POS=154;

-- Table structure for table `TRG`

DROP TABLE IF EXISTS `TRG`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `TRG` (

`id` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

"trg_single.sql" 166L, 4671C

/*!40101 SET character_set_client = @saved_cs_client */;

-- Dumping data for table `TRG`

LOCK TABLES `TRG` WRITE;

/*!40000 ALTER TABLE `TRG` DISABLE KEYS */;

/*!40000 ALTER TABLE `TRG` ENABLE KEYS */;

UNLOCK TABLES;

-- Table structure for table `honey`

DROP TABLE IF EXISTS `honey`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `honey` (

`Hallticket` int(11) DEFAULT NULL,

`Name` varchar(10) DEFAULT NULL,

`marks` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

In Above file we able to see

-- Position to start replication or point-in-time recovery from
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000006', MASTER_LOG_POS=154;

1 2	-- Position to start replication or point-in-time recovery from CHANGE MASTER TO MASTER_LOG_FILE='binlog.000006', MASTER_LOG_POS=154;

This means our backup is consistent upto above binary log file “binlog.000006”.

This backup will be useful when we setup slave server or replication or point in time recovery .

If we keep –master-data=2 then change master will be commented in output

[root@ip-172-31-21-159 backups]# mysqldump -uroot -p --single-transaction  --master-data=2  trg >trg_single.sql
Enter password:

1 2	[root@ip-172-31-21-159 backups]# mysqldump -uroot -p --single-transaction --master-data=2 trg >trg_single.sql Enter password:

Inside file change master will be commented

-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000006', MASTER_LOG_POS=154;

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

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000006', MASTER_LOG_POS=154;

Note :

By default mysqldump will not take the backup of triggers ,procedures, routines etc..,

In order to take complete snapshot of your system .

[root@ip-172-31-21-159 backups]# mysqldump -uroot -p --single-transaction --master-data=2  --routines --triggers      --all-databases  > ALLDB1.SQL
Enter password:

1 2	[root@ip-172-31-21-159 backups]# mysqldump -uroot -p --single-transaction --master-data=2 --routines --triggers --all-databases > ALLDB1.SQL Enter password: