hosted by liquidweb


Go Back   Web Hosting Talk : Web Hosting Main Forums : VPS Hosting : MySQL-bin Logs - What are they? Backups?
Reply

Forum Jump

MySQL-bin Logs - What are they? Backups?

Reply Post New Thread In VPS Hosting Subscription
 
Send news tip View All Posts Thread Tools Search this Thread Display Modes
  #1  
Old
Junior 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



Sponsored Links
  #2  
Old
New Member
 
Join Date: Apr 2006
Posts: 4
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  
Old
Junior 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!

Sponsored Links
  #4  
Old
Junior 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.
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  
Old
Disabled
 
Join Date: Dec 2002
Location: Amsterdam/Rotterdam, NL
Posts: 2,085
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  
Old
Junior 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!

Reply

Related posts from TheWhir.com
Title Type Date Posted
Enterprises Improve SLAs with Disaster Recovery in the Public Cloud: Forrester Web Hosting News 2014-05-08 12:09:27
Google Releases Hosted Database Service Cloud SQL to General Availability Web Hosting News 2014-02-12 13:46:02
Google Cloud Provides Support For Native MySQL Connections Web Hosting News 2013-11-01 14:36:06
Barracuda Network Releases Version 5.4 of Cloud Backup Solution Web Hosting News 2013-08-26 12:18:47
Carbonite Launches Enhanced Server Backup for Small Businesses Web Hosting News 2013-07-26 16:31:57


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Postbit Selector

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump
Login:
Log in with your username and password
Username:
Password:



Forgot Password?
WHT Host Brief Email:

We respect your privacy. We will never sell, rent, or give away your address to any outside party, ever.

Advertisement:
Web Hosting News:
WHT Membership
WHT Membership



 

X

Welcome to WebHostingTalk.com

Create your username to jump into the discussion!

WebHostingTalk.com is the largest, most influentual web hosting community on the Internet. Join us by filling in the form below.


(4 digit year)

Already a member?