News:

Bored?  Looking to kill some time?  Want to chat with other SMF users?  Join us in IRC chat or Discord

Main Menu

Database Backup

Started by CyberShadow, February 21, 2015, 11:29:48 AM

Previous topic - Next topic

CyberShadow

Hi. If this is the wrong place, I apologise.

I am moving a SMF forum to a new host. I have got a backup of the old database, and it weighs in at over 100Mb. To import this to the new location, I needed to go through SSH, but that all went fine and the boards are up and running.

But..... When I now get a backup through phpMyAdmin, the database .sql comes in at 20-30Mb and wont upload to a new location successfully. I have tried exporting through SSH (and another attempt is running now) but it has just timed out in the past and not succeeded.

I dont understand how the databse backup can be 1/3 of the original size, and something is clearly not working. I assume that its timing out and only grabbing 1/3 of the database, and thats why it wont then upload again......

Any pointers or suggestions welcomed!
Many thanks.

Kindred

Well, first of allll how did you generate the initial backup of 100mb?
Btw, 100 mb is tiny...   I have had database files of over 3 gigs


As for why a new backup might be smaller...
If it won't restore, then yes, it is probably failing... But it might be smaller, depending on what options you picked for the backup file
Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

Sir Osis of Liver

On some hosts phpmyadmin will time out a backup without error.  You won't know it's bad until you try to import.  If you do several backups, file size will be different.  If your host cpanel does not have a backup utility, you can use a third party backup, like MySQLDumper.
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

CyberShadow

Thanks guys. The forum is moving from one web site to another, both on the same host. The old site simply went through phpMyAdmin and got a .sql export of the database which came in at just over 100Mb. I then grabbed this and imported it through SSH, and all was good in the world.

But when I try to do exactly the same export - through phpMyAdmin - thats when the problems occur. Although both sites are on the same host, they may have different hosting packages, but any package should be able to export a database at 100Mb. As for the size, its a pretty small and niche forum, and most of that is made up of ShoutBox tables. Its also running version 1.1.20 of SMF, so yeah, will need an update.

Because I am only helping with the transfer, I only have hosting control but not cPanel, and I can get to phpMyAdmin. Are there other features in cPanel that allow a database backup thats better than phpMyAdmin? (I use Plesk on my sites.) And now the server is 504 timing out when I try to do anything on the boards, even logging in. Its fine with the boards are in maintenance mode, but turn them on and nothing works. And I cant even grab a backup of the database.

Sir Osis of Liver

Cpanel backup options vary quite a bit.  My host automatically backs up all databases every day, and makes most recent three days available for download or restore.  It also allows me to manually backup any db at any time.  GoDaddy has a manual backup feature that saves a .sql file to a backup directory in your account, from where you can download or restore it.  Server side backups are quick and not subject to timeouts.  Some hosts have no db backup feature, so you must use phpmyadmin or a third party utility.
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

CyberShadow


Thanks. I think that there is a backup utility there.... although whether this is actually switched on right now or not....

Anyway, the issues keep mounting up. More for my own sanity, to recap the issues....

- The old forum was using SMF v1.1.20 and Shoutbox on a simple hosting platform, and as far as I know never had problems (although the hosting 'level' was one higher than we currently have).

- The old database was simply exported through phpMyAdmin (I have double checked that this was the case) and the 100Mb+ database .sql was passed to me. I got an FTP of the old structure, imported the database through SSH and connected it together with repair_settings.php

- All seemed great, so I went to have a drink. Life was good.

- After a while (maybe a week or more) the boards started getting 'max_connection' lockouts (set at 20 connections). These started happening more frequently as time went on. Disabling Shoutbox appeared to slow them down initially, but not stop them.

- A few days ago, I couldnt access the 'features' admin settings page, as it would just time out. I left it a day, and this seemed to do the trick and I could get back in.

- Two days ago, switching the boards to maintenance mode was fine, but enabling them, or an admin logging in while in maintenance mode, caused the 504 gateway timeout. This did not get better or go away.

- Today, in order to check if it was a server or web site issue, I archived the old forum folder on the server, and re-uploaded the original files to a new forum folder. But, when attempting to access this, I got the following error message:

