Permissions

So, Markus Popp’s recent blog entry about trying to give a user permissions to all databases except one got me thinking.

MySQL has grown immensely, and like many products, new features are compatible with old features. . . somewhat.

Review/baseline:

For current versions of MySQL, permissions are granted and revoked by the GRANT and REVOKE commands. In older versions, administrators had to muck with the access control tables by hand, and then FLUSH PRIVILEGES to enable the new access controls.

The blog entry got me thinking. Currently, if you want to remove all rights from a user, including the ability to login, you have to REVOKE privileges and then DELETE from the mysql.user table. And then, of course, FLUSH PRIVILEGES because you manually changed that table.

It would be great if an administrator never HAD to play with the mysql.user table.

Now, that seems like a small request. But there also does not seem to be a SHOW ALL GRANTS command. Currently an administrator has to run SELECT host,user FROM mysql.user to see who might have access, and then run a SHOW GRANTS command to see who has access to what.

An alternative to that is to check out the other tables in the mysql database. However, what I’d like to see is the ability to see privileges easier than checking out tables with over a dozen fields.

This would include extending the SHOW GRANTS syntax to be able to show grants for all users, and showing grants for a user (for instance, SHOW ALL GRANTS FOR USERNAME="sheeri").

And, of course, similar functionality in REVOKE syntax. I’d love to revoke grants from a user at all their entries with a touch of a button.

In the current system, administrators get punished for having tight security and only allowing what’s necessary. There are more entries in the mysql.users table, and adding, seeing, and removing privileges requires many steps.

Perhaps what I am describing is overkill for the database? Maybe I am just describing an admin interface? But I really think that having commands to do half the functionality (seeing, granting and revoking per user/hostname combination) and having to play with tables for the other half (revoking login access, finding out the user/hostname combination) is somewhat lacking.

Or is this a function of the fact that SHOW GRANTS is available to many users by default and access to the mysql database is not? I could see security implications if the syntax was extended. . .but that’s easy enough to do by extending the access rights and adding a SHOW GRANTS right (if there is not already one)

(by the way, I’m using 4.1 and haven’t had a chance to play with 5.0 or 5.1, so if this functionality is in a later version, or coming soon, please let me know)

So, Markus Popp’s recent blog entry about trying to give a user permissions to all databases except one got me thinking.

MySQL has grown immensely, and like many products, new features are compatible with old features. . . somewhat.

Review/baseline:

For current versions of MySQL, permissions are granted and revoked by the GRANT and REVOKE commands. In older versions, administrators had to muck with the access control tables by hand, and then FLUSH PRIVILEGES to enable the new access controls.

The blog entry got me thinking. Currently, if you want to remove all rights from a user, including the ability to login, you have to REVOKE privileges and then DELETE from the mysql.user table. And then, of course, FLUSH PRIVILEGES because you manually changed that table.

It would be great if an administrator never HAD to play with the mysql.user table.

Now, that seems like a small request. But there also does not seem to be a SHOW ALL GRANTS command. Currently an administrator has to run SELECT host,user FROM mysql.user to see who might have access, and then run a SHOW GRANTS command to see who has access to what.

An alternative to that is to check out the other tables in the mysql database. However, what I’d like to see is the ability to see privileges easier than checking out tables with over a dozen fields.

This would include extending the SHOW GRANTS syntax to be able to show grants for all users, and showing grants for a user (for instance, SHOW ALL GRANTS FOR USERNAME="sheeri").

And, of course, similar functionality in REVOKE syntax. I’d love to revoke grants from a user at all their entries with a touch of a button.

In the current system, administrators get punished for having tight security and only allowing what’s necessary. There are more entries in the mysql.users table, and adding, seeing, and removing privileges requires many steps.

Perhaps what I am describing is overkill for the database? Maybe I am just describing an admin interface? But I really think that having commands to do half the functionality (seeing, granting and revoking per user/hostname combination) and having to play with tables for the other half (revoking login access, finding out the user/hostname combination) is somewhat lacking.

Or is this a function of the fact that SHOW GRANTS is available to many users by default and access to the mysql database is not? I could see security implications if the syntax was extended. . .but that’s easy enough to do by extending the access rights and adding a SHOW GRANTS right (if there is not already one)

(by the way, I’m using 4.1 and haven’t had a chance to play with 5.0 or 5.1, so if this functionality is in a later version, or coming soon, please let me know)

Now You Can Read Books BEFORE Publication!

This looks neat, however they are charging a fee. Even if you’re already a Safari subscriber. . . that seems like the only bad part of the idea.

The following is a release from O’Reilly:

