Try to set up a MySQL master-slave master-slave replication environment

The environment is centos 7.2+mysql 5.7. There are many online tutorials and the principles are not complicated (I know that I have not stepped on enough pitfalls)

Under normal circumstances, configuration is relatively simple. In addition, according to personal experience, MySQL replication feels much more refreshing than SQL Server replication (although there may be some functional differences).

For the master server, first enable the binary log, and set the server-id to a specific value

1 to create a replication user

　GRANT REPLICATION SLAVE ON *.* to 'repl_test'@'120.77.147.***' identified by '123456';

2, record the master log file and log location

Show master status;

## From the database

1. Set the master server

CHANGE MASTER TO

MASTER_HOST='120.77.159.43',
MASTER_USER='repl_test',
MASTER_PASSWORD='123456',
MASTER_PORT = 8000,
MASTER_LOG_FILE='mysql-bin.000047',
MASTER_LOG_POS=3112;

2. Start replication on the slave server

start slave;

show slave status View cluster Database status

　

Data synchronization test

Create a new DB on the master server and then synchronize it to the slave server

Create a table on the master server, write data, and synchronize it to the slave server (two ECS cloud servers applied for free by Alibaba Cloud, master-slave delay Very small, it feels no more than 1 second)

I encountered a small problem in the middle. When showing slave status, It was found that Slave_IO_Running started normally, but Slave_SQL_Running did not start.

Slave_IO_Running: Yes

Slave_SQL_Running: No

After referring to this, solve the problem

set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;Customer Run on the end, used to skip several events, and can only be executed when the synchronization process stops due to an error.

stop slave ;

set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave

Maybe it’s because I’m old and prone to emotion,

I have always been afraid of Linux, or I have a fixed thinking: Linux (so difficult) I will definitely not know it--->Then why should I touch it--->I definitely won’t
Ever since I saw a friend who had never been exposed to Linux before, after just a few days of contact, he was able to type commonly used operating commands, and then he installed a virtual machine from scratch, and he could help solve basic problems
Since then, I have been deeply aware of it. I am inspired, I dare not talk about getting started, and I probably know what Linux is all about

I feel sad again, people are really incomparable. To put it nicely, some people are "strong", to put it badly, To be more realistic, that is, people with high IQs have no choice but to take it step by step and take action. Don’t be afraid of slowness, just be afraid of doing it. Don’t do things to those who seem to be awesome in some aspects. , resulting in timidity.

Actions can change the way you think.

The above is the detailed content of Try to set up a MySQL master-slave master-slave replication environment. For more information, please follow other related articles on the PHP Chinese website!