mysql binlogs and their expiry times

[Matthew Seaman]

Zbigniew Szalbot wrote:
> Hi there,
> 
> I hope someone can help. Due to they way my HD has been sliced I had
> to move mysql database to /usr/local/mysql. All works fine. Last week
> I added this entry
> 
> #expire bin logs
> expire_logs_days = 7
> 
> to /usr/local/mysql/my.cnf
> 
> I restarted the MySQL server and now I have been waiting for the
> binlogs to automatically expire but this is not happening:
> 
> $ ls -l /usr/local/mysql
> 
> -r--r--r--  1 mysql  mysql        4954 Oct  1 07:30 my.cnf
> drwx------  2 mysql  mysql        1536 Sep 27 07:10 mysql
> -rw-rw----  1 mysql  mysql  1073745213 Sep  2 04:07 mysql-bin.000047
> -rw-rw----  1 mysql  mysql  1073746878 Sep  7 03:48 mysql-bin.000048
> -rw-rw----  1 mysql  mysql  1073745707 Sep 11 20:07 mysql-bin.000049
> -rw-rw----  1 mysql  mysql   175527890 Sep 12 08:32 mysql-bin.000050
> -rw-rw----  1 mysql  mysql      128272 Sep 12 08:40 mysql-bin.000051
> -rw-rw----  1 mysql  mysql  1073745119 Sep 17 04:35 mysql-bin.000052
> -rw-rw----  1 mysql  mysql  1073747657 Sep 22 04:26 mysql-bin.000053
> -rw-rw----  1 mysql  mysql  1073744456 Sep 27 03:28 mysql-bin.000054
> -rw-rw----  1 mysql  mysql   986782722 Oct  1 07:32 mysql-bin.000055
> -rw-rw----  1 mysql  mysql  1073742442 Oct  6 04:18 mysql-bin.000056
> -rw-rw----  1 mysql  mysql   536487381 Oct  8 07:45 mysql-bin.000057
> -rw-r-----  1 mysql  mysql         209 Oct  6 04:18 mysql-bin.index
> 
> Do you have any idea why? Or if /usr/local/mysql/ is a correct
> location for my.cnf file? Perhaphs it should go to /usr/local/etc/ ?
> 
> If it matters, I use
> $ pkg_info -Ix mysql-s
> mysql-server-5.0.67 Multithreaded SQL database (server)
> from ports.
> 

What's the output from running this SQL:  "SHOW BINARY LOGS ;" ?

MySQL can get confused if you physically move the data directory
-- especially if you set the logging directory explicitly in my.cnf 
rather than just accepting the default location.

Look at the mysql-bin.index file -- it's pure ascii text -- and
it should list all the known binlog file names.  If what you have doesn't correspond with reality, then stop the mysql process, edit
that index file back into sanity and restart mysql.  Easy.

	Cheers,

	Matthew
	


-- 
Dr Matthew J Seaman MA, D.Phil.                   7 Priory Courtyard
                                                  Flat 3
PGP: http://www.infracaninophile.co.uk/pgpkey     Ramsgate
                                                  Kent, CT11 9PW

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 258 bytes
Desc: OpenPGP digital signature
Url : http://lists.freebsd.org/pipermail/freebsd-questions/attachments/20081008/e56f0c68/signature.pgp