mysql maintenance

[Boss Hoss]

I'm struggling with my /var directory (partition) filling up and I only a couple sites on this server but they are serious mysql hogs.

So I'm looking in /var/lib/mysql and I see a TON of file named "server-bin.#" that have been generated for 2+ years and some of them are taking up 30MB or so each.

What are these files for? Is there a PURGE function to help cleanup these (phpMyAdmin function)?

I also see an error file named "server.domain.com.err" that is 350MB+ .. is this file purgeable?

[newspire]

http://dev.mysql.com/doc/mysql/en/binary-log.html
server-bin files are binary logs. They are used for replication. If you are using replication then you are not purging your binary logs. If you are not using replication then you probably don't need to have binary logging turned on.

Yes there are commands to purge the files. RESET MASTER deletes all files and PURGE MASTER to purge individual files.

http://dev.mysql.com/doc/mysql/en/error-log.html
.err files are MySQL Error files.
FLUSH LOGS will change the name of the file and then you can delete it. However, I would look at the end of the file and see what is causing all the errors. Sounds like you have something spewing errors.

[Boss Hoss]

The logs are created when I run a mysql backup script via cron at 5:00 am. The command creating the bin files is using the "--flushlog" directive:

mysqlhotcopy -q --user=$USERNAME --password=$PASSWORD --allowold --keepold --flushlog "$DB" "$BACKUPDIR"

Now, maybe I should be using "--resetmaster" instead? I have no idea what to do with the bin files that are created, and I know not much about why I need to do either.
--flushlog flush logs once all tables are locked
--resetmaster reset the binlog once all tables are locked

I just know the script usually takes about 18min to run. However on bad days where I see larger bin files created that day it takes like 48 minutes.

As for the server.err file. It's 370MB and haven't been able to get a text editor to open it to the end to see what my latest errors are. It started in June 2004, around the time the server was redeployed and I know there were tons of problems with "max_connections" which appears in the first 7000+ lines over the first 2 days.

So I have cleared that file so I can at least read it. I did a mysqld restart for giggles and get the following:

050611 23:45:50 mysqld started
050611 23:45:50 Can't start server: Bind on TCP/IP port: Address already in use
050611 23:45:50 Do you already have another mysqld server running on port: 3306 ?
050611 23:45:50 Aborting
050611 23:45:50 /usr/sbin/mysqld-max: Shutdown Complete
050611 23:45:50 mysqld ended

Figures, I had to do a reboot on the server after futsing and the latest error ssince restarting are:

050612 00:06:50 mysqld started
050612 0:06:57 InnoDB: Started
/usr/sbin/mysqld-max: ready for connections.
Version: '4.0.20-Max-log' socket: '/var/lib/mysql/mysql.sock' port: 3306
050612 0:08:37 Aborted connection 26 to db: 'table' user: 'user' host: `localhost' (Got an error reading communication packets)
050612 0:08:38 Aborted connection 5 to db: 'table' user: 'user' host: `localhost' (Got an error reading communication packets)

But this is a hacked box..we had troubles with Ensim hosting app and I had a SysAdmin friend go in and at least "break" the Ensim app so the box would run with the few virtual hosts I had to have running.

I have a new SuperMicro 1U server sitting here with SuSe9.2 if I can just get the time to swap servers out.

[Boss Hoss]

Here are some more specifics that I had posted on phpBB. They'll tell me its a MySQL problem, someone here will say its a phpBB problem etc:

I am seeing errors listed in my server.err file in the /var/lib/mysql directory like:

050612  7:41:47  Aborted connection 13008 to db: '******_phpbb' user: '******' host: `localhost' (Got an error reading communication packets)
 050612  7:41:48  Aborted connection 12682 to db: '******_phpbb' user: '******' host: `localhost' (Got an error reading communication packets)
 050612  7:41:49  Aborted connection 13003 to db: '******_phpbb' user: '******' host: `localhost' (Got an error reading communication packets)
 050612  7:41:50  Aborted connection 12635 to db: '******_phpbb' user: '******' host: `localhost' (Got an error reading communication packets)
 050612  7:41:57  Aborted connection 13036 to db: '******_phpbb' user: '******' host: `localhost' (Got an error reading communication packets)
 050612  7:42:04  Aborted connection 13039 to db: '******_phpbb' user: '******' host: `localhost' (Got an error reading communication packets)

My error_log doesn't show anything related to a php file.

My access_log for this time shows:

66.190.224.24 - - [12/Jun/2005:00:07:40 -0400] "GET /scripts/mm_menu.js HTTP/1.1" 200 3206 "-" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)"
 66.190.224.24 - - [12/Jun/2005:00:07:41 -0400] "GET /classifieds/styles/generic.css HTTP/1.1" 200 1623 "-" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)"
 66.190.224.24 - - [12/Jun/2005:00:07:41 -0400] "GET /forums/templates/subSilver/subSilver.css HTTP/1.1" 200 8363 "-" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)"
 66.190.224.24 - - [12/Jun/2005:00:07:41 -0400] "GET /styles.css HTTP/1.1" 200 934 "-" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)"
 66.190.224.24 - - [12/Jun/2005:00:07:41 -0400] "GET /images/shim.gif HTTP/1.1" 200 43 "-" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)"
 66.190.224.24 - - [12/Jun/2005:00:07:41 -0400] "GET /images/header/header_icon.gif HTTP/1.1" 200 4915 "-" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)"
 66.190.224.24 - - [12/Jun/2005:00:07:41 -0400] "GET /images/header/header_bgd.gif HTTP/1.1" 200 35739 "-" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)"
 66.190.224.24 - - [12/Jun/2005:00:07:41 -0400] "GET /images/welcome.jpg HTTP/1.1" 200 41782 "-" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)"
 66.190.224.24 - - [12/Jun/2005:00:07:42 -0400] "GET /images/ucm_watermark.jpg HTTP/1.1" 200 6107 "-" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)"

I have had some people using Windows WinXP SV1 say they can't read the classifieds on my site they say they get only a ">" printed. Now I am using the phpBB user authorization scheme for the classifieds so it would report a problem on the phpBB table.

Any ideas?

[longneck]

there's a *nix program called last that will let you view the last x bytes or lines of a file. you can use that on your 370 mb log file.

and unless your .js and .css files are actually php files that access your mysql server, then those lines you posted from your apache log have nothing to do with the lines from the mysql server log.