Backup MySQL database using mysqldump and Binary Logs.

How to Perform fullbackup /Incremental backup

MySQL Databases

This post discuss only MySQL Database Backup Using mysqldump (Logical backup) InnoDB Storage Engine

Its pretty Easy. Just execute the below command. Adjust the user as per your setup and you are done

mysqldump -u root -p --single-transaction --flush-logs --master-data=2 --all-databases > C:\mysql\backup\MySQL_Sunday_Full_Backup.sql

Enter password: *******


If you open the dump you will see something like this which indicates until what point your dump contains the data and if you need to perform point in time recovery in future from what point onward you need binary logs

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

Performing Incremental backup

Performing Incremental backup is even easier. Just flush the logs and secure them on a safe drive which you can use later as and when required.

To flush logs you can use

flush logs;
mysql> flush logs;
Query OK, 0 rows affected (0.07 sec)

 or
mysqladmin flush-logs
mysqladmin -u root -p flush-logs
Enter password: *******

copy the binarylogs to secure location using cp, copy or whatever applicable for your OS.


Additional Notes

How to identify the genarated binary log sequences
mysql> show master logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| bin_log.000001 |       177 |
| bin_log.000002 |       583 |
| bin_log.000003 |      1941 |
| bin_log.000004 |       154 |
| bin_log.000005 |       154 |
| bin_log.000006 |      1790 |
| bin_log.000007 |       177 |
| bin_log.000008 |      4407 |
| bin_log.000009 |      5574 |
| bin_log.000010 |       329 |
| bin_log.000011 |       177 |
| bin_log.000012 |       430 |
| bin_log.000013 |      2007 |
| bin_log.000014 |       177 |
| bin_log.000015 |      2284 |
| bin_log.000016 |       177 |
| bin_log.000017 |       199 |
| bin_log.000018 |       199 |
| bin_log.000019 |       199 |
| bin_log.000020 |       199 |
| bin_log.000021 |       199 |
| bin_log.000022 |       199 |
| bin_log.000023 |      1639 |
| bin_log.000024 |       199 |
| bin_log.000025 |       154 |
+----------------+-----------+
25 rows in set (0.01 sec)

How to identify the binary Log location

mysql> show variables like '%log_bin%';
+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| log_bin                         | ON                     |
| log_bin_basename                | C:\mysql\bin_log       |
| log_bin_index                   | C:\mysql\bin_log.index |
|
+---------------------------------+------------------------+
6 rows in set, 1 warning (0.00 sec)


See you next time with Next Post till then

Enjoy learning & Have Fun.....