Geen cache-versie.

Caching uitgeschakeld. Standaardinstelling voor deze pagina:ingeschakeld (code DEF204)
Als het scherm te langzaam is, kunt u de gebruikersmodus uitschakelen om de cacheversie te bekijken.

Rechercher dans le manuel MySQL

17.1.7.1 Checking Replication Status

Inhoudsopgave

The most common task when managing a replication process is to ensure that replication is taking place and that there have been no errors between the slave and the master.

The SHOW SLAVE STATUS statement, which you must execute on each slave, provides information about the configuration and status of the connection between the slave server and the master server. From MySQL 5.7, the Performance Schema has replication tables that provide this information in a more accessible form. See Section 26.12.11, “Performance Schema Replication Tables”.

The SHOW STATUS statement also provided some information relating specifically to replication slaves. From MySQL 5.7, the following status variables previously monitored using SHOW STATUS were deprecated and moved to the Performance Schema replication tables:

The replication heartbeat information shown in the Performance Schema replication tables lets you check that the replication connection is active even if the master has not sent events to the slave recently. The master sends a heartbeat signal to a slave if there are no updates to, and no unsent events in, the binary log for a longer period than the heartbeat interval. The MASTER_HEARTBEAT_PERIOD setting on the master (set by the CHANGE MASTER TO statement) specifies the frequency of the heartbeat, which defaults to half of the connection timeout interval for the slave (slave_net_timeout). The replication_connection_status Performance Schema table shows when the most recent heartbeat signal was received by a replication slave, and how many heartbeat signals it has received.

If you are using the SHOW SLAVE STATUS statement to check on the status of an individual slave, the statement provides the following information:

  1. mysql> SHOW SLAVE STATUS\G
  2. *************************** 1. row ***************************
  3.                Slave_IO_State: Waiting for master to send event
  4.                   Master_Host: master1
  5.                   Master_User: root
  6.                   Master_Port: 3306
  7.                 Connect_Retry: 60
  8.               Master_Log_File: mysql-bin.000004
  9.           Read_Master_Log_Pos: 931
  10.                Relay_Log_File: slave1-relay-bin.000056
  11.                 Relay_Log_Pos: 950
  12.         Relay_Master_Log_File: mysql-bin.000004
  13.              Slave_IO_Running: Yes
  14.             Slave_SQL_Running: Yes
  15.               Replicate_Do_DB:
  16.           Replicate_Ignore_DB:
  17.            Replicate_Do_Table:
  18.        Replicate_Ignore_Table:
  19.       Replicate_Wild_Do_Table:
  20.   Replicate_Wild_Ignore_Table:
  21.                    Last_Errno: 0
  22.                    Last_Error:
  23.                  Skip_Counter: 0
  24.           Exec_Master_Log_Pos: 931
  25.               Relay_Log_Space: 1365
  26.               Until_Condition: None
  27.                Until_Log_File:
  28.                 Until_Log_Pos: 0
  29.            Master_SSL_Allowed: No
  30.            Master_SSL_CA_File:
  31.            Master_SSL_CA_Path:
  32.               Master_SSL_Cert:
  33.             Master_SSL_Cipher:
  34.                Master_SSL_Key:
  35.         Seconds_Behind_Master: 0
  36. Master_SSL_Verify_Server_Cert: No
  37.                 Last_IO_Errno: 0
  38.                 Last_IO_Error:
  39.                Last_SQL_Errno: 0
  40.                Last_SQL_Error:
  41.   Replicate_Ignore_Server_Ids: 0

The key fields from the status report to examine are:

  • Slave_IO_State: The current status of the slave. See Section 8.14.4, “Replication Slave I/O Thread States”, and Section 8.14.5, “Replication Slave SQL Thread States”, for more information.

  • Slave_IO_Running: Whether the I/O thread for reading the master's binary log is running. Normally, you want this to be Yes unless you have not yet started replication or have explicitly stopped it with STOP SLAVE.

  • Slave_SQL_Running: Whether the SQL thread for executing events in the relay log is running. As with the I/O thread, this should normally be Yes.

  • Last_IO_Error, Last_SQL_Error: The last errors registered by the I/O and SQL threads when processing the relay log. Ideally these should be blank, indicating no errors.

  • Seconds_Behind_Master: The number of seconds that the slave SQL thread is behind processing the master binary log. A high number (or an increasing one) can indicate that the slave is unable to handle events from the master in a timely fashion.

    A value of 0 for Seconds_Behind_Master can usually be interpreted as meaning that the slave has caught up with the master, but there are some cases where this is not strictly true. For example, this can occur if the network connection between master and slave is broken but the slave I/O thread has not yet noticed this—that is, slave_net_timeout has not yet elapsed.

    It is also possible that transient values for Seconds_Behind_Master may not reflect the situation accurately. When the slave SQL thread has caught up on I/O, Seconds_Behind_Master displays 0; but when the slave I/O thread is still queuing up a new event, Seconds_Behind_Master may show a large value until the SQL thread finishes executing the new event. This is especially likely when the events have old timestamps; in such cases, if you execute SHOW SLAVE STATUS several times in a relatively short period, you may see this value change back and forth repeatedly between 0 and a relatively large value.

Several pairs of fields provide information about the progress of the slave in reading events from the master binary log and processing them in the relay log:

  • (Master_Log_file, Read_Master_Log_Pos): Coordinates in the master binary log indicating how far the slave I/O thread has read events from that log.

  • (Relay_Master_Log_File, Exec_Master_Log_Pos): Coordinates in the master binary log indicating how far the slave SQL thread has executed events received from that log.

  • (Relay_Log_File, Relay_Log_Pos): Coordinates in the slave relay log indicating how far the slave SQL thread has executed the relay log. These correspond to the preceding coordinates, but are expressed in slave relay log coordinates rather than master binary log coordinates.

On the master, you can check the status of connected slaves using SHOW PROCESSLIST to examine the list of running processes. Slave connections have Binlog Dump in the Command field:

  1. mysql> SHOW PROCESSLIST \G;
  2. *************************** 4. row ***************************
  3.      Id: 10
  4.    User: root
  5.    Host: slave1:58371
  6.      db: NULL
  7. Command: Binlog Dump
  8.    Time: 777
  9.   State: Has sent all binlog to slave; waiting for binlog to be updated
  10.    Info: NULL

Because it is the slave that drives the replication process, very little information is available in this report.

For slaves that were started with the --report-host option and are connected to the master, the SHOW SLAVE HOSTS statement on the master shows basic information about the slaves. The output includes the ID of the slave server, the value of the --report-host option, the connecting port, and master ID:

  1. mysql> SHOW SLAVE HOSTS;
  2. +-----------+--------+------+-------------------+-----------+
  3. | Server_id | Host   | Port | Rpl_recovery_rank | Master_id |
  4. +-----------+--------+------+-------------------+-----------+
  5. |        10 | slave1 | 3306 |                 0 |         1 |
  6. +-----------+--------+------+-------------------+-----------+
  7. 1 row in set (0.00 sec)

Zoek in de MySQL-handleiding

Nederlandse vertaling

U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.

Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.

Bij voorbaat dank.

Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-replication-administration-status.html

De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.

Referenties

  1. Bekijk - html-document Taal van het document:en Manuel MySQL : https://dev.mysql.com/

Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.

Inhoudsopgave Haut