Reply to: Too risky to modify a large MySQL database?

[Fred Bloggs]

You could always save a backup locally on the server, that way you don't need to download it.

Wow, I never knew you could do that! This is getting better all the time!

[Fred Bloggs]

Remember - this is CUK. We're here to help

[KevinS]

That as well.

Did I say how fliping amazing I am. Truely I'm the greatest.

And modest - let's not forget modest..

[Sockpuppet]

Ego

That as well.

Did I say how fliping amazing I am. Truely I'm the greatest.

[Sir_Edward_Matheson]

Ego

HTH

Bandwidth

[Ardesco]

You could always save a backup locally on the server, that way you don't need to download it.

[TheFaQQer]

Thank you. I'm a Mechanical Engineer not an IT person so I'm very cautious with stuff on the limits of my knowledge/capabilities. Thanks again.

Remember - this is CUK. We're here to help

[Fred Bloggs]

Get faster connection

Not an option I'm afraid. We've only had ADSL for 2.5 years where I live and 512kb down 256kb upload is the fastest we can have, I've checked.

HTH, and post again if you encounter any more difficulties - there's always another way to make things easy

Thank you. I'm a Mechanical Engineer not an IT person so I'm very cautious with stuff on the limits of my knowledge/capabilities. Thanks again.

[KevinS]

By my penis is still bigger.

Ego

HTH

[Sockpuppet]

2. Get faster connection - my ADSL 2 has got 1.3 Mbits upload.

By my penis is still bigger.

[NickFitz]

Thanks for the comments. The problem is that with such a huge database file it is impossible to manage it across a 512kb ADSL connection. I think I will investigate asking the host to take a database back up for us then if it gets hosed they can restore it for us. One of the problems is that phpMySQLAdmin times out when handling large files across the web on a 512kb connection. Ever tried uploading >900 mb of data across a piece of wet string?

Thanks again.

As I mentioned, phpMySQLAdmin allows one to choose a compression scheme when downloading a backup, and as the backup is a text file full of SQL, it compresses very well - I just downloaded a 100+Mb database gzipped, and the actual file size is 8.6Mb.

As phpMySQLAdmin will also automagically recognise the compressed version of the database when re-uploaded, you may find that this meets your needs.

If that's not going to work for you because the backup is still too large, you can always decompress the backup and then edit it into a number of separate files, and insert them one by one.

Yes, it's laborious, but then again nobody ever said this stuff was easy (except Bill Gates and Steve Jobs, and they were both talking about something else, mainly involving sharing pictures of kittens playing with balls of wool).

HTH, and post again if you encounter any more difficulties - there's always another way to make things easy

[AtW]

Ever tried uploading >900 mb of data across a piece of wet string?

1. Use compression - that can reduce size by 10-20 times.

2. Get faster connection - my ADSL 2 has got 1.3 Mbits upload.

[Fred Bloggs]

Thanks for the comments. The problem is that with such a huge database file it is impossible to manage it across a 512kb ADSL connection. I think I will investigate asking the host to take a database back up for us then if it gets hosed they can restore it for us. One of the problems is that phpMySQLAdmin times out when handling large files across the web on a 512kb connection. Ever tried uploading >900 mb of data across a piece of wet string?

Thanks again.

[NickFitz]

How do you administer the database? If it's via phpMyQLAdmin(which is often the case) then go to the "Export" tab. If the version you're using is less than 5.x.x, select the "SQL compatability mode" as appropriate (e.g. "MYSQL4)". At the bottom, tick the "Save as file" checkbox, select one of the compression schemes (otherwise it'll take ages to download) and hit the "Go" button. A zipped copy of the database will be downloaded to your machine; expand that and you'll have the .sql file necessary to restore the database if necessary.

If you really want to be cautious, install the same (or equivalent) version of mySQL locally and start it up. (Their numbering scheme is along the lines of 5.0.42; make sure you install a version where the first two numbers are the same.) Then download and install the mySQL Admin Tools, and use the mySQL Administrator to "Restore" the downloaded SQL into your local database.

Then apply the patch to your local copy, and check (using the mySQL Query Browser) that your data hasn't been hosed.

If all that goes well then cross your fingers and apply the patch to the live server. Once again, check all is well; if there's a problem, restore by uploading the compressed (zipped, gzipped or bzipped) file you downloaded at the start, using the "Import" tab of phpMySQLAdmin.

Then go down the pub to celebrate

[Diver]

Wot he said