How to Setup MySQL Master-Slave Replication on CentOS or RHEL Linux
This post will guide you how to setup MySQL Master-Slave replication on both CentOS or RHEL servers. How do I Configure MySQL Master-Slave replication with one master node and one slave node on CentOS 7 Linux system. If you want to setup Master-Slave replication for Mariadb Database on CentOS 7 Linux, just refer to the same steps with MySQL DB.
- What is MySQL Replication?
- Step1: Install MySQL
- Step2: Configure Master Server
- Step3: Create MySQL User for the Slave Server
- Step4: Configure Slave Server
- Step5: Test the Configuration
What is MySQL Replication?
You should know that MySQL Database is one of the most popular and open source relational database management. And the MySQL or MariaDB Replication is a process that allows your data from one Master server to be copied to another Slave server, so it can make your data safe. It will maintain two or more copies of your MySQL Data.
The Master-Slave replication will have only one Master server which will take all write requests, and other salve servers will be used for read request only.
Of course, there are other replication mode to replicate your data in MySQL database, such as: Master-Master.
This post will explain that how to configure a basic Master-Slave mode of replicating data with one Master node and one Slave node running on CentOS 7 Linux.
- You need to prepare two separate Linux servers running on CentOS 7 or RHEL 7.
- You also need to have a non-root user with sudo privileges for both CentOS systems.
- Set IP address for each servers: 192.168.3.43(Master node) and 192.168.3.54(slave node)
Step1: Install MySQL
The default repository of CentOS 7 or RHEL 7 do not contain MySQL packages, so you need to download and install the repo file of MySQL from its official Yum Repository. Running the following command on both CentOS 7 servers:
$ wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm $ sudo yum localinstall mysql57-community-release-el7-11.noarch.rpm $ sudo yum install mysql-community-server
[devops@localhost ~]$ wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm --2019-06-17 18:30:52-- https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm Resolving dev.mysql.com (dev.mysql.com)... 220.127.116.11 Connecting to dev.mysql.com (dev.mysql.com)|18.104.22.168|:443... connected. HTTP request sent, awaiting response... 302 Found Location: https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm [following] --2019-06-17 18:30:55-- https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm Resolving repo.mysql.com (repo.mysql.com)... 22.214.171.124 Connecting to repo.mysql.com (repo.mysql.com)|126.96.36.199|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 25680 (25K) [application/x-redhat-package-manager] Saving to: ‘mysql57-community-release-el7-11.noarch.rpm’ 100%[=============================================================================================================>] 25,680 79.4KB/s in 0.3s 2019-06-17 18:30:59 (79.4 KB/s) - ‘mysql57-community-release-el7-11.noarch.rpm’ saved [25680/25680] [devops@localhost ~]$ sudo yum localinstall mysql57-community-release-el7-11.noarch.rpm Loaded plugins: fastestmirror, langpacks Examining mysql57-community-release-el7-11.noarch.rpm: mysql57-community-release-el7-11.noarch Marking mysql57-community-release-el7-11.noarch.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package mysql57-community-release.noarch 0:el7-11 will be installed --> Finished Dependency Resolution Dependencies Resolved ======================================================================================================================================================= Package Arch Version Repository Size ======================================================================================================================================================= Installing: mysql57-community-release noarch el7-11 /mysql57-community-release-el7-11.noarch 31 k Transaction Summary ======================================================================================================================================================= Install 1 Package Total size: 31 k Installed size: 31 k Is this ok [y/d/N]: y Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : mysql57-community-release-el7-11.noarch 1/1 Verifying : mysql57-community-release-el7-11.noarch 1/1 Installed: mysql57-community-release.noarch 0:el7-11 Complete! [devops@localhost yum.repos.d]$ sudo yum install mysql-community-server Loaded plugins: fastestmirror, langpacks Loading mirror speeds from cached hostfile * base: mirrors.aliyun.com * extras: mirrors.aliyun.com * updates: mirrors.aliyun.com Resolving Dependencies --> Running transaction check ---> Package mysql-community-server.x86_64 0:5.7.26-1.el7 will be installed --> Processing Dependency: mysql-community-common(x86-64) = 5.7.26-1.el7 for package: mysql-community-server-5.7.26-1.el7.x86_64 --> Processing Dependency: mysql-community-client(x86-64) >= 5.7.9 for package: mysql-community-server-5.7.26-1.el7.x86_64 --> Running transaction check Installed: mysql-community-libs.x86_64 0:5.7.26-1.el7 mysql-community-libs-compat.x86_64 0:5.7.26-1.el7 mysql-community-server.x86_64 0:5.7.26-1.el7 Dependency Installed: mysql-community-client.x86_64 0:5.7.26-1.el7 mysql-community-common.x86_64 0:5.7.26-1.el7 Replaced: mariadb-libs.x86_64 1:5.5.60-1.el7_5 Complete!
Note: you need to press Y key and hit enter key to continue the installation. And you should be better to install the same version of MySQL package on both Master and Slave servers.
Once the installation of MySQL is complete, you need to start the MySQL service and also need to enable MySQL service to start it when system boot up. Type:
$ sudo systemctl start mysqld $ sudo systemctl enable mysqld
Step2: Configure Master Server
On Master Server, you need to modify the configuration file (/etc/my.cnf )of MySQL server with your text editor:
$ sudo vim /etc/my.cnf
Then you need to find the line bind-address, and replace the ip address with the IP address of Master server(192.168.3.43).
Then you need to set a unique server ID in the [mysqld] section, for example, you can set the server ID as number 1.
server-id = 1
then add the following lines:
Save and close the configuration file and restart the MySQL service for the changes to take effect with the following command:
$ sudo systemctl restart mysqld
Step3: Create MySQL User for the Slave Server
When you installed MySQL package on your server, it will generate a temporary password for the MySQL root user, you can use the following command to get the password:
$ grep 'temporary password' /var/log/mysqld.log
[devops@localhost ~]$ grep 'temporary password' /var/log/mysqld.log 2019-06-17T11:15:54.698577Z 1 [Note] A temporary password is generated for root@localhost: (1<CBm*hrCgz
Then you can set your new root password by running mysql_secure_installation command, it also can be used to improve the security of the MySQL server:
You need to login to the MySQL server as user root and create a user that will be used by the salve server for Replication. Type the following MySQL command to login the terminal of MySQL server:
$ mysql -u root -p
And then running the following SQL statement to create one user called “replica”:
create user 'replica'@'%' identified by 'password#'; GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%'; flush privileges;
Note: you need to replace “password#” with your own password.
mysql> create user 'replica'@'%' identified by 'password#'; Query OK, 0 rows affected (0.01 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%'; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges;
Then you need to execute the following command to print the binary filename and position that are going to be used for the slave server. Type:
Show master status;
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 1241 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
So the filename is mysql-bin.000002, and the position is 1241.
Step4: Configure Slave Server
You need to modify the configuration file of MySQL server on the Slave server. Like for the Master server above. Just add bind-address variable, and set the unique server ID for slave server. Adding the following lines in the [mysqld] section.
bind-address=192.168.3.54 server-id=2 relay-log = mysql-relay-bin log-bin = mysql-bin
Save and close the configuration file, and restarting the MySQL server on Slave server for the changes to take effect with the following command:
$ sudo systemctl restart mysqld
Then you can configure the parameters that the slave server will use to connect to the Master server. Just login to MySQL server as user root to run the following sql statements:
$ mysql -u root -p
Mysql>stop slave; Mysql> CHANGE MASTER TO MASTER_HOST = '192.168.3.43', MASTER_USER = 'replica', MASTER_PASSWORD = 'PASSWORD', MASTER_LOG_FILE = 'mysql-bin.000002', MASTER_LOG_POS = 1241; Mysql>start slave; Mysql> quit;
Note: you need to replace MASTER_LOG_FILE and MASTER_LOG_POS with the information that you get it in the Step 3. You also need to make sure that you are using the correct IP address of Master server, Master user and Master password.
Step5: Test the Configuration
If you want to check if the replication works well, you can create a new database from the Master server, and then check if the database you created can be listed on the Slave server.
On Master server:
Mysql>create database replicaTest;
On Slave server:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | replicaTest | | sys | +--------------------+ 5 rows in set (0.01 sec)
You should know that how to configure the MySQL Master-Slave replication on CentOS or RHEL Linux. If you want to get more information on how to replication Master configuration you can get if on MySQL’s official website.