Results 1 to 9 of 9

Thread: MySQL 5.0 Backup Issue

  1. #1

    Default MySQL 5.0 Backup Issue

    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:



    any ideas?

  2. #2
    Join Date
    Jun 2008
    Location
    UTC+10
    Posts
    9,686
    Blog Entries
    4

    Default Re: MySQL 5.0 Backup Issue

    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. #3
    Join Date
    Dec 2008
    Location
    GMT-7
    Posts
    382

    Default Re: MySQL 5.0 Backup Issue

    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?
    Desk: AMD Phenom II X4 945 8GB RAM Radeon HD 3300 Arch Linux Xfce 4.8
    Lap: Intel 2.13GHz Core i3 M330 8GB RAM nVidia GeForce 310M Arch Linux KDE 4.7.3

  4. #4
    Join Date
    Jun 2008
    Location
    UTC+10
    Posts
    9,686
    Blog Entries
    4

    Default Re: MySQL 5.0 Backup Issue

    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. #5

    Default Re: MySQL 5.0 Backup Issue

    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. #6
    Join Date
    Dec 2008
    Location
    GMT-7
    Posts
    382

    Default Re: MySQL 5.0 Backup Issue

    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.
    Desk: AMD Phenom II X4 945 8GB RAM Radeon HD 3300 Arch Linux Xfce 4.8
    Lap: Intel 2.13GHz Core i3 M330 8GB RAM nVidia GeForce 310M Arch Linux KDE 4.7.3

  7. #7

    Default Re: MySQL 5.0 Backup Issue

    Quote Originally Posted by twelveeighty View Post
    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?

  8. #8
    JosephKK NNTP User

    Default Re: MySQL 5.0 Backup Issue

    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. #9
    Join Date
    Dec 2008
    Location
    GMT-7
    Posts
    382

    Default Re: MySQL 5.0 Backup Issue

    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:

    Code:
    > 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
    Desk: AMD Phenom II X4 945 8GB RAM Radeon HD 3300 Arch Linux Xfce 4.8
    Lap: Intel 2.13GHz Core i3 M330 8GB RAM nVidia GeForce 310M Arch Linux KDE 4.7.3

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •