Databases

Exclude Tables in Percona’s Innobackupex Tool

└ Posted: March 5th, 2012 by Derek Downey

Percona has developed some pretty amazing tools, and at the top of my list is their innobackupex script; a wrapper for the xtrabackup tool (also developed by Percona).

Innobackupex is a great tool that I have recently switched to use to create backups on my database, which is a mix of InnoDB and MyISAM storage engines. There is, however, one issue that innobackupex doesn’t appear to handle natively: I would like to exclude certain tables from a regular backup.

In my dataset, I have about 500 tables in 3 schemas. In the main schema, I have audit tables mixed with the main relational data. 90% of the backup size comes from these audit tables, so I would like to exclude them to be able to take full backups quickly and be able to restore even more quickly in the event of an emergency.

Innobackupex does provide the ability to create partial backups, which allows for including a list of tables or databases through three different methods:

  • 1) a regex pattern passed to the ‘–include’ option
  • 2) a list of tables in a file using the ‘–tables-file’ option
  • 3) A list of databases and tables passed to the ‘–databases’ option

However, it does not appear that one can exclude a list of tables from the backup process. In my case, all of my audit tables are preceded with the ‘track_’ prefix. So naturally, I attempted to use the ‘include’ option to pass a regex to include everything except ‘track’:

innobackupex --include='[^track\_]' /path/to/backup

No dice. Looking at the other two methods, I developed an idea that does work. Using MySQL’s INFORMATION_SCHEMA, I am able to generate a list of tables to put into a tables file which will be included in the file.

$ mysql --skip-column-names -e "SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_name NOT REGEXP '^track_';" > tables.txt
$ innobackupex --tables-file=tables.txt /path/to/backup

These partial backups will dramatically improve the speed to restore vital information that may have accidentally been deleted. But even though I do these partial backups, I also create daily full backups that does include the audit information.

It is worth noting that I found this old patch on innobackupex-1.5.1 that attempted to add an ‘–exclude’ option, but since it was discarded I did not attempt to use it.

Tags: , , , , , ,
 Comment 
Databases

MySQL SHOW GLOBAL STATUS

└ Posted: November 9th, 2011 by Derek Downey

A friendly reminder: when checking your MySQL server’s status for certain status counters, be sure you know what you are looking at. Some counters are GLOBAL-only, but many of them are both SESSION and GLOBAL. Let me show an example of the difference.

If I want to get the number of temporary tables created on disk per second, I would look at the ‘Created_tmp_disk_tables’ status variables and divide that by the ‘Uptime’

mysql> SHOW STATUS LIKE '%uptime';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Uptime        | 2981332 |
+---------------+---------+
1 row in set (0.04 sec)

SHOW STATUS LIKE '%tmp%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 0      |
| Created_tmp_files       | 984448 |
| Created_tmp_tables      | 2      |
+-------------------------+--------+
3 rows in set (0.04 sec)

Wait, ‘Created_tmp_disk_tables’ is 0? Oh right, that’s my session status.

SHOW GLOBAL STATUS LIKE '%tmp%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Created_tmp_disk_tables | 8526619  |
| Created_tmp_files       | 984498   |
| Created_tmp_tables      | 48701810 |
+-------------------------+----------+
3 rows in set (0.04 sec)

That’s better. So don’t forget to check the correct STATUS for what you are looking for.

Tags: ,
 Comment 
Databases

Homebrew on Mac OSX Lion

└ Posted: November 2nd, 2011 by Derek Downey

I have finally replaced MacPorts with Homebrew on my Macbook Pro.

First, I made sure to follow the steps to remove any packages and macports itself.

derekd$ sudo port -fp uninstall --follow-dependents installed
derekd$sudo rm -rf \
    /opt/local \
    /Applications/DarwinPorts \
    /Applications/MacPorts \
    /Library/LaunchDaemons/org.macports.* \
    /Library/Receipts/DarwinPorts*.pkg \
    /Library/Receipts/MacPorts*.pkg \
    /Library/StartupItems/DarwinPortsStartup \
    /Library/Tcl/darwinports1.0 \
    /Library/Tcl/macports1.0 \
    ~/.macports

Disclaimer: Remove directories at your own risk. Steps to uninstall MacPorts found here.

And Homebrew installed just as easily!

/usr/bin/ruby -e "$(curl -fsSL https://raw.github.com/gist/323731)"

