Questions tagged [binlog]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
0 answers
6 views

How to view execution logs in PolarDB?

Database table recovery attempted at a per-second granularity, but data restoration was unsuccessful. Given that the instance does not have binlog or SQL insight enabled, how can the logs of ...
0 votes
0 answers
12 views

AWS RDS `show binary logs` consume too much CPU [closed]

We have Mysql Aurora RDS and we have a few DMS. SHOW BINARY LOGS consume like 9 CPU, how can i reduce this ? Can i optimize the Database? Is normal to have so big CPU ?
1 vote
1 answer
287 views

Write latency for adding an index on read replica

This is a question that I've not been able to find any good documentation for on the internet. I think it's a common industry standard that for tables with heavy writes, you add an index on the read ...
2 votes
1 answer
619 views

sync_binlog different for master and slave

Is it fine if sync_binlog is different for master and slave? For my system I can see master has sync_binlog=1 and slave has sync_binlog=0.
0 votes
1 answer
1k views

Unable to show binary logs how to remove logs from mysql?

Server version: 5.1.50-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show binary logs ; PROBLEM Data directory is full I want to remove ...
1 vote
1 answer
472 views

How to change directory of binlogs on Ubuntu 18.04

Current config: [mysqld] datadir=/data/mysql log-bin=test Updated config: [mysqld] datadir=/data/mysql log-bin=/binlogdata/test log-bin-index=/binlogdata/test.index What I did was: Stop mysql ...
4 votes
1 answer
644 views

mysql replication missed quite a lot SQL statement

I have set up a master/slave with mysql-5.1.73 The master's binlog format is "statement". The master and slave seemed running very well with slave status: Slave_IO_Running: Yes ...
0 votes
2 answers
1k views

Replication error from Mariadb 10.1 to Mysql 5.1/5.0/5/5 when master's logging format is set to row based

While replicating from Mariadb 10.1 to MySQL (5.0, 5.1, 5.5) or Mariadb (5.2, 5.5) lower versions, if master's binlog_format is set to row, the replication failure occurs with the following message ...
147 votes
4 answers
245k views

Is it safe to delete mysql-bin files?

I have MM Replication in mysql, and I want to squeeze some free space in the box be deleting unnecessary files, I came across these mysql-bin files inside /var/db/mysql/ There are hundreds of those ...
0 votes
1 answer
103 views

io.debezium.DebeziumException: Client requested master to start replication from impossible position

I am using Kafka and Debezium to connect to a MariaDB I have a problem where in the event the MariaDB loses power and shutsdown abruptly, on restart Debezium is no longer able to connect I have this ...
0 votes
0 answers
32 views

When is INDEX included in Row-Based Binary Log using "full-image"

MariaDB Config version: > mysql -V mysql Ver 15.1 Distrib 10.4.27-MariaDB, for Win64 (AMD64), source revision 0946c99e7d6f7ac9dfcf3e60dae6ae85161d5ef2 my.ini: [mysqld] log-bin=bin.log log-bin-index=...
4 votes
2 answers
722 views

max_binlog_files on MySQL Server?

I read recently about a setting in Percona Server called max_binlog_files which restricts the total number of binlog files. This is exactly what I need. My binlog_expire_logs_seconds is currently ...
0 votes
1 answer
44 views

Mariabackup flush and delete bin log

I'm using Mariabackup and I'd like to know if there is an option to allow me to rotate the binary log when running mariabackup. I can't seem to find anything in the documentation.
2 votes
1 answer
36 views

Is it possible that binlog thread fails?

I was reading about how to create a primary-replica system and I want to do it in production. But based on this link, I realized that binlog thread could fail and data would be lost. I didn't find ...
0 votes
0 answers
35 views

Applying MySQL binlogs that are too large

I am trying to recover a MySQL crash. I have data as of Monday and binlogs since then, and want to apply the binlogs as the combination would be a complete set of the data. The trouble is that binlogs ...
6 votes
2 answers
9k views

Binlog has bad magic number

I keep getting this error whenever I start MySQL. 121028 1:38:55 [Note] Plugin 'FEDERATED' is disabled. 121028 1:38:55 InnoDB: The InnoDB memory heap is disabled 121028 1:38:55 InnoDB: Mutexes and ...
6 votes
2 answers
23k views

MySQL - Binary Log File size growing more than the maximum limit

