Normally, there's an ad running in this spot, but you're using an ad blocker. To help support our blog, which provides free tutorials for everybody, we just ask that you whitelist or follow us on facebook, twitter, or subscribe using the form to the leftabove. Thank you!

    Manually Installing a MySQL Database on a Remote Server

    By the end of this tutorial, you should be comfortable with accessing and installing MySQL on a remote VM. Furthermore, the VM is interchangeable with any remote server, which means after you SSH in, all of the remaining steps are identical.

    Fabric DB Scripts VM Pack 1

    Purchase the entire collection of Fabric DB Scripts at the Codebox Systems Shop and auto-provision MongoDB, MySQL, and PostgreSQL in seconds.

    Shop now

    System requirements

    • A 32-bit or 64-bit computer with at least 8GB of RAM. 16GB or more is recommended.
    • At least 50GB of free disk space.

    Access the VM

    Now, that we have our Vagrant VM running, we can SSH into it like any other remote server we have SSH access to.

          # Print the VM's IP address to the terminal
          vagrant ssh -c "ip address show eth1 | grep 'inet ' | sed -e 's/^.*inet //' -e 's/\/.*$//'"
          # SSH in (password is "vagrant")
          ssh vagrant@MY.VM.IP.ADDRESS

    An alternative way to SSH in to the Vagrant VM would be the vagrant ssh command. But I recommend using the first method because it's closer to how you would log into a remote server in the cloud.

    Install MySQL

    Now we can perform the MySQL installation. Follow any onscreen prompts and enter a new MySQL root password. You will be using this same password to access the DB, later on.

          sudo apt-get install mysql-server

    Update the MySQL Configuration

    You'll want to comment out the bind-address and skip-external-locking lines for now. This is only to make testing easier.

          # Update the my.cnf and save your settings
          sudo nano /etc/mysql/my.cnf
          # Restart mysql
          sudo service mysql restart


    Before deploying to production, you'll want to uncomment the bind-address and skip-external-locking lines and add in the IP addresses any authorized servers that need to access the DB.

    Create a MySQL User and Grant Permissions

    We're now going to create a new non-root user that will be able to connect to the MySQL database remotely.

          # Connect to MySQL and enter your root password
          mysql -uroot -p
          # Run MySQL commands
          CREATE USER 'myuser'@'%' IDENTIFIED BY 'myp4ssw0rd';
          GRANT ALL ON *.* TO 'myuser' IDENTIFIED BY 'myp4ssw0rd';

    Access MySQL from Host

    First exit the VM


    Now Install mysql-client on the host.

            sudo apt-get install mysql-client
            # Install homebrew
            ruby -e "$(curl -fsSL"
            # Install MySQL
            brew install mysql

    And access MySQL.

          mysql -hMY.VM.IP.ADDRESS -umyuser -p


    If everything went well, you should now be able to connect to your virtual machine's MySQL database from the host machine. If at any point, you want to start over from scratch, run vagrant destroy from the same directory as the Vagrantfile, and the virtual machine will be deleted.

    Did you like this tutorial? Help us pay for server costs by following us on Facebook, Twitter, and subscribing below, where you'll get post notifications, training webinar invites, and free bundles.