Skip to content

Chris' Laboratory

chrislee.kr – Personal blog as bookshelves

Menu
  • Home
  • Github
  • Contact Me
Menu

Vagrant MySQL Master-Slave Replication

Posted on 22/08/201629/08/2020 by Chris Lee
Read Time:2 Minute, 26 Second

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

  • Vagrant 1.8.1+: http://www.vagrantup.com/
  • VirtualBox: https://www.virtualbox.org/

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

Share

Facebook
Twitter
LinkedIn
Email

Related

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories

  • Databases (11)
    • MongoDB (4)
    • MS-SQL (1)
    • MySQL (6)
  • E-Commerce (8)
    • Magento (8)
  • Finance (2)
  • Frameworks (84)
    • Adobe Flex (1)
    • Angular (ngx) (3)
    • Codeigniter (6)
    • CSS (5)
    • Django (2)
    • Javascript (13)
    • Node.js (6)
    • PHP (17)
    • React Native (4)
    • React.js (1)
    • Sencha Touch (4)
    • Terraform (1)
    • Vue.js (1)
    • WordPress (4)
    • Yii2 (3)
  • General Documents (15)
  • Marketing (3)
  • Mobile Development (33)
    • Android (20)
    • iPhone (13)
  • Platforms (21)
    • Arduino (2)
    • Docker (5)
    • Google App Engine (5)
    • Raspberry Pi (5)
    • Samsung Smart TV (4)
  • Security (17)
  • Server (31)
    • Linux (13)
  • Tools (14)
    • SVN (7)
  • Uncategorized (2)

Search

Recent Posts

  • Bootstrapping Kubernetes cluster with kubeadm
  • Taint all resources in the one module
  • Alpine – Plugin caching_sha2_password could not be loaded
  • npm link with peerDependencies
  • How to setup Gitlab runner with KVM enabled

Recent Comments

  • Obayed on Binance Auto Trading Bot – Buy low/Sell high with stop loss limit/Trade multiple coins
  • Ari on How to install memcache.so/memcached.so for MAMP Pro (Mac)
  • Mida ali on Binance Auto Trading Bot – Buy low/Sell high with stop loss limit/Trade multiple coins
  • Chris Lee on How to install memcache.so/memcached.so for MAMP Pro (Mac)
  • Chris Lee on Setting Up A VPN Server On OSX 10.6

Tags

1 ajax amazon android android-addpart browser chrislee-kr codeigniter codeigniter-tcpdf com-apple-net-racoon CSS CSS history hack delpaigmail-com entity-addpart-double exception-printing-is-disabled-by-default-for-security-reasons ext-plugins-listpagingplugin ext-plugins-listpagingplugin-example f iphone javascript jquery-defaultchecked jquery-samsung-smart-tv listpagingplugin mac magento-exception-printing-is-disabled-by-default-for-security-reasons magento-sample-data-exception-printing-is-disabled-by-default-for-security-reasons nu-vot null-core-errors-confignotfound-config-mk9engine-ini php samsung-smart-tv-jquery samsung-smart-tv-sdk-ajax samsung-smart-tv-sdk-jquery samsung-tv-sdk samsung-tv-sdk-jquery samsung tv sencha-smart-tv sencha-touch-list-paging smart-tv-jquery sqlite subversion svn tcedook tcpdf-codeigniter uilinebreakmodecharacterwrap-is-deprecated unknown-column-link-area

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
© 2023 Chris' Laboratory | Powered by Minimalist Blog WordPress Theme