mysql replication note

[size=medium]
Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous by default.
Replication between servers in MySQL is based on the binary logging mechanism. The MySQL instance operating as the master (the source of the database changes) writes updates and changes as “events” to the binary log. The information in the binary log is stored in different logging formats according to the database changes being recorded. Slaves are configured to read the binary log from the master and to execute the events in the binary log on the slave's local database.
Replication works because events written to the binary log are read from the master and then processed on the slave. The events are recorded within the binary log in different formats according to the type of event.


[b]1.Setting the Replication Master Configuration[/b]
[mysqld]
log-bin=mysql-bin
server-id=1
binlog_format=mixed

[b]2.Setting the Replication Slave Configuration[/b]
[mysqld]
server-id=2

[b]3.Creating a User for Replication[/b]
mysql> create user 'repl'@'%.bz.com' identified by '1234';
mysql> grant replication slave on *.* to 'repl'@'%.bz.com';

[b]4.Obtaining the Replication Master Binary Log Coordinates[/b]
a. open an session to flush all tables and block:
mysql> flush table with read lock;
b. open another session to determine the current binary log file name and position:
mysql> show master status;
remeber the File and position for further use.
c. keep the first session read lock, now create a data snapshot with mysqldump,
mysqldump --all-databases --master-data > db.sql
the read lock prevent any further changes so that the data copied to the slave is in synchrony with the master.
d. unlock tables;(within the first session)

f. copy the master data to slave i another method to create a data snapshot

[b]5.Setting Up Replication with Existing Data[/b]
a. The next step depends on how you created the snapshot of data on the master
if use mysqldump: start the slave with --skip-slave-start option and then import the dump sqldata
if use raw data: extract the dump data to the slave data directory and then start the slave with --skip-slave-start option
b. setting master configuration on the slave
mysql > change master to
> MASTER_HOST='192.168.1.111'
> MASTER_USER='repl'
> MASTER_PASSWORD='psw'
> MASTER_LOG_FILE='recorded_log_file'
> MASTER_LOG_POS=recorded_log_postion


c. start the slave
mysql > start slave;

[b]6.Introducing Additional Slaves to an Existing Replication Environment[/b]
set up the new slave by making a copy of an existing slave, except that you configure the new slave with a different server-id value.
[/size]