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

This article is mainly about some principles and necessary knowledge of mysql master-slave replication. For the construction of mysql master-slave environment, please refer to another blog: MySQL master-slave replication environment deployment [http://blog.itpub.net/31015730/viewspace-2153251/]

Mysql replication concept note

Mysql's built-in replication capabilities are the basis for building large, high-performance applications. Distribute the data of Mysql to multiple systems. The mechanism of this distribution is to copy the data of one host of Mysql to another host (slaves) and execute it again. During replication, one server acts as the master server, while one or more other servers act as the slave server. The primary server writes updates to the binary log file and maintains an index of the file to track the log cycle. These logs can record updates sent to the slave server. When a slave server connects to the master server, it informs the master server of the location of the last successful update read by the slave server in the log. Receive any updates that have occurred since then from the server, then block and wait for the primary server to notify the new update.

It is important to note that:

When mysql replication occurs, all updates to the tables in the replication must occur on the primary server. Otherwise, care must be taken to avoid conflicts between user updates to the tables on the master server and updates to the tables on the slave server.

1. Mysql supports those replicas

(1) statement-based replication: a SQL statement executed on the master server and the same statement executed on the slave server. MySQL uses statement-based replication by default, which is more efficient. Row-based replication is automatically selected when it is found that exact replication is not possible.

(2) Line-based replication: copy the changes instead of executing the command from the server. Support starts with mysql5.0

(3) mixed type replication: statement-based replication is adopted by default, and row-based replication is adopted once it is found that statement-based replication cannot be accurately replicated.

2. Problems that can be solved by Mysql replication

(1) data distribution (Data distribution)

(2) load balancing (load balancing)

(3) data backup (Backups) to ensure data security

(4) High availability and fault tolerant rows (High availability and failover)

(5) to realize the separation of read and write to alleviate the pressure on the database.

3. The principle of Mysql master-slave replication.

The master server records the change of data in the binary binlog log, and when the data on the master changes, it writes the change to the binary log. The salve server will detect whether the master binary log has changed within a certain time interval, and if so, it will start an I/OThread request master binary event, and the master node will start a dump thread for each I/OThread O thread, which is used to send binary events to it and save them to the local relay log of the slave node. The slave node will start the SQL thread to read the binary log from the relay log. Replay locally, so that its data is consistent with that of the master node, and finally I/OThread and SQLThread will go to sleep, waiting for the next time to wake up.

Note:

1--master records the operation statement in the binlog log, and then grants slave permission to connect remotely (master must enable binlog binary log function; usually for data security reasons, slave also enables binlog feature).

2--slave starts two threads: the IO thread and the SQL thread. The IO thread is responsible for reading the binlog content of the master to the relay log relay log; the SQL thread is responsible for reading the binlog content from the relay log log and updating it to the slave database, so as to ensure that the slave data is consistent with the master data.

3--Mysql replication requires at least two Mysql services. Of course, Mysql services can be distributed on different servers, or multiple services can be started on one server.

4--Mysql replication is best to ensure that the version of Mysql on the master and slave servers is the same (if the version cannot be met, then the version of the master master node is lower than that of the slave slave node)

The time between 5--master and slave nodes needs to be synchronized

The flow chart of Mysql replication is as follows:

As shown in the above figure:

The first part is that master records binary logs. Before each transaction updates the data, master records these changes in the second log. MySQL writes transactions serially to the binary log, even if the statements in the transaction are executed across each other. After the event is written to the binary log, master notifies the storage engine to commit the transaction.

The second part is that slave copies the binary log of master to its own relay log. First, slave starts a worker thread-- the Imax O thread. The iUnip O thread opens a normal connection on master and then starts binlog dump process. Binlog dump process reads events from master's binary log, and if it has caught up with master, it sleeps and waits for master to generate new events. The Icano thread writes these events to the relay log.

SQL slave thread (SQL from the thread) handles the last step of the process. The SQL thread reads events from the relay log and replays the events to update the data in slave to make it consistent with the data in master. As long as the thread is consistent with the Ibank O thread, the relay log is usually in the cache of OS, so the overhead of the relay log is small.

In addition, there is a worker thread in master: like other MySQL connections, slave opening a connection in master causes master to start a thread. There is an important limitation to the replication process-replication is serialized on slave, which means that parallel update operations on master cannot operate in parallel on slave.

4. Mysql replication mode

(1) Master-slave replication: the master database authorizes the process of remote connection from the slave database, reading binlog logs and updating to the local database; after the master database writes data, the slave database will automatically synchronize (the slave database changes with the master database)

(2) Master-master replication: the process in which the master and slave authorize each other to connect, read each other's binlog logs and update them to the local database; as long as the data of the other party changes, it changes itself.

5. The advantages of Mysql master-slave replication.

(1) query can be performed on the slave server (that is, the read function we often talk about) to reduce the pressure on the master server; (write to the master library, read from the library, step down)

(2) backup from the master server to avoid affecting the service of the master server during the backup; (ensure data security)

(3) when there is a problem with the master server, you can switch to the slave server. (improve performance)

6. Mysql master-slave replication workflow details

(1) MySQL supports one-way, asynchronous replication, in which one server acts as a master server and one or more other servers act as slave servers. MySQL replication is based on the primary server tracking all changes to the database (updates, deletions, and so on) in the binary log. Therefore, to replicate, binary logging must be enabled on the primary server. Each slave server receives saved updates that have been recorded to its binary log on the master server from the master server. When a slave server connects to the master server, it tells the master server to navigate to the location of the last successful update read by the slave server in the log. Receive any updates that have occurred since then from the server and perform the same updates on the local machine. Then block and wait for the primary server to notify the new update. Performing backups from the slave server does not interfere with the primary server, which can continue to process updates during the backup.

(2) MySQL uses three threads to perform replication functions, of which two threads (Sql thread and IO thread) are on the slave server, and the other thread (IO thread) is on the master server.

When the START SLAVE is issued, create an Imax O thread from the server to connect to the master server and have it send statements recorded in its binary log. The master server creates a thread to send the contents of the binary log to the slave server. This thread can be the Binlog Dump thread in the output of SHOW PROCESSLIST on the primary server. The content sent by the master server Binlog Dump thread is read from the slave server Icano thread and copied to the local file in the slave server data directory, that is, the relay log. The third thread, the SQL thread, is created from the server to read the relay log and perform the updates contained in the log. On the slave server, reading and executing update statements are divided into two separate tasks. When started from the slave server, its Iamp O thread can quickly retrieve all binary log contents from the master server, even though the SQL thread is far behind in performing updates.

7. Summary of knowledge points

The process of synchronizing master-slave data:

1) execute the sart slave command on the Slave server to turn on the master-slave replication switch and start master-slave replication.

