MySQL主主复制

1、环境，版本5.6

1. master1:10.10.10.20
   
2. master2:10.10.10.30

2、在master1上创建表

1. master1:
   
2. mysql> use test;
   
3. Database changed
   
4. mysql> show tables;
   
5. Empty set (0.00 sec)
   
6. 
   
7. mysql> create table t1 (id int primary key auto_increment,name varchar(50));
   
8. Query OK, 0 rows affected (0.21 sec)
   
9. 
   
10. mysql> select * from t1;
    
11. Empty set (0.00 sec)
    
12. 
    
13. mysql> insert into t1 (name) values ('aaaaa');
    
14. Query OK, 1 row affected (0.06 sec)
    
15. 
    
16. mysql> select * from t1;
    
17. +----+-------+
    
18. | id | name |
    
19. +----+-------+
    
20. | 1 | aaaaa |
    
21. +----+-------+
    
22. 1 row in set (0.00 sec)

3、备份表

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

4、在master1和master2上创建复制用户和授予权限

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.00 sec)
   
6. 
   
7. mysql> grant replication client on *.* to rep_user identified by 'rep_user';
   
8. Query OK, 0 rows affected (0.00 sec)
   
9. 
   
10. mysql> grant super 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.01 sec)

5、在master1、master2上更改参数，并重启

1. master1:
   
2. vi /etc/my.cnf
   
3. server-id = 20
   
4. replicate-same-server-id = 0
   
5. auto-increment-increment = 2
   
6. auto-increment-offset = 1
   
7. log-bin = master1-bin
   
8. log-bin-index = master1-bin.index
   
9. binlog-do-db = test
   
10. 
    
11. relay-log = slave-relay-bin
    
12. relay-log-index = slave-relay-bin.index
    
13. log-slave-updates
    
14. 
    
15. master2:
    
16. vi /etc/my.cnf
    
17. server-id = 30
    
18. replicate-same-server-id = 0
    
19. auto-increment-increment = 2
    
20. auto-increment-offset = 1
    
21. log-bin = master2-bin
    
22. log-bin-index = master2-bin.index
    
23. binlog-do-db = test
    
24. 
    
25. relay-log = slave-relay-bin
    
26. relay-log-index = slave-relay-bin.index
    
27. log-slave-updates

6、在master1和master2上查看pos点
master1：

1. mysql> show master status \G
   
2. *************************** 1. row ***************************
   
3.              File: master1-bin.000002
   
4.          Position: 120
   
5.      Binlog_Do_DB: test
   
6.  Binlog_Ignore_DB:
   
7. Executed_Gtid_Set:
   
8. 1 row in set (0.00 sec)

master2：

1. mysql> show master status \G
   
2. *************************** 1. row ***************************
   
3.              File: master2-bin.000002
   
4.          Position: 120
   
5.      Binlog_Do_DB: test
   
6.  Binlog_Ignore_DB:
   
7. Executed_Gtid_Set:
   
8. 1 row in set (0.00 sec)

7、在master2上导入数据

1. root@master2:/root# mysql -u root -p -D test < /tmp/test1.sql
   
2. Enter password:

8、启动同步
master1：

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

master2：

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 = 120;
   
8. Query OK, 0 rows affected, 2 warnings (0.01 sec)
   
9. 
   
10. mysql> start slave;
    
11. Query OK, 0 rows affected (0.00 sec)

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

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