MYsql database restore difficulties

Hi

At night i have a script that is run by cron to backup a mysql database.

The script is:



#!/bin/bash
echo "Database Replication"
echo "Exporting Database"
mysqldump -u****** -p***** databasename > /backups/dumps/databasename.$(date +%Y).$(date +%m).$(date +%d).sql
echo "Database Exported"
echo "Compressing Database"
tar -cvzf /backups/databasename.$(date +%Y).$(date +%m).$(date +%d).tgz /backups/dumps/databasename.$(date +%Y).$(date +%m).$(date +%d).sql
echo "Copy file to Windows Server"
cp /backups/databasename.$(date +%Y).$(date +%m).$(date +%d).tgz /mnt/win/
echo "Databases Compressed and moved"
echo "Finished"



If i try and restore the backup to a test database on the same server i get the following error

ERROR 1064 (42000) at line 57040868: You have an error the manual that
corresponds to your MySQL server versio use near 'NULL,'0002132153','0002132153-3','2006-11-0     523,NULL,NUL' at line 1


If i do a manual dump and then restore it works without a hitch.

Help please.
timb551IT ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tomas Helgi JohannssonCommented:
HI!

Did the cron-backup/server report any errors at the time when the backup was taken ?

Regards,
    Tomas Helgi
timb551IT ManagerAuthor Commented:
no, it seems to run fine and create all necessary files.
Tomas Helgi JohannssonCommented:
Hi!

And you are moving data from Linux to Windows  or different versions of MySQL ?

Regards,
   Tomas Helgi
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

timb551IT ManagerAuthor Commented:
i am trying to restore the sql file to the same server that exported it.
Tomas Helgi JohannssonCommented:
Hi!

What command do you use to restore the database ?

Regards,
      Tomas Helgi
timb551IT ManagerAuthor Commented:
i dropped the test database then recreated in and then ran the following

mysql -u***** -p databasename < /backups/dumps/databasebackupname.sql
Tomas Helgi JohannssonCommented:
Hi!

Can you post the lines around the line that has this data (5 lines before and after) from the dump.
'NULL,'0002132153','0002132153-3','2006-11-0     523,NULL,NUL' at line 1

This seems to be some data issue that the parser doesn't know how to deal with.

Regards,
     Tomas Helgi
timb551IT ManagerAuthor Commented:
im a bit confused but i looked at the .sql and there are only approx 4000 lines.

what does "at line 57040868" mean
Tomas Helgi JohannssonCommented:
Hi!

That is the line of the code of MySQL for bug purposes i think.
You should look at the values that either are in line 1 of your dump file or somewhere near.

Regards,
     Tomas Helgi
timb551IT ManagerAuthor Commented:
Ok i will look and report back, thanks
timb551IT ManagerAuthor Commented:
I found the issue.

The script was running over the top of itself from another source and corrupting the backup files.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
timb551IT ManagerAuthor Commented:
I found the issue.

The script was running over the top of itself from another source and corrupting the backup files.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.