Mysql master-slave replication description from the official manual

Brewed liquor tea-Mysql master-slave Replication

How can we make this copy?

Command:

show processlist;show slave status;show master status;SHOW MASTER LOGSSHOW BINARY LOGSSHOW SLAVE HOSTSshow binlog events\G

A-A1, A2> B-A1, A2

A:

A1, A2 -- time

A1, A2, A3> A1, A2 update

A-A1, A2-time.

----------- End

--- Start

A-A1 (edit), A3-time

A-A1 (edit), A3 (edit)-time

A-A1 (edit)> B-A1?

Md5

{

Table

}

1. start slave from the server and create an I/O thread.

2. The master server creates an I/O thread to send binlog logs. (Show processlist)

3. Receive binlog logs from the server and save them to the local relay log ).

4. Create an SQL thread from the server to execute the saved binlog.

1. Implement the same process of Master/Slave Data

1. LOCK table flush tables with read lock;

2. Back up data and import data from the server to make the data the same. Mysqldump ....

3. Record the binlog file and the offset show master status;

4. Add the synchronization permission user, password, permission, and synchronization host .)

Mysql> grant replication slave on *. * TO 'repl' @ '% .mydomain.com' identified by 'slavepass ';

2. modify the configuration file

MASTER:

Server_id

Log-bin

From:

Server_id

3. Set synchronization options on the slave server

mysql> CHANGE MASTER TO           ->     MASTER_HOST='master_host_name',            ->     MASTER_USER='replication_user_name',            ->     MASTER_PASSWORD='replication_password',            ->     MASTER_LOG_FILE='recorded_log_file_name',            ->     MASTER_LOG_POS=recorded_log_position

4. start slave server synchronization start slave;

5. Enable the lock table on the master server;

Location of the binary log file \ Index \ last successful update \ block and wait for the master server to notify new updates

The master server writes updates to the binary log file and maintains an index of the file to track log loops. These logs can record updates sent to the slave server. When a slave server connects to the master server, it notifies the master server of the last successful update location read from the server in the log. The slave server receives any updates from that time, blocks them, and waits for the master server to notify you of new updates.

LVS> mysql1

Mysql2

By splitting the customer query load between the master server and slave server, you can get a better response time. SELECT queries can be sent to the slave server to reduce the query processing load of the master server. However, the statement for modifying data should still be sent to the master server, so that the master server and slave server can be synchronized. If the query is not updated, the load balancing policy is effective, but generally the query is updated.

Master-slave replication execution process:

