Steps for setting Master-Master Relationship in mysql

# setting up Master-Master Relationship.
Lest say we have two systems system1 AND system2
we need to set up the replication ( Master-Master replication).
We need to consider some of the basic points before setting mysql
1. We should be able to ping on both servers (from system1 to  system2  and from system2 to  system1)
2. we should also enable the port 3306 on both servers ( generally mysql runs on 3306 port).
3. system time should be same on system1 and system2. ( if it is different we should change the mysql time and should be same on both mysql ).
4. mysql server_id should be different.
5. Binary logging should be enable on both servers.
6. try to change the auto-increment-increment and auto-increment-offset in my.cnf for more details see on the following
steps.

1. install mysql on both servers on system1  and system2.

on system1 follow the following steps:
A) in my.cnf we need to add the following. ( changing server-id,auto_increment and auto_increment offset)
The file structure is as follows:
vi /etc/my.cnf
[...]
[mysqld]
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
master-host = system2
master-user = slave
master-password = slave
log-bin = /var/log/mysql
log-bin = /var/log/mysql/mysql-bin.log
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
B.) restart the mysql
/etc/init.d/mysql restart
C.) connect to mysql:
mysq > GRANT REPLICATION SLAVE ON *.* TO 'slave'  IDENTIFIED BY 'slave';
FLUSH PRIVILEGES;
quit;
D.) mysqldump -ukiran -pkiran --all-databases --single-transaction -R  --master-data=1 > /home/kiran.chinta/system1-June10.sql
E.) copy the /home/kiran.chinta/system1-June10.sql  to system2

ON system2 follow the following steps:
A.) in my.cnf we need to add the following. ( changing server-id,auto_increment and auto_increment offset)
The file structure is as follows:
[...]
server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2
master-host = system1
master-user = slave
master-password = slave
log-bin= /var/log/mysql/mysql-bin.log
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
B.) restart the mysql
/etc/init.d/mysql restart
C.) restore the mysql which is taken from system1.

mysql -u kiran -pkiran < /home/kiran.chinta/system1-June10.sql
mysql> flush privileges;
to know the binary log position run the command as follows:
head -30 /home/kiran.chinta/system1-June10.sql
We will get the MASTER TO MASTER_LOG_FILE and MASTER_LOG_POS which is as follows:

-- CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000001', MASTER_LOG_POS=98;

D.) starting the slave
mysq> CHANGE MASTER TO MASTER_HOST='system1', MASTER_USER='slave', MASTER_PASSWORD='slave', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;
mysql> start slave;
mysql> show slave status \G
We need to carefully observe the three output "slave_IO and Slave_SQL" both should be YES. which are as follows

     Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            Seconds_Behind_Master: 0

E) mysql> show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     98   |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Go to system 2
run the following commands
mysql> mysq> CHANGE MASTER TO MASTER_HOST='system2', MASTER_USER='slave', MASTER_PASSWORD='slave', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;
mysql> start slave;
mysql> show slave status \G
We need to carefully observe the three output "slave_IO and Slave_SQL" both should be YES. which are as follows

     Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            Seconds_Behind_Master: 0

Now the master master replication is complete:

some of the important parameter in my.cnf file are as follows:

replicate-do-db
replicate-ignore-db
binlog-do-db
replicate-ignore-table
skip-slave-start
log-slave-updates
log-expire-days

some of the important command in mysql are as follows:
-- skip the sql-statement when there is a bad query
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
--start and stop the repliction until specified binary log position:
mysql> START SLAVE UNTIL MASTER_LOG_FILE='bin-log.000010', MASTER_LOG_POS=130223;

For more details about the replication give below are the URL:
http://dev.mysql.com/doc/refman/5.5/en/replication.html
http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html