2) at this point, the I / O thread of the Slave server connects to the master server through a request for replication user rights authorized on the master, and requests that the binlog log contents be sent from the specified location where the binlog log file is executed (the log file name and location are specified by the change master command when configuring the master-slave replication service).

3) after the Master server receives the request from the IO thread of the Slave server, the IO thread responsible for replication on it reads the binlog log information after the specified location of the specified binlog log file in batches according to the information requested by the IO thread of the Slave server, and then returns it to the IO thread on the Slave side. In addition to the contents of the binlog log, there are IO threads recorded on the Master server side. The information returned is except for the next specified update location in the binlog.

4) when the IO thread of the Slave server obtains the log contents, log files and location points sent by the IO thread on the Master server, the binlog log contents will be written to the end of the server's own Relay Log (relay log) file (Mysql-relay-bin.xxx), and the new binlog file name and location will be recorded in the master-info file. So that the next time you read the new binlog log on the master side, you can tell the Master server to start reading the new binlog log from the specified file and location of the new binlog log.

5) the Slave thread on the server side detects the new log content of the IO thread in the local Relay Log in real time, then parses the contents of the Relay LOG file into sql statements in time, and executes such sql statements on its own Slave server in the order in which the SQL statements are parsed, and records the file name and location point of the current application relay log in relay-log.info.

Necessary conditions for master-slave replication

1) enable Binlog function

2) the main database needs to establish an account.

3) configure master.info (CHANGE MASTER to...) from the library Equivalent to configuring password files and Master related information)

4) start slave enables replication

What master-slave replication needs to know:

1) 3 threads, master library IO, slave libraries IO and SQL and their functions

2) master.info (slave library) function

3) relay-log action

4) Asynchronous replication

5) binlog function (enable Binlog if cascading is required)

Note:

