资讯

精准传达 • 有效沟通

从品牌网站建设到网络营销策划，从策略到执行的一站式服务

网站建设 >

查看其它板块

MySQL主从复制之延迟型数据复制

   让MySQL拓扑中的从节点延迟适当的时间，可以帮助避免在主节点上发生的灾难性的错误。
   MASTER_DELAY这个属性指定SQL_THREAD会在从节点上暂定多长时间，时间以秒为单位。默认值为0，上限为68年。
mysql> stop slave;
Query OK, 0 rows affected (0.06 sec)
mysql> change master to master_delay=20;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;

点击(此处)折叠或打开

创新互联公司成都网站建设按需策划，是成都网站营销公司,为成都雨棚定制提供网站建设服务,有成熟的网站定制合作流程，提供网站定制设计服务：原型图制作、网站创意设计、前端HTML5制作、后台程序开发等。成都网站维护热线：18982081108

  1. mysql> stop slave;
  2. Query OK, 0 rows affected (0.01 sec)

  4. mysql> change master to master_delay=20 ;
  5. Query OK, 0 rows affected (0.02 sec)

  7. mysql> start slave;
  8. Query OK, 0 rows affected (0.02 sec)

  10. mysql> show slave status \G
  11. *************************** 1. row ***************************
  12.                Slave_IO_State: Waiting for master to send event
  13.                   Master_Host: 192.168.8.57
  14.                   Master_User: repl
  15.                   Master_Port: 3306
  16.                 Connect_Retry: 10
  17.               Master_Log_File: mysql-bin.000021
  18.           Read_Master_Log_Pos: 154
  19.                Relay_Log_File: relay-log.000002
  20.                 Relay_Log_Pos: 320
  21.         Relay_Master_Log_File: mysql-bin.000021
  22.              Slave_IO_Running: Yes
  23.             Slave_SQL_Running: Yes
  24.               Replicate_Do_DB:
  25.           Replicate_Ignore_DB:
  26.            Replicate_Do_Table:
  27.        Replicate_Ignore_Table:
  28.       Replicate_Wild_Do_Table:
  29.   Replicate_Wild_Ignore_Table:
  30.                    Last_Errno: 0
  31.                    Last_Error:
  32.                  Skip_Counter: 0
  33.           Exec_Master_Log_Pos: 154
  34.               Relay_Log_Space: 521
  35.               Until_Condition: None
  36.                Until_Log_File:
  37.                 Until_Log_Pos: 0
  38.            Master_SSL_Allowed: No
  39.            Master_SSL_CA_File: /usr/local/mysql/certs/ca-cert.pem
  40.            Master_SSL_CA_Path:
  41.               Master_SSL_Cert: /usr/local/mysql/certs/client-cert.pem
  42.             Master_SSL_Cipher:
  43.                Master_SSL_Key: /usr/local/mysql/certs/client-key.pem
  44.         Seconds_Behind_Master: 0
  45. Master_SSL_Verify_Server_Cert: No
  46.                 Last_IO_Errno: 0
  47.                 Last_IO_Error:
  48.                Last_SQL_Errno: 0
  49.                Last_SQL_Error:
  50.   Replicate_Ignore_Server_Ids:
  51.              Master_Server_Id: 1
  52.                   Master_UUID: 9ad24233-aeef-11e7-aa1b-080027768e58
  53.              Master_Info_File: mysql.slave_master_info
  54.                     SQL_Delay: 20
  55.           SQL_Remaining_Delay: NULL
  56.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
  57.            Master_Retry_Count: 86400
  58.                   Master_Bind:
  59.       Last_IO_Error_Timestamp:
  60.      Last_SQL_Error_Timestamp:
  61.                Master_SSL_Crl:
  62.            Master_SSL_Crlpath:
  63.            Retrieved_Gtid_Set:
  64.             Executed_Gtid_Set:
  65.                 Auto_Position: 0
  66.          Replicate_Rewrite_DB:
  67.                  Channel_Name:
  68.            Master_TLS_Version:
  69. 1 row in set (0.00 sec)
    可以看到SQL_Delay: 20
下边做一个测试：
在主节点对测试表做truncate

点击(此处)折叠或打开

  1. mysql> show tables;
  2. +----------------+
  3. | Tables_in_test |
  4. +----------------+
  5. | tb_admin |
  6. | tb_admin_bak |
  7. +----------------+
  8. 2 rows in set (0.00 sec)

  10. mysql> truncate table tb_admin_bak;
  11. Query OK, 0 rows affected (0.02 sec)
在从节点查看表信息

