MySQL replication and Performance Optimization

Replication: Copies executed statements or datasets from the master server to one or more slave servers to synchronize databases between multiple database servers.

MySQL's built-in replication capability is the foundation for building large, highperformance applications on top of MySQL.

MySQL supports two kinds of replication: statement-based replication (statement-based replication) and row-based replication (row-based replication ).

Statement-based (or "logical") replication has been available since MySQL 3.23, and it's what most people are using in production today

Row-based replication is new in MySQL 5.1.

Both kinds work by recording changes in the master's binary log and replaying the log on the slave

Statement-based replication: records the statements that change the database, and executes the statements on the slave server, which is more efficient.

Row-based replication: after the statement is executed, the results include changes to the row or the whole addition is copied to the slave server.

Hybrid replication: MySQL determines automatically.

Replication Process:

Thread on the master server: mysql dump

The slave server has two threads: I/O thread and SQL thread.

Process: from the server's I/O thread (the master server's remote client) constantly tries to connect to the master server and read its binary log, after receiving the request, the master server checks its Binary Log and checks whether the content of the master server has been updated since the last copy based on the Relay Log information sent from the slave server. If yes, the master server starts the mysql dump thread and returns the data requested by the other party to the slave server. After receiving the data, the slave server saves the data in the Relay Log. The SQL thread reads the Relay Log from time to time. If an update is found, it reads the updated statement or stores it on the slave server.

MySQL solves the following problems: data backup, Server Load balancer, high availability, data distribution (Remote Disaster Recovery), and upgrade testing.

The architecture based on MySQL master-slave replication is as follows:

Master: 192.168.1.11 MySQL has been installed

[Root @ station39 ~] # Vim/etc/my. cnf

Log-bin = master-bin // ** update line 50

Log-bin-index = master-bin.index // ** add line 51

Server-id = 1 // ** line 59

1 2 3 4 5 6 7 8 9 Next Page