Backup / restore without MySQLDumper

[CarolC1]

(3.3.2 test board) I've been using MySQLDumper for years. Loved it. Now that we are on new hosting, I installed it and got a white screen, not sure why. It may be because we are on PHP 8.1.29, but I saw information online that someone had it working with 8.1, so it may be some other reason. I am stuck without it.

I can back up through the ACP, but there are 2 issues. The backup runs super fast and completes in under a minute. And the resulting file size is half the size of the MSD dump, both of them .sql, both unzipped. So I'm not sure it completed. I had the setting on MySQLDumper throttled to slow the dump, so that may explain the time difference. The ACP dump may not include some "extra" or "optional" (?) info that MySQLDumper did in the output, again I don't know. Since I never use the ACP dump, I don't know if there are issues with it at times. Does anyone know of any?

You might say, why not just restore the ACP dump and see what you get? Because I had to get Tech Support to import the data to create the test board, and I don't want to have to ask them again, if I mess it up now importing an incomplete dump. I tried their SSH terminal and that didn't work. After over 2-ish hours on tech support checking that I had the command right and sending them 14 screenshots, they could not figure out why it wasn't working, so they just did it for me. But that is not a long term solution. I don't want to have to ask for help for something this simple.

I saw here a recommendation of MyOOS-Dumper posted a couple of years ago. It was based on MySQLDumper. Now I see on their website that the person who was working with it developed health issues, could not continue to support it, and recommends not to use it due to security.

Can anyone recommend something like MySQLDumper that is reliable? I don't mind paying for it. Or other ideas. The database is not huge, but too big for phpmyadmin. 292.54 MiB in the ACP General tab.

I want to upgrade the board because we are on PHP 8.1.29, and I need to be able to back up and restore.

Thanks for any ideas/comments.

[warmweer]

There's always phpMyAdmin

Alternatives I've used are
Adminer
HeidiSQL

and there's still Bigdump.php which solves the phpMyAdmin issue of timeouts with importing large dumps

[thecoalman]

I tried their SSH terminal and that didn't work.

Use putty: https://www.chiark.greenend.org.uk/~sgt ... atest.html

SSH is easiest, fastest and most reliable way to import/export DB data. Reliability being the keyword, especially for exports.

[invenio]

Why not stick to the ACP, phpMyAdmin, or SSH as this is how everybody else seems to be doing it and it works? Sure you can try some 3rd party solution but it's always going to be more difficult to troubleshoot as a lot less people are familiar with those.

Maybe look into new hosting if their services (like SSH) don't seem to be working properly.

[CarolC1]

Excuse my tunnel vision here. I looked at each option you gave.

@warmweer Bigdump looks like the choice to import. I tried it years ago before trying MySQLDumper. Thank you.

@thecoalman I tried both PuTTy and Bitvise. Both installed on my computer fine. PuTTy connected to the server and then the connection dropped on their end immediately every time. I went back and forth with tech support, they whitelisted my IP repeatedly but we could never get it to work. Then I tried Bitvise, same thing, connected and aborted on their end. I went back to tech support, got a different guy, who literally posted me a copy/paste of advice off of stackoverflow which didn't even apply to the SSH client, it applied to the server product from Bitvise. One of the guys did a wildcard IP 0.0.0.0/0 , warned me that it was a security issue, and it still didn't work.

A few days later I tried their SSH terminal built into the UI. Could not get that to work, but I'm no computer whiz. Kept getting something like no such file or directory. I wasn't sure I had my command formulated correctly, so I checked with tech support again, ensured it was correct, still did not work. They tried everything, said something vague about a permissions issue. (I need to learn to jump in and say, "OK, will you fix it please.") I'm not the hosting account owner but have full access, so I don't know. It was letting me make commands, it just wasn't giving the results. Maybe they didn't know how to formulate it either, I have no idea.

@invenio I read your backup KB article, which was good. That was why I decided to try the ACP again. But the ACP backup ran a short time and completed. It was about half the size of a MySQLDumper .sql file. Today I opened the .sql file in my editor and tried to see if it was incomplete. I read Lumpy Burgertushie's advice in another topic to check at the end and see if Zebra was there. Yes, it is. But then I went to the posts table and looked at the Insert commands. They start on line 976 and end on line 2160 and appear to take 2 lines each in the ACP dump. So 2160-976=1184, and 1184/2= 592. That makes me think the ACP only dumped 592 posts. The board has more like 100,000. Attaching a screenshot of the beginning and end. So I don't think it completed, even though it went to the end, but it didn't give an error. Is there a log where I would check for an error?

976-2160.PNG

When I check an older (smaller number of posts) MySQLDumper .sql file, it starts at 66985 and goes to 164,387, one line each Insert, so that's around 97,402, which is about right.

MSD_dump_posts_table.PNG

I wish I could use the ACP, and maybe it works on other servers, but I don't think it worked for me. Maybe the fact that the board is 3.3.2 and the PHP version is 8.1.29 messed it up.

Appreciate the comments. Any others welcome, too. Thanks

[invenio]

I think the entire "analyze the database" is above my level. However, if you think you were able to get about 1/2 the database from the ACP method, you could try deleting the search index as this often represents a very large portion of the database. This could potentially get the database size down where you can make a full download before it times out/errors out. BTW, what is the database size per the ACP welcome screen?

Also, and perhaps I missed it, but do you have access to phpMyAdmin, as that is another easy avenue to backing up the database?

Last but not least, if your hosting provider has access to a working SSH, not sure why it keeps failing for you, you can just give them the command line to run for the database backup. Maybe it can run on their end and it's just one line.

[thecoalman]

I wish I could use the ACP, and maybe it works on other servers, but I don't think it worked for me. Maybe the fact that the board is 3.3.2 and the PHP version is 8.1.29 messed it up.

