MySQL Series (iii)--mysql master-slave replication configuration

I. Preface

Master-slave replication is a very heavy need for a module in MySQL knowledge system. The study of master-slave copying and subsequent reading and writing separation is an important part of perfecting knowledge system. and master-slave copy read and write separation is not only the idea of MySQL, in many storage systems have this program, such as: Redis.

From the perspective of application, master-slave replication has the following advantages:

• Can back up data, disaster tolerance
• Can do read and write separation, sharing the pressure of a single MySQL node. Master only do write,slave do read
• Data classification for analysis and calculation of performance, you can only on the slave node
• A certain degree of availability, the master write node goes down and can quickly switch to the slave node

For these reasons above, it is considered very necessary to understand more or even delve into master-slave replication.

This article mainly introduces the configuration of master-slave replication from the following aspects:

1. Environment preparation
2. Configuring the Master Node
3. Configuring the Slave Node
4. Summarize

Two. Environmental preparedness

Slave nodes can have multiple, but for machine reasons, only one slave is configured here.

1. Two servers:
   10.1.134.151/macos/master
   172.16.162.157/centos 6.9/slave

2. MySQL Installation
   MySQL version: v5.7.22
   MacOS: Download the installation package directly
   Centos: Install using MySQL yum Repository, refer to installing MySQL on Linux using the MySQL yum Repository

3. Configure two machine firewalls to be trusted, or open 3306 ports

Two. Configure the primary server

1. Edit the/etc/my.cnf and create a new file if it is not. MY.CNF is the configuration file for MySQL:

vim /etc/my.cnf

2. Configure the network interface that the server listens to:

bind-address=10.1.134.151

3. Configure the unique identity in the master-slave relationship under the [mysqld] option. The identity must be guaranteed to be unique throughout the master-slave relationship:

server-id=master

4. Configure Log_bin, which is a key part of master-slave replication. Slave will keep copying master's bin Log,master changes are written to bin log:

log_bin=mysql-bin.log

• Note: It is important to note that I am configuring it as a directory in MacOS: Log_bin=/var/log/mysql/mysql-bin.log, but MySQL always faild start.

5. You need to specify which database the master/slave replicates. If you need to configure multiple databases that require master-slave replication, you can configure multiple lines:

binlog_do_db=test

6. Need to ensure that the configuration takes effect:

/usr/local/mysql/support-files/mysql.server restart

7.mysql has many security mechanisms, one of which is access control (IP Whitelist, authentication authorization), so you need to authorize slave access IP and users on master to log in to the MySQL shell:

mysql -uroot -pGRANT REPLICATION SLAVE ON *.* TO ‘root‘@‘%‘ IDENTIFIED BY ‘root‘;

• Note 1: Grant SLAVE access to master's configuration, I do not use '% ' because '% ' will match all, I think the configuration is unsafe on line, so I specify: GRANT REPLICATION SLAVE on . To ' root ' @ ' 172.16.162.157 ' identified by ' root '; Only slave can access, that is, authorized control more delicate.

• Note 2: Personally, when authorizing, you can also specify on which database to perform master-slave replication: GRANT REPLICATION SLAVE on test.* to ' root ' @ '% ' identified by ' root ';

8. Ensure that authorization is effective:

FLUSH PRIVILEGES;

• Note: You can query the Mysql.user table to ensure that authorization exists: use MySQL; Select host, use from user;

This completes the configuration of the primary server. However, in many cases, the line is temporarily increased slave, in this case, the database itself already has data exist, this is necessary to do the operation, the existing full-amount of data copied to slave:

1. Switch to the database that requires master-slave replication:

use test;

2. Lock the test library to prevent new changes from occurring:

FLUSH TABLES WITH READ LOCK;

3. Then view the current master status:

SHOW MASTER STATUS;

The results are as follows:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | Test | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

This position is very important, indicating that slave from this position to start the master-slave copy. This sentence is recorded first and will be used later.

4. Then the test library on the dump master:

mysqldump -u root -p --opt test > ~/test.sql

• Note: This dump is commonly used in MySQL backup. Not only will the data be exported, but also the created SQL for the table structure is exported. But there is no create SQL to export the library

