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

In my opinion, The Server Option and Variable Reference at http://dev.mysql.com/doc/refman/5.5/en/mysqld-option-tables.html rates as my most important page. This is a consolidated index that enables a drill down to the Server Command Options, System Variables, Startup and replication specifics, as well as important information on default values and differences between versions including point releases.

However, there is another page not in the actual manual, but at http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-optvar.html which is an Options/Variables reference akin to the Reference Manual, but includes a 5.x version matrix.

Recently I was asked about some options that had to be removed from an upgrade to MySQL 5.5. Some of these were obvious, however not all. This page enabled me to confirm deprecation (as expected), and also point to important reference material.

These options where:

• default_table_type
• log_long_format
• master-connect-retry
• default-character-set

The use of table_type was a 3.x/4.x relic, replaced with engine, so I was surprised this option was still even valid. The option replaced with default_storage_engine. The page defined this as deprecated in MySQL 5.0
log_long_format is also old, and definitely modified since MySQL 5.1 with the general_log_xxx options. In fact this has been deprecated since 4.1
I have never liked the master-xxx options, in favor of a CHANGE MASTER command and synchronization issues with the master.info file and master-xx options. master-connect-retry and several other options were deprecated in 5.1.17. On a side note, if you look at this option in the MySQL 5.5 Reference Manual you get Obsolete options. The following options are removed in MySQL 5.5. If you attempt to start mysqld with any of these options in MySQL 5.5, the server aborts with an unknown variable error. To set the replication parameters formerly associated with these options, you must use the CHANGE MASTER TO … statement (see Section 12.4.2.1, “CHANGE MASTER TO Syntax”).
Finally default-character-set. Initially I thought that is definitely still applicable, however the handy cross reference reminded me, this is also deprecated in MySQL 5.0 and the Reference Manual again stating default-character-set is also deprecated in 5.0 in favor of character-set-server.. The name has simply changed in newer version.

With so many options and as a consultant I work with many different versions each week, I sometimes need a refresher of the changes in the versions of the past 5 years. Definitely my second most important page.

If you have a favorite page, please let me know.

I would also like to say thank you to the Oracle/MySQL Documentation team that do a great job in providing an excellent resource to an open source product. We would all do well to appreciate this in comparison to other open source documentation in companion technologies and related tools. With every new release of a MySQL product you don’t realize that somebody reviewed, tested and wrote about features without receive the limelight.

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL::Sandbox 3.0.24 was released yesterday, with many new features.

More than vanilla MySQL

If you have missed my previous announcement, here's the gist of it. MySQL Sandbox can now deal with tarballs from either Percona Server or MariaDB. The main difference after this change is that you can now create a directory called <PREFIX>5.5.16 and make_sandbox will recognize it as well as the plain 5.5.16.

$ make_sandbox --export_binaries --add_prefix=ps \
   Percona-Server-5.5.11-rel20.2-114.Darwin.i386.tar.gz  \
   -- --sandbox_directory=msb_ps5_5_11
unpacking Percona-Server-5.5.11-rel20.2-114.Darwin.i386.tar.gz
[…]
installing with the following parameters:
upper_directory                = /Users/gmax/sandboxes
sandbox_directory              = msb_ps5_5_11
[…]
basedir                        = $HOME/opt/mysql/ps5.5.11
tmpdir                         = 
[…]
Your sandbox server was installed in $HOME/sandboxes/msb_ps5_5_11

After the binary export, subsequent installations will be easier:

$ make_sandbox ps5.5.11

The same commands can be used for MariaDB. At the moment, make_sandbox does not recognize other packages, but adding them should not be a big deal, provided that such packages look like MySQL. It wouldn't work with Drizzle, because it lacks the main ingredients for MySQL installation.

High Performance sandboxes

While testing parallel replication and prefetch slaves with Tungsten Replicator, I realized that I was doing too much manual fiddling with my scripts. Since I need more performant servers, I added the basic items that I need to modify to enable a faster server. Now, using the '--high_performance' option with make_sandbox, you get a server that is much better than out-of-the-box MySQL. To avoid problems with too much RAM, I am using a default of 512 MB for InnoDB, which is not enough for really demanding tests, but at least it is a good placeholder in the sandbox configuration file, should you need to modify it.

$ make_sandbox 5.1.60 -- --high_performance
[…]
innodb-flush-method=O_DIRECT ; \
innodb-log-file-size=50M ; \
innodb_buffer_pool_size=512M ; \
max_allowed_packet=48M ; \
max-connections=350 ; \
innodb-additional-mem-pool-size=50M ; \
innodb-log-buffer-size=50M ; sync_binlog=0 ; \
innodb-thread-concurrency=0 ; log-error=msandbox.err
[…]

Standalone masters and slaves

MySQL Sandbox has had the ability of creating replicated systems for years. Yet, sometimes you need a stand-alone master server that you want to use for some odd experiment. Similarly, you may want to create a slave of a specific master without having a full replication system. One case where you would like this ability is when you want to try replicating between servers of different versions.

