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

IOUG has a free series of three webinars on upgrading MySQL. Each webinar is an hour long, and it starts with a webinar by me tomorrow at 12 noon Central time (GMT-5) on “Why and How to Upgrade to MySQL 5.1″. The webinar assumes you are upgrading from MySQL 5.0 to MySQL 5.1, and talks a little bit about the new features, server variables, and what you need to know when upgrading to MySQL 5.1.

The software used is GoToWebinar (formerly GoToMeeting), so you will need to install that software. To register, use the links on the IOUG MySQL Upgrade Webinar Series page.

The complete list of webinars in the MySQL Upgrade Series is:
* MySQL 5.1: Why and How to Upgrade
Sheeri Cabral, The Pythian Group
Tuesday, July 27, 12:00 p.m. – 1:00 p.m. CT (GMT-5)

* MySQL Upgrades With No Downtime
Sean Hull, Heavyweight Internet Group
Wednesday, July 28, 12:00 p.m. – 1:00 p.m. CT (GMT-5)

* MySQL Upgrade Best Practices
Matt Yonkovit, Percona
Thursday, July 29, 12:00 p.m. – 1:00 p.m. CT (GMT-5)

(note, I am not sure if it is free for everyone or just free for IOUG members; my apologies if it is the latter)

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL has released security updates for MySQL 5.1.47 and 5.0.91. The most important changes in these releases are fixes of three security bugs. One of them is a problem that had been lurking in the code for many years, and it was found by chance when one of our developers, testing something unrelated, stumbled upon one of the vulnerabilities. Later on, when analyzing the bug, the developers found one more issue, and they fixed it as well.

MySQL 5.1.47

In addition to the security update, MySQL 5.1.47 is also very important for an additional reason. The InnoDB plugin that ships with this version has been updated to 1.0.8, which is considered to be of General Availability (GA) quality.

There are more changes, including some twists to the error log, to make replication administration more robust.

MySQL 5.0.91 security update

Together with MySQl 5.1.47, there is a security update of MySQL 5.0.91.

Since MySQL 5.0 is now in Extended Support state, the binaries are not in the main download pages, but only in the archives. As the MySQL Lifecycle Policy says, only serious security bugs are fixed, and the binaries are provided at the company's discretion.

If you are still using MySQL 5.0, this is a good moment to upgrade to 5.1.

PlanetMySQL Voting: Vote UP / Vote DOWN

Since the time that I’ve been using MySQL I have filed quite a few bug reports. Some of these have been fixed and many of the bug reports are actually new feature requests. While working with MySQL Enterprise Monitor I’ve probably filed more feature requests than bug reports.

That’s fine of course and my opinion of what is needed in MySQL or Merlin is one thing,  yours or the MySQL developers is something else. We all have our own needs and find things missing which would solve our specific problems.

If I have ten feature requests open and only one could be added to the software I’d also like to be able to say: this feature is the most important one for me.

However, it seems to me that there is no easy way in the mysql bug tracker at the moment to group together different types of new feature requests into groups of related features and then see the different types of requested features. I imagine many feature requests may be quite similar, but as I do not have a lot of time to look at all bugs it is easy to lose track of the things that people are asking for. It’s also likely that others who might be interested in my feature request are not aware of the request or able to say “I’d like this too”.

Having a clearer list of requested new features, especially if you have a clearer idea of how many people are interested in these new features (whether paying customers or not) would surely be a good way of guiding the product’s development in the way which would be useful to a wider audience. Is there any way this can be done with MySQL, and how is this done with other products which also are complex and have “insufficient resources” to be able to satisfy everyone’s wish?

Currently I do not feel that I can see where MySQL is going or work out if features that I need might actually be implemented in a reasonable time span (or at all) and that is rather frustrating. Some of the “Enterprise” type features that I think are important such as better partition management (variables such as innodb_file_per_table really suck, but the alternatives of X ibdata files which you can’t manage properly are even worse), better replication (taking out the replication process and putting into a separate daemon which would allow you to do N:1 replication, currently impossible in the current MySQL implementation but actually very useful if you want to have multiple sets of replicated databases each handling their own dataset, but with one or more central servers which see the whole combined dataset) are just larger more complex examples but many simpler changes are also important and some I get told will happen after MySQL 7. For me that’s never never land….

So is there a way that this can all be done more transparentlly?

PlanetMySQL Voting: Vote UP / Vote DOWN

Since the time that I’ve been using MySQL I have filed quite a few bug reports. Some of these have been fixed and many of the bug reports are actually new feature requests. While working with MySQL Enterprise Monitor I’ve probably filed more feature requests than bug reports.

That’s fine of course and my opinion of what is needed in MySQL or Merlin is one thing,  yours or the MySQL developers is something else. We all have our own needs and find things missing which would solve our specific problems.

If I have ten feature requests open and only one could be added to the software I’d also like to be able to say: this feature is the most important one for me.

However, it seems to me that there is no easy way in the mysql bug tracker at the moment to group together different types of new feature requests into groups of related features and then see the different types of requested features. I imagine many feature requests may be quite similar, but as I do not have a lot of time to look at all bugs it is easy to lose track of the things that people are asking for. It’s also likely that others who might be interested in my feature request are not aware of the request or able to say “I’d like this too”.

