MySQL show slave Status Command parameter

? Slave_io_state

A copy of the state field of the SHOW processlist output. SHOW processlist is used for slave I/O threads. This statement notifies you if a thread is trying to connect to the primary server, is waiting for the time to come to the host server, or is connecting to the primary server.

? Master_user

The current user that is used to connect to the primary server.

? Master_port

The current primary server interface.

? Connect_retry

Current value of the –master-connect-retry option

? Master_log_file

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

? Read_master_log_pos

In the current primary server binary log, the I/O thread has already read the location.

? Relay_log_file

The name of the trunk log file that the SQL thread is currently reading and executing.

? Relay_log_pos

In the current trunk log, the SQL thread has read and executed the location.

? Relay_master_log_file

The name of the primary server binary log file that is executed by the SQL thread that contains most recent events.

? Slave_io_running

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

? Slave_sql_running

Whether the SQL thread was started.

? replicate_do_db,replicate_ignore_db

The list of databases specified using the –REPLICATE-DO-DB and –REPLICATE-IGNORE-DB options.

? Replicate_do_table,replicate_ignore_table,replicate_wild_do_table,replicate_wild_ignore_table

The list of tables specified using the –replicate-do-table,–replicate-ignore-table,–replicate-wild-do-table and –replicate-wild-ignore_table options.

? Last_errno,last_error

The number of errors and error messages returned by most recently executed queries. The number of errors is 0 and the message is an empty string that means "no error". 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.

To illustrate:

last_errno:1051

Last_error:error ' Unknown table ' z ' on query ' drop table Z '

The message indicates that table Z existed on the primary server and was canceled, but it did not exist in the secondary server, so the DROP table failed for the secondary server. (for example, this can happen if you forget to copy this table to a subordinate server when setting up replication.) ）

? Skip_counter

The value that was recently used for Sql_slave_skip_counter.

? Exec_master_log_pos

The location (relay_master_log_file) of the last time executed by the SQL thread from the primary server's binary log. In the primary server's binary log (relay_master_log_file, Exec_master_log_pos) corresponds to the (Relay_log_file,relay_log_pos) in the trunk log.

? Relay_log_space

The total size of all the original trunk logs combined.

? Until_condition,until_log_file,until_log_pos

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

The until_condition has the following values:

o If no until clause is specified, there is no value

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

o 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,master_ssl_ca_file,master_ssl_ca_path,master_ssl_cert,master_ssl_cipher,master_ssl_key

These fields show the parameters that are used by the subordinate server. These parameters are used to connect to the master server.

The master_ssl_allowed has the following values:

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

o If SSL connections to the primary server are not allowed, the value is no

o If an 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

This field is an indication of how much "backward" the subordinate server is. When a dependent SQL thread is running (processing an update), this field begins with the time stamp of the most recent event executed by this thread on the primary server, and the number of seconds elapsed. This field is zero when this thread is caught by a subordinate server I/O thread and goes into an idle state, waiting for more events from the I/O thread. In summary, this field measures the time gap between the subordinate server SQL thread and the subordinate server I/O thread, in seconds.

If the network connection between the primary and secondary servers is faster, the subordinate server I/O threads are very close to the primary server, so this field can indicate very closely how much of the subordinate server SQL line turndown the primary server. This indication is inaccurate if the network is slow, and the subordinate SQL threads often catch up to the read slower subordinate server I/O thread, so seconds_behind_master often displays a value of 0. This is true even if the I/O thread lags behind the primary server. In other words, this column is only useful for fast networks.

Even if the primary and secondary servers do not have the same clock, the time difference calculation will also work (when the slave server I/O thread starts, the difference is calculated.) and assume that the time difference will remain the same from now on. If the dependent SQL thread does not run, or if the subordinate server I/O thread is not running or is not connected to the primary server, then Seconds_behind_master is null (meaning "unknown"). For example, if the secondary server I/O thread sleeps for master-connect-retry seconds before reconnecting, NULL is displayed because the secondary server does not know what the primary server is doing, and cannot safely say how far behind

Overview of MySQL replication, installation, troubleshooting, tips, tools

