MySQL series (i)--detailed description of show slave status parameter (most complete)

After we have built MySQL master and slave, we usually pass commands from the library.

Show Slave Status\g

To see the master-slave state, there will be a lot of parameters, the next I will take everyone to understand these parameters

[email protected]  (None) >show slave status\G*************************** 1.  row ***************************                Slave_IO_State: Waiting for master to send event                   master_host:  192.168.1.100                   Master_User: mysync                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.001822           read_master_log_pos: 290072815                Relay_Log_File: mysqld-relay-bin.005201                 Relay_Log_Pos: 256529594         Relay_Master_Log_File: mysql-bin.001821              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               Replicate_Do_DB:            Replicate_Ignore_DB:             replicate_do_table:        replicate_ignore_table:        replicate_wild_do_table:   replicate_wild_ignore_table:                     Last_Errno: 0                    last_error:                    skip_counter: 0          exec_master_log_pos:  256529431              relay_log_space:  709504534              until_ condition: none                until_log_file:                  Until_Log_Pos: 0           master_ssl_allowed: no            Master_SSL_CA_File:             Master_SSL_CA_Path:                Master_SSL_Cert:              Master_SSL_Cipher:                 master_ssl_key:         seconds_ behind_master: 2923master_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:  13ee75bb-99e2-11e6-be4d-b499baa80e6e              Master_Info_File: /home/data/mysql/master.info                     SQL_Delay: 0           SQL_Remaining_Delay: NULL       Slave_SQL_Running_State: Reading event from the relay log            Master_Retry_Count: 86400                   master_bind:       last_io_ error_timestamp:      last_sql_error_timestamp:                 Master_SSL_Crl:             Master_SSL_Crlpath:             Retrieved_Gtid_Set:              Executed_Gtid_Set:                  Auto_Position: 01 row in set  (0.02 SEC)

Detailed parameters:

1. Slave_io_state

This shows the status of the current slave I/O thread (the state of slave connected to master). status information and using show Processlist | grep "System User" (which displays two messages, one for the slave I/O thread and one for the slave SQL thread).

Slave the status of I/O threads in the following ways:

1) Waiting for Master Update

This is the state before the connecting to master State

2) connecting to master

I/O thread is trying to connect to master

3) Checking master version

This state occurs when a connection to master is established. The time that this state occurs is very short.

4) registering slave on Master

This state occurs when a connection to master is established. The time that this state occurs is very short.

5) Requesting Binlog dump

This state occurs when a connection to master is established. The time that this state occurs is very short. In this state, the I/O thread sends a request to master requesting Binlog, starting at the position location of the specified binglog name and Binglog.

6) waiting to reconnect after a failed Binlog dump request

If the Binglog request fails because of a disconnected connection, the I/O thread goes to sleep. Then try to re-connect on a regular basis. Try to re-connect the time interval, you can use the command "change Master to Master_connect_trt=x;" Change.

7) reconnecting after a failed Binglog dump request

I/O process is trying to connect to master

8) waiting for master to send event

Description, a successful connection to master, waiting for the arrival of the binary log time. If Master is idle, this state lasts for a long time. If the wait time exceeds the value of slave_net_timeout (in seconds), a connection timeout occurs. In this state, the I/O thread fails the human connection and begins trying to reconnect

9) Queueing master event to the relay log

At this point, the I/O thread has read an event and copied it to the relay log. So that the SQL thread can perform this event

Ten) waiting to reconnect after a failed master event read

An error occurred while reading (because the connection was broken). The I/O thread enters the sleep state before attempting to re-connect, and the sleep time is the value of Master_connect_try (default is 60 seconds)

one) reconnecting after a failed master event read

I/O thread is trying to re-connect master. If the connection is established, the status becomes "Waiting for master to send event"

waiting for the slave SQL thread to free enough relay log space

This is because the Relay_log_space_limit is set and the size of the relay log has been rounded to the maximum value. The I/O thread is waiting for the SQL thread to free up relay log space by deleting some relay log.

waiting for slave mutexes on exit

The state that occurs when the I/O thread stops, and the time is very short.

2. master_host:192.168.1.100

The IP address of the MySQL master library

3. Master_user:mysync

This is a user above the master. Used to be responsible for the master-slave replication of the user, create a master-slave replication when established (with reolication slave permissions).

4. master_port:3306

The port of the master server is typically 3306

5. connect_retry:60

After the connection has been interrupted, retry the connection time interval. The default value is 60 seconds.

#与master相关的日志的信息

6. master_log_file:mysql-bin.001822

The name of the primary server binary log file that the current I/O thread is reading.

7. read_master_log_pos:290072815

The location of the binary log that the current I/O thread is reading.

#与relay Log-related information

8. relay_log_file:mysqld-relay-bin.005201

The file name of the relay log currently being read and executed by the slave SQL thread.

