Selectively dumping data with mysqldump

mysqldump is a command line tool for outputting table structures and data and can be used for backups etc. By default mysqldump will dump all data from a table, but it is possible to select which data to be exported with mysqldump. This post looks at how to do this.

The examples in this post have a table called “mytable” in a database called “test”. mytable has three columns: mytable_id, category_id and name, and we will be selectively exporting data that matches a specific category_id.

Using mysqldump to dump all data from the table would look like this, subsituting [username] for your username (the -t flag suppresses the table creation sql from the dump):

mysqldump -t -u [username] -p test mytable

The output from my example table looks like this, once we remove all the extra SQL commands (I’ve added linebreaks to make it more legible):

INSERT INTO `mytable` VALUES 
  (1,1,'Lorem ipsum dolor sit amet'),
  (2,1,'Ut purus est'),
  (3,2,'Leo sed condimentum semper'),
  (4,2,'Donec velit neque'),
  (5,3,'Maecenas ullamcorper');

If we only wanted to dump data from mytable in category_id 1, we would do this:

mysqldump -t -u [username] -p test mytable --where=category_id=1

which would output this:

INSERT INTO `mytable` VALUES 
  (1,1,'Lorem ipsum dolor sit amet'),
  (2,1,'Ut purus est');

You can also abbreviate –where as -w like so:

mysqldump -t -u [username] -p test mytable -wcategory_id=1

If you need to have spaces in the where query or other special shell characters (such as > and <) then you need to put quotes around the where clause like so:

mysqldump -t -u [username] -p test mytable --where="category_id = 1"
OR
mysqldump -t -u [username] -p test mytable -w"category_id = 1"

You can also use the –where flag to selectively dump data from more than one table, but obviously the columns specified in the where clause need to be in both tables.

An example of dumping data from two tables using the same where clause could look like this, where we are selecting category_id from tables “mytable” and “anothertable”:

mysqldump -t -u [username] -p test mytable anothertable --where="category_id = 1"

If category_id exists in both tables then the dump will run without error. If the column doesn’t exist, you’ll see an error like this:

mysqldump: mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `anothertable` WHERE category_id=1': Unknown column 'category_id' in 'where clause' (1054)
mysqldump: Got error: 1054: Unknown column 'category_id' in 'where clause' when retrieving data from server

mysqldump is an excellent tool for exporting data from MySQL databases. Using the –where or -w flags allows you to selectively export data from one or more tables which saves you having to export all data from a table if you only need a specific subset.

Long query log

いつの間にか会社で古株になったyamaokaです。

webアプリケーションのバックエンドにMySQLを使っている場合、
クエリ(SQL)のチューニングをする必要がありますよね。
皆さんはチューニングの計画をどのように立てていますか。

もちろん、既に明らかに重いことが想定されているページがあれば、
その処理で使われているクエリを中心にEXPLAINなどを使って解析していけばいいと思います。

でもそうではなく、全体的にクエリの見直しやチューニングを行いたい場合は
実際に実行されているクエリを確認していくという作業が必要です。
そこで使うことができる3つの方法について書きたいと思います。

遅いクエリを記録する

MySQLにはスロークエリログといって、
実行に時間がかかったクエリを記録する機能が最初から付いています。
/etc/my.cnfに次のように設定を書けば実行時間が1秒を超えたクエリが出力されるようになります。

slow_query_log = 1
slow_query_log_file = /path/to/mysql-slow.log
long_query_time = 1

オンラインでset globalを使って変更する場合は次のようにします。

set global slow_query_log = 1;
set global slow_query_log_file = '/path/to/mysql-slow.log';
set global long_query_time = 1;

出力されたログファイルをMySQLに付属しているmysqldumpslowというツールで解析すると便利です。次のように実行すれば、平均の実行時間が長い順にソートして表示してくれます。

mysqldumpslow -s at /path/to/mysql-slow.log

クエリのパラメータは数値はN、文字列はSに置換して表示してくれるので
同じクエリをまとめてチェックすることができます。

実行回数の多いクエリを記録する

実行時間は短いけれど多くの回数実行されるクエリというのもあります。
これらは通常のスローログには出てこないのですが、
実は負荷の大部分を占めている、ということもありえます。
キャッシュなど別の方法を考えることで
アプリケーションの負荷を減らすことができるかもしれません。

従来MySQLではlong_query_timeに1秒以上の値しか設定できませんでしたが、
MySQL 5.1から1秒未満の値も設定できるようになりました。
つまり、次のように/etc/my.cnfで「0」を設定すれば
全てのクエリが記録できることになります。

slow_query_log = 1
slow_query_log_file = /path/to/mysql-slow.log
long_query_time = 0

