Too risky to modify a large MySQL database?

[Fred Bloggs]

I have a passing interest in a website based on a phpnuke PHP script and a MySQL database. The database is (for an amateur website) a whopping >900 megabytes and growing daily. If ever the database was to crash or get corrupted there is every chance that the website would be completely screwed. But........ The website needs an urgent security update applying that requires a script to be run in order to add about 9 more tables to the DB. I've been asked what I think about the risk in doing it. What do the professionals out there think the chances are of such a SQL script screwing the DB please? Thanks.

[Sockpuppet]

I'd say about 0% considering that its probably been tested to death.

Remember backups are your friend. Just backup all of the mysql data files.

Job done.

[Diver]

Wot he said

[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

[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.

[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.

[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

[Sockpuppet]

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

By my penis is still bigger.

[KevinS]

By my penis is still bigger.

Ego

HTH

[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.