~
インストールやりなおし
- 文字化けが直らずソースからインストールこすることに。
- ダウンロード
- MySQL Community Serverをダウンロード(mysql-5.0.33.tar.gz)
- tar.gz形式ソース版
- グループ追加
> pw groupadd mysql
- ユーザ追加
> adduser -g mysql -d /usr/local/var mysql
- ファイル解凍
> tar xvzf mysql-5.0.33.tar.gz
- configure
> cd mysql-5.0.33 > ./configure --with-charset=sjis -with-extra-charsets=all --with-mysqld-user=mysql
- インストール
> make > make install
- DB初期化
> /usr/local/bin/scripts/mysql_install_db --user=mysql > chown -R mysql /usr/local/var > chgrp -R mysql /usr/local/var
インストール(昔)
- sysinstallで入れた。
MySQLはrootでも起動でるが、昨今のネットワーク事情を考えると、専用ユーザーを使用した方がより安全。そこでグループとユーザーを追加する。ユーザーホームはMySQLのデータディレクトリ/var/db/mysql/を指定。
- グループmysqlの追加;
# pw groupadd mysql
- ユーザmysqlの追加;
# adduser -g mysql -d /var/db/mysql/
↑二つはしなくても設定済み?
- データベースを初期化、その後データディレクトリのオーナーを先ほど作成したmysqlユーザーに修正
# /usr/local/bin/mysql_install_db --user=mysql # chown -R mysql /var/db/mysql/ # chgrp -R mysql /var/db/mysql/
起動
# /usr/local/bin/mysqld_safe --user=mysql & # /usr/local/share/mysql/mysql.server start --default-character-set=utf8
- 停止
# /usr/local/share/mysql/mysql.server stop
- ソケットを介さない方法
# mysqladmin --host=133.XX.XX.XX shutdown
- データベーステーブルの確認
# /usr/local/bin/mysqlshow
データベースへ接続できないとき
- 病状
- パスワードを確実に入力しているものの接続を拒否される
$ mysql ERROR 1045 (28000): Access denied for user ‘momota’@'localhost’ (using password: NO) $ mysql -p Enter password: ERROR 1045 (28000): Access denied for user ‘momota’@'localhost’ (using password: YES) $ mysql -u root ERROR 1045 (28000): Access denied for user ‘root’@'localhost’ (using password: NO) $ mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user ‘root’@'localhost’ (using password: YES)
- 原因の考察
- ポートが開いていない
- 昔の設定が残っているからなんかおかしい(今回はこれっぽい感じだった)
- パスワードを本当に忘れた。
- 解決法
- mysqlデーモンを再起動
- skip-grant-tablesオプションを指定するとユーザー確認用のデータを読み込まずに起動
$ /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
設定し直す
- パスワード設定
mysql> UPDATE mysql.user SET Password=PASSWORD('YOUR-PASSWORD') WHERE User='root'; mysql> FLUSH PRIVILEGES;
- 確認
mysql> SELECT Host,User,Password FROM mysql.user;
設定
- rootユーザのパスワード設定
# mysqladmin -u root password 'new_password' # mysqladmin reload
mysqladmin を使ってパスワードを設定する場合、設定後にmysqladmin の reload コマンドを実行する必要があるらしい。
だが、拒否されてしまったので以下のようにしたらOKだった。
だが、拒否されてしまったので以下のようにしたらOKだった。
# mysqladmin reload -u root -p Enter password:さっき設定したパスワード
cnfファイルをコピー
- 自分の環境にあったものをコピー
# cp /usr/local/share/mysql/my-***.cnf /var/db/mysql/my.cnf
実は/etc/の下に置くべき?
- 使用する文字セットを設定
以下、my.cnfファイルの中身。日本語化。
[client] ''default-character-set=utf8'' #password = your_password port = 3306 socket = /tmp/mysql.sock [mysqld] ''default-character-set=utf8'' ''character-set-server=utf8'' ''language=japanese'' port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16K max_allowed_packet = 1M table_cache = 4 sort_buffer_size = 64K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 64K server-id = 1 [mysqldump] ''default-character-set=utf8'' quick max_allowed_packet = 16M [mysql] ''default-character-set=utf8'' no-auto-rehash [isamchk] key_buffer = 8M sort_buffer_size = 8M [myisamchk] key_buffer = 8M sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout
サーバへ接続
# mysql [-h host_name] [-u user_name] [-pyour_pass]
オプション-pのあとにパスワードを入力できるが-pだけをうったら次の行にEnter password: と表示されるからそれから入力したほうが安全。
デフォルト
オプション | デフォルト値 |
---|---|
-h | localhost |
-u | UNIXのログイン名 |
設定ファイルにパスワードを書いておくこともできる。そうすることにより、毎回毎回コマンドラインに引数を与えなくてすむようになる。まず、自分のホームディレクトリに .my.cnf を作り、そのファイルの中の [client] セクションに接続用のパラメターを記述する。
[client] host=host_name user=user_name password=your_pass
.my.cnf ファイルはグループやその他のユーザーが読み書きできないように。ファイルのパーミッションは 400 か 600 が最適。
ブランクユーザの削除
mysql> delete from user where user=''; mysql> select * from user;
DBの管理
新規ユーザ権限の設定
Servlet用のDBとそれを扱うユーザを設定する
- まずMySQLにルートでログイン
- servlet用のDBを作る
create database servletDB;
- ユーザの追加
grant all on servletDB.* to ユーザ名@'%' identified by 'パスワード';
ユーザは全てのホストでservletDBを使用可能。
@以下を書き換えればドメインによる制限が可能。%は’*’みたいなもん。
例:@"%.domain.co.jp"
@以下を書き換えればドメインによる制限が可能。%は’*’みたいなもん。
例:@"%.domain.co.jp"
- ユーザテーブルのリフレッシュ
flush privileges;
しなくてもなったけど。。。
バックアップ
mysqldump(SQLベースのバックアップ)
- 存在するデータをすべてSQLにしてテキスト形式に保存
$ mysqldump -u root -x --all-databases > dump.sql
- 特定のデータベースのみのバックアップ
$ mysqldump -u root データベース名 > dump.sql
- mysqldumpのすべてのデータベースのバックアップを復元
$ mysql -u root -p < dump.sql
- 特定データベースのみの復元
$ mysql -u root データベース名 < dump.sql
コマンド・SQL
テーブル名の変更
> ALTER TABLE 元のテーブル名 RENAME TO 新しいテーブル名;
設定環境情報を見る
> status;
環境変数を見れる
> show variables;
テーブル情報の表示
> desc テーブル名;
ファイルからのデータ挿入
LOAD DATA INFILE '/home/momota/5to20a_ID_name.txt' INTO TABLE aid2aname FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
AUTO_INCREMENTをリセット
ALTER TABLE table_name AUTO_INCREMENT=max_id_number
重複レコードの抽出
SELECT * FROM テーブルA WHERE 列A IN (SELECT 列A FROM テーブルA GROUP BY 列A HAVING COUNT(*) > 1);
インデックス関連
インデックスを貼る
CREATE INDEX インデックス名 ON テーブル名 (フィールド名[, フィールド名, ...]); ALTER TABLE テーブル名 ADD INDEX インデックス名(フィールド名);
インデックスの確認
SHOW INDEX FROM テーブル名;
インデックスの使用を確認する
EXPLAIN SQL文
インデックスの削除
DROP INDEX インデックス名 ON テーブル名;
SQLの結果行数を取得
- LIMIT句で取得レコードが限定された場合でも全体のレコード数を取得
SELECT SQL_CALC_FOUND_ROWS * FROM テーブル名 LIMIT 0,1; SELECT FOUND_ROWS();
と実行すると、直前クエリのLIMIT句で制限されないレコード数が取得できる。
- WHERE句を使った場合もOK
> SELECT SQL_CALC_FOUND_ROWS * FROM bookmark WHERE url_id = 1; +--------+---------+-------+------------+ | url_id | user_id | tid | date | +--------+---------+-------+------------+ | 1 | 135 | 1365 | 2007-04-18 | | 1 | 135 | 17365 | 2007-04-18 | | 1 | 1238 | 1365 | 2006-01-14 | | 1 | 1238 | 426 | 2006-01-14 | | 1 | 1550 | 1365 | 2005-10-02 | | 1 | 2211 | 23083 | 2005-10-03 | | 1 | 3933 | 426 | 2006-01-12 | | 1 | 3933 | 1365 | 2006-01-12 | | 1 | 4613 | 1365 | 2007-04-26 | | 1 | 4613 | 1073 | 2007-04-26 | | 1 | 4613 | 882 | 2007-04-26 | | 1 | 12546 | 1365 | 2005-10-02 | | 1 | 12546 | 12637 | 2005-10-02 | | 1 | 15932 | 4776 | 2006-11-22 | +--------+---------+-------+------------+ 14 rows in set (0.04 sec) > SELECT FOUND_ROWS(); +--------------+ | FOUND_ROWS() | +--------------+ | 14 | +--------------+ 1 row in set (0.00 sec)
テーブルのコピー
1
- CREATE TABLE
- カラム作成
- 以下のSQL
INSERT INTO `to_db`.`to_table` SELECT * FROM `from_db`.`from_table`;
2
CREATE TABLE to AS SELECT * FROM from_tb;
- WHERE句で条件付きのコピーも可能
テーブルの全データ削除
TRUNCATE [TABLE] hogetb;
- deleteよりも高速に削除可能
テーブル構成の変更
カラムの追加
> ALTER TABLE テーブル名 ADD カラム名 型;
カラムの削除
> ALTER TABLE テーブル名 DROP カラム名;
カラムのデータ型の変更
> ALTER TABLE テーブル名 MODIFY カラム名 新しいデータ型;
カラム名とデータ型の変更
> ALTER TABLE CHANGE 古いカラム 新しいカラム 新しいデータ型;
テーブル名の変更
> ALTER TABLE 古いテーブル名 RENAME AS 新しいテーブル名;
server再起動したらRubyからDBアクセスができなくなった件について
- error
/usr/local/lib/ruby/site_ruby/1.8/dbi.rb:329:in `load_driver': Unable to load driver 'Mysql' (DBI::InterfaceError) from /usr/local/lib/ruby/site_ruby/1.8/dbi.rb:227:in `_get_full_driver' from /usr/local/lib/ruby/site_ruby/1.8/dbi.rb:213:in `connect'
- やってみたら動いた
% sudo gem install mysql -- --with-mysql-dir=/usr/local/var % cd /usr/local/lib/ruby/gems/1.8/gems/mysql-2.7/ % sudo ruby extconf.rb install mysql -- --with-mysql-dir=/usr/local/var % make % make install clean
文字化け
DB@FreeBSDが扱う文字列をUTF-8に統一
[client] port = 3306 socket = /tmp/mysql.sock [mysqld] ''default-character-set = utf8'' init-connect = SET NAMES utf8 port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M thread_concurrency = 8 log-bin server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] ''default-character-set = utf8'' no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
データ挿入
- ファイルからの場合はUTF-8に変換してから挿入する
- もしUnix上で
select * from hoge;
で文字化けして表示されてもJDBC経由でデータ取ってきたら化けてないので御注意を。
JDBC
jdbc:mysql://ホスト名/データベース名?useUnicode=true&characterEncoding=UTF8
で接続