MySQL Database Connection from Host to Guest Virtual Machine

MySQL is a relational database management system which is popularly used in web applications and other light-weight software systems. In this article, we shall walk through the process of connecting to a MySQL database server running on a guest operating system inside a virtual machine.

Install MySQL Workbench on the host machine (Ubuntu 18.04 LTS):

sudo apt install mysql-workbench

Check the Firewall status on the guest virtual machine (Ubuntu Server 18.04 LTS) and verify if port 3306 (default port for MYSQL) is open for accepting connections to the database:

sudo ufw status

sudo ufw allow 3306

Change MySQL configuration on the guest vm:

cd /etc/mysql/mysql.conf.d

sudo vi mysqld.cnf

Comment the following line which allows incoming connections on port 3306 only from the same localhost on which mysql server is installed (guest vm):

#bind-address = 127.0.0.1

Restart the MySQL database service on the guest vm:

sudo /etc/init.d/mysql restart

Associated MySQL Commands:

sudo /etc/init.d/mysql start – Start the MySQL database.

sudo /etc/init.d/mysql stop – Stop the MySQL database.

Launch the MySQL Workbench application on the host machine:

Set up the database connection:

Test the database connection from Workbench.

Error:

Host ‘192.168.56.1’ is not allowed to connect to this MySQL server

Create a new database user which can connect to the database from any host or amend the root user’s grants to allow it to connect from any host.

CREATE USER ‘root’@’%’ IDENTIFIED WITH mysql_native_password AS ‘***’; GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ REQUIRE NONE WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

Test the database connection again from MySQL Workbench.