Remote MySQL

[visser]

so ive got multiple computers in my possession, and now that school is out i have had some random things ive wanted to do, just to do them.

The first one was being able to remotely connect to a mysql database from one computer to another. The computer that im using to run MySQL is running Ubuntu Server edition. (its teh good shit!) The computer that im using to connect is running windoze. so here is how i did it! (this isnt the only way to do it, i just found this to be the fastest way to do it.)




Remotely connecting to a MySQL database may be effective if you are getting a lot of queries made by a lot of different people at one time. You will probably hardly ever need to do this, i did it just to say that i did to be fully honest. lol

so lets get to work.
You can go about this in two ways.

the first is to install mysql database and then use the MySQL login thingy that comes standard with ubuntu. i did not like this way, command line can be a pain in the rear, but this way is doable.

the second (the way i did it) was to simply install phpmyadmin and use that to run all of our queries. This makes it easier because we can use it to create databases, restrict users to certain databases and all of the other fun stuff.

so first install all of the crap youll need:

Code: Select all

sudo apt-get install mysql-server
sudo apt-get install apache2
sudo apt-get install php5
sudo apt-get install php5-mysql
sudo apt-get install phpmyadmin

that will install all of the crap, php mysql apache phpmyadmin. pretty easy, but we arnt ready yet. you have to edit the apache config file to allow phpmyadmin to be used.
so run the following command:

Code: Select all

sudo nano /etc/apache2/apache2.conf

now you need to add the following line somewhere inside that file:

Code: Select all

Include /etc/phpmyadmin/apache.conf

save and exit.

now going from another computer on the network to:

(the computer's LAN ip adress that you jsut installed phpmyadmin on)/phpmyadmin

youll be asked to sign in to phpmyadmin. pretty easy stuff so far.

from withing phpmyadmin you can create a database or two. everything as you see fit.
for example i created a database named visser1.
now to allow remote acess to that database from my other computer i clicked on the tab in phpmyadmin that allows me to run MySQL queries. and i ran the following query:

Code: Select all

GRANT ALL ON visser1.* TO root@''192.168.1.102' IDENTIFIED BY 'PASSWORD';

where:
192.168.0.102 is the LAN ip of the computer that is conecting remotely.
PASSWORD is the password
root is the user name of the remote connection.

now that we have set it all up we need to see if we can connect from the other computer.

because my other computer was running windoze i installed a program called navicat. it allows me to manage my databases and the such and it can come in handy!
so download and install it and create a new conection.
rather than 'localhost' use the LAN ip of the computer with the mysql database set up on it. type in th username and password that you set up then hit test conection. it will tell you if it worked or if it didnt. if it did then hit connect and boom. your done!

now if you were running a script from that computer and wanted to conect to the other computers database rather than typing in local host you would type in the LAN ip.

so thats it, your all set up. fun stuff.

[computathug]

Well done visser, nice post and easily understandable for people to test if they have not done this before. I would also just add to remember to install the updates after it is all installed.

Well done buddy