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

Until now, MariaDB 5.2 was lacking a yum repository for easy installs and upgrades. It is now available, thanks to OurDelta.

Just follow our very simple installation instructions.

PlanetMySQL Voting: Vote UP / Vote DOWN

I’ve recently updated my CentOS 5 x86_64 rpms for bacula-5.0.3. Hope they are useful to you.
PlanetMySQL Voting: Vote UP / Vote DOWN

In some environments using a distro package management system may* provide benefits including handling dependencies and providing a simpler approach when there are no dedicated DBA or SA resources.

However, the incorrect use can result in pain and in this instance production downtime. Even with dedicated resources at an unnamed premium managed hosting provider, the simple mistake of assumption resulted in over 30 minutes of unplanned downtime during peak time.

One of the disadvantages of using a system such as rpm is the lack of control in managing the starting and stopping of your MySQL instance, and the second is unanticipated package dependency upgrades.

So what happened with this client. When attempting to use the MySQL client on the production server, I got the following error.

$ mysql -uxxx -p
error while loading shared libraries: libmysqlclient.so.10: cannot open shared object file: No such file or directory

The server was running MySQL 5.0.27 via an rpm install.

$ rpm -qa | grep -i mysql
MySQL-server-standard-5.0.27-0.rhel3
MySQL-shared-standard-5.0.27-0.rhel3
MySQL-devel-standard-5.0.27-0.rhel3
MySQL-shared-compat-5.0.27-0.rhel3
MySQL-client-standard-5.0.27-0.rhel3

With no access to this managed server the information was relayed to the hosting provider and some time later we found the production website down. Some 30 minutes later we found that to fix the rpm problem, a dependency upgrade has also caused an automatic upgrade from 5.0.27 to 5.0.88.

While upgrading is not necessarily a bad thing, the lack of planning including a backup, a scheduled window of downtime and any level of testing is simply a poor cowboy approach to DBA management.

PlanetMySQL Voting: Vote UP / Vote DOWN

I recently received a question from Robin Schumacher at Calpont, the makers of the InfiniDB analytics database engine for MySQL: "How would you recommend we try and get bundled in with the various Linux distros?"

Since this question has come up several times before, I thought it might make sense to blog about my take on this.

First of all, please note that there is a difference between "being part of the core distribution" and "being available from a distributor's package repository". The latter one is relatively easy, the former can be hard, as you need to convince the distributor that your application is worth devoting engineering resources to maintain and support your application as part of their product. It's also a space issue – distributions need to make sure that the core packages still fit on the installation media (e.g. CD-ROMs or a DVD). Therefore they take a very close look at each package and if it's really needed to be part of the installation medium or if it's fine to provide it for download from a package repository instead.

Distributors prefer to keep their core product small and restricted to the "basic OS building blocks". While MySQL might still be considered to be a part of this, this probably does not apply to the various plugins and extensions that are available for it. Therefore the best approach is to invest some engineering time and start doing the packaging yourself, either by hiring an engineer capable of creating and maintaining the packages, or by finding someone in your community who has the required experiences and is willing to do it.

While it's of course possible to set up and maintain your own build and package hosting infrastructure for that, I recommend to make use of the existing services provided by the distributors.

The top tier distributors all provide means of offloading the maintenance of "non-core" packages to their community, offering various options for packages to be made available. For example, Novell/openSUSE provide the free "Build Service", which is capable of building packages for other distributions as well (e.g. Fedora, Mandriva, Debian/Ubuntu, etc.). In addition to automating the builds, the Build Service also takes care of the distribution via their download mirror network and ensures that your application can be found via their package search interface.

Red Hat/Fedora provide something similar, named "Koji" – but it's "Fedora only". Here's a HOWTO that outlines the process of becoming a Fedora package maintainer.

