MySQL Master-Slave replication between CentOS 6 cPanel server

Here I am going to illustrate how to replicate MySQL database between Master and Slave Servers.

Requirements:

Two cPanel preinstalled servers with same versions of MySQL database server.

eg :

Master server –  198.228.227.1

Slave server – 198.228.227.2

Master server configuration

Default location of MySQL server configuration file will be located at /etc/my.cnf

Add the following changes to the configuration file,

server-id=1
log-bin=mysql-bin
binlog-ignore-db="mysql"   #ignores mysql database from replication

Here server-id=1 is used to represent it  as a master server.

log-bin=mysql-bin

and knowing about log-bin you may refer the following article

http://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html

Restart MySQL service by using the following command.

service mysql restart

Now, we need to setup access from slave node to master node,so we need to make the following changes in master node MySQL server.

Enter into MySQL server by using the command

mysql

In a Cpanel installed server, it is not necessary to access MySQL using username and password.If that doesn’t work you will have to use the following command.

mysql -u root -p

It will show up like this if you succeed,

mysql>

Now give the privileges using the following command

mysql>grant all privileges on *.* to 'replication'@'198.228.227.2' identified by 'rep_password';

exit mysql on success,

mysql>exit;

Restart MySQL server.

You can check whether the master server is active by using the following command

mysql>show master status;

+------------------+--------------------------+------------------+
| File | Position  | Binlog_Do_DB             | Binlog_Ignore_DB |
+------------------+--------------------------+------------------+
| mysql-bin.000020 | 210                    | | mysql            |
+------------------+--------------------------+------------------+
1 row in set (0.00 sec)

We are all now set on the master server.

Slave server configuration

In slave configuration, my.cnf should be added with following entry,

server-id=2

It’s all you have to do in my.cnf file

Now restart the MySQL service.

After that enter into MySQL and perform the following commands

mysql>stop slave;

mysql>CHANGE MASTER TO MASTER_HOST='198.228.227.1',MASTER_USER='replication',MASTER_PASSWORD='rep_password';

mysql>start slave;
mysql>SHOW SLAVE STATUS\G

Restart MySQL and use the following command to check the status of MySQL slave connection,

tailf /var/lib/mysql/[hostname].err

About Author

Digin
Howdy, I’m a System Administrator living in Ernakulam, India. I am a fan of technology, web development, and programming. I’m also interested in gaming and movies.

Leave a Reply