Implement MySQL master-slave replication

【Related learning recommendations: mysql tutorial】

The test server uses centos7.2 for relevant configuration

The article content refers to the master-slave synchronization of the mysql database to achieve read-write separation

Overview

Large-scale In order for a website to handle a large number of concurrent accesses, it is not enough to implement distributed load balancing on the website. When it comes to the data business layer and data access layer, if the traditional data structure is still used, or if only one server is used to handle so many database connection operations, the database will inevitably collapse, especially if the data is lost, the consequences will be unimaginable. At this time, we will consider how to reduce database connections, and let’s move on to today’s topic.

Use the master-slave database to separate reading and writing, thereby sharing the pressure on the master database. Deploy mysql on multiple servers, consider one of them as the master database, and the others as slave databases to achieve master-slave synchronization. The master database is responsible for active writing operations, while the slave database is only responsible for active reading operations (the slave database will still passively perform write operations in order to maintain data consistency), so that data loss can be avoided to a large extent. It can also reduce database connections and reduce the load on the main database.

Installing mysql

I refer to the following two articles to install mysql on the server

Centos7.2 Installation Mysql5.7 (Alibaba Cloud)

Centos7 cannot connect to the mysql database remotely

During the installation of the database, some pitfalls were encountered due to the inability to connect remotely. First, after configuring the database, database authorization is required to allow access, and then open the firewall settings and set 3306 The port is opened, allowing the database management tool to access the database through the port. I just kept running into trouble without setting up the firewall.

Mysql was installed on the two servers under test and the test database was imported at the same time

##Master-slave replication configuration

Main server configuration

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

#在[mysqld]中添加：
server-id=1log_bin=master-bin
log_bin_index=master-bin.index
binlog_do_db=master

#server-id 服务器唯一标识。
#log_bin 启动MySQL二进制日志，即数据同步语句，从数据库会一条一条的执行这些语句。
#binlog_do_db 指定记录二进制日志的数据库，即需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可。
#binlog_ignore_db 指定不记录二进制日志的数据库，即不需要复制的数据库名，如果有多个数据库，重复设置这个选项即可。
#其中需要注意的是，binlog_do_db和binlog_ignore_db为互斥选项，一般只需要一个即可。

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 the specified slave After the server IP

is created, restart the mysql service through the command

service mysql restart

Check the status through the command show master status

slave slave server configuration

The same modification is located in the /etc/my.cnf directory Configuration

#在[mysqld]中添加：
server-id=2relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
#replicate-do-db=master


#server-id 服务器唯一标识，如果有多个从服务器，每个服务器的server-id不能重复，跟IP一样是唯一标识，如果你没设置server-id或者设置为0，则从服务器不会连接到主服务器。
#relay-log 启动MySQL二进制日志，可以用来做数据备份和崩溃恢复，或主服务器挂掉了，将此从服务器作为其他从服务器的主服务器。
#replicate-do-db 指定同步的数据库，如果复制多个数据库，重复设置这个选项即可。若在master端不指定binlog-do-db，则在slave端可用replication-do-db来过滤。
#replicate-ignore-db 不需要同步的数据库，如果有多个数据库，重复设置这个选项即可。
#其中需要注意的是，replicate-do-db和replicate-ignore-db为互斥选项，一般只需要一个即可。

Restart the mysql service through the command

service mysql restart

Connect to the main database

#连接master主服务器
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对应主服务器的IP地址。
#master_port对应主服务器的端口。
#master_log_file对应show master status显示的File列：master-bin.000001。
#master_log_pos对应show master status显示的Position列：120。

Enable slave synchronization data

#启动slave数据同步
mysql> start slave;#停止slave数据同步（若有需要）
mysql> stop slave;

View slave information

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

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

Test

Insert a piece of data into any data table

View the corresponding table from the slave database

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

If you want to know more about programming learning, please pay attention to the

php training column!

The above is the detailed content of Implement MySQL master-slave replication. For more information, please follow other related articles on the PHP Chinese website!