■ mysqlクライアントを利用してのDB作成

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| test |
+——————–+
3 rows in set (0.00 sec)

mysql> CREATE DATABASE DB01 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.04 sec)

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| DB01 |
| mysql |
| test |
+——————–+
4 rows in set (0.00 sec)

mysql>

■ mysqladminコマンドを利用してのDB作成

[root@colinux ~]# mysqladmin -u root -ppassword CREATE DB02;

[root@colinux ~]# mysqlshow -u root -ppassword
+——————–+
|     Databases      |
+——————–+
| information_schema |
| DATABASE           |
| DB01               |
| DB02               |
| mysql              |
| test               |
+——————–+
[root@colinux ~]#

DBは作成されているが、文字コードや照合順序は?

mysql> show create database DB02;
+———-+—————————————————————+
| Database | Create Database                                               |
+———-+—————————————————————+
| DB02     | CREATE DATABASE `DB02` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+———-+—————————————————————+
1 row in set (0.00 sec)

※ 文字コードを設定し忘れた場合、ALTER DATABASE DB02 default character set=utf8; で変更可能。

mysql> SHOW COLLATION LIKE ‘utf8_%_ci’;

collation


【MY.CNFでのログの値】

#接続ログ、コマンドなども見ることが出来る。(重くなるので、普段はコメント)
log=/usr/local/mysql/data/servername_com.log
#処理の遅いクエリーを見つける。(ここでは、3秒以上)
long_query_time=3
log_slow_queries=/usr/local/mysql/data/slow_query.log
# indexを利用していないクエリーをslog logの中に記録する。
log_queries_not_using_indexes
# バイナリーコマンドログ。(Replicationには必須: mysqlbinlogコマンドで読める)
log-bin=mysql-bin

※log_queries_not_using_indexes に関しては、Slow_Queriesログの中にログを書き込みます。

【DB側で設定確認】
mysql> show variables like ‘log%’;
+———————————+———————————————-+
| Variable_name | Value |
+———————————+———————————————-+
| log | OFF |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | /usr/local/mysql/data/servername_com.com.err |
| log_output | FILE |
| log_queries_not_using_indexes | ON |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
+———————————+———————————————-+
10 rows in set (0.00 sec)

mysql>

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
※ こんなGLOBALコマンドでも設定可能
set global log_queries_not_using_indexes = ON;
set global log_queries_not_using_indexes = OFF;

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
その他のオプション
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■log_bin_trust_function_creators
http://dev.mysql.com/doc/refman/5.1/ja/stored-procedure-logging.html
■log_bin_trust_routine_creators
http://dev.mysql.com/doc/refman/5.1/ja/news-5-1-8.html
■log_slave_updates
http://dev.mysql.com/doc/refman/5.1/ja/server-system-variables.html
■log_warnings
http://dev.mysql.com/doc/refman/5.1/ja/error-log.html


MYSQLに接続して来ているユーザーの確認。
不要なプロセスは、他のDBと同様にkillコマンドでプロセスIDを指定して停止出来る。

━━━━━━━━━ show processlist ━━━━━━━━━━
mysql> show processlist;
+—-+——+———–+——+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+——+———+——+——-+——————+
| 14 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+—-+——+———–+——+———+——+——-+——————+
1 row in set (0.01 sec)

mysql> show full processlist;
+—-+——+———–+——+———+——+——-+———————–
+
| Id | User | Host | db | Command | Time | State | Info
|
+—-+——+———–+——+———+——+——-+———————–
+
| 14 | root | localhost | NULL | Query | 0 | NULL | show full processlist
|
+—-+——+———–+——+———+——+——-+———————–
+
1 row in set (0.00 sec)

mysql>

━━━━━━━━━ mysqladmin ━━━━━━━━━━

[root@colinux ~]# mysqladmin -u variable -ppassword processlist;
+—-+———-+———–+—-+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+———-+———–+—-+———+——+——-+——————+
| 11 | variable | localhost | | Query | 0 | | show processlist |
+—-+———-+———–+—-+———+——+——-+——————+
[root@colinux ~]# mysqladmin -u variable -ppassword kill 11
[root@colinux ~]#

processlist


mysqlbinlog (バイナリログファイルを処理ユーティリティ)

no-defaults

サーバが生成するバイナリログファイルはバイナリフォーマットで書かれています。
これらのファイルをテキストフォーマットで確認するには、mysqlbinlogユーティリティを使用してください。
それに加えて、複製セットアップ内のスレーブサーバによって書き出されたリレイログファイルを読み取るのに
mysqlbinlogを使用することもできます。リレイログはバイナリログファイルと同じフォーマットです。

