Solved

MYsql database restore difficulties

Posted on 2014-10-01
12
99 Views
Last Modified: 2015-05-25
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.
0
Comment
Question by:timb551
  • 7
  • 5
12 Comments
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 40355498
HI!

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

Regards,
    Tomas Helgi
0
 

Author Comment

by:timb551
ID: 40357099
no, it seems to run fine and create all necessary files.
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 40357757
Hi!

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

Regards,
   Tomas Helgi
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:timb551
ID: 40358840
i am trying to restore the sql file to the same server that exported it.
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 40358901
Hi!

What command do you use to restore the database ?

Regards,
      Tomas Helgi
0
 

Author Comment

by:timb551
ID: 40358929
i dropped the test database then recreated in and then ran the following

mysql -u***** -p databasename < /backups/dumps/databasebackupname.sql
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 40359048
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
0
 

Author Comment

by:timb551
ID: 40359262
im a bit confused but i looked at the .sql and there are only approx 4000 lines.

what does "at line 57040868" mean
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 40359496
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
0
 

Author Comment

by:timb551
ID: 40359931
Ok i will look and report back, thanks
0
 

Accepted Solution

by:
timb551 earned 0 total points
ID: 40787563
I found the issue.

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

Author Closing Comment

by:timb551
ID: 40794684
I found the issue.

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

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question