mysql5.6にアップグレードしたらユーザー追加できなくなった

mysql> CREATE USER 'hoge'@'localhost' IDENTIFIED BY 'hogepasswd';
ERROR 1054 (42S22): Unknown column 'plugin' in 'mysql.user'

↓これで解決

#mysql_upgrade -u root -p

MySQLでランダム文字列生成

これは便利。

too many connectionsでサーバーダウン

デフォルトの設定は死亡フラグだそうです。

http://chain.main.jp/fm/archives/2006/01/bug_diary_02.html

デフォルトだとMySQLの設定は、
・同時接続数は、100
・待機時間は、8時間

ひとまず今回は、
#vi my.cnf
[mysqld]
max_connections = 512
thread_cache=150
wait_time=60

で設定してみました。


↓参考サイトです。
http://open-groove.net/mysql/seek-mysql-memory/

thread_cache
MySQLは接続終了後のサーバスレッドをいきなり解放せず、次の接続時に再利用
できるようにキャッシュしておける。このため、切断後の再接続時のオーバーヘッド
をより軽減することができるのだ。

mysqlTIPS

□テーブルと、ストレージエンジンの一覧
mysql>use information_schema;
mysql>select table_name, engine from tables where table_schema = DB_NAME;

なかなか面白いです。
InnoDBの主キーはクラスターインデックスだということを意識しよう


更新があるシステムにはInnoDBを選ぼう。MyISAMを選択するならそれなりの理由が必要。それにInnoDBのパフォーマンスはそんなに悪くないよ。

データベースの移行

みんまちのサーバーを移行中

アクセス伸びてきてます。
訪問者が300ほど、ページビューが1000ほど。
なのにロードアベレージが2を超えるっていう緊急事態。
作り方や、mysqlの設定が悪いのかもしれない。
それも見直すつもり。

さて、データベースの移行を行います。

エクスポート
mysqldump -u ユーザー名 -p DB名 > バックアップファイル名
インポート
mysql -u ユーザー名 -p 新DB名 < バックアップファイル名

でOK

指定したテーブルのみ移行する場合は
エクスポート
mysqldump -u ユーザー名 -p DB名 テーブル名1 テーブル名2 > バックアップファイル名
※-tオプションをつけるとinsertのみになる

インポートの場合は普通と同じ
インポート
mysql -u ユーザー名 -p 新DB名 < バックアップファイル名
以上

テーブルが存在するなら削除、しないなら何もしないSQL文

DROP TABLE IF EXISTS tbl_name;
ね。
一時テーブルだと
DROP TEMPORARY TABLE IF EXISTS tbl_name;

参考
http://dev.mysql.com/doc//refman/4.1/ja/drop-table.html

MySQLのチューニング

MySQLを高速化したいときのチューニング方法

を参考に
1、my.cnf を編集
vi /etc/my.cnf
[mysql]
long_query_time=3
log-slow-queries=/var/log/slow.log


サーバを再起動
/etc/init.d/mysqld restart

確認
show variables like 'log%';


http://d.hatena.ne.jp/MATSU/20110415/1302851350

MySQLの最適化をする際に必要になるかもしれない知識

今はどうか知らないけれども

基本としては、
1、インデックスの見直し
2、テーブルの最適化(変な設計になっていないかなど)
3、データベースサーバーのグレードアップ

だと思う。

インデックスの最適化には↓かな
http://dev.mysql.com/doc/refman/5.1/ja/mysql-indexes.html

落ちだしたら見直そう。

mysql のバージョンを調べる

バージョン調べるコマンド
mysqladmin -u ユーザー名 -p version

リスタート
/etc/init.d/mysqld restart

mysqlってスペース検索できるんだ。。

http://www.tatamilab.jp/rnd/archives/000389.html#a

http://www.tatamilab.jp/rnd/archives/000390.html


知らなかった〜

自分のブログよく参照するから。
いつかブログのデザインを変更する。



導入メモ

CSVでエクスポート

http://c-brains.jp/blog/wsg/10/06/16-101352.php

mysql> select * from テーブル名 into outfile "/tmp/書き出しファイル名" fields terminated by ',' enclosed by '"' lines terminated by '\r\n';

で出来た。
注意:mysqlの権限があるフォルダに書き出すこと！

mysql 現在の接続数の確認

http://thinkit.co.jp/free/article/0707/2/2/
/usr/bin/mysqladmin -u root extended-status | egrep '(Max|Threads)'

mysqlでクエリログとコネクションログをとる方法

1.
#locate my.cnf
でmysqlの設定ファイルを探す

vi my.cnf
で編集
[mysqld]
datadir=*****
socket=****
#ここにログファイルの絶対パスで設定
log=/var/log/mydbname.log

[mysql]
default-character-set=utf8
[mysqldump]
default-character-set=utf8

3.ファイルを作ってユーザーをmysql、権限を640にする
touch /var/log/mydbname.log
chown mysql:mysql /var/log/mydbname.log
chmod 640 var/log/mydbname.log

4.リスタート
/etc/init.d/mysqld restart

でログが書き込める。

CSVからインポート

毎度毎度忘れる上に必要になる場合が多いのでメモ。


mysqlimport

を使ったらエラーが出た。
権限がないとか、何とか。

Access denied for user 'aaaa'@'localhost' (using password: YES)

以下よりroot権限でmysqlに入る
http://dev.mysql.com/doc/refman/4.1/ja/default-privileges.html

