MySQLの個人的なメモ

自宅のKVM上のDebianにDBサーバ(Mysql)の設定をしたので、ついでに設定方法を書き残しておこうと思います。

このての設定ってサーバ作るときに1回やるだけなのでいつまでも覚えてなかったりするのです。

ここでやるのは、Mysqlのインストール、ユーザの追加から適当なデータベース、テーブルを作成してSQLでゴニョゴニョするまでを書いていきます。

設定だけだと間が持たなそうなのでシェルからMySQLを呼び出してみたりとか、その他使い方とかを載せてみます。

写真

 

前提条件

MySQLをインストールするサーバ:Debian(Wheezy)

MySQLサーバに接続するクライアント:Ubuntu13.10

 

 

—-Debianでの設定—-

Mysql(サーバ)のインストール

sudo apt-get install mysql-server

 

Mysql(クライアント)のインストール

sudo apt-get install mysql-client

 

ローカル以外からのアクセスを可能にする

sudo vi /etc/mysql/my.cnf
 
bind-address = 127.0.0.1
 
↓
 
#bind-address = 127.0.0.1

 

 

MySQLにログイン(Debianから接続)

————————————————

mysql -u hoge -pパスワード

 

 

MySQLへの新規ユーザの追加(その1)

————————————————

mysql> create user ‘hoge’@’brokendish’; <–パスワード無しユーザ作成の場合

mysql> create user ‘hoge’@’brokendish’ IDENTIFIED BY ‘hoge’; <–パスワード有りユーザ作成(ホスト名付き)

mysql> create user ‘hoge’@’%’ IDENTIFIED BY ‘hoge’; <–パスワード有りユーザ作成(ホスト名省略)

 

 

MySQLへの新規ユーザの追加(その2)

————————————————

GRANT構文の実行権限をもつユーザを作成

mysql> GRANT ALL PRIVILEGES ON *.* TO hoge@’%’ IDENTIFIED BY ‘hoge’ WITH GRANT OPTION;

mysql> FLUSH PRIVILEGES;

 

権限の確認

————————————————

mysql> SHOW GRANTS FOR hoge;

 

ユーザの確認

————————————————

mysql> select User,Host from mysql.user;

+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| hoge             | %         |
| root             | 127.0.0.1 |
| root             | ::1       |
| root             | debian    |
| debian-sys-maint | localhost |
| root             | localhost |
+------------------+-----------+

 

ユーザの削除

————————————————

mysql> DELETE FROM mysql.user WHERE user=’hoge’;

mysql> FLUSH PRIVILEGES;

 

 データベース作成(文字コード指定無し)

————————————————

mysql> create database xml_normalize;

 

 

データベース作成(文字コード指定「DEFAULT CHARACTER SET=utf8」)

————————————————

mysql> create database xml_normalize DEFAULT CHARACTER SET=utf8;

 

 

データベース削除

————————————————

mysql> drop database xml_normalize;

 

 

データベース一覧

————————————————

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| xml_normalize      |
+--------------------+

 

 

MySQLにログイン(Ubuntuからサーバ(Debian)に接続)

————————————————

mysql -h 192.168.122.192 -u hoge -pパスワード

 

MySQLにログイン(Debianから接続)

————————————————

mysql -u hoge -pパスワード

 

データベース接続

————————————————

mysql> use データベース名;

 

使用中のデータベース確認

————————————————

mysql> select database();

+---------------+
| database()    |
+---------------+
| xml_normalize |
+---------------+

 

 

接続ユーザ確認

————————————————

mysql> select user();

+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+

 

 

スキーマ情報の確認

————————————————

mysql> SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | mysql              | latin1                     | latin1_swedish_ci      | NULL     |
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | xml_normalize      | utf8                       | utf8_general_ci        | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+

 

 

テーブル一覧表示

————————————————

mysql> show tables;

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+

 

 

カラム情報取得

————————————————

mysql> SHOW COLUMNS FROM BB;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| key1  | varchar(20) | YES  |     | NULL    |       |
| B1    | varchar(20) | YES  |     | NULL    |       |
| B2    | varchar(20) | YES  |     | NULL    |       |
| B3    | varchar(20) | YES  |     | NULL    |       |
| B4    | varchar(20) | YES  |     | NULL    |       |
| B5    | varchar(20) | YES  |     | NULL    |       |
| B6    | varchar(20) | YES  |     | NULL    |       |
| B7    | varchar(20) | YES  |     | NULL    |       |
| B8    | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

 

 

罫線を出したくない時

————————————————

結果表示の罫線を表示したくない時はログイン時に以下のように[-s]を指定する

mysql -s -u root -p

————————————————

SHOW COLUMNS FROM BB;

Field	Type	Null	Key	Default	Extra
key1	varchar(20)	YES		NULL	
B1	varchar(20)	YES		NULL	
B2	varchar(20)	YES		NULL	
B3	varchar(20)	YES		NULL	
B4	varchar(20)	YES		NULL	
B5	varchar(20)	YES		NULL	
B6	varchar(20)	YES		NULL	
B7	varchar(20)	YES		NULL	
B8	varchar(20)	YES		NULL

 

 

カラム名を出したくない時

————————————————

結果表示のカラム名を表示したくない時はログイン時に以下のように[-N]を指定する

mysql -s -N -u root -p

————————————————

SHOW COLUMNS FROM BB;

key1	varchar(20)	YES		NULL	
B1	varchar(20)	YES		NULL	
B2	varchar(20)	YES		NULL	
B3	varchar(20)	YES		NULL	
B4	varchar(20)	YES		NULL	
B5	varchar(20)	YES		NULL	
B6	varchar(20)	YES		NULL	
B7	varchar(20)	YES		NULL	
B8	varchar(20)	YES		NULL

 

 

シェルからMYSQLを呼出してみる

————————————————

#!/bin/sh
 
mysql -h 192.168.122.192 -u hoge -phoge -e "show databases;"
 
echo "
#------------------------------------------------
create temporary table AA(
 key1 varchar(20),
 OBJ  varchar(2000),
 A1   varchar(20)
);
insert into AA values('1','XXXXXXX','ABC');
#------------------------------------------------
create temporary table BB(
 key1 varchar(20),
 B1   varchar(20),
 B2   varchar(20),
 B3   varchar(20),
 B4   varchar(20),
 B5   varchar(20),
 B6   varchar(20),
 B7   varchar(20),
 B8   varchar(20)
);
insert into BB values('1','A11','A12','A13','A14','A15','A16','A17','A18');
insert into BB values('1','A21','A22','A23','A24','A25','A26','A27','A28');
insert into BB values('2','B11','B12','B13','B14','B15','B16','B17','B18');
insert into BB values('2','B21','B22','B23','B24','B25','B26','B27','B28');
 
select AA.A1,BB.B1,BB.B2,BB.B3,BB.B4,BB.B5,BB.B6,BB.B7,BB.B8
from BB cross join AA;
"|mysql -h 192.168.122.192 -u hoge -phoge xml_normalize

 

————————————————

実行

./sql.sh

 

結果

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| xml_normalize      |
+--------------------+
A1	B1	B2	B3	B4	B5	B6	B7	B8
ABC	A11	A12	A13	A14	A15	A16	A17	A18
ABC	A21	A22	A23	A24	A25	A26	A27	A28
ABC	B11	B12	B13	B14	B15	B16	B17	B18
ABC	B21	B22	B23	B24	B25	B26	B27	B28

 

 

MySQLのバックアップ

————————————————

全てのデータベースのバックアップを取る(SQLベースで吐き出される)

mysqldump -u root -p -x –all-databases –events > BkupDump.sql

指定したデータベース(bkuptekitoo)のみバックアップを取得(SQLベースで吐き出される)

mysqldump -u root -p -x –events bkuptekitoo > BkupDump.sql

 

指定したデータベース(bkuptekitoo)の指定したテーブル(BBとDD)のみバックアップを取得(SQLベースで吐き出される)

mysqldump -u root -p -x –events bkuptekitoo BB DD > BkupDump.sql

 

 

MySQLのバックアップからの復元

————————————————

全てのデータベースのバックアップを復元する

mysql -u root -p < BkupDump.sql

指定したデータベース(bkuptekitoo)のみバックアップから復元

