「MySQL」の編集履歴（バックアップ）一覧に戻る
MySQL - (2008/01/23 (水) 11:57:56) のソース
----
#contents
----
~
* インストールやりなおし
-文字化けが直らずソースからインストールこすることに。
+ダウンロード
--MySQL Community Serverをダウンロード(mysql-5.0.33.tar.gz)
--tar.gz形式ソース版
---http://dev.mysql.com/downloads/
+グループ追加
> 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/を指定。
-&color(#cccccc){グループmysqlの追加};
# pw groupadd mysql
-&color(#cccccc){ユーザ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だった。
# 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
|-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"
+ユーザテーブルのリフレッシュ
flush privileges;
しなくてもなったけど。。。
** バックアップ
*** mysqldump(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'
-- 参考： http://www.rollamonkey.com/blog/?p=20
- やってみたら動いた
% 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
で接続
* Link
-[[smart++>http://www.rfs.jp/]]
-[[MySQLクイック・リファレンス>http://www.bitscope.co.jp/tep/MySQL/quickMySQL.html]]
-[[MySQLについて 四>http://www.daito.ac.jp/~ikeuchi/webdb/mysql_4.html]]
-[[ALTER TABLE(テーブル構成の変更)>http://programnet.hp.infoseek.co.jp/database/alter.html]]
