Results 1 to 6 of 6
  1. #1
    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

  2. #2
    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

  3. #3
    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!

  4. #4
    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.
    Quote Originally Posted by Myself
    Now, I've never seen these files before, are they backups?
    Yes and no. No, they are not backups, and yes, they can be used to restore your database. They are log files so they continually log everything that's going on in the databases.

    Quote Originally Posted by Myself
    Now if they are backups, what is the best way to import them into phpmyadmin?
    After searching google, I did find ways to import them into mysql.
    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.

    Quote Originally Posted by Myself
    I'm not to privy to downloading 3 GB worth of 100+MB files to my hard drive just to upload them again.
    Simple solution: copy them to another directory Gzip them then download them using FTP, wget, or curl.

    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
    Then I switched to that directory on my site and gzipped the log file, which was 100MB, compressed, it becomes about 6 or 7MB, a pretty quick download, then the last code removes the 100MB log file, while leaving the GZIP to be downloaded:
    Code:
    cd /var/www/sites/mywebsite.com/public_html/log && gzip -c mysql-bin.123456 > mysql-bin.123456.gz && rm mysql-bin.123456
    All that's left to do is download your gzipped file using FTP, wget or curl.

    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
    This will tell it to split up the file "mysql-bin.123456" into segments of 1MB files apiece with the prefix of "segment", so they will come out as:
    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.

  5. #5
    Join Date
    Dec 2002
    Location
    Amsterdam/Rotterdam, NL
    Posts
    2,135
    Quote Originally Posted by Highway of Life
    Did I not post in the right forum?
    This issue isn't really specific to VPS technology. You will probably get more response in the "Technical & Security Issues" Forums. You could ask a moderator to move this thread there.

  6. #6
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •