MySQLをインストール/設定

MySQLをインストール

2年ぐらい前までは『mysql5 +server』だったが、今は『mysql5-server』。『mysql5 +server』するように書いてある資料はかなり古いので、他の部分も信用できない。

$ sudo port install mysql5-server
Password:
--->  Computing dependencies for mysql5-server
--->  Dependencies to be installed: mysql5
--->  Fetching mysql5
--->  Verifying checksum(s) for mysql5
--->  Extracting mysql5
--->  Applying patches to mysql5
--->  Configuring mysql5
--->  Building mysql5
--->  Staging mysql5 into destroot
--->  Installing mysql5 @5.1.50_1
The MySQL client has been installed.
If you also want a MySQL server, install the mysql5-server port.
--->  Activating mysql5 @5.1.50_1
--->  Cleaning mysql5
--->  Fetching mysql5-server
--->  Verifying checksum(s) for mysql5-server
--->  Extracting mysql5-server
--->  Configuring mysql5-server
--->  Building mysql5-server
--->  Staging mysql5-server into destroot
--->  Creating launchd control script
###########################################################
# A startup item has been generated that will aid in
# starting mysql5-server with launchd. It is disabled
# by default. Execute the following command to start it,
# and to cause it to launch at startup:
#
# sudo port load mysql5-server
###########################################################
--->  Installing mysql5-server @5.1.50_0
******************************************************
* 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-server @5.1.50_0
--->  Cleaning mysql5-server

けっこう時間がかかる。

何かの都合でアンインストールしたいときは、

$ sudo port uninstall mysql5-server
Password:
--->  Deactivating mysql5-server @5.1.50_0
--->  Cleaning mysql5-server
--->  Uninstalling mysql5-server @5.1.50_0
--->  Cleaning mysql5-server
$ sudo port uninstall mysql5
--->  Deactivating mysql5 @5.1.50_1
--->  Cleaning mysql5
--->  Uninstalling mysql5 @5.1.50_1
--->  Cleaning mysql5

MySQLを初期化

最近は『_mysql』らしい。『mysql』と書いてある資料は古いはず。

$ sudo -u _mysql mysql_install_db5
Password:
Installing MySQL system tables...
070903  8:34:23 [Warning] Setting lower_case_table_names=2 because file system for /opt/local/var/db/mysql5/ is case insensitive
OK
Filling help tables...
070903  8:34:28 [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 MacBook5.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 /opt/local/mysql-test ; perl mysql-test-run.pl

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

青字で示した部分が表示されるようならば、それはOSの再インストール時にちゃんと『大文字/小文字を区別する』にしていなかった証拠

MySQLを起動して接続できるか確認

csh系の場合はあらかじめrehashが必要。

$ sudo /opt/local/share/mysql5/mysql/mysql.server start
Password:
Starting MySQL
.. SUCCESS! 
$ mysql5 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.50 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
$

前者のコマンドで『ERROR! Manager of pid-file quit without updating file.』とエラーが出たら、原因は以下の4つが考えられる。

権限の設定が不適切
MySQL の pid ファイルが残っている
MySQL のプロセスが生き残っている
設定ファイルの記述に誤りがある

アンインストールしてインストールしなおしたときなどは、3の可能性が大きい。

root のパスワード設定

$ /opt/local/lib/mysql5/bin/mysqladmin -u root password 'ぱすわーど'

起動時に MySQL サーバーを起動するように設定

$ sudo port load mysql5-server

以前は以下のようなやり方をしていたのだが、どっちがいいのだろうか?

$ sudo launchctl load -w /Library/LaunchDaemons/org.macports.mysql5.plist

5，6秒してから "Workaround Bonjour: Unknown error: 0" っていうエラーがでてくるんですが、これは特に害のあるエラーではなくてただのバグだとおもわれるので心配ないらしい。

Rails用のデータベースとアカウントを作っておく

『Enter password:』のところには、さっきつけたMySQLのrootのパスワードを答える。

connection idってのは15だったり3だったりするのかな??

$ mysql5 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.50 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database cms_development;
Query OK, 1 row affected (0.01 sec) 

mysql> grant all on cms_development.* to rails identified by 'railsのぱすわーど' with grant option;
Query OK, 0 rows affected (0.00 sec) 

mysql> exit
Bye

『railsのぱすわーど』の部分にここでパスワードを決めて書いたものが、『*database.yml』に登録される。

MySQL用のドライヴァーをインストールする

$ sudo port install rb-dbi +dbd_mysql
Password:
--->  Computing dependencies for rb-dbi
--->  Dependencies to be installed: rb-mysql ruby libiconv gperf
--->  Fetching gperf
--->  Attempting to fetch gperf-3.0.4.tar.gz from ftp://ftp.dti.ad.jp/pub/GNU/gperf
--->  Verifying checksum(s) for gperf
--->  Extracting gperf
--->  Configuring gperf
--->  Building gperf
--->  Staging gperf into destroot
--->  Installing gperf @3.0.4_0
--->  Activating gperf @3.0.4_0
--->  Cleaning gperf
--->  Fetching libiconv
--->  Attempting to fetch libiconv-1.13.1.tar.gz from ftp://ftp.dti.ad.jp/pub/GNU/libiconv
--->  Verifying checksum(s) for libiconv
--->  Extracting libiconv
--->  Applying patches to libiconv
--->  Configuring libiconv
--->  Building libiconv
--->  Staging libiconv into destroot
--->  Installing libiconv @1.13.1_0
--->  Activating libiconv @1.13.1_0
--->  Cleaning libiconv
--->  Fetching ruby
--->  Attempting to fetch ruby-1.8.7-p302.tar.bz2 from ftp://ftp.iij.ad.jp/pub/lang/ruby/1.8
--->  Verifying checksum(s) for ruby
--->  Extracting ruby
--->  Applying patches to ruby
--->  Configuring ruby
--->  Building ruby
--->  Staging ruby into destroot
--->  Installing ruby @1.8.7-p302_0+thread_hooks
--->  Activating ruby @1.8.7-p302_0+thread_hooks
--->  Cleaning ruby
--->  Fetching rb-mysql
--->  Attempting to fetch mysql-ruby-2.7.5.tar.gz from http://tmtm.org/downloads/mysql/ruby/
--->  Verifying checksum(s) for rb-mysql
--->  Extracting rb-mysql
--->  Configuring rb-mysql
--->  Building rb-mysql
--->  Staging rb-mysql into destroot
--->  Installing rb-mysql @2.7.5_1
--->  Activating rb-mysql @2.7.5_1
--->  Cleaning rb-mysql
--->  Fetching rb-dbi
--->  Attempting to fetch dbi-0.1.1.tar.gz from http://distfiles.macports.org/ruby
--->  Verifying checksum(s) for rb-dbi
--->  Extracting rb-dbi
--->  Configuring rb-dbi
--->  Building rb-dbi
--->  Staging rb-dbi into destroot
--->  Installing rb-dbi @0.1.1_1+dbd_mysql
--->  Activating rb-dbi @0.1.1_1+dbd_mysql
--->  Cleaning rb-dbi

なんかしらんけどyakがやれっていったので

$ sudo cp /opt/local/var/macports/software/mysql5/5.1.50_1/opt/local/share/mysql5/mysql/my-medium.cnf /opt/local/etc/mysql5/my.cnf
$ sudo vi /opt/local/etc/mysql5/my.cnf

max_allowed_packet = 1Mをmax_allowed_packet = 64Mに変更。mysqldを再起動。