Κοινή χρήση τεχνολογίας

Λεπτομερής εξήγηση του MySQL Binlog: βασική τεχνολογία για τη βελτίωση της αξιοπιστίας της βάσης δεδομένων

2024-07-12

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

1. Εισαγωγή

1.1 Τι είναι η MySQL Αποθήκη Κούτσουρο;

MySQL Bin Log(Δυάδικος Κούτσουρο, δυαδικό αρχείο καταγραφής) είναι ένα αρχείο καταγραφής της βάσης δεδομένων MySQL που καταγράφει όλες τις τροποποιήσεις που πραγματοποιούνται στη βάση δεδομένωνDMLλειτουργίες (όπως INSERT, UPDATE, DELETE, κ.λπ.), αλλάΕξαιρείται η επιλογή SELECTΠερίμενεDQL Λειτουργία μόνο για ανάγνωση. Το Bin Log είναι ένα σημαντικό εργαλείο για την MySQL για την επίτευξη αναπαραγωγής, ανάκτησης και ελέγχου. Για λεπτομέρειες, δείτε:DDL, DML, DQL και DCL στη MySQL

1.2 Τα σενάρια λειτουργίας και εφαρμογής του Bin Log

Οι κύριες λειτουργίες του Bin Log περιλαμβάνουν: αναπαραγωγή master-slave, ανάκτηση δεδομένων, δημιουργία αντιγράφων ασφαλείας δεδομένων και συνδρομή δεδομένων

Σενάρια εφαρμογήςέννοιαΥπόθεση
αντιγραφή master-slaveΗ κύρια βιβλιοθήκη στέλνει τις εγγραφές λειτουργίας ενημέρωσης στο binlog στη εξαρτημένη βιβλιοθήκη και η slave βιβλιοθήκη διαβάζει το binlog και εκτελεί εντολές SQL.Πλατφόρμα ηλεκτρονικού εμπορίου: Η κύρια βάση δεδομένων καταγράφει λειτουργίες παραγγελιών για binlog και συγχρονίζει δεδομένα παραγγελιών από τη βάση δεδομένων για να διατηρήσει τη συνοχή όλων των κόμβων.
Ανάκτηση δεδομένωνΧρησιμοποιήστε εγγραφές binlog για να εκτελέσετε αντίστροφα προτάσεις SQL για ανάκτηση δεδομένωνΧρηματοοικονομικό σύστημα: Οι διαχειριστές ανακτούν τυχαία διαγραμμένες εγγραφές συναλλαγών αναλύοντας το binlog για να διασφαλίσουν την ακεραιότητα των δεδομένων και να αποφύγουν οικονομικές απώλειες.
αντίγραφο ασφαλείας δεδομένωνΤο Binlog χρησιμοποιείται για σταδιακή δημιουργία αντιγράφων ασφαλείας, εξοικονομώντας χρόνο και κόστος χώρου δημιουργίας αντιγράφων ασφαλείαςΠλατφόρμα μέσων κοινωνικής δικτύωσης: Δημιουργήστε αντίγραφα ασφαλείας των αρχείων binlog τακτικά κάθε μέρα για γρήγορη επαναφορά στην πιο πρόσφατη κατάσταση χωρίς να χρειάζεται να δημιουργήσετε πλήρη αντίγραφα ασφαλείας ολόκληρης της βάσης δεδομένων.
Συνδρομή δεδομένωνΧρησιμοποιήστε το binlog για να παρακολουθείτε τις λειτουργίες ενημέρωσης της βάσης δεδομένων σε πραγματικό χρόνοΑνάλυση δεδομένων σε πραγματικό χρόνο εταιρειών λιανικής: Με την ανάλυση του binlog, η λειτουργία εισαγωγής ρεκόρ πωλήσεων καταγράφεται και μεταφέρεται στην πλατφόρμα ανάλυσης δεδομένων σε πραγματικό χρόνο για ανάλυση τάσεων πωλήσεων.

Μέσα από αυτές τις συγκεκριμένες περιπτώσεις, μπορούμε να δούμε πιο καθαρά τη σημασία και την ποικιλομορφία του MySQL binlog σε πρακτικές εφαρμογές. Η σωστή χρήση της λειτουργίας binlog μπορεί να βελτιώσει σημαντικά την αξιοπιστία του συστήματος, την ικανότητα ανάκτησης και την ταχύτητα απόκρισης της επιχείρησης.

2. Βασικές έννοιες του Bin Log

2.1 Αρχή λειτουργίας του Bin Log