And that’s it. Further instructions can be found on Homebrew’s installation guide

My first impressions of Homebrew are that it is really easy to install and use. Even though I am masochistic and enjoy building things from source, I have gotten used to the yum package manager on RedHat production machines at work. It is nice to have something similar on my MacBook Pro.

Beware!

I had uninstalled wget from MacPorts. When building from source (yes, I still do that), some packages require wget. In my case, installing Percona Server from Bazaar requires wget. So be sure to re-install wget: 

brew install wget

Tags: , , , , , , ,
1 Comment
Databases

Install Percona on Mac OSX Lion

└ Posted: October 28th, 2011 by Derek Downey

Lead-in

Recently I mentioned my intention to install Percona Server on my MacBook Pro for development purposes. I will admit it was a mild rant because I ran into some issues with installing Percona.

(Un)fortunately for me, the issues with installing were largely due to my ignorance of various build flags and what each purpose was for and I was able to resolve most of them. So without further ado, I present the steps that I used to successfully build Percona Server from bazaar on Mac OSX Lion.

Prepare the environment

This is the software you will need to install*:

- XCode
- CMake
- Bazaar
- Percona Server with XtraDB

*Some of the above might need further things installed. That is beyond the scope of this post.

Actually, I am going to assume you have XCode installed. You need it for the compiling programs like gcc and autoconf. If you don’t have it, click the link above and get it ASAP.

Now then, MySQL 5.5 decided to start utilizing the CMake program to compile source, so that is next on the list. You can get it from the link above. They have a .dmg file for Macs, but I compiled it from the linux source file (.tar.gz), version 2.8.6 in this post.

$ cd ~/sourceFiles
derekd$ wget http://www.cmake.org/files/v2.8/cmake-2.8.6.tar.gz
derekd$ tar -xzvf cmake-2.8.6.tar.gz
derekd$ cd cmake-2.8.6.tar.gz
derekd$ ./bootstrap && make && sudo make install

Now, you will need to install Bazaar, as that’s the source control that MySQL/Percona uses. Using the link above will actually get you to some installation instructions that are exactly what I used. At the time of this writing, Bazaar does not have an install file for Mac OSX Lion, so use the one for Snow Leopard.

I want to highlight one issue. Mac OSX Lion defaults to Python version 2.7, but Bazaar needs v2.6 at the time of this writing. So if you don’t have virtual environments set up (outside scope of this article), set your default version of Python to 2.6:

derekd$ defaults write com.apple.versioner.python Version 2.6

So after installing from the .dmg file, you should have everything you need to install Percona Server.

Percona Server with XtraDB

As it turns out, Percona Server installs by taking the version of MySQL it has as its base, and applying Percona patches to the code, prior to compilation. These steps should get you an installed copy of Percona:

derekd$ cd ~/devel
derekd$ bzr init-repo ./percona-server
derekd$ cd ./percona-server
derekd$ bzr branch lp:percona-server/5.5
derekd$ cd 5.5
derekd$ make #this step downloads mysql and applies the percona packages
derekd$ cd Percona-Server
derekd$ BUILD/autorun.sh
derekd$ CFLAGS="-arch x86_64 -Wall -Wextra -Wunused -Wwrite-strings -mtune=native -m64 -DUNIV_MUST_NOT_INLINE -DEXTRA_DEBUG -DFORCE_INIT_OF_VARS  -DSAFE_MUTEX -O0 -g3 -gdwarf-2" CXXFLAGS="-arch x86_64 -Wall -Wextra -Wunused -Wwrite-strings -Wno-unused-parameter -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mtune=native -m64 -DUNIV_MUST_NOT_INLINE -DEXTRA_DEBUG -DFORCE_INIT_OF_VARS  -DSAFE_MUTEX -O0 -g3 -gdwarf-2" ./configure  --enable-thread-safe-client --with-mysqld-user=_mysql --enable-shared --with-plugins=max  --with-extra-charsets=complex --with-big-tables --with-ssl --with-readline --enable-assembler

derekd$ make
derekd$ sudo make install

And that should be it. You can follow normal MySQL installation steps after this step.

A word about Configuration options

The above configure script was taken mostly from the BUILD/compile-pentium64-debug-max script. I was forced to remove the –with-debug option on the Mac, because it forced warnings to be handled as errors, and percona has added a couple variables that are only used inside a block that is not applicable on Mac OSX. EG, in sql/sql_prepare.cc:

