Questions tagged [binlog]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
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 ...
user avatar
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 ...
Nicolas Payart's user avatar
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 ...
quanta's user avatar
  • 1,006
22 votes
1 answer
41k views

How soon after updating expire_logs_days param and restarting sql will old binlogs get deleted?

MySQL 5.1.x | InnoDB | Windows My mysql data directory is starting to fill up with bin logs. I currently have the following settings configured in my windows mysql server: [mysqld] log-bin server-...
Mike B's user avatar
  • 607
16 votes
5 answers
52k views

Why do the MySQL bin log files still exist after a purge or flush?

I've used PURGE BINARY LOGS as well as FLUSH LOGS, but the mysql directory still contains these files: mysql-bin.000025 mysql-bin.000024 mysql-bin.000023 mysql-bin.000022 mysql-bin.000021 mysql-bin....
lamp_scaler's user avatar
10 votes
2 answers
14k views

MySQL binary logs - skip a table

Is there a way to skip a specific table in the binary logs? I've got binary logs configured as my incremental backup strategy between nightly backups. But we have one huge reference table that we're ...
David Parks's user avatar
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 (...
Constance's user avatar
7 votes
2 answers
20k views

enabling BINARY LOG on AWS RDS MySQL

Context I am trying to enable BINARY LOGs on a MySQL RDS instance. Issue I used CALL mysql.rds_set_configuration('binlog retention hours', 24) without encountering an error. And using CALL mysql....
Hugo's user avatar
  • 171
6 votes
3 answers
14k views

Maximum overall size of MariaDB binary logs

I am looking for a way to prevent MariaDB binary logs from taking a huge size when they grow faster than usual. variable max_binlog_size set the maximum size of ONE binary logfile. variable ...
Nicolas Payart's user avatar
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 ...
Yashwanth Aluru's user avatar
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 ...
laggingreflex's user avatar
6 votes
3 answers
6k views

MySQL replication slave hangs after encountering SET @@SESSION.GTID_NEXT= 'ANONYMOUS';

I recently installed two identical default installations of MySQL 5.7 under Ubuntu Server 16.04, and configured them to do a binary log replication. Until now this has been working fine, but suddenly ...
mauritslamers's user avatar
6 votes
3 answers
2k views

In MySQL, how exactly does data flows from query to disk?

I would like to have clarity on how exactly data flows from the the time of firing a query till it goes to disk. (I have rough idea it will go to buffers, then to redo logs then disk files) but then ...
AnonDude's user avatar
5 votes
2 answers
5k views

MySQL : 30GB of binlogs in 6 Hours

I have a mysql master/master setup with tungsten replicator. My server is reaching out of space, occupied around 30G in 6 hours. I'm not sure how to handle this. Would be great if some one can help me ...
Swaroop Kundeti's user avatar
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 ...
V R Murthy Korimilli's user avatar
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 ...
dmgig's user avatar
  • 281
5 votes
1 answer
2k views

MySQL v5.1.73 - can the binlog_format betweem Master & slave be different?

We are in the process of testing MySQL replication before using it in production. The MySQL is being setup by Puppet in our case and I noticed that the binlog_format setting is different between ...
Sam's user avatar
  • 51
4 votes
2 answers
19k views

Mysql FLUSH LOGS in live server with replication

I need to run the slow query and unindexed query logging for some time on a live production db with replication. Changing GLOBAL const value sounds like a good solution. This article also provides ...
Rajat Singhal's user avatar
4 votes
2 answers
2k views

Is it safe to disable sync_binlogs, innodb_flush_log_at_trx_commit and innodb_doublewrite on a site-redundant galera cluster?

On a site-redundant galera cluster, a commit should only return after a quorum of database nodes have accepted the transaction. If one database node goes down, all commits will be preserved by the ...
tobixen's user avatar
  • 211
4 votes
2 answers
738 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 ...
Eaten by a Grue's user avatar
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 ...
JK Laiho's user avatar
  • 145
4 votes
1 answer
1k views

MySQL replication: purge_logs giving error

On a MySQL replication instance I have this error message: [ERROR] MYSQL_BIN_LOG :: purge_logs Was called Expired with file / db-space-no-connected-cpa / DBMS / dxnc6sla / arch / not listed in the ...
DANI's user avatar
  • 41
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 ...
welkinwalker's user avatar
3 votes
1 answer
10k views

What is the significance of sync_binlog?

What is the significance of sync_binlog? What exactly happens If I set sync_binlog=1? How does it affect the replication between master and slave?
vRm's user avatar
  • 33
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, ...
Antonis Christofides's user avatar
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 ...
santhosh's user avatar
  • 133
3 votes
1 answer
7k views

Purge mysql log bins older than two weeks automatically

I have to manually write purge binary logs bin to "mysql-bin.somenumbers" ; Once in a while, I need to reduce the spaces that binlogs are taking on my web server. Is it possible to purge binary ...
Zhenyu's user avatar
  • 185
3 votes
1 answer
8k views

Understanding BinLogDiskUsage Metric behavior

We are using AWS-RDS. The following is the screenshot of how the binary log disk usage has varied over the last 6 weeks. The log retention period is 3 days. Tables are optimized and archived once a ...
Subrata Das's user avatar
3 votes
1 answer
3k views

How to set selinux permission for non-standard mysql bin log path?

By default, mysql bin log works in folder /var/log/mysql. but moving to any other share such /mylogs/mysql gives permission error. Below is the exact message: usr/sbin/mysqld: File '/mylogs/mysql/...
PAS's user avatar
  • 131
3 votes
1 answer
925 views

How can you close or flush a binlog file on a remote server

I am replicating my binlog files to a secondary server using the mysqlbinlog --stop-never --read-from-remote-server flags. If the primary database server is compromised I want to replay the binlogs ...
bwizzy's user avatar
  • 153
3 votes
1 answer
2k views

Binlog not consistent between slave and master

Initially I don't see binlog on slave is updating. Today I discovered and add log-slave-updates option to slave, now it is updating. However, the filename does not match between slave and master. ...
Carl's user avatar
  • 133
3 votes
2 answers
7k views

MySQL Master-Master Replication not syncing

SETUP: I have a WAMP MySQL Master-Master replication setup on 2 machines with sync_binlog=1 for safety. Machine config: Windows Azure Standard 2: 2 core, 4GB RAM, Windows Server 2008, MySQL 5.6.12, ...
bob's user avatar
  • 51
3 votes
1 answer
699 views

Switch MySQL lag slave to new master and keep lagging (5.1)

We have the following scenario: Set of slaves replicate from the master, separate standby master (a.k.a slave with binlogs) replicates from the same master; lag slave, powered by pt-slave-delay, ...
favoretti's user avatar
  • 133
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 ...
steve_at_downhill's user avatar
2 votes
1 answer
2k views

mixing binlog formats between master and slave

I have a pretty much straightforward Master --> Slave replication setup in MySQL 5.6 / Innodb. Both my Master and Slave are setup in MIXED binlog format. Now I need my slave to act as a master to ...
CherryTree's user avatar
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-...
ParoX's user avatar
  • 163
2 votes
1 answer
1k views

MySQL binlog does not show SQL statements

While checking the binlogs, it struck me that I cannot see any SQL statement. Here's an example: $> sudo mysqlbinlog --database=securities --start-datetime='2015-11-26 13:00:00' --...
VH-NZZ's user avatar
  • 236
2 votes
2 answers
2k views

MySQL binary logging with just one log file?

I have a MySQL database running with binary logging turned on. Each time the database is restarted a new binary log file is created in the format : mysql-bin.00001 mysql-bin.00002 ... mysql-bin.0000n ...
user avatar
2 votes
1 answer
7k views

Hi disk I/O and crash when turning on sync_binlog in MySQL

I have a MySQL master server (MariaDB 10.0.17) that have a very odd behavior. It has one slave fetching data from it. When I set global sync_binlog = 1, the server I/O goes very high and after some ...
Daniel Cukier's user avatar
2 votes
2 answers
10k views

How can I parse mysql bin log file for statements (Row/Mixed Format)

I have a mysql database running with binary log enabled. Unfortunately, a statement was executed by the client, which turned out erroneous. Since a number of records have been "affected", I want to ...
Ifedi Okonkwo's user avatar
2 votes
1 answer
818 views

MySQL: Why statement-based binlog format cannot work with Innodb READ UNCOMMITTED or READ COMMITTED Isolation levels?

In MySQL documentation it's written: If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used. Why statement-...
Benny Bauer's user avatar
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 ...
Mostafa's user avatar
  • 23
2 votes
1 answer
934 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 ...
Bohyun's user avatar
  • 55
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 ...
Jameel's user avatar
  • 21
2 votes
1 answer
1k views

Add query executed user information to Mysql binlog

We are using MYSQL as our production database and we have setup another salve for read requests. Time to time various database users can execute insert/update queries on master database. If we want ...
Mahesh Madushanka's user avatar
2 votes
2 answers
4k views

MySQL 5.6 Slave Stops Processing Binlog on Master Restart

I have a pair of Debian 7 servers with MySQL 5.6.23. The slave server is a clone of the master (but the uuid has been regenerated on the slave). For some reason, whenever MySQL is restarted on the ...
William Carter Baller's user avatar
2 votes
1 answer
593 views

mysql binlog file size stop growing

I made a master(A)-slave(B)-slave(C) mode MySQL instances for backup. But today when I see whether the mode works fine, I find A and B are OK. That is B can get binlog from A, but B does not write ...
user2431104's user avatar
2 votes
2 answers
1k views

How to see what was executed in the database via binlogs for specific date?

I am trying figure out how to "query" binlogs to see what was exectued in the database for a specific date. For example, if I execute mysqlbinlog binlog then the thing just runs forever and it is had ...
anna's user avatar
  • 151
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.
MrTambourineMan's user avatar
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---- ...
dr_'s user avatar
  • 1,314