.:: Set Up Incremental Backup & restore in MariaDB MySQL using enable Binary Logs & convert mysqlbinlog to sql :::.

A. Enable incremental backup
There are two types of backups - full and incremental backups.

A full backup has a collection of all MySQL queries.

An incremental backup strategy saves the data that has been modified since the previous backup.

Learn how to set up MySQL incremental backups with Mysqldump and Binary log.

1. Enable Binary Logging in /etc/my.cnf.d/server.cnf

#log-bin=/data/mysql-bin.log
log_bin = /var/lib/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_format = mixed

# Disabling binary logging

# --binlog-ignore-db=database_name or --binlog-do-db=database_name
# skip-log-bin

cek mariadb

MariaDB [(none)]> show global variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_compress                | OFF                            |
| log_bin_compress_min_len        | 256                            |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
7 rows in set (0.001 sec)

MariaDB [(none)]>

2. restart mariadb
systemctl restart mariadb

3. check
ls -l /var/lib/mysql/

[root@teguhth mysql]# ls
aria_log.00000001  ib_buffer_pool  ibtmp1             mysql             mysql_error.log  mysql_upgrade_info  server_audit.log  teguhth.pid
aria_log_control   ibdata1         incrementaldb      mysql-bin.000001  mysql_slow.log   performance_schema  sys
clasicdb           ib_logfile0     multi-master.info  mysql-bin.index   mysql.sock       secretdb            teguhth
[root@teguhth mysql]#

4. Create a Database and Table
CREATE DATABASE incrementaldb;

5. Create a Table
use incrementaldb

create table my_tbl(
my_id INT NOT NULL AUTO_INCREMENT,
my_field VARCHAR(100) NOT NULL,
submission_date DATE,
time_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY ( my_id )
);

6. add some row
use incrementaldb;
INSERT into my_tbl (my_field) VALUES ('val1');
INSERT into my_tbl (my_field) VALUES ('val2');
INSERT into my_tbl (my_field) VALUES ('val3');


MariaDB [(none)]> CREATE DATABASE incrementaldb;
Query OK, 1 row affected (0.003 sec)

MariaDB [(none)]> use incrementaldb
Database changed
MariaDB [incrementaldb]> create table my_tbl(
    -> my_id INT NOT NULL AUTO_INCREMENT,
    -> my_field VARCHAR(100) NOT NULL,
    -> submission_date DATE,
    -> time_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -> PRIMARY KEY ( my_id )
    -> );
Query OK, 0 rows affected (0.013 sec)

MariaDB [incrementaldb]> use incrementaldb;
Database changed
MariaDB [incrementaldb]> INSERT into my_tbl (my_field) VALUES ('val1');
Query OK, 1 row affected (0.039 sec)

MariaDB [incrementaldb]> INSERT into my_tbl (my_field) VALUES ('val2');
Query OK, 1 row affected (0.001 sec)

MariaDB [incrementaldb]> INSERT into my_tbl (my_field) VALUES ('val3');
Query OK, 1 row affected (0.001 sec)

MariaDB [incrementaldb]> select * from my_tbl;
+-------+----------+-----------------+---------------------+
| my_id | my_field | submission_date | time_created        |
+-------+----------+-----------------+---------------------+
|     1 | val1     | NULL            | 2023-09-06 13:45:35 |
|     2 | val2     | NULL            | 2023-09-06 13:45:35 |
|     3 | val3     | NULL            | 2023-09-06 13:45:35 |
+-------+----------+-----------------+---------------------+
3 rows in set (0.002 sec)

MariaDB [incrementaldb]>


7. Take a Full MySQL MySQL Backup
take a full backup of the current MySQL database. You can do it with the following command:
--flush-logs will close current logs (mysql-bin.000001). It creates a new one (mysql-bin.000002).

----mysqldump -uroot -p --all-databases --single-transaction --flush-logs --master-data=2 > full_backup.sql;
mysqldump -uroot -p incrementaldb --single-transaction --flush-logs --master-data=2 > full_backup.sql;

