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.
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
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.
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.
