MySQL Master-slave replication----synchronous and asynchronous configuration

In simple terms MySQL 's master-slave replication is a c/S architecture application. Master can be thought of as the server in our usual sense , andslave can be regarded as a client. the I/O thread on the slave to request data on Master , and master verifies that the slave information is allowed slave access, and then send data change information.

First, MySQLMaster-slave replication principle

Here I take MySQL5.5 For example, the principle of MySQL 's master-slave replication:

650) this.width=650; "Src=" Http://7xaxaa.com1.z0.glb.clouddn.com/MySQL%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6%E5%8E %9f%e7%90%86.jpg "width=" 357 "height=" 306 "alt=" MySQL master-slave copy principle. jpg "/>

first, the standby node The I/O thread is responsible for requesting data from the primary node, and the master node verifies that the data is sent to the standby node by the dump thread later. When an I/O thread of an alternate node receives the resource, it writes the data to the relay log, and the SQL thread of the standby node detects that the trunk log change immediately follows the contents of the new repository with the contents of the trunk log. This completes the synchronization process.

second, the common replication model

1 , one master one from the model

650) this.width=650; "src=" Http://7xaxaa.com1.z0.glb.clouddn.com/1.jpg "width=" 285 "height=" 133 "alt=" 1.jpg "/>

The advantages of this architecture are relatively simple, the construction and maintenance are relatively easy, the cost is relatively low. For some cases where the load volume is not particularly large and the reliability requirement is not particularly high, this model can be used entirely. However, for some large load sites, and high availability requirements, this architecture is not very suitable. Because if the traffic is large,the pressure of the master node will be compared, and if master crashes, it will also cause the termination of the business.

2 , a master multi-slave model

650) this.width=650; "src=" http://7xaxaa.com1.z0.glb.clouddn.com/2.jpg "width=" 395 "height=" 314 "alt=" 2.jpg "/>

in the vast majority of scenarios, our applications are read-write. Using this architecture, we can effectively reduce the pressure on Master reading by reading and writing separate technologies . We can do some data backup, data mining and other work on the back-end slave. However, if the repository is more, and the main library is responsible for other requests, the pressure of the main library will increase significantly, when the main library becomes the performance bottleneck of the whole system.

Of course, there are other replication models, such as multi-level trunking, and cyclic replication, the basic principles of these replication are similar to the above, here is no longer explained in detail.

TwoConfiguring Master-slave replication

1. Asynchronous Replication

Conditions for master-slave synchronization:

Master:

A: enable the binary log;

b: Select a server-id

C: Create a user with copy permissions

Slave:

A: Enable the relay log

B: Choose a unique Server-id

C: Connect to the master server and start copying data

1.first create a user with minimum permissions for replication on the main library

Mysql> Grant replication Slave,replication Client on * * to [email protected] ' 10.12.% ', identified by ' 123456 '; Query OK, 0 rows affected (0.03 sec)

2.connecting the main library from the library

mysql> change MASTER to master_host= ' 10.12.128.19 ', master_port=3306,master_user= ' repl ', master_password= ' 123456 ' , master_log_file= ' mysql-bin.000006 ', master_log_pos=451;

#查看复制的状态mysql > show slave status\g*************************** 1. row ********                 slave_io_state: waiting for master to send event                   master_host: 10.12.128.19                    master_user: repl                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000006           read_master_log_pos: 1512                Relay_Log_File: relay_index.000002                 Relay_Log_Pos: 283         relay_master_log_file: mysql-bin.000006              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: 1512               Relay_Log_Space: 452               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: 0master_ssl_verify_server_ cert: no                 last_io_errno: 0                 Last_IO_Error:                 last_sql_errno:&nbSp;0               last_sql_error:    Replicate_Ignore_Server_Ids:               Master_Server_Id: 3306                   master_uuid: 97f33396-ed12-11e4-921a-000c29e8ee06              master_info_file: /mydata/ data5.6/master.info                     sql_delay: 0          sql _remaining_delay: null      slave_sql_running_state: slave has  read all relay log; waiting for the slave I/O thread  to update it           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.00 SEC)

3.then execute from the library:

#启动复制mysql > Start slave;

You can also start the IO thread and the SQL thread separately.

( if the IO thread state from the library is always connecting , it may be the cause of the firewall, generally shutting down the firewall or configuring firewall rules. )

2. semi-synchronous replication

semi-synchronous replication is based on The semi- synchronous plugin that Google developed for MySQL. The principle of semi-synchronous replication is that after a transaction is executed on the primary server, it must at least ensure that the transaction is committed successfully after the execution of at least one of the slave servers has completed. If there is no response from the server within a certain amount of time, it is automatically demoted to asynchronous replication.

This semi-synchronous replication is built on the basis of asynchronous replication.

First you need to install Google 's semi-synchronous plugin:

Master:install plugin rpl_semi_master soname ' semisync_master.so '; Slave:install plugin rpl_semi_sync_slave soname ' Semisync_slave.so ';

Then turn on the semi-synchronous function

Master:set Global rpl_semi_sync_master_enabled = 1;set global rpl_semi_sync_master_timeout = 100; Calculated in milliseconds

Slave:set global rpl_semi_sync_slave = on;

There is also a need to restart the IO thread from the library:

Stop slave io_thread;start slave io_thread;

View the status of the semi-synchronous plug-in running on the main and standby libraries, respectively:

mysql> show global status like  ' rpl% '; +------------------------------------------- -+-------+| variable_name                               |  value |+--------------------------------------------+-------+| rpl_semi_sync_master_clients                | 1      | |  Rpl_semi_sync_master_net_avg_wait_time     | 0      | |  Rpl_semi_sync_master_net_wait_time         | 0      | |  Rpl_semi_sync_master_net_waits              | 0     | |  rpl_semi_sync_master_no_times              | 1      | |  Rpl_semi_sync_master_no_tx                  | 8     | |  Rpl_semi_sync_master_status                 | on    | |  Rpl_semi_sync_master_timefunc_failures     | 0      | |  Rpl_semi_sync_master_tx_avg_wait_time      | 0      | |  rpl_semi_sync_master_tx_wait_time          | 0      | |  Rpl_semi_sync_master_tx_waits               | 0     | |  rpl_semi_sync_master_wait_pos_backtraverse | 0     | |  Rpl_semi_sync_master_wait_sessions         | 0      | |  Rpl_semi_sync_master_yes_tx                 | 0     |+--------------------------------------------+- ------+14 rows in set  (0.04 SEC)

Mysql> show global status like ' rpl% '; +----------------------------+-------+| variable_name | Value |+----------------------------+-------+| Rpl_semi_sync_slave_status | On |+----------------------------+-------+1 row in Set (0.04 sec)

As you can see, both the Master and standby semi-synchronous plug-ins are already enabled.

This completes the asynchronous master-slave configuration.

This article is from the "Zhang Zhiliang" blog, make sure to keep this source http://zhilight.blog.51cto.com/9880628/1639572

MySQL Master-slave replication----synchronous and asynchronous configuration