High-performance MySql evolution (14th): Replication)

Overview

The built-in replication function of Mysql is the basis for building large and high-performance applications. This distribution mechanism replicates data from a Mysql host to another Server Load balancer instance, and execute it again. During the replication process, one server acts as the master server, and one or more other servers act as slave servers. 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.

Note that when you perform a copy, all updates to the tables in the copy must be performed on the master server. Otherwise, you must be careful to avoid conflicts between updates to tables on the master server and those on the slave server.

Replication types supported by Mysql

(1) Statement-based replication: SQL statements executed on the master server and the same statements executed on the slave server. MySQL uses statement-based replication by default, which is more efficient.
The row-based replication option is automatically selected once the exact replication fails.
(2): Row-based replication: Copies the changed content instead of executing the command on the slave server. It is supported from mysql5.0.
(3) hybrid replication: Statement-based replication is used by default. If statement-based replication fails, row-based replication is used.

Problems solved by Replication

MySQL replication technology has the following features:
(1) Data distribution)
(2) load balancing)
(3) backup (Backups)
(4) High availability and fault tolerance High availability and failover

How replication works

In general, there are three steps for replication:

(1) The master will record changes to binary logs (these records are called binary log events and binary log events );
(2) slave copies the binary log events of the master to its relay log );
(3) slave redo the events in the relay log, which will change and reflect its own data.

Describes the replication process:

The first part of this process is that the master records binary logs. Before each Transaction Completes data update, the master will record these changes in the second log. MySQL writes transactions into binary logs in sequence, even if the statements in the transaction are executed in a crossover manner. After the event is written to the binary log, the master notifies the storage engine to submit the transaction.
The next step is to copy the binary log of the master to its own relay log. First, slave starts a working thread-I/O thread. The I/O thread opens a normal connection on the master and starts the binlog dump process. The Binlog dump process reads the event from the binary log of the master. If it has already followed the master, it will sleep and wait for the master to generate a new event. The I/O thread writes these events to the relay log.
The last step of SQL slave thread processing this process. The SQL thread reads the event from the relay log, replays the event, and updates the slave data so that it is consistent with the data in the master. As long as the thread is consistent with the I/O thread, the relay log is usually located in the OS cache, so the overhead of the relay log is very small.
In addition, there is also a working thread in the master: Like other MySQL connections, opening a connection in the master will also enable the master to start a thread. There is an important limitation in the replication process-replication is serialized on the slave, that is, the parallel update operations on the master cannot be performed in parallel on the slave.

Copy Configuration

There are twoMySQL databaseThe Master and slave servers are the Master servers. The slave servers are slave servers. In the initial state, the data information in the Master and slave is the same. When the data in the Master node changes, slave also changes accordingly, so that the master and slave data information is synchronized to achieve the purpose of backup.

Key points:
The media responsible for transmitting various modification actions on the master and slave servers is the binary change log of the master server, which records the various modification actions to be transmitted to the slave server. Therefore, the master server must activate the binary log function. The slave server must have the permission to connect to the master server and request the master server to transmit the binary change log to it.

Create a replication account

1. Create a backup account in the Master database: each slave uses the standard MySQL user name and password to connect to the master. The REPLICATIONSLAVE permission is granted to the user who performs the copy operation. The username and password are stored in the master.info text file.

The command is as follows:

mysql > GRANT REPLICATIONSLAVE,RELOAD,SUPER ON *.*                TO backup@'192.168.43.129'                 IDENTIFIED BY '1234';

Create an account backup and only Allow logon from 192.168.43.129. The password is 1234.

(If the new and old password algorithms of mysql are different, you can set: set password for 'backup '@ '192. 168.43.129' = old_password ('200 '))

Configure master

Next, configure the master, including opening the binary log and specifying a unique servr ID. For example, add the following values to the configuration file:

server-id=10log-bin=mysql-bin

 

Server-id: ID value of master server
Log-bin: Binary Change Daily Value

Restart the master and run showmaster status. The output is as follows:

Configure slave

The Slave configuration is similar to that of the master. You also need to restart the slave MySQL. As follows:

log_bin           = mysql-binserver_id         = 2relay_log         = mysql-relay-binlog_slave_updates = 1read_only         = 1

