Vendor docs: http://dev.mysql.com/doc/refman/4.1/en/replication.html

Overview

Mysql replication is per database and is master-slave. Two way replication is just two configs, one going the other way. These instructions are for one way master to one/many MyISAM setups on MySQL 4.1.x (Debian sarge). Like everything else in MySQL land, using InnoDB complicates matters and using 3.x makes things harder (grant privs for one).

MySQL replication masters must use bin logs. Master and all slaves must have a unique server-id assigned in my.cnf. Use id 1 for master. Slaves must be able to connect to the mysql port on the master (3306), so verify your network connection and firewall configs.

Although you have to do some configuration on the master, most of the work and configuration in MySQL replication in put on the slaves. Masters do not know how many slaves or what data they have. Slaves use the replication account to pull the binlogs by polling master. That is how they synch.

On Master:

enable bin logs and server id on master, restart if needed
lock tables on master with FLUSH TABLES WITH READ LOCK;'' and dump data to disk with ''mysqldump ...'', create strings are friendly
record output of ''show master status on master,
unlock tables with UNLOCK TABLES;''
create a user with replication privileges ''grant replication slave on ...

On each slave:

configure a server id and restart if necessary
create databases, transfer and load sql dumps from master
switch master to remote host with change master ...'' command using args from master
''slave start
check with show slave status

Details:

in master my.cnf:

# The following can be used as easy to replay backup logs or for replication
server-id

1 log-bin

/web/sites/data/mysql-bin.log #binlog-do-db

include_database_name #binlog-ignore-db

include_database_name

in slave my.cnf

# The following can be used as easy to replay backup logs or for replication
server-id

35 replicate-do-db

dbname read-only #log-bin

/var/log/mysql/mysql-bin.log #binlog-do-db

include_database_name

on master:

mysql> grant replication slave on *.* to replication@'%' identified by 'goodpass';
mysql> flush tables with read lock;
mysql> show master status\G;
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 460126
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

ERROR: No query specified # dump, eg : mysqldump -u dbuser -p -Q --add-drop-table --databases dbname |gzip -c > /web/home/dbname.sql.gz mysql> unlock tables;

on slave:

# fetch the sql dump with wget: 
wget http://server/dbname.sql.gz
zcat dbname.sql.gz | mysql -u dbuser -p 
mysql>change master to master_host

'server', master_user='replication', master_password='goodpass', ->master_log_file='mysql-bin.000001', master_log_pos

1196; mysql> slave start; # or reload mysql mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: server Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 480622 Relay_Log_File: server-relay-bin.000002 Relay_Log_Pos: 479473 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dbname Replicate_Ignore_DB: 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: 480622 Relay_Log_Space: 479473 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.00 sec)

ERROR: No query specified