mysql -u root -p bkuptekitoo < BkupDump.sql

 

その他

一時テーブル(テンポラリテーブル)で色々やってみる

(一時テーブルはMysqlからログアウトするとDropされて無くなる)

#------------------------------------------------------------------------------------
# パターン 1
#------------------------------------------------------------------------------------
USE xml_normalize
#------------------------------------------------
DROP TABLE AA;
CREATE TEMPORARY TABLE AA(
 key1 VARCHAR(20),
 OBJ  VARCHAR(2000),
 A1   VARCHAR(20)
);
INSERT INTO AA VALUES('1','XXXXXXX','ABC');
#------------------------------------------------
DROP TABLE BB;
CREATE TEMPORARY TABLE BB(
 key1 VARCHAR(20),
 B1   VARCHAR(20),
 B2   VARCHAR(20),
 B3   VARCHAR(20),
 B4   VARCHAR(20),
 B5   VARCHAR(20),
 B6   VARCHAR(20),
 B7   VARCHAR(20),
 B8   VARCHAR(20)
);
INSERT INTO BB VALUES('1','A11','A12','A13','A14','A15','A16','A17','A18');
INSERT INTO BB VALUES('1','A21','A22','A23','A24','A25','A26','A27','A28');
INSERT INTO BB VALUES('2','B11','B12','B13','B14','B15','B16','B17','B18');
INSERT INTO BB VALUES('2','B21','B22','B23','B24','B25','B26','B27','B28');
 
#**************************************************************************
#結果表示
#**************************************************************************
SELECT AA.A1,BB.B1,BB.B2,BB.B3,BB.B4,BB.B5,BB.B6,BB.B7,BB.B8
FROM BB CROSS JOIN AA;
+------+------+------+------+------+------+------+------+------+
| A1   | B1   | B2   | B3   | B4   | B5   | B6   | B7   | B8   |
+------+------+------+------+------+------+------+------+------+
| ABC  | A11  | A12  | A13  | A14  | A15  | A16  | A17  | A18  |
| ABC  | A21  | A22  | A23  | A24  | A25  | A26  | A27  | A28  |
| ABC  | B11  | B12  | B13  | B14  | B15  | B16  | B17  | B18  |
| ABC  | B21  | B22  | B23  | B24  | B25  | B26  | B27  | B28  |
+------+------+------+------+------+------+------+------+------+
4 rows in set (0.00 sec)

 

#------------------------------------------------------------------------------------
# パターン 2
#------------------------------------------------------------------------------------
USE xml_normalize
 
#------------------------------------------------
DROP TABLE AA;
CREATE TEMPORARY TABLE AA(
 key1 VARCHAR(20),
 A1   VARCHAR(20),
 OBJ  VARCHAR(2000),
 A2   VARCHAR(20),
 A3   VARCHAR(20)
);
INSERT INTO AA VALUES('1','A000','XXXXXXX','B000','C000');
#------------------------------------------------
DROP TABLE BB;
CREATE TEMPORARY TABLE BB(
 key1 VARCHAR(20),
 B1   VARCHAR(20),
 B2   VARCHAR(20),
 B3   VARCHAR(20),
 B4   VARCHAR(20),
 B5   VARCHAR(20),
 OBJ  VARCHAR(2000)
);
INSERT INTO BB VALUES('1','a11','a12','a13','a14','a15','XXXXXXX');
INSERT INTO BB VALUES('2','b21','b22','b23','b24','b25','XXXXXXX');
INSERT INTO BB VALUES('3','c11','c12','c13','c14','c15','XXXXXXX');
#------------------------------------------------
DROP TABLE CC;
CREATE TEMPORARY TABLE CC(
 key1 VARCHAR(20),
 C1   VARCHAR(20),
 C2   VARCHAR(20),
 C3   VARCHAR(20),
 C4   VARCHAR(20),
 C5   VARCHAR(20),
 C6   VARCHAR(20),
 C7   VARCHAR(20),
 C8   VARCHAR(20)
);
INSERT INTO CC VALUES('1','A11','A12','A13','A14','A15','A16','A17','A18');
INSERT INTO CC VALUES('1','A21','A22','A23','A24','A25','A26','A27','A28');
INSERT INTO CC VALUES('2','B11','B12','B13','B14','B15','B16','B17','B18');
INSERT INTO CC VALUES('2','B21','B22','B23','B24','B25','B26','B27','B28');
INSERT INTO CC VALUES('3','C11','C12','C13','C14','C15','C16','C17','C18');
INSERT INTO CC VALUES('3','C21','C22','C23','C24','C25','C26','C27','C28');
 
