MySQL Replication Setup

by panadmin • June 25, 2012 • Linux, MySQL • 0 Comments

The target uses for replication in MySQL include:

Scale-out solutions – spreading the load among multiple slaves to improve performance. In this environment, all writes and updates must take place on the master server. Reads, however, may take place on one or more slaves. This model can improve the performance of writes (since the master is dedicated to updates), while dramatically increasing read speed across an increasing number of slaves.
Data security – because data is replicated to the slave, and the slave can pause the replication process, it is possible to run backup services on the slave without corrupting the corresponding master data.
Analytics – live data can be created on the master, while the analysis of the information can take place on the slave without affecting the performance of the master.
Long-distance data distribution – if a branch office would like to work with a copy of your main data, you can use replication to create a local copy of the data for their use without requiring permanent access to the master.

Install mysql-server package on master and slave.

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

yum -y install mysql-server

service mysqld start

chkconfig mysqld on

Now we have mysql server running on both servers.

Prepare the master server.

Edit /etc/my.cnf and add two lines in [mysqld] section
log-bin=mysql-bin
server-id=1

Restart the mysqld service, create a user for replication and lock the tables so that slave can be configured.

service mysqld restart
mysql -u root
mysql> create user 'repl'@'%' identified by 'pass123';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'repl'@'%' ;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges ;
Query OK, 0 rows affected (0.00 sec)
mysql> flush tables with read lock ;
Query OK, 0 rows affected (0.00 sec)

service mysqld restart

mysql -u root

mysql> create user 'repl'@'%' identified by 'pass123';

Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'repl'@'%' ;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges ;

Query OK, 0 rows affected (0.00 sec)

mysql> flush tables with read lock ;

Query OK, 0 rows affected (0.00 sec)

Keep the mysql client alive until slave is configured.

Note : If you are using iptables firewall on server, then use the command below to open connections to mysql.

iptables -I INPUT 1 -p tcp --dport 3306 -m state --state NEW -j ACCEPT
service iptables save
service iptables restart

iptables -I INPUT 1 -p tcp --dport 3306 -m state --state NEW -j ACCEPT

service iptables save

service iptables restart

Open another client and check the log file name and position on master.

mysql> show master status \G
File  : mysql-bin.000001
Position :406

mysql> show master status \G

File : mysql-bin.000001

Position :406

Make a note of these and proceed to next steps….

Prepare the slave server

Edit /etc/my.cnf and add one line in [mysqld] section
server-id=2

Now restart service on slave and configure it to connect and replicate from master server.

service mysqld restart
mysql> change master to master_host='centos1', master_user='repl', master_password='pass123', master_log_file='mysql-bin.000001', master_log_pos=406;
mysql> start slave;

service mysqld restart

mysql> change master to master_host='centos1', master_user='repl', master_password='pass123', master_log_file='mysql-bin.000001', master_log_pos=406;

mysql> start slave;

Now the slave is ready.

Testing the setup

Unlock the server and add some data.

mysql> unlock tables ;
Query OK, 0 rows affected (0.00 sec)
mysql> use test
Database changed
mysql> create table a (id int(2) );
Query OK, 0 rows affected (0.02 sec)

mysql> unlock tables ;

Query OK, 0 rows affected (0.00 sec)

mysql> use test

Database changed

mysql> create table a (id int(2) );

Query OK, 0 rows affected (0.02 sec)

Now go to slave and check if the data has been replicated.

mysql> use test
Database changed
mysql> show tables ;
+----------------+
| Tables_in_test |
+----------------+
| a              |
+----------------+
1 row in set (0.00 sec)