MySQL master-slave, master-master replication, and high availability

The ddl and dml operations of the master database on the master server are uploaded to the slaves server through binary logs, and these log files are re-executed on the master server.

The ddl and dml operations of the master database on the master server are uploaded to the slaves server through binary logs, and these log files are re-executed on the master server.

I. MySQL replication:

MySQL replication introduction:

The ddl and dml operations of the master database on the master server are uploaded to the slaves server through binary logs, and these log files are re-executed on the master server, in this way, the data on the slave server and the master server is synchronized.

Principles of Mysql replication:

Distributing data to multiple systems is achieved by copying data from one of Mysql's master hosts to another (slave) host and executing it again;

One server acts as the master server during the replication process, and one or more other servers act as the slave server. The master server writes updates to binary log files and maintains an index of the files to track log loops.

These logs can record updates sent to the slave server. When an slaves server is connected to the master server, it notifies the master server of the last successful update location read from the server in the log. The server Load balancer receives any updates from that time, blocks them, and waits for the master server to notify you of new updates.

Recommended reading:

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL + KeepAlived + LVS single-point write master/Master synchronization high-availability architecture Experiment

MySQL master/Master synchronization Configuration

MySQL master-slave copy notes in CentOS 6.3

Advantages of mysql replication:

Perform query operations on the slave server to reduce the access pressure on the master server

If a problem occurs on the master server, you can switch to the slave server without access interruption.

Back up on the slave server to avoid affecting the service usage and daily access of the master server during the backup.

The replication function of Mysql is the basis for building large and high-performance applications.

Mysql supports the following replication types:

Statement-based replication: SQL statements executed on the master server run the same statement 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.

Row-based replication: Copies the changed content instead of executing the command on the slave server. It is supported from mysql5.0.

Hybrid replication: Statement-based replication is used by default. If statement-based replication fails, row-based replication is used.

Features of MySQL replication technology:

Data distribution)

Backup (Backups)

Load balancing)

High availability and fault tolerance High availability and failover

Replication Process:

The master will change the records to the binary log (these records are called binary log events, binary log events );

Slave copies the binary log events of the master to its relay log );

Redo the events in the relay log by slave, which will reflect its own data.

Step 1: The master node 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;

Step 2: slave copies 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;

Step 3: The last step for SQL slave thread to process the 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.

Next, see page 2nd: