MySQL 5.0 Backup Issue

English Applications
1

I’m a n00b at mysql. Just FYI. I’m using the GUI interface for MySQL admin to schedule my backup job. My settings are as follows:

  • Backup Selected Database Completely
  • Everyday at: 1:0
  • Target Directory is set correctly
  • Connection Name is using the same connection my website uses for the db

I made 2 manual backups. The rest were done by the schedule. I noticed that for the backups that were made by the schedule, the file sizes are all identical @ 580bytes. My 2 manual backups are correct @ 420k+ each. Below is a screenshot:

http://www.tabooexchange.com/limg/backupsn.png

any ideas?

2

I’ve seen the same symptom, surprisingly on a Mac server. What I found is that the cron job fails, but I didn’t find the core cause. I suspect it’s something to do with the version of MySQL Administrator vs mysql. It’s not my server so I left it with the owner to check for the moment, and I made up a mysqldump cron job to do the backup.

To check, look in the crontab for the job that MySQL Adminstrator created, and copy and paste that line into a terminal session. That will run the backup job on the spot. Note any error messages it generates, that may give you a further clue.

I’d be interested in what you find out, it may help me too.

3

What could be happening is that the crontask is not passing in the correct credentials to connect to MySQL. If you open the 580 bytes sized file in an editor, what does it say? Is there an error in there?

4

He will find that it’s an incomplete dump, only the starting lines. I found the solution to the Mac OS/X problem by searching, it’s due to the unattended job not being able to get the credentials from the keychain. Something similar may be happening here.

5

holy ****. I completely forgot about this thread and left that issue floating out there. Thankfully nothing has happened to my web server. I’m going to follow up on this over the weekend.

6

For what it’s worth: here is the script I use in my crontab to backup my databases:


mysqldump --user=root --password=the_password the_dbname > /data/backups/filename.sql

I know this is probably not the best way, but at least you can limp along until you figure out what’s going on.

7

Actually that sounds perfect. But how do I tell it what specific time to perform the backup on a nightly basis?

8

On Tue, 31 Aug 2010 14:36:03 GMT, abacabb
<abacabb@no-mx.forums.opensuse.org> wrote:

>
>twelveeighty;2210333 Wrote:
>> For what it’s worth: here is the script I use in my crontab to backup my
>> databases:
>>
>> >
>Code:
>--------------------
> > >
> > mysqldump --user=root --password=the_password the_dbname > /data/backups/filename.sql
> >
>--------------------
>> >
>>
>> I know this is probably not the best way, but at least you can limp
>> along until you figure out what’s going on.
>
>
>Actually that sounds perfect. But how do I tell it what specific time
>to perform the backup on a nightly basis?

That is what the cron utility does for you.

9

Actually that sounds perfect. But how do I tell it what specific time to perform the backup on a nightly basis?

Use the “crontab” functionality: since I pass in the username and password credentials into the backup command, I simply have it running under my own users’s crontab. Read up on crontab on the Web - it’s been around since the dawn of (Unix) civilization, so there’s ample documentation around - Wikipedia is a good place to start.

In my case, I have a crontab that takes a daily backup into a dedicated “day of week” folder, which means I have an easy accessible rolling 7-day history on my database backups. Additionally, those 7 days are backed up every time they change to Crashplan (an online backup system), with some ridiculous retention - 10 years or so. It’s probably overkill, but it makes everyone sleep without worries at night.
In the same crontab, I also run “mysqlcheck” for nightly update index statistics and database sweeps.

Here is a snippet of my crontab file - you’ll get the drift once you understand the crontab schedule syntax:


> crontab -l

0 2 * * 0 mysqldump --user=root --password=the_password the_database > /home/data/backups/the_database/daily/the_database-sun.sql
0 2 * * 1 mysqldump --user=root --password=the_password the_database > /home/data/backups/the_database/daily/the_database-mon.sql
...
0 2 * * 6 mysqldump --user=root --password=the_password the_database > /home/data/backups/the_database/daily/the_database-sat.sql
9 3 * * * mysqlcheck -Aa --user=root --password=the_password