$ make_sandbox 5.1.57 -- --master
[…]
my_clause                      = server-id=5157 ; log-bin=mysql-bin ; log-error=msandbox.err
[…]
Your sandbox server was installed in $HOME/sandboxes/msb_5_1_57

$ make_sandbox 5.5.10 -- --slaveof='master_port=5157' 
[…]
my_clause                      = server-id=5510 ; log-bin=mysql-bin ; log-error=msandbox.err
[…]
Your sandbox server was installed in $HOME/sandboxes/msb_5_5_10


$ ~/sandboxes/msb_5_1_57/use -e 'show master status'
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      106 |              |                  |
+------------------+----------+--------------+------------------+

$ ~/sandboxes/msb_5_5_10/use -e 'show slave status\G'
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 5157
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysql_sandbox5510-relay-bin.000002
                Relay_Log_Pos: 252
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 420
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 5157

You can download MySQL::Sandbox from either launchpad or CPAN.
PlanetMySQL Voting: Vote UP / Vote DOWN

When I talked about the usability improvements of Tungsten Replicator, I did not mention the procedure for upgrading.I was reminded about it by a question in the TR mailing list, and since the question was very relevant, I updated the Tungsten Cookbook with some quick upgrading instructions.A quick upgrading procedure is as important as the installer. Since we release software quite often, either because we have scheduled features to release or because of bug fixes, users want to apply a new release to an existing installation without much fuss. You can do the upgrade with a very quick and painless procedure.Let's suppose that you have installed one Tungsten Replicator cluster using this command:

#
# using tungsten-replicator 2.0.4
#
TUNGSTEN_HOME=/home/tungsten/installs/master_slave
./tools/tungsten-installer \
  --master-slave \
  --master-host=r1 \
  --datasource-user=tungsten \
  --datasource-password=secret \
  --service-name=dragon \
  --home-directory=$TUNGSTEN_HOME \
  --cluster-hosts=r1,r2,r3,r4 \
  --start-and-report

If you want to upgrade to the very latest Tungsten Replicator 2.0.5, build 321, this is what you need to do.

• Get the latest tarball, and expand it;
• Stop the replicator;
• Run the update command (this will also restart the replicator)
• Check that the replicator is running again.

The actual upgrade command is in bold in the following script.

#
# using tungsten-replicator 2.0.5-321 (get it from bit.ly/tr20_builds) 
#
TUNGSTEN_HOME=/home/tungsten/installs/master_slave
HOSTS=(r1 r2 r3 r4)
for HOST in ${HOSTS[*]} 
do 
   ssh $HOST $TUNGSTEN_HOME/tungsten/tungsten-replicator/bin/replicator stop 
   ./tools/update --host=$HOST --user=tungsten --release-directory=$TUNGSTEN_HOME -q 
   $$TUNGSTEN_HOME/tungsten/tungsten-replicator/bin/trepctl -host $HOST services
done

One benefit of this procedure, in addition to being brief and effective, is that the previous binaries are preserved.Before the upgrade, you will see:

$ ls -lh ~/installs/master_slave/ ~/installs/master_slave/releases
/home/tungsten/installs/master_slave/:
total 32K
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 backups
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 configs
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 relay
drwxrwxr-x 4 tungsten tungsten 4.0K Sep 22 22:06 releases
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 service-logs
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 share
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 thl
lrwxrwxrwx 1 tungsten tungsten   75 Sep 22 22:06 tungsten -> /home/tungsten/installs/master_slave/releases/tungsten-replicator-2.0.4
/home/tungsten/installs/master_slave/releases:
total 8.0K
drwxr-xr-x 6 tungsten tungsten 4.0K Sep 22 22:03 tungsten-replicator-2.0.4

The 'tungsten' directory is a symlink to the actual binaries inside the 'releases' directory.After the upgrade, the same directory looks like this:

ls -lh ~/installs/master_slave/ ~/installs/master_slave/releases
/home/tungsten/installs/master_slave/:
total 32K
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 backups
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 configs
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 relay
drwxrwxr-x 4 tungsten tungsten 4.0K Sep 22 22:06 releases
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 service-logs
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 share
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 thl
lrwxrwxrwx 1 tungsten tungsten   75 Sep 22 22:06 tungsten -> /home/tungsten/installs/master_slave/releases/tungsten-replicator-2.0.5-321

/home/tungsten/installs/master_slave/releases:
total 8.0K
drwxr-xr-x 6 tungsten tungsten 4.0K Sep 22 22:03 tungsten-replicator-2.0.4
drwxr-xr-x 6 tungsten tungsten 4.0K Sep 22 22:06 tungsten-replicator-2.0.5-321

If you did some manual change to the files in 2.0.4, you will be able to retrieve them. Upgrading from earlier versions of Tungsten Replicator is not as smooth. Since we changed the installation format, it has become incompatible from previous versions. Clusters running TR 2.0.3 need to be reinstalled manually. The next upgrade, though, will be much faster!
PlanetMySQL Voting: Vote UP / Vote DOWN

I see this message on our forums, and I think it’s a great question: “Which version of Percona Server is currently recommended?” It’s really the same question as “Which version of MySQL is currently recommended?” I’ll respond here and then post a link in the forum as a reply.

