Connect database in another computer

Question

I cannot connect the database in another PC. I already disabled the firewall and followed the instruction in another forum.

Should I install software or third party software to connect the database in another PC? I am using XAMPP server.

Dim con As New MySqlConnection("server=192.xxx.x.xxx;port=3306;database=sampledatabase;uid=root")

Solution

If my understanding to your query is correct (you are unable to login as root to a MySQL/MariaDB database remotely as root - assuming this is a fresh installation) - to answer your question - No there is no need to install any additional software.

You do need to update some details on your MySQL/MariaDB installation (the options below need to be done on the PC where the MySQL/MariaDB is installed)

Option 1:
Create a new user with some privileges over the database that you care about (code sample as below - edit to your need)

CREATE USER 'someuser'@'%' IDENTIFIED BY 'somepassword';
GRANT USAGE ON *.* TO 'someuser'@'%' IDENTIFIED BY 'somepassword'
CREATE DATABASE IF NOT EXISTS 'someuserdb';
GRANT ALL PRIVILEGES ON 'someuserdb'.* TO 'someuser'@'%';

The above code does a few things:

1. Creating a new username called 'someuser' with the password of 'somepassword' and allowing someuser to login from anywhere ('someuser'@'%') - You can limit the remote IP / host login by adding the correct details - simply replace % with the IP or hostname of the connecting PC.
2. Granting usage power to someuser (ie. allowing the user to login)
3. Creating database (if not there) called someuserdb
4. Give someuser full privileges on someuserdb

Option 2:
Allow remote login for root username

By default, root has

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'..... 

So root account login is only allowed from localhost (the PC where the MariaDB/MySQL server is installed).

You can change that by:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'someIPorHostname.%'
IDENTIFIED BY 'some_characters'  
WITH GRANT OPTION;
FLUSH PRIVILEGES;

The idea is that you are changing it from 'root'@'localhost' to allowing root to login from some remote IP or hostname of your choice. At the end of changing that access, you need to flush privileges to make that access granting confirmed into the DB.

Note: There may be a good reason why root login is NOT allowed from remote location - so although this option is available, I'd recommend using option 1.

Credit to stackoverflow as most of my answer came from there.

Hope this helps.

Comments

[Darius]

Did you set the MySQL db to allow connection from other IP address? By default, root connection is only allowed from localhost.. and allowing root connecting from external is probably not a good idea... (I could be wrong here)

[JRG]

i dont understand but it seems you know my problem.. whats the right syntax to connect and i create new user in xampp mysql.. thank u

[Darius]

If I see your syntax, you are trying to connect to the remote PC MySQL instance as root? Usually root login is limited to itself. So if the MySQL is running on PC B, you can only login as root in PC B. You can remove that restriction... sorry I don't use mySQL enough to know the exact syntax. Just look up how to change root user restriction, or how to create other username that allow login from other IP address. (or other user can help)

[JRG]

oh i see... if i use sql server it still the same.. or much simple to setup? tell me or the link the instruction.. Tnx buddy

[fixer1234]

@Darius, several of us have cleaned up the grammar and formatting, but the the question remains really unclear as to what is being asked. You seem to have a handle on it. Can you edit the question to make it understandable? Thanks.

[Darius]

@fixer1234 I think JRG may be asking is why that line of code (that is seemingly correct way of creating a new MySQL connection) does not work - despite having the correct details (IP, port, db, username and maybe even password). My experience with installing fresh MySQL (which is included in the XAMPP package - under MariaDB) is that root access is limited to localhost login (ie. only the PC where MySQL is installed where you can login as root).

[JRG]

oh i see.. thank you, gonna try this one sir.. thank you again sir

[JRG]

by the way, in sql server how can i connect the server in other PC? can you recommend me a site that already discuss or just give me a tip sir? thanks again sir

[Darius]

I'm not sure what you are asking...

[JRG]

uhm nevermind sir @Darius, already solve the problem and sorry for the grammar and the paragraph.. Thank you again sir