目次

コマンド

テーブル構造・カラム一覧

> DESCRIBE テーブル名;

index確認

> show index from テーブル;

テーブル定義確認

> show create table テーブル;

ユーザ確認

> SELECT host,user FROM mysql.user;
+-----------+------+
| host      | user |
+-----------+------+
| 127.0.0.1 | root |
| localhost |      |
| localhost | root |
+-----------+------+
3 rows in set (0.00 sec)

> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.02 sec)

文字列連結

CONCAT

> SELECT CONCAT('THIS ','IS ','PEN');
+---------------------------------------------------------------------+
| CONCAT('THIS','IS','PEN')                                           |
+---------------------------------------------------------------------+
| THIS IS PEN                                                         |
+---------------------------------------------------------------------+
1 row in set (0.02 sec)

PIPES_AS_CONCAT

> set @@session.sql_mode='PIPES_AS_CONCAT';
Query OK, 0 rows affected (0.00 sec)
> SELECT 'THIS ' || 'IS ' || 'PEN';
+---------------------------------------------------------------------+
| 'THIS ' || 'IS ' || 'PEN'                                           |
+---------------------------------------------------------------------+
| THIS IS PEN                                                         |
+---------------------------------------------------------------------+
1 row in set (0.02 sec)
# vi /etc/my.cnf
+) sql_mode = 'PIPES_AS_CONCAT'

エラーを無視してインポートする

-f, –force Continue even if we get an SQL error. 

mysql -uroot -ppassword -f dbname < ddl.sql

接続中のMySQLが動作しているホスト名を確認

mysql> show variables like '%hostname%';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| hostname      | mc-db04 |
+---------------+---------+
1 row in set (0.00 sec)

サーバステータスを確認

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.32, for Linux (x86_64) using readline 5.1

Connection id:          4903
Current database:       
Current user:           root@192.168.24.222
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.32-log MySQL Community Server (GPL) by Remi
Protocol version:       10
Connection:             192.168.24.183 via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 39 min 39 sec

Threads: 2  Questions: 1606  Slow queries: 0  Opens: 125  Flush tables: 1  Open tables: 33  Queries per second avg: 0.675
--------------