nofx @Wiki

MySQL

最終更新：2008年01月23日 11:57

nofx

- view

だれでも歓迎！編集

インストールやりなおし
- インストール（昔）
起動
データベースへ接続できないとき
- 設定し直す
設定
- cnfファイルをコピー
サーバへ接続
- - デフォルト
- ブランクユーザの削除
DBの管理
- 新規ユーザ権限の設定
  - Servlet用のDBとそれを扱うユーザを設定する
- バックアップ
  - mysqldump(SQLベースのバックアップ)
コマンド・SQL
server再起動したらRubyからDBアクセスができなくなった件について
文字化け
Link

インストールやりなおし

文字化けが直らずソースからインストールこすることに。

ダウンロード

- 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/を指定。

グループ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’@&#039;localhost’ (using password: NO)

$ mysql -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘momota’@&#039;localhost’ (using password: YES)

$ mysql -u root
ERROR 1045 (28000): Access denied for user ‘root’@&#039;localhost’ (using password: NO)

$ mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@&#039;localhost’ (using password: YES)

原因の考察
- ポートが開いていない
- 昔の設定が残っているからなんかおかしい（今回はこれっぽい感じだった）
- パスワードを本当に忘れた。

解決法
- mysqlデーモンを再起動
- skip-grant-tablesオプションを指定するとユーザー確認用のデータを読み込まずに起動

$ /usr/local/mysql/bin/mysqld_safe --skip-grant-tables

設定し直す

パスワード設定

mysql> UPDATE mysql.user SET Password=PASSWORD(&#039;YOUR-PASSWORD&#039;) WHERE User=&#039;root&#039;;
mysql> FLUSH PRIVILEGES;

確認

mysql> SELECT Host,User,Password FROM mysql.user;

設定

rootユーザのパスワード設定

# mysqladmin -u root password &#039;new_password&#039;
# 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]
&#039;&#039;default-character-set=utf8&#039;&#039;
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

[mysqld]
&#039;&#039;default-character-set=utf8&#039;&#039;
&#039;&#039;character-set-server=utf8&#039;&#039;
&#039;&#039;language=japanese&#039;&#039;
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]
&#039;&#039;default-character-set=utf8&#039;&#039;
quick
max_allowed_packet = 16M

[mysql]
&#039;&#039;default-character-set=utf8&#039;&#039;
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=&#039;&#039;;
mysql> select * from user;

DBの管理

新規ユーザ権限の設定

Servlet用のDBとそれを扱うユーザを設定する

まずMySQLにルートでログイン
servlet用のDBを作る

create database servletDB;

ユーザの追加

grant all on servletDB.* to ユーザ名@&#039;%&#039;
identified by &#039;パスワード&#039;;

ユーザは全てのホストで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 &#039;/home/momota/5to20a_ID_name.txt&#039;
INTO TABLE aid2aname
FIELDS TERMINATED BY &#039;,&#039;
LINES TERMINATED BY &#039;\n&#039;;

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]
&#039;&#039;default-character-set	= utf8&#039;&#039;
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]
&#039;&#039;default-character-set = utf8&#039;&#039;
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

タグ：

+ タグ編集

「MySQL」をウィキ内検索

nofx @Wiki

記事メニュー

counter

トータル	-
今日	-
昨日	-

calendar

recent updates

取得中です。

人気記事ランキング

最近更新されたページ

人気Wikiランキング

atwikiでよく見られているWikiのランキングです。新しい情報を発見してみよう！

全体ページランキング

最近アクセスの多かったページランキングです。話題のページを見に行こう！

前月		2024年4月			翌月
日	月	火	水	木	金	土
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30

MySQL

インストールやりなおし

インストール（昔）

起動

データベースへ接続できないとき

設定し直す

設定

cnfファイルをコピー

サーバへ接続

デフォルト

ブランクユーザの削除

DBの管理

新規ユーザ権限の設定

Servlet用のDBとそれを扱うユーザを設定する

バックアップ

mysqldump(SQLベースのバックアップ)

コマンド・SQL

テーブル名の変更

設定環境情報を見る

環境変数を見れる

テーブル情報の表示

ファイルからのデータ挿入

AUTO_INCREMENTをリセット

重複レコードの抽出

インデックス関連

インデックスを貼る

インデックスの確認

インデックスの使用を確認する

インデックスの削除

SQLの結果行数を取得

テーブルのコピー

1

2

テーブルの全データ削除

テーブル構成の変更

カラムの追加

カラムの削除

カラムのデータ型の変更

カラム名とデータ型の変更

テーブル名の変更

server再起動したらRubyからDBアクセスができなくなった件について

文字化け

DB@FreeBSDが扱う文字列をUTF-8に統一

データ挿入

JDBC

Link

counter

calendar

menu

recent updates