#ifdef HAVE_CLOCK_GETTIME
    /* get end cputime */
    if (!cputime_error && 
        !(cputime_error= clock_gettime(CLOCK_THREAD_CPUTIME_ID, &tp)))
      end_cpu_nsecs= tp.tv_sec*1000000000.0+tp.tv_nsec;                       
#endif

tp is declared outside the block, and clock_gettime is not implemented on Mac OSX. (src)

Update: 2011-11-02

I have been able to compile a –with-debug version from bazaar with the following:

derekd$ make clean && rm CMakeCache.txt
derekd$ CFLAGS="-arch x86_64 -Wall -Wextra -Wunused -Wwrite-strings -mtune=native -m64 -DUNIV_MUST_NOT_INLINE -DEXTRA_DEBUG -DFORCE_INIT_OF_VARS  -DSAFE_MUTEX -O1 -g3 -gdwarf-2" \
CXXFLAGS="-arch x86_64 -Wall -Wextra -Wunused -Wwrite-strings -Wno-unused-parameter -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mtune=native -m64 -DUNIV_MUST_NOT_INLINE -DEXTRA_DEBUG -DFORCE_INIT_OF_VARS  -DSAFE_MUTEX -O2 -g3 -gdwarf-2" \
./configure  --enable-thread-safe-client --with-mysqld-user=_mysql --enable-shared --with-plugins=max  --with-extra-charsets=complex --with-big-tables --with-ssl --with-readline --enable-assembler --with-debug --disable-mysql-maintainer-mode
derekd$ make

The issue was that –with-debug enables the -Werror CFLAG. To override this, provide –disable-mysql-maintainer-mode. Thanks to Stewart Smith at Percona for providing this information.

Also, I have tested this build command on Percona Server 5.1 and 5.5, and it works on both servers on my MacBook Pro running Mac OSX 10.7 Lion. Eventually I will convert to the cmake directives, but for now, if it ain’t baroque, don’t fix it.

Tags: , , , , , ,
1 Comment
Databases

Percona on Mac OSX

└ Posted: October 27th, 2011 by Derek Downey
Two birds with one stone

Two birds with one stone

Today I finally decided to give the MySQL-based fork Percona a try. Percona is going to be installed on my MacBook….soon™

I’ve been delving into MySQL 5.5 source code recently as hobby work, but have also been reading up on Percona’s performance enhancements for work. So I was going to try to kill two birds with one stone and work on Percona source code for my hobby.

However:

Quote: “Again, we do not support Mac OS X, but you may try to get it fixed.” src

I am not giving up hope though; I will be striving to get Percona installed on my MacBook Pro running Mac OSX Lion. I will be back with the steps to install from bazaar branch to working server.

* I am in no way trying to belittle Percona’s offering; I want to work on getting MacOSX supported, thereby improving their product

Tags: , , ,
3 Comments
Stack Exchange

Ask and Stack Exchange Will Answer

└ Posted: October 24th, 2011 by Derek Downey

I need to reveal one of my fanboy sites. For a couple of years now, if you have ever googled a programming-related question, the odds were decent that a site called StackOverflow would be within the first few results.

So what is StackOverflow?

It is a community-driven question-and-answer site developed with one simple principle: Ask or answer questions related to programming (actual code questions), and the community will vote. If they vote your question up, the community thinks the question is useful. If they vote it down, not useful. The same principle applies to your answers. The entire system is reputation driven (if you get your questions/answers upvoted, you increase in reputation). As your reputation grows, you get access to more tools to aid in answering questions.

So why am I such a fan?

A year ago, I found out that StackOverflow is only ONE of the sites powered by StackExchange Engine. Another was ServerFault, dedicated to system administration questions. Then, I clicked a link taking me to an oddly-named ‘Area51‘ site. This site is dedicated to new Question and Answer topics. Literally, anything under the sun can become a new StackExchange site.

There is, however, a vetting process to a new StackExchange site. First the topic must proposed on Area51, then generate enough followers to warrant creating a new site. During this process, basic guidelines are created for what types of questions are welcome on the site and what kind are not. Once a site generates enough interest, it can enter a private beta for those who committed to the site. After a few weeks, it will be opened to the public to see if it can reach what StackExchange calls ‘Critical Mass’. If the site can meet certain requirements to indicate it can reach ‘Critical Mass’, it will become a full-fledged StackExchange site.