MySQL uses three threads to execute the replication function (one of them is on the master server and the other two are on the slave server. When start slave is issued, an I/O thread is created from the server to connect to the master server and send statements recorded in its binary log. The master server creates a thread to send the binary log content to the slave server. This thread can identify the Binlog Dump thread in the output of show processlist on the master server. Read the content sent by the Binlog Dump thread of the master server from the server I/O thread and copy the data to a local file in the data directory of the slave server, that is, relay logs. The first thread is an SQL thread, which is created from the server to read relay logs and execute updates contained in logs.

MASTER:

mysql> SHOW PROCESSLIST\G*************************** 1. row ***************************     Id: 2   User: root   Host: localhost:32931     db: NULLCommand: Binlog Dump   Time: 94  State: Has sent all binlog to slave; waiting for binlog to         be updated   Info: NULL

SLAVE:

Mysql> show processlist \ G

*************************** 1. row ***************************     Id: 10   User: system user   Host:     db: NULLCommand: Connect   Time: 11  State: Waiting for master to send event   Info: NULL*************************** 2. row ***************************     Id: 11   User: system user   Host:     db: NULLCommand: Connect   Time: 11  State: Has read all relay log; waiting for the slave I/O         thread to update it   Info: NULL

Mysql> show slave status \ G

*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.100.220                  Master_User: rep1                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000006          Read_Master_Log_Pos: 240               Relay_Log_File: Web2-221-relay-bin.000006                Relay_Log_Pos: 253        Relay_Master_Log_File: mysql-bin.000006             Slave_IO_Running: Yes            Slave_SQL_Running: No              Replicate_Do_DB:          Replicate_Ignore_DB:           Replicate_Do_Table:       Replicate_Ignore_Table:      Replicate_Wild_Do_Table:  Replicate_Wild_Ignore_Table:                   Last_Errno: 1050                   Last_Error: Error 'Table '100w' already exists' on query. Default database: 'test'. Query: 'create table 100w(`id` int(11) not null,`number` int(11) not null)'                 Skip_Counter: 0          Exec_Master_Log_Pos: 107              Relay_Log_Space: 691              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: NULLMaster_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:               Last_SQL_Errno: 1050               Last_SQL_Error: Error 'Table '100w' already exists' on query. Default database: 'test'. Query: 'create table 100w(`id` int(11) not null,`number` int(11) not null)'  Replicate_Ignore_Server_Ids:             Master_Server_Id: 11 row in set (0.00 sec)

Master info:

--master-host·         --master-user·         --master-password·         --master-port·         --master-connect-retry·         --master-ssl·         --master-ssl-ca·         --master-ssl-capath·         --master-ssl-cert·         --master-ssl-cipher·         --master-ssl-key

Copy the State field output by show processlist. Show processlist is used for subordinate I/O threads. If the thread is trying to connect to the master server, waiting for the time to come to the master server or connecting to the master server, this statement will notify you. In section 6.3, possible statuses are listed in "Copy Implementation Details. When the old version of MySQL fails to connect to the master server, the thread is allowed to continue running. For an earlier version of MySQL, viewing this field is required. If it is running, there is no problem; if it is not running, you will find the error in the Last_Error field as described later ).

· Master_Host

The current master server host.

· Master_User

The current user used to connect to the master server.

· Master_Port

The current master server interface.

· Connect_Retry

-- Current value of the master-connect-retry Option

· Master_Log_File

The name of the binary log file of the Active Server currently being read by the I/O thread.

· Read_Master_Log_Pos

The position that the I/O thread has read in the binary log of the current master server.

· Relay_Log_File

Name of the relay log file currently being read and executed by the SQL thread.

· Relay_Log_Pos

The location where the SQL thread has read and executed the current relay log.

· Relay_Master_Log_File

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

· Slave_IO_Running

Whether the I/O thread is started and successfully connected to the master server. For earlier versions of MySQL earlier than 4.1.14 and 5.0.12), if the I/O thread has been started, even if the slave server is still not connected to the master server, Slave_IO_Running will be set to YES.

· Slave_ SQL _Running

Whether the SQL thread is started.

· Replicate_Do_DB, Replicate_Ignore_DB

Use the Database List specified by the -- replicate-do-db and -- replicate-ignore-db options.

· Replicate_Do_Table, Replicate_Ignore_Table, Replicate_Wild_Do_Table, Replicate_Wild_Ignore_Table

Use the tables specified by -- 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 a null string, which means "no error ". If the value of Last_Error is not null, it will also be displayed as a message in the error log of the slave server.

The startup options for replication control are listed below: many options can be reset through the change master to statement when the server is running. Other options, such as -- replicate-*, can be set only when the server is started. We plan to fix this issue.

· -- Logs-slave-updates

Generally, the updates received by the slave server from the master server are not recorded in its binary log. This option tells the slave server to log the updates executed by its SQL thread to the slave server's own binary log. To make this option take effect, you must also use the -- logs-bin option to start the slave server to enable binary logs. To apply the chain replication server, use -- logs-slave-updates. For example, you may want to set it as follows:

A-> B-> C

That is to say, A is the master server of slave server B and B is the master server of slave server C. To work, B must be both the master server and slave server. You must use -- logs-bin to start A and B to enable binary logs, and use the -- logs-slave-updates option to start B.

· -- Logs-warnings

Let the slave server output more detailed messages about the operations it performs to the error log. For example, notify you that the Network/connection has been successfully reconnected after failure, and notify you how to start each slave server thread. This option is enabled by default. To disable it, use -- skip-logs-warnings. Dropped connections are not recorded in error logs unless the value is greater than 1.

Note that the effect of this option is not limited to copying. It can generate warnings for some actions on the server.

· -- Master-connect-retry = seconds

When the master server goes down or the connection is lost, the number of seconds before sleep from the server thread to try again to connect to the master server. If the value in the. info file of the master server can be read, it is preferred. If not set, the default value is 60.

· -- Master-host = host

The host name or IP address of the master replication server. If this option is not provided, the slave server thread does not start. If the value in the. info file of the master server can be read, it is preferred.

· -- Master-info-file = file_name

The name of the file used by the slave server to record information about the master server. The default name is mysql.info in the data directory.

· -- Master-password = password

Password of the account used for authentication by the slave server thread when the master server is connected. If the value in the. info file of the master server can be read, it is preferred. If not set, the password is null.

· -- Master-port = port_number

The TCP/IP Port Number of the master server. If the value in the. info file of the master server can be read, it is preferred. If no value is set, it is assumed that the compiled value is used. If you have not used the configure option, the value should be 3306.

· -- Master-ssl, -- master-ssl-ca = file_name, -- master-ssl-capath = directory_name, -- master-ssl-cert = file_name, -- master-ssl-cipher = cipher_list, -- master-ssl-key = file_name

These options are used to set secure replication connection with the master server using SSL. Their meanings are described in section 5.8.7.6, the-SSL, -- ssl-ca, -- ssl-capath, -- ssl-cert, -- ssl-cipher, and -- ssl-key options described in "ssl command line options" are the same. If the value in the. info file of the master server can be read, it is preferred.

· -- Master-user = username

Username of the account used for authentication by the slave server thread when the master server is connected. This account must have the replication slave permission. If the value in the. info file of the master server can be read, it is preferred. If the master server user is not set, test is used.

· -- Max-relay-logs-size = size

Automatically recyclically relay logs. See section 5.3.3 "server system variables ".

· -- Read-only

This option allows the slave server to only allow updates from the slave server thread or users with SUPER permissions. The slave server does not accept updates from customers.

· -- Relay-log = file_name

Relay log name. The default name is the host_name-relay-bin.nnnnnn, where host_name is the name of the slave server host, and nnnnnnnn indicates that relay logs are created in the serial number sequence. If the relay logs are too large (and you do not want to reduce max_relay_log_size), you need to place them elsewhere in the data directory, or if you want to increase the speed through load balancing between hard disks, you can specify the option to create a relay log name irrelevant to the host name.

· -- Relay-log-index = file_name

The location and name of the relay log index file. The default name is host_name-relay-bin.index, where host_name is the slave server name.

· -- Relay-log-info-file = file_name

The name of the file used by the slave server to record the information related to the relay log. The default name is the relay-log.info in the data directory.

· -- Relay-log-purge = {0 | 1}

Whether to enable or disable automatic clearing when you no longer need to relay logs. The default value is 1 (Enabled ). This is a GLOBAL variable and can be dynamically changed using set global Relay_log_purge.

· -- Relay-log-space-limit = size

Limit the maximum space occupied by all relay logs on the slave server (0 indicates "unlimited "). It is useful when the disk space of the slave server host is limited. When the limit is reached, the I/O thread stops reading events in binary logs from the master server until the SQL thread is locked and some unused relay logs are deleted. Note that this restriction is not absolute: it is possible that the SQL thread needs more events before deleting the relay log. In this case, the I/O thread will exceed the limit until the SQL thread can delete some of the relay logs. (Do not do this will cause a deadlock ). -- Relay-log-space-limit value cannot be smaller than -- max-relay-logs-size (or if -- max-relay-logs-size is 0, select -- max-binlog-size) to double the value. In this case, it is possible that the I/O thread is waiting to release space because it exceeds -- relay-log-space-limit, but the SQL thread does not have the relay log to be cleared, cannot meet I/O thread requirements. Force the I/O thread to temporarily ignore -- relay-log-space-limit.

· -- Replicate-do-db = db_name

Tells the slave server to restrict the replication of statements where the default database (selected by USE) is db_name. To specify multiple databases, you must use this option multiple times. Each database is used once. Do not copy cross-database statements. For example, if you have selected another database or no database, run UPDATE some_db.some_table SET foo = 'bar '. To update data across databases, use -- replicate-wild-do-table = db_name. %. Read the considerations behind this option list.

Change master to master_def [, master_def]...

master_def:      MASTER_HOST = 'host_name'    | MASTER_USER = 'user_name'    | MASTER_PASSWORD = 'password'    | MASTER_PORT = port_num    | MASTER_CONNECT_RETRY = count    | MASTER_LOG_FILE = 'master_log_name'    | MASTER_LOG_POS = master_log_pos    | RELAY_LOG_FILE = 'relay_log_name'    | RELAY_LOG_POS = relay_log_pos    | MASTER_SSL = {0|1}    | MASTER_SSL_CA = 'ca_file_name'    | MASTER_SSL_CAPATH = 'ca_directory_name'    | MASTER_SSL_CERT = 'cert_file_name'    | MASTER_SSL_KEY = 'key_file_name'    | MASTER_SSL_CIPHER = 'cipher_list'