Detailed explanation of mysql log-based master-slave replication-Mysql Tutorial-php.cn

Detailed explanation of mysql log-based master-slave replication

People always ask me if I can separate reading and writing. Sometimes I really don’t know how to answer. Let’s put it this way. The technology itself is not difficult for you. The difficult thing is that we can You can't encounter such a big project. If there is such a big project, the separation of reading and writing is definitely not done by one or two people, but should be the collaboration of many people.

Related learning recommendations:mysql video tutorial

So! I haven't done it.

But...

Can't we use it as an experimental environment? (An awkward smile)

It took me a total of 3 days from looking for documentation to implementing the experiment (because I didn’t focus on this matter all day long). Basically done, that is, if someone asks me if I know how to do it, I will say that I have already done it and it is not a difficult task.

Specific points to note:

The firewall between master and slave needs to be allowed to pass
Mysql-id must only see my. cnf
uuid must be unique to auto.cnf
The master server needs to be authorized. Generally, a slave account is authorized for the slave server to use.

Commonly used Several commands (for log methods)

The ones that operate on the main library are

mysql > show master status; #View master status Library status

mysql > grant replication slave on.to 'slave'@'%ip%' identified by 'password';# to explanation behind: 'slave' represents which account, @ is the IP of the slave server and by is the password;

systemctl status firewalld# Check the firewall status

firewall-cmd – list-all # View the firewall’s port list

firewall-cmd –permanent –zone=public –add-port=3306/tcp# Release port 3306

firewall- cmd –reload# Restart the firewall

In addition, if you have iptable, you can check it yourself
The main operations on the slave library are
1. mysql> stop slave; // Stop replication
2. mysql> reset slave; // Reset replication
3. mysql> start slave; // Start replication
4. mysql> show slave status\G;# View status

##Specific configuration

Environment introduction
1. Slave server 192.168.5.239 centos 7.4
2. Use logs for master-slave configuration
Configuration on the master server
1. Firewall operations to prevent If strange problems occur later, please refer to the firewall operations of several commonly used commands
2. mysql log-related configuration. Don’t touch other things for now, mainly read this paragraph
  
  # aster live copy configurelog-bin=mysql-bin //Log file name
  binlog_format=mixed //There are several options for this log format. Everyone chooses the mixed option. statement/row/mixed, if you want to learn more, you can read the official documentation
  server-id = 238 //In order to avoid repeated use, try to use the last digit of the server IP to name it
  skip_name_resolve=ON //Write this directly That's it, refer to other people's documents
  expire_logs_days = 10 //Set the number of days for log storage. I think you don't need to add this. It doesn't matter if you add it, because in the test environment, if it is officially launched and run, you have to weigh it.
  
  # /etc/init.d/mysql restart // This is not necessarily this command. It depends on how you installed centos. There is also systemctl restart mysql. If you don’t understand this, you can do more research. Linux operation##If the two servers are not copied, the uuid of auto.cnf should be different. If they are copied, it is time to take a look. If there is this in the /var and directory of the mysql installation directory Just delete this file or mv back it up
configure mysql copy permissions

# mysql -uroot -p "password" //Log in to the mysql server, you will be asked to enter your password
mysql>GRANT replication slave ON . TO 'slave'@'%' IDENTIFIED BY '111111'; // Explain that replication assigns replication permissions. This.can operate which library. The 'slave'@'%' at the end means that any host can use slave to copy from the library, or you can specify After the IP is the password ha
mysql> show master status;
—————— ————- ————– —————— ——————-
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
—————— ————- ————– —————— ——————-
| mysql-bin.000010 | 2812 | # Seeing the above table indicates success

Configuration from the library
Modify my.cnf
1. log-bin=mysql-bin //Log file name
  binlog_format=mixed //Log format
  server-id =239 //Server ID This is explained the same as the main server, using the tail of the IP
  
  # expire_logs_days = 10 //I commented out this,
  # early-plugin-load = "" //This is not used for the time being. It can be configured if it is enabled. This item, master-slave replication is not a key item
  relay_log = mysql-relay-bin //This is a replication log from the slave libraryrelay_log_index=relay_log.index //Log index
  
  # /etc/init.d/mysql restart // This is not necessarily this command. It depends on how you installed centos. There is also systemctl restart mysql. If you don’t understand this, you can do more research on linux operations.
  
  Set up the slave library
2. Enter the mysql terminal of the slave library #mysql -uroot -p //Enter the terminal
  1. # mysql -uroot -p //Enter the terminal (the reason why I write more details is because I am afraid that people who read this article will misunderstand that the operation is incomplete)
    
    Close slave
  2. # Be careful to close the slave before configuring the slave library;
    ##mysql>stop slave; //This is to close the slave library, make sure you can get through it
    
    Modify master parameters
  3. mysql>change master to
    master_host='192.168.5.238',
    master_user='slave',
    master_password='Fill in the password when setting the master library copy permission above',
    master_log_file='mysql-bin.000010', //Fill in the file name of the master library show master status here, just copy the location.
    master_log_pos=2812; //Look in the show master status of the main library
    
    ##Start the slave library
  4. Check whether the master-slave replication is successful
  5. ```
  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
```
  Copy after login
  Seeing that the values of Slave_IO_Running and Slave_SQL_Running are both Yes, it means it is successful. If it fails, please see the prompt message below and you should be able to find it by searching Google Baidu.
  
  I am here I encountered a problem during configuration, that is, a uuid error was reported. The reason is that my experimental environment was copied from a virtual machine after mysql was installed, so the uuid of mysql was the same
  1. # cd /usr/local/mysql/var
    
    # mv auto.cnf auto.cnf.back //See if this is present. If not, don’t operate. If so, that’s it
    # /etc/init.d/mysql restart //Restart the databaseThen execute show slave status\G; observe two yes
    
    So far , the log-based master-slave replication is completed

The firewall must be read firstIt doesn’t matter, just follow it until it succeeds

Be careful and don’t panic

Related graphic tutorials:

The above is the detailed content of Detailed explanation of mysql log-based master-slave replication. For more information, please follow other related articles on the PHP Chinese website!