Remote mySQL access

[martindell]

I've got a mySQL database running on a dedicated server and I'm accessing it from a desktop computer via an SSH tunnel.

Everything seems to work more or less OK except that when I do a large dB query. it actually stops the server from serving web pages to other users. The server doesn't crash, nor does it seem hugely loaded but as soon as I kill the SSH session, it comes right back.

I'm pulling my hair out about where to start to diagnose this and wonder if anyone has seen something similar? Alternatively, is there a better way of connecting to a remote mySQL db? Thanks in advance for your help.

Martin

[roscoepico]

I've used both SQL Query Analyzer and phpMyAdmin to remotely access databases. Both work well and have never seen them cause a web page to not load.

[txbakers]

I turned off all internet access to the mySQL db and remote into the server then launch the mySQL client to run queries.

It's a little more inconvenient for me, but MUCH safer and secure for my clients.

[martindell]

Thanks for the replies - it got me thinking of another approach to this - of synchronising the remote database with a local copy and running from the local copy - we can live with the data being a couple of hours old and we have the benefit of a fairly recent backup of the databse if the worst happens. Have been testing a synchronisation tool snipped specifics and so far so good.

Thanks for your help
Martin

[edited by: DaveAtIFG at 12:49 pm (utc) on May 27, 2004]
[edit reason] URL removed [/edit]

[martindell]

Rsync seemed like a good idea but eventually caused the same server crash - I think I know why although not how to resolve it.

Here's the set up.
Apache server serving a majority of it's pages from a mysql database - reasonably busy with normal web-serving traffic.

My desktop PC using MySQL ODBC driver accessing same (remote) mysql database via SSH tunnel.

Connection works fine but when I try and synchronise or copy the remote database to my desktop, the server gets slower and slower and eventually reboots itself.

What I think is happening is that my ODBC driver gets exclusive access to the remote database when it's doing it's synchronise or copy and in the meantime, all the web generated mysql requests get queued up.

Because the process involves copying a large database (takes 30 minutes or so) - their is enough 'normal' mysql requests joining the queue to cause the server to reboot itself.

So, finally to the question - how can I get my ODBC driver to take it's turn with all of the other mysql requests? Alternatively, maybe I'm missing something and this isn't what's happening at all!

[Hanu]

Martin, you seem to know what you are talking about so I'm just throwing in a couple of ideas. And BTW, it's not a bandwidth issue ;-).

Use a different client. For example, the mysql.exe command line tool. It should be part of the binary distribution available at the mysql site. You generate an SQL file with INSERTS and UPDATES and you feed this into mysql.exe.

Find out about LOW_PRIORITY clause for UPDATE and INSERT.

You can't use rsync unless you shut the server down for the duration of the sync, because it directly accesses the database files instead of going thru the MySQL server daemon.

Find out about MySQL replication in Chapter 6 of the MySQL manual. I have never used it but I know it exists.

-- Hannes

[martindell]

Thanks, that gave me an idea - I used the same dB tool but copied instead of syncd - took a LONG time but it worked and apache carried on serving pages from the same database, so it looks like it's something to do with the type of query being performed. I can live with working from a database copy once a day so it looks like I'll skirt around the issue that way.

Thanks for your help.
Martin