MySQL replication introduction and Setup

MySQL replication introduction and Setup
MySQL Replication

MySQL replication is a MySQL Server (slave) that replicates logs from another MySQL Server (master) and then parses the logs and applies them to itself, similar to Data Guard in Oracle.

MySQL replication has the following benefits:

• The first is to solve the data inconsistency caused by downtime, because MySQL replication can back up data in real time;

• The second is to reduce the pressure on database servers. The performance of multiple servers is generally better than that of a single server. However, MySQL replication is not suitable for large data volumes. clusters are recommended for large data volumes.

The MySQL replication process is divided into three steps:

• The master will change the log to binary log ). These record processes are called binary log events and binary log events;

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

• Slave retries the events in the relay log and applies the changes to its own database. MySQL replication is asynchronous and serialized.

Prepare the MySQL replication Environment

Master 192.168.1.5

Slave 192.168.1.6

OS: Oracle Linux 6.1

MySQL: 5.5.37

Master Configuration

1) Assign the copy permission

Both the master database and slave Database need to be executed

2) Clear log files

Both the master and slave databases enable binary log files by default.

Note that if you do not want to clear the log file, you need to record the log_file and log_pos of the current master, and specify these two parameters or the slave configuration file when you enable the copy operation below.

Slave settings

1) modify the slave server-id

Restart the database after modification.

2) Clear the log file, same as the Master

3) enable replication

Connect the slave to the master and start to redo the events in the master binary log.

The value of master_log_pos is 0 because it is the start position of the log and master_log_file is the initial log file. If the master log is not cleared, the log information of the current master is displayed.

Note that by default, all the databases under the user will be synchronized. If you want to define which databases are there, there are three ideas.

• In/etc/my. inf on the master, set the database to be synchronized using the binlog-do-db and binlog-ignore-db parameters.

• Restrict the database

• Restrict the database to replicate-do-db = dbname on slave

4) Enable slave

5) check whether the Slave communicates with Mater. If both Slave_IO_Running and Slave_ SQL _Running are yes, the configuration is successful.

Test

Create a database on the Master node

Slave check that the database has been synchronized

Insert data into tables created by the Master

View Slave

Through the above verification, we can see that the changes on the master server can be synchronized to the slave server normally.

Additional instructions

1) After MySQL master-slave replication, when using mysqldump to back up data, be sure to follow the following methods:

In this way, the file and position information can be retained. When a new slave is created, the database is restored, and the file and position information is also updated, then start slave to quickly complete incremental synchronization.

Other replication methods

Master-master Replication

Master-slave can only perform one-way operations, such as half-duplex in the network. Master-master can synchronize servers, and master-master replication can avoid system downtime after a single point of failure. The biggest problem with Master replication is data insertion or update conflict. The configuration method is the same as master-slave replication, which in turn enables slave to synchronize the master. Note that the master_log_file and master_log_pos parameters must correspond to those on the master. For details, refer to here

Single master and multi-slave

A replication system composed of one master and multiple slave instances is relatively simple. Slave does not communicate with each other and can only communicate with the master. If there are few write operations and many read operations, you can use them. You can distribute read operations to other slave instances to relieve the pressure on the master. However, when slave is increased to a certain number, slave's load on the master and network bandwidth will become a problem.

Master-slave multi-level replication

Many read operations can use a single maste and multiple slave. However, increasing the number of slaveIO threads connected to the master after a certain slave increases the pressure on the master, resulting in Data Replication latency. Multi-level replication is designed to solve this problem. If you want to implement master-slave multi-level replication, you need to set the log-slave-updates parameter. Binary logs must also be enabled.

Of course, to increase the cascade level of replication, the same change to the bottom layer of the Slave requires more MySQL, also may cause a long delay risk. If conditions permit, it is preferred to resolve the problem by splitting it into multiple Replication clusters.

This article permanently updates the link address: