Best way to take initial backup when starting MySQL replication?

sundialsvcs · 02-12-2016, 10:27 AM

I have a moderately-busy MySQL database that's about 78 gigabytes in size, and we are going to start replicating it. What is your opinion of the best way to do the initial backup / contents-capture to populate the slave? Any other good websites to bone-up on before doing this?

My impression is: "MySQLDump and plan for about an hour's downtime ... yes?"

Habitual · 02-12-2016, 10:54 AM

I think an hour is a little optimistic, but a dump is what I would do, if it were me.
Some links I have:
http://dev.mysql.com/doc/refman/5.1/...n-general.html
http://dev.mysql.com/doc/refman/5.1/...-starting.html
http://dev.mysql.com/doc/refman/5.1/...atibility.html
http://dev.mysql.com/doc/refman/5.5/...-features.html
http://dev.mysql.com/doc/refman/5.1/...-features.html
http://stackoverflow.com/questions/3...in-mysql-query
https://blogs.oracle.com/MySQL/entry...es_across_data
http://www.mysqlperformanceblog.com/...-large-tables/
http://stackoverflow.com/questions/1...ion-clustering
http://capttofu.livejournal.com/1752.html?nojs=1
http://stackoverflow.com/questions/2...-table-locking
http://dev.mysql.com/doc/refman/5.1/...mp_lock-tables

Hope that "helps"?

Guttorm · 02-12-2016, 12:51 PM

You can reduce downtime with rsync. First rsync /var/lib/mysql, stop mysql, and rsync again. But the mysqldump way is easier and safer.

TenTenths · 02-15-2016, 03:03 AM

mysqldump with the --master-data and just take the initial performance hits are what I usually go with.

If your replication is for backup rather than as a live "slave" server then you might want to consider not replicating any tables that use the MEMORY engine.