Ubuntu/Canonical have "Personal Package Archives (PPAs) – if your project is hosted on Launchpad already, that might be something to look into for providing Debian/Ubuntu packages. Alternatively you could join the Debian project and start building and maintaining your package there. They maintain a list of "Work-Needing and Prospective Packages", a description of the process on how to become a new maintainer is outlined here.

If you'd like to target Solaris/OpenSolaris as well, there is the OpenSolaris Source Juicer – a web service which allows OpenSolaris community developers to build packages (using RPM spec files) and publish them for review, so they will be included in an official package repository. The Software Porters Community Group coordinates, advocates, encourages and helps with the porting of Software from multiple Platforms to the OpenSolaris Platform.

PlanetMySQL Voting: Vote UP / Vote DOWN

Over at the OPenARK blog Shlomi Noach argues that using apt-get or yum to install your MySQL instance will one day most likeley break your MySQL setup. Depdendencies, distros not shipping the MySQL version you want to use and on some distro's indeed the mysql vs MySQL issue, agreed, it all makes things less trivial.

However why give up a clean packaged system if there are other ways out ?

First of all by claiming that such an installation can break a working production environment looks to me like admitting you don't have a split development, production environment and that rather than testing stuff upfront indeed you just hack a long in production.

So rather than using a tarball for the MySQL instance an --force to satisfy the missing dependencies (hence also cluttering your system) , a much cleaner and less error prone setup is to only deploy from your own , self controlled repository , in which you only allow tested packages, most probably not the distro based package , hence packages that won't break your setups ;) But still you will be using apt or yum and deploying rpm's and debs , perfectly satisfying dependency needs.

Apart from that .. watch out for Banquise .. :) Coming to your favourite distro soon..

PlanetMySQL Voting: Vote UP / Vote DOWN

I have been upgrading more MySQL database instances recently and have found a few more potential gotchas, which if you are not careful, can potentially be rather nasty. These are not documented explicitly by MySQL, so it may be handy for you to know if you have not come across this type of thing before.

Most of the issues are those related to upgrading MySQL instances which are replicated, either the master servers or the slaves. Some seem specific to the rpm packages I am using (MySQL enterprise or MySQL advanced rpms), though others are not.

Take care upgrading a 5.0 master when you have 5.1 slaves

It is not a good idea to run a mixed major version of mysql in a replicated environment so why would I be doing this? If you work in a replicated environment and have several slaves then it is recommended that you upgrade the slaves first. I work with quite a few slaves so the process of upgrading them all takes longer than you would think. Quite a long time in fact, while different systems are tested and upgraded. Along came a newer version of 5.0 and I thought of upgrading the master which had been giving a few issues, and one of them was resolved in the lastest 5.0.89. At least when using rpm packages, the package upgrade uses /usr/bin/mysql_install_db –rpm –user=mysql as part of the package upgrade procedure. This ensures that the mysql db is up to date but also writes to the binlog if one is configured. running on a master normally this would be the case. Of course these are 5.0 install commands and they are not really understood by the slaves which try to interpret them too. End result. Broken replication and you need to skip several transactions. If you have several slaves this can be rather painful.

Note: this is the cause of replicating the mysql database which I do as it is a good, quick and clean way to distribute GRANT information on to all slaves. As far as I know MySQL does not discourage this though perhaps many sites do not replicate their MySQL database.

If you upgrade a master server from 5.o to 5.1 by running mysql_upgrade after upgrading the binaries you may have a similar issue as the mysql_upgrade script determines that some new columns or tables are needed on the master and so adds them.  Again this gets written to the binlog which is good for point in time recovery on the server itself, but again bad from a replication point of view if the slave does not run the same version of MySQL.

In fact the binlog has these 2 uses: (a) writing changes for point in time recovery, and (b) writing the changes for replication to slaves.  Once could argue that for (a) the changes should not be written to the binlog but I think that is wrong if you have to recover just after an upgrade. So I would suggest that the upgrade changes should probably flagged specially in the binlog, allowing the slave to probably ignore them in the normal situation but also be able to recognise them, allowing you to stop the slave and perform the same binary upgrade, and then continue the upgrade with the new binaries exactly in the way this had been executed on the master. This behaviour should be controlled by a runtime flag which can be dynamically configured.