[root@localhost data]#mysqlbinlog --no-defaults mysql-bin.000008

http://dev.mysql.com/doc/refman/5.1/ja/mysqlbinlog.html

※–no-defaultsオプションはmy.cnfの中でDefaultの文字コードが設定されてある場合に必要。

[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000008
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#090127 17:15:55 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.25-rc-log created 090127 17:15:55 at startup
ROLLBACK/*!*/;
BINLOG ‘
u8J+SQ8BAA4xLjI1LXJjLWxvZwAAAAAAA
‘/*!*/;
# at 106
#090127 17:16:08 server id 1 end_log_pos 453 Query thread_id=2 exec_time=0 error_code=0
use DBNAME /*!*/;
SET TIMESTAMP=1233044168/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
mysqlbinlogコマンドは特定の時間までリカバリーするときなどにも使われる、
mysqlの管理者は理解しておく必要がある。
※ Oracleのアーカイブログ適用と同じOracleを運用していた時には、
時間指定で戻した(mysqlオプション: –stop-datetime)経験はありませんが
特定のArchiveログの適用(mysqlオプション: –stop-position)する事は
頻繁に発生しておりました。

==========================================================
MYSQLBINLOGのヘルプ抜粋
==========================================================
mysqlbinlog Ver 3.3 for pc-linux-gnu at i686
By Monty and Sasha, for your professional use
This software comes with NO WARRANTY: This is free software,
and you are welcome to modify and redistribute it under the GPL license

Dumps a MySQL binary log in a format usable for viewing or for piping to
the mysql command line client

MYSQLのバイナリーログを読むViewとして利用する事が出来ます。

Usage: mysqlbinlog [options] log-files
-?, –help Display this help and exit.
–base64-output[=name]
Determine when the output statements should be
base64-encoded BINLOG statements: ‘never’ disables it and
works only for binlogs without row-based events; ‘auto’
is the default and prints base64 only when necessary
(i.e., for row-based events and format description
events); ‘always’ prints base64 whenever possible.
‘always’ is for debugging only and should not be used in
a production system. The default is ‘auto’.
–base64-output is a short form for
–base64-output=always.
–character-sets-dir=name
Directory where character sets are.
-d, –database=name List entries for just this database (local log only).

指定されたデータベースのログのみをsqlファイルに出力する。
例)
$ mysqlbinlog --database="TEST" \
/backup/mysql-bin.000019 > /backup/recovery.sql

–debug-check Check memory and open file usage at exit .
–debug-info Print some debug info at exit.
-D, –disable-log-bin
Disable binary log. This is useful, if you enabled
–to-last-log and are sending the output to the same
MySQL server. This way you could avoid an endless loop.
You would also like to use it when restoring after a
crash to avoid duplication of the statements you already
have. NOTE: you will need a SUPER privilege to use this
option.

バイナリログの出力を無効にする。

-F, –force-if-open Force if binlog was not closed properly.
-f, –force-read Force reading unknown binlog events.
-H, –hexdump Augment output with hexadecimal and ASCII event dump.
-h, –host=name Get the binlog from server.
-l, –local-load=name
Prepare local temporary files for LOAD DATA INFILE in the
specified directory.
-o, –offset=# Skip the first N entries.
-p, –password[=name]
Password to connect to remote server.
-P, –port=# Port number to use for connection or 0 for default to, in
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/services, built-in default (3306).
-j, –position=# Deprecated. Use –start-position instead.
–protocol=name The protocol of connection (tcp,socket,pipe,memory).
-R, –read-from-remote-server
Read binary logs from a MySQL server
-r, –result-file=name
Direct output to a given file.
–server-id=# Extract only binlog entries created by the server having
the given id.
–set-charset=name Add ‘SET NAMES character_set’ to the output.
-s, –short-form Just show regular queries: no extra info and no row-based
events. This is for testing only, and should not be used
in production systems. If you want to suppress
base64-output, consider using –base64-output=never
instead.
-S, –socket=name Socket file to use for connection.
–start-datetime=name
Start reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).

バイナリログからの開始日時を指定し、その時点からのログがsqlファイルに出力される。
例)
$ mysqlbinlog --start-datetime="2006-12-13 06:30:00" \
/backup/mysql-bin.000019 > /backup/recovery.sql

–start-position=# Start reading the binlog at position N. Applies to the
first binlog passed on the command line.

指定された位置からログを読み始める。
このオプションはコマンドラインで指定された最初のバイナリログファイルに適用される。
例)
$ mysqlbinlog --start-position=7743 \
/backup/mysql-bin.000017 /backup/mysql-bin.000018 > /backup/recovery.sql

–stop-datetime=name
Stop reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).

