Results 1 to 6 of 6
-
05-05-2006, 06:38 PM #1Junior Guru Wannabe
- Join Date
- Apr 2006
- Location
- Hello World!
- Posts
- 51
MySQL-bin Logs - What are they? Backups?
Hello everyone,
I had a few of my Databases deleted the other day, one of them was to a huge forum.
Now I lost quite a few posts and the new users we got since the last backup. Not a whole lot, but some.
I had reserved myself to the fact that I may never get those back. However, I was running through my server logs when I came across the MySQL log directory and over 60 mysql-bin.xxxxxx logs. Ranging in size from 9k to 100+ MB, altogether this mysql logs folder contains over 3GB of data.
Now, I've never seen these files before, are they backups?
I viewed some of the smaller ones and they do appear to by MySQL tables.
Now if they are backups, what is the best way to import them into phpmyadmin?
I'm not to privy to downloading 3 GB worth of 100+MB files to my hard drive just to upload them again.
Anybody have a solution?
Thanks!
- Highway
-
05-06-2006, 02:01 PM #2Newbie
- Join Date
- Apr 2006
- Posts
- 5
They are MySQL binary logs. See dev.mysql.com/doc/refman/4.1/en/binary-log.html for more info. I think you may disable them by removing the line "log-bin" from your my.cnf file.
Jacek
-
05-06-2006, 02:39 PM #3Junior Guru Wannabe
- Join Date
- Apr 2006
- Location
- Hello World!
- Posts
- 51
Hey Jacek,
Thanks for the link.
The primary purpose of the binary log is to be able to update databases during a restore operation as fully as possible, because the binary log contains all updates done after a backup was made. The binary log is also used on master replication servers as a record of the statements to be sent to slave servers.
Running the server with the binary log enabled makes performance about 1% slower. However, the benefits of the binary log for restore operations and in allowing you to set up replication generally outweigh this minor performance decrement.
This is why I don't want to disable the logs, but I have to figure out how to use them on the server side to restore the database.
If any body has a quick clue on how to do this that would be great.
Though I'm sure I'll figure it out sooner or later.
Thanks!
-
05-07-2006, 04:39 AM #4Junior Guru Wannabe
- Join Date
- Apr 2006
- Location
- Hello World!
- Posts
- 51
Well, It seems I've had such an overwhelming response! :-?
Did I not post in the right forum?
After downloading and viewing the mysql logs, 99.9% of them are sessions, so I've had to go through a lot of logs to retrieve the posts.
Does anybody know how to ONLY disable the database from logging sessions, while continuing to log the posts?
In my first post, I answered most of my questions... And I'll post what I found incase anybody else comes along searching the forums with the same questions.
Originally Posted by Myself
Originally Posted by Myself
But since I did not do this right away, I would be destroying my new information by importing the old info on top of it. So this was/is not a viable option.
Originally Posted by Myself
e.g.: The mysql-bin logs are (usually) contained in the server log directory (/var/log/mysql/) I copied them to a location where I could download them using an FTP application, in this case, one of my sites. So I used this code in shell:
Code:cp /var/log/mysql/mysql-bin.123456 /var/www/sites/mywebsite.com/public_html/log/mysql-bin.123456
Code:cd /var/www/sites/mywebsite.com/public_html/log && gzip -c mysql-bin.123456 > mysql-bin.123456.gz && rm mysql-bin.123456
Now, since the file is too big to be viewed by a text editor, I used the split command to make them more viewable:Code:split -b 1m mysql-bin.123456 segment
segmentaa
segmentab
segmentac etc...
Who knows, perhaps that will help somebody down the road.Last edited by Highway of Life; 05-07-2006 at 04:45 AM.
-
05-07-2006, 04:44 AM #5Disabled
- Join Date
- Dec 2002
- Location
- Amsterdam/Rotterdam, NL
- Posts
- 2,135
Originally Posted by Highway of Life
-
05-07-2006, 04:13 PM #6Junior Guru Wannabe
- Join Date
- Apr 2006
- Location
- Hello World!
- Posts
- 51
Ah, of course. Thanks Apoc
Can a Mod please move this forum to the Technical & Security Issues forum?
Thanks!