{"id":4208,"date":"2023-05-26T19:19:38","date_gmt":"2023-05-26T17:19:38","guid":{"rendered":"https:\/\/tech.lobobrothers.com\/mysql-8-master-slave\/"},"modified":"2025-02-06T13:19:20","modified_gmt":"2025-02-06T12:19:20","slug":"mysql-8-master-slave","status":"publish","type":"post","link":"https:\/\/tech.lobobrothers.com\/en\/mysql-8-master-slave\/","title":{"rendered":"MySQL 8 Master\/Slave"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

MYSQL MASTER\/SLAVE REPLICATION<\/strong><\/h2>\n

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.<\/p>\n

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.<\/p>\n

Master<\/strong><\/p>\n

We start updating<\/p>\n

yum -y update<\/p>\n

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

wget https:\/\/cdn.mysql.com\/archives\/mysql-8.0\/mysql-community-libs-8.0.20-1.el7.x86_64.rpm
\nwget https:\/\/cdn.mysql.com\/archives\/mysql-8.0\/mysql-community-client-8.0.20-1.el7.x86_64.rpm
\nwget https:\/\/cdn.mysql.com\/archives\/mysql-8.0\/mysql-community-common-8.0.20-1.el7.x86_64.rpm
\nwget https:\/\/cdn.mysql.com\/archives\/mysql-8.0\/mysql-community-server-8.0.20-1.el7.x86_64.rpm<\/p>\n

We install<\/p>\n

rpm -Uvh mysql-community-*<\/p>\n

We enable the service and start it<\/p>\n

systemctl enable mysqld
\nsystemctl start mysqld<\/p>\n

During the installation it creates a temporary root password that we can locate with the following command:<\/p>\n

grep ‘temporary password’ \/var\/log\/mysqld.log<\/p>\n

Having the password we secure the installation.<\/p>\n

mysql_secure_installation<\/p>\n

It will ask us questions<\/p>\n

The first thing is to set the new root password<\/p>\n

It will ask us if we want to remove anonymous users, we say Yes.<\/p>\n

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.<\/p>\n

It continues asking us if we want to delete the test database, we say yes.<\/p>\n

And finally if we want to apply by doing a Reload privileges.<\/p>\n

Now we edit my.cnf<\/p>\n

nano \/etc\/my.cnf<\/p>\n

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)<\/p>\n

datadir=\/data
\nsocket=\/var\/lib\/mysql\/mysql.sock<\/p>\n

log-error=\/var\/log\/mysqld.log
\npid-file=\/var\/run\/mysqld\/mysqld.pid<\/p>\n

server-id=1
\nlog_bin=mysql-bin<\/p>\n

We restarted the service<\/p>\n

systemctl restart mysqld<\/p>\n

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<\/p>\n

mysql -u root -p<\/p>\n

CREATE USER ‘replicate’@’172.17.17.2’ IDENTIFIED BY ‘passwordreplica’;
\nGRANT REPLICATION SLAVE ON *.* TO ‘replicate’@’172.17.17.2’;<\/p>\n

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.<\/p>\n

CREATE USER ‘replicate’@’172.17.17.3’ IDENTIFIED BY ‘passwordreplica’;
\nGRANT REPLICATION SLAVE ON *.* TO ‘replicate’@’172.17.17.3’;<\/p>\n

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<\/p>\n

SHOW MASTER STATUS\\G<\/p>\n

And he will give us this back<\/p>\n

File: mysql-bin.000005
\nPosition: 722<\/p>\n

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.<\/p>\n

Well, we’ll keep the user we created and this last piece of information.<\/p>\n

Slave<\/strong><\/p>\n

Same steps that we have carried out in the master until editing the my.cnf<\/p>\n

nano \/etc\/my.cnf<\/p>\n

The only thing that will change is the server id, if you have more replicas then you will increase this server id.<\/p>\n

datadir=\/data
\nsocket=\/var\/lib\/mysql\/mysql.sock<\/p>\n

log-error=\/var\/log\/mysqld.log
\npid-file=\/var\/run\/mysqld\/mysqld.pid<\/p>\n

server-id=2
\nlog_bin=mysql-bin<\/p>\n

We restart service<\/p>\n

systemctl restart mysqld<\/p>\n

We connect to the mysql console<\/p>\n

mysql -u root -p<\/p>\n

And we write<\/p>\n

STOP SLAVE;<\/p>\n

CHANGE MASTER TO
\nMASTER_HOST=’172.17.17.1′,
\nMASTER_USER=’replicate’,
\nMASTER_PASSWORD=’passwordreplica’,
\nMASTER_LOG_FILE=’mysql-bin.000005′,
\nMASTER_LOG_POS=722;
\nSTART SLAVE;<\/p>\n

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.<\/p>\n

We can check the status by running in the console<\/p>\n

SHOW MASTER STATUS\\G<\/p>\n

Where it will give us a lot of data about the replica, an important parameter being the state<\/p>\n

\"slave<\/p>\n

We can see that he has read everything and is waiting for more updates to the master.<\/p>\n

As always, a pleasure and have a good weekend.<\/p>\n

TL.<\/p>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t

FAQS<\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/path><\/svg><\/span>\n\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tWhat is Master-Slave Replication in MySQL and What is it Used For?<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

Master-Slave replication in MySQL is a configuration where one server acts as the master and one or more servers act as slaves. The master server handles write and read operations, while the slaves primarily handle read operations. This architecture is used to distribute the workload, improve performance, and provide redundancy for disaster recovery.<\/p>\n<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/path><\/svg><\/span>\n\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t\t\t\t\t\tWhat are the benefits of implementing Master-Slave replication in MySQL?<\/a>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t

Implementing Master-Slave replication offers several benefits, including:<\/p>\n