RE: OT: MySQL Healthcheck

From: CRISLER, JON A <JC1706_at_att.com>
Date: Tue, 26 Mar 2013 05:44:27 +0000
Message-ID: <9F15274DDC89C24387BE933E68BE3FD3082FACAA_at_MISOUT7MSGUSR9D.ITServices.sbc.com>

You can implement a simple table check program using mysqlcheck. See the analyze and optimize parameters. Analyze is something like dbms_stats, optimize is like a table reorg. Also make sure expire_logs is in place if you are using innodb binary logging (like cleaning up archive redo logs). Mysqlcheck optimize can be done daily or once a week, depending on your table changes. I would put in a mysqldump regardless of whatever other backup program you are using (if any). Mysqldump is like oracle export. MySQL 5.5 introduces a newish hot backup program as well (it was previously only available in a extra cost program in earlier versions).

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of William Muriithi Sent: Monday, March 25, 2013 6:38 PM
To: cicciuxdba_at_gmail.com
Cc: oracle-l-freelists
Subject: Re: OT: MySQL Healthcheck

Alan,
> Now, my question to the list is what does a MySQL healthcheck entail?
>

Depends with your setup? Mind describing it a bit for us? Single instance? Size? Master - slave? Master - master? Shared storage?
> I checked that the data is in a separate filesystem and that it has
> plenty of room, and some my.cnf parameters, but I'm worried about
> transaction log rotation and stuff like that. Not to mention I didn't even
Transaction logs are self maintained. It cleans them after commiting data to storage. If you want to back them up look for ibdata01 and ibdata02 file.
> start checking if they are using innodb or if there are any MyISAM

Start there then. It will drive the backup strategy that will fit your system best.

> tables... not to mention there's no backup policy implemented yet which
> means we'll have to consider backups at a later time (which may require
> additional software).

You don't need to backup software like rman. If you are running on lvm, use it to snapshot your data directory and copy them over using rsync. Alternatively, do a mysqldump if the database is not too large.

Other things I would check

What SQL_mode is it using? Turn on strict for good data integrity

If older than MySQL 5.5, enable innodb plugin

If the application is generating utf data, ensure the database is saving in the same format

Adjust innodb_buffer_pool_size to match your hardware if using innodb extensively or key_buffer if your database is primarily myism based.

Enable slow queries for pointers when something is really none optimal
>
> Thanks in advance
> Alan.-
>

William
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 26 2013 - 06:44:27 CET