1) Master-slave replication is asynchronous logical SQL statement-level replication

2) when copying, the master library has one I / O thread, and the slave library has two threads, Icano and SQL threads.

3) the necessary condition for master-slave replication is that the master library should turn on the record binlog function.

4) the server-id of all replicated Mysql nodes cannot be the same

5) the binlog file only records SQL statements that have changes to the database (changes from the contents of the main database), not any query (select,show) statements.

Completely dissolve the master-slave replication relationship

1) stop slave

2) reset slave; or delete the two files master.info and relay-log.info directly

3) modify my.cnf to delete master / slave configuration parameters.

Let slave not start automatically with MySQL

Modify my.cnf to add skip-slave-start option in [mysqld].

After MySQL master-slave replication is done, when backing up data using mysqldump, be sure to follow the following steps:

Mysqldump-master-data-single-transaction-user=username-password=password dbname > dumpfilename

In this way, you can retain the information of file and position. When you build a new slave, after restoring the database, the information of file and position is also updated, and then start slave can quickly complete incremental synchronization!

There are three ways to limit which databases to synchronize:

1) qualify the database when performing grant authorization

2) qualify binlog_do_db = database name on the primary server

3) the database is not qualified on the master server, but the replicate-do-db = database name is qualified on the slave server

If you want to achieve a master-slave (master)-slave chain structure, you need to set:

Only by adding it to log-slave-updates can the data synchronized from one previous machine be synchronized to the next.

Of course, binary logging must also be enabled:

Log-bin=/opt/mysql/binlogs/bin-log

Log-bin-index=/opt/mysql/binlogs/bin-log.index

You can also set a log save cycle:

Expire_logs_days=14

Easy deployment of MySQL master-slave replication in production scenarios

Quick steps MySQL Master-Slave replication

1) install and configure the database of the slave database and configure the log-bin and server-id parameters

2) there is no need to configure the main library my.cnf. The log-bin and server-id parameters of the main library are configured by default.

3) Log in to the master database, increase the account that is synchronized with the slave library, such as rep, and authorize replication synchronization

4) use the mysqldump command with-x and-master-data=2 commands and parameters to restore it to the slave library

5) execute CHANGE MASTER TO from the library. . Statement, the binlog file and the corresponding point are required (because-master-data=2 is already with it)

6) enable the synchronization switch from the library, start slave

7) show slave status\ G from the slave library, check the synchronization status, and update the test in the master library

The working status of the main library Icano

The working status of the main library Icano thread

Explain and explain

Sending binlog event to slave

The thread has read an event from the binary binlog log and is sending it to the slave server

Finished reading one binlog;switching to next binlog

The thread has finished reading the binary binlog log file and opens the next binlog log file to be sent to the slave server

Master Has sent all binlog to slave;waiting for binlog to be updated

The thread has read all updates from the binlog log and has sent them to the slave database server. The thread is now idle, waiting to be updated by new events in the binary binlog log on the primary server.

Waiting to finalize termination

A simple state sent when a thread stops

From the library I to O thread working status show processlist

From the library I to O thread working status

Explain and explain

Connecting to master

The thread is trying to connect to the primary server

Checking master version

Synchronize the temporary state after the establishment between the primary servers

Registering slave to master

Requesting binlog dump

The state that occurs temporarily immediately after the connection between the database and the master server. The thread sends a request to the master server for the contents of the binary binlog log starting from the requested binary binlog log file name and location.

Waiting to reconnect after a failed binlog dump request

If the binary binlog log transfer request fails, the thread sleeps and attempts to reconnect

Reading event from the relay log

The thread has read an event from the relay log and is ready to process the event.

Slave Has read all relay log;waiting for the slave I/O thread to update it

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

Waiting for slave mutex on exit

A very simple state occurs when the thread stops.

Article link: http://www.cnblogs.com/kevingrace/p/6256603.html

Related connections:

Deployment of MySQL master-slave replication environment: [http://blog.itpub.net/31015730/viewspace-2153251/]

Deployment of MySQL multi-instance environment: [http://blog.itpub.net/31015730/viewspace-2153184/]

Install mysql database and problem solving: [http://blog.itpub.net/31015730/viewspace-2152272/]

Author: SEian.G (hard practice changes in 72, but it is difficult to laugh at 81)

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.