[Help] Using MySQL in VPS

['LinKin]

Hello,

Guys I need to have a MySQL database which accept EXTERNAL connections.

I was told I could do this because I've a VPS. But the thing is, I don't know how to use it!

I've installed mysql client & server. (Ubuntu 12.04 64bits).

And I've installed succesfully, but what now? How to create a database? How to change the settings so that it accepts external connections?

Can anyone be so kind and help me out with this?

Thanks,

Linkin.

[bradio10]

What I did was install phpmyadmin onto my VPS so that it's much easier to add databases and tables and it's also easier to manage them.

MySQL also has console commands too which you could find on the internet which include making tables, inserting data into them, etc.

Also, have to forwarded the ports for MySQL?

[MTASAHOST]

Hello,

How to create a database? How to change the settings so that it accepts external connections?

Can anyone be so kind and help me out with this?

Thanks,

Linkin.

install phpmyadmin , apt-get install phpmyadmin ,

edit your php.ini change 127.0.0.1 to vps ip example

before

bind-address = 127.0.0.1

after

bind-address = 172.87.13.12

-------------------------------------

commands:

1- mysql

2- GRANT ALL ON databasename.* TO mysqlusername@'%' IDENTIFIED BY 'PASSWORD';

-------------------------------------

['LinKin]

Also, have to forwarded the ports for MySQL?

No I haven't.

-----

I was getting this error: http://s7.postimg.org/s5vwgckvf/errir.png

So, I tried the options: Abort, Retry, Retry (skipping questions) and ignore.

But with every option I was getting back to this error, it was an endless cycle.. I didn't know how to quit this so I had to close the program (I use PuTTy).

Then, I readed something telling me that I had to have LAMP installed, so I followed the stepts to install it and it was a sucess.

Then, when I typed

sudo apt-get purge phpmyadmin 

to uninstall PhpMyAdmin it showed this error:

E: Could not get lock /var/lib/dpkg/lock - open (11: Resource temporarily unavailable) 
E: Unable to lock the administration directory (/var/lib/dpkg/), is another process using it? 

Then, I killed the proccess with apt word with this:

ps aux | grep apt 
kill -9 processnumber 

And the processes were stopped.

Then I tried back:

sudo apt-get purge phpmyadmin 

But still getting the error:

E: Could not get lock /var/lib/dpkg/lock - open (11: Resource temporarily unavailable) 
E: Unable to lock the administration directory (/var/lib/dpkg/), is another process using it? 

This is the task manager (I deleted the processes that I know have nothing to do with this trouble)

http://s23.postimg.org/jinp5y1sr/TOP.png

[bradio10]

I am not sure about these errors.

All I did was follow this tutorial: https://www.digitalocean.com/community/ ... -on-ubuntu

Then once I finished that, I installed phpmyadmin with this tutorial: https://www.digitalocean.com/community/ ... untu-12-04

If you wanna know how to port forward your MySQL ports, use this command:

iptables -I INPUT -p tcp --dport 3306 --syn -j ACCEPT

I did all this and it worked out for me. Hope it does for you.

['LinKin]

Yes, I installed LAMP with that tutorial too.

But it's just that before I was installed it, I had tried to install phpmyadmin with apt-get install phpmyadmin.

I'm still getting that error... I've asked my provider, let's see how it goes.

iptables -I INPUT -p tcp --dport 3306 --syn -j ACCEPT

Must I replace something in this code? can you explain it a little bit more?

Thanks!

[bradio10]

Yes, I installed LAMP with that tutorial too.

But it's just that before I was installed it, I had tried to install phpmyadmin with apt-get install phpmyadmin.

I'm still getting that error... I've asked my provider, let's see how it goes.

iptables -I INPUT -p tcp --dport 3306 --syn -j ACCEPT

Must I replace something in this code? can you explain it a little bit more?

Thanks!

If it's still giving you the error after installing LAMP, then I recommend just uninstalling phpmyadmin and then re-installing it. ANd, have you installed php and everything that is in the LAMp tutorial I gave above? Because you also need all of those installed too.

iptables -I INPUT -p tcp --dport 3306 --syn -j ACCEPT

No, I put the port in for you which is the 3306 in the line above.

This it to forward the tcp port which is 3306. You need to forward this port so you can use mysql externally.

If you want to ford a udp port, use this code below and just change the 3306 to whatever port you need forwarding.

iptables -I INPUT -p udp --dport 3306 -j ACCEPT

If you don't know what tcp and udp are, search them on google and you will find an explanation.

['LinKin]

The problem is, I cannot uninstall the phpmyadmin because of this error:

E: Could not get lock /var/lib/dpkg/lock - open (11: Resource temporarily unavailable)

E: Unable to lock the administration directory (/var/lib/dpkg/), is another process using it?

I've googled this a lot. And I've found a lot of answers saying to remove the lock. But by removing the lock you can end up with a broken system, corrupt files, and some other BAD stuff..

I don't want to risk like this!

['LinKin]

Ok, I think I solved the lock problem

But now,

I'm stuck in this line

sudo nano /etc/apache2/apache2.conf

from the tutorial: https://www.digitalocean.com/community/ ... untu-12-04

It said nano: command not found. Then I did sudo apt-get install nano and did the proccess.

Then I tried again, and it openned like an editor.. But now, the next step it asks is to write:

Include /etc/phpmyadmin/apache.conf

But where do I type this? Inside the thing that 'nano' command opens?

I tried to do it outside, and it said

-bash: Include: command not found

Eh...

EDIT: Nevermind! I 'skipped' these 2 lines, restarted apache and it's working....

[bradio10]

Ok, I think I solved the lock problem

But now,

I'm stuck in this line

sudo nano /etc/apache2/apache2.conf

from the tutorial: https://www.digitalocean.com/community/ ... untu-12-04

It said nano: command not found. Then I did sudo apt-get install nano and did the proccess.

Then I tried again, and it openned like an editor.. But now, the next step it asks is to write:

Include /etc/phpmyadmin/apache.conf

But where do I type this? Inside the thing that 'nano' command opens?

I tried to do it outside, and it said

-bash: Include: command not found

Eh...

EDIT: Nevermind! I 'skipped' these 2 lines, restarted apache and it's working....

Nice to hear you got it working.

You add the Include thingo to the file you opened in nano.

So just go to the bottom of the file and just type it in there.

['LinKin]

Nice. I got it working. Accepting external connections, etc..

[Blokker_1999]

A few notes:

- if you have no experience in maintaining your own server, practice in a virtual machine at home before taking to the internet

- there is a reason why MySQL does not allow remote connections by default. Use them only if absolutly necessary.

- do not use a GRANT ALL on a database unless it is absolutly necessary. Use user permissions with care. Only give them rights they should have, nothing more. And do not accept connections from % unless you have no other choice (dynamic IPs can by a PITA), if you are connecting from another dedicated server, limit the user account to that IP.