http://www.softwareprojects.com/resources/programming/t-how-to-move-copy-a-live-mysql-database-and-what-1257.html
On Mysql Server
1. Turn binary logging on and add server id
CAUTION ! DO NOT USE “log-bin=/var/log/mysql/mysql-bin.log’
[mysqld]
log-bin=mysql-bin
server-id=1
2. Create replication account on the server
GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’slave-ip-address’ IDENTIFIED BY ‘password-here’;
3. Keep the above mysql console open, start new terminal and connect to server via new terminal
cd /var/lib/mysql
now use dir, you should see all the database folders
4. Time to lock all the databases on server.
mysql> SET GLOBAL WAIT_TIMEOUT=600000; SET WAIT_TIMEOUT = 600000; FLUSH TABLES WITH READ LOCK;
now on the new console enter
root@server:/var/lib/mysql#tar -cvf /tmp/mysql-snapshot.tar ./ –exclude mysql &
enter following in mysql console, this will show the position of server in log file, keep this window open and note file and position column
SHOW MASTER STATUS;
now enter following in mysql console
UNLOCK TABLES;
5. Copy the tar file from master to slave server
scp -i permission-file.pem /tmp/mysql-snapshot.tar root@slave-server-ip:/root/
6. Extract the tar file on slave server
cd /var/lib/mysql
mv /root/mysql-snapshot.tar .
tar –extract –file=mysql-snapshot.tar
7. Change server id on slave mysql server, add following to config file /etc/my.cnf or /etc/mysql/my.cnf
[mysqld]
server-id=2
8. Start or restart Slave mysql server with
service mysql restart
9. Login to mysql console and enter
mysql>stop slave;
mysql> CHANGE MASTER TO
MASTER_HOST=’ip-of-master-server’,
MASTER_USER=’repl’,
MASTER_PASSWORD=’replication_password-from-step-2′,
MASTER_LOG_FILE=’recorded_log_file_name-from-step-4′,
MASTER_LOG_POS=recorded_log_position-from-step-4;
mysql>start slave;
If every thing is done correctly the slave should start replicating.
Once master has been set on and slave started on Slave, after restart the slave will auto start.
If something is not right on slave, login to slave mysql console
use following
stop slave;
reset slave;
now from the new bash console, reextract the tar file replacing existing file with following command
root@slave-server:/var/lib/mysql# tar –overwrite –extract –file=mysql-snapshot.tar
now repeat step 9.