Shulou(Shulou.com)06/01 Report--

This article is to share with you about the implementation of MySQL master-slave replication. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

The test server uses centos7.2 for related configuration

The content of this article refers to the master-slave synchronization of mysql database to realize the separation of read and write.

General description

In order to soften a large number of concurrent visits, large websites are far from enough except to achieve distributed load balancing on the website. When it comes to the data business layer and the data access layer, if it is still the traditional data structure, or just relying on a single server to handle so many database connection operations, the database will inevitably collapse, especially if the data is lost. The consequences are even worse. At this point, we will consider how to reduce the number of database connections, so let's move on to today's topic.

The master-slave database is used to realize the separation of read and write, so as to share the pressure of the master database. Deploy mysql on multiple servers, one of which is considered as the master database and the other as the slave database, achieving master-slave synchronization. The master database is responsible for the active write operation, while the slave database is only responsible for the active read operation (slave slave database will still write passively, in order to maintain data consistency), so that the problem of data loss can be avoided to a great extent, at the same time, the connection of the database can be reduced and the load of the master database can be reduced.

Install mysql

Server installation mysql I refer to the following two articles

Centos7.2 installs Mysql5.7 (Aliyun)

Centos7 cannot connect to the mysql database remotely

In the process of installing the database, because there are some holes in the remote connection, first of all, after configuring the database, you need to authorize the database to allow access, then open the firewall setting and open port 3306. Allow access to the database through the port through the database management tool. I have been stepping in the hole when I have not set up the firewall.

Mysql is installed on both servers tested, and the test database is imported at the same time

Master-slave replication configuration master server configuration

Modify the master server mysql configuration, the file is located in / etc/my.cnf

# add: unique ID of server-id=1log_bin=master-binlog_bin_index=master-bin.indexbinlog_do_db=master#server-id server in [mysqld]. # log_bin starts MySQL binary logs, that is, data synchronization statements, and executes these statements one by one from the database. # binlog_do_db specifies the database that records the binary log, that is, the database name that needs to be replicated. If multiple databases are replicated, you can set this option repeatedly. # binlog_ignore_db specifies a database that does not record binary logs, that is, a database name that does not need to be replicated. If there are multiple databases, you can set this option repeatedly. # it should be noted that binlog_do_db and binlog_ignore_db are mutually exclusive options, so only one is needed. Create users and permissions

Grant replication slave on. To masterbackup@'%' identified by '123456'

% wildcard, indicating that any IP can access the master server. For formal environment, please configure and specify slave server IP

After the creation is completed, restart the mysql service by command

Service mysql restart

Check the status through the command show master status

Slave slave server configuration

Also modify the configuration located in the / etc/my.cnf directory

# add: unique ID of server-id=2relay-log=slave-relay-binrelay-log-index=slave-relay-bin.index#replicate-do-db=master#server-id server in [mysqld]. If there are multiple slave servers, the server-id of each server cannot be duplicated. Like IP, it is a unique ID. If you do not set server-id or set it to 0, the slave server will not connect to the master server. # relay-log starts MySQL binary logs, which can be used for data backup and crash recovery, or if the master server dies, use this slave server as the master server for other slave servers. # replicate-do-db specifies the synchronized database. If you copy multiple databases, you can set this option repeatedly. If binlog-do-db is not specified on the master side, replication-do-db can be used to filter on the slave side. # replicate-ignore-db does not require synchronized databases. If you have multiple databases, you can set this option repeatedly. # it should be noted that replicate-do-db and replicate-ignore-db are mutually exclusive options, so only one is needed. Restart the mysql service with a command

Service mysql restart

Connect to the master database # Connect to the master master server mysql > change master to master_host='103.246.246.225',master_port=3306,master_user='masterbackup',master_password='123456',master_log_file='master-bin.000001',master_log_pos=120;#master_host corresponds to the IP address of the master server. # master_port corresponds to the port of the primary server. # master_log_file corresponds to the File column displayed by show master status: master-bin.000001. # master_log_pos corresponds to the Position column displayed by show master status: 120. Enable slave data synchronization # start slave data synchronization mysql > start slave;# stop slave data synchronization (if necessary) mysql > stop slave; view slave information

View slave information through the command show slave status\ G;

If both Slave_IO_Running and Slave_SQL_Running are yes, the synchronization is successful.

test

Insert a piece of data into any data table

View the corresponding tables from the database

You can see that the corresponding data has been successfully synchronized from the database!

Thank you for reading! On the implementation of MySQL master-slave replication is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

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.