Claudio Nanni wrote:
All, Happy New Year, and let's hope somebody tries to stop the killing in
gaza.
I need to setup a replication slave off a master that is currently
production and not stoppable or lockable(possibly).
If you are using InnoDB, there is a --single-transaction method backup
( http://lists.mysql.com/replication/1235 ) however, you will need to
try that in your staging environment under realistic load to see if you
running that kind of transaction creates an unreasonable load spike or
memory usage.
Do you have any idea on how to setup the slave with minimum or no impact on
the master?
If you are using LVM, you might consider snapshotting, however, doing a
live snapshot without stopping mysql server would only work if you were
copying only myisam tables. Mysql-hot-copy would probably be better, but
either way, you need to flush your tables, which will briefly lock them,
so they can get onto disk.
In contrast, InnoDB actually needs to "shut down" to cleanly close its
table structures before you can physically copy the filesystem.
I use a method where I flush the tables, firewall off the system, shut
down mysql, do LVM snapshot, start mysql server, and then copy the
snapshot before unfirewalling it.
Why do I leave it firewalled? Because once you start writing to an LVM
volume that's been snapshotted, you start copying disk extents like mad,
creating a high load condition that can force queries to reach
connect_timeout. I have my connect_timeout set pretty low in my
environment.
The database is about 80GB.
Consider the transfer time with a dataset this large.
I would have a business level meeting with stakeholders telling them the
possible risks and adjust their expectations for uptime or service
availability. Write some scripts to automate the transfer, however you
do it, so that you don't fat-finger the process in production. Test and
time your scripts in a staging environment. Use this data, adjust it as
necessary for production load, to set stakeholder expectations.
Good luck!
Jed
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
