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

This article mainly introduces what is the master-slave replication of mysql log, which has certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

People always ask me if I can read and write separately, and sometimes I really don't know how to answer, so, the technology itself is not difficult for you, the difficulty is whether we can get such a big project. If there is such a big project, the separation of reading and writing is certainly not the work of one or two people, but should be the cooperation of many people.

So! I didn't do it.

But.

Can't you do the experimental environment? (an awkward smile)

It took me a total of three days from looking for documentation to landing the experiment (because it didn't revolve around it all day). Basically done, that is to say, if someone asks me if I will, I say I have already operated, it is not difficult.

Specific note: the firewall between master and slave needs to release mysql-id. Only see my.cnfuuid. Only see auto.cnf master server to authorize. Generally authorize a slave account to use several commands commonly used by slave server (for logs).

Those operating on the main library are

Mysql > show master status; # View the status of the main library

Mysql > grant replication slave on. The explanation behind to 'slave'@'%ip%' identified by' password';# to: 'which account does slave' represent? @ is followed by a password from the server's IP by.

Systemctl status firewalld # View firewall status

Firewall-cmd-list-all # View the port list of the firewall

Firewall-cmd-permanent-zone=public-add-port=3306/tcp # release port 3306

Firewall-cmd-reload # restart the firewall

Besides, iptable can see for himself. Wow.

The main operations on the slave library are

Mysql > stop slave; / / stop replication mysql > reset slave; / / reset replication mysql > start slave; / / enable replication mysql > show slave status\ G; # View status specific configuration

Environment introduction

Master server 192.168.5.238 centos 7.4Slave server 192.168.5.239 centos 7.4 uses logs for master-slave configuration

Configuration on the primary server

Firewall operation to prevent strange problems. Refer to the firewall operation of several commonly used commands.

The log-related configuration of mysql does not need to be moved first, mainly by looking at this paragraph.

# aster live copy configure

Log-bin=mysql-bin / / Log file name

Binlog_format=mixed / / this log format, there are several options, we all choose mixed optional statement/row/mixed, if you want to delve into it, you can read the official documents

Server-id = 238 / / try to use the last bit of the server IP for non-reuse

Skip_name_resolve=ON / / just write this down and refer to other people's documents.

Expire_logs_days = 10 / / set the number of days to keep the log. I think you don't have to add it, and it doesn't matter if you add it, because in the test environment, if you are officially running online, you have to weigh it.

# / etc/init.d/mysql restart / / this is not necessarily the command. Depending on how you install centos, you can also have systemctl restart mysql. If you don't understand this, you can study the linux operation more.

# # if the two servers are not replicated, the uuid of auto.cnf should be different. If it is replicated, you should take a look at it. Delete this file or back up the mv if it is in the / var or directory of the mysql installation directory.

Mysql configure replication permissions

# mysql-uroot-p "password" / / Log in to the mysql server and will ask you to enter your password

Mysql > GRANT replication slave ON. TO 'slave'@'%' IDENTIFIED BY' 111111 permissions; / / explain the permissions that replication assigns to replication. You can manipulate the 'slave'@'%'' after that library to indicate that any host can copy from the library with slave, or you can specify the password after IP.

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000010 | 2812 | |

+-+

1 row in set (0.00 sec)

Seeing the table above indicates that it is a success.

From the configuration on the library

Modify my.cnf

Log-bin=mysql-bin / / Log file name

Binlog_format=mixed / / log format

Server-id / / Server ID this is an explanation with the main server, using the tail of IP

# expire_logs_days = 10 / / I commented this out

# early-plugin-load = "" / / this is not used for the time being. If enabled, you can configure this item. Master-slave replication is not a key item.

Relay_log = mysql-relay-bin / / this is a copy log from the library

Relay_log_index=relay_log.index / / log index

# / etc/init.d/mysql restart / / this is not necessarily the command. Depending on how you install centos, you can also have systemctl restart mysql. If you don't understand this, you can study the linux operation more.

Set up slave library

Enter the mysql terminal # mysql-uroot-p / / of the slave library

# mysql-uroot-p / / enter the terminal (write more details for fear that those who read this article will misunderstand that the operation is incomplete)

Close slave # be sure to shut down slave before configuring the slave library

Mysql > stop slave; / / this is closed from the library wow, guarantee a pass

Modify master parameters

Mysql > change master to

Master_host='192.168.5.238'

Master_user='slave'

Master_password=', fill in the password above when setting the replication permission of the main library.'

Master_log_file='mysql-bin.000010', / / fill in the file name of the main library show master status here, and copy it opposite the location.

Master_log_pos=2812; / / look in the show master status of the main library

Start the slave library

Mysql > start slave; / / start the slave library

Check whether the master-slave replication is successful

Mysql > show slave status\ G

Slave_IO_State: Waiting for master to send event Master_Host: 192.168.5.238 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000010 Read_Master_Log_Pos: 2812 Relay_Log_File: mysql-relay-bin.000013 Relay_Log_Pos: 3025 Relay_ Master_Log_File: mysql-bin.000010 Slave_IO_Running: Yes Slave_SQL_Running: Yes

See that the Slave_IO_Running and Slave_SQL_Running values are both Yes. If not, please take a look at the prompt below. You should be able to find it in front of Google Baidu.

I encountered a problem in configuration, that is, a uuid error was reported, because my experimental environment was replicated by a virtual machine after installing mysql, so the uuid of mysql was the same.

# cd / usr/local/mysql/var

# mv auto.cnf auto.cnf.back / / do you have this? if you don't have it, you won't operate it, that's all.

# / etc/init.d/mysql restart / / restart the database

Then execute show slave status\ G; observe the two yes

At this point, the log-based master-slave replication is complete.

Summary: the firewall should first look at the steps, it does not matter, until the success and carefulness, do not panic thank you for reading this article carefully, hope that the editor to share the mysql log-based master-slave copy of what is helpful to everyone, but also hope that you support, pay attention to the industry information channel, encounter problems to find, detailed solutions waiting for you 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.