Master slave replication of MySQL based on log

Time:2021-1-17

There are always people who ask me if I can separate reading from writing. Sometimes I really don’t know how to answer. Let’s say that technology itself is not difficult for you. The difficulty is whether we can meet such a large project. If there is such a big project, the separation of reading and writing is certainly not the work of one or two people. It should be the work of many people.

So! I didn’t do it.

But

Can’t you do an experimental environment? (an embarrassed smile)

It took me three days from looking for documents to landing the experiment (because I didn’t focus on this all day). Basically done, that is to say, if someone asked me if I would, I said I had already operated, it was not difficult.

Specific points for attention:


  1. The firewall between master and slave needs to be released
  2. The unique view of MySQL ID my.cnf
  3. UUID’s only view auto.cnf
  4. The master server should be authorized. Generally, a slave account should be authorized to the slave server

Several common commands (for logging)


  1. In the main library operation of the

    MySQL > show master status; #

    mysql > grant replication slave on . to ‘slave‘@’%ip%’ identified by ‘password’;#“Slave” stands for which account, @ followed by the IP address of the slave server, and then the password;

    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 #Port 3306

    firewall-cmd –reload #service iptables restart

    In addition, if IPtable, you can see for yourself

  2. The main operations in the slave library are as follows

    1. MySQL > stop slave; / / stop copying
    2. MySQL > Reset slave; / / reset replication
    3. MySQL > start slave; / / enable replication
    4. mysql> show slave status\G; #View status

Specific configuration


  1. Environment introduction

    1. Main server 192.168.5.238 CentOS 7.4
    2. From server 192.168.5.239 CentOS 7.4
    3. Using log to configure master and slave
  2. Configuration on the primary server

    1. Firewall operation, to prevent strange problems behind, refer to several commonly used commands firewall operation

    2. MySQL log related configuration, other do not move, mainly to see this paragraph

      # aster live copy configure
      Log bin = MySQL bin / / log file name
      binlog_ Format = mixed / / there are several other options for this log format. You can choose the mixed option statement / row / mixed. If you want to further study, you can go to the official document
      Server id = 238 / / in order not to be reused, try to use the last bit of the server IP to name it
      skip_ name_ Resolve = on / / just write it down. Please refer to other people’s documents
      expire_ logs_ Days = 10 / / set the number of days to save the log. I don’t think it’s necessary to add it, because in the test environment, if you go online, you have to weigh it
      #/Etc / init.d/mysql restart / / this is not necessarily the command to see how it is installed. CentOS also has systemctl restart mysql. If you don’t understand this, you can study Linux operation more
      ##If the two servers are not replicated, auto.cnf UUID of MySQL should be different. If it’s a copy, it’s time to take a look. If there is one in / VAR and / or / Var of the MySQL installation directory, delete the file or make a MV backup

  3. Configure the replication permission in MySQL

    #MySQL – uroot – P “password” / / log in to the MySQL server and ask you to enter your password
    MySQL > grant replication slave on. To ‘slave’ @ ‘%’ identified by ‘111111’; / / explain how replication allocates replication permissions.You can operate the “slave” @ “%” on the back of the library, which means that any host can copy from the library with slave, or you can specify the password after the 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 success

  4. Configuration from library

    1. modify my.cnf

      Log bin = MySQL bin / / log file name
      binlog_ Format = mixed / / log format
      Server id = 239 / / the server ID is the same as the primary server, using the tail of IP
      #expire_ logs_ Days = 10 / / I’ve commented this out,
      #Early plugin load = ‘/ / this item is not used at the moment. If it is enabled, this item can be configured, and master-slave copy is not a key item
      relay_ Log = MySQL relay bin / / this is a copy log from the database
      relay_ log_ index=relay_ log.index //Log index
      #/Etc / init.d/mysql restart / / this is not necessarily the command to see how it is installed. CentOS also has systemctl restart mysql. If you don’t understand this, you can study Linux operation more

    2. Set up slave Library

      1. Enter the MySQL terminal of the slave library # MySQL – uroot – P / / enter the terminal

        #MySQL – uroot – P / / enter the terminal

      2. Close slave#Pay attention to close slave before configuring slave library;

        MySQL > stop slave; / / this is to close the slave library. Wow, make sure it’s passed

      3. Modify master parameters

        mysql>change master to
        master_host=’192.168.5.238’,
        master_user=’slave’,
        master_ Password =’fill in the password for setting the copy permission of the main database here ‘,
        master_ log_ File =’mysql-bin.000010 ‘, / / fill in the file name of show master status in the main database, and the location is just to copy
        master_ log_ POS = 2812; / / find it in show master status in the main library

      4. Start slave Library

        MySQL > start slave; / / start slave

      5. Check whether 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 slave_ IO_ Running and slave_ SQL_ If the running value is yes, it means success. If it doesn’t work, please see the following prompt information. Google Baidu should be able to find it

        1. I encountered a problem in the configuration, that is, the UUID error was reported. The reason is that my experimental environment was copied after a virtual machine installed mysql, so the UUID of MySQL was the same

          # cd /usr/local/mysql/var
          #mv auto.cnf auto.cnf.back //Do you think there is one? If there is one, it will not be operated. If there is one, it will be done
          #/Etc / init.d/mysql restart / / restart the database
          Then execute show slave status / g; observe two yes

        2. So far, log based master-slave replication is complete

Conclusion:


  1. Firewall first
  2. It doesn’t matter what the steps are, until you succeed
  3. Careful, don’t panic

    This article refers to

    Blog: MySQL master-slave replication operation
    Original address:
    blog.motkit.com/post/p3bc0ba45.html

This work adoptsCC agreementReprint must indicate the author and the link of this article

Recommended Today

Background management system menu management module

1 menu management page design 1.1 business design Menu management, also known as resource management, is the external manifestation of system resources. This module is mainly to add, modify, query and delete the menu. CREATE TABLE `sys_menus` ( `id` int(11) NOT NULL AUTO_INCREMENT, `Name ` varchar (50) default null comment ‘resource name’, `URL ` varchar […]