How to set up MySQL Master/Slave replication?

Colleagues hello to all.

In today's article, I'll be talking about the Mysql thing called replication based on the position of the binary log file. This option is considered the easiest to perform, but no less affective. The process of replication in Mysql allows you to copy data from one database server to another, such servers are commonly called master slaves. Replication is asynchronous by default.

I will use the database version: 8.0.29

Servers I will use for demonstration:

192.168.2.229 mysql1.local - MASTER
192.168.2.230 mysql2.local - SLAVE

Settings 192.168.2.229 mysql1.local and 192.168.2.230 mysql2.local will be exactly the same since you will have cases when you need to switch replication in the opposite direction, and in order not to reconfigure anything then we make everything the same at once.

Article content:

1. Configuring the master server.
2. Creating an account on the master server.
3. Setting up a slave server.
4. Backup copy of the master server.
5. Search for binary log position on master server.
6. Connect the slave server to the master server.
7. Starting replication.
8. Checking replication.
9. Summary

1. Setting up the master server.

We'll start by setting up the database configuration file. By default, it is located at /etc/my.cnf.

$. vim /etc/my.cnf

1.1. The first thing we need to do is set up the server_id. server_id is the id of the server and is used to identify the server in the replication topology and must be a positive integer between 1 and 32.

[mysqld]
server_id = 10

1.2. Configuring binary log logging.

Binary logging on the master server is required because the slave server will connect to the master server and read these binary logs. Binary logging is enabled by default. But by default, all binary logging will be created and stored in the directory where all database files are located. This does not suit us and we ourselves will specify the location for storing binary logs.

[mysqld]
log_bin = /app/mysql/binary_log/mysql-bin.log
log_bin_index = /app/mysql/binary_log/binlog.index

1.3. Set up replication consistency.

The innodb_flush_log_at_trx_commit and sync_binlog variables are responsible for replication consistency. Always set the values ​​of these variables to 1. This variable can have the values ​​0, 1, 2. If you enter 0 or 2, then there is a risk of losing transactions.

[mysqld]
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

1.4. Setting binary logging on the replica.

Binary logging on the slave server is optional, but since at some point you want to switch replication in the opposite direction, it is better that the slave server also maintains its own binary logging.

[mysqld]
log_replica_updates = ON

1.5. Setting relay logging.

The relay_log and relay_log_index variables are responsible for relay logging. These variables are specific to the slave server, and they are enabled by default, but they will be created in the directory where you have all the database files, and this is not suitable for us. We will create our directories.

[mysqld]
relay_log = /app/mysql/relay_log/mysql-relay.log
relay_log_index = /app/mysql/relay_log/mysql-relay.index

1.6. Result.

In the end, your configuration file should be like mine. This server is master 192.168.2.229 mysql1.local

[mysqld]
server_id = 10
log_bin = /app/mysql/binary_log/mysql-bin.log
log_bin_index = /app/mysql/binary_log/binlog.index
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
log_replica_updates = ON
relay_log = /app/mysql/relay_log/mysql-relay.log
relay_log_index = /app/mysql/relay_log/mysql-relay.index

2. Creating an account on the master server.

Each slave server will connect to the master server using a username and password, so the master server must have a user account that the slave server will use to connect.

2.1. Create an account on the master server.

mysql> CREATE USER 'replication'@'%' IDENTIFIED BY 'kjs&#&d89h3G';

2.2. Assign the necessary rights to this user.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';

3. Configuring the slave server.

In the configuration file of the slave server, we must add everything that we added to the master server, except for the server_id. server_id must be unique on each server in the replication topology where it participates. Well, let's add the  read_only parameter so that it is not possible to insert, edit and delete data, but only read them.

[mysqld]
server_id = 11
log_bin = /app/mysql/binary_log/mysql-bin.log
log_bin_index = /app/mysql/binary_log/binlog.index
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
log_replica_updates = ON
relay_log = /app/mysql/relay_log/mysql-relay.log
relay_log_index = /app/mysql/relay_log/mysql-relay.index
read_only = ON

On the master server, we have server_id 10, and the slave server will be 11.

4. A backup copy of the master server.

4.1. Now it's time to make a backup copy of the master server database and transfer this copy to the slave server. There are many options for making a backup, but we will use the simplest one, the mysqldump utility.

$. mysqldump -u root -p --all-databases --source-data > /app/backup/backup.db

4.2. After we have made a backup copy of the database, we need to transfer it to the slave server.

$. scp /app/backup/backup.db  root@192.168.2.230:/app/backup/

4.3. On the slave server, we now need to import this backup.

$. mysql -u root -p < /app/backup/backup.db

5. Finding the position of the binary log on the master server.

On the master server, we need to find the last position in the binary log, which is what the log is currently called.

mysql> SHOW MASTER STATUS;

In this result, it is important for us to record the File and Position values.

6. Connect the slave server to the master server.

Now after all that we have done above, we need to tell the slave server how to connect to the master server.

mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.2.229', SOURCE_USER='replication', SOURCE_PASSWORD='kjs&#& d89h3G', SOURCE_LOG_FILE='mysql-bin.000004', SOURCE_LOG_POS=871;

Success! It remains to start replication.

7. Start replication.

There are START REPLICA and STOP REPLICA commands for starting and stopping replication in the database. We need to execute START REPLICA.

mysql> START REPLICA;

8. Checking replication.

After starting replication, we need to check if everything is working correctly. To check on the slave server, use the command:

mysql> SHOW REPLICA STATUS\G;

The most important parameters that indicate that replication is successful are:

Replica_IO_Running: Yes
Replica_SQL_Running: Yes

9. Results.

As a result of our colleague, today we have discussed how to set up the simplest implementation of master slave replication based on binary log positions. In most cases, this implementation option will be enough, but if you have a very loaded database, then you need to use additional replication features.

Gradually, in the following articles, we will analyze more complex implementations of replication, including such concepts as GTID and so on.