MySQL双主双从复制

1、接着之前配置的主主复制的基础，在每个主库下面分别配置一个从库
master1：

1. mysql> select * from t1;
   
2. +----+----------+
   
3. | id | name |
   
4. +----+----------+
   
5. | 1 | aaaaa |
   
6. | 2 | aaaaa |
   
7. | 3 | bbbbbbbb |
   
8. | 5 | bbbbbbbb |
   
9. +----+----------+
   
10. 4 rows in set (0.00 sec)

master2：

1. mysql> select * from t1;
   
2. +----+----------+
   
3. | id | name |
   
4. +----+----------+
   
5. | 1 | aaaaa |
   
6. | 2 | aaaaa |
   
7. | 3 | bbbbbbbb |
   
8. | 5 | bbbbbbbb |
   
9. +----+----------+
   
10. 4 rows in set (0.00 sec)

2、复制环境

1. master1(10.10.10.20)—>slave1(10.10.10.21)
   
2. master2(10.10.10.30)—>slave2(10.10.10.22)

3、逻辑备份数据库test

1. mysql> flush table with read lock;
   
2. Query OK, 0 rows affected (0.00 sec)
   
3. 
   
4. root@master1:/root# mysqldump -u root -p -c test > /tmp/test1.sql
   
5. Enter password:
   
6. root@master1:/root# scp /tmp/test1.sql 10.10.10.21:/tmp/
   
7. root@10.10.10.21                        test1.sql                                                 100% 1953     1.9KB/s   00:00    
   mysql> unlock tables;
   Query OK, 0 rows affected (0.00 sec)
   

4、在两个从库上创建用户

1. mysql> create user rep_user;
   
2. Query OK, 0 rows affected (0.00 sec)
   
3. 
   
4. mysql> grant replication slave on *.* to rep_user identified by 'rep_user';
   
5. Query OK, 0 rows affected (0.01 sec)
   
6. 
   
7. mysql> grant super on *.* to rep_user identified by 'rep_user';
   
8. Query OK, 0 rows affected (0.00 sec)
   
9. 
   
10. mysql> grant replication client on *.* to rep_user identified by 'rep_user';
    
11. Query OK, 0 rows affected (0.00 sec)
    
12. 
    
13. mysql> show grants for rep_user;
    
14. +------------------------------------------------------------------------------------------------------------------------------------------------+
    
15. | Grants for rep_user@% |
    
16. +------------------------------------------------------------------------------------------------------------------------------------------------+
    
17. | GRANT SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'rep_user'@'%' IDENTIFIED BY PASSWORD '*66275EE2A4400687CE14AD3B2CEE003B1B753575' |
    
18. +------------------------------------------------------------------------------------------------------------------------------------------------+
    
19. 1 row in set (0.00 sec)

5、在从库上导入数据，并更改参数

1. root@slave1:/root# mysql -u root -p -D test < /tmp/test1.sql
   
2. Enter password:
   
3. 
   
4. vi /etc/my.cnf
   
5. server-id = 21
   
6. binlog-do-db = test
   
7. 
   
8. relay-log = slave-relay-bin
   
9. relay-log-index = slave-relay-bin.index

6、查看master1主库的POS，并在slave上配置POS

1. mysql> CHANGE MASTER TO
   
2.     -> MASTER_HOST = '10.10.10.20',
   
3.     -> MASTER_PORT = 3306,
   
4.     -> MASTER_USER = 'rep_user',
   
5.     -> MASTER_PASSWORD = 'rep_user',
   
6.     -> MASTER_LOG_FILE = 'master1-bin.000002',
   
7.     -> MASTER_LOG_POS = 387;
   
8. Query OK, 0 rows affected, 2 warnings (0.05 sec)
   
9. 
   
10. mysql> start slave;
    
11. Query OK, 0 rows affected (0.00 sec)

7、在slave上重复3-6的步骤。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12219480/viewspace-1768487/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12219480/viewspace-1768487/