Positing this for reference, I am sure someone might find it useful here...

I have a customer who has over 100GB of MySQL data and taking their site
down for even a few minutes is not an option. However, I really wanted to
get a slave set up in case the main server ever dies. Even though the
server is backed up, it would take 2-3 hours to restore the MySQL server,
if not longer.

The solution is to use replication. The traditional problem with this
approach is locking the tables for so long while the dump happens, we are
talking close to 4-5 hours for the database size. R1Soft's HotCopy Free
Product was the solution. By using HCP, you can lock the tables, make a
"snapshot" and only have to lock tables for a few seconds... here's the
process I went through..

On Master Server:

- create a replication user:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'SLAVE_IP_OR_HOSTNAME'
IDENTIFIED BY 'repluserpass';
- set up bin logs and master id in /etc/my.cnf
log-bin=mysql-bin
server-id=1
- lock tables ( FLUSH TABLES WITH READ LOCK; in mysql)
- get master position ( show master status )
- issue a hcp command ( hcp -o /dev/sda2 ) in my case (takes 1 second)
- unlock tables;
- stop slave server if running, and delete /var/lib/mysql folder
- do a simple rsync to the new server:
rsync -avz /var/hotcopy/sda2_hcp1/var/lib/mysql root@slavehostorip.com:
/var/lib/
- copy my.cnf to slave:
scp -P22 /etc/my.cnf root@slaveip_or_host:/etc/my.cnf

On Slave Server
- make sure you have the same version of mysql..
- make sure slave is set to ID 2:
edit /etc/my.cnf and change ID to 2
- start up mysql, and then enter commands to connect to master:
mysql> CHANGE MASTER TO
MASTER_HOST='MASTER_IP_OR_HOST',
MASTER_USER='repl',
MASTER_PASSWORD='repluserpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=12345678;
START SLAVE;

In my case, the slave was now 37,000,000 positions behind, it caught up in
about 20 seconds over 1gig ethernet. Very impressive!

If you want help with something like this, let me know.

Thanks!
Marc Pope
Falcon Internet

#############################################################

Attend the Lasso Developer Conference 2013!
Sept 12-14, 2013 in Niagara Falls, Canada
http://www.lassosoft.com/LDC-niagara-falls-2013

#############################################################
This message is sent to you because you are subscribed to
the mailing list Lasso
Lasso@lists.lassosoft.com
To unsubscribe, E-mail to: <Lasso-unsubscribe@lists.lassosoft.com>
Send administrative queries to <Lasso-request@lists.lassosoft.com>