[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

restoring database with binary log files

Posted on 2004-11-24
12
Medium Priority
?
2,746 Views
Last Modified: 2007-12-19
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.
0
Comment
Question by:Peter1985
  • 5
  • 4
  • 3
12 Comments
 
LVL 7

Assisted Solution

by:petoskey-001
petoskey-001 earned 500 total points
ID: 12688540
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
 
LVL 17

Accepted Solution

by:
Squeebee earned 500 total points
ID: 12693243
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
 
LVL 1

Author Comment

by:Peter1985
ID: 12696279
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 17

Expert Comment

by:Squeebee
ID: 12696526
Are those files still present? If not you would not be able to write anything that would work.
0
 
LVL 1

Author Comment

by:Peter1985
ID: 12697739
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
 
LVL 7

Expert Comment

by:petoskey-001
ID: 12697755
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
 
LVL 7

Expert Comment

by:petoskey-001
ID: 12697802
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
 
LVL 1

Author Comment

by:Peter1985
ID: 12698508
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
 
LVL 17

Expert Comment

by:Squeebee
ID: 12698554
And where is this file relative to your MySQL data directory?
0
 
LVL 1

Author Comment

by:Peter1985
ID: 12699751
data folder = C:\data

mysql data directory = C:\mysql\data
0
 
LVL 17

Expert Comment

by:Squeebee
ID: 12699959
What if you add c: to the path to the file? So that it is c:/data/new_prices.csv?
0
 
LVL 1

Author Comment

by:Peter1985
ID: 12700765
I try this out.

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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

834 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