LocalMySQLの作り方

使い捨てmysqlをdocker で作成して、javaで接続してみる

MySQL のdocekrImageをpull

$ docker pull mysql:5.7

確認

$ docker images

先ほどダウンロードしたimageを使ってコンテナを起動する ※ MYSQL_ROOT_PASSWORDを適当に設定

$ docker run \
    --name my-mysql \
    -e MYSQL_ROOT_PASSWORD=admin \
    -p 3306:3306 \
    -d mysql:5.7

terminalから先ほどのdatabaseに接続してみる(ちょっと起動まで時間がかかる)

$ mysql -uroot -psecret --port=33306 --host=127.0.0.1

全てのデータベースのサイズを確認する

MB単位

select table_schema, sum(data_length+index_length) /1024 /1024 as MB from information_schema.tables  group by table_schema order by sum(data_length+index_length) desc;

+--------------------------+---------------+
| table_schema             | MB                  |
+--------------------------+---------------+
| database1                 | 5579.89978790| 
| database2                 | 3658.48437786| 
+--------------------------+---------------+

GB単位

select table_schema, sum(data_length+index_length) /1024 /1024/1024 as GB from information_schema.tables  group by table_schema order by sum(data_length+index_length) desc;

+--------------------------+----------------+
| table_schema             | GB                |
+--------------------------+----------------+
| database1                  | 5.449120886624|  
| database2                  | 3.572738650255| 
+--------------------------+----------------+

特定のデータベースのサイズを確認する

use database1;
select table_schema, sum(data_length+index_length) /1024 /1024 as MB from information_schema.tables where table_schema = database();

もしくは

select table_schema, sum(data_length+index_length) /1024 /1024 as MB from information_schema.tables where table_schema = 'databaseName';

+-----------------+---------------+
| table_schema | MB            |
+-----------------+---------------+
| database1      | 5579.89978790| 
+-----------------+---------------+

テーブル単位でサイズを確認する

use databaseName;
select table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,  floor((data_length+index_length)/1024/1024) as allMB,  #総容量floor((data_length)/1024/1024) as dMB,  #データ容量floor((index_length)/1024/1024) as iMB   #インデックス容量from information_schema.tables  where table_schema=database()  order by (data_length+index_length) desc; 

+------------------------------+--------+----------+------+-------+------+------+
| table_name                | engine     | tbl_rows  | rlen | allMB | dMB | iMB |
+------------------------------+--------+----------+------+-------+------+------+
| table1                 | MyISAM   |   156382     | 3738 |   570 |  557 |   12 | 
| table2                 | MyISAM   |   185280     | 1624 |   311 |  287 |   24 | 
| table3                 | MyISAM   |   208823     |  394  |   103 |   78  |   24 | 
+------------------------------+--------+----------+------+-------+------+------+

MySQLコマンド

データベースに接続

mysql -u root -p
-u ----- 「ユーザを指定する」というオプション
-u root ----- 「rootユーザで」みたいな意味
-p ----- 「パスワードを指定してログインする」というオプション

データベース一覧の表示

SHOW DATABASES;

現在のデータベース確認

SELECT DATABASE();

データベースの中身を確認

SHOW DATABASES;

データベースの作成

CREATE DATABASE [データベース名];

データベースの削除

DROP DATABASE [データベース名];

使用するデータベースの選択

USE [データベース名];

テーブル一覧の表示

SHOW TABLES;

テーブルの作成

CREATE TABLE [テーブル名] (
    column1 [データ型] [その他オプション],
    column2 [データ型] [その他オプション],
    column3 [データ型] [その他オプション],
);

例)

CREATE TABLE `posts`(
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `user_id` int(10) unsigned NOT NULL,
   `post` varchar(512) NOT NULL,
   PRIMARY KEY (`id`)
);

CREATE TABLE文を確認

SHOW CREATE TABLE [テーブル名];

テーブルにデータを挿入(INSERT)

INSERT INTO `users`(`id`, `name`, `address`, `tel`) VALUES(1, 'nishide', 'Cebu', '080-xxxx-oooo');

例) postsテーブルにサンプルデータをインサート

INSERT INTO `posts`(
    `user_id`,
    `post`
) VALUES (
    1,
    'hogehoge fugafuga なんやで'
);

テーブルからデータを取得(SELECT)

SELECT * FROM `users`;

抽出するデータを一部にする

SELECT `id`, `name` FROM `users`;

条件つきでデータを取得

SELECT * FROM `users` WHERE id = 2;

複数の条件で絞り込んでデータを取得

SELECT * FROM `users` WHERE id > 3 and id < 6;

並べ替えてデータを取得

SELECT * FROM `users` ORDER BY id ASC;
SELECT * FROM `users` ORDER BY id DESC;

データを更新

UPDATE [テーブル名] SET [COLUMN名] = '新しい値'

データを削除

DELETE FROM [テーブル名] WHERE [条件]

MySQLでコマンドから直接クエリを実行 mysqlコマンドを実行して、中にはいってからSQLを実行しても良いんだけど、ちょっとした事だったら面倒くさいし、シェルでワンライナーでいけるならそっちのが良いっていうときには -eオプションを使うと直接クエリを実行することができる。

$mysql -u root DBNAME -e "クエリ"

これと、 into outfile を併用することでテーブルの中身をCSVファイルとして書き出すことができる。例)

$mysql -u root DBNAME -e
"select * from table into outfile '/tmp/output.csv'
fields terminated by ',' enclosed by '\"'"
例) 
 mysql -u root -e "select user,host from mysql.user;"

MySQL 性能試験 mysql ---> telegraf ---> influxdb ---> Grafana

Version確認

mysqladmin -u root -p version

現在の接続しているスレッド数

mysql> show status like 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 3     |
+-------------------+-------+
1 row in set (0.00 sec)

あるいは SELECT文で

SELECT * FROM information_schema.PROCESSLIST;

プロセスリスト（処理中の接続）を表示する

mysql> show processlist;
+------+------+-----------------+---------------------------+---------+------+-------+------------------+
| Id   | User | Host            | db                        | Command | Time | State | Info             |
+------+------+-----------------+---------------------------+---------+------+-------+------------------+
|   68 | root | localhost:36562 | hogehogehogehogehogehogeh | Sleep   |   30 |       | NULL             |
|   69 | root | localhost:36563 | NULL                      | Sleep   |   40 |       | NULL             |
| 2268 | root | localhost       | hogehogehogehogehogehogeh | Query   |    0 | init  | show processlist |
+------+------+-----------------+---------------------------+---------+------+-------+------------------+
3 rows in set (0.00 sec)

不要なコネクションをkill

mysql> kill 69;
Query OK, 0 rows affected (0.00 sec)

60秒以上経過しているコネクションを抽出 RDS の場合'rdsadmin'は除外

mysql> SELECT * FROM information_schema.PROCESSLIST WHERE time >= 60 and user != 'rdsadmin';

そのIDをカンマ区切りで抽出

mysql> SELECT GROUP_CONCAT(id) FROM information_schema.PROCESSLIST WHERE time >= 60 and user != 'rdsadmin';

まとめて kill

$ mysqladmin kill xx,xx,xx -h host -u user -p

（おまけ）起動してからの累積接続数

mysql> show status like 'Conn%'; 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 85    |
+---------------+-------+
1 row in set (0.01 sec)

（おまけ）プライマリ/レプリカの判別

SHOW GLOBAL VARIABLES LIKE 'innodb_read_only';

プライマリは OFF, レプリカは ON

MySQLのデータベースの場所を調べたり、ディレクトリ変更したりのメモ MySQL （mysql Ver 14.14 Distrib 5.1.58, for debian-linux-gnu (x86_64) using readline 6.2）データベースの場所を調べる

mysql -u root -p#パスワード入力> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+1 row in set (0.00 sec)

ユーザの確認

mysql> select Host, User, Password from mysql.user;
+-----------+----------+-------------------------------------------+
| Host      | User     | Password                                  |
+-----------+----------+-------------------------------------------+
| %         | admin    | *6B5EDDE567F4F29018862811195DBD14B8ADDD2A |
| localhost | dev      | *6B5EDDE567F4F29018862811195DBD14B8ADDD2A |
| localhost | rdsadmin | *6FE95FED37F22C2C8E3910CE6AD0D9144D1E600B |
+-----------+----------+-------------------------------------------+
3 rows in set (0.02 sec)