Server_id is required and unique. Slave does not need to enable binary logs, but in some cases, it must be set. For example, if slave is another slave master, bin_log must be set. Here, we enable binary logs and display the name (the default name is hostname, but if the hostname is changed, the problem will occur ).
Relay_log configures the relay log. log_slave_updates indicates that slave writes the replication event into its own binary log (which will be useful later ).
Some people have enabled the slave binary log, but have not set log_slave_updates, and then check whether the slave Data has changed. This is an incorrect configuration. Therefore, use read_only whenever possible, which prevents data changes (except for special threads ). However, read_only is very useful, especially for applications that need to create tables on slave. Copy Data

(If you have completely installed the mysql Master/Slave server, this step is not required. Because the newly installed master and slave have the same data)

The following methods are used to copy data:

Cold backup

Shut down the Master server and copy the data in the Master to the server B to synchronize the data in the Master and Server Load balancer. This method has obvious problems. You need to restart the Master database server.

Hot Backup

If all tables in the database use MYISAM tables, you can use mysqlhotcopy or rsync to copy data when the master database is running.

Use mysqldump

If you only use innodb, you can run the following command to copy

mysqldump -u root -p --single-transaction --all-databases --add-drop-database --disable-keys --flush-privileges --quick --routines --triggers | mysql -h 192.168.43.129 -u root –p

If a 1130 HY000 exception occurs during the replication process, seeHttp://blog.csdn.net/eric_sunah/article/details/18567091

Do not perform write operations on the Master and slave servers, so that the data in the two databases must be the same!

Start slave

Next, let slave connect to the master and begin to redo the events in the master binary log. Instead of using the configuration file for this operation, you should use the change master to statement. This statement can completely replace the modification TO the configuration file, and it can specify different masters for slave, you do not need to stop the server. As follows:

mysql> CHANGE MASTER TO MASTER_HOST='server1',    -> MASTER_USER='backup',    -> MASTER_PASSWORD='1234',    -> MASTER_LOG_FILE='mysql-bin.000001',    -> MASTER_LOG_POS=0;

The value of MASTER_LOG_POS is 0 because it is the start position of the log.

You can use the show slave status statement to check whether the slave settings are correct:

mysql> SHOW SLAVE STATUS\G

* *************************** 1. row ***************************

Slave_IO_State:

Master_Host: server1

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 4

Relay_Log_File: mysql-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: No

Slave_ SQL _Running: No

... Omitted...

Seconds_Behind_Master: NULL

Slave_IO_State, Slave_IO_Running, and Slave_ SQL _Running are No, indicating that slave has not started the replication process. The log location is 4 rather than 0, because 0 is the start position of the log file, not the log location. In fact, MySQL knows that the first event is located at 4.

To start replication, you can run:

mysql>START SLAVE;

Run show slave status to view the output result:

mysql>SHOW SLAVE STATUS\G

* *************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: server1

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 164

Relay_Log_File: mysql-relay-bin.000001

Relay_Log_Pos: 164

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

... Omitted...

Seconds_Behind_Master: 0

Here we mainly look:
Slave_IO_Running = Yes
Slave_ SQL _Running = Yes

Slave's I/O and SQL threads have started to run, and Seconds_Behind_Master is no longer NULL. The log location is increased, meaning that some events are obtained and executed. If you modify the log file on the master, you can view the changes in the location of various log files on the slave, and you can also see the changes in the database data.

You can view the status of the master and slave threads. On the master, you can see the connection created by the slave I/O thread:

Enter show processlist \ G on the master;

Mysql> show processlist \ G * *************************** 1. row *************************** Id: 1 User: root Host: localhost: 2096 Db: test Command: Query Time: 0 State: NULL Info: show processlist * *************************** 2. row *************************** Id: 2 User: repl Host: localhost: 2144 Db: NULL Command: Binlog Dump Time: 1838 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL 2 rows in set (0.00 sec)

Row 2 is the connection to the slave I/O thread.

Run this statement on the slave server:

Mysql> show processlist \ G * *************************** 1. row *************************** Id: 1 User: system user Host: Db: NULL Command: Connect Time: 2291 State: Waiting for master to send event