バイナリログからの終了日時を指定し、その時点までのログがsqlファイルに出力される。
例)
$ mysqlbinlog --stop-datetime="2006-12-13 08:00:00" \
/backup/mysql-bin.000019 > /backup/recovery.sql

–stop-position=# Stop reading the binlog at position N. Applies to the
last binlog passed on the command line.

指定された位置までログを読み込む。
このオプションはコマンドラインで指定された最後のバイナリログファイルに適用される。
例)
$ mysqlbinlog --stop-position=15324 \
/backup/mysql-bin.000017 /backup/mysql-bin.000018 > /backup/recovery.sql

-t, –to-last-log Requires -R. Will not stop at the end of the requested
binlog but rather continue printing until the end of the
last binlog of the MySQL server. If you send the output
to the same MySQL server, that may lead to an endless
loop.
-u, –user=name Connect to the remote server as username.
-v, –verbose Reconstruct SQL statements out of row events. -v -v adds
comments on column data types
-V, –version Print version and exit.
–open_files_limit=#
Used to reserve file descriptors for usage by this
program

==========================================================


━ Wireshark ━
http://www.wireshark.org/
WiresharkはEtherealとして広く利用されてきた、Packet Captureツールです。
パケットキャプチャーしてネットワーク解析したい時に便利です。
ダウンロードは以下のサイトから
http://www.wireshark.org/download.html

━ STONE ━
http://www.gcd.org/sengoku/stone/
特定のネットワーク接続が分かっていて、詳しく調査したい場合はstoneが便利。
たとえば、アプリケーションの動作確認や接続コマンドを接続先に負荷を
かけずに調査したりする事が出来ます。(Repeaterとして利用)

ダウンロードは以下のサイトから
* stone version 2.3e
http://www.gcd.org/sengoku/stone/stone-2.3e.tar.gz
* stone version 2.3e for WindowsXP
http://www.gcd.org/sengoku/stone/stonexp-2.3e.zip

stone1


statusコマンドで、現在接続しているDBの情報を確認。

mysql>status;
————–
mysql Ver 14.14 Distrib 5.1.30, for pc-linux-gnu (i686) using readline 5.1

Connection id: 1
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server version: 5.1.30 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 10 min 44 sec

Threads: 1 Questions: 12 Slow queries: 0 Opens: 17 Flush tables: 1 Open tab
les: 4 Queries per second avg: 0.18
————–

mysql>

status

status


バッチ処理結果をカスタマイズする。

–html HTMLフォーマット出力 (-H)
–xml XMLフォーマット出力 (-X)
–verbose 詳細表示(-v , -vv, -vvv) (-v)
–table TABLE形式で出力 (-t)
–skip-column-names カラムを表示しない (-N)
–columun-names カラムを表示
–execute=name 指定したクエリーを実行する (-e)
–force エラーを無視して続行 (-f)

[root@colinux ~]# ls
backupfiles mysql–help.txt select_batch1.sql
[root@colinux ~]# mysql test < select_batch1.sql -p --html > select_batch1.html
Enter password:
[root@colinux ~]# cat select_batch1.html

< TD>SHOW WARIN
idnamecomment
1test update
2SHOW WARIN
22222222222
3select_limtest 20090117
4select_limtest 20090117
5s
elect_lim
test 20090117
6select_limte
st 20090117
7select_limtest 20090117
8select_limtest 20090117
9s
elect_lim
test 20090117

[root@colinux ~]#

[root@colinux ~]# mysql test < select_batch1.sql -p --table > select_batch1.txt
Enter password:
[root@colinux ~]# cat select_batch1.txt
+—-+————+—————+
| id | name | comment |
+—-+————+—————+
| 1 | SHOW WARIN | test update |
| 2 | SHOW WARIN | 22222222222 |
| 3 | select_lim | test 20090117 |
| 4 | select_lim | test 20090117 |
| 5 | select_lim | test 20090117 |
| 6 | select_lim | test 20090117 |
| 7 | select_lim | test 20090117 |
| 8 | select_lim | test 20090117 |
| 9 | select_lim | test 20090117 |
+—-+————+—————+
[root@colinux ~]#

