Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 106
  • Last Modified:

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.
0
timb551
Asked:
timb551
  • 7
  • 5
1 Solution
 
Tomas Helgi JohannssonCommented:
HI!

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

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

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

Regards,
   Tomas Helgi
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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

What command do you use to restore the database ?

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

mysql -u***** -p databasename < /backups/dumps/databasebackupname.sql
0
 
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
0
 
timb551Author Commented:
im a bit confused but i looked at the .sql and there are only approx 4000 lines.

what does "at line 57040868" mean
0
 
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
0
 
timb551Author Commented:
Ok i will look and report back, thanks
0
 
timb551Author Commented:
I found the issue.

The script was running over the top of itself from another source and corrupting the backup files.
0
 
timb551Author Commented:
I found the issue.

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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now