5

I'm fairly new to databases so I hope this isn't a silly question!

Is it possible to set up master/slave replication on the SAME server?

I understand there are not any advantages to this in terms of hardware utilisation/recovery purposes, but a set up like this would help test a piece of software I am developing.

6

It's possible, why not. You have to run two instances of MySQL. One will be a master, second - a slave.

Check this for detailed instructions https://dev.mysql.com/doc/refman/5.5/en/multiple-servers.html

UPDATE:

Usually I use Vagrant for this. Here's a piece of code to setup a master + slave.

Vagrantfile:

Vagrant.configure(2) do |config|
  config.vm.define "db01" do |db01|
    db01.vm.box = "bento/centos-6.7"
    db01.vm.hostname = "db01"
    db01.vm.provision :shell, path: "bootstrap-master.sh"
    db01.vm.network "private_network", ip: "192.168.50.101"
    db01.vm.synced_folder "../", "/home/vagrant/src/twindb"
  end
  config.vm.define "db02" do |db02|
    db02.vm.box = "bento/centos-6.7"
    db02.vm.hostname = "db02"
    db02.vm.provision :shell, path: "bootstrap-slave.sh"
    db02.vm.network "private_network", ip: "192.168.50.102"
    db02.vm.synced_folder "../", "/home/vagrant/src/twindb"
  end

bootstrap-master.sh

#!/usr/bin/env bash

set -e

hostname="`hostname`"

yum -y install https://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm

packages="
mysql-community-server"

yum -y install ${packages}

chkconfig mysqld on

function wait_for_mysql() {
    # wait till mysql starts
    timeout=300
    mysql_started="NO"
    while [ ${timeout} -gt 0 ]
    do
        if ! [ "`mysql -e 'SELECT 1'`" = "1" ]
        then
            echo "SUCCESS"
            break
        fi
        sleep 1
        let timeout=$timeout-1
    done
}
cat <<EOF > /etc/my.cnf
[mysqld]
server_id=101
log_bin=mysqld-bin
EOF

/etc/init.d/mysqld start

if [ "`wait_for_mysql`" = "SUCCESS" ]
then
    mysql -u root -e "RESET MASTER"
    mysql -u root -e "CREATE USER 'replication'@'%' IDENTIFIED BY 'bigs3cret'"
    mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%'"
    mysql -u root -e "GRANT ALL PRIVILEGES ON *.* TO root@'%'";
else
    echo "MySQL failed to start"
    exit -1
fi

bootstrap-slave.sh

#!/usr/bin/env bash

set -e

hostname="`hostname`"

yum -y install https://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm

packages="
mysql-community-server"

yum -y install ${packages}

chkconfig mysqld on

function wait_for_mysql() {
    # wait till mysql starts
    timeout=300
    mysql_started="NO"
    while [ ${timeout} -gt 0 ]
    do
        if ! [ "`mysql -e 'SELECT 1'`" = "1" ]
        then
            echo "SUCCESS"
            break
        fi
        sleep 1
        let timeout=$timeout-1
    done
}
cat <<EOF > /etc/my.cnf
[mysqld]
server_id=102
log_bin=mysqld-bin
EOF

/etc/init.d/mysqld start

if [ "`wait_for_mysql`" = "SUCCESS" ]
then
    mysql -u root -e "RESET MASTER"
    mysql -u root -e "CHANGE MASTER TO
        MASTER_HOST='192.168.50.101',
        MASTER_USER='replication',
        MASTER_PASSWORD='bigs3cret',
        MASTER_LOG_FILE='mysqld-bin.000001',
        MASTER_LOG_POS=120"
    mysql -u root -e "START SLAVE"
else
    echo "MySQL failed to start"
    exit -1
fi
0

The best way to setup replication for testing purpose on a local machine is to use MySQL Sandbox very easy to setup and manage multiple Master-Slave and different replication methods.

http://mysqlsandbox.net/

0

Developers often work on only one machine, and have their whole development environment on that machine. Testing database replication before deploying changes in this kind of a development environment can be a challenging task. In this article, Toptal engineer Ivan Bojovic guides us through a step-by-step tutorial on how to implement MySQL Master-Slave Replication on the Same Machine.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.