Detailed analysis of MySQL to quickly build a master-slave replication architecture

This article brings you relevant knowledge about mysql, which mainly introduces the related issues of how to quickly build a master-slave replication architecture. The master-slave replication function provided by the MySQL database can be easily implemented Data is automatically backed up on multiple servers to expand the database and greatly enhance data security. I hope it will be helpful to everyone.

Recommended learning: mysql tutorial

1. Master-slave replication and separation of reading and writing

1.1 Master-slave replication (data level)

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

As shown in the figure is the general implementation process of master-slave replication.

1.2 Read-write separation (business level)

Read-write separation is based on master-slave replication. Only master-slave replication of the database is realized. , in order to further realize the separation of reading and writing. Read-write separation can be understood as all query operations are performed in the sub-database, and all write operations are performed in the main database. After the data is written to the main database, the data is backed up to the sub-database through master-slave replication to ensure data consistency.

2. Implementation

Let’s first understand the principle of master-slave replication:

1. First, be the data of the master database After changes occur, the change record will be written to the binlog log.
2. The slave database will detect the binlog log in the master database within a certain period of time. If changes occur, it will request the master log file information.

After understanding its principle, we need to know the prerequisites for enabling master-slave replication:

1. The master node needs to enable binlog logs (mysql does not enable binlog by default)

2. slave node, specify a binlog file, and synchronized offset

   • Specify the ip of the master node

   • Username and password for executing the master node

Now we all understand it roughly, let’s build a database model with one master and one slave and demonstrate it. Configuration process of database master-slave setup.

Prepare two CentOS servers and install mysql5.7 in advance.

2.1 Open the binlog log of the master server

Edit the my.cnf file of mysql: (The installation method is different, the file location may be different, specific analysis of the specific problem~)

vim /etc/my.cnf

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

server-id = 1 #server-id 服务器唯一标识log_bin = master-bin 
#log_bin 启动MySQL二进制日志log_bin_index = master-bin.index
binlog_do_db = myslave #binlog_do_db 指定记录二进制日志的数据库 这两个指定可以不添加binlog_ignore_db = mysql 
#binlog_ignore_db 指定不记录二进制日志的数据库

Use the following command to restart mysql. Different Linux versions may have different commands.

service mysql restart

After successful startup, we can see the bin-log opening status through the following statement: (Query directly in Navicat)

show variables like 'log_bin%';

2.2 Remote access

#允许远程用户访问
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.221.131' IDENTIFIED BY '123456' WITH GRANT OPTION;
#刷新
FLUSH PRIVILEGES;

2.3 Slave node configuration

• First on the master node, run the following command to understand the status of the master node and get the information as shown in the figure below.

show master status;

• Modify related configurations

  vim /etc/my.cnf

  Add relevant information:

  server-id = 2  #唯一标识relay-log = slave-relay-bin
  relay-log-index = slave-relay-bin.index
  replicate-do-db=myslave #备份数据库 对应master中设置的，可以不设置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 position obtained in the first step;

• Start slave synchronization

  start slave;

• Check the synchronization status and get the status shown in the figure below, which means that the master-slave synchronization is successfully established. Table tests can be created in the main database.

  show slave status\G

  

Recommended learning: mysql learning tutorial

The above is the detailed content of Detailed analysis of MySQL to quickly build a master-slave replication architecture. For more information, please follow other related articles on the PHP Chinese website!