preface

MySQL > MySQL > MySQL > MySQL > MySQL > MySQL

Copy the overview

Mysql’s built-in replication capabilities are the foundation for building large, high-performance applications. The data of Mysql is distributed to multiple systems. This distribution mechanism is realized by copying the data of a host of Mysql to other hosts and performing it again. One server acts as the master server and one or more other servers act as slave servers during replication. The master server writes updates to the binary log file and maintains an index of the file to track the log cycle. These logs record updates sent to the slave server. When a slave connects to the master, it informs the master of the last successful update read by the slave in the log. The slave server receives any updates that have occurred since then, then blocks and waits for the master server to notify of new updates.

Note that when you replicate, all updates to the replicated tables must be made on the master server. Otherwise, you must be careful to avoid conflicts between user updates to tables on the master server and updates to tables on the slave server.

1.1 Mysql supports the following replication types:

(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 efficient. Row-based replication is automatically selected when it is found to be impossible to replicate exactly.

(2) : line-based replication: copying changes over instead of executing commands on the slave server, supported starting with mysql5.0

(3) : Mixed type replication: statement-based replication is adopted by default. Once it is found that statement-based replication cannot be accurate, row-based replication is adopted.

1.2 Problems Solved by Replication

MySQL replication technology has the following features:

(1) Data distribution

(2) Load balancing

(3) the backup (Backups)

(4) High availability and failover

1.3 How does Replication work

Overall, there are three steps to replication:

(1) The master logs the changes to the binary log (binary log events).

(2) Slave copies master binary log events to its relay log;

(3) The slave rewrites events in the relay log to change the data that reflects itself.

The following diagram illustrates the replication process:

The first part of the process is for the master to log binary logs. Before each transaction updates the data, the master logs these changes in the second log. MySQL writes transactions serially to the binary log, even though the statements in the transaction are executed across. After the event is written to the binary log, the master notifies the storage engine to commit the transaction. The next step is for the slave to copy the master’s binary log into its own relay log. First, slave starts a worker thread — the 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 events from the master’s binary logs. If it has followed the master, it sleeps and waits for the master to generate new events. The I/O thread writes these events to the relay log. SQL slave Threads process the last step of the process. The SQL thread reads the events from the slave log and replays the events to update the slave data to match the data in the master. As long as the thread is consistent with the I/O thread, the relay log is usually in the OS cache, so the overhead of the relay log is minimal. In addition, there is a worker thread in the master: as with all MySQL connections, opening a connection in the master causes the master to start a thread. The replication process has one important limitation — replication is serialized on the slave, which means that parallel updates on the master cannot be performed in parallel on the slave.

Copy the configuration

There are two MySQL database servers: Master and slave. Master is the Master server and slave is the slave server. In the initial state, the data information in Master and slave is the same. Synchronize data between the master and slave for backup.

Important: The medium responsible for transferring the various changes between the master and slave servers is the binary change log of the master server, which records the various changes that need to be transferred to the slave server. Therefore, binary logging must be enabled on the primary server. The slave server must have permissions that allow it to connect to the master server and request that the binary change log be transferred to it. Environment: The MySQL database version of the Master and slave databases is 5.0.18 operating system: Unbuntu 11.10 IP address: 10.100.0.100

2.1. Create a replication account

1. Create a backup account in the Master database: each slave connects to the Master using the standard MySQL username and password. The user performing the REPLICATION operation is granted REPLICATION SLAVE. Usernames and passwords are stored in a text file called master.info

Mysql > GRANT REPLICATION SLAVE,RELOAD,SUPER on. TO backup@ ‘10.100.0.200’ IDENTIFIED BY ‘1234’;

Create a backup account and allow login only from 10.100.0.200 with password 1234.

Set password for ‘backup’@’10.100.0.200’=old_password(‘1234’)

2.2. Copy data

(This step is not necessary if you have completely new mysql master/slave server installed. Because the newly installed master and slave have the same data.

Shut down the Master server and copy the data in the Master server to the B server to synchronize the data in the Master and slave servers. Make sure that no write operations are performed on the Master and slave servers until all Settings are complete. Ensure that the data in the two databases must be the same.

2.3. Configure the Master

Next configure the master, including turning on binary logging and specifying a unique Servr ID. For example, add the following values to the configuration file:

server-id=1 log-bin=mysql-bin

Server-id: indicates the ID of primary server A. log-bin: indicates the binary change daily value

Restart the master and run SHOW master STATUS. The following output is displayed:

2.4. 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-bin server_id = 2 relay_log= mysql-relay-bin log_slave_updates = 1 read_only= 1

Server_id is required and unique. It is not necessary to enable binary log on the slave. However, it must be enabled in some cases. For example, if the slave is the master of another slave, bin_log must be enabled. Here, we have binary logging turned on, and the naming of the display (the default name is hostname, but there are problems if the hostname changes).

Relay_log configures the relay log, log_slave_updates means that slave writes replication events to its binary log (see how useful this is later). Some people enable binary logging for their slave without setting log_slave_updates, and then check to see if the slave data has changed. This is a configuration error. So, try to use read_only, which prevents data from changing (except for special threads). However, read_ONLY is not very useful, especially if you need to create tables on your slave.

2.5. Start slave

The next step is to have the slave connect to the master and start redoing the events in the master binary log. You should not do this with a configuration file, but instead use the CHANGE MASTER TO statement, which completely replaces the modification TO the configuration file, and it can specify a different MASTER for the slave without stopping the server. Mysql > CHANGE MASTER TO MASTER_HOST=’server1′,

-> MASTER_USER='repl',

-> MASTER_PASSWORD='p4ssword',

-> MASTER_LOG_FILE='mysql-bin.000001',

-> MASTER_LOG_POS=0;
Copy the code

‘MASTER_LOG_POS has a value of 0 because it is where the log starts.

You can check whether the SLAVE is set correctly by using the SHOW SLAVE STATUS statement:

`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`Copy the code

Slave_IO_State, Slave_IO_Running, and Slave_SQL_Running are No

Indicates that slave has not started the replication process. The log position is 4 instead of 0 because 0 is just the start of the log file, not the log position. In fact, the location of the first event that MySQL knows is 4.

To start copying, you can run:

`mysql> START SLAVE;

Run SHOW SLAVE STATUS to view the output:

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`Copy the code

Here is mainly to see:

Slave_IO_Running=Yes Slave_SQL_Running=Yes

The SLAVE I/O and SQL threads are already running, and Seconds_Behind_Master is no longer NULL. The location of the log is increased, which means that some events are fetched and executed. If you make changes on the master, you can see changes in the location of various log files on the slave, as well as changes in the data in the database.

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

Type show processList \G on master;

Line 2 is the connection that handles the SLAVE I/O thread.

Run this statement on the slave server:

Line 1 is the I/O thread state, and line 2 is the SQL thread state.

2.5. Add a slave server

If the master has been running for a long time and you want to synchronize data to the newly installed slave, even if it does not have the master’s data.

At this point, there are several ways to make a slave start from another service, for example, copy data from the master, clone data from another slave, and start a slave from the most recent backup. To synchronize the Slave and master, you need three things:

(1) A snapshot of the data at the master point in time;

(2)master the current log file and the byte offset when the snapshot was generated. These two values are called log file coordinate, because they determine the location of a binary log, and you can use the SHOW MASTER STATUS command to figure out the coordinate of the log file;

(3) Master binary log file.

You can clone a slave in one of the following ways:

(1) Cold copy

Stop master and copy master files to slave. Then restart the master. The disadvantages are obvious.

(2) Warm copy

If you only use MyISAM tables, you can use mysqlHotCopy even if the server is running.

(3) use the mysqldump

Using mysqldump to get a snapshot of the data can be divided into the following steps:

<1> Lock table: if you do not already lock the table, you should lock the table to prevent other connections from modifying the database, otherwise, you can get inconsistent data. Mysql > FLUSH TABLES WITH READ LOCK; < 2> Create a dump of the database you want to replicate using mysqldump on another connection: shell> mysqldump –all-databases –lock-all-tables >dbdump.db < 3> Release the lock on the table. mysql> UNLOCK TABLES;

Learn more about replication

Having covered some of the basics of replication, let’s take a closer look at replication.

3.1 Statement-based Replication

MySQL 5.0 and earlier only supported statement-based replication (also known as logical replication, logical replication), which is not common in databases. The master records the query that changes the data. The slave then reads the event from the slave slave log and executes it. These SQL statements are the same as those executed by the master.

The advantage of this approach is the simplicity of implementation. In addition, statement-based replication of binary logs can be well compressed and log data is small and bandwidth intensive – for example, a query updating gigabytes of data requires only a few dozen bytes of binary logs. Mysqlbinlog is handy for statement-based log processing.

However, statement-based replication is not as simple as it seems, because some query statements depend on specific conditions for the master; for example, the master and slave may have different times. So, MySQL binary logs are formatted not just for queries, but also for metadata information, such as the current timestamp. Even so, some statements, such as the CURRENT USER function, do not copy correctly. In addition, stored procedures and triggers are an issue.

Another problem is that statement-based replication must be serialized. This requires a lot of special code, configuration, such as InnoDB’s next-key lock, etc. Not all storage engines support statement-based replication.

3.2 Row-based Replication

MySQL adds record-based replication, recording changes to the actual data in a binary log, similar to how some other DBMSS are implemented. There are advantages and disadvantages to this approach. The advantage is that it can work correctly with any statement, and some statements are more efficient. The main drawback is that binary logs can be large and unintuitive, so you can’t use mysqlbinlog to view binary logs. Record-based copying works more efficiently for some statements, such as:

mysql> INSERT INTO summary_table(col1, col2, sum_col3) -> SELECT col1, col2, sum(col3) -> FROM enormous_table -> GROUP BY col1, col2;

Suppose there are only three unique col1 and COL2 combinations, but the query scans many rows of the original table and returns only three records. At this point, record-based replication is more efficient.

On the other hand, statement-based replication is more efficient with the following statements:

mysql> UPDATE enormous_table SET col1 = 0; Record-based replication can be very expensive at this point. Since neither approach works well in all cases, MySQL 5.1 supports dynamic exchange before statement-based replication and record-based replication. You can control this by setting the session variable binlog_format.

3.3. Copy related files

In addition to binary and relay log files, there are other copy-related files.

As follows:

(1)mysql-bin.index

Once binary logging is enabled on the server, a file with the same name as the binary log file but ending with.index is generated. It is used to track which binary log files exist on disk. MySQL uses it to locate binary log files. It reads as follows (on my machine) :

(2)mysql-relay-bin.index

The function of this file is similar to that of mysql-bin.index, but it is for relay logs, not binary logs. \mysql-02-relay-bin.000017.\mysql-02-relay-bin.000018

(3)master.info

Save information about the master. Do not delete it; otherwise, the slave cannot connect to the master after it restarts. It reads as follows (on my machine) :

The I/O thread updates the master.info file with the following content (on my machine) :

(4)relay-log.info

Contains information about current binary logs and trunk logs of the slave.

3.4. Send replication events to other slaves

When setting log_slave_updates, you can have your slave act as the master of another slave. At this point, the slave writes the events executed by the SQL thread into its own binary log, which its slave can then retrieve and execute. As follows:

3.5 Replication Filters

Replication filtering allows you to copy only a portion of the server’s data. There are two types of replication filtering: filtering binary log events on the master; Filter events in the trunk log on the slave. As follows:

Common topologies for replication

The architecture of replication has the following basic principles:

(1) Each slave can have only one master;

(2) Each slave can only have a unique server ID;

(3) Each master can have many slaves;

(4) If you set log_slave_updates, the slave can be the master of another slave, thereby spreading out the master updates.

MySQL does not support Multimaster Replication — that is, a slave can have multiple masters. However, with a few simple combinations, we can build flexible and powerful replication architectures.

4.1. Single Master and Multiple Slaves

A replication system consisting of one master and one slave is the simplest case. The slaves do not communicate with each other, but only with the master. As follows:

If the write operation is small and the read operation is very short, this structure can be adopted. You can distribute reads to other slaves to reduce the stress on the master. However, when the number of slaves increases to a certain level, the load of slaves on the master and network bandwidth becomes a serious problem. This structure is simple, but it is flexible enough to meet most application requirements. Some suggestions:

(1) Different slaves perform different functions (such as using different indexes or storage engines);

(2) Use a slave as the standby master, and only replicate;

(3) Use a remote slave for disaster recovery;

4.2 Master Mode (master-master in active-active Mode)

Master-master The two replication servers are both masters and slaves of the other server. As shown in figure:

Active master-master replication has some special uses. For example, both geographically distributed parts need their own writable copies of data. The biggest problem with this structure is update conflict. Suppose a table has only one row (column) of data with the value 1, if both servers execute the following statement simultaneously:

mysql> UPDATE tbl SET col=col + 1; Mysql > UPDATE TBL SET col=col * 2;

So what is this going to be? One server is 4 and the other is 3, however, this does not generate an error. In fact, MySQL does not support Multimaster Replication, which is supported by some other DBMSS. This is a major limitation of MySQL’s Replication capabilities (the difficulty with multi-master is resolving update conflicts), but if you have such a need, You can take MySQL Cluster and combine Cluster with Replication to build a powerful high-performance database platform. However, there are other ways to simulate this multi-master server replication.

4.3 master-master (master-master in active-passive Mode)

This is a variation of the master-master structure, which avoids m-M’s shortcomings and is, in effect, a fault-tolerant and highly available system. The difference is that one of the services can only be read-only. As shown in figure:

4.4 Master-Master with Slaves

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

The last

I here organized a MySQL related information documents, Spring series of family barrel, Java systematic information (including Java core knowledge points, interview topics and 20 years of the latest Internet real questions, e-books, etc.) friends who need to pay attention to the public number can be obtained.