Having a clearer list of requested new features, especially if you have a clearer idea of how many people are interested in these new features (whether paying customers or not) would surely be a good way of guiding the product’s development in the way which would be useful to a wider audience. Is there any way this can be done with MySQL, and how is this done with other products which also are complex and have “insufficient resources” to be able to satisfy everyone’s wish?

Currently I do not feel that I can see where MySQL is going or work out if features that I need might actually be implemented in a reasonable time span (or at all) and that is rather frustrating. Some of the “Enterprise” type features that I think are important such as better partition management (variables such as innodb_file_per_table really suck, but the alternatives of X ibdata files which you can’t manage properly are even worse), better replication (taking out the replication process and putting into a separate daemon which would allow you to do N:1 replication, currently impossible in the current MySQL implementation but actually very useful if you want to have multiple sets of replicated databases each handling their own dataset, but with one or more central servers which see the whole combined dataset) are just larger more complex examples but many simpler changes are also important and some I get told will happen after MySQL 7. For me that’s never never land….

So is there a way that this can all be done more transparentlly?

PlanetMySQL Voting: Vote UP / Vote DOWN

Since the time that I’ve been using MySQL I have filed quite a few bug reports. Some of these have been fixed and many of the bug reports are actually new feature requests. While working with MySQL Enterprise Monitor I’ve probably filed more feature requests than bug reports.

That’s fine of course and my opinion of what is needed in MySQL or Merlin is one thing,  yours or the MySQL developers is something else. We all have our own needs and find things missing which would solve our specific problems.

If I have ten feature requests open and only one could be added to the software I’d also like to be able to say: this feature is the most important one for me.

However, it seems to me that there is no easy way in the mysql bug tracker at the moment to group together different types of new feature requests into groups of related features and then see the different types of requested features. I imagine many feature requests may be quite similar, but as I do not have a lot of time to look at all bugs it is easy to lose track of the things that people are asking for. It’s also likely that others who might be interested in my feature request are not aware of the request or able to say “I’d like this too”.

Having a clearer list of requested new features, especially if you have a clearer idea of how many people are interested in these new features (whether paying customers or not) would surely be a good way of guiding the product’s development in the way which would be useful to a wider audience. Is there any way this can be done with MySQL, and how is this done with other products which also are complex and have “insufficient resources” to be able to satisfy everyone’s wish?

Currently I do not feel that I can see where MySQL is going or work out if features that I need might actually be implemented in a reasonable time span (or at all) and that is rather frustrating. Some of the “Enterprise” type features that I think are important such as better partition management (variables such as innodb_file_per_table really suck, but the alternatives of X ibdata files which you can’t manage properly are even worse), better replication (taking out the replication process and putting into a separate daemon which would allow you to do N:1 replication, currently impossible in the current MySQL implementation but actually very useful if you want to have multiple sets of replicated databases each handling their own dataset, but with one or more central servers which see the whole combined dataset) are just larger more complex examples but many simpler changes are also important and some I get told will happen after MySQL 7. For me that’s never never land….

So is there a way that this can all be done more transparentlly?

PlanetMySQL Voting: Vote UP / Vote DOWN

Since the time that I’ve been using MySQL I have filed quite a few bug reports. Some of these have been fixed and many of the bug reports are actually new feature requests. While working with MySQL Enterprise Monitor I’ve probably filed more feature requests than bug reports.

That’s fine of course and my opinion of what is needed in MySQL or Merlin is one thing,  yours or the MySQL developers is something else. We all have our own needs and find things missing which would solve our specific problems.

If I have ten feature requests open and only one could be added to the software I’d also like to be able to say: this feature is the most important one for me.

However, it seems to me that there is no easy way in the mysql bug tracker at the moment to group together different types of new feature requests into groups of related features and then see the different types of requested features. I imagine many feature requests may be quite similar, but as I do not have a lot of time to look at all bugs it is easy to lose track of the things that people are asking for. It’s also likely that others who might be interested in my feature request are not aware of the request or able to say “I’d like this too”.

Having a clearer list of requested new features, especially if you have a clearer idea of how many people are interested in these new features (whether paying customers or not) would surely be a good way of guiding the product’s development in the way which would be useful to a wider audience. Is there any way this can be done with MySQL, and how is this done with other products which also are complex and have “insufficient resources” to be able to satisfy everyone’s wish?

Currently I do not feel that I can see where MySQL is going or work out if features that I need might actually be implemented in a reasonable time span (or at all) and that is rather frustrating. Some of the “Enterprise” type features that I think are important such as better partition management (variables such as innodb_file_per_table really suck, but the alternatives of X ibdata files which you can’t manage properly are even worse), better replication (taking out the replication process and putting into a separate daemon which would allow you to do N:1 replication, currently impossible in the current MySQL implementation but actually very useful if you want to have multiple sets of replicated databases each handling their own dataset, but with one or more central servers which see the whole combined dataset) are just larger more complex examples but many simpler changes are also important and some I get told will happen after MySQL 7. For me that’s never never land….

So is there a way that this can all be done more transparentlly?

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

If you have recently downloaded or checked out a new version of Kontrollbase and find that you need to upgrade the schema from a previous version then you will probably find this post useful. If you don’t know if you need to upgrade then you probably do, so read along for a bit just to [...]
PlanetMySQL Voting: Vote UP / Vote DOWN