タグ: MySQL

カラム名の違うテーブルにデータをコピーする

同じ構造のテーブルにデータをコピーするときはここにも書いてある通り、

INSERT INTO new_table SELECT * FROM original_table;

で大丈夫だけど、
コピーしようと思ったらカラム名が微妙に違う!なんていうのも無くはない。

その時はちょっとめんどくさいけどカラム名を指定してあげることでコピーできる。

INSERT INTO new_table (id, name, division) SELECT serial, name, section FROM original_table

ということは、
コピー元にはないカラムも値を指定してあげれば、いける。

INSERT INTO new_table (id, name, division, sex) SELECT serial, name, section, 'male' FROM original_table

こんなカンジ?
指定しなくてもカラムのデフォルト値が入る。

テーブル内の文字列を一括TRIMする

UPDATE `table_name` SET `column_name` = TRIM ([LEADING or TRAILING or BOTH] ' ' FROM `column_name`);

文字列の先頭にある半角スペースを取り除くなら[LEADING]、
最後にある半角スペースを取り除くなら[TRAILING]、
前後両方なら[BOTH]で。

それ以前に半角スペースを取り除きたいだけなら

UPDATE `table_name` SET `column_name` = TRIM (`column_name`);

のように何も指定しなければ

UPDATE `table_name` SET `column_name` = TRIM (BOTH ' ' FROM `column_name`);

と同様のことをやってくれます。

前後を問わず、かつ途中にあるものも全て!
とか言うのであれば、「テーブル内の文字列を一括変換する」のREPLACEの方が楽かな…

スロークエリを見つける

mysql> show global variables;
 long_query_time        | 10.000000
 slow_query_log         | OFF
 slow_query_log_file    | /home/mysql_data/localhost-slow.log

この辺を確認する。
[ long_query_time ]の設定が10秒だとなかなか引っかかるものは少ないです。
むしろこの状態でも引っかかるのは相当重い処理ということに……
なので、これは1秒に変更。
大量に引っかかるようなら、2秒3秒に後で増やせばいいので。

mysql> set global long_query_time=1;

では実際にログ取得を開始する。

mysql> set global slow_query_log='on';

これで[ slow_query_log_file ]で指定されたファイルに
処理時間が[ long_query_time ]を超えるクエリが書き込まれます。

# Time: 151105 16:22:44
# User@Host: root[root] @  [192.168.100.200]  Id: 123456789
# Query_time: 2.960907  Lock_time: 0.000287 Rows_sent: 3750  Rows_examined: 21077
SET timestamp=1446708164;
SELECT `hogehoge`.`moe` AS `moe`, `hogehoge`.`moemoe` AS `moemoe`........

こんなカンジで。
このクエリは3秒近く時間がかかっててヤバイね。

mysql-bin の自動削除

mysql-bin を過去分から自動的に消してくれるありがたい設定があるみたい。

グローバルに「expire_logs_days」という変数があるのでこれを確認。

show global variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.00 sec)

ということで、デフォルトは「0」。削除しない設定になっています。
とりあえず1年ぐらいは残したいので、366日で設定してみる。

set global expire_logs_days = 366;                                      
Query OK, 0 rows affected, 1 warning (0.00 sec)

ふむ。
出来たかな?

show global variables like 'expire_logs_days';                          
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 99    |
+------------------+-------+
1 row in set (0.00 sec)

おい!
最大値99かよ!
最大値は「99」みたいです。(MySQLリファレンスマニュアル)

99日で自動的に消されちゃうとむしろ困るので元に戻しました……

mysql-bin の削除…切り離し

久々にDBサーバの中を覗いてみる。

show master logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000001 | 1073758177 |
| mysql-bin.000002 | 1074413659 |
| mysql-bin.000003 |  599684970 |
| mysql-bin.000004 |   86882358 |
| mysql-bin.000005 | 1074358779 |
	・
	・
	・

わぁいっぱい。。。
約2年前からこの辺の整備はしてこなかったってことみたい。

一応、内規で最低1年分は取っておかないといけないらしいので、それより前の部分を切り離すことにする。

どのログがいつまでのものなのかはこのコマンドではわからない?
ので、一旦抜けて、普通にログファイルの市まで行って確かめる。
000022
までがそうみたいね。

なので、

purge master logs to 'mysql-bin.000022';                                
Query OK, 0 rows affected (0.25 sec)

で、

show master logs;                                                       
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000022 | 1073746806 |
| mysql-bin.000023 |  993562002 |
| mysql-bin.000024 |  871890321 |
| mysql-bin.000025 |   12771312 |
+------------------+------------+
15 rows in set (0.00 sec)

うむ、おっけー。

Daily Backup を取っておく

MySQLのデータを日毎にバックアップとっておくことになった。
sqldumpで書き出しておけば、自分じゃなくても戻せるだろう……という理由のもとに、
書き出し方法はシェルとcrontabでいいんじゃないかなぁと妄想しつつ、書いてみる。

#!/bin/sh

date_digit=`date +%Y%m%d`
week_ago_digit=`date -d '-7 days' +%Y%m%d`
for db_name in `mysql -u root -p'00000000' -N -s -e"show databases;"`; do
    mysqldump -u root -p'00000000' --single-transaction ${db_name} > /home/mysql_daily_backup/${date
_digit}_${db_name}.sql
    scp -Cqpi /root/.ssh/id_rsa /home/mysql_daily_backup/${date_digit}_${db_name}.sql root@111.222.333.444:/var/www/html/mysql_daily_backup
    cd /home/mysql_daily_backup
    if [ -e ${week_ago_digit}_${db_name}.sql ]; then
        rm -rf /home/mysql_daily_backup/${week_ago_digit}_${db_name}.sql
    fi
