MySQL – Master/Slave Replication on Same VM Instance

master-slave-replicationMySQLreplication

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.

Best Answer

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
Related Question