The function of master-slave replication:

1. Data distribution
2. Load Balancing (Read)
3. Backup
4. High Availability and Fault Switching
5. MySQL Upgrade Test

The way of master-slave replication:

1. Master-slave duplication
2. Mutual dominance and subordinate replication
3. Semi-synchronous replication

Principle of master-slave replication:

Mysql has a log called bin log (binary log). This log records all the SQL statements (insert,update,delete,create/alter/drop table, grant, etc.) that modify the database. The principle of master-slave replication is to copy the bin log on the master server to execute once on the slave server, so that the data on the slave server is the same as that on the master server. Now.

The process of master-slave replication:

1. The primary node must enable binary logging to record any events that modify database data.
2. Open a thread (I/O Thread) from the node to act as a client of mysql and request events in the binary log file of the primary node through mysql protocol
3. The primary node starts a thread (dump Thread), checks the events in its binary log and compares them with the location requested by the other party. If there is no request location parameter, the primary node will send the first event in the first log file one by one to the slave node.
4. The data sent from the primary node is received from the node and placed in the Relay log file. And record the specific location of the request to the main node in which binary log file.
5. Start another thread (sql Thread) from the node, read out the events in the Relay log, and execute it locally again.

The role of threads in mysql replication:

Slave node:

1. I/O Thread: Requests binary log events from the Master node and saves them in the relay log.
2. Sql Thread: Read log events from Relay log and replay them locally

Following are the deployment steps:

Environmental preparation:

Machine A: 10.0.8.8 (main mysql)

Machine B: 10.0.8.4 (from mysql)

mysql version: mysql 5.7.23

Modify server-id and log_bin of master-slave mysql configuration

vim /etc/my.conf
 Add to:
Ser-id = 8 (ID is unique, master-slave machine ID cannot be the same)
log-bin = master-bin # Creating master-slave requires opening log-bin log files

##### Refer to the configuration of mysql in the blogger section (the following is not related to the topic):

[mysqld]
server-id=4                 #To specify server-id, you must ensure that the server-id of the master and slave servers is different
auto_increment_increment=1  #Setting Primary Key Single Increment
auto_increment_offset=1     #Setting the offset of the primary key in a single increment
binlog-ignore-db=mysql        #Replication filtering: databases that do not require backups (MySQL libraries are generally not synchronized)
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
replicate-ignore-db=mysql     #Do not copy the binlog of mysql Library
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys
binlog_format=mixed           #Master-slave replication format (mixed,statement,row, default format is statement)
expire_logs_days=7            #Days of automatic deletion/expiration of binary logs. The default value is 0, indicating no automatic deletion.
slave_skip_errors=all        #Skip all errors encountered in master-slave replication or specified types of errors to avoid slave end replication interruption. For example: 1062 error refers to some primary key duplication, 1032 error is due to inconsistency between master and slave database data.
log-bin = master-bin           #Creating master and slave requires opening log-bin log files
log-bin-index=master-bin.index
log-slave-updates             #Write the updated log to the binlog
# binlog_cache_size=1M          #Memory allocated for each session, the cache used to store binary logs during a transaction

Create an account that connects to the main mysql:

mysql> grant replication slave on *.* to 'repl'@'%' identified by 'slavepwd';
mysql> FLUSH PRIVILEGES;

View master status information for main mysql:

mysql> show master status;
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
| master-bin.000006 | 10054212 |              | mysql,information_schema,performance_schema,sys |                   |
+-------------------+----------+--------------+-------------------------------------------------+-------------------+

Set the connection master mysql on the slave machine:

mysql>stop slave;
mysql>reset slave;

mysql>change master to master_host='10.0.8.8',master_port=3306,master_user='repl',master_password='slavepwd',master_log_file='master-bin.000006',master_log_pos=10054212,MASTER_CONNECT_RETRY=10,MASTER_RETRY_COUNT=0;

mysql>start slave;

See if the modification works:

mysql>show slave status\G

Verify success (create a database in the main mysql to see if it succeeds):

mysql> create database testdata charset='utf8';

# Check for success from mysql

mysql> show databases;

Setting the parameter analysis of master and slave:

master_host * The main mysql address to which the connection is made

master_port. Connected primary mysql port

master_user * Connected primary mysql user

master_password

master_log_file

master_log_pos. Postion information for master mysql

MASTER_CONNECT_RETRY The timeout waiting time for reconnection to master by default of 60 seconds

MASTER_RETRY_COUNT = 0 denotes unlimited number of reconnections

===============================================================================================

Focus on the blogger's new Wechat public number. Lazy people update it all the time.