PlanetMysql.ru — информация о СУБД MySQL

[UPDATE 2011/01/11] : New poll added, vote for the best GUI client tool ! (And continue to vote for other polls)
And thanks again for your involvement. It’s time to vote again… 

First of all, I wish you a happy new year.
Many things happened last year, it was really exciting to be involved in the MySQL ecosystem.
I hope this enthusiasm will be increased this year, up to you !

To start the year, I propose the MySQL+ Community Awards 2011
It will only take 5 minutes to fill out these polls.
Answer with your heart first and then with your experience with some of these tools or services.

Polls will be closed January 31, so, vote now !
For “other” answers, please,  let me a comment with details.

Don’t hesitate to submit proposal for tools or services in the comments.
And, please, share these polls !

 

Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.

Happy 2012 !
Cédric

This article is obviously not sponsored !
(MySQL is a trademark of Oracle Corporation and/or its affiliates)

Sources :

• Oracle products, tools and services : http://www.mysql.com
• SkySQL products, tools and services : http://www.skysql.com
• Percona products, tools and services : http://www.percona.com
• FromDual products, tools and services : http://www.fromdual.com
• PalominoDB products, tools and services : http://palominodb.com/
• Pythian products, tools and services : http://www.pythian.com/
• Blue Gecko products, tools and services : http://www.bluegecko.net/
• Zabbix : http://www.zabbix.com/
• MySQL Cacti templates : http://code.google.com/p/mysql-cacti-templates/
• Nagios : http://www.nagios.org/
• r1soft CDP for MySQL : http://www.r1soft.com/linux-cdp/cdp-enterprise-edition/mysql/ 
• mylvmbackup tool : http://www.lenzg.net/mylvmbackup/
• mydumper tool : http://www.mydumper.org/
• ZRM tools : http://www.zmanda.com
• Galera replication : http://codership.com/products/galera_replication
• Tungsten replicator : http://code.google.com/p/tungsten-replicator/
• DRBD : http://www.drbd.org/
• ShoonerSQL : http://www.schoonerinfotech.com/products/schoonersql
• ScaleBase : http://www.scalebase.com/
• common-schema : http://code.google.com/p/common-schema/
• innotop : http://code.google.com/p/innotop/
• MySQL Tuner : https://launchpad.net/mysqltuner/2.0/mysqltuner-2.1-rev17
• mycheckpoint : http://code.openark.org/forge/mycheckpoint
• MariaDB : http://mariadb.org/
• TokuDB : http://www.tokutek.com/
• InfiniDB : http://infinidb.org/
• Drizzle : https://launchpad.net/drizzle
• Amazon SimpleDB : http://aws.amazon.com/fr/simpledb//180-2635771-0721067/
• mongoDB : http://www.mongodb.org/
• Cassandra : http://cassandra.apache.org/
• CouchDB : http://couchdb.apache.org/
• Tokyo Cabinet : http://fallabs.com/
• Riak : http://basho.com/
• Hadoop : http://hadoop.apache.org/
• Tarantool : http://tarantool.org/
• memcached : http://memcached.org/
• MySQL Sandbox : http://mysqlsandbox.net/
• openark kit : http://code.openark.org/forge/openark-kit
• Redis : http://redis.io/
• Ganglia : http://ganglia.sourceforge.net/
• HandlerSocket : https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL

PlanetMySQL Voting: Vote UP / Vote DOWN

Last week we concluded our first Oracle Technology Network Sys Admin Day in Sacramento (CA). Well, it was actually the second Sys Admin Day, but the first one that had two parallel tracks of sessions about both Oracle Linux and Oracle Solaris.

I helped preparing for the event by creating the Linux lab handbook as well as the VirtualBox appliance of Oracle Linux 6.1 that was used for the exercises. Unfortunately I could not be there in person, but it would have been pointless for me to go on an intercontinental flight just for one day.

From the feedback we've received so far, the attendees really enjoyed the event and were positively surprised about the depth and quality of the practical hands-on lab sessions.

If you've missed the first one and happen to live somewhere in the Seattle area, you have another chance to attend OTN sysadmin day: we'll be hosting another one on Thursday, September 22nd at The Westin Seattle (1900 5th Ave., Seattle, WA 98101). Again, attendance is free, all you need to bring is your own laptop with VirtualBox installed. We'll provide the rest. Space is limited — you can review the agenda and register here!