O’Reilly’s Safari Books Online has just announced a new service called Rough Cuts that gives you early access to content on cutting-edge technologies months before it’s published. Rough Cuts allows you to purchase work-in-progress manuscripts of selected titles. You’ll even have the chance to shape the final product by sending feedback to the author and editors. The beta version just debuted with four works-in-progress covering Ajax, Ruby, and Flickr. For more information, go to:

http://www.oreilly.com/roughcuts/

Titles now available:
Ajax Hacks: Rough Cuts Version
http://www.oreilly.com/catalog/ajaxhks/

Flickr Hacks: Rough Cuts Version
http://www.oreilly.com/catalog/flickrhks/

Ruby Cookbook: Rough Cuts Version
http://www.oreilly.com/catalog/rubyckbk/

Ruby on Rails: Up and Running: Rough Cuts Version
http://www.oreilly.com/catalog/rubyrails/

Rough Cuts FAQ
http://www.oreilly.com/roughcuts/faq.csp

This looks neat, however they are charging a fee. Even if you’re already a Safari subscriber. . . that seems like the only bad part of the idea.

The following is a release from O’Reilly:

O’Reilly’s Safari Books Online has just announced a new service called Rough Cuts that gives you early access to content on cutting-edge technologies months before it’s published. Rough Cuts allows you to purchase work-in-progress manuscripts of selected titles. You’ll even have the chance to shape the final product by sending feedback to the author and editors. The beta version just debuted with four works-in-progress covering Ajax, Ruby, and Flickr. For more information, go to:

http://www.oreilly.com/roughcuts/

Titles now available:
Ajax Hacks: Rough Cuts Version
http://www.oreilly.com/catalog/ajaxhks/

Flickr Hacks: Rough Cuts Version
http://www.oreilly.com/catalog/flickrhks/

Ruby Cookbook: Rough Cuts Version
http://www.oreilly.com/catalog/rubyckbk/

Ruby on Rails: Up and Running: Rough Cuts Version
http://www.oreilly.com/catalog/rubyrails/

Rough Cuts FAQ
http://www.oreilly.com/roughcuts/faq.csp

Hacked!

If you store a user’s User-Agent and use it again, make sure you scrub that data first.

[If you store anything, make sure you scrub it. Of course, this isn’t user-inputted data, it’s data that the server gets from the client’s browser.]

We were hacked? abused? today by a member who had javascript in place of his User Agent. A very clever hack. However, we have learned our lesson.

Here’s hoping you do, too.

If you store a user’s User-Agent and use it again, make sure you scrub that data first.

[If you store anything, make sure you scrub it. Of course, this isn’t user-inputted data, it’s data that the server gets from the client’s browser.]

We were hacked? abused? today by a member who had javascript in place of his User Agent. A very clever hack. However, we have learned our lesson.

Here’s hoping you do, too.

Backups Video Now Downloadable

The Google Video for the MySQL Backups presentation stops after about 13 minutes, so I’ve put it up as a download at:

http://www.technocation.org/videos/BostonMySQLMeetupJanuary-mp4.mov

Many of the questions brought up by the discussion of the summary slide are answered in the presentation.

As always, I encourage feedback — both technical as well as presentational (ie, the slides were not understandable, you talk to fast, etc).

The slides are up at:

http://sheeri.net/presentations/MySQLbackups.swf — 125 Kb flash
or
http://sheeri.net/presentations/MySQLbackups.pdf — 2 Mb PDF

Why didn’t I think of this? Tom Limoncelli was nice enough to create a livejournal feed for this site — http://www.livejournal.com/users/sheericom_feed/

Direct link to add as a ‘friend’ if you’re logged in — http://www.livejournal.com/friends/add.bml?user=sheericom_feed

Why didn’t I think of this? Tom Limoncelli was nice enough to create a livejournal feed for this site — http://www.livejournal.com/users/sheericom_feed/

Direct link to add as a ‘friend’ if you’re logged in — http://www.livejournal.com/friends/add.bml?user=sheericom_feed

The MySQL User Group meeting on Monday, January 9th will focus on backups — I will give a 30-45 minute presentation on backups and we will then delve into a discussion — how folks within the group are backing up, how they’re not backing up, how they’d like to back up. If there’s time, we’ll do a “lessons learned from backup horror stories”.

Afterwards, we’ll head a few doors down to Boston Beer Works for more chatting.

You can see a video of last month’s presentation by Philip Antionades of MySQL AB on the new features in MySQL 5.0 at Google Video:

http://video.google.com/videosearch?q=Mysql+5.0+by+Philip+Antoniades

(we will video this month’s presentation but not the discussion)

More details, including the event location and directions, are at: http://mysql.meetup.com/137/events/

