Mysql master-slave replication principle and construction

Mysql master-slave replication principle

Master-slave replication means that one server acts as the master database server and one or more servers act as the slave database server, and the data in the master server is automatically copied to the slave server. For multilevel replication, the database server can act as either the host or slave. MySQL master-slave replication is based on the primary server recording binary logs of database changes, and the secondary server automatically performs updates through the binary logs of the primary server.

Mysq Specifies the type of primary/secondary replication

Statement based replication:

Statements executed on the primary server are executed again on the secondary server, supported after mysqL-3.23.

Problems: Deviations may occur due to incomplete synchronization, and statements may be executed by a different user.
Row-based replication:

In mysql-5.0, copy the changed content directly from the main server, regardless of which statement caused the change.

Problem: If too many lines are modified, the overhead is high.

MySQL uses statement-based replication by default. When statement-based replication causes problems, it uses row-based replication, which MySQL automatically selects.

In the MySQL master-slave replication architecture, read operations can be performed on all servers and write operations can only be performed on the primary server. The master-slave replication architecture extends read operations, but if there are many writes (multiple slave servers synchronizing data from the master server), the master server can become a performance bottleneck in single-master replication.

Mysql master-slave replication works

As shown above, any changes made on the primary server are stored in the Binary log. An I/O thread (essentially a client process on the primary server) is started on the primary server and connected to the primary server to request the Binary log. The binary log is then written to a local Realy log. Start a SQL thread from the server to periodically check the Realy log. If any changes are detected, execute the changes on the machine.

If one master has multiple slaves, then the master library is responsible for both writing and providing binary logs for several slave libraries. In this case, you can adjust the binary log to a slave. The slave then enables the binary log and sends its binary log to another slave. Or it could simply never log and only forward binary logs to other slaves, which would probably be much better architeted, and the latency between data should be slightly better.

Mysql primary/secondary replication

The IO process on the Slave connects to the Master and requests the log content from the specified location in the specified log file (or from the original log).
After the Master receives the request from the Slave I/O process, the REPLICATION I/O process reads the specified log information and sends the log information to the Slave I/O process. In addition to the log information, the returned information includes the name of the bin-log file that has been sent to the Master and the location of the bin-log file.
After receiving the log information, the Slave I/O process adds the received log content to the end of the Slave relay log file, and records the file name and location of the bin-log read from the Master to the master-info file. So that the next read can clearly tell the Master where to start reading the log.
When the Sql process of the Slave detects the new content added to the relay log, it immediately parses the content of the relay log into the executable content that is actually executed on the Master end and executes it itself.

Linux installs mysql8 and implements master/slave synchronization

Preparing the server

Prepare servers Server1 and Server2. If they are on the same server, change the port of the server when installing mysql.

Uninstall mysql

Before installing mysql, check whether mysql has been installed on the host. If yes, uninstall mysql.

Mysql installation

Download the package:

wget https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm

Local installation:

yum localinstall mysql80-community-release-el7-1.noarch.rpm

Mysql installation:

yum install mysql-community-server

Set to boot:

systemctl enable mysqld

systemctl daemon-reload

Start the mysql:

systemctl start mysqld

This completes the installation of mysql8.

Obtain temporary password for mysql:

grep 'temporary password' /var/log/mysqld.log

Log on to the mysql:

mysql -uroot -p

You will be prompted to enter your password. You can log in by entering the previously obtained temporary password.

Change the mysql password, or the next step will force you to change the password:

ALTER USER 'root'@'localhost' IDENTIFIED BY '121b33dAj934J1^Sj9ag';

Mysql8 default password strength requirements, you need to set a complex, otherwise the error will be displayed.

Refreshing the configuration:

FLUSH PRIVILEGES;

A master-slave configuration

Before configuring the primary and secondary databases, ensure that the status of the two mysql libraries to be synchronized is the same.

The main

The default configuration file is /etc/my.cnf.

Add the following configuration to the configuration file:

[mysqld]
Be unique within the same LAN
server-id=100  
## Enable the binary log function, can be arbitrary (key)
log-bin=mysql-bin
Copy the code

The modification takes effect only after you restart the system:

service mysql restart

Mysql > restart mysql

mysql -uroot -p

Create a data synchronization user on the master database and grant slave REPLICATION and REPLICATION CLIENT permissions to the user to synchronize data between the master and slave databases.

CREATE USER 'slave'@'%' IDENTIFIED BY '@#$Rfg345634523rft4fa';

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

% in this statement means that all servers can use this user. If you want to specify a specific IP address, change % to IP.

Mysql > select * from host;

show master status;

Record the values of File and Position and do nothing else to avoid Position changes.

from

Add from my.cnf configuration:

mysqld]
Set server_id to unique
server-id=101  
The binary log function is enabled for the Slave to function as the Master of other slaves
log-bin=mysql-slave-bin   
## relay_log Configures relay logs
relay_log=edu-mysql-relay-bin  
Copy the code

The modification takes effect only after you restart the system:

service mysql restart

Mysql > restart mysql

mysql -uroot -p

Change master to master_host='172.17.0.2', master_user='slave', master_password='@#$Rfg345634523rft4fa', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos= 2830, master_connect_retry=30;

Master_host: indicates the address of the Master

Master_port: indicates the Master port number

Master_user: user used for data synchronization

Master_password: indicates the password used for synchronization

Master_log_file: specifies the log File from which Slave starts copying data, that is, the value of the File field mentioned above

Master_log_pos: Which Position to start reading from, that is, the value of the Position field mentioned above

Master_connect_retry: Retry interval if the connection fails, in seconds. The default value is 60 seconds

Mysql > update master/slave synchronization status

show slave status \G;

At this point SlaveIORunning and SlaveSQLRunning are No because we haven’t started the master/slave replication process yet.

Enable primary/secondary replication:

start slave;

Check the synchronization status again:

show slave status \G;

SlaveIORunning and SlaveSQLRunning are both Yes indicating that master/slave replication is enabled.

If Slavei Running is Connecting, there are 4 reasons:

1. If the network is abnormal, check the IP port

2. If the password is incorrect, check the user name and password used for synchronization

3, pos is not correct, check the Master Position

Mysql > mysql8 mysql > mysql8 mysql > mysql8

ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '@#$Rfg345634523rft4fa';

Change the password rule to mysql_native_password

If you need to specify which database you want to synchronize from master to slave, you can add a configuration to the master’s my.cnf:

Binlog-do-db: specifies which DB is recorded in the mysql binlog log

Or add configuration to slave my.cnf:

Replicate-do-db = The name of the database you want to replicate. Repeat this option to replicate multiple databases

If you want to synchronize all libraries and tables, run the following command from mysql:

STOP SLAVE SQL_THREAD; CHANGE REPLICATION FILTER REPLICATE_DO_DB = (); start SLAVE SQL_THREAD;

If there is a problem with the above steps, you can view the log:

/etc/log/mysqld.log

This completes the mysql8 master-slave synchronization setup.

Mysql master-slave replication principle and construction