{themattreid}

MySQL DBA | Linux'er

Archive for February, 2008

Basic requirements of production database environments

Feb 26

Posted by admin in General | No Comments

I just need to get some basics off of my chest here, it’s by no means a full list but it’s the most basic list I can think of to start with, and it’s basic because I am surprised by some of the slop I’ve seen in production environments.

1. Highly available server clusters – this is different than load balancing cluster, if confused see here.

2. Disaster recovery

-> this means daily,weekly,monthly backups as well as off site backups, and tertiary backups as well as a plan to get those backups imported and running in production as fast as possible. Backups should have consistency checking when they are created.

3. Security

-> perimeter on the network, VLAN’d databases from the web/app servers, firewall, ACLs, etc

-> system level: strong passwords on OS and database accounts (no blank passwords – that *should* be obvious but you’d be surprised what I’ve run into), file permissions, encryption of sensitive database information.

4. Monitoring: monitor everything possible. Log files, disk partitions, service ports, service details (traffic for a service, memory used, tuning parameters: query cache usage, etc), CPU/RAM usage, logged in users, and most importantly being alerted about monitored services. If you’re not getting called when something has passed a threshold, you need to pay more attention to the infrastructure.

Tags: , , ,

For the love of god please use the following on high traffic servers

Feb 20

Posted by admin in General, Replication | 1 Comment

Let’s begin by assuming you have a server that runs MySQL and lots and lots of traffic flows through it everyday, let’s say… something like 50% of the size of the partition that the mysql binary logs are written to is on, then we will assume the binary log is turned on. Then we assume that expire_logs_days is not set.

What happens? Nagios/etc alerts that the partition is reaching a usage threshold because – low and behold the binary logs are filling up the partition. Tuning this variable is also important. It may need to be set to as low as 1 day, in which case I would say we need a bigger partition for binary logs, but setting it so low can cause replication problems if the slave(s) gets behind more than 1 day – god help us if it does – then those binary logs that the slave is reading are no longer available, and rebuilding replication will be next on the task list. (or using maatkit)

While I’m at it here are some good ones for relieving tension. By no means is this a full list but some things on the top of my head at the moment.


expire_logs_days = x
myisam_recover
skip_bdb
sql_mode = NO_AUTO_CREATE_USER
max_binlog_size = x
long_query_time = x
log_slow_queries
log_warnings
transaction-isolation=REPEATABLE-READ (or SERIALIZABLE)

SQL commands for a fresh install

Feb 19

Posted by admin in General | 3 Comments

As a rule I always execute the following commands on a fresh database installation. Then for each user that is granted privileges, of course they are given a password and the host permissions are locked down as much as possible for their needs, alternately table and column privs as well. I’m not going to get into the parts the manual covers, but rather mention a couple of things on my mind.

First the initial commands:


mysql> use mysql
mysql> delete from user where User='';
mysql> delete from db where User='';
mysql> update user set Password=password('password_here') where User='root';
mysql> flush privileges;

However, one thing I’ve noticed is that when you hand over a server to someone that doesn’t necessarily follow your same understanding or regard to user privilege security, bad things can happen. Such as users created without a password. It is possible to grant a user privileges, even global level privileges without a password. Why this would ever be considered a “feature” from the code point of view – or the default state of the SQL mode (which handles the GRANT command as well as other things) is beyond me but there is a good way to make sure your users cannot be created without a password: NO_AUTO_CREATE_USER SQL MODE. So just add this to the cnf:


[mysqld]
sql_mode=NO_AUTO_CREATE_USER

Or issue the global command at runtime:

mysql> set global sql_mode=NO_AUTO_CREATE_USER;

To further lock down the server it’s best to just begin with sql_mode=TRADITIONAL but that’s a whole new discussion and chances are some applications will not enjoy using this mode and cease to function because of improper design. So remember, set a password :)

Tags: , , , ,

Monolith MySQL DBA Console – version 1.2a Released

Feb 16

Posted by admin in Monolith | No Comments

Ok folks, just after testing out some new changes to this week’s release – I’ve uploaded a new version. Here are the following changes:

