[MySQL]他ホストから接続させる方法

まずはPortの確認。デフォルトの3306。

mysql> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)


あれよこれよといじっても接続できない。
$ mysql -u xxx -p -h 10.X.X.X -P 3006
$ mysql -u xxx -p -h 10.X.X.X
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '10.X.X.X' (111) <-　適宜IP

重要なのがここ！定義ファイル！
sudo vi /etc/mysql/my.cnf
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
bind-address            = 0.0.0.0

MySQL再起動！
$ sudo /etc/init.d/mysql restart


参考：http://atatte55.blog62.fc2.com/blog-entry-204.html

mysqlでヘッダ非表示

スクリプトから実行する時に、枠線やヘッダ （カラム名） 不要で値だけ欲しい時、
例えば、データベース一覧のみが欲しい場合のオプションの付け方のメモ。

$ mysql -u [user] -p[password] -N -s -e "show databases;"
db1
db2
db3

-N は、ヘッダなし
-s は枠線なし
-e はコマンド実行

railsとmysqlのインデックス

インデックスを付与してみよう！ってことで…

migrateファイルにはこんなふうに書いて、 rake:migrate を実行

class AddIndex < ActiveRecord::Migration
  def change
    add_index :users, [:project_id, :login], :unique=>true, :name=>'users_project_login'
  end
end

uniqueをつけると、ユニークインデックス
name はインデックスの名前。指定しないと、
テーブル名_カラム名1_カラム名2_...カラム名N になります。

mysqlで実際どんなふうになているかを見る時のコマンドはこれ。

 show index from index;