In my opinion, it’s important to qualify this question by understanding whether we’re talking about an existing MySQL installation, or a new one. The answer is different for each case. (There are other qualifying questions I’d ask too, but this is the biggest distinguisher).

For an existing MySQL database server, I’d encourage not jumping on a new version immediately when it comes out. Let some early adopters try it out first, and when it gets more broad deployment, then consider it. The reason I say this is that the previous version is going to be maintained quite actively for some time. You can continue to run the previous version safely and responsibly. A major upgrade should really be about features, not bug fixes or security patches; those critical fixes should be applied to the previous version. So if you’re not dying from the lack of a new feature that’s available in the new version, then there shouldn’t really be urgent pressure to upgrade.

For new deployments, I’d definitely start by using the most recent version from the very beginning, or maybe even using a release candidate of the next version if it’s available, especially if the new deployment isn’t in production yet. The reason is that I like to keep the upgrade cycle as long as possible, and if you’re going to production with something established or old, you’re going to have to look at an upgrade sooner. You might as well develop the application to the new or upcoming version in the first place. A properly tested upgrade is a fair amount of work, and it’s nothing to jump into. Sometimes I even try to skip a version if I can. A fair number of our customers are still running MySQL 5.0 and are now considering or executing upgrades to MySQL 5.5.

So what’s the bottom line?

• For new applications, I’ve said use MySQL 5.5 without hesitation for many months now, and now I’d even say use MySQL 5.6.
• For older applications running MySQL 5.0, I’d say upgrade to 5.5 directly, skipping over 5.1. MySQL 5.0 is really old now, and although Percona still supports it and will as long as we’re asked to, the reality is it’s slow, limited, and buggy in comparison with 5.5. Everyone on 5.0 just needs to be upgrading, even super-conservative companies.
• For applications running MySQL 5.1, you can safely keep doing it for a while if you are very conservative, but you really need to be thinking about when you’re going to leave 5.1 for something newer. And if you’re relatively agile and don’t need a lengthy process (weeks or months) to upgrade, you should be thinking about upgrading in the near future. At this point, MySQL 5.1 is starting to get old, and MySQL 5.5′s new releases have just minor fixes for edge-case problems, not major reworking as we saw in the 5.0 and 5.1 series.

That was a long bottom line. Here’s a short one: upgrade to 5.5 or 5.6, or plan to do it in the near future.

PlanetMySQL Voting: Vote UP / Vote DOWN

I have been meaning to downgrade my Vigor software due to the issue I reported last month. It certainly hasn’t gone away, though I guess that’s not unexpected. So today I was planning on doing the downgrade and went off to Draytek’s site. I now see a newer version released, V3.3.5, compared to the 3.3.4.1 [...]
PlanetMySQL Voting: Vote UP / Vote DOWN

I recently upgraded the firmware on my Draytek Vigor 2820n ADSL router. One thing that surprised me was that the change stopped me being able to access my public IP from behind the router. That is I have an internal LAN with RFC1918 addresses such as 192.168.x.x and could access my public ip address which gets routed back by the router to an internal host. The 3.3.4 firmware upgrade stops that working. That makes it a bit of a pain to test connectivity to sites like my web site from inside compared to from outside and it took me a while to figure out what had happened. In the end I’ve had to implement split DNS so that internal references to the “external sites” I provide resolve to internal ip addresses internally but the public see my external ip address. A lot of work for a home network but otherwise things just don’t work properly.

So if you have a Vigor 2820n then be aware of the change in behaviour from the previous version of the firmware.

PlanetMySQL Voting: Vote UP / Vote DOWN

One of the requirements of owning a domain is that you must give your contact details when registering that domain.

Internet regulations require that your name, address, email, and phone number be accessible 24/7 to anybody who wants to see them just by looking up your domain. This rule was created to make it easy to contact a domain owner in case of problems, but it has since been exploited by spammers and marketers to bother people. Your personal information is exposed 24 hours a day, everyday, to anyone, anywhere.

Unfortunately since this information is publicly available it may be used by spammers and marketers to send unsolicited messages, or by others who want to harass a domain owner.

You have the power to change this. With a private (“unlisted”) registration through Domains By Proxy (our affiliate company), registering a domain name doesn’t mean sacrificing your privacy. Their name is shown on the registration instead of yours, but you still retain the full benefits of full registration.

Starting today you can add the private registration upgrade when you buy a domain, or if you have an existing domain registered on WordPress.com you can make it private at any time. If your domain expires in less than six months in the current year you’ll get the rest of the year free but you will have to renew the domain and cover the cost of the privacy upgrade for the remainder of the domain’s lifetime.
For example, if it’s July, and your domain expires in two months in September, you can renew the domain today and only be charged for the renewal and private registration upgrade for the coming year starting in September. We’ll give you two free months of private registration upgrade.

The private domain registrations upgrade costs $8 a year and you must purchase the upgrade for the length of time your domain is registered for.

PlanetMySQL Voting: Vote UP / Vote DOWN

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