[root@teguhth data]# mysqldump -uroot -p incrementaldb --single-transaction --flush-logs --master-data=2 > full_backup.sql;
Enter password:
[root@teguhth data]# ls -l | grep full_backup
-rw-r--r-- 1 root root     2387 Sep  6 13:47 full_backup.sql
[root@teguhth data]#
tidak pakai --flushlog jg tidak apa2
mysqldump -uroot -p incrementaldb --single-transaction  > full_backup.sql;

mysqladmin -uroot -p flush-logs
8. check the new MySQL binary log fil
ls -l /var/lib/mysql/

[root@teguhth mysql]# ls
aria_log.00000001  ib_buffer_pool  ibtmp1             mysql             mysql_error.log  mysql_upgrade_info  server_audit.log  teguhth.pid
aria_log_control   ibdata1         incrementaldb      mysql-bin.000001  mysql_slow.log   performance_schema  sys
clasicdb           ib_logfile0     multi-master.info  mysql-bin.index   mysql.sock       secretdb            teguhth
[root@teguhth mysql]#

9. Now, all database changes will be writen in mysql-bin.000002 file.
use incrementaldb;
INSERT into my_tbl (my_field) VALUES ('val4');
INSERT into my_tbl (my_field) VALUES ('val5');
INSERT into my_tbl (my_field) VALUES ('val6');

MariaDB [(none)]> use incrementaldb;
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
MariaDB [incrementaldb]> INSERT into my_tbl (my_field) VALUES ('val4');
Query OK, 1 row affected (0.002 sec)

MariaDB [incrementaldb]> INSERT into my_tbl (my_field) VALUES ('val5');
Query OK, 1 row affected (0.002 sec)

MariaDB [incrementaldb]> INSERT into my_tbl (my_field) VALUES ('val6');
Query OK, 1 row affected (0.001 sec)

MariaDB [incrementaldb]> select *,@@hostname from my_tbl;
+-------+----------+-----------------+---------------------+------------+
| my_id | my_field | submission_date | time_created        | @@hostname |
+-------+----------+-----------------+---------------------+------------+
|     1 | val1     | NULL            | 2023-09-06 13:45:35 | teguhth    |
|     2 | val2     | NULL            | 2023-09-06 13:45:35 | teguhth    |
|     3 | val3     | NULL            | 2023-09-06 13:45:35 | teguhth    |
|     4 | val4     | NULL            | 2023-09-06 13:49:41 | teguhth    |
|     5 | val5     | NULL            | 2023-09-06 13:49:41 | teguhth    |
|     6 | val6     | NULL            | 2023-09-06 13:49:41 | teguhth    |
+-------+----------+-----------------+---------------------+------------+
6 rows in set (0.000 sec)

MariaDB [incrementaldb]>

10. Take an Incremental MySQL Backup
For incremental backups only, flush the binary log again and save binary logs created from the last full backup.

To flush the binary log, use the following command -

mysqladmin -uroot -p flush-logs
[root@teguhth data]# mysqladmin -uroot -p flush-logs
Enter password:
[root@teguhth data]#

ls -l /var/lib/mysql/
[root@teguhth mysql]# ls
aria_log.00000001  ib_buffer_pool  ibtmp1             mysql             mysql-bin.index  mysql.sock          secretdb          teguhth
aria_log_control   ibdata1         incrementaldb      mysql-bin.000001  mysql_error.log  mysql_upgrade_info  server_audit.log  teguhth.pid
clasicdb           ib_logfile0     multi-master.info  mysql-bin.000002  mysql_slow.log   performance_schema  sys
[root@teguhth mysql]#