Any questions, comments, etc should be sent to me. We’re currently looking for a space better than North Station — something around MIT might be nice, and we’re trying to get an MIT grad student/faculty/staff member/organization to sponsor it. (We want a location that’s good for people driving AND taking the T, and when the Garden has an event, it’s not feasible to park there). If you can help out, please let me know.

(You may forward this announcement to other groups, lists, blogs, whatever.)

I’m quite excited about the presentation, as I’m writing down a lot of information from different sources that really wants to be together, but as far as I can tell is not yet, at least not in a public place.
Why didn’t I think of this? Tom Limoncelli was nice enough to create a livejournal feed for this site — http://www.livejournal.com/users/sheericom_feed/

Direct link to add as a ‘friend’ if you’re logged in — http://www.livejournal.com/friends/add.bml?user=sheericom_feed

The MySQL User Group meeting on Monday, January 9th will focus on backups — I will give a 30-45 minute presentation on backups and we will then delve into a discussion — how folks within the group are backing up, how they’re not backing up, how they’d like to back up. If there’s time, we’ll do a “lessons learned from backup horror stories”.

Afterwards, we’ll head a few doors down to Boston Beer Works for more chatting.

You can see a video of last month’s presentation by Philip Antionades of MySQL AB on the new features in MySQL 5.0 at Google Video:

http://video.google.com/videosearch?q=Mysql+5.0+by+Philip+Antoniades

(we will video this month’s presentation but not the discussion)

More details, including the event location and directions, are at: http://mysql.meetup.com/137/events/

Any questions, comments, etc should be sent to me. We’re currently looking for a space better than North Station — something around MIT might be nice, and we’re trying to get an MIT grad student/faculty/staff member/organization to sponsor it. (We want a location that’s good for people driving AND taking the T, and when the Garden has an event, it’s not feasible to park there). If you can help out, please let me know.

(You may forward this announcement to other groups, lists, blogs, whatever.)

I’m quite excited about the presentation, as I’m writing down a lot of information from different sources that really wants to be together, but as far as I can tell is not yet, at least not in a public place.
This article is somewhat long. Interestingly, as there is much to talk about besides the mechanics of each backup option. I wonder what I’d need to do to make this into a white paper or an article?

The backup presentation was finished last night. I may decide to go back and put some extra stuff in there, but that would be syntax and code and stuff. The logic is all in there, and the notes have been printed. I will post the slides (in .pdf and .swf (flash, the file is very small that way) formats) after the talk on Monday, as I may yet revise them.

I am very excited about one slide in particular, and I’ll share it here. It’s really a slide that I end with, but I feel as though it’s a great starting point as well as a summary point. I haven’t seen this information encapsulated this way before, so here goes:

Comparison Table of MySQL Backup Methods

Continue reading “Backups Video Now Downloadable”

MySQL Backup Presentation

The Boston MySQL User Group was successful yet again! I was a bit worried about my presentation, that it would be too basic or folks would have wanted to see actual code and scripts, but it turned out well.