点击(此处)折叠或打开

  1. mysql> select count(*) from tb_admin_bak;
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. | 1 |
  6. +----------+
  7. 1 row in set (0.02 sec)

  9. mysql> select count(*) from tb_admin_bak;
  10. +----------+
  11. | count(*) |
  12. +----------+
  13. | 1 |
  14. +----------+
  15. 1 row in set (0.00 sec)

  17. mysql> show full processlist \G
  18. *************************** 1. row ***************************
  19.      Id: 3
  20.    User: root
  21.    Host: localhost
  22.      db: test
  23. Command: Query
  24.    Time: 0
  25.   State: starting
  26.    Info: show full processlist
  27. *************************** 2. row ***************************
  28.      Id: 8
  29.    User: system user
  30.    Host:
  31.      db: NULL
  32. Command: Connect
  33.    Time: 334
  34.   State: Waiting for master to send event
  35.    Info: NULL
  36. *************************** 3. row ***************************
  37.      Id: 9
  38.    User: system user
  39.    Host:
  40.      db: NULL
  41. Command: Connect
  42.    Time: 100
  43.   State: Waiting until MASTER_DELAY seconds after master executed event
  44.    Info: NULL
  45. 3 rows in set (0.00 sec)
  46. mysql> select count(*) from tb_admin_bak;
  47. +----------+
  48. | count(*) |
  49. +----------+
  50. | 0 |
  51. +----------+
  52. 1 row in set (0.00 sec)
发现从节点此表数据刚开始并没有被清理，20S之后数据才被清理掉。

在主库删除测试表

点击(此处)折叠或打开

  1. mysql> drop table tb_admin_bak;
  2. Query OK, 0 rows affected (0.06 sec)

  4. mysql> show tables;
  5. +----------------+
  6. | Tables_in_test |
  7. +----------------+
  8. | tb_admin |
  9. +----------------+
  10. 1 row in set (0.00 sec)
在从库查看表是否存在

点击(此处)折叠或打开

  1. mysql> show tables;
  2. +----------------+
  3. | Tables_in_test |
  4. +----------------+
  5. | tb_admin |
  6. | tb_admin_bak |
  7. +----------------+
  8. 2 rows in set (0.00 sec)

  10. mysql> show tables;
  11. +----------------+
  12. | Tables_in_test |
  13. +----------------+
  14. | tb_admin |
  15. | tb_admin_bak |
  16. +----------------+
  17. 2 rows in set (0.00 sec)

  19. mysql> show tables;
  20. +----------------+
  21. | Tables_in_test |
  22. +----------------+
  23. | tb_admin |
  24. | tb_admin_bak |
  25. +----------------+
  26. 2 rows in set (0.00 sec)

  28. mysql> show tables;
  29. +----------------+
  30. | Tables_in_test |
  31. +----------------+
  32. | tb_admin |
  33. | tb_admin_bak |
  34. +----------------+
  35. 2 rows in set (0.00 sec)

  37. mysql> show tables;
  38. +----------------+
  39. | Tables_in_test |
  40. +----------------+
  41. | tb_admin |
  42. | tb_admin_bak |
  43. +----------------+
  44. 2 rows in set (0.00 sec)

  46. mysql> show tables;
  47. +----------------+
  48. | Tables_in_test |
  49. +----------------+
  50. | tb_admin |
  51. | tb_admin_bak |
  52. +----------------+
  53. 2 rows in set (0.00 sec)

  55. mysql> show tables;
  56. +----------------+
  57. | Tables_in_test |
  58. +----------------+
  59. | tb_admin |
  60. | tb_admin_bak |
  61. +----------------+
  62. 2 rows in set (0.00 sec)

  64. mysql> show tables;
  65. +----------------+
  66. | Tables_in_test |
  67. +----------------+
  68. | tb_admin |
  69. +----------------+
  70. 1 row in set (0.00 sec)

  72. mysql> show tables;
  73. +----------------+
  74. | Tables_in_test |
  75. +----------------+
  76. | tb_admin |
  77. +----------------+
  78. 1 row in set (0.00 sec)
一段时间之后，测试表才被删除。





标题名称：MySQL主从复制之延迟型数据复制
当前链接：http://cdkjz.cn/article/jdoohh.html
返回首页 了解更多建站资讯

相关资讯

多年建站经验

多一份参考，总有益处

联系快上网，免费获得专属《策划方案》及报价

咨询相关问题或预约面谈，可以通过以下方式与我们联系

大客户专线   成都：13518219792   座机：028-86922220

在线咨询 提交需求
友情链接 交换友情链接
德阳定制网站建设成都网站推广广安网站建设衣柜书柜酒柜定制重庆托管服务器混凝土密封固化剂西部信息机房成都免费建网站重庆建站软文推广

成都网站建设公司地址：成都市青羊区太升南路288号锦天国际A座10层 建设咨询028-86922220

成都快上网科技有限公司-四川网站建设设计公司 | 蜀ICP备19037934号 Copyright 2020,ALL Rights Reserved cdkjz.cn | 成都网站建设 | © Copyright 2020版权所有.

专家团队为您提供成都网站建设,成都网站设计,成都品牌网站设计,成都营销型网站制作等服务,成都建网站就找快上网！ | 成都网站建设哪家好？ | 网站建设地图