Quote: Table 'smf_messages' is marked as crashed and last (automatic?) repair failed
/Sources/Load.php
432

- Then, when I try to get to the database through phpMyAdmin I find that all database users are gone, and when I try to create a new one I get the follwing error message:

QuoteSocket Error # 10061 Connection refused

- All of this is entirely separate from the inability to get a full backup of the database through either phpMyAdmin or SSH in the past.

I knew that I should have gone into art. You dont get these problems with a box of crayons!

Sir Osis of Liver

Can you access your forum error log?  What's in it?
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

CyberShadow

So.... The database lock was ended and I was able to gain access to the database through phpMyAdmin. I have managed to repair all of the tables except one... But, its the smf_log_search_results table, so I guess that I can just empty this table out and that should do the trick...?

The issue is that I am effectively locked out of the forums. They are fine in maintenance mode, but as soon as I try to log in (and, I guess, as the boards access the database at this point) it all grinds to a halt and I only get a 504 Gateway Error. So, all functionality that requires me to log in to the forum is currently unavailable.

Kindred

a 504 gateway error ??


That has nothing at all to do with the forum and is due to something on your host side of things.
Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

CyberShadow

That ws my first thought, too. But the boards are fine when in maintenance mode and load quickly. As soon as I try to log in, and every time I try to log in, that error comes up and I have been unable tp get access for more than two days. I can only think that there is a script somewhere that is causing problems on the server, and most likely it is linked to the Shoutbox/chat module that was installed (since this is not a normal issue for the forums as far as I know). To the point where if I use Firefox to go to the forum page, I get the Gateway error because I still have an active logged in session through that browser, but if I use IE to view the boards (where I am not logged in), the 'maintenance mode closed' page loads right away,

I think that I need to empty out the search log table to get the database at least working on paper, and then talk to the host to find where the conflict might be on the server side of things.

CyberShadow

#10
Its taken a while, but I am in danger of making some progress with this.

After back and forth with the web host, it turns out that the database is dangerously close to the maximum permitted size (150Mb). I suspect that attempting to upgrade the data created backup tables and this pushed it breaking point. From this point on, every call that actually tried to alter or write, was stuck and timed out.

So, simple solution is to clear out the data. Prime candidates are the log tables and the Shoutbox tables (Shoutbox is heavily used on the boards and we inherited Shoutbox chat messages going back 6 years!).

In addition, there are a set of tables with the backup_ prefix, which I assume are a result of the attempted forum upgrade. However, because of data limit (or, maybe nothing to do with it) I still cant make a backup of the database. So, I want to be very careful when I empty out tables.

I plan on;
- Deleting all tables with a 'backup_smf_' prefix
- Emptying out the Shoutbox tables (since past messages are not required and its likely we will find an alternative anyway)
-- Thats 4 tables with the 'smf_axajchat_' prefix and 4 tables with the 'smf_axajshout_' prefix
- Emptying out the various log tables
-- Thats 17 tables with the 'smf_log_' prefix

Can anyone confirm that this is sensible and none of these tables will cause issues for the functioning of the boards, before I go ahead with this?

Thanks.

EDIT - I have just done this action on my test forum with no serious adverse effects, so I think that I am safe to carry this out on the real thing now.

Kindred

yup. that is safe....

side note... 150 MB?  seriously?   I have a database that is 3 GIGABYTES!

You are going to need to find another, better host soon... especially if you use a shoutbox.
Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

CyberShadow

Many thanks. Tables dropped/emptied and things are back online... I am now back to the point that I was at before I started!  :P

But, now when I try an import, I get....

QuoteERROR 1064 (42000) at line 25587: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
<html><head>
<title>500 Inter' at line 109

I am not sure if the previous export is incorrect or the import, but I assume its the export that is causing this problem and inserting HTML into the database which is being read as HTML instead of data.

Any ideas?
Many thanks.

PS. Yeah, 150Mb is tiny. The guy who inherited the forum basically got the entry level hosting package at the same host that the forum was previously hosted at. We can upgrade later, but clearing out the old Shoutbox table made a massive difference.

Advertisement: