Remote Access Mysql

bangms

Jun 20 2008, 09:57 PM

Is it possible to directly access my local database from my website?

For example, if somebody submits info on my website, the info they submitted would be transferred to my local database directly. Is that possible? And if so, how do I set that up?

The reason I don't want to upload my database to my subdomain is because it would be difficult to keep the website database and my local database in sync . . . or is there a way to do that?

Thanks for any replies.

Comment/Reply (w/o sign-up)

bangms

Jun 21 2008, 07:37 AM

OK, I did some looking up about remote accessing and I found out I could use SSH/Shell . . . something like that. If I'm not mistaken, the admins disabled this feature on free accounts due to hacking attempts, right? So what are my alternatives to accessing my local database from my site?

Comment/Reply (w/o sign-up)

rvalkass

Jun 21 2008, 09:08 AM

You can't access your Trap17 database from elsewhere, but I don't think there is anything stopping your Trap17 account from updating your local database.

You would need a PHP script that would go through your entire database, create a SQL command to copy it all, and then run that SQL on your local machine. To do that, you need to change one thing in the standard code to connect to the database. To connect to your database, change the server 'localhost' to 'your_ip:port'. Obviously you will need to find out your IP address, and what port your MySQL server is running on. You should then be able to run commands on your local database from your hosting account.

However, copying the entire database would be very slow and server intensive. What would be better would be to copy only changed rows. I have no idea what your database structure is, so I can't offer more advice apart from adding a new field to every table that stores the time the row was last updated. Then, only copy rows updated in the last day or whatever.

Comment/Reply (w/o sign-up)

bangms

Jun 21 2008, 05:51 PM

QUOTE(rvalkass @ Jun 21 2008, 02:08 AM)

To connect to your database, change the server 'localhost' to 'your_ip:port'. Obviously you will need to find out your IP address, and what port your MySQL server is running on. You should then be able to run commands on your local database from your hosting account.

However, copying the entire database would be very slow and server intensive. What would be better would be to copy only changed rows. I have no idea what your database structure is, so I can't offer more advice apart from adding a new field to every table that stores the time the row was last updated. Then, only copy rows updated in the last day or whatever.

Hm. That sounds like a good idea but it sounds like a lot of advanced scripting. Do you think it would just be easier to directly connect to my local database by the way you suggested - using 'my_ip:port'? It wouldn't be too hard to change the scripts I already have and that way, I wouldn't have to keep two databases in sync. Would that be an option?

Comment/Reply (w/o sign-up)

Galahad

Jun 21 2008, 06:24 PM

Yes, you can change all your scripts to point to your local MySQL server, and that way all the information would be immediately stored on your computer and in your local MySQL database...

You only need to make sure your computer is always accessible, if you have a firewall, open one port for MySQL server to listen to... Also, a good idea would be to subscribe to some free dynamic DNS service, like DynDNS, so you don't have to change your scripts whenever your IP changes...

Don't know your bandwidth, but if you're on 512K ADSL, your MySQL server could serve possibly around 10 requests at once, with some lagging, depending on how much data is being trasnfered...

Comment/Reply (w/o sign-up)

hitmanblood

Jun 23 2008, 07:24 PM

I would suggest that you simply use trap17 base and then use your personal computer and database for the backup or something similar.

And one more thing it is not that hard to write such script. You need some basci knowledge of SQL more or less and some basci knowledge of PHP in coordination with databases.

best regards

Comment/Reply (w/o sign-up)

bangms

Jun 23 2008, 11:02 PM

So I should upload my database and just direct all my php scripts to the database on trap17? But then do I have to make my own script to back up the files on my computer? The trap17 database and my local database have to be in immediate sync - meaning they have to be updated right away - not manually. Would I have to script that myself or could I find one somewhere else?

Comment/Reply (w/o sign-up)

rvalkass

Jun 24 2008, 08:57 AM

It depends on exactly what you want. Why do you need a perfect replica of the database on your local machine? If it is as a backup, then I suggest getting hold of a PHP script to make automatic backups for you every so often. If it is to work on the database, then it doesn't need to be up-to-the-minute. If it is to run queries on the data or something, then why can't you use one database on Trap17?

Comment/Reply (w/o sign-up)

bangms

Jun 24 2008, 09:37 AM

The reason I need the database on my local machine is because I'm running a private game server. The server uses the database stored on my local computer, unless I could direct it to access a database on Trap17 - which would slow the server down. I want the user to be able to play the game as soon as they register, but I guess it is not necessary that they play immediately. I'm thinking that directly linking my PHP scripts to my local database would be easiest as Galahad suggested. I already have a static IP and I already have an open port for MySQL. So is directly linking to my database the way to go (and safest)?

Comment/Reply (w/o sign-up)