The Ultimate Guide to High Availability with MariaDB

mysqlbinlog --read-from-remote-server --host=localhost mariadb-bin.000001 -p --raw --stop-never

Hello, I have an master /slave replication, and I would like to backup binlogs on the master server. mysqlbinlog --read-from-remote-server --host=localhost mariadb-bin.000001 -p --raw --stop-never --result-file=$(date +%F)

I would like if the binlog at the database and at backup to be in sync. I do not know , why the backuped binlog and database binlog differ in size and content.

MariaDB [mysql]> system ls -ltr /backup/binlog/@2021*mariadb-bin.000005 -rw-r--r--. 1 root root 16384 Jan 28 08:25 /backup/binlog/@2021-01-26mariadb-bin.000005 MariaDB [mysql]> system ls -ltr /var/lib/mysql/mariadb-bin.000005 -rw-rw----. 1 mysql mysql 17073 Jan 28 08:24 /var/lib/mysql/mariadb-bin.000005 MariaDB [mysql]> insert into almafa values('korte'); Query OK, 1 row affected (0.003 sec)

MariaDB [mysql]> insert into almafa values('korte'); ---->9 times Query OK, 1 row affected (0.003 sec)

MariaDB [mysql]> system ls -ltr /backup/binlog/@2021*mariadb-bin.000005 -rw-r--r--. 1 root root 16384 Jan 28 08:25 /backup/binlog/@2021-01-26mariadb-bin.000005 MariaDB [mysql]> system ls -ltr /var/lib/mysql/mariadb-bin.000005 -rw-rw----. 1 mysql mysql 18823 Jan 28 09:10 /var/lib/mysql/mariadb-bin.000005 MariaDB [mysql]> insert into almafa values('korte'); ------>9 times Query OK, 1 row affected (0.003 sec)

MariaDB [mysql]> system ls -ltr /backup/binlog/@2021*mariadb-bin.000005 -rw-r--r--. 1 root root 16384 Jan 28 08:25 /backup/binlog/@2021-01-26mariadb-bin.000005 MariaDB [mysql]> system ls -ltr /var/lib/mysql/mariadb-bin.000005 -rw-rw----. 1 mysql mysql 20398 Jan 28 09:10 /var/lib/mysql/mariadb-bin.000005 MariaDB [mysql]> insert into almafa values('korte'); ----->11 times Query OK, 1 row affected (0.003 sec)

MariaDB [mysql]> system ls -ltr /backup/binlog/@2021*mariadb-bin.000005 -rw-r--r--. 1 root root 16384 Jan 28 08:25 /backup/binlog/@2021-01-26mariadb-bin.000005 MariaDB [mysql]> system ls -ltr /var/lib/mysql/mariadb-bin.000005 -rw-rw----. 1 mysql mysql 22323 Jan 28 09:11 /var/lib/mysql/mariadb-bin.000005 MariaDB [mysql]> insert into almafa values('korte'); ----->9 times Query OK, 1 row affected (0.002 sec)

MariaDB [mysql]> system ls -ltr /backup/binlog/@2021*mariadb-bin.000005 -rw-r--r--. 1 root root 16384 Jan 28 08:25 /backup/binlog/@2021-01-26mariadb-bin.000005 MariaDB [mysql]> system ls -ltr /var/lib/mysql/mariadb-bin.000005 -rw-rw----. 1 mysql mysql 23898 Jan 28 09:12 /var/lib/mysql/mariadb-bin.000005 MariaDB [mysql]> insert into almafa values('korte'); ----->7 times Query OK, 1 row affected (0.002 sec)

MariaDB [mysql]> system ls -ltr /backup/binlog/@2021*mariadb-bin.000005 -rw-r--r--. 1 root root 24576 Jan 28 2021 /backup/binlog/@2021-01-26mariadb-bin.000005 MariaDB [mysql]> system ls -ltr /var/lib/mysql/mariadb-bin.000005 -rw-rw----. 1 mysql mysql 25123 Jan 28 09:12 /var/lib/mysql/mariadb-bin.000005 MariaDB [mysql]>

[mysql]# mysqlbinlog mariadb-bin.000005|tail -20

  1. 210128 9:12:33 server id 1 end_log_pos 24948 CRC32 0x4e467693 Xid = 278 COMMIT/*!*/;
  2. at 24948
  3. 210128 9:12:37 server id 1 end_log_pos 24990 CRC32 0xf96df3a5 GTID 0-1-606 trans /*!100001 SET @@session.gtid_seq_no=606**!*/; START TRANSACTION /*!*/;
  4. at 24990
  5. 210128 9:12:37 server id 1 end_log_pos 25092 CRC32 0x822fe2db Query thread_id=10 exec_time=0 error_code=0 SET TIMESTAMP=1611821557/*!*/; insert into almafa values('korte') /*!*/;
  6. at 25092
  7. 210128 9:12:37 server id 1 end_log_pos 25123 CRC32 0x1ca110db Xid = 279 COMMIT/*!*/; DELIMITER ;
  8. End of log file

[backup]# mysqlbinlog 2021-01-26mariadb-bin.000005|tail -20 ERROR: Error in Log_event::read_log_event(): 'Event truncated' ERROR: Could not read entry at offset 24567: Error in log format or read error. START TRANSACTION /*!*/;

  1. at 24290
  2. 210128 9:12:24 server id 1 end_log_pos 24392 CRC32 0xf2767a00 Query thread_id=10 exec_time=0 error_code=0 SET TIMESTAMP=1611821544/*!*/; insert into almafa values('korte') /*!*/;
  3. at 24392
  4. 210128 9:12:24 server id 1 end_log_pos 24423 CRC32 0x1390e641 Xid = 275 COMMIT/*!*/;
  5. at 24423
  6. 210128 9:12:27 server id 1 end_log_pos 24465 CRC32 0x5f32f79c GTID 0-1-603 trans /*!100001 SET @@session.gtid_seq_no=603**!*/; START TRANSACTION /*!*/;
  7. at 24465
  8. 210128 9:12:27 server id 1 end_log_pos 24567 CRC32 0xf05d3b40 Query thread_id=10 exec_time=0 error_code=0 SET TIMESTAMP=1611821547/*!*/; insert into almafa values('korte') /*!*/;

Thank you for your answere. Szilvia

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.