It just so happens that when I started looking at Area51, one of the proposed topics was for database administration. So I committed to this proposal. And so, in January, a StackExchange site dedicated to database administration went into private beta. Questions were asked, questions were answered. Eventually, Database Administrator graduated to a full StackExchange site.

While I appreciate the programming site, and the system administration site, and now database administration, I really love the idea that this engine can be used for any topic. They have one for Home Brewing, Cooking, Do-it-yourself home improvements, and the list goes on.

The flexibility of this engine is why I am in full support of the StackExchange engine. They only get dinged a little for using Microsoft technology to implement it.

Tags: , , ,
 Comment 
Databases

MySQL Proxy Users – SU emulation

└ Posted: October 23rd, 2011 by Derek Downey

Earlier this year (January 2011), a question was asked on Database Administrator regarding whether it was possible to masquerade as a user other than the one you log in as. That is to say, can you assume the permissions of another user, similar to how ‘su’ works on *nix?

At the time, the answer was ‘no’. However, with the release of MySQL 5.5.7, you can do this using the ‘Proxy Users‘ feature, which is combined with pluggable authentication.

Here I outline the steps to get a rudimentary ‘su’ working in MySQL 5.5.7 and above.

First, as root user:

  • 1) Install plugin
    mysql> INSTALL PLUGIN test_plugin_server SONAME 'auth_test_plugin.so';
Query OK, 0 rows affected (0.22 sec)

mysql> SHOW PLUGINS;
+-----------------------+----------+--------------------+---------------------+---------+
| Name                  | Status   | Type               | Library             | License |
+-----------------------+----------+--------------------+---------------------+---------+
| binlog                | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |
| mysql_native_password | ACTIVE   | AUTHENTICATION     | NULL                | GPL     |
| mysql_old_password    | ACTIVE   | AUTHENTICATION     | NULL                | GPL     |
| CSV                   | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |
| MEMORY                | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |
| MyISAM                | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |
| MRG_MYISAM            | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |
| ARCHIVE               | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |
| BLACKHOLE             | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |
| FEDERATED             | DISABLED | STORAGE ENGINE     | NULL                | GPL     |
| InnoDB                | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |
| INNODB_TRX            | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_LOCKS          | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_LOCK_WAITS     | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_CMP            | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_CMP_RESET      | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_CMPMEM         | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| INNODB_CMPMEM_RESET   | ACTIVE   | INFORMATION SCHEMA | NULL                | GPL     |
| PERFORMANCE_SCHEMA    | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |
| partition             | ACTIVE   | STORAGE ENGINE     | NULL                | GPL     |
| test_plugin_server    | ACTIVE   | AUTHENTICATION     | auth_test_plugin.so | GPL     |
+-----------------------+----------+--------------------+---------------------+---------+
21 rows in set (0.08 sec)
  • 2) Create user to emulate (if it doesn’t exist)
    mysql> GRANT ALL PRIVILEGES ON test.* TO dtest@localhost IDENTIFIED BY 'mypass';

  • 3) Create ‘proxy’ user and grant the PROXY privilege
    mysql> CREATE USER proxy@localhost IDENTIFIED WITH test_plugin_server AS 'dtest';
mysql> GRANT PROXY ON dtest@localhost TO proxy@localhost;
mysql> FLUSH PRIVILEGES;

Now, you should be able to login as user ‘proxy’ and have the same grants as ‘dtest’:

  • $ mysql -uproxy -pdtest
mysql> SELECT USER(), CURRENT_USER();
+-----------------+-----------------+
| USER()          | CURRENT_USER()  |
+-----------------+-----------------+
| proxy@localhost | dtest@localhost |
+-----------------+-----------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS;
+----------------------------------------------------------------------------------------------+
| Grants for dtest@localhost                                                                   |
+----------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dtest'@'localhost' IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'dtest'@'localhost'                                      |
+----------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Tags: , ,
 Comment 
Databases

MySQL Conference – Percona Live 2011

└ Posted: May 25th, 2011 by Derek Downey

My first MySQL conference will be Percona Live in New York City on May 26th, 2011. I am excited for the lineup of talks scheduled for the one-day event.

