Setting up SQL learning environment with Vagrant Linux VM

Environment: Windows 10, 16GB RAM, i5 CPU

• Install Virtual Box
• Install Vagrant
• Install MySQL Workbench or other SQL clients

Basic Setup

1. Make a directory to store virtual machine
2. cd into the directory
3. Save file below as “Vagrantfile” in the directory (change hostname etc as needed)

Vagrant.configure("2") do |config|
    config.vm.box = "debian/buster64"
    config.vm.hostname = "dbworks-01"
    config.vm.define "dbworks-01"
    config.vm.network "private_network", ip: "192.168.33.10"
    config.vm.provider "virtualbox" do |vb|
      vb.cpus = 4
      vb.memory = "2048"
    end
    config.vm.provision "shell", inline: <<-SHELL
      apt-get update
      apt-get install -y curl git cmake vim  # ...
    SHELL
    config.vm.provision "docker" do |d|
      d.run "nginx"
    end
  end

4. While in the directory with Vagrant file, vagrant up . Wait for VM to start.

5. Open another terminal window, cd into the directory and vagrant ssh

6. sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys B7B3B788A8D3785C

7. Follow through https://tecadmin.net/install-mysql-on-debian-10-buster/

8. Follow through https://gist.github.com/rmatil/8d21620c11039a442964 (may not need to do “Change privileges of the MySQL user to allow connection attempts from any host”)

Connecting via MySQL WorkBench

SSH Key File can be found with vagrant ssh-config and finding the location of private key.

Setting Up Administration Tab

1. Click on the spanner icon next to INSTANCE menu
2. In Remote Management Tab, select SSH login based management and following credentials — Hostname: 127.0.0.1, Port: 2222, Username: vagrant. Check “Authenticate Using SSH Key” then add SSH key path (the same as private key from vagrant ssh-config).
3. In System Profile, set following configurations — System Type: Linux; Installation Type: Custom; Configuration File: /etc/mysql/my.cnf; Configuration File Selection: mysqld; Start MySQL: systemctl start mysqld.service; Stop MySQL: systemctl stop mysqld.service

You should be able to access Options File submenu without warning popups.

Trying to access Server Logs submenu may give permission errors. To fix “error creating log folder sftp server permission denied” error which comes from lack of permissions for the root user to access mysql folder and the error.log file inside it…

1. On the VM, move to /var/log/ and see if you can cd into mysql. ls -la to confirm the permissions.
2. If you can’t, sudo chmod 755 and sudo chown root:root the folder.
3. cd into folder and do the same for error.log

Other tips

To copy SQL dump files into SQL server living on VM…

scp -i /Volumes/dailystorage/program_analysis_VM/.vagrant/machines/default/virtualbox/private_key demo.zip vagrant@private_ip_address_your_remote_machine:/home/vagrant