天达云 科技型企业 | 亚太互联网络信息中心(APNIC)成员 | 注册免费体验
天达云首页 > 互联网学习教程 > MySQL数据库 > MySQL主从复制之延迟型数据复制

MySQL主从复制之延迟型数据复制
更新：HHH   时间：2023-1-7

   让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;

点击(此处)折叠或打开

  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数据库教程...
400-805-1963
7 * 24小时全天全国服务热线400电话
天达云·专业的云计算服务平台 全国400服务电话：400-805-1963
Copyright © 2011-2026 Tdyun.com. All Rights Reserved. 天达云 版权所有 粤ICP备18083267号-7