[MySQL]master-master replication【原创】

xufengnju

                                                Master1:10.20.15.120
Master2:10.20.15.121
Step1：按照MySQL 5.0 Reference Manual中2.4.15. MySQL Installation Using a Source Distribution安装MySQL。Master1/Master2执行相同的操作。
Step2: 配置'root'@'localhost'的密码
On Master1:
[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.67-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use mysql;
mysql> update user set Password=password('10.20.15.120') where user='root' and host='localhost';
mysql> flush privileges;
mysql> exit;
On Master2:
[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.67-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use mysql;
mysql> update user set Password=password('10.20.15.121') where user='root' and host='localhost';
mysql> flush privileges;
mysql> exit;
Step3: 关闭mysqld进程。
On Master1/Master2 分别执行
[root@localhost ~]# mysqladmin -p shutdown
Step4：修改/etc/my.cnf配置
在Master1的/etc/my.cnf配置如下：
[client]
port            = 3306
socket          = /tmp/mysql.sock
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
default-character-set = utf8
skip-locking
skip-federated
log-bin=mysql-bin    #开启二进制日志
server-id       = 120    #server-id必须配置，且Master1/Master2的server-id必须唯一。
replicate-do-db = test_db1    #指定复制的db
replicate-ignore-db = mysql    #指定不复制的db，可以有多行
auto_increment_increment = 2
auto_increment_offset = 1 #以上两行的目的是避免在有auto_increment的字段的表同时被Master1/Master2插入值时中引起的自增长主键的冲突问题
[mysql]
auto-rehash
default-character-set = utf8
在Master2的/etc/my.cnf配置如下：
[client]
port            = 3306
socket          = /tmp/mysql.sock
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
default-character-set = utf8
skip-federated
log-bin=mysql-bin
server-id       = 121
replicate-do-db = test_db1
replicate-ignore-db = mysql
auto_increment_increment = 2
auto_increment_offset = 2
[mysql]
default-character-set = utf8
auto-rehash
Step5: 启动mysqld进程。
On Master1/Master2：
[root@localhost ~]# mysqld_safe --user=mysql &
Step6：授权REPLICATION SLAVE
On Master1:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication1'@'10.20.15.121' IDENTIFIED BY 'replication1';
mysql> flush privileges;
On Master2:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication2'@'10.20.15.120' IDENTIFIED BY 'replication2';
mysql> flush privileges;
Step7：记录master状态，执行change master to...
On Master1:
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)

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

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
On Master2:
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.06 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |     1902 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

On Master1:
mysql> CHANGE MASTER TO MASTER_HOST='10.20.15.121', MASTER_PORT=3306,MASTER_USER='replication2', MASTER_PASSWORD='replication2',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=1902;
mysql> start slave;
mysql> show slave status;
On Master2:
mysql> CHANGE MASTER TO MASTER_HOST='10.20.15.120', MASTER_PORT=3306,MASTER_USER='replication1',MASTER_PASSWORD='replication1',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=2379;
mysql> start slave;
mysql> show slave status;
至此，我们的master-master replication模式配置起来了。在Master1上，执行
mysql> create database test_db1;
mysql> use database test_db1;
mysql> create table test_tb1(id int auto_increment primary key,name varchar(10)) engine=myisam;
在Master1/Master2上同时对表test_tb1执行insert语句。
可以看到在Master1/Master2几乎同时看到表test_tb1中最新最完整的数据记录。
关于replication的选项的含义和使用方法请参考：
http://dev.mysql.com/doc/refman/5.0/en/replication-options.html

               
               
               
               
               
               

本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u2/74751/showart_1736816.html