Shulou(Shulou.com)06/02 Report--

This article mainly introduces " Mysql master-slave replication principle and construction method". In daily operation, I believe many people have doubts about Mysql master-slave replication principle and construction method. Xiaobian consulted all kinds of information and sorted out simple and easy operation methods. I hope to help you answer the doubts of " Mysql master-slave replication principle and construction method"! Next, please follow the small series to learn together!

Mysql master-slave replication principle

Master-slave replication means that one server acts as the master database server and another or more servers act as slave database servers, and the data in the master server is automatically replicated to the slave server. For multi-level replication, the database server can act as either a master or slave. MySQL master-slave replication is based on the master server recording binary logs of database modifications, and the slave server automatically performs updates through the master server's binary logs.

Types of Mysq master-slave replication Statement-based replication:

Statements executed on the master server are executed again on the slave server, supported after MySQL-3.23.

Problems: Time may not be completely synchronized, resulting in deviation, and the user executing the statement may also be different.

Row-based replication:

Copy the changed content directly from the master server, regardless of which statement caused the change, introduced after MySQL-5.0.

Problems: If too many rows are modified, the overhead is relatively large.

MySQL uses statement-based replication by default, and row-based replication is used when statement-based replication causes problems, and MySQL automatically selects it.

In MySQL master-slave replication architecture, reads can be performed on all servers, while writes can only be performed on the master server. Although the master-slave replication architecture provides extensions for read operations, if there are also many write operations (multiple slave servers have to synchronize data from the master server), the master server in the replication of the single master model is bound to become a performance bottleneck.

How Mysql Master-Slave Replication Works

As shown above, any changes made on the master server are saved in the binary log, and the slave server starts an I/O thread (actually a client process on the master server), connects to the master server, requests to read the binary log, and then writes the binary log read to a local Realy log. Open a SQL thread from the server to check the Realy log regularly. If any changes are found, immediately execute the changed contents on the local machine.

If there is a master with multiple slaves, then the master is responsible for both writing and providing binary logs for several slaves. At this point, you can make a slight adjustment to give the binary log only to a slave, which then opens the binary log and sends its binary log to other slaves. Or simply this never log is only responsible for forwarding binary logs to other slaves, so the architecture performance may be much better, and the latency between data should be slightly better.

Mysql master-slave replication process

The IO process above Slave connects to Master and requests log content from the specified location in the specified log file (or from the initial log).

After the Master receives the request from the Slave IO process, the IO process responsible for replication reads the log information after the specified position according to the request information and returns it to the Slave IO process. In addition to the information contained in the log, the returned information also includes the name of the bin-log file where the information returned this time has been sent to the Master side and the location of the bin-log file.

After the Slave IO process receives the information, it adds the received log contents to the end of the relay-log file on the Slave side in turn, and records the file name and location of the bin-log read from the Master side into the master-info file, so that it can clearly tell the Master where to start reading the log next time.

Slave's Sql process detects the addition of new content to the relay-log, and immediately parses the contents of the relay-log into those executable contents that are actually executed on the Master side, and executes them on its own.

Linux install mysql8 and implement master-slave synchronization server preparation

Prepare servers Server1 and Server2, if on the same server you need to change the port when installing mysql.

Uninstalling mysql

Before installation, you must check if mysql has been installed on the host. If it has been installed, you must uninstall it.

install MySQL

Download software packages:

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

Local installation:

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

Install mysql:

yum install mysql-community-server

Set to boot:

systemctl enable mysqld

systemctl daemon-reload

Start mysql:

systemctl start mysqld

The above steps will install MySQL8.

Get mysql temporary password:

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

Log in to mysql:

mysql -uroot -p

You will be prompted to enter your password. Enter the temporary password you obtained before you can log in.

At this point, you need to change the password of mysql, otherwise the following steps will force you to change the password:

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

mysql8 default to password strength requirements, need to set a bit more complex, otherwise it will prompt errors.

Refresh Configuration:

FLUSH PRIVILEGES;

master-slave configuration

Before master-slave configuration, it is necessary to ensure that the state of the two mysql libraries that need to be synchronized is consistent.

main

By default, the configuration file is located under/etc/my.cnf.

Add configuration to configuration file:

[mysqld]##In the same local area network, pay attention to the unique server-id=100 ##Enable binary log function, you can arbitrarily take (key) log-bin=mysql-bin

Configuration changes require a reboot to take effect:

service mysql restart

After restart, enter mysql:

mysql -uroot -p

Create a data synchronization user in the master database and grant the user the slave REPLICATION SLAVE permission and the REPLICATION CLIENT permission to synchronize data between the master and slave databases.

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

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

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

Check the status of the main mysql:

show master status;

Record the values of File and Position and do nothing else to avoid causing a change in Position.

from

Add from my.cnf configuration:

mysqld]##Set server_id, note that only server-id=101 ##Enable binary log function, in case Slave is used as Master of other Slaves log-bin=mysql-slave-bin relay_log=edu-mysql-relay-bin

Configuration changes require a reboot to take effect:

service mysql restart

After restart, enter 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: address of Master

master_port: The port number of the Master

master_user: user used for data synchronization

master_password: password of the user used for synchronization

master_log_file: Specifies which log file Slave copies data from, i.e. the value of the File field mentioned above

master_log_pos: From which Position to read, i.e. the value of the Position field mentioned above

master_connect_retry: the retry interval if the connection fails, in seconds, the default is 60 seconds

To view master-slave synchronization status from mysql:

show slave status \G;

SlaveIORunning and SlaveSQLRunning are both No because we haven't started master-slave replication yet.

Enable master-slave copy:

start slave;

Check synchronization status again:

show slave status \G;

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

If SlaveIORunning is always Connecting, there are four reasons:

1, the network is not working, check the ip port

2. The password is incorrect. Check the username and password used for synchronization.

3, pos is incorrect, check the Master's Position

4, mysql8 unique password rules caused by:

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 configuration in my.cnf of master:

binlog-do-db: specifies which db is logged in mysql binlog

Or add configuration to slave's my.cnf:

replicate-do-db= name of database to be replicated. If multiple databases are replicated, set this option repeatedly. replicate-ignore-db= name of database to be replicated. If multiple databases are replicated, set this option repeatedly.

If you want to synchronize all libraries and tables, run 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 check the log:

/etc/log/mysqld.log

So far, mysql8 master-slave synchronization has been completed.

At this point, the study of " Mysql master-slave replication principle and construction method" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.