ITPub博客

首页 > 数据库 > MySQL > MySQL主主复制

MySQL主主复制

原创 MySQL 作者:javenzhen 时间:2015-08-10 15:57:54 0 删除 编辑
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. mysql> create table t1 (id int primary key auto_increment,name varchar(50));
  7. Query OK, 0 rows affected (0.21 sec)

  8. mysql> select * from t1;
  9. Empty set (0.00 sec)

  10. mysql> insert into t1 (name) values ('aaaaa');
  11. Query OK, 1 row affected (0.06 sec)

  12. mysql> select * from t1;
  13. +----+-------+
  14. | id | name |
  15. +----+-------+
  16. | 1 | aaaaa |
  17. +----+-------+
  18. 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. mysql> exit
  4. Bye

  5. root@master1:/root# mysqldump -u root -p -c test > /tmp/test1.sql
  6. Enter password:
  7. root@master1:/root# scp /tmp/test1.sql 10.10.10.30:/tmp/
  8. root@10.10.10.30
  9. 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. mysql> grant replication slave on *.* to rep_user identified by 'rep_user';
  4. Query OK, 0 rows affected (0.00 sec)

  5. mysql> grant replication client on *.* to rep_user identified by 'rep_user';
  6. Query OK, 0 rows affected (0.00 sec)

  7. mysql> grant super on *.* to rep_user identified by 'rep_user';
  8. Query OK, 0 rows affected (0.00 sec)

  9. mysql> show grants for rep_user;
  10. +------------------------------------------------------------------------------------------------------------------------------------------------+
  11. | Grants for rep_user@% |
  12. +------------------------------------------------------------------------------------------------------------------------------------------------+
  13. | GRANT SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'rep_user'@'%' IDENTIFIED BY PASSWORD '*66275EE2A4400687CE14AD3B2CEE003B1B753575' |
  14. +------------------------------------------------------------------------------------------------------------------------------------------------+
  15. 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. relay-log = slave-relay-bin
  11. relay-log-index = slave-relay-bin.index
  12. log-slave-updates

  13. master2:
  14. vi /etc/my.cnf
  15. server-id = 30
  16. replicate-same-server-id = 0
  17. auto-increment-increment = 2
  18. auto-increment-offset = 1
  19. log-bin = master2-bin
  20. log-bin-index = master2-bin.index
  21. binlog-do-db = test

  22. relay-log = slave-relay-bin
  23. relay-log-index = slave-relay-bin.index
  24. 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. mysql> start slave;
  10. 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. mysql> start slave;
  10. Query OK, 0 rows affected (0.00 sec)

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

请登录后发表评论 登录
全部评论

注册时间:2011-01-27

  • 博文量
    41
  • 访问量
    89672