PlanetMySQL Voting: Vote UP / Vote DOWN

LVM (Logical Volume Management) is a very important tool to have in the toolkit of a MySQL DBA. It allows you to create and extend logical volumes on the fly. This allows me to, say, add another disk and extend a partition effortlessly. The other very important feature is the ability to take snapshots, that you can then use for backups. All in all its a must have tool. Hence, this guide will allow you to understand various terminologies associated with LVM, together with setting up LVM volumes and in a later part will also show you how to extend...
PlanetMySQL Voting: Vote UP / Vote DOWN

The Problem

Developers often need to have a development database copy of the live production system you are using in able to allow them to test their code and to test new functionality and make schema changes to the database for this new functionality to work.

That’s normal and happens everywhere. A typical DBA task is to make a copy of the live system, sometimes to remove any confidential or sensitive information which perhaps the development database users should not be able to see, and then give them access to this development instance. The developers then “hack away”, changing their code and perhaps things in the database until they are ready to put these new changes into production when they then come along and discuss how to apply these changes into the live systems.

Once the development database has been created it soon becomes stale so often the developers want a new up to date copy to be made to “simplify” their testing.

This is all fine until the database size begins to grow and this process of dumping and copying the data for the developers takes hours rather than minutes and therefore can only be done on a daily or weekly basis.

I have recently been experimenting with the use of mounting the development database instance on an  LVM snapshot of the original filesystem where the live system is running.  This procedure does not have to be Linux specific but should work with any OS or storage which provides a facility to make a filesystem snapshot based on the contents of another filesystem.

So what does this mean in practice?

Previous Behaviour:

Situation: server1 has a live production copy of the database. We want to make a copy to server2. server2 is already prepared with a configuration which will work based on the copy of the production data.

Procedure: stop server1, copy the filesystem holding the database to server2, start server1. start server2. [the copy procedure takes hours.]

New Behaviour:

Situation: server1 has a live production copy of the database (probably via a slave), and space/memory for a second development instance to run concurrently on the same server.

Procedure: stop server1 (live instance), make a LVM snapshot of the live filesystem (the snapshot size can be much smaller than the live filesystem size), start server1 (live instance), start server1 (development instance). [the copy procedure takes just a few seconds.]

Since I do this with the live system being a slave, I tend to also include a routine to disable replication information on the snapshot filesystem by removing the appropriate files. It may also be necessary adjust the grants on the dev-instance so that it is appropriate for the new set of db users.

To all intents and purposes when you login to the development instance it looks like an up to date copy of live system. You can make as many changes as you like as long as the number of disk blocks on the snapshot which get changed don’t exceed the snapshot size. At this point the snapshot filesystem becomes invalid and mysqld is unable to access it. Mysqld gets a bit upset about this, but you just kill it and then go and rebuild the instance again if this happens – it only takes a few seconds.

This works pretty well and speeds things up for the developers. I can create a new development environment from the live system in seconds rather than hours. The disk storage requirements also tend to drop significantly. It also helps the devs. If you do a daily refresh of this development instance then it allows the developers to test any schema changes which will be needed to be applied to the live system much more easily as “going back to the current live state” is so easy.

So if you haven’t done something like this it might be worth giving it a go.

This is an example of the output from a script I’m currently using:


[root@myhost ~]# clone_instance -s40G -d live-instance dev-instance
Aug 22 10:57:00 myhost clone_instance[16405] Cloning live-instance to dev-instance with a snapshot volume of size 40G
Aug 22 10:57:00 myhost clone_instance[16405] /mysql/live-instance is mounted as expected
Aug 22 10:57:00 myhost clone_instance[16405] Device /dev/volgroup1/live-instance is mounted on /mysql/live-instance, having volume group: volgroup1, logical volume: live-instance
Aug 22 10:57:00 myhost clone_instance[16405] live-instance is defined in /etc/my.cnf [mysqld1]
Aug 22 10:57:00 myhost clone_instance[16405] dev-instance is defined in /etc/my.cnf [mysqld2]
Aug 22 10:57:00 myhost clone_instance[16405] Found defaults file /root/.my-live.cnf needed to shutdown live-instance
Aug 22 10:57:00 myhost clone_instance[16405] Going to viciously kill any processes using files under mount point: /mysql/dev-instance
Aug 22 10:57:02 myhost clone_instance[16405] Unmounting /mysql/dev-instance
Aug 22 10:57:02 myhost clone_instance[16405] Removing existing SNAPSHOT LV /dev/volgroup1/dev-instance
Logical volume "dev-instance" successfully removed
Aug 22 10:57:03 myhost clone_instance[16405] SNAPSHOT LV /dev/volgroup1/dev-instance removed
Aug 22 10:57:03 myhost clone_instance[16405] Shutting down live-instance [mysqld1] using mysqldmin and defaults file /root/.my-live.cnf (as 'mysqld_multi stop 1' does not seem work properly)
Aug 22 11:02:34 myhost clone_instance[16405] Creating new snapshot LV dev-instance (40G) based on /dev/volgroup1/live-instance
Logical volume "dev-instance" created
Aug 22 11:02:35 myhost clone_instance[16405] Restarting live-instance [mysqld1] using mysqld_multi start 1
Aug 22 11:02:35 myhost clone_instance[16405] Mounting SNAPSHOT LV /dev/volgroup1/dev-instance on /mysql/dev-instance
Aug 22 11:02:35 myhost clone_instance[16405] Cleaning up log files on SNAPSHOT LV
Aug 22 11:02:35 myhost clone_instance[16405] Removing replication information from SNAPSHOT LV
Aug 22 11:02:35 myhost clone_instance[16405] Found defaults file /root/.my-dev.cnf needed to access dev-instance
Aug 22 11:02:35 myhost clone_instance[16405] Starting SNAPSHOT DB instance dev-instance [mysqld2] using: 'mysqld_multi start 2'
Aug 22 11:02:35 myhost clone_instance[16405] Clone procedure complete.
[root@myhost ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
...
/dev/mapper/volgroup1-live--instance
160G  135G   26G  84% /mysql/live-instance
/dev/mapper/volgroup1-dev--instance
160G  134G   27G  84% /mysql/dev-instance
[root@myhost ~]# lvs
LV             VG        Attr   LSize   Origin         Snap%  Move Log Copy%  Convert
live-instance  volgroup1 owi-ao 160.00G
dev-instance   volgroup1 swi-ao  40.00G live-instance    2.06
[root@myhost ~]#


The longest part of the procedure is shutting down the active live slave. The rest of the time is insignificant.
The “dev experience” when using this dev-instance is just as before. The instance works, can be modified and behaves just as you would expect.

PlanetMySQL Voting: Vote UP / Vote DOWN

If you need to work with LVM in your scripts but haven’t found a good method to access details about Logical Volume Groups, here’s a simple Python script that will print the details about any volumes on your system. This could be useful for writing a partition check script for your MySQL data directory (if you’re not using a standard monitoring system like Nagios). Here’s the link: http://codepad.org/8kjOBJdj

PlanetMySQL Voting: Vote UP / Vote DOWN

I am happy to announce that mylvmbackup version 0.13 has now been released. This release includes a fix for a nasty bug in on of the recently added Perl hooks (precleanup.pm) and some added functionality (better support for remote rsync backups).

From the ChangeLog: 

• Deleted sample precleanup.pm hook as it has potential to cause harm and is too specialized on a particular use case (BUG#394668)
• Added support for rsync via SSH (BUG#392462)
• Fixed InnoDB recovery in case a relative path to the MySQL data directory is defined (BUG#38337), improved the documentation of relpath in the man page.

 

PlanetMySQL Voting: Vote UP / Vote DOWN

You can do all sorts of magic with LVM, like backing up MySQL using file system snapshots, but in order to do so you need to set it first. When installing a new computer with Suse Linux I found that this was harder than expected, unless you know what to do. Needless to say, I didn't, but some friends gave me a hand on IRC, and now I do. :-)

So I wrote a little article about this which you can find here, in an effort to save others some time. The article is about setting up LVM with Suse Yast, but you can probably use it for other flavors of Linux, too.