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.

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.

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.

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.