Όταν ο διακομιστής MySQL ενεργοποιεί τη συνάρτηση καταγραφής απορριμμάτων, όλες οι λειτουργίες τροποποίησης στη βάση δεδομένων θα είναιρεκόρ εκδήλωσης στο αρχείο καταγραφής κάδου. Αυτά τα συμβάντα αποθηκεύονται με σειρά εκτέλεσης, σχηματίζοντας μια ακολουθία καταγραφής συνεχούς λειτουργίας. Αυτά τα συμβάντα μπορούν να αναπαραχθούν ξανά για να αναδημιουργηθεί η κατάσταση της βάσης δεδομένων όταν τα δεδομένα πρέπει να αποκατασταθούν ή να αντιγραφούν.

2.2 Τρεις μορφές αρχείου καταγραφής κάδου

Το MySQL Bin Log έχει τρεις μορφές εγγραφής:
Ακολουθούν οι τρεις μορφές εγγραφής του MySQL Bin Log οργανωμένες σε μορφή πίνακα:

μορφή εγγραφήςπεριγράφωπλεονέκτημαέλλειψη
Καταγραφή βάσει δηλώσεων (SBL)Καταγράψτε την ίδια την εκτελεσμένη δήλωση SQLΟ όγκος καταγραφής είναι μικρός και κατάλληλος για απλές λειτουργίες SQL.Η συνέπεια των δεδομένων ενδέχεται να μην είναι εγγυημένη σε ορισμένες περιπτώσεις, όπως οι μη ντετερμινιστικές συναρτήσεις (όπως το NOW())
Καταγραφή βάσει σειράς (RBL)Καταγράψτε τις συγκεκριμένες αλλαγές κάθε σειράς δεδομένωνΠιο ακριβές, κατάλληλο για σύνθετες λειτουργίες και αντιγραφήΜεγάλος όγκος αρχείων καταγραφής, μεγάλος δίσκος και επιβάρυνση δικτύου
Μικτή καταγραφή (ML)Εναλλαγή μεταξύ των λειτουργιών Statement και Row σύμφωνα με συγκεκριμένες συνθήκες.Λαμβάνοντας υπόψη τα πλεονεκτήματα και των δύοΣχετικά πολύπλοκο στην εφαρμογή και διαχείριση

3. Διαμορφώστε και διαχειριστείτε το αρχείο καταγραφής κάδου

3.1 Ενεργοποίηση Bin Log

mysql> show variables like "%log_bin%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set, 1 warning (0.00 sec)


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

Για να ενεργοποιήσετε το Bin Log, πρέπει να διαμορφώσετε το αρχείο διαμόρφωσης MySQL (συνήθωςmy.cnfήmy.ini) και εκτελέστε την ακόλουθη διαμόρφωση:

[mysqld]
log-bin=mysql-bin
server-id=1
  • 1
  • 2
  • 3

Αφού τροποποιήσετε τη διαμόρφωση, επανεκκινήστε το mysql.υλοποιώ, εφαρμόζωSHOW VARIABLES LIKE 'log_bin'; Η τιμή τιμής είναι ΟΝ.

mysql> show variables like "%log_bin%";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