mysql> mysql -u root DB名
mysql> grant file on *.* to 'aaaa'@'localhost';

で権限作れたはず。


実行してみる
mysql> mysqlimport -u aaaa -p aaaa CSVファイルのフルパス(サーバー上の)
と、エラー
Can't get stat of 'CSVファイルのフルパス' (Errcode: 13), when using table:　****

何でかな〜
http://www.jun2065.net/index.php?mysql%B4%D8%CF%A2%2Fimport%A4%CB%A4%C4%A4%A4%A4%C6
ファイルがローカルにあるってのをつけなきゃいけないらしい。

mysql> mysqlimport -u aaaa -p aaaa -local CSVファイルのフルパス(サーバー上の)

でやっと完成。

mysqlのデータを書き出し(mysqldump )

mysqldump -u ユーザ名 DB名 -p --default-character-set=binary --tables テーブル名 > 20080722_HOGE.sql

http://d.hatena.ne.jp/ichikaway/20080722/1216696390

SNSが通常装備の時代だと思う。

joinしてupdate

http://www.abe-tatsuya.com/web_prog/mysql/update_inner_join.php

update table1
inner join table2 on table1.id = table2.id
set table1.cellname1 = hoge
where table2.cellname2 = 'huga';


■サブクエリからjoinしてupdate
更新対象のテーブルをjoinしてupdateは出来ないらしい。
http://hamamuratakuo.blog61.fc2.com/blog-entry-427.html
そりゃぁそうだよなぁ。


一時テーブルに入れといて、は出来ました。
ちなみに一時テーブルの作り方は
create TEMPORARY table テーブル名
select ....なんちゃら
で作れます。

max_allowed_packet(でかいデータ)

今日は膨大な郵便番号データ(13万件程度)をMYSQLにINSERTします。

mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+

で、エラーが出た。
データ大きすぎるよ。って

vi my.cnf
を編集
[mysqld]
max_allowed_packet=16MB
の記載を追加してmysqlを再起動

/etc/init.d/mysqld restart


そうするとエラーが出なくなる。

ついでに愚痴ると、mysqlworkbench、バグる。
私が想定外の使い方してるのは承知してる。

MySQLのストアドを使いたい

バッチ処理は
DBixで云々するより、ストアドにしたい。

むかーし、使っていた記憶がある。
便利なんだよなぁ・・

って事で参考サイト。

http://blog.livedoor.jp/sasata299/archives/51180619.html


あと、結合してUPDATE
UPDATE table1
INNER JOIN table2 ON　table1.id = table2.id
SET table1.name1 = table2.name1
WHERE table2.cellname2 = 'huga';

SQLいじるの楽しい。
結構数学的。

use base

use base 'CGI';
CGIクラスの継承。

our @ISA = 'CGI';
という書き方もできる。
@ISAは、packageが属する(継承する)クラスを指定するために使用する。

ISAは
A horse is a critter
の略

複雑なSQL IFとsum

select
sum(IF(wage < '1500' ,'1','0') ) as '1500円以下',
sum(IF(wage > '1499' ,'1','0') )as '1500円以上' ,
sum(IF(wage > '1999' ,'1','0') )as '2000円以上' ,
sum(IF(wage > '2499' ,'1','0') )as '2500円以上' ,
sum(IF(wage > '2999' ,'1','0') )as '3000円以上' ,
sum(IF(wage > '3499' ,'1','0') )as '3500円以上'
from zikyuu

SQL面白い。

CASE構文も使える。

しかしこれをDBIxにどう書くか。

DBIxではCASE構文使えないらしい。
IFでむりくり書こうと思えばかける。

my $row = $schema->resultset('zikyuu')->find(
undef,
{
select => [
{ sum => \'IF(zikyuu.wage < 1500 , 1 , 0 ) ' , -as => 'cun1' },
{ sum => \'IF(zikyuu.wage > 1499 , 1 , 0 ) ' , -as => 'cun2' },
{ sum => \'IF(zikyuu.wage > 1999 , 1 , 0 ) ' , -as => 'cun3' },
{ sum => \'IF(zikyuu.wage > 2499 , 1 , 0 ) ' , -as => 'cun4' },
{ sum => \'IF(zikyuu.wage > 2999 , 1 , 0 ) ' , -as => 'cun5' },
{ sum => \'IF(zikyuu.wage > 3499 , 1 , 0 ) ' , -as => 'cun6' },
],
as => [qw/ cun1 cun2 cun3 cun4 cun5 cun6 /],
join => { 'zikyuu'},
order_by => 'zikyuu.wage DESC',
}
);

my $data;
my %cols = $row->get_columns;
while (my($key,$value) = each(%cols)){
$data->{$key} = $value;
}


これでできた(*^^)v

mysqlのデータ型(tityint)

tityintの扱い方がちょっと不明だったから。
http://plaza.rakuten.co.jp/hknopage/diary/200903240000/


http://www.dbonline.jp/mysql/type/index1.html


http://dev.mysql.com/doc/refman/4.1/ja/numeric-types.html

SQL Tips

■データ構造の表示
SHOW COLUMNS FROM `テーブル名`;

■MySQLのvarchar(XX)の××は文字数
http://d.hatena.ne.jp/sotarok/20070821/1187707045

PerlでMySQL関係メモ

DBIx::Class コンポーネントについて
http://rt.air-nifty.com/pods/DBIx/Class/Manual/Component.html

MySQL Workbench が便利です
http://blog.toor.jp/2010/05/24/mysql_workbench_great_software/