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.
1. Root access on both servers
2.Root access to mysql instances
3. 2 servers for hardware fault tolerence
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.
Configure the master. We will assume the master has the IP of 18.104.22.168
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.
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.
We then start the MySQL service.
service mysqld start
Log into the mysql server
mysql -u root -p (remember the password you set above)
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;
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.