You may notice that the links at the side (http://www.sheeri.net if you’re reading a feed) include a category called “Presentations”. Currently the December presentation is linked to, direct to Google Video, and the slides from tonight’s meeting are also up in PDF format as well as macromedia flash. And, of course, tonight’s video, thanx to Mike Kruckenberg. Mike also took pictures!

Folks who were at the presentation — feel free to let me know what you thought of it. Folks not at the presentation — feel free to watch the video and let me know what you think. I’m especially interested for those folks who read the last article, if the talk goes more in depth and answers more questions.

(quick links for those reading feeds:

Mysql User Group Photos

Mysql Backups (slides, flash, 125 Kb)
Mysql Backups (slides, pdf, 2.0 Mb)

MySQL Backups presentation at the Jan. 9th, 2006 meeting.
Mysql 5.0 Presentation at the December user group meeting featuring Philip Antoniades of MySQL AB.
MySQL webinars from mysql.com
)

Why didn’t I think of this? Tom Limoncelli was nice enough to create a livejournal feed for this site — http://www.livejournal.com/users/sheericom_feed/

Direct link to add as a ‘friend’ if you’re logged in — http://www.livejournal.com/friends/add.bml?user=sheericom_feed

Why didn’t I think of this? Tom Limoncelli was nice enough to create a livejournal feed for this site — http://www.livejournal.com/users/sheericom_feed/

Direct link to add as a ‘friend’ if you’re logged in — http://www.livejournal.com/friends/add.bml?user=sheericom_feed

The MySQL User Group meeting on Monday, January 9th will focus on backups — I will give a 30-45 minute presentation on backups and we will then delve into a discussion — how folks within the group are backing up, how they’re not backing up, how they’d like to back up. If there’s time, we’ll do a “lessons learned from backup horror stories”.

Afterwards, we’ll head a few doors down to Boston Beer Works for more chatting.

You can see a video of last month’s presentation by Philip Antionades of MySQL AB on the new features in MySQL 5.0 at Google Video:

http://video.google.com/videosearch?q=Mysql+5.0+by+Philip+Antoniades

(we will video this month’s presentation but not the discussion)

More details, including the event location and directions, are at: http://mysql.meetup.com/137/events/

Any questions, comments, etc should be sent to me. We’re currently looking for a space better than North Station — something around MIT might be nice, and we’re trying to get an MIT grad student/faculty/staff member/organization to sponsor it. (We want a location that’s good for people driving AND taking the T, and when the Garden has an event, it’s not feasible to park there). If you can help out, please let me know.

(You may forward this announcement to other groups, lists, blogs, whatever.)

I’m quite excited about the presentation, as I’m writing down a lot of information from different sources that really wants to be together, but as far as I can tell is not yet, at least not in a public place.
Why didn’t I think of this? Tom Limoncelli was nice enough to create a livejournal feed for this site — http://www.livejournal.com/users/sheericom_feed/

Direct link to add as a ‘friend’ if you’re logged in — http://www.livejournal.com/friends/add.bml?user=sheericom_feed

The MySQL User Group meeting on Monday, January 9th will focus on backups — I will give a 30-45 minute presentation on backups and we will then delve into a discussion — how folks within the group are backing up, how they’re not backing up, how they’d like to back up. If there’s time, we’ll do a “lessons learned from backup horror stories”.

Afterwards, we’ll head a few doors down to Boston Beer Works for more chatting.

You can see a video of last month’s presentation by Philip Antionades of MySQL AB on the new features in MySQL 5.0 at Google Video:

http://video.google.com/videosearch?q=Mysql+5.0+by+Philip+Antoniades

(we will video this month’s presentation but not the discussion)

More details, including the event location and directions, are at: http://mysql.meetup.com/137/events/

Any questions, comments, etc should be sent to me. We’re currently looking for a space better than North Station — something around MIT might be nice, and we’re trying to get an MIT grad student/faculty/staff member/organization to sponsor it. (We want a location that’s good for people driving AND taking the T, and when the Garden has an event, it’s not feasible to park there). If you can help out, please let me know.

(You may forward this announcement to other groups, lists, blogs, whatever.)

I’m quite excited about the presentation, as I’m writing down a lot of information from different sources that really wants to be together, but as far as I can tell is not yet, at least not in a public place.
This article is somewhat long. Interestingly, as there is much to talk about besides the mechanics of each backup option. I wonder what I’d need to do to make this into a white paper or an article?

The backup presentation was finished last night. I may decide to go back and put some extra stuff in there, but that would be syntax and code and stuff. The logic is all in there, and the notes have been printed. I will post the slides (in .pdf and .swf (flash, the file is very small that way) formats) after the talk on Monday, as I may yet revise them.

I am very excited about one slide in particular, and I’ll share it here. It’s really a slide that I end with, but I feel as though it’s a great starting point as well as a summary point. I haven’t seen this information encapsulated this way before, so here goes:

Comparison Table of MySQL Backup Methods

Continue reading “MySQL Backup Presentation”

Backups

This article is somewhat long. Interestingly, it does not actually cover my entire talk, as there is much to talk about besides the mechanics of each backup option. I wonder what I’d need to do to make this into a white paper or an article?

The backup presentation was finished last night. I may decide to go back and put some extra stuff in there, but that would be syntax and code and stuff. The logic is all in there, and the notes have been printed. I will post the slides (in .pdf and .swf (flash, the file is very small that way) formats) after the talk on Monday, as I may yet revise them.

I am very excited about one slide in particular, and I’ll share it here. It’s really a slide that I end with, but I feel as though it’s a great starting point as well as a summary point. I haven’t seen this information encapsulated this way before, so here goes:

Comparison Table of MySQL Backup Methods

Method No Locking DDL Snapshot Remote Free All Engines All Tables Text File Recover
Corruption
# No
SELECT . . .
INTO OUTFILE
Engine
Dependent
No No Yes Yes Yes No Yes Yes 3-4
mysqldump No Option No Yes Yes Yes Yes Yes Yes 2-3
Replication Yes No Yes Yes Yes Yes Yes No No 3
OS level copy No Yes No No Yes No Yes No Yes 5
mysqlhotcopy Yes Yes Yes No Yes No Yes No Yes 3
InnoDB Hot
Backup
Yes Yes Yes No No No Yes No Yes 4

The table is set up so the “No” answers are ‘bad’ and the “Yes” answers are ‘good’. The last column contains the # of “No”s in the row. Right off the bat, we see why mysqldump is probably the most used backup tool — it has the fewest “No”s.

“No locking” — A backup routine should interfere as little as possible with the actual database. If you lock a row or table, even for just reading, you’re blocking others from writing it. Transactional engines like InnoDB and BDB offer transactions to help get around this, but many databases have a mixture of transactional and non-transactional storage engines.

“DDL” — Backups should be able to completely restore a database. If you do not have the Data Definition Language (ie, CREATE TABLE statements), you will not be able to completely restore a database.

“Snapshot” — To use a backup for point-in-time recovery or to build a new replication slave, a snapshot is needed. It is possible to do a point-in-time recovery without a snapshot, but it involves checking the binary logs for possible duplicate statements.

“Remote” — Does the backup need to be run on the OS where the server lies? Due to security in companies or the use of an ISP, it is not always possible for the DBA to have the level of access needed to run programs on the database server.

“Free” — Self-explanatory. Note that the backup options that are free come packaged with MySQL, with the exception of OS-level copy, which is in all OS systems (ignoring embedded systems for now).

“All Engines” — Does the backup option deal with all storage engines? Some tools or commands are engine-specific.

“All Tables” — Can the tool easily backup all the tables without external looping code?

“Text file” — A text file backup is advantageous for a two reasons: Corruption can be easily detected, partial backups (ie, of one table) can be done, and SQL can be standardized for migration. Text files are usually larger than data files, but compression of text is excellent.

“Recover Corruption” — A “Yes” here means the backup option can be used to recover from corruption.

A brief discussion about the options:

SELECT . . . INTO OUTFILE is not used often, because it does not copy DDL. Code is needed to loop through tables, and there will be table-level locking for MyISAM tables. If the SELECT statement is put into a transaction, there will not be a problem for BDB and InnoDB tables.

In MySQL 5.0 it is possible to copy the information about a table from the INFORMATION_SCHEMA database by using SELECT . . .
INTO OUTFILE
. I have not tested if inserting data into the INFORMATION_SCHEMA tables is an equal and complete substitution for DDL. Instinct says “no”.

It’s my opinion that mysqldump is the most widely used backup tool. It has the fewest “No”s of all of them, and the features it does not have can be worked around.

Note that mysqldump does do DDL by default, but it’s easy to get an incomplete DDL statement. If you turn off –opt, and don’t put –create-options, you will end up with CREATE TABLE statements that are standard SQL. This may sound good, but remember that storage engines are MySQL specific, so your CREATE TABLE statements will end up using the default storage engine, which may not be what you want upon restoration.

mysqldump can do a snapshot if the option to lock all the tables is used. However, it is usually not feasible to lock every database on the server while the backup is running, because a backup can take minutes.

Replication is also widely used, but it requires another instance. And to reduce single points of failure, that means more hardware needs to be bought. It may be difficult to justify hardware simply to do nothing unless a backup is needed.

One of the features of replication is also a detriment. The standard way to set up a replication slave is to take a snapshot of the master, import it to the slave, and then use binary logs from there. Another way is to use LOAD DATA FROM MASTER, but that only works for MyISAM tables. It is possible to alter tables and data on the slave before or during replication, without replication failing. This is a handy feature for having a write-only master with InnoDB tables and a read-only slave with MyISAM tables (for example, with fulltext searching). However, this also means that the DDL backup is not necessarily to be trusted.

That mistrust can be extended to the data for the same reasons. Replication may not be a reliable backup if DML is being run on the slave server. As well, if the master becomes corrupt, it is likely that corruption will spread to the replication server.

The two most widely-used storage engines, MyISAM and InnoDB, are OS independent (explanation in the MySQL manual for MyISAM and InnoDB). This means that they can simply be copied on the OS-level. The biggest gotcha is that in order to get a snapshot, the entire database has to be locked. And unlike mysqldump, there is no option to an OS-level copy that will lock the tables. Using this is bulkier than mysqldump , and it cannot be used for all storage engines as mysqldump can.

Two solutions have been devised to combat those problems — mysqlhotcopy and InnoDB Hot Backup. mysqlhotcopy is for MyISAM tables while InnoDB Hot Backup is for InnoDB tables. The other difference is that the InnoDB Hot Backup is not free. However, InnoDB Hot Backup does offer a free perl script that will use InnoDB Hot Backup to take a snapshot of the InnoDB tables, and then use MySQL commands to get a snapshot of the other engine types.

Most people will choose a combination of backup methods — many use both replication and mysqldump, or replication and mysqlhotcopy and InnoDB Hot Backup. There is no clear-cut answer, and mostly it depends on your environment and what the backup will be used for.

Ideally there would be one tool that performed all of the functions listed in the chart. The chart is not complete, either — just a listing of my opinion of the most common desired features. There are other desired features not listed, for example replication is an “immediate hot backup” whereas all the other tools require manually importing or starting the MySQL server with new data files, etc. I am definitely interested in what folks have to say on the subject.

Why didn’t I think of this? Tom Limoncelli was nice enough to create a livejournal feed for this site — http://www.livejournal.com/users/sheericom_feed/

Direct link to add as a ‘friend’ if you’re logged in — http://www.livejournal.com/friends/add.bml?user=sheericom_feed

Why didn’t I think of this? Tom Limoncelli was nice enough to create a livejournal feed for this site — http://www.livejournal.com/users/sheericom_feed/

Direct link to add as a ‘friend’ if you’re logged in — http://www.livejournal.com/friends/add.bml?user=sheericom_feed

The MySQL User Group meeting on Monday, January 9th will focus on backups — I will give a 30-45 minute presentation on backups and we will then delve into a discussion — how folks within the group are backing up, how they’re not backing up, how they’d like to back up. If there’s time, we’ll do a “lessons learned from backup horror stories”.

Afterwards, we’ll head a few doors down to Boston Beer Works for more chatting.

You can see a video of last month’s presentation by Philip Antionades of MySQL AB on the new features in MySQL 5.0 at Google Video:

http://video.google.com/videosearch?q=Mysql+5.0+by+Philip+Antoniades

(we will video this month’s presentation but not the discussion)

More details, including the event location and directions, are at: http://mysql.meetup.com/137/events/

Any questions, comments, etc should be sent to me. We’re currently looking for a space better than North Station — something around MIT might be nice, and we’re trying to get an MIT grad student/faculty/staff member/organization to sponsor it. (We want a location that’s good for people driving AND taking the T, and when the Garden has an event, it’s not feasible to park there). If you can help out, please let me know.

(You may forward this announcement to other groups, lists, blogs, whatever.)

I’m quite excited about the presentation, as I’m writing down a lot of information from different sources that really wants to be together, but as far as I can tell is not yet, at least not in a public place.
Why didn’t I think of this? Tom Limoncelli was nice enough to create a livejournal feed for this site — http://www.livejournal.com/users/sheericom_feed/

Direct link to add as a ‘friend’ if you’re logged in — http://www.livejournal.com/friends/add.bml?user=sheericom_feed

The MySQL User Group meeting on Monday, January 9th will focus on backups — I will give a 30-45 minute presentation on backups and we will then delve into a discussion — how folks within the group are backing up, how they’re not backing up, how they’d like to back up. If there’s time, we’ll do a “lessons learned from backup horror stories”.

Afterwards, we’ll head a few doors down to Boston Beer Works for more chatting.

You can see a video of last month’s presentation by Philip Antionades of MySQL AB on the new features in MySQL 5.0 at Google Video:

http://video.google.com/videosearch?q=Mysql+5.0+by+Philip+Antoniades

(we will video this month’s presentation but not the discussion)

More details, including the event location and directions, are at: http://mysql.meetup.com/137/events/

Any questions, comments, etc should be sent to me. We’re currently looking for a space better than North Station — something around MIT might be nice, and we’re trying to get an MIT grad student/faculty/staff member/organization to sponsor it. (We want a location that’s good for people driving AND taking the T, and when the Garden has an event, it’s not feasible to park there). If you can help out, please let me know.

(You may forward this announcement to other groups, lists, blogs, whatever.)

I’m quite excited about the presentation, as I’m writing down a lot of information from different sources that really wants to be together, but as far as I can tell is not yet, at least not in a public place.
This article is somewhat long. Interestingly, as there is much to talk about besides the mechanics of each backup option. I wonder what I’d need to do to make this into a white paper or an article?

The backup presentation was finished last night. I may decide to go back and put some extra stuff in there, but that would be syntax and code and stuff. The logic is all in there, and the notes have been printed. I will post the slides (in .pdf and .swf (flash, the file is very small that way) formats) after the talk on Monday, as I may yet revise them.

I am very excited about one slide in particular, and I’ll share it here. It’s really a slide that I end with, but I feel as though it’s a great starting point as well as a summary point. I haven’t seen this information encapsulated this way before, so here goes:

Comparison Table of MySQL Backup Methods

Continue reading “Backups”

Boston MySQL User Group: Backups!

The MySQL User Group meeting on Monday, January 9th will focus on backups — I will give a 30-45 minute presentation on backups and we will then delve into a discussion — how folks within the group are backing up, how they’re not backing up, how they’d like to back up. If there’s time, we’ll do a “lessons learned from backup horror stories”.

Afterwards, we’ll head a few doors down to Boston Beer Works for more chatting.

You can see a video of last month’s presentation by Philip Antionades of MySQL AB on the new features in MySQL 5.0 at Google Video:

http://video.google.com/videosearch?q=Mysql+5.0+by+Philip+Antoniades

(we will video this month’s presentation but not the discussion)

More details, including the event location and directions, are at: http://mysql.meetup.com/137/events/

Any questions, comments, etc should be sent to me. We’re currently looking for a space better than North Station — something around MIT might be nice, and we’re trying to get an MIT grad student/faculty/staff member/organization to sponsor it. (We want a location that’s good for people driving AND taking the T, and when the Garden has an event, it’s not feasible to park there). If you can help out, please let me know.

(You may forward this announcement to other groups, lists, blogs, whatever.)

I’m quite excited about the presentation, as I’m writing down a lot of information from different sources that really wants to be together, but as far as I can tell is not yet, at least not in a public place.

Why didn’t I think of this? Tom Limoncelli was nice enough to create a livejournal feed for this site — http://www.livejournal.com/users/sheericom_feed/

Direct link to add as a ‘friend’ if you’re logged in — http://www.livejournal.com/friends/add.bml?user=sheericom_feed

Why didn’t I think of this? Tom Limoncelli was nice enough to create a livejournal feed for this site — http://www.livejournal.com/users/sheericom_feed/

Direct link to add as a ‘friend’ if you’re logged in — http://www.livejournal.com/friends/add.bml?user=sheericom_feed

The MySQL User Group meeting on Monday, January 9th will focus on backups — I will give a 30-45 minute presentation on backups and we will then delve into a discussion — how folks within the group are backing up, how they’re not backing up, how they’d like to back up. If there’s time, we’ll do a “lessons learned from backup horror stories”.

Afterwards, we’ll head a few doors down to Boston Beer Works for more chatting.

You can see a video of last month’s presentation by Philip Antionades of MySQL AB on the new features in MySQL 5.0 at Google Video:

http://video.google.com/videosearch?q=Mysql+5.0+by+Philip+Antoniades

(we will video this month’s presentation but not the discussion)

More details, including the event location and directions, are at: http://mysql.meetup.com/137/events/

Any questions, comments, etc should be sent to me. We’re currently looking for a space better than North Station — something around MIT might be nice, and we’re trying to get an MIT grad student/faculty/staff member/organization to sponsor it. (We want a location that’s good for people driving AND taking the T, and when the Garden has an event, it’s not feasible to park there). If you can help out, please let me know.

(You may forward this announcement to other groups, lists, blogs, whatever.)

I’m quite excited about the presentation, as I’m writing down a lot of information from different sources that really wants to be together, but as far as I can tell is not yet, at least not in a public place.

Livejournal Feed!

Why didn’t I think of this? Tom Limoncelli was nice enough to create a livejournal feed for this site — http://www.livejournal.com/users/sheericom_feed/

Direct link to add as a ‘friend’ if you’re logged in — http://www.livejournal.com/friends/add.bml?user=sheericom_feed

Why didn’t I think of this? Tom Limoncelli was nice enough to create a livejournal feed for this site — http://www.livejournal.com/users/sheericom_feed/

Direct link to add as a ‘friend’ if you’re logged in — http://www.livejournal.com/friends/add.bml?user=sheericom_feed

Book Review: Time Management for System Administrators

I picked up this book for a few reasons:

1) I have a sysadmin background, and it helps me understand databases immensely (though not as much as the actual database courses I’ve had).

