The New England Database Summit is an all day conference-style event where participants from the research community and industry in the New England area can come together to present ideas and discuss their research and experiences working with on data-related problems. It is an academic conference with applications to real life, and includes any type of database.
The 5th annual NEDB will be held in Cambridge, MA MIT (in 32-123) on Friday, February 3, 2012. Anyone who would like is welcome to present a poster (registration required), or submit a short paper for review. We plan to accept 8–10 papers for presentation (15 minutes) at the meeting. All posters will be accepted.
For more details, and to register and / or upload a paper, see:
http://db.csail.mit.edu/nedbday12/
The New England Database Summit is an all day conference-style event where participants from the research community and industry in the New England area can come together to present ideas and discuss their research and experiences working with on data-related problems. It is an academic conference with applications to real life, and includes any type of database.
The 5th annual NEDB will be held in Cambridge, MA MIT (in 32-123) on Friday, February 3, 2012. Anyone who would like is welcome to present a poster (registration required), or submit a short paper for review. We plan to accept 8–10 papers for presentation (15 minutes) at the meeting. All posters will be accepted.
For more details, and to register and / or upload a paper, see:
http://db.csail.mit.edu/nedbday12/
From time to time, organizations want to know if there are any users that are not used. For clients using MySQL 5.1 and up, that can handle a 1% overhead for general logging, we will enable the general log for a period of time and analyze the users that connect.
Note: we have some extremely busy clients, and we very rarely have a problem turning the general log on, other than making sure we’re rotating and compressing logs so we do not run out of disk space.
Once we have the logs, I run this little perl tool I made — I call it genlog_users.pl:
#!/usr/bin/perl
my $infile=$ARGV[0];
my %seen=();
my @uniq=();
open (INPUT, “<$infile”);
while (<INPUT>) {
my $line=$_;
if ($line=~/Connect/) {
if ($line=~/(\S*@\S*)/) { push(@uniq, $1) unless $seen{$1}++; }
} # end if line matches Connect
}
close INPUT;
open (OUTPUT, “>>..users.txt”);
$,=”\n”;
print OUTPUT (keys %seen);
print OUTPUT (“\n”);
close OUTPUT;
———-
I hope it is useful for whoever stumbles on this; I know it has been useful for me in the past — it’s just doing some string matching, and I bet if I used Python it would be done in half the lines, but it’s already fewer than 20 lines, so it’s pretty small to begin with.
From time to time, organizations want to know if there are any users that are not used. For clients using MySQL 5.1 and up, that can handle a 1% overhead for general logging, we will enable the general log for a period of time and analyze the users that connect.
Note: we have some extremely busy clients, and we very rarely have a problem turning the general log on, other than making sure we’re rotating and compressing logs so we do not run out of disk space.
Once we have the logs, I run this little perl tool I made — I call it genlog_users.pl:
#!/usr/bin/perl
my $infile=$ARGV[0];
my %seen=();
my @uniq=();
open (INPUT, “<$infile”);
while (<INPUT>) {
my $line=$_;
if ($line=~/Connect/) {
if ($line=~/(\S*@\S*)/) { push(@uniq, $1) unless $seen{$1}++; }
} # end if line matches Connect
}
close INPUT;
open (OUTPUT, “>>..users.txt”);
$,=”\n”;
print OUTPUT (keys %seen);
print OUTPUT (“\n”);
close OUTPUT;
———-
I hope it is useful for whoever stumbles on this; I know it has been useful for me in the past — it’s just doing some string matching, and I bet if I used Python it would be done in half the lines, but it’s already fewer than 20 lines, so it’s pretty small to begin with.
Percona Live: London was a rousing success for PalominoDB. I was sad that I could not attend, but I got a few people who sent “hellos” to me via my coworkers. But on to the most important stuff — slides from our presentations are online!
René Cannao spoke about MySQL Backup and Recovery Tools and Techniques (description) – slides (PDF)
Enjoy!
Percona Live: London was a rousing success for PalominoDB. I was sad that I could not attend, but I got a few people who sent “hellos” to me via my coworkers. But on to the most important stuff — slides from our presentations are online!
René Cannao spoke about MySQL Backup and Recovery Tools and Techniques (description) – slides (PDF)
Enjoy!
Monitoring systems are a great thing, and we rely heavily on Nagios here at PalominoDB. We also rely heavily on xtrabackup for (mostly) non-locking, “warm” backups of MySQL. In order to get a consistent backup with a proper binlog position on a slave, xtrabackup stops replication for a short period of time. If the monitoring system catches this, the pager will go off, and usually in the middle of the night.
Nagios can have specific windows when it is on or off for a particular host or service, but you have to remember that when you change the time the backup runs. I prefer to call a script from cron, just before I call the backup script, so that I can easily see that they are related.
Note that this script works even if you have Nagios password protected with .htaccess, because wget allows for a username and password. This script is not perfect – there is not a lot of error checking, and log files are hard-coded. But it does what it needs to.
The script is called like this:
./scheduleDowntimeByService service length host
Sample cron entries (note that the service name depends on what you are checking and the service names themselves):
00 5 * * 0 /home/palominodb/bin/scheduleDowntimeByService.pl MySQL+Replication+Lag 3600 hostname
00 5 * * 0 /home/palominodb/bin/scheduleDowntimeByService.pl MySQL+Slave+IO+Thread 3600 hostname
00 5 * * 0 /home/palominodb/bin/scheduleDowntimeByService.pl MySQL+Slave+SQL+Thread 3600 hostname
Here is the script itself:
cat scheduleDowntimeByService.pl
#!/usr/bin/perl
use strict;
use POSIX;
my $service = shift @ARGV;
my $length = shift @ARGV;
my $host = shift @ARGV;
unless ($length ) {
$length = 3600;
}
my $startTime = time();
my $endTime = $startTime + $length;
my $nagios_start = POSIX::strftime(“%m-%d-%Y %H:%M:00”, localtime($startTime));
my $nagios_end = POSIX::strftime(“%m-%d-%Y %H:%M:00”, localtime($endTime));
$nagios_start =~ s@:@%3A@g;
$nagios_start =~ s@-@%2D@g;
$nagios_start =~ s@ @%20@g;
$nagios_end =~ s@:@%3A@g;
$nagios_end =~ s@-@%2D@g;
$nagios_end =~ s@ @%20@g;
my $URL = ‘https://monitoring.company.com/nagios//cgi-bin/cmd.cgi?fixed=1&start_time=’ . $nagios_start . ‘&end_time=’ . $nagios_end . ‘&cmd_typ=56&cmd_mod=2&host=’ . $host . ‘&service=’ . $service . ‘&com_data=Backups&btnSubmit=Commit’;
my $cmd = “/usr/bin/wget –quiet –user=user –password=PASS -O /tmp/nagios_downtime.html ‘$URL'”
;
open ( L, “>>/tmp/nagios_downtime.log” );
print L print $cmd . “\n”;
print L `$cmd`;
close L;
Monitoring systems are a great thing, and we rely heavily on Nagios here at PalominoDB. We also rely heavily on xtrabackup for (mostly) non-locking, “warm” backups of MySQL. In order to get a consistent backup with a proper binlog position on a slave, xtrabackup stops replication for a short period of time. If the monitoring system catches this, the pager will go off, and usually in the middle of the night.
Nagios can have specific windows when it is on or off for a particular host or service, but you have to remember that when you change the time the backup runs. I prefer to call a script from cron, just before I call the backup script, so that I can easily see that they are related.
Note that this script works even if you have Nagios password protected with .htaccess, because wget allows for a username and password. This script is not perfect – there is not a lot of error checking, and log files are hard-coded. But it does what it needs to.
The script is called like this:
./scheduleDowntimeByService service length host
Sample cron entries (note that the service name depends on what you are checking and the service names themselves):
00 5 * * 0 /home/palominodb/bin/scheduleDowntimeByService.pl MySQL+Replication+Lag 3600 hostname
00 5 * * 0 /home/palominodb/bin/scheduleDowntimeByService.pl MySQL+Slave+IO+Thread 3600 hostname
00 5 * * 0 /home/palominodb/bin/scheduleDowntimeByService.pl MySQL+Slave+SQL+Thread 3600 hostname
Here is the script itself:
cat scheduleDowntimeByService.pl
#!/usr/bin/perl
use strict;
use POSIX;
my $service = shift @ARGV;
my $length = shift @ARGV;
my $host = shift @ARGV;
unless ($length ) {
$length = 3600;
}
my $startTime = time();
my $endTime = $startTime + $length;
my $nagios_start = POSIX::strftime(“%m-%d-%Y %H:%M:00”, localtime($startTime));
my $nagios_end = POSIX::strftime(“%m-%d-%Y %H:%M:00”, localtime($endTime));
$nagios_start =~ s@:@%3A@g;
$nagios_start =~ s@-@%2D@g;
$nagios_start =~ s@ @%20@g;
$nagios_end =~ s@:@%3A@g;
$nagios_end =~ s@-@%2D@g;
$nagios_end =~ s@ @%20@g;
my $URL = ‘https://monitoring.company.com/nagios//cgi-bin/cmd.cgi?fixed=1&start_time=’ . $nagios_start . ‘&end_time=’ . $nagios_end . ‘&cmd_typ=56&cmd_mod=2&host=’ . $host . ‘&service=’ . $service . ‘&com_data=Backups&btnSubmit=Commit’;
my $cmd = “/usr/bin/wget –quiet –user=user –password=PASS -O /tmp/nagios_downtime.html ‘$URL'”
;
open ( L, “>>/tmp/nagios_downtime.log” );
print L print $cmd . “\n”;
print L `$cmd`;
close L;
Last week, I won USD$100*. Gazzang is giving away $100 every Friday in October, in honor of National Cyber Security Awareness Month. There are only 2 more weeks left — all you have to do is tweet @Gazzang_Inc one way to improve your own cyber security.
My winning tweet:
one way is to secure MySQL! With Gazzang or the tips in or .
The tip can be any kind of cyber security. I believe you have to tweet on Friday, though…..so wait until tomorrow!
Last week, I won USD$100*. Gazzang is giving away $100 every Friday in October, in honor of National Cyber Security Awareness Month. There are only 2 more weeks left — all you have to do is tweet @Gazzang_Inc one way to improve your own cyber security.
My winning tweet:
one way is to secure MySQL! With Gazzang or the tips in or .
The tip can be any kind of cyber security. I believe you have to tweet on Friday, though…..so wait until tomorrow!
(Tune in tomorrow where I tell you how you can win USD$100, like I did last week!)
Not signed up for Percona Live:London and need some motivation to do so? How about a £40.00 discount? That means the Expo Hall is free, regular registration is £260.00 and the tutorials + conference price is £500.00. Use discount code “PDBUK”.
In just 3 weeks, RightScale is having a cloud conference in Santa Clara, CA Nov 8-9. There is a Zero-to-cloud training on Tuesday, November 8th, and you can get a 25% discount on the training by using code “RSCONF25”.
RightScale and Eucalyptus are teaming up to provide a training, too — on Thursday, November 10th. If the name Eucalyptus sounds familiar, it is because former MySQL employees Mårten Mickos and Mark Atwood work there. There is a 25% discount off that training by using code “RSEUCT25”.
(Tune in tomorrow where I tell you how you can win USD$100, like I did last week!)
Not signed up for Percona Live:London and need some motivation to do so? How about a £40.00 discount? That means the Expo Hall is free, regular registration is £260.00 and the tutorials + conference price is £500.00. Use discount code “PDBUK”.
In just 3 weeks, RightScale is having a cloud conference in Santa Clara, CA Nov 8-9. There is a Zero-to-cloud training on Tuesday, November 8th, and you can get a 25% discount on the training by using code “RSCONF25”.
RightScale and Eucalyptus are teaming up to provide a training, too — on Thursday, November 10th. If the name Eucalyptus sounds familiar, it is because former MySQL employees Mårten Mickos and Mark Atwood work there. There is a 25% discount off that training by using code “RSEUCT25”.
I forget from whom I first learned about kSar, but I am in debt to that person once again. I first learned about it about a year ago, and it has been extremely useful whenever I am trying to debug system where I do not have access to trending graphs. kSar is an open source graphing tool for Linux, Mac OS X and Solaris sar output. Graphs can be generated and exported to many different formats (JPG, PNG, and even a PDF with all the graphs in it).
By simply copying a few files from /var/log/sa (at least that’s the default on Linux, and of course it does depend on sar being installed and running, but 95% of the time it is), running kSar and choosing the data file, you get beautiful graphs. Try it right now, it will take you less than 10 minutes. (Note, I have a Mac, and I know the client graphical program works on Linux/Unix/Solaris, but I have no clue if it works on Windows — there is a run.sh script, but it just calls a jar file, so I am pretty sure it will work on Windows.)
I copy the /var/log/sa/sar* files to my laptop, then cat them together (usually cd /path/to/sa, cat sar* > alldays), then run kSar and click “Data -> Load from text file”. It is just that simple.
I forget from whom I first learned about kSar, but I am in debt to that person once again. I first learned about it about a year ago, and it has been extremely useful whenever I am trying to debug system where I do not have access to trending graphs. kSar is an open source graphing tool for Linux, Mac OS X and Solaris sar output. Graphs can be generated and exported to many different formats (JPG, PNG, and even a PDF with all the graphs in it).
By simply copying a few files from /var/log/sa (at least that’s the default on Linux, and of course it does depend on sar being installed and running, but 95% of the time it is), running kSar and choosing the data file, you get beautiful graphs. Try it right now, it will take you less than 10 minutes. (Note, I have a Mac, and I know the client graphical program works on Linux/Unix/Solaris, but I have no clue if it works on Windows — there is a run.sh script, but it just calls a jar file, so I am pretty sure it will work on Windows.)
I copy the /var/log/sa/sar* files to my laptop, then cat them together (usually cd /path/to/sa, cat sar* > alldays), then run kSar and click “Data -> Load from text file”. It is just that simple.