☰

Probleme mit der MySQL-Replikation. Warum wachsen Binärprotokolle so schnell?

tag-icon • tag-icon mysql docker replication
Probleme mit der MySQL-Replikation. Warum wachsen Binärprotokolle so schnell?

Ich habe 2 MySQL-Server:
Masterserver: MySQL Version 5.7.14
Slaveserver: MySQL Version 5.7.14 im Docker-Container (vom offiziellen Docker-Hub).
GTID-Replikation.

Ich habe zwei Probleme:

  1. Binlogs wachsen sehr schnell. Ich habe eine Rotationsbegrenzung von 2 Tagen festgelegt, aber das hilft nicht – jeden Tag wächst der Binlog-Ordner mindestens zweimal (am ersten Tag 25 GB, am zweiten 50, am dritten 80 usw.).
  2. Der Abstand zwischen Slave-Server und Master-Server in Sekunden wird größer.

Server im lokalen Netzwerk (100 Mbit/s), SSD-Festplatten, Datenbankgröße ca. 40 GB.
Ich klone die Datenbank für den Slave mit Percona Xtrabackup.

Vielleicht ist die Serverkonfiguration nicht korrekt und ich muss etwas reparieren?

Dies ist die Master-Datei my.cnf:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

max_allowed_packet = 256M

log-error       = /var/log/mysql/error.log

symbolic-links=0

!includedir /etc/mysql/conf.d/

innodb_buffer_pool_size=15000M
innodb_buffer_pool_instances=1
sql_mode = ''

slow_query_log = 1
slow_query_log_file     = '/var/log/mysql/slow.log'
long_query_time = 1
log_queries_not_using_indexes = 0

#skip-grant-tables

default_week_format = 1

skip-name-resolve
sort_buffer_size=4M
join_buffer_size=4M
innodb_sort_buffer_size=4M
tmp_table_size=5000M
max_heap_table_size=5000M

[mysqld]
server-id = 1
binlog-format = row
gtid_mode=ON
enforce-gtid-consistency=ON
log-slave-updates
log_bin = /var/log/mysql/mysql-bin.log
performance_schema_max_digest_length = 8192
max_digest_length = 8192
max_binlog_size= 1G
expire_logs_days = 2
binlog-ignore-db=check_passport
replicate-ignore-db=check_passport

sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

key_buffer_size             = 16M
innodb_flush_method         = O_DIRECT
max_connections             = 200

#innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:5G

Dies ist meine.cnf des Slave-Servers

[client]
port                        = 3306
socket                      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket                      = /var/run/mysqld/mysqld.sock
nice                        = 0

[mysqld]
sql_mode                    = ""
character_set_server        = utf8
collation_server            = utf8_general_ci
user                        = mysql
pid-file                    = /var/run/mysqld/mysqld.pid
socket                      = /var/run/mysqld/mysqld.sock
port                        = 3306
basedir                     = /usr
datadir                     = /var/lib/mysql
tmpdir                      = /tmp
language                    = /usr/share/mysql/english
old_passwords               = 0
bind-address                = 127.0.0.1
skip-host-cache
skip-name-resolve

skip-external-locking

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

max_allowed_packet          = 256M
#key_buffer_size             = 16M
innodb_buffer_pool_size     = 2048M
innodb_log_file_size        = 256M
innodb_file_per_table       = 1
innodb_flush_method         = O_DIRECT
innodb_flush_log_at_trx_commit  = 1

max_connections             = 136

query_cache_size            = 0

slow_query_log              = /var/log/mysql/mysql-slow.log
long_query_time             = 1

expire_logs_days            = 2
max_binlog_size             = 1G

[mysqldump]
quick
quote-names
max_allowed_packet          = 16M


[mysqld]
server-id = 2
binlog-format = row
gtid_mode=ON
enforce-gtid-consistency=ON

relay-log = /var/log/mysql/mysql-relay-bin.log

log_bin = /var/log/mysql/mysql-bin.log

skip_slave_start

log_slave_updates = 0
read_only = ON

innodb_file_per_table = ON
#innodb_buffer_pool_size = 3G
innodb_flush_method         = O_DIRECT
innodb_flush_log_at_trx_commit  = 1

max_binlog_size = 1G

#max_relay_log_size = 1G
#relay_log_space_limit = 20G
relay_log_recovery = ON

expire_logs_days = 2

#slave-parallel-workers = 0

binlog-ignore-db=check_passport
replicate-ignore-db=check_passport