2) The author is a friend of mine.

3) The author is an exceptional sysadmin and writer.

4) I watched the Google video presentation he gave to a user group about the book, and wanted to learn more.

So, without ado, here is my full review of the book:

Firstly, let’s delve into why system administrators (and database administrators!) need a book on time management. Most folks have too much on their plate. System administrators, however, have a lot on their plate in terms of projects, but are also interrupted many times per day with numerous situations.

System administrators have the situation where folks come to them with a problem, expecting immediate attention, much like a retail cashier — “I’m here, I’d like to make a purchase, please help me.” If the cashier is busy doing other things, such as stocking merchandise, and takes a while to acknowledge the customer, the customer gets upset.

However, system administrators have large projects looming — they need to reconcile “create this account” with “rebuild the network infrastructure.”

Tom Limoncelli’s book, “Time Management for System Administrators” deals with this quandary. He focuses on making the work environment sane and handleable, so a sysadmin feels more accomplished (and his boss feels he is, too). However, this book is also focused on keeping folks who need work from you happy. Limoncelli reforms the idea of an overworked, grumpy sysadmin, and shows you how you can transform yourself.

In the first part of Chapter 2, “Focus Versus Interruptions”, he states:

“You might say that this chapter teaches you how to keep yourself focused and deal with interruptions without being a jerk.”

Limoncelli has the uncanny way of answering my questions with the next paragraph. For instance, I was a bit wary of his “Cycle system”, and thought, “well, I could use Life Balance for that, couldn’t I? And it’s really better!” And lo and behold, on page 75, he gives that option.

The values of routines and automation are discussed, as well as how to set those up. It’s all well and good to say “you should automate things,” but Limoncelli explains how to do that for tasks. He explains how to get over ‘mental garbage’ that blocks us from improving ourselves. There’s an entire chapter devoted to email management, which comes after one on stress management. And yes, he even delves into such sticky issues as documentation and prioritization.

All of his tips are useful. I did not find myself disagreeing with any tips — they either fell into the category of “I’m already doing it,” “I should do it now,” and “I’m not ready to do that now, but I will do it in the future.”

This is a book I would read once or twice a year and keep refining my techniques based on it.

I intend to give this book to anyone who works in this type of environment — sysadmins I know, in particular, but DBAs as well.

I picked up this book for a few reasons:

1) I have a sysadmin background, and it helps me understand databases immensely (though not as much as the actual database courses I’ve had).

2) The author is a friend of mine.

3) The author is an exceptional sysadmin and writer.

4) I watched the Google video presentation he gave to a user group about the book, and wanted to learn more.

So, without ado, here is my full review of the book:

Continue reading “Book Review: Time Management for System Administrators”

Looking at cron

Having a sysadmin background, I am fairly familiar with cron. I’m amazed at how many companies set up cron jobs and assume everything will just work. If root mail is not checked (or forwarded to someone who actually reads it), a cron job could fail and you would never know.

Having an automated task fail silently is a Bad Thing (TM). I take commands which should have no output normally (such as mysqldump > dumpfile.sql and mysql < maintenance.sql) and pipe the output into a script called mailif. The script will e-mail a specified recipient if standard input is not blank.

In plain English, if the automated task has ANY output or error code, it e-mails me.

One of my current tasks is going through all the database maintenance scripts and making sure it e-mails me if there's an error.

Standard cron entries have 6 fields:
minute 0-59
hour 0-23
day of the month 1-31
month 1-12
day of the week 0-7
command (the rest of the line)

A wildcard, *, is permitted. So you can easily have a command (or a script) run every minute, every hour, every day, every month, or every Monday. You can also do more complex routines like run a command when there's a "Friday the 13th during October."

So a backup script to dump tables in a database every day at 6 am would have the following crontab entry:
0 6 * * * mysqldump --all-databases > /var/log/mysql/dump/backup.sql | /usr/local/bin/mailif -t user@domain.com "output of mysqldump"