#**************************************************************************
#結果表示
#**************************************************************************
SELECT AA.A1,BB.B1,BB.B2,BB.B3,BB.B4,BB.B5,CC.C1,CC.C2,CC.C3,CC.C4,CC.C5,CC.C6,CC.C7,CC.C8,AA.A2,AA.A3
FROM (BB LEFT OUTER JOIN CC 
         ON BB.key1=CC.key1) CROSS JOIN AA;
+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
| A1   | B1   | B2   | B3   | B4   | B5   | C1   | C2   | C3   | C4   | C5   | C6   | C7   | C8   | A2   | A3   |
+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
| A000 | a11  | a12  | a13  | a14  | a15  | A11  | A12  | A13  | A14  | A15  | A16  | A17  | A18  | B000 | C000 |
| A000 | a11  | a12  | a13  | a14  | a15  | A21  | A22  | A23  | A24  | A25  | A26  | A27  | A28  | B000 | C000 |
| A000 | b21  | b22  | b23  | b24  | b25  | B11  | B12  | B13  | B14  | B15  | B16  | B17  | B18  | B000 | C000 |
| A000 | b21  | b22  | b23  | b24  | b25  | B21  | B22  | B23  | B24  | B25  | B26  | B27  | B28  | B000 | C000 |
| A000 | c11  | c12  | c13  | c14  | c15  | C11  | C12  | C13  | C14  | C15  | C16  | C17  | C18  | B000 | C000 |
| A000 | c11  | c12  | c13  | c14  | c15  | C21  | C22  | C23  | C24  | C25  | C26  | C27  | C28  | B000 | C000 |
+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
6 rows in set (0.00 sec)

 

#------------------------------------------------------------------------------------
# パターン 3
#------------------------------------------------------------------------------------
USE xml_normalize
 
#------------------------------------------------
DROP TABLE BB;
CREATE TEMPORARY TABLE BB(
 key1 VARCHAR(20),
 B1   VARCHAR(20),
 B2   VARCHAR(20),
 B3   VARCHAR(20),
 B4   VARCHAR(20),
 B5   VARCHAR(20),
 OBJ  VARCHAR(2000)
);
INSERT INTO BB VALUES('1','a11','a12','a13','a14','a15','XXXXXXX');
INSERT INTO BB VALUES('1','aa1','aa2','aa3','a14','a15','XXXXXXX');
INSERT INTO BB VALUES('2','b21','b22','b23','b24','b25','XXXXXXX');
INSERT INTO BB VALUES('3','c11','c12','c13','c14','c15','XXXXXXX');
#------------------------------------------------
DROP TABLE CC;
CREATE TEMPORARY TABLE CC(
 key1 VARCHAR(20),
 key2 VARCHAR(20),
 C1   VARCHAR(20),
 C2   VARCHAR(20),
 OBJ  VARCHAR(2000)
);
INSERT INTO CC VALUES('1','1','A11','A12','XXXXXXX');
INSERT INTO CC VALUES('1','2','A21','A22','XXXXXXX');
INSERT INTO CC VALUES('2','1','B11','B12','XXXXXXX');
INSERT INTO CC VALUES('2','2','B21','B22','XXXXXXX');
INSERT INTO CC VALUES('3','1','C11','C12','XXXXXXX');
INSERT INTO CC VALUES('3','2','C21','C22','XXXXXXX');
#------------------------------------------------
DROP TABLE DD;
CREATE TEMPORARY TABLE DD(
 key1 VARCHAR(20),
 key2 VARCHAR(20),
 D1   VARCHAR(20),
 D2   VARCHAR(20),
 D3   VARCHAR(20),
 D4   VARCHAR(20),
 D5   VARCHAR(20),
 D6   VARCHAR(20)
);
INSERT INTO DD VALUES('1','1','A11','A12','A13','A14','A15','A16');
INSERT INTO DD VALUES('1','1','A21','A22','A23','A24','A25','A26');
INSERT INTO DD VALUES('1','1','A31','A32','A33','A34','A35','A36');
INSERT INTO DD VALUES('1','1','A41','A42','A43','A44','A45','A46');
INSERT INTO DD VALUES('2','1','B11','B12','B13','B14','B15','B16');
INSERT INTO DD VALUES('2','1','B21','B22','B23','B24','B25','B26');
INSERT INTO DD VALUES('2','1','B31','B32','B33','B34','B35','B36');
INSERT INTO DD VALUES('3','1','C11','C12','C13','C14','C15','C16');
INSERT INTO DD VALUES('3','1','C21','C22','C23','C24','C25','C26');
 