9. relay_log_pos:256529594

The location of the relay log file currently being read and executed by the slave SQL thread;

Ten. relay_master_log_file:mysql-bin.001821

The file name of the relay log currently read and executed by the slave SQL thread contains most recent events and corresponds to the name of the primary server binary log file.

Status of #slave I/O and SQL threads (important)

Slave_io_running:yes.

Whether the I/O thread is started and successfully connected to the primary server.

Slave_sql_running:yes.

Whether the SQL thread was started.

replicate_do_db.

replicate_ignore_db

Replicate_do_table

Replicate_ignore_table

Replicate_wild_do_table

Replicate_wild_ignore_table

These parameters are used to indicate which libraries or tables are not synchronized to the slave library at the time of replication, but these parameters need to be used with caution because problems may occur when cross-library usage occurs.

In general, this parameter is used when the limit is replicate_wild_ignore_table.

last_errno:0.

Last_error

The slave SQL thread reads the error number of the log parameter and the error message. The number of errors is 0 and the message is an empty string representing no errors.

If the Last_error value is not a null value, it is also displayed as a message in the error log of the subordinate server.

skip_counter:0.

A value of sql_slave_skip_counter that sets the number of steps to skip SQL execution.

exec_master_log_pos:256529431.

Slave the events currently executed by the SQL thread, corresponding to the position in the corresponding binary log of master. (combined with relay_master_log_file understanding)

relay_log_space:709504534.

The total size of all the original trunk logs combined.

Until_condition:none.

Until_log_file:

until_log_pos:0

The value specified in the until clause of the start slave statement.

The until_condition has the following values:

1) If no until clause is specified, there is no value

2) If the secondary server is reading until the primary server's binary log is reached for a given location, the value is master

3) If the secondary server is reading until it reaches the given location of the secondary log, the value is relay

Until_log_file and Until_log_pos are used to indicate the log file name and location values. The log file name and location value define the point at which the SQL thread aborts execution.

Master_ssl_allowed:no.

Master_ssl_ca_file:

Master_ssl_ca_path:

Master_ssl_cert:

Master_ssl_cipher:

Master_ssl_key:

Master_ssl_verify_server_cert:no

MASTER_SSL_CRL:

Master_ssl_crlpath:

These fields show the parameters that are related to the use of encryption by the subordinate server. These parameters are used to connect to the master server.

The master_ssl_allowed has the following values:

1) If you allow SSL connections to the primary server, the value is yes

2) If you do not allow SSL connections to the primary server, the value is no

3) If the SSL connection is allowed, but the secondary server does not have SSL support enabled, the value is ignored.

The value of the field associated with SSL corresponds to the value of the –master-ca,–master-capath,–master-cert,–master-cipher and –master-key options.

seconds_behind_master:2923.

The value is the difference between the timestamp. Is the difference between the current timestamp of the slave and the timestamp when master records the event.

last_io_errno:0.

Last_io_error:

last_sql_errno:0

Last_sql_error:

Error number and error message for the last I/O thread or SQL thread.

Replicate_ignore_server_ids:

Master-slave replication, the main library server ID number that is ignored from the library. is to not use these server IDs as the primary library.

Master_server_id:1.

master_uuid:13ee75bb-99e2-11e6-be4d-b499baa80e6e

Master_info_file:/home/data/mysql/master.info

Indicates the main library server ID number, the UUID of the main library server, and the directory location where the main library server is saved from the library.

sql_delay:0.

A nonnegative integer that represents the number of seconds and how many seconds the slave lags in master.

Sql_remaining_delay:null.

When Slave_sql_running_state waits until master_delay seconds, the master executes an event that contains an integer that represents the number of seconds or so of a delay. At other times, this field is null.

Slave_sql_running_state:reading event from the relay log

SQL Thread Run state:

1) Reading event from the relay log

The thread has read an event from the trunk log and can handle the event.

2) have read all relay log; Waiting for the slave I/O thread to update it

The thread has processed all the events in the trunk log file and is now waiting for the I/O thread to write the new event to the relay log.

3) waiting for slave mutexes on exit

A very simple state that occurs when a thread stops.

master_retry_count:86400.

The maximum number of retries that failed to connect to the main library.

Master_bind.:

Slave is used from the library in the case of multiple network interfaces to determine which slave network interface to connect to master.

Last_io_error_timestamp:

Last_sql_error_timestamp:

Timestamp of the last I/O thread or SQL thread error.

#GTID模式相关

Retrieved_gtid_set:

Get to the Gtid<io thread >

Executed_gtid_set:

Gtid<sql Threads executed >

auto_position:0

This article is from the "Zheng Xiaoming Technology Blog" blog, make sure to keep this source http://zhengmingjing.blog.51cto.com/1587142/1910565

MySQL series (i)--detailed description of show slave status parameter (most complete)