I have a MySQL 5.7 running on a Centos 6. I enabled Binary Logging and didn't provide any custom value for the maximum size of Binary Log in my configuration file. By default, the parameter ...
0 votes
0 answers
23 views

Last GTID on replica matches source but data is not there

We have a following set up: Multi A-Z source RDS instance (MySQL 8.0.32) Single #1 A-Z replica RDS instance (MySQL 8.0.32) Single #2 A-Z replica RDS instance (MySQL 8.0.32) ProxySQL (2.5.2) Binlog ...
0 votes
0 answers
157 views

enable Mysql Binlog for only specific tables

I am using Mysql Binlogs to replicate data into a DataWarehouse. I have many small Mysql Instances and I will be enabling Binlogs to all of those DBs and uses Nifi to collect changes and populate/...
0 votes
1 answer
231 views

recovery with binlogs?

I would like to know if there is a way that I can restore my databases, I don't have a replication and I would like to migrate my databases without downtime or losing data. So with binlogs could I do ...
3 votes
2 answers
1k views

mysql bin logs expiry to not to fill up disk space

I am using 5.7.37-log MySQL Community Server. my mysqld.cnf looks below: server_id = 11 log_bin = bin.log log-bin-index = bin-log.index binlog_format = row max_binlog_size = 100M socket = mysql.sock ...
0 votes
0 answers
230 views

Foreign key constraints fails even if I disable FOREIGN_KEY_CHECK in MySQL

I'm working with MySQL v8.0.16. I have a binlog file from MySQL server A that I want to load in my MySQL server B. Server B has the same schemas as A, but not the data. When I attempt to load the ...
3 votes
1 answer
3k views

How can I remove binlog files after disabling bin-logging in mysql 8

In mysql 8, what is a recommended and safe way of removing old bin logs other than with "PURGE BINARY LOGS..." After disabling binary logging on mysql (with skip-log-bin) I have 500GB of ...
1 vote
1 answer
54 views

How to identify binlog_format for a specific query?

I have binlog_format set to MIXED and it states that mariadb will decide whether to use ROW or STATEMENT based logging for each query. Where can I see that information for a specific query?
1 vote
2 answers
183 views

Using binlog format row, is there any possibility to omit the statement text itself, and only having the binary part?

I have set the following in MariaDB: binlog-format=row binlog_row_image=MINIMAL # probably out of topic log_bin_compress # probably out of topic When I use mysqlbinlog I see both the #Q> INSERT ...
1 vote
3 answers
368 views

MariaDB column compression and binlog

I have a table with big text column, approx 3M per row, the table is using InnoDB storage engine. The binlog size is an issue, I have binlog turned on and configured to format:row. I do know about ...
1 vote
1 answer
168 views

MariaDB / (MySQL ?) point in time recovery

I am trying to get know the exact workflow of point in time recovery, or more general using the binary logs for backup/restore (meaning: using binary log files with mysqlbinlog) This is important not ...
0 votes
0 answers
294 views

MySQL apply --base64-output=decode-rows to MYSQL servers

I have decoded some of my binary logs using the following command. mysqlbinlog mysql-bin-changelog.003495 --base64-output=DECODE-ROWS --verbose --verbose --verbose > sqlfile This has the content ...
2 votes
2 answers
1k views

Suddenly a massive amount of binlog files

I've seen a sudden increase of binlog files, very large in number and size, in all three nodes of a Percona XtraDB cluster: -rw-rw---- 1 mysql mysql 1,1G 19 oct. 16:11 binlog-32.000001 -rw-rw---- ...
1 vote
1 answer
121 views

Mysql point in time restore using event position from 100 binary log files

i stuck on one question! the question is that i hava a large database in production environment and i have a full, incremental backup like till 2pm and aftre 1 hour i suddenly run the "drop ...
2 votes
2 answers
1k views

MariaDB replication failure: Fatal error 1236 - binlog shows 'Event truncated'

Disclaimer: I am not a certified DBA I have configured MariaDB Master - Slave replication and everything was working but now have an error that I cannot get past. I have run the following on the ...
1 vote
0 answers
127 views

Different size binlogs of galera-cluster nodes

I have galera-cluster with (HA) Keepalived. I noticed that node receiving the connection from the applications has a smaller binlog. On a node that accepts connections from applications: START ...
4 votes
1 answer
251 views

MySQL replication setup based on an LVM snapshot – am I forgetting anything?

I'm tasked with starting a brand new replica of a client's MySQL 5.7.35 database server. While I've observed others doing this in the past in various circumstances, I haven't been in charge of this ...
3 votes
1 answer
3k views