5. Then unlock the test library:

UNLOCK TABLES;

Three. Configure the slave server

1. Log in to the MySQL shell of the slave library and create the test library:

create database test;

2. Then use the MySQL program to recover the data:

mysql -u root -p test < ~test.sql

3. In the configuration of the my.cnf of the slave node, the same is true if/etc does not have the file created. Under the [mysqld] option, configure the unique identity in the master-slave relationship:

server-id=slave-1

4. Reconfigure the relevant logs and master-slave databases, relay logs, Bin_log, test libraries:

relay-log=mysql-relay.loglog_bin=mysql-bin.logbinlog_do_db=test

5. Restart the service to confirm that the configuration is in effect:

service mysqld restart

6. Then configure the authorized master to access slave and log in to the MySQL shell:

mysql -uroot -pGRANT ALL PRIVILEGES ON test.* TO ‘root‘@‘%‘ IDENTIFIED BY ‘root‘;

7. Then configure the slave server again:

CHANGE MASTER TO MASTER_HOST=‘10.1.134.151‘,MASTER_USER=‘root‘, MASTER_PASSWORD=‘root‘, MASTER_LOG_FILE=‘mysql-bin.000001‘, MASTER_LOG_POS=107;

The command does the following things:

• Specifies the slave node for the current node as the master node
• Provides login authentication information
• Provides the slave from where to start copying postion

8. Start Slave:

start slave;

9. View the details of master-slave replication:

SHOW SLAVE STATUS\G

Four. Summary 1. Summary of issues

In the construction of the master-slave replication environment, I encountered a lot of problems, so here to summarize:

Master-slave Copy read-write separate architecture diagram:

As can be seen from the above figure:

1. The network communication between the master and slave servers needs to be normal, that is, the firewall is trusted or 3306 ports need to be open.
2. MySQL Province has access authorization (whitelist User table), and master and slave need to set up IP, user and password access between each other.
   Use grant authorization, or directly update the host in the Mysql.user table as '% '.
   For details, refer to:
   MySQL Setup white list tutorial
   Troubleshooting MySQL Navicat error: 1130-host. Is isn't allowed to connect to this MYSQL server

PS: here must pay attention to the password, otherwise the master and slave will be unable to authenticate the connection.
General troubleshooting means: Show slave status, by viewing the connection status information, file read and write processing information.

3. The above two, namely Master and slave guarantee the network connection, authorization authentication. But the control of data synchronization between master and slave still needs attention, namely position. When I built it myself, I couldn't finish the master-slave copy, view: Show slave status, Discover:

Got fatal error 1236 from master when reading data from binary log: ‘binlog truncated in the middle of event; consider out of disk space on master; the first event ‘mysql-bin.000001‘ at 105, the last event read from ‘./mysql-bin.000001‘ at 123, the last byte read from ‘./mysql-bin.000001‘ at 124.‘

It can be seen from here that slave reads bin log, position's position is wrong. When I saw this mistake, I was a little skeptical of position's problem, but I didn't think it was Baidu.
In fact, we can further consider, improve their ability to solve problems.
can refer to:
MySQL master-slave failure error got fatal error 1236 workaround

2. Thinking of Master-slave replication:

In the application architecture, the idea of separation must be understood in depth:

1. Can do expansion after separation
2. Different applications after separation
3. Post-separation load pressure
   ....

But the separation will inevitably cause how to close, how to split the routing problem, and if the data is not fragmented, it will inevitably lead to redundant storage.

• Merge/split issues: There is also the separation of this architecture, and how to ensure that the outside of the heap to provide a consistent interface, here involves the application of read and write separation: Application layer decision/middleware Layer Unified interface;
  
• Redundant storage: This needs to see the scene, redundancy is also a backup strategy;

And after the separation, whether the different nodes need to communicate with each other heartbeat, heartbeat if you need to consider security (authorization, authentication) and so on

Reference

MySQL master-slave failure error got fatal error 1236 workaround
Last_io_error:error Connecting to Master
MySQL plugin installation or uninstallation (window Validate_password as an example)
How to add a new MySQL slave
How to Set up Master Slave Replication in MySQL

MySQL Series (iii)--mysql master-slave replication configuration