Link to home
Start Free TrialLog in
Avatar of Peter1985
Peter1985

asked on

restoring database with binary log files

I tried to restore a database using an mysqldump file and binary log files.

i ran the following command to roll foward the database to the point i wanted:
C:\mysql\bin>mysqlbinlog binarylog.277 | mysql

This command gave me the error:
ERROR 2 (HY000} at line 6165051: File 'C:DOCUME~1peterbLOCALS~1TempNew_Prices.csv-3f-2' fot found {Errcode: 2)
mysqlbinlog: Error writing file 'UNOPENED' (Errcode: 22)
mysqlbinlog: Error writing file 'UNOPENED' (Errcode: 22)
mysqlbinlog: Error writing file 'UNOPENED' (Errcode: 22)
mysqlbinlog: Error writing file 'UNOPENED' (Errcode: 22)
mysqlbinlog: Error writing file 'UNOPENED' (Errcode: 22)
(repeating...)

I don't understand the error as it shouldn't be looking for that file (which i'm not sure if it exists).

I also tried editing the log to take out queries i don't want to run. I used the command:
C:\mysql\bin>mysqlbinlog binarylog.277 > textfile.txt
i then edit the textfile.  When inputing (C:\mysql\bin>mysql database < textfile.txt) this into the database i got silimar errors as above.

Has anyone had similar problems with bin logs?

Am i doing anything wrong?

Thanks for any help.
SOLUTION
Avatar of petoskey-001
petoskey-001

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Peter1985
Peter1985

ASKER

My log files will always have a load data query which will look something like:

LOAD DATA infile '/data/filename.csv' into table [talbename]

/data is a shared drive on that computer.  How would i write this in my queries so i don't get this problem in the future?

Should i write it as '//data/filename.csv'?
Are those files still present? If not you would not be able to write anything that would work.
Yes files are still present

On the database server i have a shared folder (called data) that has textfiles in it.  I have scripts that run during the day and load data from those textfiles into the database, i currently write them like so:

load data infile '/data/filename.csv' ......

There is always a file in that folder with same name, yet i still get the error when running the binarylog.

do i have to write it differently?

i use '/data/filename.csv' when at the server or when using the mysql client software, and all seems to work when running queries just not in binarylog.
the error is file not found.  Try running from the command line and see if you can make the load data infile command work
Can you post the actual command from line 6165051?

This command gave me the error:
ERROR 2 (HY000} at line 6165051: File 'C:DOCUME~1peterbLOCALS~1TempNew_Prices.csv-3f-2' fot found {Errcode: 2)

Pipe the output to a textfile then scroll to that line.  I think that the line that is giving you troubles is using C:\ backslash type pathing and that's what is failing.  Verify the specific line the server mentioned before you go looking at everything else.  That is the key to this problem.
this is the actual line in the log file

# LOAD DATA INFILE '/data/new_prices.csv' INTO TABLE `tmppricedata` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' STARTING BY '' IGNORE 1 LINES (prodcode,manucode,manufacturer,proddesc,packsize,prodcat,abcrank,stock,etadate,rrp,price,ourcode,bosscode,imgurl,minorder,fulldesc);
And where is this file relative to your MySQL data directory?
data folder = C:\data

mysql data directory = C:\mysql\data
What if you add c: to the path to the file? So that it is c:/data/new_prices.csv?
I try this out.

I will test the file paths some more.  Thank you both for helping me with my question.