The ACP and phpMyadmin have limitations because of php's max execution time and other directives. That's why SSH is more reliable, there is no php involved. MySQL dump works because it does it incrementally. Quite honestly I'd be looking for a new host if they can't get SSH issue resolved for more reasons than just the lack of SSH access. That's not a good sign .

If you have Cpanel open MultiPHP INI editor. note the existing values because you'll want to restore them. Change them to:

max_execution_time = 500
memory_limit = 500M
post_max_size = 10M higher than size of file
upload_max_filesize = 10M higher than size of file

Go into phpmyadmin and the new values should be reflected for max fie size on import tab. You can also check all the values by going to php information page in phpBB's ACP. If it they aren't changed or the MultiPHP INI editor isn't available then you can't change them. If successful that should allow you to import/export a large DB file. You'll want to change the values back, they are there for a reason.

Specifically for exporting and since it's PITA to be changing php.ini values all the time you can open adm/index.php and find:

Code: Select all

// Is user any type of admin? No, then stop here, each script needs to
// check specific permissions but this is a catchall
if (!$auth->acl_get('a_'))
{
	send_status_line(403, 'Forbidden');
	trigger_error('NO_ADMIN');
}

After add:

Code: Select all

set_time_limit(300);

You can leave that in place and you should have plenty of time to complete DB export.

[Mick]

On the subject of BigDump:

Bigdump looks like the choice

The author has stated that if there are issues with newer versions of PHP (version 8.x presumably) then he will investigate and fix the issue. Basically it’s still supported contrary to the rumours.

Hello Mick,

Are there any issues with Bigdump running the latest PHP versions? No one has ever reported to me. I'd be glad to fix it.

Alexey

BigDump support

[CarolC1]

@invenio The database size is 292.54 MiB in the ACP General tab. Yes about the search table, and notifications is really large, too. I do not currently have access to PhpMyAdmin on either board. It does not come with a database on this hosting, you have to install it or ask for it. They installed it on the live board for me and it currently says it has no tables. I never even got to use it yet. I have not got it installed on the test board yet. They have a KB article how to do it yourself. Strangely (to me) they installed it in the directory containing the forum files. I can see it by FTP. That's weird because always before in the past I could never see PhpMyAdmin. Also, on previous hosting I could always see both databases (live and test) in phpMyAdmin but they seem to have everything isolated and I guess it's going to require another installation for the test board even though it's on the same server. Everything is different.

@the coalman and @Mick, thanks for the great information.

@thcoalman After we lost the hosting we had, we were offered free hosting by the person who started the board in 2001. That was the best thing that could have happened, not because it's free, but because of who offered it. He knows hosting and servers and I suspect if I was more of a techie I would know 5 ways around every problem I've encountered trying to use their facilities, but I'm not/don't.

What I want to do is make the dump and download it to my computer. With MySQLDumper, it went to a backups folder inside the forum files and I could access it by FTP. If I use mysqldump or SSH, is it going to store in some inacessible place on the server?

I can try SSH again when I have a block of free time. The hosting UI SSH Terminal worked (at least you could type commands and get responses. It just seemed to say the command was wrong for what I was trying to do previously. I must have had the path wrong, but they couldn't figure it out either. Once I know the command, I'll have it. So it's really just one hurdle, getting the comman right.

@MIck I am impressed by the commitment of the developer of BigDump (and that you could contact him). It's good to know they offer support, but I hope to never need it. I really just want to be able to do my work independently without having to appeal to anyone for help, like I used to be able to do. If I have to contact hosting tech support or BigDump (or other) support every time I need to backup/restore, it will make me put off doing anything. However, it gives you confidence about BigDump.

[invenio]

SSH will dump the backup into what ever directory you are in currently when you enter the command. If you want it in a specific directory, change into that directory first and then execute the backup command.

[CarolC1]

@invenio Thanks, that helped.

A little progress. Their SSH Terminal in the hosting UI defaulted to public_html so I didn't have to change it.

Was able to do a mysqldump via SSH and found the .sql file in public_html and downloaded it.

But...it looks incomplete. Less than 100 posts.

I thought that wasn't supposed to happen with this method? Or I'm doing something wrong.

mysqldump.PNG

@thecoalman This hosting does not have Cpanel, but thanks for the information.

[invenio]

I really don't know the comparison differences but it would seem unlikely that the database is only 100 posts. You mentioned that it's over 100 mb in size. My database size is 90MB and it's 25k posts for example, and that includes the search index.

A little tangential, but have you considered changing hosts? SSH doesn't work properly, no phpMyAdmin access,... just really not ideal for managing a phpBB forum. May be worthwhile to put you time into changing hosts vs endlessly dealing with the failings of your current one?

[thecoalman]

What command are you using in SSH?

[invenio]

Also, just for reference. When I make an ACP database backup and one with SSH, the (uncompressed) database size is about equal, so if there is a big discrepancy between the way you do the backup, then there may be something wrong (with one of the methods).

The command line I am using is:

Code: Select all

mysqldump -u [username] -p --opt [databasename] > [backupfilename.sql]

[CarolC1]

@ thecoalman

Code: Select all

mysqldump -u ********** -p********** ********** > backup.sql

@invenio Yes, it's more like 100,000 posts. It's complicated about the hosting. They seem to be geared to WordPress, not phpBB, I agree. I suspect (don't really know) that if I was better at more advanced computer/server "stuff" there are probably 5 work-arounds for every problem I've encountered and I just don't know them. And when I go to tech support, they probably have me pegged for the ignoramus I am, and they do not suggest the advanced solutions, though that isn't all of the problem. I looked up the reputation of the hosting and it seems to be fine. I just need to learn to do this better. Thanks for your help!