MySQL master – slave replication

Today I’m going to write about MySQL replication. Some of the benefits of replication are balancing load, writes and updates can take place on the master, while read operations can take place in the slave and analyzing data on the slave.

Lets get started. On the master my.cnf file add the following below the [mysqld] directive.

[mysqld]
log-bin=mysql-bin
server-id=1

Now restart mysqld on the master for the changes to take effect.

shell> /etc/init.d/mysql restart

Add the following on the slave [mysqld] directive.

[mysqld]
server-id=2
master-host=<masterip>
master-user=<replicationuser>
master-password=<password>
master-connect-retry=60

Restart mysqld on the slave server.

shell> /etc/init.d/mysql restart

Now we need to create a replication user on the master server. Log into the mysql prompt and type the following. Substitute replicationuser and password by your own values.

mysql>CREATE USER '<replicationuser>'@'%' IDENTIFIED BY '<password>';
mysql>GRANT REPLICATION SLAVE ON *.* TO '<replicationuser>'@'%';
mysql>FLUSH PRIVILEGES;

Check the master binary logs. Log into the mysql prompt and type the following.

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

Output of show master status should be similar to this. Write down the show master status output, you will need the values later.

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |  1457034 |              |                  | 
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

Now we need to take a backup of the databases.

shell> mysqldump --all-databases --lock-all-tables >dbdump.db

Copy the dbdump file to the slave server.

shell> scp dbdump.db user@slave:/tmp

Now copy dump the databases on the slave server.

shell> mysql -u root -p<password> < /tmp/dbdbump.db

Now on the slave. We set up the master. From mysql prompt.

mysql> SLAVE STOP;
mysql> SHOW SLAVE STATUS \G
mysql>CHANGE MASTER TO MASTER_HOST='<masterip>', MASTER_USER='<replicationuser>', 
MASTER_PASSWORD='<password>', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=1457034;
mysql> START SLAVE;
mysql> QUIT;

And we have set up MySQL replication. We are good to go.
Note:
On my.cnf file change the bind-address directive from 127.0.0.1 to the IP you want replication to take place. For example:

bind-address            = 192.168.1.2

References:

  1. http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html
  2. http://www.howtoforge.com/mysql_database_replication

Leave a Reply