binlog_format=ROW log-bin=binlog server_id=168150As with any normal replication setup, server_id can be any valid value for that range, provided it is unique across the topology. In my situation, I chose sql1 for this task. The server id reflects part of its IP address, since it is typically unique across a replication topology (often because on a multi-DC setup you may have the same private network on different DCs. I try not to do that). This is a change that requires a mysqld service restart on the affected sql node. Since this is MySQL Cluster, we can make the restart with no impact by simply removing this node from rotation. Additionally, we will need to create a replication user, which we can do by executing the following statements:
CREATE USER 'replica'@'10.0.2.15' IDENTIFIED BY 'examplepassword'; GRANT REPLICATION SLAVE ON *.* TO 'replica'@'10.0.2.15';
server_id=100215 log-bin=binlog binlog_format=ROWMySQL also needs to be restarted here for this change to take effect.
mysqldump --master-data=1 example > example.sql The --master-data option set to 1 will include a CHANGE MASTER TO command on the resulting dump, so that we can START SLAVE when the load completes, and start replicating off the server where we generated the dump (sql1 in this case). I am including the database name explicitly, instead of using --all-databases, to not get the mysql schema in the backup. You can backup multiple databases by using --databases if needed. Additionally, I am not using --single-transaction because NDB only supports the READ COMMITTED isolation level, and only on a per-row level, so this mysqldump option is not really supported for MySQL Cluster.
mysql -e 'change master to master_host="192.168.1.50";create database example' mysql < example.sql example Besides loading the data, this will also run a CHANGE MASTER to command and set the right replication coordinates, thanks to the --master-data file. For example, here is the command generated in my test backup: CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=154; Since it does not include a host specification, the previously set master_host will be used, which is the one we set with the 'change master to' command we ran right before creating the database and loading the data.
mysql> use mysql Database changed mysql> CREATE TABLE `ndb_apply_status` ( -> `server_id` int(10) unsigned NOT NULL, -> `epoch` bigint(20) unsigned NOT NULL, -> `log_name` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, -> `start_pos` bigint(20) unsigned NOT NULL, -> `end_pos` bigint(20) unsigned NOT NULL, -> PRIMARY KEY (`server_id`) USING HASH -> ); Query OK, 0 rows affected (0.02 sec) The 'USING HASH' will be ignored by Innodb, but it won't hurt (a usual btree index will be created instead). Now we're finally ready to start replication: start slave user='replica' password='examplepassword'; We can now check and see that it is running:
mysql> pager egrep -i running PAGER set to 'egrep -i running' mysql> show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: Yes Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 1 row in set (0.00 sec)We can also verify that everything thing works as expected, we can insert into MySQL Cluster using sql2, which does not have binary logging enabled, and we'll still have the write propagated to the MySQL slave which is replicating off sql1:
[vagrant@sql2 ~]$ mysql -e 'select max(id) from example.t' +---------+ | max(id) | +---------+ | 9 | +---------+ [vagrant@sql2 ~]$ mysql -e 'select max(id) from example.t' +---------+ | max(id) | +---------+ | 9 | +---------+ [vagrant@sql2 ~]$ mysql -e 'insert into example.t values (null)' [vagrant@sql2 ~]$ mysql -e 'select max(id) from example.t' +---------+ | max(id) | +---------+ | 10 | +---------+ [root@mysql57 ~]# mysql -e 'select max(id) from example.t' +---------+ | max(id) | +---------+ | 10 | +---------+And we can see we have a working asynchronous replication channel from MySQL Cluster into MySQL.
Even though manuals always discuss asynchronous replication in MySQL Cluster in the context of inter-cluster replication, we have seen that there are valid use cases for replicating to standalone MySQL (or a Galera based cluster), and we can achieve this setup with minimal effort. I'd like to reiterate that in the specific case that inspired this post, the use case was a migration away from MySQL Cluster, so the replication channel was not expected to be live for a long time; just long enough to allow the migration to take place with minimal application impact. It is valid to use this in other scenarios, but bear in mind that these are mostly uncharted waters: be prepared for some sharks!
Looking to optimize your MySQL use?