分类: MySQL

2018-06-13 09:41:39

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

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

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

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

  9. mysql> truncate table tb_admin_bak;
  10. 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)

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

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

在主库删除测试表

点击(此处)折叠或打开

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

  3. mysql> show tables;
  4. +----------------+
  5. | Tables_in_test |
  6. +----------------+
  7. | tb_admin |
  8. +----------------+
  9. 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)

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

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

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

  33. mysql> show tables;
  34. +----------------+
  35. | Tables_in_test |
  36. +----------------+
  37. | tb_admin |
  38. | tb_admin_bak |
  39. +----------------+
  40. 2 rows in set (0.00 sec)

  41. mysql> show tables;
  42. +----------------+
  43. | Tables_in_test |
  44. +----------------+
  45. | tb_admin |
  46. | tb_admin_bak |
  47. +----------------+
  48. 2 rows in set (0.00 sec)

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

  57. mysql> show tables;
  58. +----------------+
  59. | Tables_in_test |
  60. +----------------+
  61. | tb_admin |
  62. +----------------+
  63. 1 row 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)
一段时间之后,测试表才被删除。




阅读(121) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册