"Hot Backups" of your MySQL Database on Linux

Would you like to take consistent snapshot backups of your entire database without a SAN or external software, and without slowing or locking your database for the duration?

RedHat? Enterprise Linux (and Centos Linux) install an LVM by default.
LVM, Logical Volume Manager is a layer that sits on top of any file system (default ext3).

You can tell if you're already running on an LVM, by checking your file system with "df -h" — an entry like "/dev/mapper/VolGroup01-LogVol00" shows that your disk is mapped through the LVM.

LVM brings you features such as the ability to grow and shrink volumes, add hard disks without migrating data, RAID0, and for the purpose of this article: file system SNAPSHOTS.

FLUSH TABLES WITH READ LOCK;
\! lvcreate --size 100m --snapshot --name snap …

"Hot Backups" of your MySQL Database on Linux

Would you like to take consistent snapshot backups of your entire database without a SAN or external software, and without slowing or locking your database for the duration?

RedHat? Enterprise Linux (and Centos Linux) install an LVM by default.
LVM, Logical Volume Manager is a layer that sits on top of any file system (default ext3).

You can tell if you're already running on an LVM, by checking your file system with "df -h" — an entry like "/dev/mapper/VolGroup01-LogVol00" shows that your disk is mapped through the LVM.

LVM brings you features such as the ability to grow and shrink volumes, add hard disks without migrating data, RAID0, and for the purpose of this article: file system SNAPSHOTS.

FLUSH TABLES WITH READ LOCK;
\! …

In Chicago now, and tonight is the (moved from Monday) local MySQL UG meeting, from 7pm. See : http://mysql.meetup.com/5/ (the group) and http://mysql.meetup.com/5/events/4892024/ (this event) for full details.
If you're in the Chicago area, do drop in, say hi, and ask any questions! Mark Matthews (MySQL Java and Windows lead) and I will be there from MySQL, plus assorted users. Should be good! I've got an autographed book by Guy Harrison (the new Stored Procedures one) to hand out to someone, and a few "what's your uptime?" shirts.

This is probably a dumb question, but I’ll put it forth anyway. Is there a routine or easy way to limit the number of items in a group? What I want to do is limit the number of items in a group to no more than a certain number. For instance, the last 10 times someone logged in.

I’m thinking of a routine that takes in field1, field2, # limit, and then an optional keyword of {FIRST,LAST} and maybe an optional WHERE clause. So in an example, the routine would take in:

uid
lastLoginTime
10
FIRST
uid=12345

and the routine would find the number of times uid 12345 logged in. If it’s less than or equal to 10, leave it alone. If it’s greater than 10, delete it so it gets to 10, deleting the oldest records first.

This is not something that could be done with a trigger (ie, on insert of a new login, check to see how many logins there are, and if there are 10 delete the first (oldest) …

So, the “MySQL Gotchas” page was mentioned in one of the talks at the conference last week. The page itself is at:

http://sql-info.de/mysql/gotchas.html

Now, to go through it all…..

Our site went from weekly crashes during our two busiest nights to not even peeping this week (during the two busiest nights), and the only thing we changed was that we did some table maintenance. We hadn’t done table maintenance at least as long as I’ve been around, which is 6 months. We are a site with high volumes of both reads and writes. This article will talk about the care and feeding of tables; feel free to use this for justification to have a maintenance window, or even permission to run table maintenance statements.

MySQL uses a cost-based optimizer to best translate the written query into what actually happens. This means when you write:

SELECT foo FROM t1 INNER JOIN t2 USING (commonField);

The optimizer looks at the statistics for tables t1 and t2 and decides which is better:
1) To go through each item in t1, looking for a matching “commonField” in t2
or
2) To go …

Many people have some kind of reporting or auditing on their database. The problem is that the data grows very large, and lots of times there is data that can be purged. Sure, theoretically one never needs to purge data, but sometimes a “delete” flag just won’t work — when you search on the delete flag, a full table scan may be the most efficient way to go.

Of course, that’s not acceptable. And in many cases, say when you have users who no longer use the site but did in the past (and perhaps have billing data associated with them), you never want to get rid of them.

So what to do? Make a special reporting database, that gathers information from the production database(s). Use MyISAM tables, because a reporting server can afford to be behind the master, and MyISAM is better for reporting — better metadata. For something like a “Users” table, make 2 more tables:

1) DeletedUsers
2) AllUsers

…

By tim

Greg Linden of Findory wrote: "I've been enjoying your series on O'Reilly Radar about database war stories at popular startups. I was thinking that it would be fantastic if you could get Jeff Dean or Adam Bosworth at Google to chat a little bit about their database issues. As you probably know, Jeff Dean was involved designing BigTable and the Google File System. Adam Bosworth wrote a much discussed post about the need for better, large scale, distributed databases."

I followed up with mail to Jeff and Adam. Jeff wrote back briefly about BigTable: "Interesting discussion. I don't have much to add. I've been working with a number of …

Yesterday, I learned that I will be at this year?s Linuxtag Conference in Wiesbaden. Somehow, their proposal system sent out a confirmation on one of my proposals but was caught by my spam filter. Linuxtag guys, are you listening? Don?t send such important mails without a fullname next time.

Well, the topic that was accepted is ?SQL vs. LDAP? where I will introduce both systems and their pros & cons. Which systems makes sense in which environment, when to combine both worlds, what to avoid, etc. I will be on the train in 5 hours and have a 6 hours ride to get the slides done.

Additionally, I heritated another talk about MySQL Cluster. It will be the same slides I used from the latest Usergroup meeting in Hamburg. But since most people at the conference didn?t attend that meeting, it should be interesting and …

Round 2. Question 2? From the MySQL Quiz Show. (you had to be there)