Shulou(Shulou.com)06/01 Report--

Editor to share with you what the MySql master-slave copy is. I hope you will gain a lot after reading this article. Let's discuss it together.

What is Mysql master-slave replication

MySQL master-slave replication is one of its most important functions. Master-slave replication means that one server acts as the master database server, and one or more servers act as the slave database server, and the data in the master server is automatically copied to the slave server. For multilevel replication, the database server can act as either a host or a slave. The basis of MySQL master-slave replication is that the master server records the binary log of the database modification, and the slave server automatically performs updates through the binary log of the master server.

2. Type of Mysq master-slave replication 1. Statement-based replication:

The statements executed on the master server are executed again on the slave server and are supported after the MySQL-3.23 version.

The problem: the time may not be fully synchronized to cause deviation, and the user executing the statement may be a different user.

2. Row-based replication:

Copy the adapted content from the main server directly, regardless of which statement causes the change, which will be introduced after the MySQL-5.0 version.

The existing problems: for example, if there are 10, 000 users in a payroll table, and we put the salary of each user + 1000, then row-based replication will copy 10, 000 rows of content, which will cost a lot of money. Statement-based replication is just one statement.

3. Mixed type replication:

MySQL uses statement-based replication by default, and row-based replication is used when statement-based replication causes problems, and MySQL automatically selects it.

In the MySQL master-slave replication architecture, reads can be performed on all servers, while writes can only be performed on the master server. Although the master-slave replication architecture provides an extension for read operations, if there are more write operations (multiple slave servers also synchronize data from the master server), the master server is bound to become a performance bottleneck in the replication of a single master model.

Third, the working principle of Mysql master-slave replication.

1. Statement-based replication: the statements executed on the master server are executed again on the slave server and are supported after the MySQL-3.23 version.

The problem: the time may not be fully synchronized to cause deviation, and the user executing the statement may be a different user.

2. Line-based replication: copy the adapted content on the main server directly, regardless of which statement causes the change, which will be introduced after the MySQL-5.0 version.

The existing problems: for example, if there are 10, 000 users in a payroll table, and we put the salary of each user + 1000, then row-based replication will copy 10, 000 rows of content, which will cost a lot of money. Statement-based replication is just one statement.

3. Mixed-type replication: MySQL uses statement-based replication by default, and row-based replication is used when statement-based replication causes problems, and MySQL will select it automatically.

In the MySQL master-slave replication architecture, reads can be performed on all servers, while writes can only be performed on the master server. Although the master-slave replication architecture provides an extension for read operations, if there are more write operations (multiple slave servers also synchronize data from the master server), the master server is bound to become a performance bottleneck in the replication of a single master model.

Working principle of MySQL Master-Slave replication

As shown in the following figure:

Any changes on the primary server will be saved in the binary log Binary log, launch an Icano thread (actually a client process of the primary server) from the server, connect to the primary server and request to read the binary log, and then write the read binary log to a local Realy log. Open a SQL thread from the server to check the Realy log regularly, and immediately execute the changes on the machine if you find any changes.

If there is one master and multiple slaves, the master library is responsible for both writing and providing binary logs for several slave libraries. At this point, you can adjust the binary log to only one slave, which reopens the binary log and sends its own binary log to other slaves. Or simply this never record is only responsible for forwarding binary logs to other slaves, so the performance may be much better in architecture, and the latency between data should be slightly better. The working schematic diagram is as follows:

In fact, in the old version of MySQL master-slave replication, the server side is not done by two processes, but by one process. However, it was later found that there were greater risks and performance problems in doing so, mainly as follows:

First of all, a process will make the process of copying the bin-log log and parsing the log into a serial process, the performance is limited, and the latency of asynchronous replication will be long.

In addition, after the Slave side obtains the bin-log from the Master side, it needs to parse the log content and execute it on its own. In the process, the Master side may have made a lot of changes and added a large number of logs. If there is an irreparable error in the storage on the Master side at this stage, all changes made at this stage will never be recovered. If the pressure on the Slave side is high, the process may take a long time.

In order to improve the performance of replication and address the risks, later versions of MySQL delegate the replication on the Slave side to two processes. The person who proposed this improvement is Yahoo! One of the engineers "Jeremy Zawodny". This not only solves the performance problem, but also shortens the asynchronous delay time, but also reduces the possible amount of data loss.