11. You can also check the current state of the table.
select *,@@hostname from incrementaldb.my_tbl;
MariaDB [(none)]> select *,@@hostname from incrementaldb.my_tbl;
+-------+----------+-----------------+---------------------+------------+
| my_id | my_field | submission_date | time_created        | @@hostname |
+-------+----------+-----------------+---------------------+------------+
|     1 | val1     | NULL            | 2023-09-06 13:45:35 | teguhth    |
|     2 | val2     | NULL            | 2023-09-06 13:45:35 | teguhth    |
|     3 | val3     | NULL            | 2023-09-06 13:45:35 | teguhth    |
|     4 | val4     | NULL            | 2023-09-06 13:49:41 | teguhth    |
|     5 | val5     | NULL            | 2023-09-06 13:49:41 | teguhth    |
|     6 | val6     | NULL            | 2023-09-06 13:49:41 | teguhth    |
+-------+----------+-----------------+---------------------+------------+
6 rows in set (0.001 sec)

MariaDB [(none)]>

B. Testing incremental backup & restore
1. drop & create database
drop database incrementaldb;
create database incrementaldb;
use incrementaldb;
show tables;
select *,@@hostname from incrementaldb.my_tbl;

MariaDB [(none)]> drop database incrementaldb;
Query OK, 1 row affected (0.015 sec)

MariaDB [(none)]> create database incrementaldb;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> use incrementaldb
Database changed
MariaDB [incrementaldb]> show tables;
Empty set (0.000 sec)

MariaDB [incrementaldb]>

2. restore using full backup

mysql -u root -p incrementaldb < full_backup.sql
[root@teguhth data]# mysql -u root -p incrementaldb < full_backup.sql
Enter password:
[root@teguhth data]#

3. check
select *,@@hostname from incrementaldb.my_tbl;
MariaDB [incrementaldb]> select *,@@hostname from incrementaldb.my_tbl;
+-------+----------+-----------------+---------------------+------------+
| my_id | my_field | submission_date | time_created        | @@hostname |
+-------+----------+-----------------+---------------------+------------+
|     1 | val1     | NULL            | 2023-09-06 13:45:35 | teguhth    |
|     2 | val2     | NULL            | 2023-09-06 13:45:35 | teguhth    |
|     3 | val3     | NULL            | 2023-09-06 13:45:35 | teguhth    |
+-------+----------+-----------------+---------------------+------------+
3 rows in set (0.002 sec)

MariaDB [incrementaldb]>


4. restore using incremental backup
Next, you have to restore data from the binary log saved in the mysql-bin.000002 file.

Run the following command to restore the incremental backup.
mysqlbinlog --database=incrementaldb /var/lib/mysql/mysql-bin.000002  | sed '/^use / s/^/# /' | mysql -uroot -p incrementaldb

[root@teguhth data]# mysqlbinlog /var/lib/mysql/mysql-bin.000002 | mysql -uroot -p incrementaldb
Enter password:
[root@teguhth data]#

5. alternative convert from mysqlbinlog to sql

---backup
mysqlbinlog --database=incrementaldb /var/lib/mysql/mysql-bin.000002 | sed '/^use / s/^/# /' > /mysqlbinlog/backup/mysql-bin002.sql

--- restore
mysql -uroot -p incrementaldb < /mysqlbinlog/backup/mysql-bin002.sql

example

[root@teguhth mysql]# pwd
/var/lib/mysql
[root@teguhth mysql]# ls
aria_log.00000001  clasicdb        ib_logfile0    multi-master.info  mysql-bin.index  mysql_slow.log      performance_schema  sys          tka
aria_log_control   ib_buffer_pool  ibtmp1         mysql              mysql-bin.state  mysql.sock          secretdb            teguhth
blocking_example   ibdata1         incrementaldb  mysql-bin.000002   mysql_error.log  mysql_upgrade_info  server_audit.log    teguhth.pid
[root@teguhth mysql]#
[root@teguhth mysql]# mysqlbinlog /var/lib/mysql/mysql-bin.000002 > /mysqlbinlog/backup/mysql-bin002.sql
[root@teguhth mysql]#
[root@teguhth mysql]# cd /mysqlbinlog/backup/
[root@teguhth backup]# ls
mysql-bin002.sql
[root@teguhth backup]# mysql -uroot -p < /mysqlbinlog/backup/mysql-bin002.sql
Enter password:
[root@teguhth backup]#
6. check
select * from incrementaldb.my_tbl;

