Access MySQL database in Ubuntu VM from Macbook

It is handy to have a MySQL database VM.   I can use it to build the demo data in the database and can carry it on my laptop.  I can even share it between the Window laptop and Macbook.

I am using Ubuntu linux and Virtualbox.

Here is how to enable MySQL to be accessible from the host laptop:

1. Install MySQL

2. Access MySQL locally
mysql -u<user> -p<password>

3. Add the host-only network into the VM

4. Use ifconfig to get the IP address of the VM
The value must be something like 192.168.*.*.


5. This is the most important step - Edit the my.cnf file

This file is stored in /etc/my.cnf
It was not created as part of installation.  I have to create the file and added these two lines.



To override the bind-address=<IP of the VM>

6.  Restart MySQL

# /etc/init.d/mysql restart

Here are other ways:

service mysql stop
service mysql start
service mysql restart

7. Verify if the MySQL (port 3306) is running under 127.0.0.1.
If yes, it means that the above is not working.



8. Go over all the cnf files and find the bind-address

cd /etc
find . -name '*.cnf' -exec grep -l bind-address {} \;



9.  Comment out the line that set the bind-address to 127.0.0.1

10. Restart the server as #6 and verify  the address as #7

11.  Create a user that will access the database remotely.

CREATE USER <username> IDENTIFIED BY '<password>;

12.  Create the database that will be shared with remote users

CREATE DATABASE <dbname>;


13. Grant access to the user from the remote host

GRANT ALL ON <dbname>.* TO <username>.'%';

14.  We can verify the above by issuing these SQL:

mysql -u<rootuser> -p<password>
use mysql
select * from db;
select * from user;

16. Edit the /private/etc/hosts file
To give the VM IP a domain name.


15. Test from my laptop (the host machine), which the Virtualbox is running on.

nz -z -w1 <VM domain name> 3306

echo X |telnet -e X <ip address from #4> 3306

16. Connect MySQL database from SQLDeveloper