Practice background

Recently, I joined the technical sharing group of my classmates. Four people are divided into two groups and have a technical sharing once a half month. Now I am assigned to explain Mysql blog. Last week I finished it, but they always feel that they don't know the blog thoroughly and feel it's not relevant. So they feel compelled to add a round of sharing.As a summary of my learning during this period of MySQL and sharing it with practice, the logs I shared before were very theoretical in classification and principles, and I was not satisfied with them. I spent a day doing data recovery and master-slave replication of binary logs, so this article came out, I always felt shallow on paper and knew nothing about it.To bow, this sentence popped out of my mind.

Preconfiguration

I'm using Mysql as master on the Mac OS native machine and Mysql as slave on the virtual machine, first modifying my.cnf database configuration files for master and slave.The contents are as follows:

master's my.cnf

[mysqld]
# 1. Configuration related to master-slave backup
server-id = 1                # Server id number, do not duplicate with other servers
log-bin=mysql-bin            # Open Binary Log
log_bin_index = mysql-bin.index        # File name of index binary log
sync_binlog = 1                # Set to 1 to synchronize the log of changes and events that occur every time MySql is on your hard drive
binlog_format = Row            # Copy mode Statement, Row, mixed
skip_slave_start = 1        # Prevent the slave server from automatically turning on after a crash to give you enough time to repair.
max_binlog_size = 200M         # Specify the size of the binary log

# 1.1 The name of the binary database that needs to be synchronized
binlog-do-db = test
# 1.2 Unsynchronized binary database name, commented out if not set
binlog-ignore-db = information_schema
binlog-ignore-db = mysql

slave's my.cnf

 # 1. Master-Slave Backup Related Configuration-Slave Server
 # Instead of configuring master server information here, configure it by command
 server-id = 2                  # Server id number, do not duplicate with other servers
 read_only = 1                  # Making the slave server read-only prevents someone from incorrectly inserting data from the server, causing inconsistent master-slave data. 
 log-bin=mysql-bin              # Open Binary Log
 log_bin_index = mysql-bin.index        # File name of index binary log
 log_slave_updates = 1
 relay_log = mysql-relay-bin    # relay logs 
 relay_log_index = mysql-relay-bin.index
 skip_slave_start = 1           # Prevent the slave server from automatically turning on after a crash to give you enough time to repair. 
 max_binlog_size = 200M         # Specify the size of the binary log

 # The following configuration is for convenience when switching from library to primary Library
 # 1.1 The name of the binary database that needs to be synchronized
 binlog-do-db = test
 # 1.2 Unsynchronized binary database name, commented out if not set
 binlog-ignore-db = information_schema
 binlog-ignore-db = mysql

Practice process

When the configuration file is modified, the mysql service needs to be restarted for the configuration to take effect; then the master uses the root user to create a synchronization account and authorize the slave; then checks the master status, stops the master operation at this time, configures the location of the master log of the slave; and finally checks whether the master-slave backup succeeds.The implementation process is as follows:

The user I created here allows any host to login with the correct password or specify an ip address

// Create synchronization account repl and authorize
create user 'repl'@'%' identified by 'password'; 
grant all privileges on *.* to 'repl'@'%';

View mster status

show master status\G

Configure the location of the slave tracking master log, the local IP address is 192.168.1.90, the slave's IP address is 192.168.92.141, and the slave's IP address is 192.168.92.141. The configuration process does not require the slave's ip. repl is the synchronization account we created, followed by the password, the binary log file, and positon 5616 as seen in the binary log.

CHANGE MASTER TO
             MASTER_HOST='192.168.1.90',
             MASTER_USER='repl',
             MASTER_PASSWORD='password',
             MASTER_LOG_FILE='mysq-bin.000003',
             MASTER_LOG_POS=5616;

Next, turn on the slave and check the slave master-slave backup status, where the Slave_IO_State status is Wait for master to send event indicating that the master-slave backup configuration was successful

// Open slave
start slave;

// View Status
SHOW SLAVE STATUS\G

Next, we add a row of data user4 to the user table of the test database:

Look from the library:

Seeing the addition of a row of data from the library synchronization indicates that we have successfully implemented master-slave backup using binary log binlog. Finally, let me remind you that my database versions are all versions of mysql8,0+. If the versions do not conform to the configuration, it is best to unify. When creating a synchronization account, mysql8.0+ version of SQLStatements are also different. If your version is below MySQL 8.0, you can refer directly to others'tutorials, but the general steps are the same. This article focuses on master-slave replication of binary logs, followed by practices on transaction logs (redo log and undo log) for a better understanding of MySQLLog, my technology is limited. If there are any errors, you are welcome to criticize and correct them. Thank you.