Master slave replication of MySQL based on log

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

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

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