MySQL replication may sound complicated, but it can be done easily. Here’s a quick 7-step guide:
1) Create a replication user on the master:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'password';
2) On the master server, add the following to the [mysqld] section in my.cnf and restart MySQL:
server-id = 1
relay_log=mysqldrelay
log-bin
expire_logs_days = 7
3) On the slave server, add the following to the [mysqld] sesion in my.cnf and restart MySQL:
server-id = 2
4) Create a mysqldump file on the master server which includes a global lock:
# mysqldump -u user -ppassword -FQqx --opt --master-data=1 --databases db1,db2 > databases.sql
5) Configure the slave:
# mysql -u user -ppassword
mysql> CHANGE MASTER TO MASTER_HOST='master host name', MASTER_USER='repl', MASTER PASSWORD='repl';
6) Move the dump to the slave server and import it:
mysql -u user -ppassword < databases.sql
7) Start the slave:
mysql -u user -ppassword
mysql> START SLAVE;
Entries (RSS)
[...] the original master comes back online, set it up just like a new slave. You can skip some steps, such as setting the server-id, since that still should correspond to your [...]
If a stored procedure or function is associated with the database you will want to add the -R flag to the mysqldump command. If a stored procedure or function is referenced in the backup file and does not exist on the slave the import will error out.
Complete info at http://bugs.mysql.com/bug.php?id=9056