replicate-ignore-table=gfk.application_insurance
replicate-ignore-table=gfk.archive_client_building
replicate-ignore-table=gfk.comments_passwords
replicate-ignore-table=gfk.date_interval

Ausgabe vonSLAVE-STATUS ANZEIGEN\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.4
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.004720
          Read_Master_Log_Pos: 518759418
               Relay_Log_File: mysql-relay-bin.000188
                Relay_Log_Pos: 213202356
        Relay_Master_Log_File: mysql-bin.004703
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: check_passport
           Replicate_Do_Table: 
       Replicate_Ignore_Table: gfk.application_insurance,gfk.date_interval,gfk.archive_client_building,gfk.comments_passwords
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 213202143
              Relay_Log_Space: 18773097825
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /var/lib/master_cert/ca.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /var/lib/master_cert/client-cert.pem
            Master_SSL_Cipher: 
               Master_SSL_Key: /var/lib/master_cert/client-key.pem
        Seconds_Behind_Master: 14488
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 8ab33cfb-bb00-11e6-84cd-fa163eb352dd
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: System lock
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 8ab33cfb-bb00-11e6-84cd-fa163eb352dd:62276836-70424802
            Executed_Gtid_Set: 8ab33cfb-bb00-11e6-84cd-fa163eb352dd:1-67413143
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version:

Antwort1

Ist es für den Primärserver praktisch, dies zu haben binlog_ignore = check_passport? Wenn ja und wenn viel Verkehr über diese Datenbank herrscht, würde dies die Binärprotokolle „erheblich“ verkleinern.

Groß DELETEsoder UPDATEsnimmt viel Binlog-Speicherplatz in Anspruch, wodurch das Binlog riesig wird. (Beispiel: Aktualisieren jeder Zeile einer Tabelle mit einer Million Zeilen.) Geben Sie Einzelheiten an; möglicherweise gibt es wichtige Workarounds.

Wie viele Replik-Threads werden auf der Replik ausgeführt? Dies hat Auswirkungen Seconds_behind_master. (Mehr Threads führen bis zu einem bestimmten Grenzwert wahrscheinlich zu einem Rückgang.)

innodb_flush_log_at_trx_commit = 1--> ändern auf 2. Dadurch geht etwas Robustheit verloren (bei einem Absturz), aber der Durchsatz wird verbessert.

Wie viel RAM ist in jedem? Ich sehe einen größeren Pufferpool im primären. Normalerweisedie Replik sollte die leistungsstärkere Maschine sein.

100 Mbit/s scheinen wenig zu sein. Können Sie überprüfen, ob es gesättigt ist?

Täglich frische Daten

Hier ist eine effiziente Möglichkeit zum Ersetzenalledie Daten in einer Tabelle:

  1. CREATE TABLE new LIKE real;
  2. LOAD DATA INFILE INTO new ... - oder was auch immer nötig ist
  3. ``Tabelle real in alt umbenennen, neu in REAL;
  4. DROP TABLE old;

Schritt 2 ist der langsamste Teil.
Schritt 3 ist sehr schnell; es ist das einzige Mal, dass Ihre Tabelle nicht verfügbar ist.
Kein Binlog-intensives UPDATEoder DELETE…

Antwort2

Ich habe eine Lösung für meinen Fall gefunden

Zuerst suche ich, welche Datenbanken und Tabellen so große Binärprotokolle generieren (welche Tabellen keine Primärschlüssel oder eindeutigen Schlüssel haben):

SELECT t.table_schema,t.table_name,engine
FROM information_schema.tables t
INNER JOIN information_schema .columns c
on t.table_schema=c.table_schema
and t.table_name=c.table_name
and t.table_schema not in ('performance_schema','information_schema','mysql')
GROUP BY t.table_schema,t.table_name
HAVING sum(if(column_key in ('PRI','UNI'), 1,0)) =0;

Dann:

  1. Meine Netzwerkgeschwindigkeit beträgt bereits 1 GBit/s, also ist alles in Ordnung.
  2. Ich schließe die Datenbank, die jeden Tag neu erstellt wird, von der Replikation und Protokollierung aus.
  3. Ich erhöhe den RAM auf dem Slave auf 23 GB (wie auf dem Master).
  4. Ich verschiebe Binärprotokolle auf dem Slave von der SSD auf die Festplatte – es ist ok – die Geschwindigkeit der Festplatte reicht aus.
  5. Ich setze log_slave_updates = 1, weil mein Replikationsschema Master>Slave>Slave ist.

Und diese Schritte lösen meine Probleme! Derzeit werden meine Binärprotokolle nicht größer als 10 GB.

verwandte Informationen