MySQL replication is quite complex compared to the replication of NoSQL databases like Mongodb,redis! The following is a detailed introduction, I believe the content below is very helpful for you to master the replication of MySQL database.

Overview

First the master server records the data changes to the primary log, then reads the master log on the primary server from the server through the I/O thread, writes it to the relay log from the server, and the SQL thread reads the relay log and replays it from the server for MySQL replication. As shown in the following examples:

MySQL Replication

The entire process is reflected from the server, corresponding to three sets of log information, can be viewed from the server with the following command:

mysql> SHOW SLAVE STATUS;

Master_log_file & Read_master_log_pos: Primary log information for the next transfer.

Relay_master_log_file & Exec_master_log_pos: The main log information for the next execution.

Relay_log_file & Relay_log_pos: The next execution of the relay log information.

Understanding the meaning of these log messages is critical to resolving failures and is explained in detail later in this article.

Installation

Create the replication account on the primary server first:

Mysql> GRANT REPLICATION SLAVE on *. *

To ' <SLAVE_USER> ' @ ' <SLAVE_HOST> '

Identified by ' <SLAVE_PASSWORD> ';

Note: For security and flexibility considerations, do not use root and other super-privileged users as replication accounts.

Then set the master server configuration file (default:/ETC/MY.CNF):

[Mysqld]

server_id = 100

Log_bin = Mysql-bin

Log_bin_index = Mysql-bin.index

Sync_binlog = 1

Innodb_flush_log_at_trx_commit = 1

Innodb_support_xa = 1

Note: Be sure to ensure that the master-slave server server_id unique, avoid conflicts.