[root@colinux ~]# mysql test < select_batch1.sql -p -vvv Enter password: -------------- select * from test.T1 -------------- +----+------------+---------------+ | id | name | comment | +----+------------+---------------+ | 1 | SHOW WARIN | test update | | 2 | SHOW WARIN | 22222222222 | | 3 | select_lim | test 20090117 | | 4 | select_lim | test 20090117 | | 5 | select_lim | test 20090117 | | 6 | select_lim | test 20090117 | | 7 | select_lim | test 20090117 | | 8 | select_lim | test 20090117 | | 9 | select_lim | test 20090117 | +----+------------+---------------+ 9 rows in set (0.00 sec) Bye [root@colinux ~]# [root@colinux ~]# mysql test < select_batch1.sql -p --skip-column-names Enter password: 1 SHOW WARIN test update 2 SHOW WARIN 22222222222 3 select_lim test 20090117 4 select_lim test 20090117 5 select_lim test 20090117 6 select_lim test 20090117 7 select_lim test 20090117 8 select_lim test 20090117 9 select_lim test 20090117 [root@colinux ~]# ■HTMLフォーマット batch_html

■テーブルフォーマット
batch_table_format

■Colums無し (他のバッチ連携などに便利)
batch_no_colum


データが大量にあるテーブルをselectすると、反応が返ってこなくなる
事があります。where句できちんとフィルター出来れば良いけれども
調査の段階ではミスする事もありえるので、こんなオプションを知って
おいても良いかもしれません。

[root@colinux ~]# mysql –help | grep imit
–delimiter=name    Delimiter to be used.
–select_limit=#    Automatic limit for SELECT when using –safe-updates
–max_join_size=#   Automatic limit for rows in a join when using

[root@colinux ~]# mysql -u root -p –prompt=”\u@\h-DB:\d>” –safe-updates –select_limit=3

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.1.30 MySQL Community Server (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

root@localhost-DB:(none)>use test
Database changed
root@localhost-DB:test>select count(*) from T1;
+———-+
| count(*) |
+———-+
|        9 |
+———-+
1 row in set (0.00 sec)

root@localhost-DB:test>select * from T1;
+—-+————+—————+
| id | name       | comment       |
+—-+————+—————+
|  1 | SHOW WARIN | test update   |
|  2 | SHOW WARIN | 22222222222   |
|  3 | select_lim | test 20090117 |
+—-+————+—————+
3 rows in set (0.00 sec)

root@localhost-DB:test>

–select_limitオプションでMax3件までのデータが表示されています。

select-limit

select-limit



[root@localhost scripts]# cat mysqldump_and_gz.sh
#! /bin/bash

######################################################
##   localhostデータベースバックアップ
##   From: localhost
##   Created Date: 2009年01月17日
##   Changed On  : 2009年02月17日
##   Created by: variable.jp
##   Job Time: 3:00am   Tape Backup:  9:00am
######################################################

################## 日付設定 ##################
set echo
TODAY=`date -d 'today' +%Y%m%d`
DFILES=`date -d '7 days ago' +%Y%m%d`
############ DBバックアップ完了 #############
/usr/local/mysql/bin/mysqldump --user=username -ppassword -F --all-databases | gzip > /backup/mysql/ALLDB_${TODAY}.sql.gz
############ 1 Week前のバックアップファイル削除 #############

rm /backup/mysql/ALLDB_${DFILES}.sql.gz
[root@localhost scripts]#

「参考URL」
スタックアスタリスク


–safe-updatesオプションを指定してDBに接続すると、プライマリーキーを
指定しないとUPDATEやDELETEなどの更新クエリーを実行出来ないように
するオプションです。WHERE句を忘れてデータすべて変更してしまったなど
と焦らないようにする為に良いかもしれません。

[root@colinux mysql5130]# mysql -u root -p --prompt="\u@\h-DB:\d>" --safe-updates
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.30 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

root@localhost-DB:(none)>use test
Database changed
root@localhost-DB:test>show tables;
+----------------+
| Tables_in_test |
+----------------+
| T1             |
+----------------+
1 row in set (0.00 sec)

root@localhost-DB:test>select * from T1;
+----+------------+-------------+
| id | name       | comment     |
+----+------------+-------------+
|  1 | SHOW WARIN | 1111111     |
|  2 | SHOW WARIN | 22222222222 |
+----+------------+-------------+
2 rows in set (0.00 sec)

root@localhost-DB:test>

root@localhost-DB:test>update T1 set comment="test update";
ERROR 1175 (HY000): You are using safe update mode and you tried to update a tab
le without a WHERE that uses a KEY column
root@localhost-DB:test>update T1 set comment="test update" where id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost-DB:test>select * from T1;
+----+------------+-------------+
| id | name       | comment     |
+----+------------+-------------+
|  1 | SHOW WARIN | test update |
|  2 | SHOW WARIN | 22222222222 |
+----+------------+-------------+
2 rows in set (0.00 sec)

root@localhost-DB:test>

※primaryキーを指定しないと更新出来ないので、ある程度まとまったデータ
を更新する場合は、BEGIN TRAN ~ COMMIT TRAN/ROLLBACK TRAN
などで処理を戻せるようにする事で対応する事も可能ですね。