MariaDB [incrementaldb]> drop database incrementaldb;
Query OK, 1 row affected (0.006 sec)

MariaDB [(none)]> create database incrementaldb;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use incrementaldb;
Database changed
MariaDB [incrementaldb]> show tables;
Empty set (0.000 sec)

MariaDB [incrementaldb]> select *,@@hostname from incrementaldb.my_tbl;
ERROR 1146 (42S02): Table 'incrementaldb.my_tbl' doesn't exist
MariaDB [incrementaldb]> select *,@@hostname from incrementaldb.my_tbl;
+-------+----------+-----------------+---------------------+------------+
| my_id | my_field | submission_date | time_created        | @@hostname |
+-------+----------+-----------------+---------------------+------------+
|     1 | val1     | NULL            | 2023-09-06 13:45:35 | teguhth    |
|     2 | val2     | NULL            | 2023-09-06 13:45:35 | teguhth    |
|     3 | val3     | NULL            | 2023-09-06 13:45:35 | teguhth    |
+-------+----------+-----------------+---------------------+------------+
3 rows in set (0.001 sec)

MariaDB [incrementaldb]> select *,@@hostname from incrementaldb.my_tbl;
+-------+----------+-----------------+---------------------+------------+
| my_id | my_field | submission_date | time_created        | @@hostname |
+-------+----------+-----------------+---------------------+------------+
|     1 | val1     | NULL            | 2023-09-06 13:45:35 | teguhth    |
|     2 | val2     | NULL            | 2023-09-06 13:45:35 | teguhth    |
|     3 | val3     | NULL            | 2023-09-06 13:45:35 | teguhth    |
|     4 | val4     | NULL            | 2023-09-06 14:20:07 | teguhth    |
|     5 | val5     | NULL            | 2023-09-06 14:20:07 | teguhth    |
|     6 | val6     | NULL            | 2023-09-06 14:20:07 | teguhth    |
+-------+----------+-----------------+---------------------+------------+
6 rows in set (0.000 sec)

MariaDB [incrementaldb]>


C. Testing incremental backup & restore only using incremental backup
1. drop & create database
drop database incrementaldb;
create database incrementaldb;
use incrementaldb;
show tables;
CREATE TABLE `my_tbl` (
  `my_id` int(11) NOT NULL AUTO_INCREMENT,
  `my_field` varchar(100) NOT NULL,
  `submission_date` date DEFAULT NULL,
  `time_created` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`my_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
select *,@@hostname from incrementaldb.my_tbl;

2. restore using only incremental backup not need full backup
mysqlbinlog /var/lib/mysql/mysql-bin.000002 | mysql -uroot -p incrementaldb

[root@teguhth data]# mysqlbinlog /var/lib/mysql/mysql-bin.000002 | mysql -uroot -p incrementaldb
Enter password:
[root@teguhth data]#

 

---backup
mysqlbinlog --database=incrementaldb /var/lib/mysql/mysql-bin.000002 | sed '/^use / s/^/# /' > /mysqlbinlog/backup/mysql-bin002.sql

--- restore
mysql -uroot -p incrementaldb < /mysqlbinlog/backup/mysql-bin002.sql

3. check
select *,@@hostname from incrementaldb.my_tbl;
MariaDB [incrementaldb]> select *,@@hostname from incrementaldb.my_tbl;
Empty set (0.002 sec)

MariaDB [incrementaldb]> select *,@@hostname from incrementaldb.my_tbl;
+-------+----------+-----------------+---------------------+------------+
| my_id | my_field | submission_date | time_created        | @@hostname |
+-------+----------+-----------------+---------------------+------------+
|     4 | val4     | NULL            | 2023-09-06 14:20:07 | teguhth    |
|     5 | val5     | NULL            | 2023-09-06 14:20:07 | teguhth    |
|     6 | val6     | NULL            | 2023-09-06 14:20:07 | teguhth    |
+-------+----------+-----------------+---------------------+------------+
3 rows in set (0.000 sec)

MariaDB [incrementaldb]>