0

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 change it during runtime (live environment) since it's our main database.

Questions

  1. What things do I have to consider before I change it?
  2. What is the safest way to do it?
  3. I am just wondering if there will be growth of the binlog size when I change it?
1

you can use at the runtime.

SET GLOBAL binlog_format = 'ROW';

or for the session

SET SESSION binlog_format = 'ROW';

see the manual for more information.

3
  • how much the binlog size will be growth ? – parveen Jun 5 '20 at 11:00
  • You can set max_binlog_size and max_binlog_files. to get a grip on the maximumsite if the bin log. the max size is max_binlog_size * max_binlog_files. – nbk Jun 5 '20 at 11:11
  • You should set it in the config, else it will be lost after the next restart. – Rick James Jun 9 '20 at 3:51
0

Short answer: Nothing to worry about.

Long answer...

The size of the binlog is mostly irrelevant. (It used to be defaulted to 1G, has recently defaulted to 100M.)

The binlog grows until it exceeds the configured limit. Then a new binlog is started. It is up to either expire_logs_days to purge old files or some other admin task.

The binlog may grow faster, or maybe slower; it really depends on the types of queries that are replicated. For example, a million-row UPDATE (a naughty thing to do) will be one statement (the UPDATE) in STATEMENT mode and will be a million row-changes in ROW mode. In this example, ROW will fill up more disk; the admin task will eventually clear it.

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.