MySQL 8 Master/Slave

MYSQL MASTER/SLAVE REPLICATION

We will see one of the most used architectures, where we will have a mysql master for writing/reading and nodes in slave mode for reading only, allowing us to distribute the load between several machines for reading using for example HAProxy, which we will make a post about how to use in pfsense due to the great reception of the posts on this blog about it.

We will do this installation in centos, but understanding the concept you can do it in any distro because it is basically the same changing only the way of installation.

Master

We start updating

yum -y update

We download the most recent version or the one you like, as I mentioned in the post Cluster Mysql 8 Centos We will use version 8.0.20, a version that has given me zero problems compared to the higher ones.

wget https://cdn.mysql.com/archives/mysql-8.0/mysql-community-libs-8.0.20-1.el7.x86_64.rpm
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-community-client-8.0.20-1.el7.x86_64.rpm
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-community-common-8.0.20-1.el7.x86_64.rpm
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-community-server-8.0.20-1.el7.x86_64.rpm

We install

rpm -Uvh mysql-community-*

We enable the service and start it

systemctl enable mysqld
systemctl start mysqld

During the installation it creates a temporary root password that we can locate with the following command:

grep 'temporary password' /var/log/mysqld.log

Having the password we secure the installation.

mysql_secure_installation

It will ask us questions

The first thing is to set the new root password

It will ask us if we want to remove anonymous users, we say Yes.

Then if we want root to be able to log in remotely. Here it is best to say no and then give it remote permission from our IP as long as it is fixed.

It continues asking us if we want to delete the test database, we say yes.

And finally if we want to apply by doing a Reload privileges.

Now we edit my.cnf

nano /etc/my.cnf

indicating the data directory, I always like to put it in /data previously created, the id that it will have and we will activate binlog since without it we would be nobody in this scenario, remaining as follows (we will not go into other details since each one will have to do the corresponding tuning to get the best performance)

datadir=/data
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

server-id=1
log_bin=mysql-bin

We restarted the service

systemctl restart mysqld

And we proceed to create a user for the replicas. Let's assume that our master has the IP 172.17.17.1 and our slave has the IP 172.17.17.2

mysql -u root -p

CREATE USER 'replicate'@'172.17.17.2' IDENTIFIED BY 'passwordreplica';
GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'172.17.17.2';

If we wanted to have a second replica with the IP 172.17.17.3, the same thing happens but we change the IP. Two replicas is a good number for most scenarios.

CREATE USER 'replicate'@'172.17.17.3' IDENTIFIED BY 'passwordreplica';
GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'172.17.17.3';

Once we have this we will see the position in which we have the master and then indicate it to the slaves, this position will not change until we work with this mysql. As we continue within the mysql console we execute

SHOW MASTER STATUS\G

And he will give us this back

File: mysql-bin.000005
Position: 722

Keep in mind that if you already have a mysql and you want to add a replica, you will have to block the work on it, that is, make sure there are no changes, otherwise it will be impossible, since each time you execute the previous command it will return a different position.

Well, we'll keep the user we created and this last piece of information.

Slave

Same steps that we have carried out in the master until editing the my.cnf

nano /etc/my.cnf

The only thing that will change is the server id, if you have more replicas then you will increase this server id.

datadir=/data
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

server-id=2
log_bin=mysql-bin

We restart service

systemctl restart mysqld

We connect to the mysql console

mysql -u root -p

And we write

STOP SLAVE;

CHANGE MASTER TO
MASTER_HOST='172.17.17.1',
MASTER_USER='replicate',
MASTER_PASSWORD='passwordreplica',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=722;
START SLAVE;

As we have previously mentioned, we are indicating who the master is, the user we will use and the position it is in, if this position has changed, it will fail.

We can check the status by running in the console

SHOW MASTER STATUS\G

Where it will give us a lot of data about the replica, an important parameter being the state

We can see that he has read everything and is waiting for more updates to the master.

As always, a pleasure and have a good weekend.

TL.