0

I have problem, I would like to use mysqlbinlog for the backup, I do not know what causes it but the backuped binlog seems to be 4KB behind the binlog. I made a small shell.

while [ $i -lt 30 ]
do
mysql -D almafa -e 'insert  into b values("0HHHHHH");commit;'
   echo 'NUMBER:'$i >>/tmp/fut.lof34
   ls -la /tmp/almafamariadb-bin.000034  >>/tmp/fut.lof34
   ls -la /var/lib/mysql/mariadb-bin.000034   >>/tmp/fut.lof34
   i=`expr $i + 1`
done
systemctl restart mariadb

the new binlog was mariadb-bin.000034 sync_binlog=1

result is:

...
NUMBER:17
-rw-r--r-- 1 root root 0 Feb  3 10:30 /tmp/almafamariadb-bin.000034
-rw-rw---- 1 mysql mysql 3476 Feb  3 10:31 /var/lib/mysql/mariadb-bin.000034
NUMBER:18
-rw-r--r-- 1 root root 0 Feb  3 10:30 /tmp/almafamariadb-bin.000034
-rw-rw---- 1 mysql mysql 3650 Feb  3 10:31 /var/lib/mysql/mariadb-bin.000034
NUMBER:19
-rw-r--r-- 1 root root 0 Feb  3 10:30 /tmp/almafamariadb-bin.000034
-rw-rw---- 1 mysql mysql 3824 Feb  3 10:31 /var/lib/mysql/mariadb-bin.000034
NUMBER:20
-rw-r--r-- 1 root root 0 Feb  3 10:30 /tmp/almafamariadb-bin.000034
-rw-rw---- 1 mysql mysql 3998 Feb  3 10:31 /var/lib/mysql/mariadb-bin.000034
NUMBER:21
-rw-r--r-- 1 root root 4096 Feb  3 10:31 /tmp/almafamariadb-bin.000034
-rw-rw---- 1 mysql mysql 4172 Feb  3 10:31 /var/lib/mysql/mariadb-bin.000034
NUMBER:22
1

Set sync_binlog = 1

When sync_binlog is set to 0 (default), the OS caches all writes to disk and writes in 4K batches. This is for speed. If set to 1, every database update will be immediately written to the binary log. This may cause a higher iops consumption.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.