Major problem with backing up SQL database

JackJack

Hello,

I am having some trouble backing up my database for my site. I will say this right now: I know near nothing about computers. Sure, I know HTML and stuff like that, but SQL confuses me so much it's not even funny. I am literally clueless in this department.

You see, my site has forums. I'm switching hosts, and trying to backup the forum database so I don't have to completely restart. I have managed to successfully backup every file (members, skins, calendar, etc) except one: The posts.

I am using phpMyAdmin to do this. The post file is 18.8 MB. For some reason, I cannot download it. I always get this message:

Fatal error: Allowed memory size of 15728640 bytes exhausted (tried to allocate 59055 bytes) in /usr/local/www/data/phpMyAdmin/libraries/mysql_wrappers.lib.php on line 36

Sometimes the "Tried to allocate _____ bytes" part changes to a different amount.

Is there any way to prevent this error from happening, and backup the file?

Roger_Ramjet

Max file size is specified as a parameter by the system administrators where your file is hosted. You can vary it sometimes through php.ini or .htaccess parameters, but probaly not as you probably do not have access. Generally speaking, commercial hosts specify ridiculously low sizes for input and output files, and there is nothing you can do about it. Just accept that your host employs wankers with limited technical skills and no idea of what we need, and they must be wankers to work for the sort of salaries that most ISP pay these days. I run into this kind of problem all the time, and am still searching for a host that will eg. let me load more than 2Mb data at a time (what kind of usefull database has tables all less then 2MB??). You will just have to export your data in chunks and reassemble it afterwards.

JackJack

...you mean I'll have to backup each row individually, as in saving each post seperately?

Roger_Ramjet

NO!! I mean you will have to export chunks that do not exceed your server's specified max output file size by a) using LIMIT and itterating through the table b) creating multiple temp tables that you export individually. Bear in mind that your new host may have even norrower limits on LOAD DATA or however you are going to import your data.

You could use other methods to dump your whole database and then upload it. Try posting a request for help with backup methods, I'm not at all familiar with them myself http://dev.mysql.com/doc/administrator/en/mysql-administrator-backup.html.

Any decent ISP should also be able to deliver your database structure and data in a useable form. Try asking them for, you never know.

JackJack

Okay. So do multiple rows at each time, this way I don't completely exceed it at once? I gotcha. I think. I hope.

But of course... how would I do this?

Roger_Ramjet

You would dump a query using LIMIT to restrict the number of rows returned
http://dev.mysql.com/doc/mysql/en/SELECT.html
"The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must be integer constants. With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1): mysql> SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15"

However, as I said, this is not the best way to port your db. Try posting a request for help in dumping your database, or search the forum for previous threads on the same subject, of which I know there are several.

nemonoman

If using PHP my admin, there are 2 options worth trying: one zip (or even gzip) the export file.

Alternatively, select each table by name, and export each table.

JackJack

nemonoman, I am zipping all the tables. It's just this one that I'm having trouble with; it appears to be too large.

Roger, I appreciate the help, but that method sounds way too complicated. If need be, I'm willing to have to manually back up chunks of rows. You see, my host is shutting me down by tomorrow afternoon and I'm desperate to back it up by then.

Roger_Ramjet

OK, then create multiple tables and fill them using LIMIT and dump each table is the short, simple, and foolproof solution.
eg first select LIMIT 100
next LIMIT 100,100
next LIMIT 200,100

read the manual to check the syntax, and vary on the side of caution, better to duplicate records that you eliminate later than to miss some out now.

JackJack

...I'm totally lost...

I apologize for being so clueless. I've never done anything like this before.

Could you give a step-by-step explanation (if it's not too much to ask)? I'm really confused and don't know quite what to do, let alone how to do it.

Roger_Ramjet

OK. I'm only in on a Sunday to install new anit-virus software and now find I can not get the licence key untill tommorrow. Might as well do something usefull so it's not a complete bust.

Create a copy of the original posts table and add the first 1000 rows from your posts table
http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html

CREATE TABLE dump1 SELECT * FROM posts LIMIT 1000

Will create a new table called dump1 with the first 1000 rows (1- 1000)from your posts table. It will have the same structure as the original table.

Test to see if it dumps OK, and what size it is. Increase or decrease the limit as neccessary. When satisfied

CREATE TABLE dump2 SELECT * FROM posts LIMIT 999,1001

will create table dump2 with 1001 rows starting at row 1000 and ending at row 2000 inclusive.

CREATE TABLE dump3 SELECT * FROM posts LIMIT 1999,1001

rows 2000-3000 inclusive

Repeat untill all rows are replicated into new tables and dumped successfully.

I have deliberately created an overlap of rows from one table to the next. The offset for start number begins at 0 and this can confuse, leading to lost rows. When you load your new table in your new database, table constraints enforce by your unique primary key will ensure that duplicate rows are dropped.

kender

if you want to try this out, it should allow for no timeout in larger databases, and give you an idea of how far along it is

i would appreciate any feedback (one of my coders made it and it works fine on my database, but it is a small one)

JackJack

Dang it. I wish I could use that! My host cut off my account, so all I can access if FTP and phpMyAdmin until they shut it down tomorrow. I can't access it if I upload it...