Brother ramble about

MySQL master-slave replication (MASTER-slave replication) MySQL master-slave replication (master slave replication) There are two ways to implement master/slave replication: binlog and GTID. In this article, we will use binlog to implement master/slave replication.

Big thumbs up, look at the brush up. If you think you can learn something from brother’s article, please recommend brother to your friends. Sharing is a happy thing, let’s play Java together.

Set up a master and slave cluster

Binlog profile

Mysql has a binlog binary log. This log records all the modified SQL statements on the primary server. The secondary server copies the binlog binary log of the primary server in a specified location and executes all the modified statements on the secondary server.

In short, the master server records the CREATE, UPDATE, and DELETE statements in a binary file (binlog), reads the file from the server, and executes the create, UPDATE, and DELETE statements recorded in the file. In this way, master and slave data are synchronized.

The preparatory work

  • Three servers: 192.168.216.111, 192.168.216.222, and 192.168.216.333
  • Master slave and Master Master We used machines 111 and 222, 111 master and 222 slave. Master master both machines are master. If there are two masters and many slaves, 333 is slave
  • Server environment: Windows, because most partners are using Windows system, convenient for everyone to learn, real enterprise Linux.
  • The MySQL database is installed on the three machines, and they can ping each other.

Configure the my.ini or my. CNF file for the master/slave library

My. Ini is for Windows and my. CNF is for Linux

  • Under the [mysqld] node in my.ini of 111 and 222

    • server-id = A unique ID: Unique ID of the primary server, usually set to the last three digits of the host IP address
    • log-bin = Path for storing binary log files: This is to start and log the binlog
    • log-err = Error log path(Optional) : Start error logs
    • read-only = 0: 0 is read/write (master library), 1 is read-only (slave library)
    • binlog-lgnore-db= The database name(Optional) : Set databases that do not need primary/secondary replication
    • binlog-do-db = The database name(Optional) : Name of the database to be replicated

111 primary database authorizes user 222 secondary database

After the my.ini file has been configured for both the master and slave libraries, the master library needs to create an authorized user for the slave library to log in to the master library.

  • grammar
111Master library execute: GRANT REPLICATION SLAVE ON *.* TO'Username'@'from the machine IP' IDENTIFIED BY 'password'; (Create authorized users) FLUSH PRIVILEGES; MySQL > alter table system permissionsCopy the code
  • See if you can connect to the host from the slave machine
222Mysql -h host IP-usally - PilovesallyCopy the code
  • If the connection fails, check whether it is caused by the firewall and configure IP rules for the firewall

The primary/secondary replication starts

  • Check the status of master 111
show master status;
Copy the code

I’m going to focus on thisFileandPositionTwo parameters,FileWhich log file to synchronize data from,PositionBinlog-do-db and binlog-lgnore-db mean which databases to synchronize and which not to synchronize.

  • Log in to the master library from the slave library to set up synchronized data files

If you have synchronized data before, stop it (stop slave;) Otherwise, an error will be reported.

222Execution: MASTER_HOST ='host IP',
MASTER_USER='Host username', MASTER_PASSWORD ='Host password',
MASTER_LOG_FILE='File name', MASTER_LOG_POS=Position number;Copy the code
  • Enable replication from the slave library
start slave;
Copy the code
  • View synchronization status of the slave library
show slave status\G;
Copy the code

If both parameters are YES, the synchronization is successful. Insert some new data and see if you can synchronize it from the library!

The active and active replication cluster is set up

The above introduction of master/slave replication implementation method, we introduce master/slave replication on the basis of master/slave replication (only need to 111 also into 222 slave machine), the above said 222 slave library into the master library, 111 and 222 two libraries mutually master/slave, do not understand the students can see the previous article master/slave replication architecture diagram.

Go from library to master library

  • The above222To switch from the master library to the master library, execute the following statement on 222. Note that the slave IP is111The IP address, because the primary and secondary
GRANT REPLICATION SLAVE ON *.* TO 'Username'@'from the machine IP' IDENTIFIED BY 'password'; (Create authorized users) FLUSH PRIVILEGES; MySQL > alter table system permissionsCopy the code

Configure two primary primary databases my.ini

  • Set the following parameters in [mysqld]
auto_increment_increment=2# step size auto_imcrement N auto_increment_offset=1# Start value. MySQL > select MySQL from NTH hostCopy the code

111 Synchronize data with 222

  • Check the status of master 222
show master status;
Copy the code

  • 111Set the synchronous222The data file

If you have synchronized data before, stop it (stop slave;) Otherwise, an error will be reported.

MASTER_HOST='host IP',
MASTER_USER='Host username', MASTER_PASSWORD ='Host password',
MASTER_LOG_FILE='File name', MASTER_LOG_POS=Position number;Copy the code
  • To view111Library synchronization state
show slave status\G;
Copy the code

If both parameters are YES, the synchronization is successful. At this point, you can insert some new data and see if the 111 and 222 libraries can synchronize with each other.

Setting up a dual-master multi-slave cluster

We create a dual master multi-slave cluster on the basis of the above dual master cluster. This is where machine 333 comes into play. Since 111 and 222 are both master machines, 333 can be attached to either master machine as slave machine. Let’s pick 111 here.

The procedure is the same as that for the first primary/secondary replication cluster.

After all the operations were done, we added data to host 111 for testing, and found that 222 and 333 had synchronized data. However, when 222 is added to the data test, 111 is synchronized, but 333 is not. Since 333 is a slave library under 111, when all 222 hosts add data, 333 does not synchronize 222 data, which is obviously not possible. To solve this problem, add the following configuration files to the configuration files of hosts 111 and 222 and restart them.

log-slave-updates=on
Copy the code

This parameter must be added to take effect when the slave library is the master of other slave libraries. 111 and 222 are each other’s slave libraries, 333 is the slave library of 111, so 111 and 222 need to be added to this parameter, so you can understand the logic.

Brother epilogue

MySQL master-slave replication is the cornerstone of achieving high performance and high availability of MySQL. Therefore, it is necessary to know this knowledge to further learn MySQL and follow the footsteps of my brother. In the next article, we will talk about the principle of master-slave replication.

IT brother

A big factory to do advanced Java development program ape

Follow wechat public account: IT elder brother

Java foundation, Java Web, JavaEE all tutorials, including Spring Boot, etc

Reply: Resume template, you can get 100 beautiful resumes

Reply: Java learning route, you can get the latest and most complete a learning roadmap

Re: Java ebooks, get 13 must-read books for top programmers