亚洲激情专区-91九色丨porny丨老师-久久久久久久女国产乱让韩-国产精品午夜小视频观看

溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

MySQL主從復制之延遲型數據復制

發布時間：2020-08-12 21:47:20 來源：ITPUB博客 閱讀：146 作者：StevenBeijing 欄目：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;

點擊(此處)折疊或打開

  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)
一段時間之后，測試表才被刪除。
向AI問一下細節

免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。

AI
助
手
莎车县| 湄潭县| 禄劝| 七台河市| 开封市| 玉田县| 磐安县| 延安市| 囊谦县| 昌都县| 定边县| 广昌县| 太谷县| 德阳市| 光泽县| 拜城县| 阳山县| 柳林县| 黔西县| 石台县| 若尔盖县| 赤峰市| 南部县| 阿克陶县| 灵璧县| 桐柏县| 思南县| 凯里市| 治多县| 崇文区| 西盟| 申扎县| 洱源县| 祁东县| 衡东县| 霍山县| 收藏| 马山县| 柏乡县| 商南县| 东阳市|