1. 概要
これは わたしが よく使うコマンドです。お間違えのないよう。
以下、FreeBSD にインストールしている MySQL を操作するときの記述です。
2. データベース一覧
SHOW DATABASES;
実際に見てみます。
$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2778
Server version: 5.6.26 Source distribution
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| myhome |
| mysql |
| performance_schema |
| test |
| wordpress |
| xoops |
+--------------------+
7 rows in set (0.00 sec)
mysql> quit
Bye
3. テーブル一覧
SHOW TABLES FROM データベース名;
実際に見てみます。
$ mysql -u root -p
Enter password: ← パスワードを入力
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21-log Source distribution
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@localhost [(none)]> SHOW TABLES FROM mysql;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
4. カラム一覧
SHOW COLUMNS FROM テーブル名;
実際に見てみます。
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.21-log Source distribution
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@localhost [(none)]> use mysql
Database changed
root@localhost [mysql]> SHOW COLUMNS FROM user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
・・・
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)
root@localhost [mysql]> quit
Bye
5. 文字コードを調べる
mySQL が動作している状態で mysql
$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.26 Source distribution
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql のプロンプトが表示されたところで status
mysql> status
--------------
mysql Ver 14.14 Distrib 5.6.26, for FreeBSD10.2 (i386) using EditLine wrapper
Connection id: 3
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: more
Using outfile: ''
Using delimiter: ;
Server version: 5.6.26 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /tmp/mysql.sock
Uptime: 52 min 13 sec
Threads: 1 Questions: 12 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.003
--------------
とまあこんな感じです。
6. データベースの作成
以下、すべて mySQL のクライアントインタフェース上ですが・・・。
データベースの作成
mysql> CREATE DATABASE データベース名;
ユーザにデータベースへの権限を与える
mysql> GRANT ALL ON データベース名.テーブル名 TO ユーザ名;
7. ユーザ作成
単に、ユーザを作成するならば、「root」ユーザで、「mysql」を起動して
CREATE USER 'ユーザ名'@'localhost' IDENTIFIED BY 'パスワード';
データベースの作成とともに、オーナーを設定することもできます。
CREATE DATABASE データベース名;
GRANT ALL PRIVILEGES ON データベース名.* to ユーザ名@localhost IDENTIFIED BY 'パスワード';
8. ユーザ一覧
SELECT HOST, USER, PASSWORD FROM MYSQL.USER;
「root」で、ログインする必要があるようです。
> mysql -u root -p
Enter password: ← パスワードを入力
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 606
Server version: 5.7.24-log Source distribution
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select Host, User, Password from mysql.user;
+---------------+------------+-------------------------------------------+
| Host | User | Password |
+---------------+------------+-------------------------------------------+
| localhost | root | |
| ns.sing.ne.jp | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| ns.sing.ne.jp | | |
| localhost | ユーザ名 | *ちゃい |
+---------------+------------+-------------------------------------------+
11 rows in set (0.13 sec)
