MySQL Replication HowTo
How to replicate MySQL using a master and slave
MySQL Replication
Notes/HowTo
Links that are helpful:
How To Set
Up Replication (from mysql.com)
Setup Master
Server:
You'll need this
in your my.cnf on your server:
log-bin
server-id = 1
log bin turns on logging and server-id (which must be
unique on your network) is requried for replication to work. I made the
master in this case #1.
Then, setup the
replication user (assumes you're running mysql 4.
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@"%" IDENTIFIED '';
restart your
mysql server, if necessary do a:
Get a snapshot of
current data for the slave
after restarting
with log-bin enabled, tar up your /var/lib/mysql databases that you are
going to want to replicate and copy them over to the slave. i replicated
all but the 'mysql' database.
If you are using MyISAM tables, flush all the tables and block write
queries by executing FLUSH TABLES WITH READ LOCK command.
mysql> FLUSH TABLES WITH READ LOCK;
tar -cvf /tmp/mysql-snapshot.tar /path/to/data-dir
then, on your
master, do a
mysql > SHOW MASTER STATUS;
and note the
'File' name and the 'Position' number
then you can
allow writes again on your master by doing an
'unlock tables'
Setup the slave
server:
add server-id to
the my.cnf file
(other things i
added to my.cnf:
log-bin
server-id = 2
log-slave-updates
log-warnings
replicate-ignore-db=mysql
)
restart the
mysqld
then:
mysql> CHANGE MASTER TO MASTER_HOST='',
MASTER_USER='',
MASTER_PASSWORD='',
MASTER_LOG_FILE='',
MASTER_LOG_POS=;
replacing the
values in <> with the actual values relevant to your system.
Start the slave
threads:
mysql> START
SLAVE;
Once a slave is
replicating, you will find a file called `master.info' and one called
`relay-log.info' in the data directory. These two files are used by the
slave to keep track of how much of the master's binary log it has
processed. Do not remove or edit these files, unless you really know
what you are doing. Even in that case, it is preferred that you use
CHANGE MASTER TO command. NOTE : the content of `master.info' overrides
some options specified on the command-line or in `my.cnf'
Our Replication
Scenario
[.6-- Rules] [.11 -- Pilgrim]
-read/write -read/write
-Master for external Databases -Master for internal Databases
[.3 -- dual processor]
-read only
- slave for .6 and .11
- wicked fast for 'selects' from php
tica,arial,sans-serif;">
some real world scenarios 