Can it be safe to disable binary logging in MySQL?

Is the MySQL binlog similar to Oracle's redo log or PostgreSQL's Write-Ahead Log? If yes, how come it be possible to disable bin logging? If the machine crashes while the data files are being updated, ...
0 votes
1 answer
273 views

Does deleting multiple rows in one statement reduce the size of the binlog

I need to remove a large amount of data, but need to do so without overwhelming the amount of data in the binlog. From the binlog's perspective are the following 2 equivalent? delete from table ...
1 vote
1 answer
192 views

MySQL store current binary log in different folder from past binary logs`

Is there any way to do this? Specify a different directory for currently active binary log. (It would nice if possible, because then we could store past binary logs in a compressed file system ...
5 votes
2 answers
4k views

pt-table-checksum and row-based replication

The set up I work with is MySQL 5.5 in a master-slave replication configuration, but we have to use "MIXED" replication (statement and row-based) because we use an application that requires it, and it ...
0 votes
1 answer
492 views

do_table or ignore_table alternatives on MariaDB Galera Multi-Master

As far as I can see, there are no do_table or ignore_table configs in MariaDB Galera multi-master (master-master) to skip or ignore specific tables. And there are only binlog_do_db and ...
1 vote
2 answers
2k views

Risks of sync_binlog = 0 for mysql

I'm trying to understand the risks of sync_binlog=0 for MySQL. We do not have a slave database, and, if we lost a second or two's (or even a minute or twos) transactions in the case of a catastrophic ...
0 votes
1 answer
276 views

Is it ok if I use only the latest binary log file when restoring MySQL db?

mysqlbinlog --verbose --stop-datetime="2019-12-28 10:00:00" bin.000009 > backup.sql mysql -u root -p < backup.sql (The stop-datetime parameter here is just for testing) I did the recovery ...
72 votes
6 answers
219k views

Disable MySQL binary logging with log_bin variable

Default MySQL config file /etc/mysql/my.cnf installed by some debian package using APT often set log_bin variable, so binlog are enabled: log_bin = /var/log/mysql/mysql-bin.log When I want to ...
5 votes
1 answer
12k views

MySQL Exec_Master_Log_Pos value greater than Read_Master_Log_Pos

I am trying to repair one MySQL replication setup. One of the database's table at slave side is deleted and the updates from master side are received but because of missing table these updates are not ...
9 votes
2 answers
25k views

How do I make binlog-format=ROW the start up default in the /etc/my.cnf file?

I can run the query SET GLOBAL binlog_format = 'ROW' and it works for that session. How do I make this setting stick after stopping and restarting MySQL? NOTE: I am on version 5.5.19 of MySQL (...
30 votes
2 answers
53k views

What is the safest way to switch the binlog format at runtime?

Because of the following warning in mysqld.log: [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it ...
1 vote
2 answers
9k views

Mysql binlogs exists, show binary logs is empty

I am working on a replication setup and have turned on bin-logs. I have moved my mysql datadir to a new location as well as the binlog path From my.cnf datadir = /vol/data/mysql log-bin=/vol/data/...
2 votes
1 answer
933 views

what happens If I remove mysql-bin.index file?

Because of the file storage full, we are removing mysql bin log files. Accidentally, we removed mysql-bin.index file. What happens if I remove mysql-bin.index file? These below are my DBMS info. Thank ...
1 vote
0 answers
519 views

How to extract binlog related to a single table in MySQL from binlog file?

I am using MySQL 5.7.31 and binlogs enabled in MIXED format. I am trying to perform a partial PIT Recovery, i.e. restoring specific table in a specific instant of the past. After restoring my last ...
2 votes
3 answers
3k views

Can I disable binlogs to save space temporarily

I am using percona mysql 8. I do not use any type of replication, but I read that binlogs are useful for data recovery. I would like to turn of binlogging and flush them while I run pt-online-schema-...
0 votes
0 answers
7k views

How to avoid the error "read ECONNRESET"?

I'm building an API that tracks binlog files in order to notify users of some specific changes on database. My max_binlog_size is set to 1048895 (1M) and after some time running the follow error is ...
0 votes
2 answers
4k views

Change the binlog format MIXED to ROW

I have a database that is running on AWS RDS (MySQl 5.7.22). Currently, I have one master and 2 replicas running. My binlog format is MIXED. I have plans to change it to ROW based. I want to ...