If the mysqldump fails, cron will send user@domain.com an e-mail with the subject of “output of mysqldump” and the body will be the output.

One thing I learned looking at the cron entries that were setup by someone else is that to set up a routine occuring every ten minutes, the minute field has */10 in it. Fascinating, given that I’d always written out a comma-separated list 0,10,20,30,40,50.

Having a sysadmin background, I am fairly familiar with cron. I’m amazed at how many companies set up cron jobs and assume everything will just work. If root mail is not checked (or forwarded to someone who actually reads it), a cron job could fail and you would never know.

Having an automated task fail silently is a Bad Thing (TM). I take commands which should have no output normally (such as mysqldump > dumpfile.sql and mysql < maintenance.sql) and pipe the output into a script called mailif. The script will e-mail a specified recipient if standard input is not blank.

In plain English, if the automated task has ANY output or error code, it e-mails me.

One of my current tasks is going through all the database maintenance scripts and making sure it e-mails me if there's an error.

Standard cron entries have 6 fields:
minute 0-59
hour 0-23
day of the month 1-31
month 1-12
day of the week 0-7
command (the rest of the line)

A wildcard, *, is permitted. So you can easily have a command (or a script) run every minute, every hour, every day, every month, or every Monday. You can also do more complex routines like run a command when there's a "Friday the 13th during October."

So a backup script to dump tables in a database every day at 6 am would have the following crontab entry:
0 6 * * * mysqldump --all-databases > /var/log/mysql/dump/backup.sql | /usr/local/bin/mailif -t user@domain.com "output of mysqldump"

If the mysqldump fails, cron will send user@domain.com an e-mail with the subject of “output of mysqldump” and the body will be the output.

One thing I learned looking at the cron entries that were setup by someone else is that to set up a routine occuring every ten minutes, the minute field has */10 in it. Fascinating, given that I’d always written out a comma-separated list 0,10,20,30,40,50.