RPM Installs/Upgrades always start the server

rpm(8) is good but some of the design decisions in the MySQL rpms are questionable. One of these is that the current MySQL rpms are designed to always start when either doing a fresh install or doing an upgrade. Currently it’s not good practice to do rpm -Uvh MySQL-server-advanced-gpl…rpm (5.1 rpm) if you are running a MySQL-server-enterprise-gpl…rpm (5.0 rpm) so normally I stop MySQL, remove the enterprise rpm and install the advanced rpm. That starts the server and the slave on a box which is not completely “stable”. Solution is to add slave-skip-start to /etc/my.cnf but that should not be necessary as immediately after running mysql_upgrade you need to remove the value again.

Also during the upgrade process it is a good thing to avoid external client access so sometimes I also set the bind-address to 127.0.0.1 during the period of the upgrade.  That may not keep clients away if they are running on the local server but helps in many cases.

Conclusions

All this leads me to a simple conclusion: if you can, upgrade one box the slow way and then clone the other slaves from that. Cloning is simple and requires no thought so is a good idea.

The other conclusion based on the first one is that if you can: build a new master. That is clone a slave to make a new master. This will be the new master. Configure it to write it’s own binlogs and then you can move the existing slaves underneath the new one. Once all slaves are underneath the new master (left as the only slave of the original master) then you can simply point clients to the new master instead of the old one. That keeps down time to a minimum and avoids many problems.

As far as I know none of the comments I have made above are in the current 5.1 upgrade documentation. I have opened quite a few tickets requesting the documentation be improved and I guess that will happen slowly. For many people the points I have mentioned may seem irrelevant for their situation but for me they have caused a few problems. If you do not need to worry, then you can skip the documentation, otherwise if this were documented then you would be saved a few tears when least expecting problems.

PlanetMySQL Voting: Vote UP / Vote DOWN

Normally I like to use the OS’s package manager to manage the software on my system. This ensures that things are done consistently. When managing MySQL I’d also like to manage all my instances the same way. That makes life easier for me but also for my fellow DBAs and sysadmin colleagues.

I use CentOS and the rpm packages it provides and these work quite well. However, the MySQL rpms do not allow me to manage single and multiple instances alike and while mysqld runs as the mysql user the instance management needs to be done as root (stopping, starting instances, or default access). If you want to run multiple instances you can also use mysqld_multi, but that’s not the default setup.

So this is not ideal. While this may not be much of an issue if you manage a single server, if you manage tens or hundreds things changes somewhat and you REALLY want to manage all instances consistently.

So I’ve been thinking about finding an alternative which would suit me better. What do I need?

• The same treatment of a single instance or multiples on one box.
• Management as much as possible from a non-root user (mysql?)
• Separation of instances into separate “areas”, along the lines of Oracle’s Optimal Flexible Architecture.
• Automatic startup on server boot, and shutdown of “SELECTED” instances.
• Ability to support different versions of mysql running at the same time.
• Straight forward mechanism to upgrade a server or switch the version of MySQL that is used to manage a specific instance..

I was thinking of writing my own scripts but one solution which looks potentially close enough to my ideal solution would be to use Giuseppe Maxia’s MySQL Sandbox. I think this project was born with a completely different goal (enable you to quickly get a different mysql version up and running for testing), but it actually seems to solve most of the things I want for managing production instances.

If you’ve not used it yet take a look. It works pretty well and is easy to setup.

So what can it do at the moment:

• Allow you to install different versions of mysql on the same box.
• Run everthing as a non-root user
• Stop and start either one or all instances easily

For my needs it seems to be missing:

• The ability to indicate which instances should start / stop and a way to trigger this from init during startup / shutdown.
• The way to access the instances is quite different from a normal mysql installation. That has confused me quite a bit and as I use the Sandbox infrequently I have to go back and workout how to “get in” and also how to start or stop any instance, or check if it’s running.
• I’m not entirely sure if it’s easy to adjust the paths for the different mysql “areas” to fit in with my usage of MySQL or something similar to OFA. These things are probably pretty easy to fix.
• The name MySQL Sandbox sounds rather inappropriate if you really used it to manage mysql instances. That’s easy to change.

Ideal MySQL Layout

So how would I like the layout to look like?

Tarball binaries should probably be located somewhere like /path/to/some_binary_name, which allows us to distinguish between different architectures (i686, x86_64), and versions (5.0.89, 5.1.42, 5.5.0-m2). That’s already solved by the current tarballs which have a prefix such as mysql-5.5.0-m2-osx10.5-x86.

Each instance’s data should probably be located along the lines of:

/path/to/instance_name/data (for MySQL data files and my.cnf)
/path/to/instance_name/tmp (for MySQL tmpdir)
/path/to/instance_name/log (for MySQL logfiles (binlog and relaylog)

This allows the locations under /path/to/ to be mounted on different file systems for performance (if needed). In my case /path/to is simply /mysql, but that does not need to be that rigid. OFA is slightly different but the idea is the same.

Accessing the Instances

How do I access the instances?

Currently I access the instances using the following mechanism. Each instance has a name so that name is used to access the instance as a prefix of the normal mysql command.

So instance “a” is “managed”/”accessed” as follows:

mysql_a access to mysql command line
mysqldump_a access to mysqldump
mysqladmin_a access to mysqladmin
…

Oracle does something slightly different by setting up the environment to determine which instance to be accessed. Then the normal command line utilities talk to the right instance.

Either way is fine but it should be very clear which instance you are attempting to access to avoid what could be costly mistakes.

It would be nice if the sandbox provided a common directory which could be included in $PATH so that all instances could be accessed from one place.

I don’t currently have a managed way to start / stop the instances. A file like /etc/oratab would do and then a command like:

mysql_init start # start all ‘marked’ instances
mysql_init stop # stop all ‘marked’ instances
mysql_init_a start # start instance a

The final thing to want to do is to upgrade an instance. Normally this involves:

• Downloading the new version.
• Stopping the instance to be upgraded.
• Adjusting links/directories.
• Perform various “admin tasks” to upgrade the instance.
• Starting it normally again.

All that MySQL sandbox would need to do would be to perform 3.

So these are some of my ideas. I need to look at the MySQL Sandbox code in more detail to see if it will fit my needs but I expect it should not be too difficult to adjust it if needed.

Perhaps if you use other operating systems or package managers and manage one or more MySQL instances on multiple servers these issues are less of a problem. I have not seen comments by others worrying about some of the limitations of the current MySQL rpms and the problem is not really with rpm itself but with the expected usage of a single instance on a single server.

So is there a better way to do this or might MySQL Sandbox be the right solution to achieve my goal?

PlanetMySQL Voting: Vote UP / Vote DOWN

So you're a small startup company, ready to go live with your product, which you intend to distribute under an Open Source License. Congratulations, you made a wise decision! Your developers have been hacking away frantically, getting the code in good shape for the initial launch. Now it's time to look into what else needs to be built and setup, so you're ready to welcome the first members of your new community and to ensure they are coming back!

Keep the following saying in mind, which especially holds true in the Open Source world: "You never get a second chance to make a first impression!". While the most important thing is of course to have a compelling and useful product, this blog post is an attempt to highlight some other aspects about community building and providing the adequate infrastructure. This insight is based on my own experiences and my observations from talking with many people involved in OSS startups and projects.

Continue reading "Some friendly advice for bootstrapping your OSS project"
PlanetMySQL Voting: Vote UP / Vote DOWN