真实的国产乱ⅩXXX66竹夫人,五月香六月婷婷激情综合,亚洲日本VA一区二区三区,亚洲精品一区二区三区麻豆

成都創(chuàng)新互聯(lián)網(wǎng)站制作重慶分公司

MySQL主從復(fù)制之延遲型數(shù)據(jù)復(fù)制

   讓MySQL拓?fù)渲械膹墓?jié)點(diǎn)延遲適當(dāng)?shù)臅r(shí)間，可以幫助避免在主節(jié)點(diǎn)上發(fā)生的災(zāi)難性的錯(cuò)誤。
   MASTER_DELAY這個(gè)屬性指定SQL_THREAD會在從節(jié)點(diǎn)上暫定多長時(shí)間，時(shí)間以秒為單位。默認(rèn)值為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;

點(diǎn)擊(此處)折疊或打開

創(chuàng)新互聯(lián)公司成都網(wǎng)站建設(shè)按需策劃，是成都網(wǎng)站營銷公司,為成都雨棚定制提供網(wǎng)站建設(shè)服務(wù),有成熟的網(wǎng)站定制合作流程，提供網(wǎng)站定制設(shè)計(jì)服務(wù)：原型圖制作、網(wǎng)站創(chuàng)意設(shè)計(jì)、前端HTML5制作、后臺程序開發(fā)等。成都網(wǎng)站維護(hù)熱線：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
下邊做一個(gè)測試：
在主節(jié)點(diǎn)對測試表做truncate

點(diǎn)擊(此處)折疊或打開

  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)
在從節(jié)點(diǎn)查看表信息

點(diǎn)擊(此處)折疊或打開

  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)
發(fā)現(xiàn)從節(jié)點(diǎn)此表數(shù)據(jù)剛開始并沒有被清理，20S之后數(shù)據(jù)才被清理掉。

在主庫刪除測試表

點(diǎn)擊(此處)折疊或打開

  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)
在從庫查看表是否存在

點(diǎn)擊(此處)折疊或打開

  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)
一段時(shí)間之后，測試表才被刪除。





分享文章：MySQL主從復(fù)制之延遲型數(shù)據(jù)復(fù)制
當(dāng)前路徑：http://weahome.cn/article/jdoohh.html

其他資訊

在線咨詢

微信咨詢

電話咨詢

028-86922220（工作日）

18980820575（7×24）

提交需求

返回頂部