Changelog 1.2a
– changes line thickness to 1 for all graphs, 2 was too thick for running at 5 minute intervals for the poller
- added several scripts for running and logging monitor agent and report_generator
- changes talkback sripts to one script instead of 3

Tags: , , ,

One backup script that does it all.

Feb 15

Posted by admin in Backups, Monolith, Scripts | 1 Comment

This integrates with Monolith, but the database update function can be stripped out for use without Monolith. The idea is that this script is a wrapper for mysqldump that does backup file consistency checking, email reporting, file based logging and directory pruning.

I used to have one script for daily, weekly, and monthly all running out of /etc/cron.daily /etc/cron.weekly /etc/cron.monthly – respectively. But maintaining 3 scripts is foolish if one can do everything. So I added some variables to check day of week and day of month to achieve this.

Enjoy the code. Script Link here.

Tags: , , , ,

Project: RSS Feed Storage Using InnoDB #2

Feb 15

Posted by admin in OLAP - DW, Scripts | No Comments

So far so good. I have a bit over two hundred RSS entries logged in the database for testing purposes. Today I changed the table structure for for the title and description to support longer entries. Here is the pertinent code.

Add feed function:

function add_feed($item,$site_id) {
$each_title = $item['title'];
$each_link = $item['link'];
$each_desc = $item['description'];
if(isset($item['guid'])) { $each_guid = $item['guid'];} else { $each_guid="";}
if(isset($item['pubDate'])) { $each_pubDate = $item['pubDate'];} else { $each_pubDate="";}
//print "\n$each_desc\n";
$sql2=sprintf("
INSERT INTO `extrabigassfries`.`feed_items` (
`id` ,
`rss_site_id` ,
`item_title` ,
`item_link` ,
`item_description` ,
`item_guid` ,
`item_pubDate` ,
`Creation_time`
)
VALUES (
NULL , '$site_id', '%s', '%s','%s','$each_guid', '$each_pubDate', NOW( ))",
mysql_real_escape_string($each_title),
mysql_real_escape_string($each_link),
mysql_real_escape_string($each_desc));
//print "$sql2\n";
$state=0;
mysql_query($sql2) or $state=1;
return($state);
}

Table structure

--
-- Table structure for table `feed_items`
--