もちろん、全てのクエリの記録は負荷も大きくかかることを
理解してから設定を行うようにしてください。
また、ログの容量も大きくなるので、ディスクの空き容量にも注意が必要です。
オンラインでset globalを使って一時的に値を0に変更し、すぐに元の値(1など)に戻すのがオススメです。

set global slow_query_log = 1;
set global slow_query_log_file = '/path/to/mysql-slow.log';
set global long_query_time = 0;
# 後で set global long_query_time = 1; で元に戻す

出力されたログファイルをmysqldumpslowで次のように解析することで、
実行回数の多い順に表示することが可能です。

mysqldumpslow -s c /path/to/mysql-slow.log

mysqldumpslowは他にもいろいろ機能を持っているので、「–help」を付けて実行して
一度オプションを確認してみるといいかもしれません。

追記: MySQLでは/etc/my.cnfに次のような設定をすることで全てのクエリを記録することが可能です。
最初から全ての記録を保存したい場合はこちらの方法もいいかもしれません。

log = /path/to/mysql-query.log

インデックスを使っていないクエリを記録する

多くの場合、インデックスを使用しないクエリは遅いです。
インデックスの設計は計画的に行う必要があると思いますが、
今現在インデックスを使用していないクエリはどれなのか知りたい場合があると思います。
次のように/etc/my.cnfに記述することでインデックスを使用していないクエリを
スロークエリログに記録することができるようになります。

slow_query_log = 1
slow_query_log_file = /path/to/mysql-slow.log
long_query_time = 5
log_queries_not_using_indexes = 1

オンラインでset globalを使って設定する場合は次のようにします。

set global slow_query_log = 1;
set global slow_query_log_file = '/path/to/mysql-slow.log';
set global long_query_time = 5;
set global log_queries_not_using_indexes = 1;

出力されたログファイルは今までと同じように
mysqldumpslowを使って解析していくことになると思います。

終わりに

最近は、開発するときにフレームワークに付属のORマッパーを使ったりして
実際に発行されるクエリを意識しないことが多くなっていると思います。
もちろんそれはメリットだと思うのですが、実際に実行されるのはクエリ(SQL)である以上、
完全に意識しないで済むということはありません。

実際に発行されているクエリを眺めつつ、
少しでもパフォーマンスのよいwebアプリケーションを作っていけたらいいと思います。

追記: オプションの変数名がMySQL 5.1基準のものになっていなかったのでMySQL 5.1をベースに修正しました。

Memo

[mysqld]
long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log

You must create the file manually and change owners this way:

mkdir /var/log/mysql
touch /var/log/mysql/log-slow-queries.log
chown mysql.mysql -R /var/log/mysql

Virtual Hosts for MAMP

Virtual Hosts for MAMP

Running a Web server on your desktop computer is a great way to speed up Web site development. By previewing your Web pages directly through a Web server on your own computer you can test out server-side programming like server-side include files, form processing scripts, or database-driven Web pages. However, there’s one problem associated with running a server on your own computer: by default, you only have a single “domain”–http://localhost/–so if you work on more than one Web site you put them all under “localhost” in different directories. This isn’t very real world and can cause problems when using server-side includes and root-relative links. Fortunately there’s a better way.

For information on setting up MAMP, visit the MAMP instructions page.

Virtual Hosts

Virtual Hosts give you the ability to “host” more than one Web site and domain on your computer. With a virtual host you can have separate local domain names for each of your Web sites: for example, http://clientA/ for one site and http://clientB/ for another. When you type the URL for the Virtual Host in your Web browser, the browser doesn’t go out onto the internet to find the site, but instead asks for the proper file from the Web server running on your computer. Not only does a Virtual Host let you run multiple Web sites on your computer, but it also lets you store the files for those sites anywhere on your computer and not just in the C:\XAMPP\htdocs folder.

Note: You must have administrator privileges on your Mac to do this, so if you’re trying to setup Virtual Hosts on a computer at school, or some other computer that you’re not the admin for, you can’t do this without the help of an administrator.

Adding a Virtual Host is a 2-step process:

  1. Add a new entry to your computer’s hosts file.

    A hosts file can be used to point requests for a domain to a particular IP address–in other words, it lets you re-direct communications to a particular domain. In the case of a virtual host, it can tell the computer to NOT go out on the internet when you type a particular URL like http://clientA/, but instead look for that particular domain on your own computer.

  2. Edit the Apache configuration file to accept Virtual Hosts and define the particular Virtual Hosts you want to setup on your computer. The first step above, merely redirects requests from a particular domain to your computer, but this step prepares the Web server on your computer for handling those requests. In this step, you not only provide the name of the Virtual Host, but also tell Apache where the files for the site are located on your computer.

Detailed Steps

  1. Launch Terminal (it’s in the Utilities folder in the Applications folder.

    You’ll be editing a file on your system.

  2. Type: sudo cp /etc/hosts /etc/hosts.bak

    You need admin privileges to work with this file, so you’ll get a prompt for your password. This step is a safety precaution: it makes a back up of the hosts file so that if you make a horrible mistake in the next few steps you can always revert back to the old file by typing:
    sudo cp /etc/hosts.bak /etc/hosts

  3. Type: sudo pico /etc/hosts

    This opens the hosts file in a text editing program named pico.

  4. Press the down arrow key (or press Ctrl-V) until the cursor appears at the bottom of the file.
  5. Type:
    127.0.0.1    clientA.local

    127.0.0.1 is how a computer refers to itself—it’s an IP address that points back to the computer, kind of like a computer’s way of saying “ME.” The second part (clientA.local) is the “domain” of the virtual host. To visit this domain in a Web browser you’d type http://clientA.local. Of course, you should change clientA.local in the above example to match whatever you want the localhost to be. In addition, you don’t have to add the .local part to the hosts files—you could just as easily add 127.0.0.1 clientA and access the site in your Web browser with http://clientA—but I find it helpful for differentiating between a real Web site out on the Internet like clientA.com, and the test sites I have running on my own computer.

  6. Press Ctrl-O, followed by the Return key (this saves the file), followed by Ctrl-X (this closes the file and the pico program.)

    That finishes the first part of this task. You’ve prepared your computer to handle requests to http://clientA.local. Now you need to tell the Web server, Apache, how to handle those requests. You can quit terminal now, you can complete the rest of the tasks with a text editor like TextEdit or even Dreamweaver.

  7. In TextEdit (or any text editor even Dreamweaver) open the Apache configuration file located at Applications:MAMP:conf:apache:httpd.conf

    Note: Make a backup of the httpd.conf file before you edit it. Just in case you make a mistake and Apache won’t start up again.

  8. At the bottom of that file add:
    NameVirtualHost *
    <VirtualHost *>
    DocumentRoot "/Applications/MAMP/htdocs"
    ServerName localhost
    </VirtualHost>
    <VirtualHost *>
    DocumentRoot "/Users/YOU/sites/clientA/site"
    ServerName clientA.local
    </VirtualHost>
      

    The first five lines of code turn on the Virtual Host feature on Apache, and set up the Applications:MAMP:htdocs folder as the default location for http://localhost. That’s important since you need to be able to access the MAMP web pages at http://localhost/ so that you can use PHPMyAdmin.

    The stuff in yellow represents a single Virtual Host. You’ll add one chunk of code just like this for each Virtual Host (or Web site) on your computer

    Note: Make sure you set the ports to the default Apache and MySQL ports as described on the MAMP instructions page.

    You’ll need to modify the stuff highlighted in blue. The first item — DocumentRoot — indicates where the files for this site are located on your computer. The second part–ServerName — is the name you provided in step 2 above: the virtual host name. For example, clientA.local. The third item — the <Directory> part — is the same path you provided for the DocumentRoot. This is required to let your Web browser have clearance to access these files.

  9. Save and close the Apache configuration file, and restart Apache from the MAPP control panel.
  10. Start a Web browser and type a URL for the virtual host. For example: http://clientA.local/.

    You should now see the home page for your site.

More Virtual Hosts

If you want to add additional Virtual hosts add the proper entry to the hosts file and add another block of text like that in yellow above to the Apache configuration file. For example, say you had another Web site for ClientB. You’d add 127.0.0.1    clientB.local in the hosts file and theApplications:MAMP:conf:apache:httpd.conf would look like this:

NameVirtualHost *
<VirtualHost *>
DocumentRoot "/Applications/MAMP/htdocs"
ServerName localhost
</VirtualHost>
<VirtualHost *>
DocumentRoot "/Users/YOU/sites/clientA/site"
ServerName clientA.local
</VirtualHost>
<VirtualHost *>
DocumentRoot "/Users/YOU/sites/clientB/site"
ServerName clientB.local
</VirtualHost>
  

More Information

This site also has instructions for installing MAMP.

 

Following through the examples in the “Agile Web Application Development with Yii 1.1 and PHP5” book

I just started reading the Agile Web Application Development with Yii 1.1 and PHP5 book. Chapter 3 explains the virtues of TDD (test-driven development) and tries to walk us through setting up a development environment that supports automated testing through PHPUnit and Selenium.

At the same time, right at the beginning of the chapter 2 there’s this note:

“There are several versions of Yii from which to choose when downloading the framework. We will be using version 1.1.2 for the purposes of this book, which is the latest stable version as of the time of writing. Though most of the sample code should work with any 1.1.x version of Yii, there may be some subtle differences if you are using a different version. Please use 1.1.2 if you are following along with the examples.”

This clearly makes sense – it’s better to use the same version of the tools, as the author, if we’re expecting to see the same results as shown in the book. The realty is that by the time some readers, like myself, get to read a book some of the software tools, and their dependencies, have evolved to the point where what they produce does not even remotely match what the version that the author used produced.

So I followed the advice, got Yii version 1.1.2, and I tried to follow along. It wasn’t a smooth ride. I’ll try to walk you through some of the pitfalls that I experienced, and the ways I overcame them, hopefully helping you if you’re experiencing similar problems.

First problem appeared when I tried:

sudo pear install phpunit/PHPUnit 

shown on page 45

I got an error telling me that the version of pear I’ve been using was too old (had been using a MAMP setup common on Macs, which may have grown old, at version 1.*). Even though I don’t have the exact message, based on my googling history it said something along these lines: “requires PEAR Installer (version >= 1.9.4)”.

The solution for that was to upgrade pear, like so:

sudo pear upgrade pear

and do a
pear –version 
to check that the version went up.

However, if we follow what the book says, literally, when it tells us to install  phpunit/PHPUnit, like so:

sudo pear install phpunit/PHPUnit

we’ll get the latest version of PHPUnit, which, of course, doesn’t match the older version of the Yii framework, which we were advised to install. This opens a whole can of worms.

First off, when you try to run the functional test cases, by doing:
phpunit functional/SiteTest.php 
you’ll be informed that:

PHP Warning:  require_once(PHPUnit/Extensions/SeleniumTestCase.php): failed to open stream: No such file or directory

If you check the location of PHPUnit install, which on my system is at /Applications/MAMP/bin/php5/lib/php/PHPUnit, you’ll see that indeed that file is missing. How so ? Turns out that most recent versions of PHPUnit and Selenium now get that file installed by doing:

sudo pear install phpunit/phpunit_selenium

but don’t do that, because you’d just be wasting your time ! The thing is, you don’t want to use the most recent PHPUnit, because if you do, as you would be if you followed the instructions in the book, you’ll next hit this error:

Warning: require_once(PHPUnit/Framework.php): failed to open stream: No such file or directory in …/framework/test/CTestCase.php on line 11

Yeah, Yii version 1.1.2 requires PHPUnit/Framework.php, which is not present in the most recent versions of the PHPUnit.

So, “yes it is” – a complete mess, that is. To save your sanity, uninstall the newer versions of the tools:

sudo pear uninstall phpunit/PHPUnit_selenium (if you installed it already)
sudo pear uninstall phpunit/PHPUnit

Then install the version that matches the one used in the book:

sudo pear install –alldeps phpunit/PHPUnit-3.3.17

This should bring you to clear sailing through chapter 3.

Nrpe install

sudo su

cd /home/ec2-user/

mkdir downloads

cd downloads

wget http://sourceforge.net/projects/nagios/files/nrpe-2.x/nrpe-2.13/nrpe-2.13.tar.gz/download?use_mirror=jaist

tar -zxvpf nrpe-2.13.tar.gz

 

groupadd nagios

useradd -d /usr/local/nagios -g nagios nagios

 

wget http://sourceforge.net/projects/nagiosplug/files/nagiosplug/1.4.16/nagios-plugins-1.4.16.tar.gz/download?use_mirror=jaist

 

tar -zxvpf nagios-plugins-1.4.16.tar.gz

 

yum install openssl-devel

 

cd nagios-plugins-1.4.16

 

./configure

 

make

make install

 

cd ../nrpe-2.13

 

./configure –help

 

./configure –enable-command-args

make all

 

cd /usr/local/nagios/

mkdir etc bin

 

cp downloads/nrpe-2.13/src/nrpe /usr/local/nagios/bin/

 

cp downloads/nrpe-2.13/sample-config/nrpe.cfg /usr/local/nagios/etc/

cp downloads/nrpe-2.13/init-script.debian /etc/init.d/nrpe

 

cd /usr/local/src/

wget -c http://cloudman.rumahweb.com/wp-content/uploads/2011/10/dpkg_1.14.31.tar.gz

tar -zxvf dpkg_1.14.31.tar.gz

cd dpkg-1.14.31/

./configure

make

cd utils

make install

export PATH=”/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/opt/aws/bin”

 

 

chmod 777 /etc/init.d/nrpe

vi /usr/local/nagios/etc/nrpe.cfg

/etc/init.d/nrpe start

cd /home/ec2-user/downloads/nrpe-2.13/src

./check_nrpe -H localhost -c check_users

 

00 */3 * * * root /usr/bin/php /home/ec2-user/bash_script/create_ranking.php

00 00 * * * root /usr/bin/php /home/ec2-user/bash_script/renew_user_log.php

 

 

command[check_mysq_disk]=/usr/local/nagios/libexec/check_disk -w 2% -c 1% -p /data