Home  >  Article  >  Database  >  Detailed explanation of mysql log-based master-slave replication

Detailed explanation of mysql log-based master-slave replication

coldplay.xixi
coldplay.xixiforward
2020-08-17 16:55:332224browse

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:


  1. The firewall between master and slave needs to be allowed to pass
  2. Mysql-id must only see my. cnf
  3. uuid must be unique to auto.cnf
  4. 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)


  1. 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

  2. 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


  1. Environment introduction

      Master server 192.168.5.238 centos 7.4
    1. Slave server 192.168.5.239 centos 7.4
    2. Use logs for master-slave configuration
  2. 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

  3. 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
  4. 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. mysql>start slave; //Start the slave library
      5. Check whether the master-slave replication is successful

      6. mysql> show slave status \G;
      7.          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

        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

    3. ##Summary:

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


Be careful and don’t panic
  1. Related graphic tutorials:
  2. mysql database 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!

Statement:
This article is reproduced at:learnku.com. If there is any infringement, please contact admin@php.cn delete