Mysql Master Slave Replication

MySql replication scenario Solves

a.)One of the biggest advantages to have master-slave set up in MySQL is to be able to use master for all of the inserts and send some, if not all, select queries to slave. This will most probably speed up your application without having to diving into optimizing all the queries or buying more hardware. It will Offload some of the queries from one server to another and spread the load.

b) Do backups from slave: One of the advantages people overlook is that you can use MySQL slave to do backups from. That way site is not affected at all when doing backups. This becomes a big deal when your database has grown to multiple gigs and every time you do backups using mysqldump, site lags when table locks happen. For some sites, this could mean that site goes down for few secs to minutes. If you have slave, you just take slave out of rotation (should be built into code) and run backups off the slave. You can even stop slave MySQL instance and copy the var folder instead of doing mysqldump.

In this scenario i have used

Master server ip: 192.168.10.1
Slave server ip: 192.168.10.2
Slave username: pawan
Slave pw: pawan123
data directory is: /usr/local/mysql/var/

(192.168.10.2) master my.cnf file under [mysqld] section:

# changes made to do master
server-id = 1
relay-log = /usr/local/mysql/var/mysql-relay-bin
relay-log-index = /usr/local/mysql/var/mysql-relay-bin.index
log-error = /usr/local/mysql/var/mysql.err
master-info-file = /usr/local/mysql/var/mysql-master.info
relay-log-info-file = /usr/local/mysql/var/mysql-relay-log.info
datadir = /usr/local/mysql/var
log-bin = /usr/local/mysql/var/mysql-bin
# end master

(192.168.10.2) slave’s my.cnf
[mysqld] section:

# changes made to do slave
server-id = 2
relay-log = /usr/local/mysql/var/mysql-relay-bin
relay-log-index = /usr/local/mysql/var/mysql-relay-bin.index
log-error = /usr/local/mysql/var/mysql.err
master-info-file = /usr/local/mysql/var/mysql-master.info
relay-log-info-file = /usr/local/mysql/var/mysql-relay-log.info
datadir = /usr/local/mysql/var
# end slave setup

Create user on master:
mysql> grant replication slave on *.* to ‘pawan’@’10.0.0.2′ identified by ‘pawan123′;

Do a dump of data to move to slave
mysqldump -u root –all-databases –single-transaction –master-data=1 > masterdump.sql

import dump on slave
mysql CHANGE MASTER TO MASTER_HOST=’192.168.10.1′, MASTER_USER=’pawan’, MASTER_PASSWORD=’pawan123′;

Let us start the slave:
mysql> start slave;

You can check the status of the slave by typing
mysql> show slave status\G

The last row tells you how many seconds its behind the master. Don’t worry if it doesn’t say 0, the number should be going down over time until it catches up with master. If it shows NULL, it could be that slave is not started (you can start by typing: start slave) or it could be that it ran into an error (shows up in Last_errno: and Last_error under show slave status\G).

Thanks
Pawan

This entry was posted on Friday, July 2nd, 2010 at 6:50 pm and is filed under Linux, Mysql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

4 Responses to “Mysql Master Slave Replication”

  1. Manoj Chauhan Says:
    July 2nd, 2010 at 6:53 pm

    We can add monitoring for slave status, Master Status, and slave behind status. I will post article about this soon, how can we add monitoring in Nagios.

    Good work
    Manoj

  2. Yevette Cotham Says:
    July 3rd, 2010 at 7:48 pm

    Interesting blog, thanks! I finally see the bigger picture :)

  3. cna training Says:
    July 12th, 2010 at 10:25 am

    Terrific work! This is the type of information that should be shared around the web. Shame on the search engines for not positioning this post higher!

  4. Manoj Chauhan Says:
    August 23rd, 2010 at 1:16 pm

    Some useful information, explanations of these settings:

    * server-id : this is an integer id helping to identify the server (must be unique in your replication farm!)
    * master-host : specifies the ip/hostname of the MySQL acting as master for the current server
    * master-user : specifies the user used to make the connection to the master
    * master-password : is the user’s password
    * master-port : specifies on which port the master is listening
    * log-bin : needed to start the binary logging process
    * binlog-do-db : specifies on which databases the binary logging must be active (only those databases will be in the binary log)
    * replicate-do-db : which database must be replicated by the server as slave.
    * auto_increment_increment : controls the increment between successive AUTO_INCREMENT values.
    * auto_increment_offset : determines the starting point for AUTO_INCREMENT column values.

    The last two options are needed to prevent key collisions when using AUTO_INCREMENT columns, otherwise multiple masters may attempt to use the same AUTO_INCREMENT value when inserting rows.

    optionally two extra options can be added :
    * show-slave-auth-info : Display slave usernames and passwords in the output of SHOW SLAVE HOSTS on the master server.
    * slave_compressed_protocol={0|1} : this option enable the use of compression for the slave/master protocol if both the slave and the master support it. The default is 0 (no compression). This accelerates the replication process

Leave a Reply