How-to Linux

Installation and setup of MySQL Master – Slave under CENTOS 6

The following guide will walk through a user on the basic installation and setup of a MySQL server on Centos 6 and then expand on this to include a MySQL slave server.

Requirements

1. Root access on both servers

2.Root access to mysql instances

3. 2 servers for hardware fault tolerence

Step 1

Setup MySQL on both servers, is an easy process.

yum install mysql-server

This installs mysql we then want to make sure mysql starts on boot.

chkconfig –levels 235 mysqld on

We then start the mysql service.

service mysqld start or /etc/init.d/mysqld start

login to mysql

mysql -u root

Then we want to set a root password for mysql

SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘new-password‘);
SET PASSWORD FOR ‘root’@’localhost.localdomain’ = PASSWORD(‘new-password‘);
SET PASSWORD FOR ‘root’@’127.0.0.1’ = PASSWORD(‘new-password‘);

We then want to create a test database.

create database testdb;

That’s it do the above on both servers and we have two fully functioning MySQL databases ready to go.

Step 2

Configure the master. We will assume the master has the IP of 200.200.200.1

Firstly stop the database server.

service mysqld stop

We then go ahead and edit the /etc/my.cnf file

We then need to adjust the following to allow networking on MySQL and have it bind to the localhost.

skip-networking

bind-address 127.0.0.1

Next we need to tell MySQL where to write it’s logs. These logs are used to the slave to determine whats changed on the master. We also want to define what database is to replicated and that this server is the master.This will set the log location the type of logs what database to replicate and set server id to 1 hence master.

log-bin= /var/log/mysql/mysql-bin.log

binlog-do-db=testdb

server-id=1

We then start the MySQL service.

service mysqld start

Step 3

Log into the mysql server

mysql -u root -p (remember the password you set above)

Step 4

Now we are logged into the database server as root we need to issue commands to begin to setup permissions for slave server.

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘password’;

mysql> FLUSH PRIVILEGES;

Step 5

While still on the MySQL console, we want to Flush the tables on testdb and issue a read lock so that we can get it’s current position for the slave.

mysql> USE exampledb;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

We then will get an output such as the following.