Database Backups

[boxturt]

Greetings!

 

I'm pretty good at making sure I separately back up my databases in addition to regular full backups. Before today I never had reason to check the contents of backup.sql.gz and I noticed that the database backups are all table structure and no data.

 

How do I get complete backups? If I lose my mailing list database and have only tables to restore....

 

I am perplexed. I know I can export right out of phpMyAdmin but that seems time consuming.

 

Hints?

[OJB]

How are you backing up? Are you using the Backup options from within CPanel? I have just tested on Alderaan and the backups from there contain all the data as well as table structure.

[TCH-Bruce]

I use the dbsender script and it has the tables as well as the data from the database. So like OJB is asking how are you backing up?

[boxturt]

Thanks for the replies. I am using the backup option(s) in cPanel. I tried it on a couple sites, on a couple servers, including dedicated, all with the same result. (makes me think it's me) Here is a sample of what I receive:

 

>-- MySQL dump 10.11
--
-- Host: localhost    Database: user_myscript
-- ------------------------------------------------------
-- Server version	5.0.81-community

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `myscript_archives`
--

DROP TABLE IF EXISTS `myscript_archives`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `myscript_archives` (
 `list` varchar(32) default NULL,
 `archive_id` varchar(32) default NULL,
 `subject` text,
 `message` mediumtext,
 `format` text,
 `raw_msg` mediumtext,
 KEY `myscript_archives_list_archive_id_index` (`list`,`archive_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `myscript_archives`
--

LOCK TABLES `myscript_archives` WRITE;
/*!40000 ALTER TABLE `myscript_archives` DISABLE KEYS */;
/*!40000 ALTER TABLE `myscript_archives` ENABLE KEYS */;
UNLOCK TABLES;

(MORE TABLES HERE)

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2009-10-29 23:10:07

 

Strange strange.

Edited October 30, 2009 by boxturt

[TCH-Bruce]

Try the dbsender script Ty. Also, open a ticket with the help desk, it should be dumping the data as well.

[boxturt]

I will, and I will. Thank you.

[boxturt]

Sometimes I am such a maroon. I'm glad I didn't open a ticket. Remember when I said it "must be me"? It is.

My text editor decided to default to no-wrap and I just did not see the microscopic horizontal scroll bar at the bottom of the document. It's all there, the entire database, on one or three really long lines.

 

I'm still going to look into the dbsender though. Thanks guys.

[Bob Crabb]

If you have a reseller account, are you backing up from the root account, or from the account of the actual site.

 

Some time ago, I found that if I back up databases for sites in my reseller account from the root account, it looks like the backup is done, but the backup files do not include all the data from the database.

[boxturt]

Good to know for future but I have separate accounts. Thank you Bob.

[TCH-Bruce]

Glad you sorted it Ty but dbsender works great.

[sardaukar]

I am afraid, I need some help.

I have a problem with a freelancer from India and backups. His worker ruined entire section of my website (talonai*com). He acknowledged the problem and told this happened before 1 November 2009. I asked support at livechat and they told me they have backup as of 31 October. Pretty nice, asked them to restore. Support was, as usuall, very helpfull, they did listen to me and did all they could. They did their job.

Funny enough, the backup did not help. Section was gone (0 listings when there should be >100). Restored via CPanel as of 27 October. Still nothing. The strange thing was - the site loads tickets I submitted for 03 November.

I have a full backup as of 12 October, but am not sure how to restore the site from my PC.

What may I do? Any help would be highly appreciated.

yes, i know - my fault is, I did not make my own backups. But I never expected to meet an unprofessional freelancer

[TCH-Thomas]

With a full backup, do you mean a backup of the database or a backup of the html/php files?

 

If it´s a database backup, you can upload it to your ftp and tell the helpdesk where you put it and they can restore it for you.

 

If it´s the html/php files you have a full backup of, I am afraid you are out of luck.

[sardaukar]

Well, at 12 October I did a full backup of everything. Some days later downloaded it to my PC. I thought, if I was doing full backup - all database entries, everything should be restored. i.e. the site should look like it was on, say, 27th or 31st October 2009. Was I wrong?

The questions are:

1) is there anything I can do about restoring destroyed data?

2) in future, while making backups - should I do full backup or only database etc?

 

 

The only good thing is - I get almost lightspeed support form TCH...

[TCH-Bruce]

If you have made a full backup in cPanel:

A full backup includes all of the files in your home directory, your MySQL Databases, and your email forwarders and filters. You can backup your account to preserve your data or use a backup file to move your account to another cPanel server.

 

Your databases and every thing else in your site is backed up. Upload the file to your site and open a ticket with the help desk to have it restored. If you did a Home Backup the database will not be included and should have been backed up separately.

[sardaukar]

Backup, made by me, is located in home directory (file:backup-10.12.2009_06-26-43_talonai.tar).

Should I open a new ticket and request backup from this specific file? Sorry for noobish questions, it's my first time I face data destroyed.

[TCH-Bruce]

Yes, just open a ticket with the help desk and ask them to restore the file for you. Give them the location.

[telcor]

If you have a reseller account, are you backing up from the root account, or from the account of the actual site.

 

Some time ago, I found that if I back up databases for sites in my reseller account from the root account, it looks like the backup is done, but the backup files do not include all the data from the database.

 

 

When performing MySQL backups using the Root/Reseller override feature* then a valid .my.cnf file needs to exist in the user's home directory. Otherwise the resulting file with be zero bytes.

 

* Root/Reseller override: this feature allows one to login to a user's cPanel interface using a combination of the username and the root or reseller password. In this scenario the login does not have access to the MySQL password ( since it is managed by MySQL which handles its own authentication ). Hence the need for a .my.cnf in the account's home directory. The .my.cnf file needs to contain the user name and password and often looks like:

 

>
[client]
user='username'
pass='temp123'

Please note that the permissions on the .my.cnf file should be 0600 since the MySQL password is often the same as the account password.

 

if a .my.cnf file exists in the user's home directory, but contains the wrong information then everything will appear to work but no backup will actually occur. If no .my.cnf exists then cPanel will generate an ugly error message.

[sardaukar]

Opened a ticket. Support responded - and asked to confirm that I do want to restore. I wrote that yes, I want to restore and am aware any data loss would be on my risk. However, restoring did not produce results

Anyway, the only thing that enlightens me, is TCH attitude towards customer's problems. Everyone I met, talked to did listen to me, they were interested in solving my problems. A quality, professional support deserving the best words both there and in Webhostingtalk.

[TCH-Bruce]

Thank you for the kind words. Sorry it did not produce the results you were looking for.

[lemina]

Hello,

 

1. I'm doing database backup using cPanel->Backup

2. Last "good" backup I have from 20th March 2010. When I tried to do new backup 29 - I downloaded empty file. Why is that and what is cure for that? I would like do do full backup of database, but right now this is impossible. So, I need some help.

 

Thanks,

[TCH-Thomas]

Welcome to the forum, lemina.

 

I would open a ticket with the help desk about this and ask them to have a look at it. Link on top of page and in my signature.

[lemina]

Welcome to the forum, lemina.

 

I would open a ticket with the help desk about this and ask them to have a look at it. Link on top of page and in my signature.

Thanks Thomas - I thought that first I will ask here But if there's no simple answer I will create ticket.

[lemina]

Thanks Thomas - I thought that first I will ask here But if there's no simple answer I will create ticket.

Super. I've created ticket and now my database is dead

 

General Error

SQL ERROR [ mysql4 ]

 

Access denied for user 'frugane'@'localhost' (using password: YES) [1045]

 

An sql error occurred while fetching this page. Please contact an administrator if this problem persists.

 

Before reaktions like that I would like to be inform

[TCH-Thomas]

Time to reply to that ticket and ask them to investigate it further it seems.

[lemina]

Time to reply to that ticket and ask them to investigate it further it seems.

I replied, but they see no problem (they can do the backup) I tried different browser ans the problem persist. I will insist to solve this problem. I'm sorry to tell that, but in this case I am like a pitbull ;-) The problem will be solved I hope

Edited April 11, 2010 by lemina

[TCH-Bruce]

Welcome to the forums lemina

 

Please reopen your ticket and let them know it's still not working for you.

[lemina]

Welcome to the forums lemina

 

Please reopen your ticket and let them know it's still not working for you.

Hello Bruce

 

I'm reopening ticket all the time, but last few hours I just sleep

 

Nobody before had such problem?

 

Ech... Life ;-)

[TCH-Thomas]

In a couple of posts above this one, you say they can do the backup which I don´t doubt since you can do it too (if I haven´t misunderstood it), but I would ask them to also extract the backup and ask them for the result.

[lemina]

In a couple of posts above this one, you say they can do the backup which I don´t doubt since you can do it too (if I haven´t misunderstood it), but I would ask them to also extract the backup and ask them for the result.

Yes, they can do the backup, but when I'm doing - it's empty. When I write is empty - it means it is empty. Yesterday I have downloaded backup made by e-mail interlocutor. It was fine, good, perfect and not empty backup of my database. But I - I can do just empty.

[TCH-Bruce]

I'm not sure what's happening when you try to make a backup but I use the dbsender script to make my backups and email them to me. Maybe you should give that a try. You can set up a cron job to run once a day and email the backup to you automating the processes.

[lemina]

I'm not sure what's happening when you try to make a backup but I use the dbsender script to make my backups and email them to me. Maybe you should give that a try. You can set up a cron job to run once a day and email the backup to you automating the processes.

Bruce, thank you. I will try your script.

 

The problem with database backup is solved. I don't have exact reason, but maybe the resolution was in this topic: http://forums.cpanel.net/f5/mysql-backup-cpanel-80453.html

 

TYhe result: I have one more password to change and remember (I get a new database user and password) I don't have them enough

[srm_creator]

How are you backing up? Are you using the Backup options from within CPanel? I have just tested on Alderaan and the backups from there contain all the data as well as table structure.

 

 

I also was trying to do a partial backup of my sql database (should be about 45MBytes). It surprised me when the download finished so quickly! But when I examined the compressed file, it was only 334 bytes, and looked like an index table only. What should I do? I only need an SQL database backup.

Please help.

Thanks,

[TCH-Carl]

Please open a help desk ticket (link on my signature and on the top menu of the forum), include your cpanel logins as well as the database you are trying to back up. Also mention that the issue is while extracting the zip file so that they know exactly what to look for.

[lemina]

Hallo everyone

 

I have other problem with home directory backup. I'm using option for partial backup in cPanel.

The problem is that I'm downloading home directory backup and when I try to unpack it I have information about "unexpected end of file". I created ticket and the result is:

changing network, changing computer - change nothing for me with backup - it is corrupted.

 

Can anybody tell me if there is any coherence between size of home directory and space on the disk? I mean: if my home directory has around 840MB and I have just 1,4GB place on disk space there is possibility to do backup using function of partial backup in cPanel or it will be uncomplished because of not enough disk space?

 

Anybody knows answer to that question?

[TCH-Thomas]

I am not sure I understand this, too early in the morning, but let´s give it a try.

 

You say you have 1.4 GB left on the disc space, is that your server space or your local computers disc space? I am seeing a possiblity here that your local computer space are saying something like "this file is too big for me to download)".

 

As for the corruption, please see if this post helps. If that posts information don´t help, I would reopen the ticket and ask the techs what to do.

[lemina]

Noooooooooo

 

It is not like that. I can use 1,4 of disk space. My home directory had 840MB (so I had around 560MB free) and the gziped file was corrupted.

This post, which you linked explain something but...

 

I made some cleaning and right now my home directory has about 640MB and the problem persist - I cannot download good backup.

 

Please, I change network, computers and the problem persist. For right now the one answer of support is: we can do your backup without problems and if you want - we can do for you good backup. It is very kind but I want to do backup all by myself - They gave me function do do it but I cannot use it (I have button "Make backup" and i am not afraid to use it! ;-) )

 

So... Anybody had such a problem?

[TCH-Bruce]

The problem is that I'm downloading home directory backup and when I try to unpack it I have information about "unexpected end of file".

 

Either the packed file is corrupt or there is a file that's being packed that is corrupt. Have you tried a full backup instead of a Home Backup?

[lemina]

Either the packed file is corrupt or there is a file that's being packed that is corrupt. Have you tried a full backup instead of a Home Backup?

Well, I did full backup and it is fine. I have problem with partial backup. Since June all files are corrupted but just right now I noticed that... I just want partial backup from time to time, not full... And the problem is that there exist option in cPanel but I cannot use it properly. Funny thing is that for support everything goes fine and I have a problem.

 

I will not say that I cannot delete database and support do that without problems. Problem is that if I can use something (I have option) than why I cannot do that?

[TCH-Bruce]

Only the techs can help if it's not working I'm afraid.

[lemina]

Only the techs can help if it's not working I'm afraid.

The funniest thing is that they cannot do that Life...