ねもぷらす

ふぁいんでぃんぐねもの日記。プログラミングとか育児とか

MacPorts で MySQL を導入

DB使おうと思ったら、OS再インストールしたあと何も入れていないの忘れてた。
導入するかを迷ったあげく、面倒なのでパッケージ管理ソフトを使うことに。


先ずはインストールしたいアプリが存在するか検索。

$ port search mysql
gauche-dbd-mysql               databases/gauche-dbd-mysql 0.2.1        Gauche database driver for MySQL
mysql3                         databases/mysql3 3.23.58      Multithreaded SQL database server
mysql4                         databases/mysql4 4.1.22       Multithreaded SQL database server
mysql5                         databases/mysql5 5.0.67       Multithreaded SQL database server
mysql5-devel                   databases/mysql5-devel 5.1.28-rc    Multithreaded SQL database server
mysqltcl                       databases/mysqltcl 3.02         Simple API for Mysql-Database and Tcl scripting language.
mysqlxx                        databases/mysqlxx 3.0.2        C++ wrapper for MySQL's C API
mysql-connector-java           java/mysql-connector-java 5.0.7        MySQL Official JDBC connector for MySQL database.
p5-class-dbi-mysql             perl/p5-class-dbi-mysql 1.00         Extensions to Class::DBI for MySQL
p5-dbd-mysql                   perl/p5-dbd-mysql 4.008        Perl5 Database Interface to the MySQL database
py-mysql                       python/py-mysql 1.2.2        Python interface to mysql
py25-mysql                     python/py25-mysql 1.2.2        Python interface to mysql
rb-dbd-mysql                   ruby/rb-dbd-mysql 0.0.23       Ruby DBI driver for Mysql. OBSOLETE: please use dbd_mysql variant of rb-dbi
rb-mysql                       ruby/rb-mysql  2.7.5        Ruby API for the Mysql database

なんかたくさん出てきた。
mysql5-devel か mysql5 あたりで良いかな?そんなにガツガツ使う予定もないので mysql5 にしぼってみる。

$ port search mysql5
mysql5                         databases/mysql5 5.0.67       Multithreaded SQL database server
mysql5-devel                   databases/mysql5-devel 5.1.28-rc    Multithreaded SQL database server

せっかくなので依存関係も確認。

$ port deps mysql5
mysql5 has library dependencies on:
	zlib
	openssl
$ port installed | grep ' zlib\| openssl'
  openssl @0.9.8g_0 (active)
  zlib @1.2.3_1 (active)


おけ。
次はMacPortsの真骨頂、インストールオプションは何があるんだコマンド。

$ port variants mysql5
mysql5 has the variants:
	universal
	server

サーバを入れれば良いのかな?
インストールオプションは variants の前に+を着ければ良いっぽい。

$ sudo port install mysql5 +server
Password:
--->  Fetching mysql5
--->  Attempting to fetch mysql-5.0.67.tar.gz from http://distfiles.macports.org/mysql5
--->  Verifying checksum(s) for mysql5
--->  Extracting mysql5
--->  Applying patches to mysql5
--->  Configuring mysql5
--->  Building mysql5 with target all
--->  Staging mysql5 into destroot
--->  Creating launchd control script
###########################################################
# A startup item has been generated that will aid in
# starting mysql5 with launchd. It is disabled
# by default. Execute the following command to start it,
# and to cause it to launch at startup:
#
# sudo launchctl load -w /Library/LaunchDaemons/org.macports.mysql5.plist
###########################################################
--->  Installing mysql5 5.0.67_1+server
******************************************************
* In order to setup the database, you might want to run
* sudo -u mysql mysql_install_db5
* if this is a new install
******************************************************
--->  Activating mysql5 5.0.67_1+server
--->  Cleaning mysql5


最後に「sudo -u mysql mysql_install_db5 を実効しろ」と出るので従う。

$ sudo -u mysql mysql_install_db5
Password:
Installing MySQL system tables...
090115 21:26:50 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
090115 21:26:50 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
090115 21:26:50 [Warning] Setting lower_case_table_names=2 because file system for /opt/local/var/db/mysql5/ is case insensitive
OK
Filling help tables...
090115 21:26:51 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
090115 21:26:51 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
090115 21:26:51 [Warning] Setting lower_case_table_names=2 because file system for /opt/local/var/db/mysql5/ is case insensitive
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/opt/local/lib/mysql5/bin/mysqladmin -u root password 'new-password'
/opt/local/lib/mysql5/bin/mysqladmin -u root -h ****.local password 'new-password'

Alternatively you can run:
/opt/local/lib/mysql5/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /opt/local ; /opt/local/lib/mysql5/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl

Please report any problems with the /opt/local/lib/mysql5/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com

初期化完了。
デーモンを起動したければ↓を実行しろと言っておられるので実行してみる。
cd /opt/local ; /opt/local/lib/mysql5/bin/mysqld_safe &

の前に、my.cnf 調整しておくか。どこに入ったんだろ?

$ find /opt -name my*.cnf
/opt/local/share/mysql5/mysql/my-huge.cnf
/opt/local/share/mysql5/mysql/my-innodb-heavy-4G.cnf
/opt/local/share/mysql5/mysql/my-large.cnf
/opt/local/share/mysql5/mysql/my-medium.cnf
/opt/local/share/mysql5/mysql/my-small.cnf
/opt/local/var/macports/software/mysql5/5.0.67_1+server/opt/local/share/mysql5/mysql/my-huge.cnf
/opt/local/var/macports/software/mysql5/5.0.67_1+server/opt/local/share/mysql5/mysql/my-innodb-heavy-4G.cnf
/opt/local/var/macports/software/mysql5/5.0.67_1+server/opt/local/share/mysql5/mysql/my-large.cnf
/opt/local/var/macports/software/mysql5/5.0.67_1+server/opt/local/share/mysql5/mysql/my-medium.cnf
/opt/local/var/macports/software/mysql5/5.0.67_1+server/opt/local/share/mysql5/mysql/my-small.cnf
/opt/local/var/macports/sources/rsync.macports.org/release/ports/databases/mysql4/files/my.cnf

/opt/local/share/mysql5/mysql/my-small.cnf
コレで良いか。
で、/opt/local/etc/mysql5/ 下に配置すれば良い??
いちおう文字コードだけutf-8にしておく。*1

$ sudo cp /opt/local/share/mysql5/mysql/my-small.cnf /opt/local/etc/mysql5/my.cnf
$ sudo vi /opt/local/etc/mysql5/my.cnf
...
[mysqld]
...
default-character-set = utf8

あとデフォルトだとデータはどこに作られるんじゃーとか思ったら、/opt/local/var/db/mysql5/ でした。
さあ起動だ。

$ cd /opt/local ; /opt/local/lib/mysql5/bin/mysqld_safe &
[1] 89307
touch: /opt/local/var/db/mysql5/****.local.err: Permission denied
chown: /opt/local/var/db/mysql5/****.local.err: No such file or directory
Starting mysqld daemon with databases from /opt/local/var/db/mysql5
/opt/local/lib/mysql5/bin/mysqld_safe: line 380: /opt/local/var/db/mysql5/****.local.err: Permission denied
/opt/local/lib/mysql5/bin/mysqld_safe: line 388: /opt/local/var/db/mysql5/****.local.err: Permission denied
STOPPING server from pid file /opt/local/var/db/mysql5/****.local.pid
tee: /opt/local/var/db/mysql5/****.local.err: Permission denied
090115 22:01:57  mysqld ended
tee: /opt/local/var/db/mysql5/****.local.err: Permission denied

[1]+  Exit 1                  /opt/local/lib/mysql5/bin/mysqld_safe

ですよねー。実行する前に sudo しなきゃいけないの思い出したけど面倒なので実行。
言われるがママにやってちゃダメダメという教訓です。ちなみに2コマンド続けて書かれているので、デーモンを起動する方に sudo してあげないといけない罠も兼ねそろえてます。

$ cd /opt/local ; sudo /opt/local/lib/mysql5/bin/mysqld_safe &
[1] 89361
Starting mysqld daemon with databases from /opt/local/var/db/mysql5

$ mysql5 -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.67 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> exit
Bye

やっとー、動いた。
奥様共用マシンなので重くなると問い合わせが発生するので必要の無いときは止めておくやさしい旦那。
いつもプロセス kill してたけど、ちゃんと止めよう。mysqladmin5 を使えば良いんですね

$ ps ax | grep mysql
89361 s000  S      0:00.04 /bin/sh /opt/local/lib/mysql5/bin/mysqld_safe
...
$ mysqladmin5 -u root shutdown
STOPPING server from pid file /opt/local/var/db/mysql5/****.local.pid
090115 22:14:16  mysqld ended

[1]+  Done                    sudo /opt/local/lib/mysql5/bin/mysqld_safe
$ ps ax | grep mysql
89412 s000  R+     0:00.00 grep mysql
$ mysql5 -uroot
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/opt/local/var/run/mysql5/mysqld.sock' (2)

そういえば止めるときは一般ユーザでもOKなのか。

$ cd /opt/local ; sudo /opt/local/lib/mysql5/bin/mysqld_safe &
Starting mysqld daemon with databases from /opt/local/var/db/mysql5

$ ls -la /opt/local/var/db/mysql5/
total 40976
drwxr-xr-x  10 _mysql  _mysql       340  1 15 22:22 .
drwxr-xr-x   3 root    admin        102  1 14 22:44 ..
-rw-r--r--   2 root    _mysql         0  1 14 22:44 .turd_mysql5
-rw-rw----   1 _mysql  _mysql   5242880  1 15 22:22 ib_logfile0
-rw-rw----   1 _mysql  _mysql   5242880  1 15 22:05 ib_logfile1
-rw-rw----   1 _mysql  _mysql  10485760  1 15 22:22 ibdata1
drwx------  53 _mysql  _mysql      1802  1 15 21:26 mysql
drwx------   2 _mysql  _mysql        68  1 15 21:26 test
-rw-rw----   1 _mysql  _mysql      3473  1 15 22:22 ****.local.err
-rw-rw----   1 _mysql  _mysql         6  1 15 22:22 ****.local.pid

さて、使う時デーモン起動するのがめんどいな…

*1:クライアントも指定しなきゃだったっけ?思い出したら修正