LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices

Reply
 
Search this Thread
Old 09-12-2006, 10:32 PM   #1
filex
Member
 
Registered: Sep 2004
Posts: 56

Rep: Reputation: 15
MySQL Replicate Log Backup?


I have a MySQL Replicate running on 2 production servers.

Just wonder shall I backup this huge files daily?

-rw-r----- 1 root root 851740807 Sep 13 02:26 mysql-bin.000001
-rw-r----- 1 root root 637 Sep 13 03:46 mysql-bin.000002
-rw-r----- 1 root root 47 Sep 13 03:46 mysql-bin.000003
-rw-r----- 1 root root 280 Sep 13 03:42 mysql-bin.000004
-rw-r----- 1 root root 600212 Sep 13 03:42 mysql-bin.000005
-rw-r----- 1 root root 32628369 Sep 13 02:54 mysql-bin.000006
-rw-r----- 1 root root 47 Sep 13 03:52 mysql-bin.000007
-rw-r----- 1 root root 47 Sep 13 03:40 mysql-bin.000008
-rw-r----- 1 root root 47 Sep 13 03:26 mysql-bin.000009
-rw-r----- 1 root root 193609 Sep 13 03:42 mysql-bin.000010
-rw-r----- 1 root root 316885320 Sep 13 03:39 mysql-bin.000011
-rw-r----- 1 root root 47 Sep 13 03:39 mysql-bin.000012
-rw-r----- 1 root root 47 Sep 13 03:26 mysql-bin.000013
-rw-r----- 1 root root 47 Sep 13 03:07 mysql-bin.000014
-rw-r----- 1 root root 56894 Sep 13 01:55 mysql-bin.000015
-rw-r----- 1 root root 140704293 Sep 13 03:49 mysql-bin.000016
-rw-r----- 1 root root 47 Sep 13 03:39 mysql-bin.000017
-rw-r----- 1 root root 47 Sep 13 03:27 mysql-bin.000018
-rw-r----- 1 root root 47 Sep 13 02:31 mysql-bin.000019
-rw-r----- 1 root root 47 Sep 13 03:52 mysql-bin.000020
-rw-r----- 1 root root 1116032 Sep 13 02:54 mysql-bin.000021
-rw-r----- 1 root root 236042422 Sep 13 03:42 mysql-bin.000022

and it's keep increasing.

Kindly advise.
 
Old 09-13-2006, 03:27 PM   #2
tronayne
Senior Member
 
Registered: Oct 2003
Location: Northeastern Michigan, where Carhartt is a Designer Label
Distribution: Slackware 32- & 64-bit Stable
Posts: 3,118

Rep: Reputation: 818Reputation: 818Reputation: 818Reputation: 818Reputation: 818Reputation: 818Reputation: 818
Yeah, it's going to get bigger -- that's your data base.

You might want to consider doing mysqldump, something like this (where "dbname" is the name of your data base):
#
DATE=$(date +%Y%m%d%H%M)
# delete any content in /tmp/dbname
#
rm -rf /tmp/dbname
#
# create the dbname directory in /tmp
#
mkdir -m 777 -p /tmp/dbname
#
# execute mysqldump
#
mysqldump --tab=/tmp/dbname --opt dbname --password='bigsecret'
#
# now, make a tar
#
tar -Pcf /tmp/dbname${DATE}.tar /tmp/dbname
#
# and compress it...
#
gzip /tmp/dbname${DATE}.tar
#
# copy the tar file to archive storage
#
su mysql -c "cp /tmp/dbname${DATE}.tar.gz /archive/dbname"
#
# and clean up after ourselves
rm -rf /tmp/dbname /tmp/dbname${DATE}.tar.gz
in cron.daily (you need to set PATH and other system variables before this body).

Been doing this for years, works fine, easy to restore with mysqlimport, might be worth a try.
 
Old 09-14-2006, 02:37 AM   #3
filex
Member
 
Registered: Sep 2004
Posts: 56

Original Poster
Rep: Reputation: 15
Thanks for your reply!

But just to confirm whether I can delete:

mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004

(#just assume mysql-bin.000005 is the last copy)

after backup? or even I can delete mysql-bin.000005?

Kindly help!

Thanks.
 
Old 09-14-2006, 06:31 AM   #4
tronayne
Senior Member
 
Registered: Oct 2003
Location: Northeastern Michigan, where Carhartt is a Designer Label
Distribution: Slackware 32- & 64-bit Stable
Posts: 3,118

Rep: Reputation: 818Reputation: 818Reputation: 818Reputation: 818Reputation: 818Reputation: 818Reputation: 818
You need to look at http://dev.mysql.com/doc/refman/4.1/...intenance.html and decide what to do about rotating those files (they're replication logs).

Using mysqldump as above creates a full backup of the data base at a given time (so you can restore it if something goes blooey) which is not the same thing as the replication logs.

Give it a read and see what you think.
 
  


Reply

Tags
mysql


Thread Tools Search this Thread
Search this Thread:

Advanced Search

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
mysqld - /var/log/mysql/mysql-bin.index not found robin.w Linux - Software 5 02-10-2011 02:00 AM
Can I instantly replicate data over 2 drives on different servers with a codafs? abefroman Linux - Hardware 1 09-14-2005 01:03 PM
Tar Backup Log bally Linux - General 2 04-14-2004 01:24 AM
how to replicate os on 2nd machine terrybaker Linux - General 4 11-01-2002 11:45 AM
Replicate mail from Imap to exchnage swift Linux - Networking 0 06-22-2001 09:27 AM


All times are GMT -5. The time now is 03:29 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration