MySQL Master and Slave Configuration on LINUX

Replication allows MySQL server (the master) to be replicated to optional MySQL servers (the slaves). Duplication is asynchronous – slaves needn’t be connected all the time to receive updates from the master. This implies that updates will occur over long-distance links and even over transient or intermittent links like a dial-up service. Depending on the configuration, you’ll replicate all databases, designated databases, or maybe designated tables at intervals. Let us find out the steps for installation procedure of MySQL master slave.

Step 1: Edit iptables file.
Step 2: Restart iptables.
Step 3: Install mysql server and required files.
Step 4: Start and check the mysql services.
Step 5: Set mysql password.
Step 6: Edit my.cnf file.
Step 7: Login to mysql and provide privileges.
Step 8: Dump master database and save.
Step 9: Unlock database tables.
Step 10: Copy database files.
Step 11: Set slave privileges to database.
Step 12: Edit configuration for slave.
Step 13: Restart mysql.
Step 14: Check master and slave status.
Step 15: Test replication
Step 16: Video Installation

To start the configuration process follows the steps given below:

Step 1: Edit iptables file.

#vi /etc/sysconfig/iptables

add the below lines:

-A INPUT -p udp -m state --state NEW --dport 3306 -j ACCEPT
-A INPUT -p tcp -m state --state NEW --dport 3306 -j ACCEPT

Step 2:  Restart the iptables services.

#service iptables restart

Step 3:  Install mysql server to start the master configuration.

#yum install mysql-server mysql –y

Step 4:  Start and check the services.

#service mysqld start
#chkconfig mysqld on

Step5: Set mysql root password

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQLSERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!In order to log into MySQL to secure it, we’ll need the currentpassword for the root user.  If you’ve just installed MySQL, andyou haven’t set the root password yet, the password will be blank,so you should just press enter here.Enter current password for root (enter for none):OK, successfully used password, moving on…Setting the root password ensures that nobody can log into the MySQLroot user without the proper authorisation.
You already have a root password set, so you can safely answer ‘n’.
Change the root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
… Success!
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
… Success!
Normally, root should only be allowed to connect from ‘localhost’.  This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y
… Success!
By default, MySQL comes with a database named ‘test’ that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
– Dropping test database…
ERROR 1008 (HY000) at line 1: Can’t drop database ‘test’; database doesn’t exist
… Failed!  Not critical, keep moving…
– Removing privileges on test database…
… Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
… Success!
Cleaning up…
All done!  If you’ve completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!

Step6: Edit my.cnf file

#vi /etc/my.cnf/
server-id = 1
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/
relay-log-info-file = /var/lib/mysql/
log-bin = mysql-bin
# Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe]

Save and exit.

#service mysqld restart

step7: Login to the database and give privileges.

#mysql -u root –p
>stop slave;
>grant replication slave on*.*to  ‘root’@’localhost’ Identified BY ‘password’;
>flush privileges;
>flush tables withread lock;
>show master status;

Step 8: Replace database file and save

#mysqldump --all-databases --user=root --password --master-data > masterdatabase.sql

Step 9: Unlock database tables.

#mysql –u root –p
>unlock tables;

Step 10:  Copy database files.

#scp masterdatabase.sql root@

Step 11: Setting mysql slave server.

#yum install mysql-server mysql –y
#service mysqld start
#chkconfig mysqld on

Step 12: Edit configuration for slave in my.cnf

#vi /etc/my.cnf
server-id = 2
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/
relay-log-info-file = /var/lib/mysql/
log-bin = mysql-bin

Step 13:  Restart mysql.

#mysql -u root -p < /home/masterdatabase.sql
#service mysqld restart

Step 14: Check the status of master and slave by login into mysql database.

#mysql -u root –p

Step 15:  Test replication

I. Master side

#mysql –u root –p
>create database demo;
>Use root;
> create table sample (c int);
> insert into sample (c) values (1);
> select * from sample;

II. Slave side

#mysql –u root –p
>Use demo;
>Select * from sample;

Step 16:  Video Installation of mysql master and slave on centos

Leave a Reply

Your email address will not be published. Required fields are marked *

− 3 = 6