Home

How to create MySQL master-slave/master-slave chain.

Posted June 29th, 2009 by gnulinux

Hi all, In this article I will explain how to create master-slave/master-slave chaing using mysql database server. As we know that complex data storage requirements are growing day-by-day and we need to make sure that information is available from multiple place to simplify access requirements. E.g Marketing department, billig and accoutns department, customer support etc might need to access data from database server while performing there day-to-day work. We can not rely on 1 database server for such scenario. In most of the cases most of them will need read-only copy of data. The best way is to create couple of SLAVE / Secondary database servers which are ready-only and available to those users. Also it is good to take daily backups from read-only copy (slave) as the backup process may overload the server. If you try to take backups from your primary/master mysql server then you will probably see performance issues with it.

First of all we will install mysql database server and required packages. Also we will configure it to act as Master server. I will assume that you have already install your favourite gnu/linux operating system. We will use mysql-server-5.0.45 and mysql-5.0.45 packages. once you install these two packages, you are ready to configure master mysql server.


    yum install mysql mysql-server

now initialize your mysql database server by starting it first time. #/etc/init.d/mysql start. You will able to see all your database related files and directories in /var/lib/mysql directory as this is default location where mysql stores data files, index files and binary logs. One can change it by changing options in configuration file. Default configuration file for mysql database is /etc/my.cnf. In my mysql master / primary server this configuration file is as below.


    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Default to using old password format for compatibility with mysql 3.x
    # clients (those using the mysqlclient10 compatibility package).
    old_passwords=1

    server-id=1
    log-bin=/var/lib/mysql/mysqld-bin

    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    relay-log=/var/log/mysql-relay-bin

In this configuration files, Important parameters for mysql as master server is
(i) server-id : This must be a unique id to identify each mysql server seperately. each master and slave will have unique id for this parameter.
(ii) log-bin : When started with the --log-bin[=base_name] option, mysqld writes a log file containing all SQL commands that update data. If no base_name value is given, the default name is the name of the host machine followed by -bin. If the basename is given, but not as an absolute pathname, the server writes the file in the data directory. It is recommended that you specify a basename.

Second thing is now to prepare your slave server. Install it exactly in same way as you have installed your mysql master server. after you are ready, copyt following lines OR replyace your my.cnf on slave with following contents.
NOTE: Make sure you change server-id for your slave server as mentioned below. Also we don't need log-bin again here but we are specifying it because if we want to use our slave server as master also to replicate data to third server in chaing then it is necessary.


    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Default to using old password format for compatibility with mysql 3.x
    # clients (those using the mysqlclient10 compatibility package).
    old_passwords=1

    server-id=2
    log-bin=/var/lib/mysql/mysqld-bin

    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    relay-log=/var/log/mysql-relay-bin

Now we need to create a user on master which has to have replication permissions from your slave system.

    mysql> grant replication slave on *.* to myslaveusr@'10.10.10.101' identified by 'MYSLVPWD';

If you are setting up fresh copy of master and slave server the you will not need to perform data dump from master to slave. but if your master is/was already in use then you need to take latest copy of database(s) from master and dump it on slave.

    #mysqldump -u root --all-databases --single-transaction --master-data=1 > masterdumpforslave.sql

Next step is to imprt dump on slave to setup a copy of your database.

    #mysql < masterdump.sql

After restore completes successfully, login to mysql on slave system and you need to tell which is master server for your slave.

    mysql> CHANGE MASTER TO MASTER_HOST='10.10.10.100', MASTER_USER='myslaveusr', MASTER_PASSWORD='MYSLVPWD';

Now last step is to start slave process.

    mysql> start slave;

and then check for your slave status.

    mysql> show slave status\G;
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 10.10.10.100
    Master_User: myslaveusr
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysqld-bin.000001
    Read_Master_Log_Pos: 270
    Relay_Log_File: mysqld-relay-bin.0001
    Relay_Log_Pos: 236
    Relay_Master_Log_File: mysqld-bin.000001
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Replicate_Do_DB:
    Replicate_Ignore_DB:
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
    Last_Errno: 0
    Last_Error:
    Skip_Counter: 0
    Exec_Master_Log_Pos: 270
    Relay_Log_Space: 236
    Until_Condition: None
    Until_Log_File:
    Until_Log_Pos: 0
    Master_SSL_Allowed: No
    Master_SSL_CA_File:
    Master_SSL_CA_Path:
    Master_SSL_Cert:
    Master_SSL_Cipher:
    Master_SSL_Key:
    Seconds_Behind_Master: 0

Back to top