Enabling replication in MySQL in fresh database



  • We have a legacy TokuDB database that we would like to begin migrating to a new engine. Unfortunately taking the db offline for migration is not feasible. We also can reprocess all the data in the database eventually. Is it possible in MySQL to replicate new without syncing the extant data first?



  • Years ago, MySQL supported a way to initialize replication the way you're describing. You configure a replica, and basically tell it to suck the current state of data from the source node. I have never used this feature, and it was quickly removed from the product. I can't even find documentation on this feature now. I think it would have to lock the source table to get the initial state of data, so it would have a pretty high risk that someone could cause an outage by accident just by enabling a new replica. I assume that's why the feature vanished.

    Recently a plugin called MySQL Clone appears to do the same thing, but it only works with InnoDB tables as far as I can tell. See https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-innodb-cluster-clone-deployment.html

    Creating a replica does not need to cause downtime on the source instance. At my last job, we did this frequently to add or repair replicas. We used https://www.percona.com/software/mysql-database/percona-xtrabackup to create a hot backup from the source node, and transferred that backup to the replica server, then started replication using the binary log coordinates recorded with the backup. Once we converted all our clusters to GTID, that got even easier.

    But I think your task is even easier than this. You don't need to use replication to convert the storage engine of a table. I would use https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html , a free tool that allows you to use ALTER TABLE on a table with virtually no downtime.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2