Note: If you do not specify Log_bin, the lack of capital uses the hostname as the name, so that once the hostname changes, there will be a problem, so it is recommended to specify Log_bin (the same problem from the server's Relay_log).

Note: Sync_binlog,innodb_flush_log_at_trx_commit,innodb_support_xa three options are set for security purposes, not a required option for replication.

Then set from the server configuration file (default:/ETC/MY.CNF):

[Mysqld]

server_id = 200

Log_bin = Mysql-bin

Log_bin_index = Mysql-bin.index

Relay_log = Mysql-relay-bin

Relay_log_index = Mysql-relay-bin.index

READ_ONLY = 1

Skip_slave_start = 1

Log_slave_updates = 1

Note: If the user has super privileges, the READ_ONLY is not valid.

Note: With Skip_slave_start, the copy will not start from the server unless you use the start slave command.

Note: Setting the log_slave_updates allows logging from the server to help you switch from the main to the master when necessary.

The most important step below is how to clone a copy of the primary server's data:

If the database is using the MyISAM table type, you can do so as follows:

shell> mysqldump--all-databases--master-data=1 > Data.sql

Note: The Master-data option lacks the capital to open lock-all-tables and writes the change master to statement.

If the database is using the InnoDB table type, you should use Single-transcation:

shell> mysqldump--all-databases--single-transaction--master-data=1 > Data.sql

With the data file, transfer to the slave server and import:

shell> MySQL < Data.sql

If the amount of data is large, the mysqldump will be very slow, and copying the data file directly can save a lot of time:

Lock the data before copying it, and then get the relevant log information (FILE & POSITION):

Mysql> FLUSH TABLES with READ LOCK;

mysql> SHOW MASTER STATUS;

Then copy the data file, if it is the MyISAM table type, the direct copy can be, if it is the InnoDB table type, you must stop the MySQL service and then copy, or the copy file may not be used. Copy the data files directly to the data directory from the server.

Finally, you need to specify the log information:

Mysql> Change MASTER to

Master_host= ' <MASTER_HOST> ',

Master_user= ' <SLAVE_USER> ',

Master_password= ' <SLAVE_PASSWORD> ',

Master_log_file= ' <FILE> ',

master_log_pos=<position>;

Note: Do not set Master_user and Master_password in the MY.CNF configuration file, as the final effect is the information in the Master.info file generated by change MASTER.

Copying data files directly on the primary server is fast, but requires a lock table or stops the service, which affects the online service. If you have previously had a slave server, then you can clone the new slave server with the old slave server:

Query the log information on the old slave server first:

mysql> SHOW SLAVE STATUS;

What we need is one of the Relay_master_log_file & Exec_master_log_pos.

The data is then obtained from the previous method on the old slave server and restored on the new slave server.

Then set the log information on the new slave server:

Mysql> Change MASTER to

Master_host= ' <MASTER_HOST> ',

Master_user= ' <SLAVE_USER> ',

Master_password= ' <SLAVE_PASSWORD> ',

Master_log_file= ' <Relay_Master_Log_File> ',

master_log_pos=<exec_master_log_pos>;

In that way, remember to start replication from the server and check for normal work:

Mysql> START SLAVE;

mysql> SHOW SLAVE STATUS;

If both the IO thread and the SQL thread show Yes, you can thank God:

Slave_io_running correspondence: Master_log_file & Read_master_log_pos

Slave_sql_running correspondence: Relay_master_log_file & Exec_master_log_pos

If no is displayed, some previous configuration steps have failed or there is a problem with the corresponding log file.

Fault

Problem: Master-slave replication stopped more than a reason, what should I do?

Answer: Replication errors are mostly due to log errors, so first of all to figure out whether the main log error or relay log error, from the error message is generally able to judge, if you can not use similar to the following Mysqlbinlog command:

shell> Mysqlbinlog <MASTER_BINLOG_FILE> >/dev/null

shell> Mysqlbinlog <SLAVE_BINLOG_FILE> >/dev/null

If there are no errors, there will be no output and, conversely, if there are errors, they will be displayed.

If this is a primary log error, you will need to use set GLOBAL Sql_slave_skip_counter from the server, as follows:

mysql> SET GLOBAL sql_slave_skip_counter = 1;

Mysql> START SLAVE;

Note: If you have multiple errors, you may need to perform multiple times (reminders: the master-slave server data may be inconsistent).

If it is a relay log error, as long as the log information in the show SLAVE status results from the server is used again, the system discards the current relay log and downloads it again:

Mysql> Change MASTER to

Master_log_file= ' <Relay_Master_Log_File> ',

master_log_pos=<exec_master_log_pos>;

Mysql> START SLAVE;

As for why Relay_master_log_file & Exec_master_log_pos are used, see Overview.

Problem: The primary server is down, how to promote the primary server from the server?

Answer: In a master multi-slave environment, you need to select the latest data from the server to make the new master server. As shown in the following:

Upgrading from a server to a primary server

In a master (Server1) two from (Server2, SERVER3) environment, after Server1 down, wait until Server1 and Server2 the log that synchronizes before the outage is complete, compare master_log_file and Read_master_ Log_pos can determine who is fast and who is slow, because Server2 data synchronized from Server1 (1582) is newer than SERVER3 data synchronized from Server1 (1493), so the Server2 should be promoted to the new primary server, then What parameters should Server3 use when change MASTER to Server2? 1582-1493=89, and Server2 's last binary log location is 8167, so the answer is 8167-89=8078.

Skills

Tables in the master-slave server can use different table types. For example, the primary server can use the InnoDB table type, provides advanced features such as transactions, row locks, and can be used from the Server MyISAM table type, memory consumption, easy backup and other advantages. Another example, if a master server with many from the server at the same time, will inevitably affect its performance, at this time can take out a server as a proxy from the server, using the Blackhole table type, log only, not write data, from it with more than one server, thereby improving performance.

Tables in the master-slave server can use different key types. For example, the main server with InnoDB, the key with varchar words save space, from the server using MyISAM, key with Char to improve speed, because MyISAM has static table one said.

Tables in the master-slave server can use different indexes. Primary server is mainly used to cope with write operations, so in addition to the primary key and unique index, such as guaranteed data relationship index generally can not add, from the server is generally used for reading operations, so you can set the index for query characteristics, and further, different from the server can set different indexes for different queries.

Tools

There are some good tools to make your copy work more effective, please refer to the respective documentation for details:

Multi-master Replication Manager for MySQL

Percona Xtrabackup

Maatkit

Tungsten-replicator

MySQL show slave Status Command parameter