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
No comments:
Post a Comment