42 votes

Disable MySQL binary logging with log_bin variable

In MySQL 8, it is the option disable_log_bin which has to be provided without any parameter in the my.cnf file in the [mysqld] section: [mysqld] disable_log_bin some-other-option = any-value From the ...
Peter VARGA's user avatar
13 votes

Is it safe to delete mysql-bin files?

Try this: RESET MASTER; as the document said: RESET MASTER enables you to delete any binary log files and their related binary log index file, returning the master to its state before binary ...
Xiaofeng's user avatar
  • 241
12 votes

Is it safe to delete mysql-bin files?

Yes, it is safe. For mysql 8, here's how you can do it. We can set binlog_expire_logs_seconds as a option or as a system variable or as a command line parameter too, but I will show how to use it as ...
Eduardo Santana's user avatar
12 votes

Disable MySQL binary logging with log_bin variable

From the Manual for MariaDB (https://mariadb.com/kb/en/library/activating-the-binary-log/) If you don't provide a name (which can, optionally, include an absolute path), the default will be ...
I'm Root James's user avatar
11 votes

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

In my case PURGE BINARY simply wasn't deleting anything. I had my partition at 100% usage (I had to clean up my slowqueries log a bit so that there was enough space to mysql to be restarted), so the ...
carla's user avatar
  • 603
9 votes

Maximum overall size of MariaDB binary logs

max_binlog_size With regards to max_binlog_size, what you are asking is not possible. Why ? On May 05, 2017, I answered the question MySQL - Binary Log File size growing more than the maximum limit. I ...
RolandoMySQLDBA's user avatar
8 votes
Accepted

What is the significance of sync_binlog?

Turning it on flushes binlog events to disk. This costs some extra I/O. Turning it off lets the events eventually go to disk. If the one of the servers crashes, the Slave can be in an "impossible" ...
Rick James's user avatar
  • 78.1k
7 votes
Accepted

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

The reason your binary logs are so gigantic is simple: YOU ARE COMMITTING TOO MUCH DATA PER SINGLE TRANSACTION !!! Why is this the case ? According to the MySQL Documentation on max_binlog_size: A ...
RolandoMySQLDBA's user avatar
7 votes
Accepted

Can it be safe to disable binary logging in MySQL?

Unlike many other DBMSes, MySQL has two levels of logs to perform the "redo" function. Logs used to perform crash recovery are implemented (or not) by the storage engines, e.g. the InnoDB ...
mustaccio's user avatar
  • 26k
6 votes
Accepted

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

TL;DR: This is probably caused by poor table design combined with ROW-based replication. I just ran into this problem. I was asked to move an old database to a new server and set up replication. I ...
Pan's user avatar
  • 184
6 votes

enabling BINARY LOG on AWS RDS MySQL

Amazon will not allow the user to enable binlogs. You have to enable automatic backups on the RDS Instance. The RDS framework will then enable binary logs for you. You cannot enable them manually. ...
RolandoMySQLDBA's user avatar
5 votes
Accepted

Mysql FLUSH LOGS in live server with replication

FLUSH LOGS; is safe to do anytime. It will rotate the replication logs; this is harmless; replication knows how to move on to the next log. The binlog is automatically rotated when it exceeds ...
Rick James's user avatar
  • 78.1k
4 votes

Unable to start mysqld service after enabling log_bin

According to the manual entry for the mysqld option log-bin: If you specify this option without also specifying a --server-id, the server is not allowed to start. So it seems you need something ...
4 votes
Accepted

Understanding BinLogDiskUsage Metric behavior

(Long-winded, but incomplete, answer) The graph moves up and down rather smoothly. This puzzled me. With your latest numbers, I can explain some of what is going on. Data is written to the binlog ...
Rick James's user avatar
  • 78.1k
4 votes
Accepted

mysql bin logs expiry to not to fill up disk space

One tricky thing about binlog expiration you have to keep in mind is that it doesn't happen until the MySQL Server opens a new binlog file. So as long as it is currently writing to bin.000006, the ...
Bill Karwin's user avatar
  • 14.7k
3 votes
Accepted

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

I've done some research on this now, so I'll answer on my own question. Summary first If the performance problems are observed only on one node, it's perfectly safe to turn off innodb_doublewrite ...
tobixen's user avatar
  • 211
3 votes

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

To set mysql selinux on a directory you can: semanage fcontext -a -t mysqld_db_t "/yourdirectory(/.*)?" then restorecon -Rv /yourdirectory Also check the mysql user has permissions on your ...
cormpadre's user avatar
  • 131
3 votes
Accepted

replicate-ignore-table setup and usage for excluding table from binlog backup

The replicate-ignore-table filter applies only the streaming over replication protocol (specifically in this case the SQL thread Slave), it doesn't affect if you apply log changes manually as you are ...
3manuek's user avatar
  • 1,449
3 votes

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

A Google of "peter zaitsev" file systems buffering double write gives several good references (Peter Zaitsev works for Percona who are big hitters in the MySQL world. Of these, I picked out 4 as ...
Vérace's user avatar
  • 29.9k
3 votes
Accepted

Is it safe to copy the binlogs from a running mysql server

As long as you have done flush logs and you have taken the logfilename just before flush logs say it should be fine. Example current binlog file is bin-log00333 and after flush logs it is bin-...
Mannoj's user avatar
  • 1,561
3 votes

enabling BINARY LOG on AWS RDS MySQL

I found this blog post that answered the question: https://blog.pythian.com/enabling-binary-logging-rds-read-replica/ Essentially, if you make sure your "Backup retention period" for your RDS ...
JD D's user avatar
  • 131
3 votes
Accepted

Can I disable binlogs to save space temporarily

What you need to do is set sql_log_bin to 0 as you start pt-online-schema-change using set-vars pt-online-schema-change --set-vars sql_log_bin=0 Your binary logs will never know the difference. ...
RolandoMySQLDBA's user avatar
3 votes

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

First, I'll say that the answer is "it depends". With binlog_format = "STATEMENT", the statements are put in the binlog; hence the second format is bigger. With "ROW" ...
Rick James's user avatar
  • 78.1k
3 votes
Accepted

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

FLUSH TABLES WITH READ LOCK is sufficient. This locks the tables, preventing new writes, and it also makes sure any other writes to tables are flushed to the disk volume for MyISAM tables. For InnoDB ...
Bill Karwin's user avatar
  • 14.7k
3 votes
Accepted

max_binlog_files on MySQL Server?

No, the upstream MySQL implementation does not have a feature equivalent to max_binlog_files. So you can't really prevent a short-term surge in binary log growth. Well, the option log_bin=OFF would ...
Bill Karwin's user avatar
  • 14.7k
2 votes

Mysql FLUSH LOGS in live server with replication

using flush logs; then usually reset master will save all unsaved changes to the logs and drop old ones. there is a workaround for it: If you are using replication, you should not delete old binary ...
Ahmad Abuhasna's user avatar
2 votes
Accepted

Partial mysqlbinlog on latest binlog file

You can use FLUSH LOGS to rotate the binlog before it reaches max size.
jkavalik's user avatar
  • 5,080
2 votes
Accepted

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

Flushing the log on the master solves your problem pretty much by accident. When the master log file rotates, mysqlbinlog closes the current file and opens the next one, which implicitly flushes any ...
Michael - sqlbot's user avatar
2 votes

Is it safe to copy the binlogs from a running mysql server

Is this a Master? Or a Slave? FLUSH LOGS, by itself, will start new log files. (Please verify that this happens.) Then the old files are properly closed and can be copied. And the POS is 0. (4 ...
Rick James's user avatar
  • 78.1k
2 votes
Accepted

Target log not found in binlog index, from purge binary logs mysqld-relay-bin

You are mixing up two things: "binlogs" and "relaylogs" The Master writes to a sequence of binlogs on the Master. These are purged by any of () expire_logs_days, () explicit PURGE, (*) OS delete ...
Rick James's user avatar
  • 78.1k

Only top scored, non community-wiki answers of a minimum length are eligible