Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

MySQL master-slave replication In actual production applications, there is a master-slave mechanism to ensure the stability of service data and prevent production accidents caused by faults. Before understanding the principle of master-slave replication, Let’s go….

Configure the primary and secondary servers

The first step is primary server configuration

  1. First set up the my.cnf configuration file

This step is to enable bnLog logging and set the globally unique server ID

# vim /etc/my.cnf [mysqld] server_id=51 //server_id log-bin=master51 //Copy the code
  1. User authorization is then performed

Authorization Specifies the permission to copy data from the server. You can customize the user name, use % for the client address, or specify only the address of the secondary server.

#mysql -uroot -p

mysql> grant replication slave on . to repluser@”%” identified by”123qqq… A”;

mysql>quit;

Step 2 Configure the slave server

Configure the my.cnf file

Specify server_id. It must be different from the primary server


# vim /etc/my.cnf

[mysqld]  

server_id=52          //server_id

:wq

Copy the code
  1. Specify primary server information
The database administrator root logs on to the local server and specifies the primary server information, where the log file name and offset are recorded in the allbak.sql file. # mysql -uroot -p mysql> show slave status; // Check the status information. Empty set (0.00 SEC) mysql> change master to // specify master -> master_host= "192.168.4.51", // Master server IP address -> master_user= "repluser", // Master server authorized user -> master_password= "123qQQ... A ",// master server authorization user password -> master_log_file= "master51-bin.000001",// Master server log file -> master_log_pos=441; // Primary server log offsetCopy the code
  1. Example Enable the primary/secondary replication command

start slave

After the above SAO operation, master and slave set up, of course, the general company is basically responsible for the construction of MySQL services, but as the most outstanding contemporary programmers, but also need to start their own wave, practice is the only standard to test the truth.

Principle of master-slave replication

MySQL master-slave replication is exponential data that can be copied from a MySQL server master node to one or more slave nodes.

MySQL uses asynchronous replication by default. Secondary nodes can replicate all databases in the primary database or specific databases, or specific tables.

Primary purpose of MySQL primary secondary replication

The master library is responsible for writing and the slave library is responsible for reading

Data is backed up in real time to facilitate failover when a node fails to send data.

Architecture expansion: As service traffic increases in the system, the I/O access frequency is too high if the database is deployed on a single server. By using master/slave replication, multiple data storage nodes are added to distribute load across multiple nodes, reducing disk I/O frequency and improving THE I/O performance of a single machine.

MySQL is in primary/secondary form

  • There is one master who obeys, and one master who obeys many

  • Multiple master and One slave: Multiple master and one slave can back up multiple mysql databases to a server with good storage performance

  • Dual-master replication: In dual-master replication, each master is the master and slave of the other server.

  • Cascading replication: Data synchronization of some slaves is not connected to the master node, but to the slave node. Other secondary nodes are connected to the secondary node as secondary or tertiary nodes, which not only relieves the pressure on the primary node, but also has no negative impact on data consistency.

How replication works

  • The data is recorded to a binary file (bin log) in the master library

  • The slave library copies logs from the master library to its own Relay Log

  • The standby library reads the events in the relay log and replaces them on top of the standby library data.

The diagram shows the workflow of making master slave replication

Connect to the master library, which allocates the dump thread to communicate with the slave IO thread

The primary library dump thread receives the request, intercepts the binary log, and returns it to the secondary library IO thread

Finally write binlog from library IO to relay log, save and update the file name and location of binlog read to master. info file, and tell the Master node which position I need to start from when I read next time.

The SQL thread reads relay. Info to retrieve the location of the last execution

The SQL thread executes the new relay log backwards, updating realy.info again

Master/slave replication

There are three types of master-slave replication: SQL statement-based replication, row-based replication, and mixed mode replication

Copy based on SQL statements, that is, copy SQL statements. In bin log, mysql 5.1.4 and earlier uses this copy format. The advantage is that only the SQL statements that modify data need to be recorded in the binlog, reducing the amount of binlog logs, saving I/0, and improving performance.

Row-based replication, where the mysql master node breaks down SQL statements into row-based changes and records them in a bin log, i.e. only which data was modified and to what extent. The advantage is that there are no specific cases where stored procedures, or functions, or trigger calls or triggers cannot be copied correctly. The disadvantage is that a large number of logs are generated.

Mixed mode replication is a mixture of the preceding two replication modes. For common replication, SQL statements are recorded. For operations that cannot be replicated in SQL statement mode, the mysql database saves logs in row mode.

Master copy get did not, if still not satisfied, then pay attention to small eggs, continue to output dry goods, Ollie to