Implementation steps of mysql master-slave replication

mysql master-slave replication

I was originally working on sharding, but after finishing it, I found that the master-slave replication of the database had not been done, so I made some records after configuring it:

mysql master-slave replication method

There are two ways of MySQL master-slave replication: log-based (binlog) and GTID-based (global transaction identifier). This blog post uses log-based (binlog) replication.

The principle of mysql master-slave replication

1. The Master records data in the binary log, which is the file specified by log-bin in the MySQL configuration file my.cnf. These records are called binary log events.
2. Slave has two threads, one I/O thread and one SQL thread. The I/O thread requests the Master to write the obtained binlog log to the relay log file.
3. The SQL thread will read the logs in the relay log file and parse them into specific operations to achieve consistency in the operations of the master and the slave, and ultimately consistency in the data.

Specific implementation of mysql master-slave configuration

1. Master configuration

(1) Modify the my.cnf configuration file of MySQL. The tricky thing here is that the MySQL version I use does not have a my.cnf configuration file, not even a my-default.cnf. Generally speaking, my.cnf is in the /etc directory, and my-default.cnf is in /usr/local/mysql/support-files. Finally, I copied a my.cnf file from the Internet and placed it in /etc (friends can find it online by themselves, there are a lot of them).
(2) The configurations that need to be modified in my.cnf mainly include enabling the logging function, setting server_id to ensure its uniqueness (it cannot be the same as the slave database), and filtering the databases that need to be backed up.

bash-3.2# vim /etc/my.cnf
# Set server_id, which cannot be repeated. Generally, it can be set to the IP address of the main database.
server_id=81
# Backup filter: database that needs to be backed up, output binlog
binlog-do-db=demo_ds_master
# Backup filter: Databases that do not need to be backed up are not output binlog-ignore-db=mysql
# Enable binary logging and record the master database data in a log file starting with mysql-bin (customizable)          
log-bin=mysql-bin
# Configure the memory of each session, which is used to store the binary log cache during the transaction process. binlog_cache_size=1M
# Master-slave replication format (mixed, statement, row, the default is statement)
binlog_format=mixed
# The number of days after which binary logs are automatically deleted/expired. The default value is 0, which means no automatic deletion. expire_logs_days=7
# Used to skip all errors or specified error types encountered in master-slave replication to avoid slave terminal # 1062: Primary key conflict (specific parameters can be found online)
slave_skip_errors=1062

(3) Start/restart the Master database service, log in to the database, create a data synchronization user, and authorize

#Authorize this machine to back up the data of the main database mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'192.168.31.60' identified by 'password';
#Then refresh the privileges mysql> flush privileges;
#View the configured master database information mysql> show master status; 

(4) Master database data backup to ensure data consistency between master and slave databases

# Temporarily lock the table and set read-only permission mysql> flush tables with read lock;
# Back up the databasebash-3.2# mysqldump -uroot -p --add-drop-table demo_ds_master_1 > /tmp/demo_ds_master_1.sql;
# Unlock the tablemysql> unlock table;

2. Slave Configuration

(1) Modify the my.cnf configuration file of the slave database mysql and add the relay log configuration of the slave database

[root@develop etc]# vim my.cnf
# Set server_id, which cannot be repeated. The IP address of the slave database is set here.
server-id=60
# Enable the relay log from the database, write the master database's binlog to the relay log, and the relay log starts with hehe-mysql-relay-bin (customizable)
relay_log=hehe-mysql-relay-bin
# Set the slave database to read-only permission to prevent inconsistency between master and slave data read_only=1

(2) If the slave database needs to serve as the master database of another database, you need to add the configuration information on the Master and add log_slave_updates=1 to record the Master's binlog in your own binlog.

(3) Restart the slave database and synchronize the master database data

# Run the backup database of the master database [root@develop etc]# mysql -uroot -p demo_ds_master_1 < /tmp/demo_ds_master_1.sql
# Log in to the slave database [root@develop etc]# mysql -uroot -p
# Set the master node of the slave nodemysql> change master to master_host='192.168.31.80', master_user='root',master_password='password',
master_port=3306,
master_log_file='mysql-bin.000001',
master_log_pos=154;
# Enable master-slave synchronization mysql> start slave;
# Check the master-slave synchronization statusmysql> show slave status\G;
# View the status of the master and slave threads mysql> show processlist\G; 

The picture shows the following, indicating that the master-slave replication has been configured. Now inserting data into the master database can realize the automatic replication of the master database data by the slave database.

• Slave_IO_State: Waiting for master to send event
• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes

This is the end of this article about the implementation steps of MySQL master-slave replication. For more relevant MySQL master-slave replication content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!