kafkamaster-slave-replicationMySQLreplication
We have a master slave setup of MYSQL 5.6. Master and slave runs SBR replication where as slave is not writing binlogs.
We need to enable binlogs on the Slave server but we want the binlogs to be in RBR format on the slave instead. We want to ship them to Kafka and it wants RBR replication only.
Is this doable to have RBR on the slave where as it is getting data from master as SBR ?
Thanks
Using the topology you mentioned from the MySQL Documentation
Let's setup the first scenario
EXAMPLE IPs for Each DB Server
Master1 : 10.20.30.40Master2 : 10.20.30.50Slave_1 : 10.20.30.60repluser@'%'replpassMake sure binary logging is enabled on all the Slaves
We will do the following
Step 01 : On Master2, run the following
mysql> SET GLOBAL read_only = 1;
mysql> STOP SLAVE;
mysql> RESET SLAVE;
mysql> CHANGE MASTER TO master_host='';
mysql> FLUSH TABLES;
mysql> SET GLOBAL read_only = 0;
Step 02 : Move your CNAME/VIP to Master2
Step 03 : On Slave_1, run mysql> RESET MASTER; FLUSH TABLES;
Step 04 : On Slave_1, dump the data
NEW_MASTER_HOST="10.20.30.60"
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS="--single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases"
echo "STOP SLAVE;" > MySQLData.sql
echo "CHANGE MASTER TO master_host='${NEW_MASTER_IP}'," >> MySQLData.sql
echo "master_port=3306," >> MySQLData.sql
echo "master_user='repluser'," >> MySQLData.sql
echo "master_password='replpass'," >> MySQLData.sql
echo "master_log_file='bogus'," >> MySQLData.sql
echo "master_log_pos=1;" >> MySQLData.sql
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} >> MySQLData.sql
echo "START SLAVE;" >> MySQLData.sql
gzip MySQLData.sql
Step 05 : When Master1 comes back up, rsync or scp MySQLData.sql.gz from Slave_1 to Master1
Step 06 : Login to MySQL on the Master1 and setup it up to replicate from Slave_1
Don't worry about the real binary log filename and position.
Using --master-data=1 embeds CHANGE MASTER TO command with the real coordinates on line 23 of a standard dump.
You can see it with the following
less MySQLData.sql.gz | head -35 | tail -1
Step 07 : Load the data into Master1
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
gzip -d < MySQLData.sql.gz | mysql ${MYSQL_CONN}
Step 08 : Login to MySQL on Master1
mysql> SHOW SLAVE STATUS\G
and Make sure Slave_IO_Running and Slave_SQL_Running both say Yes
When Done, the topology should be
Master1 : 10.20.30.50Master2 : 10.20.30.60Slave_1 : 10.20.30.40Please try this out on Test Servers before deploying to Production
Note: I would advise that you have multiples slaves
SELECTsIf you want to setup slave from another slave steps would be as follows
For details regarding master-slave version difference, shouldn't be a big issue but a good idea is to go through MySQL documentation.
Hope it helps
Best Answer
You shouldn't have any trouble. For you replication you use the binlogs in the master, and the your slave reads them and "translates" them to his relay logs. Then your slave executes that relay log and for the replication, that's it.
In fact, you have already "different" settings, since you weren't doing any binlogs at all in your slave, so enabling RBR won't do any harm.