.::: Using MariaDB MySQL Show Binary Logs in, clause, limit, convert to sql base datetime & position :::.

 

1. show bin log event (in, from, limit)
MariaDB [(none)]> SHOW BINLOG EVENTS in 'mysql-bin.000027';
+------------------+------+-------------------+-----------+-------------+------------------------------------------------+
| Log_name         | Pos  | Event_type        | Server_id | End_log_pos | Info                                           |
+------------------+------+-------------------+-----------+-------------+------------------------------------------------+
| mysql-bin.000027 |    4 | Format_desc       |         1 |         256 | Server ver: 10.5.21-MariaDB-log, Binlog ver: 4 |
| mysql-bin.000027 |  256 | Gtid_list         |         1 |         299 | [1-1-138]                                      |
| mysql-bin.000027 |  299 | Binlog_checkpoint |         1 |         342 | mysql-bin.000027                               |
| mysql-bin.000027 |  342 | Gtid              |         1 |         384 | GTID 1-1-139                                   |
| mysql-bin.000027 |  384 | Query             |         1 |         485 | create database mariabinlog                    |
| mysql-bin.000027 |  485 | Gtid              |         1 |         527 | GTID 1-1-140                                   |
| mysql-bin.000027 |  527 | Query             |         1 |         610 | drop database tkl                              |
| mysql-bin.000027 |  610 | Gtid              |         1 |         652 | GTID 1-1-141                                   |
| mysql-bin.000027 |  652 | Query             |         1 |         735 | drop database tk2                              |
| mysql-bin.000027 |  735 | Gtid              |         1 |         777 | GTID 1-1-142                                   |
| mysql-bin.000027 |  777 | Query             |         1 |         860 | drop database tka                              |
| mysql-bin.000027 |  860 | Gtid              |         1 |         902 | GTID 1-1-143                                   |
| mysql-bin.000027 |  902 | Query             |         1 |         985 | drop database asd                              |
| mysql-bin.000027 |  985 | Gtid              |         1 |        1027 | GTID 1-1-144                                   |
| mysql-bin.000027 | 1027 | Query             |         1 |        1112 | drop database asde                             |
| mysql-bin.000027 | 1112 | Gtid              |         1 |        1154 | GTID 1-1-145                                   |
| mysql-bin.000027 | 1154 | Query             |         1 |        1241 | drop database asdet                            |
| mysql-bin.000027 | 1241 | Gtid              |         1 |        1283 | GTID 1-1-146                                   |
| mysql-bin.000027 | 1283 | Query             |         1 |        1390 | create database triallogbackup                 |
| mysql-bin.000027 | 1390 | Gtid              |         1 |        1432 | GTID 1-1-147                                   |
| mysql-bin.000027 | 1432 | Query             |         1 |        1533 | create database mafiabackup                    |
| mysql-bin.000027 | 1533 | Gtid              |         1 |        1575 | GTID 1-1-148                                   |
| mysql-bin.000027 | 1575 | Query             |         1 |        1656 | drop database ls                               |
+------------------+------+-------------------+-----------+-------------+------------------------------------------------+
23 rows in set (0.000 sec)
  

MariaDB [(none)]>

MariaDB [(none)]> SHOW BINLOG EVENTS in 'mysql-bin.000027' from 860;
+------------------+------+------------+-----------+-------------+--------------------------------+
| Log_name         | Pos  | Event_type | Server_id | End_log_pos | Info                           |
+------------------+------+------------+-----------+-------------+--------------------------------+
| mysql-bin.000027 |  860 | Gtid       |         1 |         902 | GTID 1-1-143                   |
| mysql-bin.000027 |  902 | Query      |         1 |         985 | drop database asd              |
| mysql-bin.000027 |  985 | Gtid       |         1 |        1027 | GTID 1-1-144                   |
| mysql-bin.000027 | 1027 | Query      |         1 |        1112 | drop database asde             |
| mysql-bin.000027 | 1112 | Gtid       |         1 |        1154 | GTID 1-1-145                   |
| mysql-bin.000027 | 1154 | Query      |         1 |        1241 | drop database asdet            |
| mysql-bin.000027 | 1241 | Gtid       |         1 |        1283 | GTID 1-1-146                   |
| mysql-bin.000027 | 1283 | Query      |         1 |        1390 | create database triallogbackup |
| mysql-bin.000027 | 1390 | Gtid       |         1 |        1432 | GTID 1-1-147                   |
| mysql-bin.000027 | 1432 | Query      |         1 |        1533 | create database mafiabackup    |
| mysql-bin.000027 | 1533 | Gtid       |         1 |        1575 | GTID 1-1-148                   |
| mysql-bin.000027 | 1575 | Query      |         1 |        1656 | drop database ls               |
+------------------+------+------------+-----------+-------------+--------------------------------+
12 rows in set (0.000 sec)

MariaDB [(none)]>

MariaDB [(none)]> SHOW BINLOG EVENTS in 'mysql-bin.000027' limit 6 ;
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| Log_name         | Pos | Event_type        | Server_id | End_log_pos | Info                                           |
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| mysql-bin.000027 |   4 | Format_desc       |         1 |         256 | Server ver: 10.5.21-MariaDB-log, Binlog ver: 4 |
| mysql-bin.000027 | 256 | Gtid_list         |         1 |         299 | [1-1-138]                                      |
| mysql-bin.000027 | 299 | Binlog_checkpoint |         1 |         342 | mysql-bin.000027                               |
| mysql-bin.000027 | 342 | Gtid              |         1 |         384 | GTID 1-1-139                                   |
| mysql-bin.000027 | 384 | Query             |         1 |         485 | create database mariabinlog                    |
| mysql-bin.000027 | 485 | Gtid              |         1 |         527 | GTID 1-1-140                                   |
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
6 rows in set (0.000 sec)

MariaDB [(none)]>
 

2. convert data base on start date time
-- backup incremental/transaction at 13 pm
mysqlbinlog --start-datetime="2023-10-23 13:00:00" --stop-datetime="2023-10-23 14:00:00" /var/lib/mysql/mysql-bin.000027 > /backup/backup_binlog_20231023-140000.sql;

 

3. convert data base on start position
-- backup position
mysqlbinlog --start-position="1112" --stop-position="1432" /var/lib/mysql/mysql-bin.000027 > /backup/backup_binlog_position_1112_1432.sql;