MySQL realizes enterprise-level master-slave replication and inter-Master slave mode architecture

In the context of implementing multiple modes of MySQL, let's first introduce how MySQL works.

Probably the network topology diagram is as follows:

1, the first part of the process is the master record binary log. Master records These changes in binary logging before each transaction updates the data. MySQL writes the transaction serially to the binary log, and when the event is written to the binary log completion, Master notifies the storage engine to commit the transaction. You can then receive the slave request.

2. The next step is to copy the binary log of Master slave to its own relay logs. First, slave starts a worker thread--i/o thread. The I/O thread opens a normal connection on master and then starts Binlog dump process (binary dump thread) on the master node. Binlog dump process reads the event from the binary log of master, and if it has been followed by master, it sleeps and waits for master to produce a new event. The I/O thread writes these events to the relay log.

3. SQL slave thread (SQL slave thread) handles the last step of the process. The SQL thread reads events from the log and replays the events in them to update the slave data so that it is consistent with the data in master. As long as the thread is consistent with the I/O thread, the trunk log is typically located in the OS cache, so the overhead of the trunk log is minimal.

First, the master-slave replication mode.

1. Environment Preparation:

CentOS System server 2, a user to do MySQL master server (172.17.110.111), one for the MySQL slave server (172.17.38.39), CONFIGURED yum source, firewall shutdown, each node clock service synchronization, Each node can communicate with each other through the host name.

2. Preparation steps:

(1) iptables-f && setenforce Empty firewall policy, turn off SELinux

(2) Take two servers to install the MySQL service using Yum mode, requiring the same version

(3) Start the MySQL service of two servers separately to ensure the service is normal

① The master configuration file for configuration master.

② Start the service, create a copy account, and authorize the slave.

You can also view the binaries to see what's updated.

③ Configuring the master configuration file for slave

④ lets slave connect master, reading the things that the master binary log updates.

Second, to achieve mutual master from the framework.

① Modifying a configuration file (172.17.110.111 server)

Two server profiles except for the same ID, the others are the same (172.17.38.39 server).

② Mutual authorization, that is, each other even if the other's Lord is also the other side from.

On (172.17.38.39 server) point (172.17.110.111), and view status

On the 172.17.110.111 server, point to (172.17.38.39), and view the status.

③ test.

Create a database (172.17.110.111 server) that stores content. Check for synchronization on (172.17.38.39 server).

Create a database in (172.17.38.39 server) that stores content. Check for synchronization on (172.17.110.111 server).

MySQL implements enterprise-level master-slave replication and inter-primary slave schema architecture