ちなみにインデックスを外すときはmysqlでこれね。
migrate/rollbackしてるうちにデータがおかしくなった私(^^;

drop index index_name on tbl_name;

ユニークインデックスとは

「ユニークインデックス」と「ユニークじゃないインデックス（ノンユニークインデックス？）」の違いって？

あれこれ読んだ所、私なりの解釈としてはざっくりと・・・

ユニークインデックス
　　指定したカラムの値は重複しない。（重複したデータ入れることができない）

ノンユニークインデックス
　　重複したデータを入れることが可能。


また、インデックスには、複数のカラムの指定が可能。

これを理解した上で、railsに設定します！

railsでMySQLのデータが文字化け

RailsのActiveRecordからMySQLのデータ取得すると日本語が文字化け…。
MySQLのコンソールから見ると、ちゃんと見れる。
mysqldumpをすると文字化け…

default-character-setの指定が必要云々とあったので、
下記ファイルに

sudo /etc/mysql/my.cnf 

これを1行追加して

default-character-set=utf8

MySQL再起動すればOK！

$ sudo /etc/init.d/mysql restart

RubyからMySQLにアクセス

MySQLのデータベースに直接アクセスしてデータを抜く方法。
これ以外にももちろんあります！

require 'mysql'
db = Mysql.connect("ホスト", "ログイン名", "パスワード", "DB名")
sql = "select login, firstname, lastname from users;"
rs = db.query(sql)

# 件数
puts rs.num_rows

# 結果の表示
rs.each do |row|
  puts row[0] + " " + row[1] + " " + row[2]
end

db.close

selectで書いた　login, firstname, lastnameが、
結果でも0,1,2の順に配列に入っています。

MySQLに他のサーバーから接続したい

MySQLにlocalhost以外の他のサーバーからアクセスするには設定が必要です。
ユーザ(tester/pass)だけ作って、アクセスしようとすると、こんなエラーになります。

$  mysql -u tester -ppass -h 10.20.30.40
ERROR 1045 (28000): Access denied for user 'tester'@'10.20.30.40' (using password: YES)

接続したいサーバーにて、権限を付与します。
いろいろごそごそやってるとおかしなことになるので、一旦クリーンな形にします。

% DELETE FROM mysql.user Where user = 'tester';
% REVOKE ALL PRIVILEGES ON xxx_db.* FROM tester@'%';
% FLUSH PRIVILEGES;

改めて下記を設定。

% GRANT USAGE ON *.* TO tester@'%' IDENTIFIED BY 'pass';
% GRANT ALL PRIVILEGES ON xxx_db.* to 'tester'@'%';
% SELECT USER,HOST FROM mysql.user;

権限の確認

% SHOW GRANTS for tester;
GRANT USAGE ON *.* TO 'tester'@'%' IDENTIFIED BY PASSWORD '*945E2AF30B5AA2DC36C9B6363D9B1D52A3E5CD1B'
GRANT ALL PRIVILEGES ON `xxx_db`.* TO 'tester'@'%'

上記まで行ったら、つながりました！

$ mysql -u tester -ppass -h 10.20.30.40
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.1.37-1ubuntu5 (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

これでも繋がらない?!という場合は、iptableやufwを疑ってみるのもありかもしれません。
私はufwで引っかかってました…。

MySQLでカラム型を知りたい

焦ってる時にいきなり思い出せないカラム型を見るコマンド。
それはDESC または DESCRIBE。

Redmine1.1のusersテーブルをのぞいてみました。

mysql> desc users;

MySQLのSHOWコマンド

MySQLのSHOWコマンドでいろいろな情報が見れます。
私自身よく使うのがこちらです。
他にもまだまだたくさんありますが、メモとしてこのくらいに。。

・show databases;
データベース一覧を表示

・show tables;
テーブル一覧を表示

・show columns from [テーブル名];
指定したテーブルのカラム情報を表示

・show index from [テーブル名];
指定したテーブルのインデックス情報を表示

・show variables [like キー];
MySQLのシステム変数を表示します。

例）show variables like "%max%";

MySQLの文字コード設定

mysqlコマンドでMySQLのデータベースに接続する際に設定と異なるキャラクターセットでログインする場合は
　--default-character-set={文字コード}
のオプションを利用することができます。

mysql --default-character-set=utf8 -u root -pXXXXX 

毎回つけるのが面倒な場合は、my.cnfの設定で変更が可能です。

% sudo vi /etc/mysql/my.cnf

[client]
port        = 3306
socket        = /var/run/mysqld/mysqld.sock
default-character-set = utf8   # ★追加

[mysqld]
#
# * Basic Settings
#
default-character-set = utf8   # ★追加

[mysql]
#no-auto-rehash    # faster start of mysql but no tab completition
default-character-set = utf8   # ★追加
　

設定されたかどうかを確認するには下記コマンドを用います。

SHOW VARIABLES LIKE 'char%';

MySQLに用意されている文字コードはこちらのコマンドで確認できます。

show character set;

MySQLインストール後に、特に何も変更していなければ
デフォルトでは latin1 が設定されます。

<注意>
上記はMySQL5.1の場合の設定ですが、
MySQL5.5以降では、[mysqld]セクションのdefault-character-setがなくなり、
character-set-serverに変わったそうです。


MySQLのバージョンチェックするにはこちら

select version();

+-----------------+
| version()       |
+-----------------+
| 5.1.37-1ubuntu5 | 
+-----------------+
1 row in set (0.02 sec)

MySQL processlist

mysql> show processlist;
+--------+------+--------------------+-------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+--------------------+-------------+---------+------+-------+------------------+
| 379844 | xxx | localhost | NULL | Query | 0 | NULL | show processlist |
| 383550 | xxx | 172.16.1.151:40526 | redmine_xxx | Sleep | 7 | | NULL |
| 383551 | xxx | 172.16.1.151:40527 | redmine_xxx | Sleep | 7 | | NULL |
| 383552 | xxx | 172.16.1.151:40528 | redmine_xxx | Sleep | 7 | | NULL |
| 383553 | xxx | 172.16.1.151:40529 | redmine_xxx | Sleep | 7 | | NULL |
| 383554 | xxx | 172.16.1.151:40530 | redmine_xxx | Sleep | 6 | | NULL |
| 383555 | xxx | 172.16.1.151:40531 | redmine_xxx | Sleep | 6 | | NULL |
| 383556 | xxx | 172.16.1.151:40532 | redmine_xxx | Sleep | 6 | | NULL |
+--------+------+--------------------+-------------+---------+------+-------+------------------+