principle: When the database performs DDL and DML statement operations, it will be recorded in the binlog log file. By reading the logs here, you can know which DDL and DML operations the database has performed. This is the log of the main database. This log can be obtained in real time through relevant configurations from the database. The content that needs to be synchronized in this log is written into the relay log of the slave database through IO thread. Note that this is written to the relay log, not directly to the database. Then the SQL thread obtains log information from the relay log file, reads the relevant DDL and DML operations and then performs synchronization operations.
To perform master-slave replication operations, you need to enable the database port (MySQL port defaults to 3306) or turn off the firewall.
If you are learning, you can choose to turn off the firewall. The operation is as follows
# Turn off firewall service
sudo systemctl stop firewalld
# Prohibit the firewall service from starting and starting up automatically
sudo systemctl disable firewalldIf it is a development environment, it is recommended to only open the ports that need to be opened. Take 3306 as an example, and the operation is as follows
# Open the TCP protocol of port 3306
sudo firewall-cmd --zone=public --add-port=3306/tcp --permanent
# Reload the firewall configuration
sudo firewall-cmd --reloadPerform the following configuration on the main library server
# Install pure mysql first, configure /etc/file that needs to be set as the main library
vim /etc/
# Add two lines of configuration to the file, as follows, server-id is a unique service id, read-only is 0, which means reading and writing are allowed, and 1 means reading is allowed only
server-id=1
read-only=0
# Restart the database service
systemctl restart mysqld
# Log in to the database and create the slave user
create user 'rep'@'%' identified with mysql_native_password by 'Root@123456';
# Grant user permissions
grant replication on *.* to 'rep'@'%';
# Check the file status of the binary log
show master status;
# You may see such data at this time
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 156 | | | | |
+---------------+----------+--------------+------------------+-------------------+
# The above record indicates that the current binary file is recorded in the binlog.000001 file, and the location is 1476
# Record the above informationConfigure the following configuration from the library
# Configure the /etc/ file from the library, add the following two lines of configuration
server-id=2
read-only=1
# Restart the database service
systemctl restart mysqld
# Log in to the database and execute the following statement. My MySQL version is 8.0.26. It is said that the commands before 8.0.23 will be somewhat inconsistent. Here, the source_host is replaced with its own main database server IP. Other configurations will be changed depending on the situation.
change replication source to source_host='192.168.179.136',source_user='rep',source_password='Root@123456',source_log_file='binlog.000002',source_log_pos=156;
# Enable master-slave replication in mysql window
start slave;
# You can use the following statement to view the synchronization status
show slave status\G;
# The information obtained is as follows
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.179.136
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 156
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 156
Relay_Log_Space: 534
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: f432d09b-058f-11f0-9f06-000c29058b59
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
# Regarding the above configuration, some more critical things are discussed.
# Master_Host is the ip of the main library
# Master_User is a user
# Master_Port is the port
# Master_Log_File is the main library binlog file name
# Read_Master_Log_Pos is the location where the main library binlog file is written to
# Relay_Log_File is the name of the relay log file
# Relay_Log_Pos is the location where the relay log is written from the library
# Slave_IO_Running is the state of the IO thread running. It mainly obtains the binlog of the main library from the main library and writes it to the relay log of the slave library, that is, Relay_Log_File
# Slave_SQL_Running is the state of SQL thread running, mainly writing files in the Relay_Log_File log to the slave library to realize data synchronization
# If there is a problem with one of Slave_IO_Running and Slave_SQL_Running, then the master-slave replication schema can no longer synchronize the data normally. You need to check and resolve the error before starting the synchronization again.If the above steps are normal, you can then build the database and add, delete and modify data on the main library, and verify whether the slave library can successfully synchronize the data of the main library.
Note: The slave library cannot add, delete and modify data at this time, but there is an exception. If it is the permission of the super administrator, it can still add, delete and modify. You need to add, super-read-only=true to the /etc/ file of the slave library.