A Beginners Guide To MySQL Replication Part 2: Configuring Source and Replica Servers

In the first part of this series, we spoke about MySQL Replication, the different types, replication formats, benefits, and downsides, as well as a brief introduction to the requirements needed for setting up MySQL Replication. To ensure we stay on track with our intended topic, it’s important to have certain prerequisites in place before we begin. While I won’t be able to provide an in-depth explanation, I recommend conducting research and preparing these environments beforehand.

1. Setup two computers, ideally virtual machines, one for the source server, and the other for the replica server.

2. Install MySQL server software on the virtual machines and make sure they are both running the same version of the operating system and MySQL software.

3. Ensure there is a network connectivity available. This is to help the source and replica servers communicate with each other.

Once these three prerequisites are set, then we can finally dive into setting up our replication servers.

Configure the source server

We would be configuring our source server using the binary log file position based replication. This is essential for the replica servers to receive updates to the database, if binary logging isn’t setup, replication becomes unfeasible. In order to set up binary logging in MySQL replication, we need to modify the MySQL configuration file (my.cnf or my.ini) on the source server to enable binary logging. Here’s what we need to do:

• Locate the MySQL configuration file: The location of the configuration file varies depending on the operating system you’re using. On Linux systems, it is typically located in /etc/my.cnf or /etc/mysql/my.cnf. On Windows systems, it is typically located in the MySQL installation directory under the subdirectory named “my.ini”.
• Modify the ini file: Add the following lines to the [mysqld] section of the configuration file:

server-id=1

log-bin=mysql-bin

To identify servers within a replication topology, a server ID is assigned. MySQL 8.0 assigns a default server-id value of 1, however this value can be changed dynamically and must be a positive integer ranging from 1 to (232)−1. You can modify the server-id value easily using the following query:

SET GLOBAL server_id = your-prefered-integer-value;

In this article, I will be using the default server-id value of 1. The `server-id` value should be unique for each server in the replication setup. Also, the `log-bin` value sets the name of the binary log file that will be created on the source server. According to the official documentation, it is important to note that the following has an effect on the source:

To achieve the highest level of durability and consistency in a replication configuration that uses InnoDB with transactions, it is recommended to set the following values in the source’s my.cnf file:

innodb_flush_log_at_trx_commit=1

And:

sync_binlog=1

Verify that the skip_networking system variable is disabled on the source. If this variable is enabled, communication between the replica and the source will be impossible, resulting in a replication failure.

For the changes to take effect, restart the MySQL service.

Configure the replication server

Remember that in order for our replication to work, each server must have a unique server-id value. For example, if you have two servers, one source(or primary) and one replica, you can set `server-id` to 1 on the source and 2 on the replica. It is worthy to note that each replica server must be configured with a unique server-id that is greater than the source’s server-id to which it is connected to. It is possible to also have multiple replica servers, this isn’t a problem as long as you choose a server-id value that does not conflict with existing replicas.

• Stop the MySQL service on the replica server.
• Create a backup of the MySQL data directory (my.ini) on the replica server. This is to ensure that you can recover the database if something goes wrong during the replication process.
• Edit the MySQL configuration file (my.cnf or my.ini) to enable replication and set a unique server-id. This can be done by adding the following lines to the configuration file:

[mysqld]
server-id= unique_integer_value

Or by using the MySQL command:

SET GLOBAL server_id=unique_integer_value;

Make sure that the `unique_integer_value` is a unique integer that is different from the server ID of the master server and any other slave servers.

• Start the MySQL service on the replica server.

Create a MySQL user with replication privileges on the replica server

To establish a connection between the source and its replicas, each replica requires a unique MySQL username and password. Hence, it is necessary to create a corresponding user account on the source. You can create a new user account by employing the CREATE USER command.

CREATE USER 'replication_user'@'localhost' 
                     IDENTIFIED BY 'password';

Replace `replication_user` with the desired username and `password` with the desired (far stronger) password for the new user.

Additionally, to authorize this account for replication purposes, use the GRANT statement to confer the requisite privileges.

GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%'

Note that in this example, the privilege is granted to a user named `replication_user` from any host (represented by `%`)

Get Binary Log Coordinates

In order for the replica to start the replication process at the accurate time, it is important to get the source’s current coordinates within its binary log. This is to ensure that the source server’s data is synchronized with the replication servers. To do this, set the ‘Flush tables’ option with a read lock and stop executing any commands on the source server.

mysql> FLUSH TABLES WITH READ LOCK;

Note: If you intend to export data without locking your tables, please skip this step.

To determine the current binary log file name and position, use the SHOW MASTER STATUS statement on a separate session of the source.

mysql> SHOW MASTER STATUS

Then, record the current binary log coordinates (file name) for the source server and position, as they will be necessary when configuring the replication settings later.

Create snapshots of existing data in the source server

We are going to copy the existing data from the source server into the replica server. This can be easily done using the mysqldump tool, which is used to create a dump of all the databases we want to replicate. It is the most preferred method when using the InnoDB

mysqldump --all-databases --master-data > dbdump.db

This dumps all databases to a file named dbdump, and locks the table which might result to a downtime. To avoid this, you can use this command like this:

mysqldump --skip-lock-tables --single-transaction --flush-logs 
--hex-blob --master-data=2 -A -uroot -p > C:\dump.sql

The –master-data statement starts the replication process on the replica server. If you do not want to start the replication process yet, then start the replica with `--skip-slave-start`

Use the `CHANGE MASTER TO` statement to configure the replica.

This statement helps us configure the replica in order to connect to the source server by specifying some information. It includes information such as the source server’s hostname or IP address, the replication username and password, and the binary log file and position from which replication should start.

For example, the `CHANGE MASTER TO` statement might look like this:

CHANGE MASTER TO
      MASTER_HOST='master_hostname',
      MASTER_USER='replication_user',
      MASTER_PASSWORD='password',
      MASTER_LOG_FILE='mysql-bin.000001',
      MASTER_LOG_POS=12345;

Make sure to replace `master_hostname`, `replication_user`, `password`, `mysql-bin.000001`, and `12345` with the appropriate values for your setup.

Lastly, start the replication process by executing the `START SLAVE` statement on the slave server.

mysql> START SLAVE;

Test MySQL Replication

You can test that your MySQL replication is working by executing the following steps:

1. Login to the MySQL on your source server, create a database and insert some data into a table.

mysql -u root -p

Then:

mysql> CREATE DATABASE testdb;

Exit from the source server using the EXIT statement:

mysql> EXIT;

2. Login to the replica server and verify the the replication status using the following command:

mysql> SHOW SLAVE STATUS\G

Look for the `Slave_IO_Running` and `Slave_SQL_Running` fields in the output to ensure that replication is running without errors.

3. Check for all existing databases in the replica server using the following statement:

mysql> SHOW DATABASES;

The newly created testdb should have been replicated and exists on the replica server. If the data is not replicated, check the replication status on the slave server for any error messages or warnings.

Yayy! You just learnt how to replicate servers using MySQL Replication techniques.

 

The post A Beginners Guide To MySQL Replication Part 2: Configuring Source and Replica Servers appeared first on Simple Talk.

from Simple Talk https://ift.tt/pwVe5O3
via