Vagrant MySQL Master-Slave Replication

Vagrant MySQL Master-Slave Replication

Source Repo: https://github.com/chrisleekr/vagrant-mysql-master-slave-replication

Vagrantfile to spin up MySQL Master-Slave replication with persistent data storage

Note: This project is created for just practice. Not suitable for production use.

Prerequisites

Usage

$ git clone https://github.com/chrisleekr/vagrant-mysql-master-slave-replication.git
$ cd vagrant-mysql-master-slave-replication
$ vagrant up

After vagrant machines are running, you can connect MySQL to:

  • Master machine: 192.168.100.11
  • Slave machine: 192.168.100.12

After vagrant halt/suspend, need to run provisioning scripts again to synchronize MySQL from master to slave machine

$ vagrant halt
$ vagrant up && vagrant provision

If having the error message ‘The guest additions on this VM do not match the install version of VirtualBox!’, then run following command before vagrant up

$ vagrant plugin install vagrant-vbguest

Environments

  • VM Box: Ubuntu/prcise64
    • Master MySQL
    • Private IP: 192.168.100.11
    • Hostname: mysqlmaster
    • Database Name: sample
    • Database Username: root
    • Database Password: root
  • Slave MySQL
    • Private IP: 192.168.100.12
    • Hostname: mysqlslave
    • Database Name: sample
    • Database Username: root
    • Database Password: root

How it works

  1. MySQL slave machine will be launched first.
    • Vagrant provisioning script will update package list and upgrade system (Currently commented out. If need, uncomment it)
    • Set up timezone to Australia/Melbourne
    • Configure firewall to enable 22/3306 ports (may not need?)
    • Install MySQL server and client
    • Configure vagrant synced folder as persistent directory and move initial database file to persistent directory
    • Copy config/my-slave.cnf to /etc/mysql/conf.d/my_override.cnf, which will override default MySQL configurations
    • Create database and grant privileges to root user
    • Set up root user’s host to be accessible from any remote
    • Restart MySQL service
  2. MySQL master machine will be launched following.
    • Vagrant provisioning script will update package list and upgrade system (Currently commented out. If need, uncomment it)
    • Set up timezone to Australia/Melbourne
    • Configure firewall to enable 22/3306 ports (may not need?)
    • Install MySQL server and client
    • Configure vagrant synced folder as persistent directory and move initial database file to persistent directory
    • Copy config/my-master.cnf to /etc/mysql/conf.d/my_override.cnf, which will override default MySQL configurations
    • Create database and grant privileges to root user
    • Set up root user’s host to be accessible from any remote
    • Create replication user in MySQL master machine
    • Restart MySQL service
    • Install sshpass to access ssh to MySQL slave machine
    • Check slave machine is up and running and MySQL is configured by checking /var/log/setup_mysql
    • Dump database to SQL file from master machine
    • Get current log file and position from master machine
    • Log into MySQL slave machine and import dumped SQL file to sample database
    • Set slave machine to connect master machine for reading the master binary log
    • Create test transaction in master machine

Screenshots

Alt textAlt textAlt text