0

We all know, there are several methods for syncing data from a mysql master to a slave. After reading many blogs about this issue, two common tools always come up:

  • pt-online-schema-change
  • pt-table-sync
  • xtrabackup

in my case, i cannot use them, because:

  • pt-online-schema-change (only works with tables, that don´t have any triggers)
  • pt-table-sync (only works if binglog logformat of master is not ROW)
  • xtrabackup (only works for the whole server, i need to sync single DBs)

so if my reasons, not using these tools, are not wrong, i am searching for the best solution, with less or none downtime, when syncing all the data from the master to the slave.

Maybe a simple mysqldump is the best solution here, maybe not.

michabbb
  • 131
  • 4
  • My go to is Percona Xtrabackup. Back up the master, restore on the slave. Mysqldump will work but Xtrabackup will likely be far faster. Later, do the backups regularly so you can restore from those. Xtrabackup can do it with (usually) very little downtime locking. –  Nov 07 '16 at 21:49
  • @yoonix but if i am not wrong, Xtrabackup is not able to sync only specific databases, thats my main problem here – michabbb Nov 07 '16 at 21:57
  • I never used that ability, but it does have support for it. Depending on your data size, a full multi-threaded backup with Xtrabackup might be far faster than a mysqldump on a single database. That's been my experience. –  Nov 07 '16 at 22:35

1 Answers1

0

pt-table-sync (only works if binglog logformat of master is not ROW)

not exactly correct. pt-table-sync will set a binlog format in its session if it's different form STATEMENT

xtrabackup (only works for the whole server, i need to sync single DBs)

If innodb_file_per_table is ON XtraBackup can backup/restore individual tables/databases

akuzminsky
  • 738
  • 4
  • 9
  • dear @akuzminsky my problem with pt-table-sync is: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED. – michabbb Nov 08 '16 at 10:13
  • can you just print statements and execute them with a mysql client? (see `--print` option) – akuzminsky Nov 08 '16 at 17:37