How to access Mysql installed in windows host from ubuntu guest?

Question

I have Windows 7 host and Ubuntu as a guest OS. I am using VMware workstation to run guests.

I have installed mysql on host i.e. Windows 7 OS. I want to access this mysql from my ubuntu guest os. I have no clue how to do that.

Please help me to access mysql from this guest. Basically I am trying to develop one sample application using DJango in my ubuntu guest OS. For that I need mysql. I have very slow internet connection so I cant download mysql for ubuntu. I dont have python installed in my host OS.

Answer 1

You need to look over the table mysql.user

Usually you can run this query to see the usernames, host addresses, and MD5-encrypted passwords.

SELECT user,host,password FROM mysql.user;

EXAMPLE #1 : let's create a user called 'myclient' who will connect to mysql from an Ubuntu server whose IP address is '123.50.89.191' and who can fully access and manipulate everything in the database 'mydata'. The password will be 'permission';

GRANT ALL PRIVILEGES on mydata.*
TO 'myclient'@'123.50.89.191'
IDENTIFIED BY 'permission';

EXAMPLE #2 : let's create a user called 'myreadclient' who will connect to mysql from an Ubuntu server whose IP address is '123.50.89.191' and who can only INSERT and SELECT everything in the database 'mydata'. The password will be 'readpermission';

GRANT INSERT,SELECT on mydata.*
TO 'myreadclient'@'123.50.89.191'
IDENTIFIED BY 'readpermission';

For more on how to create users with specific privileges, please follow this URL.

Give it a Try !!!

CAVEAT

It is always better to set up password in MD5 format rather than plain text passwords.

Example

mysql> SET @X=PASSWORD('permission');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @X;
+-------------------------------------------+
| @X                                        |
+-------------------------------------------+
| *1D6447E2F5B7AFD0E27D8E6CCA53099BE980803C |
+-------------------------------------------+
1 row in set (0.00 sec)

Now, set the password for the first example like this:

GRANT ALL PRIVILEGES on mydata.*
TO 'myclient'@'123.50.89.191'
IDENTIFIED BY PASSWORD @X;

Here is the second example using MD5 format

mysql> SET @X=PASSWORD('readpermission');
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @X;
+-------------------------------------------+
| @X                                        |
+-------------------------------------------+
| *22DA1CEDDBA3B75FD193775AC69D9184105F0BE0 |
+-------------------------------------------+
1 row in set (0.00 sec)

GRANT INSERT,SELECT on mydata.*
TO 'myreadclient'@'123.50.89.191'
IDENTIFIED BY PASSWORD @X;

Doing this will protect against recording the plain text password in binary logs or any file that is recording mysql client sessions.