MySQL Replication from On-Premises to AWS

Question

I have an on-premises DB server running MySQL 5.5.37 with multiple databases.

I created database on the AWS RDS.

The idea is to set the replication from on-premises to the AWS, but for only one database.

So, I configured binlog and set binlog-do-db to use only one database which is needed to replicate. When slave is started on the AWS, I get an 1049 error.

Now, I guess the trouble is binlog_format, which is MIXED.

Does someone know is it possible to change binlog_format FROM MIXED to ROW and restart the master database or should I run the whole dumping process from the beginning?

Answer 1

You have to edi6t the my.ini/cnf and put following under the section mysqld

[mysqld]
binlog_format = ROW

If you add it under another section it will not work.

You have the right idea to flush first the logs and the switch at runtime to another format, and having the my.cnf changed.

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'ROW';
FLUSH LOGS;
UNLOCK TABLES;

You should also check the manual for more information