#**************************************************************************
#結果表示
#**************************************************************************
SELECT BB.B1,BB.B2,BB.B3,BB.B4,BB.B5,CC.C1,CC.C2,DD.D1,DD.D2,DD.D3,DD.D4,DD.D5,DD.D6
FROM (BB LEFT OUTER JOIN (CC LEFT OUTER JOIN DD
                    ON CC.key1=DD.key1 
                    AND CC.key2=DD.key2)
          ON  BB.key1=CC.key1
     );
+------+------+------+------+------+------+------+------+------+------+------+------+------+
| B1   | B2   | B3   | B4   | B5   | C1   | C2   | D1   | D2   | D3   | D4   | D5   | D6   |
+------+------+------+------+------+------+------+------+------+------+------+------+------+
| a11  | a12  | a13  | a14  | a15  | A11  | A12  | A11  | A12  | A13  | A14  | A15  | A16  |
| a11  | a12  | a13  | a14  | a15  | A11  | A12  | A21  | A22  | A23  | A24  | A25  | A26  |
| a11  | a12  | a13  | a14  | a15  | A11  | A12  | A31  | A32  | A33  | A34  | A35  | A36  |
| a11  | a12  | a13  | a14  | a15  | A11  | A12  | A41  | A42  | A43  | A44  | A45  | A46  |
| a11  | a12  | a13  | a14  | a15  | A21  | A22  | NULL | NULL | NULL | NULL | NULL | NULL |
| aa1  | aa2  | aa3  | a14  | a15  | A11  | A12  | A11  | A12  | A13  | A14  | A15  | A16  |
| aa1  | aa2  | aa3  | a14  | a15  | A11  | A12  | A21  | A22  | A23  | A24  | A25  | A26  |
| aa1  | aa2  | aa3  | a14  | a15  | A11  | A12  | A31  | A32  | A33  | A34  | A35  | A36  |
| aa1  | aa2  | aa3  | a14  | a15  | A11  | A12  | A41  | A42  | A43  | A44  | A45  | A46  |
| aa1  | aa2  | aa3  | a14  | a15  | A21  | A22  | NULL | NULL | NULL | NULL | NULL | NULL |
| b21  | b22  | b23  | b24  | b25  | B11  | B12  | B11  | B12  | B13  | B14  | B15  | B16  |
| b21  | b22  | b23  | b24  | b25  | B11  | B12  | B21  | B22  | B23  | B24  | B25  | B26  |
| b21  | b22  | b23  | b24  | b25  | B11  | B12  | B31  | B32  | B33  | B34  | B35  | B36  |
| b21  | b22  | b23  | b24  | b25  | B21  | B22  | NULL | NULL | NULL | NULL | NULL | NULL |
| c11  | c12  | c13  | c14  | c15  | C11  | C12  | C11  | C12  | C13  | C14  | C15  | C16  |
| c11  | c12  | c13  | c14  | c15  | C11  | C12  | C21  | C22  | C23  | C24  | C25  | C26  |
| c11  | c12  | c13  | c14  | c15  | C21  | C22  | NULL | NULL | NULL | NULL | NULL | NULL |
+------+------+------+------+------+------+------+------+------+------+------+------+------+
17 rows in set (0.00 sec)

 

 

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です