done;

これでいいのかな?
動いてるからいいかな?

あ、事前に秘密鍵公開鍵の設定は済ませておくこと。

MySQL はじめのユーザ設定

インストールしたてのMySQLのrootにはパスワードが設定されてないんだか、あるんだか。
とりあえず、mysqladminでパスワードを設定する。

# /usr/bin/mysqladmin -u root password 'ほげほげ'
Warning: Using a password on the command line interface can be insecure.

コマンドラインで設定するのは丸見えだからセキュアじゃないとかなんとか。
仕方がないじゃん。
で、入ってみる。

# mysql -u root -p -h 192.168.1.2
Enter password: 
ERROR 1130 (HY000): Host '192.168.1.2' is not allowed to connect to this MySQL server

ああ、さっきのmysqladminで設定したのはあくまで「root@localhost」に対してってことなのね。
では気を取り直して

# mysql -u root -p

で入る。

で入ってから

mysql> create user root@192.168.1.2 identified by 'ほげほげ';
Query OK, 0 rows affected (0.00 sec)

作っただけだとなにも権限がないので、

mysql> grant all privileges on *.* to root@192.168.1.2 identified by 'ほげほげ' with grant option;
Query OK, 0 rows affected (0.00 sec)

これでいつもどおり。

explicit_defaults_for_timestamp という WARNING

久々にMySQLをインストールしてみたら、起動時に

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

なんてWARNINGが出てきた。
なんのことやら?と思ったら、
「TIMESTAMP データ型は非標準的な方式である」という部分が
「MySQL 5.6.6 以降では非推奨」ということになったらしい。

詳細は「2.11.1.3. MySQL 5.5 から 5.6 へのアップグレード」の下の方に。

いままでは、なんの指定もしなくてもTIMESTAMPカラムには
NOT NULL, DEFAULT CURRENT_TIMESTAMP, ON UPDATE CURRENT_TIMESTAMP 属性が自動的に付けられてた。

これからは、このへんの属性を自動で付けるけど、「非推奨WARNING」をしつこく出すことにする!

ということらしい┐( -“-)┌…

これを出したくなければ、
「explicit_defaults_for_timestamp」を「ON」にしましょう!
その代わり、
これから作るTIMESTAMPは「明示的に NOT NULL 指定」をしない限り「NOT NULL」じゃなくなる。
よってTIMESTAMPについて何も指定していなかったSQLで新しいテーブルに書き込むと現在時刻は自動的に入らず、「NULL」が入ります!
ってことみたい、よ。

とりあえず、このWARNINGを消すには「ON」にするしかないねぇ。

書き方からして、今後のアップデートで「DEFAULT = ON」にしたいのは明白っぽいもんねぇ。
SQL書く側が気をつけるしかないねぇ。

すでに文字列が入っているカラムに文字を追加する

文字列の結合はCONCATでできるわけで。

UPDATE `table_name` 
SET `already_column` = CONCAT(`already_column`, '追加したい文字列')
WHERE `hogehoge` = 'mogemoge';

こんなかんじで出来る。
`already_column`がNULLだと、返り値は「追加したい文字列」ではなく「NULL」になってしまうらしい。
そういう時は

UPDATE `table_name` 
SET `already_column` = CONCAT(IFNULL(`already_column`,''), '追加したい文字列')
WHERE `hogehoge` = 'mogemoge';

こうかな。

テーブル内の文字列を一括変換する

UPDATE文でテーブル内に存在する文字列を一括変換する。

UPDATE `table_name` 
SET column_name = REPLACE (column_name, "moto_string", "replace_string");

一部分のみを指定したいならWHERE句を使えばいいので

UPDATE `hogehoge` 
SET `column_name` = REPLACE (`column_name`, "moto_string", "replace_string")
WHERE (`area` = '4' OR `area` = '5')
  AND (`ward` = '121' OR `ward` = '122' OR `ward` = '131' OR `ward` = '132')
  AND `string` LIKE 'mogumogu%';

こんなカンジ。

MySQL のストレージエンジンを確認&変更

通常は InnoDB エンジンで作っているけど、
他の人が作ったもので MyISAM が使われちゃってる物があったりする。
これの確認方法。

show table status from `DB名`;

これで左から2つ目のカラムに「Engine」というカラムがあるので、ココを確認する。

MyISAM エンジンを使用しているテーブルを見つけちゃったら

alter table `テーブル名` engine=InnoDB;

で変更していく。

MySQL データディレクトリの移動

yumでインストールされたままのmy.cnfは
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
と、なっているのでこれを変更したい。

まずは
/etc/init.d/mysqld stop
で、データベースを止めておく。

止めたら
nice -n 19 rsync -auvz –delete /var/lib/mysql/ /mysql_data_dir
で、データを移動したいディレクトリにコピー。

併せてmy.cnfも移動後のディレクトリを見るように書き換えておく。
datadir=/mysql_data_dir
socket=/mysql_data_dir/mysql.sock

あとはコピー完了後に
/etc/init.d/mysqld start
で起動するだけ。

もし、起動に失敗するようだったらSELINUXの設定を確認する。
getenforce
の結果が
enforcing
だったら
/etc/selinux/config
で指定を[permissive]もしくは[disabled]に書き換える。