A. background
OS: CentOS 4.4
MySQL package: 4.1.20-1.RHEL4.1
node1: 192.168.0.131 (Master 1/Slave 2)
node2: 192.168.0.132 (Master 2/Slave 1)
mysql path: /var/lib/mysql
mysql config file: /etc/my.cnf
reference:
http://www.howtoforge.com/mysql_master_master_replication
http://cha.homeip.net/blog/archives/2004/12/replication_in.html
B. procedure
1.
On Master 1, make changes in my.cnf
#vim /etc/my.cnf
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 #replication log-bin binlog-do-db=horde binlog-ignore-db=mysql binlog-ignore-db=test server-id=1 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid |
2.
On master 1, create a replication slave account in mysql.
#mysql -u root -p
mysql> grant replication slave on *.* to ‘replication’@192.168.0.132 identified by ‘slave’;
mysql> exit
and restart the mysql master1.
#service mysqld restart
3.
Now edit my.cnf on Slave1 or Master2 :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 server-id=2 master-host = 192.168.0.131 master-user = replication master-password = slave master-port = 3306 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid |
4.
Restart mysql slave 1 and check config at
#service mysqld restart
#mysql -u root -p
mysql> start slave;
mysql> show slave statusG;
ensure Slave_IO_Running and Slave_SQL_Running: must be to YES.
mysql> exit
5.
On master 1:
|
1 2 3 4 5 6 7 |
mysql> show master status; +------------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------------+----------+--------------+------------------+ |MysqlMYSQL01-bin.000008 | 79 | horde | mysql,test | +------------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) |
The above scenario is for master-slave, now we will create a slave master scenario for the same systems and it will work as master master.
6.
On Master2/Slave 1, edit my.cnf and master entries into it:
#vim /etc/my.cnf
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 server-id=2 master-host=192.168.0.131 master-user=replication master-password=slave master-port=3306 log-bin #information for becoming master added binlog-do-db=horde [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid |
7.
Create a replication slave account on master2 for master1
mysql> grant replication slave on *.* to ‘replication’@192.168.0.131 identified by ‘slave2’;
8.
Edit my.cnf on master1 for information of its master.
#vim /etc/my.cnf
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 #master log-bin binlog-do-db=adam binlog-ignore-db=mysql binlog-ignore-db=test server-id=1 #information for becoming slave. master-host=192.168.0.132 master-user=replication master-password=slave2 master-port=3306 [mysql.server] user=mysql basedir=/var/lib |
9.
Restart both mysql master1 and master2.
On mysql master1:
mysql> start slave;
On mysql master2:
mysql > show master status;
On mysql master 1:
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.132
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Mysql1MYSQL02-bin.000008
Read_Master_Log_Pos: 410
Relay_Log_File: Mysql1MYSQL01-relay-bin.000008
Relay_Log_Pos: 445
Relay_Master_Log_File: Mysql1MYSQL02-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 410
Relay_Log_Space: 445
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: 103799
1 row in set (0.00 sec)
ERROR:
No query specified
make sure its running.
Now you can create tables in the database and you will see changes in slave. Enjoy!!