Pythian Group

On many of our clients, we have a need to run XtraBackup as a regular OS user. Aside from running into the issue where tar4ibd was not provided with Percona’s xtrabackup-1.6.2.tar.gz package, our main issues have been with permissions when attempting a streaming backup.

I have found the following:

1. The user needs permissions for a temp directory to stream to/from. The my.cnf of the target database cannot be used because the user does not have permission to write to /tmp/mysql-stdout, so we set a tmpdir in a separate defaults-file.
2. A backup target directory must be used that the user has read/write permissions to. It seems to me a target directory should not be needed for a streaming backup, but it is.
3. A user who has SUPER privileges must be used to connect to the target database.
4. Group and datadir permissions:

• The primary group for the user should be the same as the mysql user running the target database, i.e. pythian:mysql.
• The user must have 770 access to the database schema directories.
• The user must have 740 access to the all files under the mysql datadir.

Here is the innobackupex command that works for us.

$ innobackupex --defaults-file=/home/pythian/my.cnf --user=root --stream=tar ./ | gzip - > /home/pythian/backups/2011-08-02_backup.tar.gz

So if you are seeing errors such as,

tar: -: Cannot write: Broken pipe
sh: /tmp/mysql-stout: Permission denied

do yourself a favor and run through this permission checklist. I hope it saves a lot of headaches.

In The Doom of Multiple Storage Engines, Peter talks about how the storage engine concept of MySQL is usually spoken of in positive terms, but there are many negatives.

I have a hard time trying to figure out the deeper meaning behind Peter’s post, given that Percona writes a storage engine for MySQL, XtraDB. Does this mean that Percona will stop developing XtraDB? Does this mean that the Percona Server will diverge farther and farther away from MySQL so that they’re not compatible any more and migrating from MySQL to Percona Server is very difficult?

Or maybe it’s just that Peter is saying one thing and doing the opposite; which just seems wrong because that would be blatant hypocrisy on Percona’s part.

(This idea was a comment on the blog post but seems to be trapped in the spam filter, so I’m posting it; apologies if the comment comes through eventually….)

My own opinion of the issue: Peter is factually correct with what he says. However, it’s nice to have the framework and be allowed to use more than one storage engine, or use exclusively one storage engine that’s not MyISAM.

I am writing because Sheeri sent me a note about a blog post written by Brian Aker, where Brian concludes, quite correctly, that (in Sheeri’s words not Brian’s)


MySQL is now just a branch (the official branch,
but a branch nonetheless, and a bunch of trademark (logo) and
copyright (docs) ownerships).


This is exactly true. No denying it. Why bother. It’s true. It’s also true for the vast majority of open-source projects, by the way.

I replied to Sheeri:


There's no denying that. The product direction will be set by whoever sets the best product management strategy backed by the most effective development effort. And there can be multiple winners.
-Paul


Well, this is the kind of quality output I can be relied on. It might not fit on twitter, but it’s not blogworthy. Sheeri’s word of encouragement:


See, now that would be a nice blog post with a positive outlook that
both Oracle Corp and MySQL community would agree and be happy with,
because both Oracle Corp and the MySQL community feel they can set
"the best product management strategy backed by the most effective
development effort."
-Sheeri


God. My reply was embarassing but maybe I should include it for humour value:


Go for it. Its a tweet for me at the most. No time to expand that thinking into a blog worthy of the blog today.
-Paul


and then, right away,


ah censored it i'll do it.
it'll be short.
-paul


You are now reading the result of this very modest effort.

Here’s the future of MySQL, Drizzle, Monty Program, the Percona fork, etc.

The best product management strategies… should we be lightweight for the web, plug-in oriented like Drizzle? Should we follow Monty’s giant-killing roadmap? Should we focus on performance-oriented patches? The best product management strategies will win.

They can’t win alone. Will they be backed by appropriate investments from effective developers? Effective developers are the ones who convert winning product management strategies into working products. You can’t get there without them and I’ve seen lots of great strategies fail that test (including my own actually).

And there can be more than one winner.

It’s doesn’t matter what roadmap Oracle plots for MySQL. If it’s not the roadmap the community wants, it will lose ground and open an opportunity for another fork. If it is, however, (and NEVER, NEVER underestimate Oracle’s product management because it is outstanding and a big component of their historical success), if it is, however, Oracle can win the long-term hearts and minds, because they can resource quality developers in a way that I don’t think any of the competing forks are capitalized to do (yet.)

Either way, it’s going to be fun to watch.

And more than one player can win.

And regardless, the community wins. Big time.

Recently while we were building a slave with a newer version of MySQL 5.1 from an InnoDB Hot backup, the following error occurred when we ran mysql_upgrade:

mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
ERROR 13 (HY000) at line 311: Can't get stat of './mysql/general_log.CSV' (Errcode: 2)
ERROR 13 (HY000) at line 316: Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)
FATAL ERROR: Upgrade failed

The problem is that in MySQL 5.1, it is possible to log the slow query log and general log to tables in the mysql schema (source: Selecting General Query and Slow Query Log Output Destinations). These tables are created by default as CSV tables for performance reasons, and are created even if MySQL is set not to log to tables.

CSV tables, however, are not copied as part of the InnoDB Hot Backup process (by the wrapper script innobackupex.pl), thus creating this error. The fix to get the slave working Read the rest of this entry . . .