Of course, even after the current two-thread processing, there is still the possibility of slave data latency and data loss, after all, the replication is asynchronous. These problems exist as long as the changes to the data are not in the same thing. If you want to avoid these problems completely, you can only use MySQL's cluster to solve them. However, MySQL's cluster is an in-memory database solution, which needs to load all the data into memory, so the requirement for memory is very large, and the implementability for general applications is not too great.

Another thing to mention is MySQL's replication filtering (Replication Filters), which allows you to replicate only part of the data on the server. There are two types of replication filtering: filtering events in binary logs on Master and events in relay logs on Slave. As follows:

Configure the my.cnf file for Master (critical configuration) / etc/my.cnf

Log-bin=mysql-binserver-id = 1binlog-do-db=icingabinlog-do-db=DB2 / / if multiple databases are backed up, set this option repeatedly to binlog-do-db=DB3 / / databases that need to be synchronized, if there is no such line That is, to synchronize all databases binlog-ignore-db=mysql / / ignored database configuration Slave my.cnf files (critical configuration) / etc/my.cnflog-bin=mysql-binserver-id=2master-host=10.1.68.110master-user=backupmaster-password=1234qwermaster-port=3306replicate-do-db=icingareplicate-do-db=DB2replicate-do-db=DB3 / / databases that need to be synchronized. If there is no such line, it means to synchronize all databases replicate-ignore-db=mysql / / ignored databases

