How to setup MySQL Master-Slave replication

-







Hello there!

This time I’m going to discuss one of the projects I got involved recently. The team is expanding the operations to a new operator and we need to deploy the platform in new servers. One instance is to create database replications in MySQL.

Below is the environment I’m working on

  1. Master and Slave are CentOS Linux release 7.4 Linux Servers. 
  2. Master IP Address is 192.168.100.1. 
  3. Slave IP Address is 192.168.100.2. 
  4. Master and Slave are on the same LAN network. 
Let’s install MySQL on the master server and configure

1. For CentOS 7 and Red Hat (RHEL) 7
yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

2. CentOS 7.4/6.9 and Red Hat (RHEL) 7.4/6.9
yum install mysql-community-server

3. Start MySQL server and auto start MySQL on boot. Below commands can be used in Fedora 27/26/25 and CentOS 7.4 and Red Hat (RHEL) 7.4
systemctl start mysqld.service
systemctl enable mysqld.service


But I couldn’t log in to the MySQL by using below command.
$mysql -u root -p

It prompts to enter the password which I didn’t enter into the installation process. Then I used below mechanism to get the root password reset.

1. Stop mysql:
systemctl stop mysqld

2. Set the mySQL environment option
systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"

3. Start mysql using the options you just set
systemctl start mysqld

4. Login as root
mysql -u root

5. Update the root user password with these MySQL commands
mysql> UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPassword')
-> WHERE User = 'root' AND Host = 'localhost';
mysql> FLUSH PRIVILEGES;
mysql> quit

6. Stop mysql
systemctl stop mysqld

7. Unset the MySQL environment option so it starts normally next time
systemctl unset-environment MYSQLD_OPTS

8. Start mysql normally:
systemctl start mysqld

Try to login using your new password:
7. mysql -u root -p


Note
As of MySQL 5.7.6, for MySQL installation using an RPM distribution, server startup and shutdown is managed by systemd on several Linux platforms. On these platforms, mysqld_safe is no longer installed because it is unnecessary.


Now I’ve access to the mysql-server of the master database.


Let’s configure the master server.


# vi /etc/my.cnf
Add the following entries under [mysqld] section and don’t forget to replace testdb with the database name that you would like to replicate on Slave.

# [mysqld] section
# Start Modification
# First line is probably already there
server-id = 1
binlog-do-db=ideabizAdmin
expire-logs-days=7
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql-bin.log


Once configurations are done, you need to restart the mysql. Use below commands.

# /etc/init.d/mysqld restart

Now all the configurations are done in the master database. Let’s create the slave user and grant permission.

Use below command to log in to MySQL

# mysql -u root -p

mysql> STOP SLAVE;
Since MySQL is using row-based logging format, by using above command it will shut down the slave server process.


mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password';

Above command will create slave user. Earlier I’ve given the access to the user by below command. Once finished the implementation, I saw there’s a connectivity issue from slave server to Master.

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'localhost' IDENTIFIED BY 'slave_password';

When granting the permission it’s important to mention the IP that you need to access. Otherwise, you can use the wildcard(%) and grant permission.

Note: Using wildcard may cause some security issues.

mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | POSITION | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 451228 | lintut | |
+------------------+----------+--------------+------------------+
1 ROW IN SET (0.00 sec)
Now we need to apply the read lock and dump all the databases to the slave server.
mysqldump -u root -p --master-data database > /root/database.sql

Once you dump all the data, let’s release the read lock and exit mysql by using below commands.
mysql> UNLOCK TABLES;
mysql> quit;

Okay, now I’m going to work on the slave server. Let’s copy the mysql dump to the slave server by using below command

scp /root/database.sql root@192.168.100.2:/root/

Let’s configure slave server. 




source: www.toptal.com


As we did in the master server, you need to install MySQL and configure my.cnf file in the slave as below
server-id = 2
replicate-do-db=ideabizAdmin
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin

In the earlier MySQL versions, you may allow configuring master-host details in my.cnf file. But when I tried the same, it gave an error saying MySQL cannot be restarted after the modification.
master-host=192.168.100.1
master-connect-retry=60
master-user=slave_user
master-password=slave_password

Instead of setting the slave's my.cnf, I used the MySQL console like below.

1. Let’s stop the slave
mysql> slave stop;

2. Change the host details
mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.1', MASTER_USER='slave_user', MASTER_PASSWORD=’slave_password’, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=451228;
3. Start the slave again
mysql> slave start;
4. See the slave status
mysql> show slave status\G

That’s it!
Now we have successfully implemented the Master-Slave replication in MySQL. Let’s check and confirm.
On the master server, I’m creating the database/tables now
mysql> create database ideabizAdmin;
mysql> use ideabizAdmin;
mysql> CREATE TABLE employee (c int);
mysql> INSERT INTO employee (c) VALUES (1);
mysql> SELECT * FROM employee;

Below is the expected result

+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

Let’s log in to the slave server and verify the data is replicated.
mysql> use ideabizAdmin;
mysql> SELECT * FROM employee;

+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)


Yeyyyyy! We are done :) Do post your comments below

Comments

Post a Comment

Popular posts from this blog

Hash Functions

-
Image
One way functions that transfer character inputs to compressed output value named as hash functions. The input can be infinite(arbitrary), but the output always has a finite(fixed) set of characters. This produces a fingerprint of the file/message/data source: Wikipedia You cannot reverse the function and get the input value in Hash functions. If you have the content, you can use the hash function to calculate the hash value, but the other way around is not possible. If you have the hash value and the function, you can’t use it to get the input text. Below are sample scenarios that hash functions may become useless. If the function is reversible, it’s not secure. Hash values can be exposed to the public. Hence we don’t want someone seeing the hash value. The values may not collision resistant. There may be use cases with two unique input values return the same hash value. Since digital certificates/Signatures using hash fun
Read more

CAP THEOREM

-
Image
What’s C, A and P? Consistency is the easiest one. It roughly means that the clients get the same view of data. By saying that a system is consistent we often mean strong consistency, but it also can come in different flavors. Availability is a property saying that every request to a non-failing node will return a (meaningful) response. The response may not contain all data (so the harvest will not be 100%), but it should be useful for the client. Partition tolerance means that the system will continue working even if any number of messages sent between nodes is lost. This can be e.g. a network failure between two data centers, where nodes in each data center form a partition. Also note that a failure of any number of nodes forms a partition (it is not possible to distinguish between a network failure and a node failing and stopping to respond to messages). The basic statement of the CAP theorem is that, given the three properties of Consistency, Availability, and Pa
Read more

How you can manage throttle out errors in WSO2 API Manager

-
Image
You might face the above-mentioned error if you have an application in Dialog Ideabiz platform.Let’s start from the beginning. Once you create an application on Ideabiz, you need to subscribe to the APIs you require. We use WSO2 API manager to manage the APIs . When admin approves your API subscriptions, he/she will assign a tier value for each API(5tps, 10 tps etc.). Also, they will assign a tier value for the application as well. Let’s assume you wanted to create an application by using SMS and Payment API. The team has assigned 5tps for each API and 10tps for your application. You use SMS API only to send SMS to the users and payment API to charge the users on daily basis. When considering each API, (as per above example) you should manage  5 transactions per second in the SMS API. Which means you can have 5 API calls per second. Let’s assume before you send the SMS, you want to charge Rs 1 from the user. Therefore you need to call payment API as well. Since the Pa
Read more