0

I'm trying to configure MySQL on a Bitnami-prepared VM for replication (Not to be confused with MySQL With Replication from Bitnami), and I am having issues with its binary logging.

When I add the line log-bin = mysql-bin or log_bin = mysql-bin in the my.cnf file under [mysqld], everything saves and MySQL restarts just fine. But when I test my application, only read transactions are working. Editing or creating new content fails.

I haven't found another cnf file that I'm conflicting with, and the bin logs do appear. But there seems to be some strange effects happening that I haven't encountered when configuring other MySQL servers in the past.

Anyone have an idea about what's going on?

Anthony Casson
  • 43
  • 6
  • Could you please provide more information about your deployment? (i.e. where did you get such VM, versioning... etc) – David González Ruiz Apr 25 '17 at 10:13
  • If you deployed that VM in Azure, you may experience issues similar to the ones below: https://community.bitnami.com/t/mysql-with-replication-not-replicating/48547/10 – David González Ruiz Apr 25 '17 at 10:15
  • Hey David. It was deployed through Azure, but it was not MySQL with Replication like is referenced in the post. I should have communicated that better. It's a deployed Bitnami MySQL 5.6.29-1 app on an Ubuntu 14.04 box. – Anthony Casson Apr 25 '17 at 21:24

1 Answers1

1

I did some further digging and discovered that the culprit was our 'transaction-isolation' configuration. We have ours set to 'READ-COMMITTED' to help address locking issues, and this configuration does not allow 'STATEMENT' binary logging ('binlog_format'), which is the default setting on our server. The effect was restricting our transactions to read-only.

The solution was to set the 'binlog_format' to 'ROW' (or MIXED). Obviously people who might ever reference this shouldn't set that unless they know their system can operate under that condition, but in our case it's a viable option.

It certainly has nothing to do with Bitnami. Just a configuration restriction.

Anthony Casson
  • 43
  • 6