Netizens said that there may be some problems in the use of replicate-do-db (http://blog.knowsky.com/19696...), and they did not test it themselves. It is assumed that the binlog-do-db parameter is used in the master server to filter out databases that are not allowed to replicate in the configuration file by filtering Binary Log, that is, not to write operation logs that do not allow replication data to Binary Log, while replicate-do-db is used in the slave server to filter out databases or tables that are not allowed to replicate by filtering Relay Log, that is, to perform actions in Relay Log that are not allowed to modify. In this case, in the case of multiple slave database servers: some slave servers not only copy data from the master server, but also copy data from the master server to another slave server, then it should be possible to have both binlog-do-db and replicate-do-db parameters in its configuration file. Everything is my own prediction, and the specific use of binlog-do-db and replicate-do-db has to be explored a little bit in the actual development.

It is said on the Internet that the operation of ignoring certain databases or tables during replication had better not be carried out on the master server, because after the master server ignores it, it will no longer write in the binary file, but although some databases are ignored on the slave server, the operation information on the master server will still be copied to the relay log on the slave server, but it will not be performed on the slave server. I think this means that it is recommended to set replicate-do-db on the slave server rather than binlog-do-db on the master server.

In addition, you only need to write one blacklist (binlog-ignore-db, replicate-ignore-db) or whitelist (binlog-do-db, replicate-do-db). If you use it at the same time, only the whitelist takes effect.

IV. The process of Mysql master-slave replication

There are two cases of MySQL master-slave replication: synchronous replication and asynchronous replication. Most of the actual replication architecture is asynchronous replication.

The basic process of replication is as follows:

The IO process above Slave connects to the Master and requests the log contents from the specified location of the specified log file (or from the beginning of the log).

After Master receives a request from the IO process of Slave, the IO process responsible for replication reads the log information after the specified location of the log according to the request information and returns it to the IO process of Slave. In addition to the information contained in the log, the returned information also includes the name of the bin-log file that has been sent to Master and the location of the bin-log.

After receiving the information, the IO process of Slave adds the received log content to the end of the relay-log file on the server, and records the file name and location of the bin-log read to the master-info file, so that the next time it is read, it can clearly tell Master, "where do I need to start the log content of a bin-log, please send it to me".

As soon as Slave's Sql process detects a new addition to the relay-log, it immediately parses the relay-log content into the executable content that is actually executed on the Masterside and executes it on its own.

5. Specific configuration of Mysql master-slave replication

Replication is usually used to create replicas of master nodes, to ensure high availability by adding redundant nodes, but replication can also be used for other purposes, such as data reading, analysis, and so on. In the scale-out business, replication is easy to implement, mainly in the use of master nodes for write operations and multiple slave nodes for read operations. The asynchrony of MySQL replication means that things are first submitted on the master node, then copied to the slave node and applied on the slave node, which means that the data on the master and slave nodes may be inconsistent at the same time. The advantage of asynchronous replication is that it is faster than synchronous replication. If there is a high requirement for data consistency, synchronous replication is better.

The simplest replication mode is an one-master-one-slave replication mode, which takes only three steps to complete:

(1) create a master node, enable binlog, and set the server id

(2) set up a slave node and set the server id

(3) connect the slave node to the master node.

Let's start with MySQL 5.5 as an example, the operating system Ubuntu12.10,Master 10.1.6.159 Slave 10.1.6.191.

Apt-get install mysql-serverMaster machine

The binlog log is enabled on the Master, and a unique server id is set, which must be unique in the LAN. The binary binlog log records all database changes on the master, and the log is copied to the slave node and played back on the slave node. Modify the my.cnf file and modify the following under the mysqld module:

[mysqld] server-id = 1log_bin = / var/log/mysql/mysql-bin.log

Log_bin sets the basic name of the file produced by the binary log, which consists of a series of files. The value of log_bin is optional. If no value is set for log_bin, the default value is: hostname-bin. If you change the hostname casually, the name of the binlog log will also be changed. Server-id is used to uniquely identify a server, and each server has a different server-id. When the slave connects to the master, it asks master to pass all the binlog to it, and then plays back the binlog on the slave. In order to prevent confusion of permissions, it is common to set up a separate account for replication.

Binlog is the key to the replication process, which records all changes to the database. Usually, statements that are about to be executed will write a record at the end of the binlog log. Binlog only records statements that change the database, but not statements that do not change the database. This situation is called statement-based replication, and there is another situation mentioned earlier that is row-based replication, and each mode has its own advantages and disadvantages.

Slave machine

Slave machines, like master, require a unique server-id.

[mysqld] server-id = 2

Connect Slave to Master

After Master and Slave are configured, all you need to do is to master the slave

Change master to master_host='10.1.6.159',master_port=3306,master_user='rep',master_password='123456';start slave

Next, do some operations on master to change the database to observe the changes in slave. After you change the my.cnf configuration and restart the database, you start recording the binlog. You can see a mysql-bin.000001 file in the / var/log/mysql directory, and there is also a mysql-bin.index file. What is this mysql-bin.index file? This file saves the list of all binlog files, but we do not set the change value in the configuration file. This can be set through log_bin_index. If there is no setting change value, the default value is the same as log_bin. Execute the show binlog events command on master, and you can see the contents of the first binlog file.

Note: the above sql statement is to copy the first binlog from scratch. If you want to copy binlog from a location, you need to specify the name of the binlog file to start and the starting location of the statement in the file when change master to. The parameters are as follows: master_log_file and master_log_pos.

Mysql > show binlog events\ gateway * 1. Row * * Log_name: mysql-bin.000001 Pos: 4 Event_type: Format_desc Server_id: 1End_log_pos: 107Info: Server ver: 5.5.28-0ubuntu0.12.10.2-log Binlog ver: 4girls * 2.row * * Log_name: mysql-bin.000001 Pos: 107Event_type: Query Server_id: 1End_log_pos: 181Info: create user rep* * 3. Row * * Log_name: mysql-bin.000001 Pos: 181 Event_type: Query Server_id: 1End_log_pos: 316 Info: grant replication slave on *. * to rep identified by '123456 rows in set (0.00 sec)

Log_name is the name of the binary log file. An event cannot span two files.

Pos this is where the event starts in the file

The type of Event_type event, which is the basic method of passing information to slave. Each new binlog starts with the Format_desc type and ends with the Rotate type.

Server_id creates the server id for this event

End_log_pos where this event ends and where the next event begins, so the event scope is Pos~End_log_pos-1

Readable text of Info event information. Different events have different information.

Example

Create an rep table in master's test library and insert a record.

Create table rep (name var); insert into rep values ("guol"); flush logs

The flush logs command forces the log to be rotated to generate a new binary log, which can be viewed through show binlog events in 'xxx'. You can view the binlog file that is currently being written through show master status. In this way, the corresponding change operation is performed on the slave.

The above is the simplest master-slave replication mode, but sometimes with the passage of time, binlog will become very large, if you add a new slave, copy master binlog files from scratch is very time-consuming, so we can start from a specified location to copy binlog logs, you can use other methods to quickly copy the previous binlog files, such as copy physical files. There are two parameters in change master to to do this, master_log_file and master_log_pos, which specify the binlog file and its location. We can copy from master or from slave. If we are copying from master, the procedure is as follows:

(1) in order to prevent data update during operation, resulting in data inconsistency, you need to refresh the data and lock the database: flush tables with read lock.

(2) check the current binlog file and its location: show master status.

Mysql > show master status\ gateway * 1. Row * * File: mysql-bin.000003Position: 107Binlog_Do_DB:Binlog_Ignore_DB:1 row in set (0.00 sec)

(3) the logical backup of creating database through mysqldump command: mysqldump-- all-databases-hlocalhost-p > back.sql.

(4) after having the logical backup of master, unlock the database: unlock tables.

(5) copy back.sql to the new slave and execute: mysql-hlocalhost-p insert the logical backup of master into slave's database.

(6) now you can connect the new slave to master, just set two more parameters master_log_file='mysql-bin.000003' and master_log_pos='107' in change master to, and then start slave:start slave, so that slave can be copied from the location of 107s.

Change master to master_host='10.1.6.159',master_port=3306,master_user='rep',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos='107';start slave

Sometimes master does not allow you to lock the table for replication, because you may run some uninterrupted services, and if master already has a slave, we can extend a new slave through this slave. The principle is similar to copying on master. The key is to find the location of binlog. When you copy, the slave may also be synchronizing with master, as follows:

(1) in order to prevent data changes, you still need to stop the synchronization of slave: stop slave.

(2) then refresh the table and logically back up the database with mysqldump.

(3) use show slave status to view the information about slave and record the values Relay_Master_Log_File and Exec_Master_Log_Pos of the two fields, which are used to determine where to start the replication.

(4) unlock slave, import the backup logical database into the new slave database, and then set change master to, which is the same as copying master.

In-depth understanding of Mysql master-slave configuration 1. One master and multiple slaves

A replication system consisting of a master and a slave is the simplest case. Slave do not communicate with each other, only with master. In practical application scenarios, more than 90% of MySQL replication is an architecture mode in which one Master is replicated to one or more Slave, which is mainly used for cheap database expansion solutions for applications with high reading pressure.

In the figure above, we mentioned the case of one master and multiple slaves at the beginning, in which the master library is responsible for both writing and providing binary logs for several slave libraries. In this case, the binary log is only given to one slave, which reopens the binary log and sends its own binary log to other slaves, or simply this unrecorded log is only responsible for forwarding the binary log to other slaves. In this way, the performance may be much better, and the latency between the data should be slightly better. PS: these have been written before and copied again.

2. Master replication

In the figure above, the two servers replicated by Master-Master are both master and the slave of another server. In this way, changes made by either party will be applied to the other party's database through replication. In this replication architecture, each is not running the same db, for example, the one on the left is db1, the one on the right is the slave of db2,db1 and the other is the slave of db2 on the left. The two are master and slave to each other, and some monitoring services can also achieve high availability to a certain extent.

3. Master-Master in active-passive mode (Master-Master in Active-Passive Mode)

In the figure above, this is a change from the structure of master-master, which avoids the shortcomings of Mmurm M. in fact, it is a system with fault tolerance and high availability. The difference is that only one node is providing read and write services, and the other node is always ready to take over the service in the event of a failure. For example, such a set of highly available services can be provided through corosync+pacemaker+drbd+MySQL, and the separation of read and write can also be achieved by following the slave server in active / standby mode.

4. Master-Master structure with slave server (Master-Master with Slaves)

The advantage of this structure is that it provides redundancy. Geographically distributed replication structure that does not have a single node failure problem and can also put read-intensive requests on the slave.

5. MySQL-5.5 supports semi-synchronous replication.

Previous MySQL replication can only be implemented asynchronously, starting with MySQL-5.5 and supporting semi-automatic replication. In previous asynchronous replication, the main library did not monitor the progress of the library after performing some transactions. If the slave database is lagging behind, and more unfortunately, the main database has Crash (for example, downtime), the data in the slave database is incomplete. In short, when the primary database fails, we can no longer use the standby library to continue to provide data-consistent services. Semisynchronous Replication (semi-synchronous replication) ensures to some extent that committed transactions have been passed to at least one standby. In Semi synchronous, it is only guaranteed that the transaction has been passed to the slave, but there is no guarantee that the execution has been completed on the slave.

In addition, there is another situation that can lead to inconsistency between active and standby data. In a certain session, after a transaction is committed on the main database, it will wait for the transaction to be passed to at least one standby database. If the main library Crash during this waiting process, then the standby database and the main database may be inconsistent, which is fatal. If the master / slave network fails or the slave database fails, the master database will continue after waiting 10 seconds after the transaction is committed (the default value for rpl_semi_sync_master_timeout). At this point, the main library changes back to its original asynchronous state.

After MySQL loads and opens the Semi-sync plug-in, each transaction needs to wait for the standby database to receive the log before returning to the client. If you are doing small transactions and the latency between the two hosts is small, Semi-sync can achieve zero data loss with little performance loss.

After reading this article, I believe you have a certain understanding of what MySql master-slave replication is, want to know more about it, welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.