ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > 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)

  7. mysql> create table t1 (id int primary key auto_increment,name varchar(50));
  8. Query OK, 0 rows affected (0.21 sec)

  10. mysql> select * from t1;
  11. Empty set (0.00 sec)

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

  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)

  4. mysql> exit
  5. Bye

  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)

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

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

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

  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

  11. relay-log = slave-relay-bin
  12. relay-log-index = slave-relay-bin.index
  13. log-slave-updates

  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

  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)

  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)

  10. mysql> start slave;
  11. Query OK, 0 rows affected (0.00 sec)

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

0
0
上一篇： percona-xtrabackup：增量备份
下一篇： MySQL双主双从复制
请登录后发表评论 登录
全部评论
javenzhen

注册时间：2011-01-27

  • 博文量
    41
  • 访问量
    124941

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593