timb551
asked on
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/databasenam e.$(date +%Y).$(date +%m).$(date +%d).sql
echo "Database Exported"
echo "Compressing Database"
tar -cvzf /backups/databasename.$(da te +%Y).$(date +%m).$(date +%d).tgz /backups/dumps/databasenam e.$(date +%Y).$(date +%m).$(date +%d).sql
echo "Copy file to Windows Server"
cp /backups/databasename.$(da te +%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','000213 2153-3','2 006-11-0 523,NULL,NUL' at line 1
If i do a manual dump and then restore it works without a hitch.
Help please.
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/databasenam
echo "Database Exported"
echo "Compressing Database"
tar -cvzf /backups/databasename.$(da
echo "Copy file to Windows Server"
cp /backups/databasename.$(da
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','000213
If i do a manual dump and then restore it works without a hitch.
Help please.
ASKER
no, it seems to run fine and create all necessary files.
Hi!
And you are moving data from Linux to Windows or different versions of MySQL ?
Regards,
Tomas Helgi
And you are moving data from Linux to Windows or different versions of MySQL ?
Regards,
Tomas Helgi
ASKER
i am trying to restore the sql file to the same server that exported it.
Hi!
What command do you use to restore the database ?
Regards,
Tomas Helgi
What command do you use to restore the database ?
Regards,
Tomas Helgi
ASKER
i dropped the test database then recreated in and then ran the following
mysql -u***** -p databasename < /backups/dumps/databasebac kupname.sq l
mysql -u***** -p databasename < /backups/dumps/databasebac
Hi!
Can you post the lines around the line that has this data (5 lines before and after) from the dump.
'NULL,'0002132153','000213 2153-3','2 006-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
Can you post the lines around the line that has this data (5 lines before and after) from the dump.
'NULL,'0002132153','000213
This seems to be some data issue that the parser doesn't know how to deal with.
Regards,
Tomas Helgi
ASKER
im a bit confused but i looked at the .sql and there are only approx 4000 lines.
what does "at line 57040868" mean
what does "at line 57040868" mean
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
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
ASKER
Ok i will look and report back, thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I found the issue.
The script was running over the top of itself from another source and corrupting the backup files.
The script was running over the top of itself from another source and corrupting the backup files.
Did the cron-backup/server report any errors at the time when the backup was taken ?
Regards,
Tomas Helgi