+---------------------------------+--------------------------------------------------------------------------+
| Variable_name                   | Value                                                                    |
+---------------------------------+--------------------------------------------------------------------------+
| log_bin                         | ON                                                                       |
| log_bin_basename                | C:UsershiszmMySQL5.7.26databinlog       |
| log_bin_index                   | C:UsershiszmMySQL5.7.26databinlog.index |
| log_bin_trust_function_creators | OFF                                                                      |
| log_bin_use_v1_row_events       | OFF                                                                      |
| sql_log_bin                     | ON                                                                       |
+---------------------------------+--------------------------------------------------------------------------+
6 rows in set, 1 warning (0.01 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

3.2 Διαμόρφωση παραμέτρων καταγραφής κάδου

Οι παράμετροι διαμόρφωσης Κοινού αρχείου καταγραφής κάδου περιλαμβάνουν:

  • log_bin: Ενεργοποίηση Bin Log.
  • server_id: Μοναδικό αναγνωριστικό διακομιστή, που χρησιμοποιείται για αναπαραγωγή.
  • binlog_format: Ορισμός της μορφής του αρχείου καταγραφής κάδου (ΔΗΛΩΣΗ, ΣΕΙΡΑ, ΜΙΚΤΗ).
  • expire_logs_days: Ορίστε τον αριθμό των ημερών για αυτόματη λήξη και διαγραφή αρχείων Bin Log.
  • max_binlog_size: Ορίστε το μέγιστο μέγεθος ενός αρχείου καταγραφής Bin.

3.3 Διαχείριση αρχείων καταγραφής Bin

Συνήθεις εντολές για τη διαχείριση αρχείων καταγραφής Bin:

  • Προβολή λίστας αρχείων καταγραφής κάδου

    SHOW BINARY LOGS;
    
    • 1
  • Προβολή μεγέθους αρχείου καταγραφής κάδου

    SHOW MASTER STATUS;
    
    • 1
  • Διαγράψτε τα παλιά αρχεία καταγραφής κάδου

    PURGE BINARY LOGS TO 'mysql-bin.000010';
    
    • 1

    ή:

    PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
    
    • 1

3.4 Προβολή περιεχομένου αρχείου καταγραφής κάδου


-- 创建一个名为 'simple_table' 的表
CREATE TABLE `simple_table` (
  `item_id` int(11) NOT NULL,  -- 项目编号
  `value` int(11) DEFAULT NULL,  -- 值
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 最后更新时间
  PRIMARY KEY (`item_id`),  -- 设置项目编号为主键
  KEY `value_index` (`value`),  -- 为值字段创建索引
  KEY `update_time_index` (`last_updated`)  -- 为最后更新时间字段创建索引
) ENGINE=InnoDB;  -- 使用InnoDB存储引擎

-- 插入数据,将日期设置为当前日期
insert into `simple_table` values(1, 1, '2024-07-07');
insert into `simple_table` values(2, 2, '2024-07-07');
insert into `simple_table` values(3, 3, '2024-07-07');
insert into `simple_table` values(4, 4, '2024-07-07');
insert into `simple_table` values(5, 5, '2024-07-07');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

Χρησιμοποιήστε το εργαλείο mysqlbinlog για να προβάλετε τα περιεχόμενα του Bin Log:

mysql> show binary logs;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    4
Current database: *** NONE ***

+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |      2411 |
+---------------+-----------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

Αν το ανοίξετε απευθείας, θα αλλοιωθεί.

mysql> show binlog events in 'binlog.000001' from 0 limit 0,4G;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: *** NONE ***

*************************** 1. row ***************************
   Log_name: binlog.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.26-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: binlog.000001
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info:
*************************** 3. row ***************************
   Log_name: binlog.000001
        Pos: 154
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 219
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: binlog.000001
        Pos: 219
 Event_type: Query
  Server_id: 1
End_log_pos: 765
       Info: use `d`; -- 创建一个名为 'simple_table' 的表
CREATE TABLE `simple_table` (
  `item_id` int(11) NOT NULL,  -- 项目编号
  `value` int(11) DEFAULT NULL,  -- 值
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 最后更新时间
  PRIMARY KEY (`item_id`),  -- 设置项目编号为主键
  KEY `value_index` (`value`),  -- 为值字段创建索引
  KEY `update_time_index` (`last_updated`)  -- 为最后更新时间字段创建索引
) ENGINE=InnoDB
4 rows in set (0.01 sec)

ERROR:
No query specified

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47

3.5 Χρήση του εργαλείου mysqlbinlog

Το mysqlbinlog είναι ένα εργαλείο γραμμής εντολών που χρησιμοποιείται για την ανάλυση αρχείων Bin Log. Οι κοινές επιλογές περιλαμβάνουν:

  • --start-datetime: Καθορίστε την ώρα έναρξης της ανάλυσης.
  • --stop-datetime: Καθορίστε την ώρα που τελειώνει η ανάλυση.
  • --start-position: Καθορίστε τη θέση όπου ξεκινά η ανάλυση.
  • --stop-position: Καθορίστε τη θέση όπου τελειώνει η ανάλυση.

Για παράδειγμα, για να προβάλετε το αρχείο καταγραφής κάδου για μια συγκεκριμένη χρονική περίοδο:

mysqlbinlog --start-datetime="2024-07-01 00:00:00" --stop-datetime="2024-07-01 12:00:00" binlog.000001
  • 1

3.6 Ανάλυση και επανάληψη του Bin Log

Τα συμβάντα Bin Log μπορούν να αναπαραχθούν ξανά στον διακομιστή MySQL για ανάκτηση δεδομένων:

mysqlbin logbinlog.000001 | mysql -u root -p
  • 1

4. Εφαρμογή Bin Log στην αναπαραγωγή

4.1 Αρχή αντιγραφής Master-slave

Η βασική αρχή της αναπαραγωγής master-slave της MySQL είναι ότι η κύρια βάση δεδομένων καταγράφει Bin Log και η βάση δεδομένων slave διαβάζει και αναπαράγει αυτά τα αρχεία καταγραφής για να επιτύχει συγχρονισμό δεδομένων. Τα συγκεκριμένα βήματα είναι τα εξής:

  1. Η κύρια βιβλιοθήκη εκτελεί λειτουργίες εγγραφής και καταγράφει αυτές τις λειτουργίες στο αρχείο καταγραφής Bin.
  2. Η βοηθητική βιβλιοθήκη συνδέεται με την κύρια βιβλιοθήκη, διαβάζει το αρχείο καταγραφής και το εφαρμόζει στα δικά της δεδομένα.

4.2 Διαμόρφωση αντιγραφής master-slave

  1. Διαμόρφωση της κύριας βιβλιοθήκης

    [mysqld]
    log-bin=mysql-bin
    server-id=1
    
    • 1
    • 2
    • 3
  2. Διαμόρφωση υποτελούς βιβλιοθήκης

    [mysqld]
    server-id=2
    relay-log=relay-bin
    
    • 1
    • 2
    • 3
  3. Δημιουργήστε έναν χρήστη αναπαραγωγής στην κύρια βάση δεδομένων

    CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
    GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
    
    • 1
    • 2
  4. Ρύθμιση παραμέτρων αναπαραγωγής στη βάση δεδομένων σκλάβων

    CHANGE MASTER TO
        MASTER_HOST='主库IP',
        MASTER_USER='replica',
        MASTER_PASSWORD='password',
        MASTER_LOG_FILE='binlog.000001',
        MASTER_LOG_POS=0;
    START SLAVE;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
  5. Ελέγξτε την κατάσταση αναπαραγωγής

    SHOW SLAVE STATUSG
    
    • 1

4.3 Παρακολούθηση και διαχείριση αναπαραγωγής

Η κατάσταση αναπαραγωγής μπορεί να παρακολουθηθεί με την ακόλουθη εντολή:

SHOW SLAVE STATUSG
  • 1

Κοινές επεξηγήσεις πεδίων κατάστασης:

  • Slave_IO_Running: Κατάσταση νήματος IO.
  • Slave_SQL_Running:Κατάσταση νήματος SQL.
  • Seconds_Behind_Master: Ο χρόνος που η slave βιβλιοθήκη υστερεί σε σχέση με την κύρια βιβλιοθήκη.

4.4 Αύξουσα δημιουργία αντιγράφων ασφαλείας

Το επαυξητικό αντίγραφο ασφαλείας σημαίνει τη δημιουργία αντιγράφων ασφαλείας όλων των αλλαγών από το τελευταίο πλήρες αντίγραφο ασφαλείας ή το τελευταίο σταδιακό αντίγραφο ασφαλείας. Η σταδιακή δημιουργία αντιγράφων ασφαλείας μπορεί να επιτευχθεί χρησιμοποιώντας το Bin Log.

  1. Εκτελέστε ένα πλήρες αντίγραφο ασφαλείας

    mysqldump --all-databases --master-data=2 > full_backup.sql
    
    • 1
  2. Καταγράψτε την τρέχουσα θέση καταγραφής κάδου

    Αναζητήστε τις ακόλουθες γραμμές στο πλήρες αρχείο αντιγράφου ασφαλείας:

    -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=12345;
    
    • 1
  3. Αρχείο καταγραφής αντιγράφων ασφαλείας

    mysqlbinlog --start-position=12345 binlog.000001 > incremental_backup.sql
    
    • 1

4.5 Ανάκτηση με βάση το αρχείο καταγραφής

  1. Επαναφορά πλήρους αντιγράφου ασφαλείας

    mysql < full_backup.sql
    
    • 1
  2. Εφαρμογή σταδιακής δημιουργίας αντιγράφων ασφαλείας

    mysql < incremental_backup.sql
    
    • 1

5. Γράψτε στο τέλος

MySQL Bin Log Είναι ένα ισχυρό εργαλείο που χρησιμοποιείται ευρέως σε σενάρια όπως η ανάκτηση δεδομένων, η αναπαραγωγή και ο έλεγχος.Με σωστή διαμόρφωση και χρήσηBin Log , το οποίο μπορεί να βελτιώσει σημαντικά την αξιοπιστία και τη διαθεσιμότητα της βάσης δεδομένων MySQL. Σε πρακτικές εφαρμογές, είναι σημαντικό για τους διαχειριστές και τους προγραμματιστές βάσεων δεδομένων να κατακτήσουν τις δεξιότητες χρήσης και τις μεθόδους βελτιστοποίησης του Bin Log.