Well we had to setup some hefty realtime backup solution for a database server here at work. After much research i decided hotcopy and mysqldump would not suffice, its too load intensive across the system and ties up the tables with LOCKs for too long. So in walked replication, my beautiful angel of salvation! It was pretty smooth to get setup once i fumbled through it a bit, though i'm not a pro yet, it seems to be running pretty smoothly.

Basically the steps go as follow:
1. Install MySQL on your two servers (I did matching versions [5.x] don't know how diff versions will work)

2. Configure your master my.cnf add the following:

log-bin
server-id=1


3. login to mysql as root and run this statement:

mysql> GRANT SELECT , RELOAD , LOCK TABLES , REPLICATION SLAVE , REPLICATION CLIENT ON *.* TO username@"%" IDENTIFIED '<password>';
mysql> FLUSH TABLES WITH READ LOCK;
mysql > SHOW MASTER STATUS;

take note of the binary log file and its position you'll need these values when configuring your slave.

4. mysqlhotcopy.sh your databases from your master to your slave

> mysqlhotcopy db_name /path/to/backup/directory
> tar -czvf db_name.tgz /path/to/backup/directory
> scp db_name.tgz user@slave:~/db_name.tgz
> ssh -l user slave
> su
> tar -zvxf db_name.tgz
> mysqld stop
> mv db_name /path/to/mysql/data/directory
> mysqld start

I was very genirc on this stuff it will be up to you to figure out the exact details that match your operating system

5. Configure your my.cnf for your slave

log-bin
server-id=2
log-slave-updates
log-warning
replicate-ignore-db=mysql


6. restart mysql on your slave and run the following:

mysql> CHANGE MASTER TO MASTER_HOST='<master host name>',
MASTER_USER='<replication user name>',
MASTER_PASSWORD='<replication password>',
MASTER_LOG_FILE='<recorded log file name>',
MASTER_LOG_POS=<recorded log offset>;

mysql> SLAVE START;


7. check it!

mysql> SHOW SLAVE STATUSG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master
Master_User: slaveuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 24169757
Relay_Log_File: slave-bin.000032
Relay_Log_Pos: 12546280
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 24169757
Relay_Log_Space: 12546280
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.05 sec)

ERROR:
No query specified


Well that's the jist of it, hope it makes some sense, if not eh well maybe i'm crazy and mysql loves me. Here are a couple of links i used to help me get through it so maybe they'll shed more light on the situation.


MySQL Reference Manual :: 6 Replication in MySQL
Database Replication in MySQL
Live Backups of MySQL using Replication

Well this is it kids, i caved in and made a blog. I'm going to use it to document my journeys through the interweb, mainly covering LAMP development and some related topics. SO... if you wanna be as cool as me get on the bandwagon and start readin! Oh and i don't spell well or have good typing gramar so eat it! ok?