CREATE TABLE `feed_items` (
`id` bigint(20) NOT NULL auto_increment,
`rss_site_id` int(11) NOT NULL,
`item_title` longtext NOT NULL,
`item_link` varchar(255) NOT NULL,
`item_description` longtext NOT NULL,
`item_guid` varchar(255) NOT NULL,
`item_pubDate` varchar(255) NOT NULL,
`Creation_time` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `rss_site_id` (`rss_site_id`,`item_link`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=208 ;

Monolith MySQL DBA Console – version 1.2 Released

Feb 15

Posted by admin in Monolith | No Comments

I’m pleased to announce that after almost one year since the last release, the new version is available. There are many new features this time around. Originally it was titled Monolith MySQL Backup Manager, but because of the new features it has been changed to the new name. You can download here: http://sourceforge.net/projects/monolith-mysql

Screenshots:

Monitor1 Monitor2 Monitor3

Features:

  1. Graphing / Trending with variable date reporting
    • Query rate + Data growth
    • Connections + Uptime
    • Query cache in + Query cache hit
    • Queries not cached + Queries in cache
    • Memory allocation: max_allocated + max_configured
    • Data transfer: bytes_sent + bytes_received
    • Created temp files + Created temp tables
    • Open tables + Created temp disk tables
    • Threads cached + Threads created + Thread utilization ratio
    • Threads connected + Threads running
  2. Alerting for the following tuning settings
    • Slow Query
    • Long Query
    • Used Connections
    • Thread Status
    • Key Buffer
    • Query Cache
    • Sort Merge Operations
    • Join Merge settings
    • Temporary Tables
    • Open Files
    • Table Cache
    • Table Locks
    • Table Scans
    • InnoDB settings
    • Memory Usage
  3. Talkback backup scripts for local disk backup – scripts report back from remote servers to Monolith server with status of backup. Talkback report per server tab, and itemized for all servers in summarization page.
  4. Data+Index size reporting with data summarization page for all servers
  5. Misc server meta data tracking
  6. Change request feature that show recommended cnf file changes for performance tuning + full audit report in html

Note:

The installation process is a bit involved, and I know of one hangup about the stored procedures – you must connect the monolith user to the IP address of the server and not localhost. Feel free to email me or get me on #mysql as Lumberg if you have any questions or need help installing.

Project: RSS Feed Storage Using InnoDB

Feb 14

Posted by admin in OLAP - DW, Scripts | No Comments

I’ve been coding a couple of scripts that run on 5 minute intervals to grab RSS/Atom feed data from http://mysql-dba.com and import that into a MySQL database. It idea is to create a search function for the site that will look at all past data from the aggregated feeds. Since there are multiple pollers running at different intervals I decided to use Innodb for the read/write nature of the poller/processing scripts.

This is very simple so far – and as such I felt it should be documented from the start unlike many of my other projects. Here’s the feed table that is storing the information from the RSS feeds.


mysql> show create table feed_items\G
*************************** 1. row ***************************
Table: feed_items
Create Table: CREATE TABLE `feed_items` (
`id` bigint(20) NOT NULL auto_increment,
`rss_site_id` int(11) NOT NULL,
`item_title` varchar(255) NOT NULL,
`item_link` varchar(255) NOT NULL,
`item_description` varchar(255) NOT NULL,
`item_guid` varchar(255) NOT NULL,
`item_pubDate` varchar(255) NOT NULL,
`Creation_time` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `rss_site_id` (`rss_site_id`,`item_link`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Tags: , , ,

News from the front lines – Monolith Talkback

Feb 13

Posted by admin in Backups, Monolith | No Comments

I’ve been giving presentations at work each week about MySQL DBA topics. An hour of speaking about the things one does on a daily basis is rather fun, and hopefully spreads the work about how efficient MySQL is to administer and setup, as well as optimize and troubleshoot vs MSSQL and Oracle – for which we also have weekly classes.

Recently I’ve been covering backup and recovery methods, which brings me to my next point – the talkback scripts I have been integrating into my Monolith application. These scripts are wrappers for mysqldump that do extensive error checking for file completion, directory pruning to rotate out old backups per the backup retention period policy, and then report back via email and to a separate database for tracking purposes.

Here is the create table for the reporting aspect. Note: this is part of the Monolith 1.2 release which is still in beta. Stay tuned for the release that includes this, as well as remote backup, graphing for many MySQL utilization properties, alerting, and this section: the talkback local backup scripts.


mysql> show create table hosts_incoming_backup_state\G
*************************** 1. row ***************************
Table: hosts_incoming_backup_state
Create Table: CREATE TABLE `hosts_incoming_backup_state` (
`id` bigint(20) NOT NULL auto_increment,
`host_id` bigint(20) NOT NULL,
`process_status_id` varchar(255) NOT NULL,
`file_name` varchar(255) NOT NULL,
`file_size` bigint(64) NOT NULL,
`exec_time` bigint(32) NOT NULL,
`exec_compress` bigint(32) NOT NULL,
`Creation_time` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

The mysql_talkback script can be found here.

Rotating General Query & Slow Logs

Feb 5

Posted by admin in Backups, Scripts | 6 Comments

Sometimes you need to have the general query log on and even though it causes more disk I/O than you may want, it’s good for troubleshooting. This log can and probably will fill up your disks rather quickly. Then there’s the slow query log – setting log_slow_queries and log_queries_not_using_indexes will write out the queries that take longer than long_query_time to execute, as well as any query not using an index.

So, since MySQL does not apply the expire_logs_days value to these logs – only to the binary log (log_bin), we need another solution. There are probably a bunch of custom scripts out there that do this, but big surprise – we have one as well. This was originally written by Jim Wood until I got my hands on it and made some changes. The changes are listed in the head of the script. This little guy will rotate the logs out to another directory and gzip them. Ideally you’ll run this from cron as such:

#mysql rotate logs
01 * * * * root /usr/local/bin/mysql_rotatelogs > /dev/null 2>&1

The script can be downloaded here.