Reviewing the schedule of events, I am thrilled at all the choices. Here are my MySQL Picks for each time slot:

  • 10:15am – Breaking Down Query Latency With DTrace (Brendan Gregg)
  • 11:00am – MySQL High Availability With Pacemaker (Yves Trudeau)
  • 11:45am – MySQL Replication Alternatives: Pros & Cons (Darpan Dinker)
  • 1:30pm – Measuring Scalability and Performance With TCP (Baron Schwartz)
  • 2:15pm – Preventing MySQL Emergencies (Espen Braekken)
  • 3:00pm – Efficiently Monitoring MySQL with Nagios (Sheeri Cabral)
  • 3:30pm – How NoSQL Complements MySQL (Matt Yonkovit)
  • 4:15pm – Improvements in MySQL 5.5 and 5.6 (Peter Zaitsev)
Tags: , ,
 Comment 
Life

Gone Fishing

└ Posted: April 1st, 2011 by Derek Downey
Picture of a bass fish

A hooked fish

I sat in the boat with a friend from my college years. We slowly approached a quiet cove and began preparing our rods. The two of us baited our hooks and prepared to make our first casts of the day. One second our bobbers touched the water, the next there was a sharp pull on our lines. A moment later, we looked at our catches: two large bass!

And then I woke up. I remembered that I am not a fisher. Sure, I’ve gone bass fishing and have dabbled briefly in fly-fishing. But in general, that is not one of my hobbies.

I lay there for a few minutes more, thinking about the dream. Why was I suddenly dreaming about fishing? From the few times I have been fishing, I do know that rarely do you make such a great catch two minutes after setting up shop. It is just not how it works, in my limited experience.

This thought made me realize the importance of the dream. It wasn’t about fishing at all, but a lesson to apply to a new project we have started at work. It is not going to “just happen”. It is going to take some time, some hard work.

Have a dream, and then set your energy to accomplishing the dream.

Tags: , ,
 Comment 
Databases

Understanding MySQL Binary Collations

└ Posted: March 16th, 2011 by Derek Downey

Today I am going to explore MySQL’s Binary Collations. About a week ago, I wrote a post about an issue I was having with trailing whitespace not showing up as a different value in a GROUP BY query as I expected.

As a brief recap, this is because the MySQL character set I was using was PADSPACE, which means that all trailing whitespace is ignored in comparisons. The solution was to use the BINARY operator.

This issue led me to do more investigation into MySQL character sets and collations. I wanted to see if there were a way to make trailing spaces be significant by changing the column’s character definition, instead of using the BINARY operator. Reading through the MySQL manual on the BINARY operator did not encourage me:

BINARY (operator) also causes trailing spaces to be significant….The BINARY attribute in character column definitions has a different effect.

Ok, so defining the column as BINARY does not make trailing spaces significant. Instead, it changes the collation of the column to the _bin collation of the character set. As an example, if your column is defined as character set ‘utf8′, then defining the column ‘char(10) BINARY’ changes the collation to to ‘utf8_bin’. So what does that mean?

All nonbinary strings have a character set and collation. Examples of character sets are ‘utf8′ and ‘latin1′. They define the type of characters allowed in the column. Each character set has multiple collations associated only with that character set (utf8_general, utf8_bin). These collations define the rules for comparing and sorting the characters in the parent set.

The _bin collation will order the column based on the value of the string on the binary code values of the characters. These characters can be multi-byte (as in the case of utf8), and in this respect makes the _bin collation different from binary strings stored in BINARY, VARBINARY and BLOB column types.

‘_bin’ collations will still allow the lettercase to be modified (for example, calling LOWER() on a _bin collation will change ‘AA’ to ‘aa’) but ordering the values will ignore the lettercase. This ability to modify the lettercase of a _bin collation differs from a binary string, which does not allow lettercase conversion.

Unfortunately, ‘_bin’ collations are still considered nonbinary strings, making this rule apply:

Nonbinary strings have PADSPACE behavior for all collations, including _bin collations

Trailing spaces are still insignificant in comparisons: ‘a ‘ = ‘a’.

So while I was not able to avoid using the BINARY operator to make trailing spaces significant by changing the column definition, I did learn that _bin collation will allow me to sort the values of a column in a case-insensitive manner, while still maintaining lettercase significance for comparisons (‘a’ is not equal to ‘A’).

For more reading on the _bin collation and the difference from binary strings, there is some good information on the MySQL manual page.

Tags: , ,
2 Comments