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.
LVL 1
Peter1985Asked:
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.

petoskey-001Commented:
The error you are getting is error 2 - File Not Found

Looking at the file it can't open I would say it is trying converting the backslashes to special characters.  'C:DOCUME~1peterbLOCALS~1TempNew_Prices.csv-3f-2' should be 'C:\DOCUME~1\peterb\LOCALS~1\Temp\New_Prices.csv-3f-2'  (or something like that)

Try this instead...

C:\mysql\bin>mysqlbinlog binarylog.277 > RestoreDB.sql

Open the RestoreDB.sql file in a text editor and change all the backslashes to forward slashes where they refer to a filename, or double them up.

Example...

C:\Path\To\Filename.txt   <== Bad
C:/Path/To/Filename.txt      <== Good
C:\\Path\\To\\Filename.txt   <== Good

Once that is fixed, you may get error 1046, No Database Selected.  Specify the default database when piping to Mysql, you may also require a password.

C:\mysql\bin>mysqlbinlog binarylog.277 | mysql test

... or this...

C:\mysql\bin>mysqlbinlog binarylog.277 | mysql test -u root

0
SqueebeeCommented:
More importantly, the binlog is trying to read a CSV formatted file (New_Prices.csv) for a LOAD DATA command. I am going to guess you do not have that file anymore. Try to remove the LOAD DATA statements from the binlog.
0

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
Peter1985Author Commented:
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'?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

SqueebeeCommented:
Are those files still present? If not you would not be able to write anything that would work.
0
Peter1985Author Commented:
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.
0
petoskey-001Commented:
the error is file not found.  Try running from the command line and see if you can make the load data infile command work
0
petoskey-001Commented:
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.
0
Peter1985Author Commented:
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);
0
SqueebeeCommented:
And where is this file relative to your MySQL data directory?
0
Peter1985Author Commented:
data folder = C:\data

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

I will test the file paths some more.  Thank you both for helping me with my question.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.