Shulou(Shulou.com)05/31 Report--

This article focuses on "how to quickly build a master-slave replication architecture in MySQL". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to quickly build a master-slave replication architecture in MySQL".

1. Master-slave replication and read-write separation 1.1 master-slave replication (data plane)

Master-slave replication is the premise of the separation of read and write. The master-slave replication function provided by MySQL database can easily realize automatic backup of data in multiple servers, realize the expansion of database, and greatly enhance the security of data. At the same time, after the implementation of master-slave replication, the load performance of the database can be further enhanced by the separation of read and write.

As shown in the figure, this is the approximate implementation process of master-slave replication.

1.2 read-write separation (business level)

Read-write separation is based on master-slave replication. Only when the master-slave replication of the database is realized, can read-write separation be further realized. Read-write separation can be understood as all query operations in the sub-database and all write operations in the main database. When the data is written to the master database, the data is backed up to the subdatabase by master-slave replication to ensure the consistency of the data.

two。 Realize

Let's first take a look at the principle of master-slave replication:

First, when the data in the primary database changes, the change record is written to the binlog log.

The slave database probes the binlog logs in the master database for a certain period of time, and requests master log file information if there is a change.

After understanding how it works, we need to know the premise of enabling master-slave replication:

Master node needs to enable binlog log (binlog is not enabled by mysql by default)

Slave node, specify a binlog file, and synchronized offset

Specify the ip of the master node

Execute the username and password of the master node

Now that we have a general understanding, let's build an one-master-one-slave database schema to demonstrate the configuration process of database master-slave construction.

Prepare two CentOS servers and install mysql5.7 ahead of time.

2.1 enable binlog log of master server

Edit the my.cnf file of mysql: (different installation methods, different file locations, specific analysis of specific problems ~)

Vim / etc/my.cnf

The original file does not have the following content, we need to add it ourselves. My side is the master-slave replication of the data from the myslave library.

Server-id = 1 # server-id server unique ID log_bin = master-bin # log_bin launch MySQL binary log log _ bin_index = myslave # binlog_do_db specify the database to record the binary log these two specifications can not add binlog_ignore_db = mysql # binlog_ignore_db specify the database that does not record the binary log

Restart mysql using the following command. Different versions of linux may have different commands.

Service mysql restart

After the startup is successful, we can see whether the bin-log is enabled through the following statement: (you can query it directly in Navicat)

Show variables like 'log_bin%'

2.2 remote access # allow remote users to access GRANT ALL PRIVILEGES ON *. * TO 'root'@'192.168.221.131' IDENTIFIED BY' 123456' WITH GRANT OPTION;# refresh FLUSH PRIVILEGES;2.3 slave node configuration

First, on the master node, learn about the status of the master node through the following command, and get the information shown in the following figure.

Show master status

Modify related configuration

Vim / etc/my.cnf

Add relevant information:

Server-id = 2 # unique ID relay-log = slave-relay-binrelay-log-index = slave-relay-bin.indexreplicate-do-db=myslave # backup database is set in master, and the database can be ignored without setting replicate-ignore-db=mysql #

Remember to restart the service

Service mysql restart

Execute the following command on the slave node.

Change master to master_host='192.168.221.128',master_user='root',master_password='123456',master_log_file='binlog.000009',master_log_pos=2339

Master_log_file is the file name obtained in the first step

Master_log_pos is the synchronization location point obtained in the first step.

Start slave synchronization

Start slave

Check the synchronization status and get the status shown in the following figure, which indicates that the master-slave synchronization is built successfully. You can create table tests in the main library.

Show slave status\ G

At this point, I believe you have a deeper understanding of "how to quickly build a master-slave replication architecture in MySQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.