MySQL 関連
単純な MySQL コマンドに関してのメモ書き． 本格的に使うわけではないが， MacOS 上でのWordPress の稼働確認(Mac Tips > MacOSデータ移設)とか， 簡単な私的DBの取り扱いなどで利用する．
基本事項
CentOS7 では，MariaDB がMySQLの代用品として使える． MySQL も専用の yum repository を入れれば使えるが， 自分の使い方程度では必ずMySQLであらねばならない理由も無いので，正直どちらでも構わない．
$ sudo systemctl enable mariadb.service $ sudo systemctl start mariadb.service (CentOS v8 系列では，mysqld (v8.0.26; chk 2021-12-14)を使う) $ sudo systemctl enable mysqld $ sudo systemctl start mysqld $ sudo systemctl status mysqld
起動，基本情報表示，終了
MySQL の管理者(root)のパスワードを sql_passwd とする
$ mysql -u root -psql_passwd (平文で画面表示させるのがマズイ場面なら下記) $ mysql -u root -p Enter password: (ここにパスワード入力．画面表示されない) mysql> ここが MySQL のコマンドプロンプト．このプロンプトに各種の SQL コマンドを入力して実行．
mysql> quit ※ quit でMySQLを抜ける．quit では行末のセミコロン不要． Bye $
コマンドラインで daemon の起動・停止．safe モードなど
(safe モードでの起動) $ /usr/bin/mysqld_safe & (停止) $ mysqladmin shutdown (停止; MacOSのパス) $ /usr/local/mysql/support-files/mysql.server stop
MacOS にMySQL本家パッケージを入れた場合のパスは /usr/local/mysql (実体はバージョン番号などが入ったディレクトリへのリンク)．See also WordPress セットアップ #各種パラメータ
- あるバージョン以後(MySQL v5.7以後?)だと，パスワードのセキュリティポリシーがMEDIUM以上となってるらしい．
- MySQL でのパスワードのセキュリティポリシー MEDIUM の定義は下記：
- アルファベットの大文字・小文字の両方が1文字以上存在する事
- 数字，特殊記号が両方共に1文字以上存在する事
- 上記条件をクリアした上で，文字数が8文字以上．まぁ，常識的なセキュリティポリシーと言えるだろう．
DB一覧，テーブル一覧，テーブル定義の表示，削除
存在するデータベース一覧を表示(シェルのコマンドプロンプトからの直接実行)
$ mysqlshow -u root -psql_passwd または $ mysqlshow -u root -p Enter password:
存在するデータベース一覧を表示
mysql> show databases;
データベース名 "hoge_db" のテーブル一覧表示
mysql> use hoge_db; mysql> show tables;
データベース名 "hoge_db" のテーブル "hoge_tbl" の詳細表示
mysql> use hoge_db; mysql> desc hoge_tbl;
データベース名 "hoge_db" 全体の削除
mysql> drop database hoge_db;
データベース名 "hoge_db" のテーブル "hoge_tbl" の削除
mysql> drop table hoge_tbl;
データベース名 "hoge_db" のテーブル "hoge_tbl" の名前変更(rename する)
mysql> alter table hoge_tbl rename new_table;
データベース名 "hoge_db" のテーブル "hoge_tbl" にフィールド hage (実数型，浮動小数点)を追加
mysql> alter table hoge_tbl add hage float;
データベース名 "hoge_db" のテーブル "hoge_tbl" のフィールド hage (実数型，浮動小数点)を hige (整数型) に変更
mysql> alter table hoge_tbl change hage hige int; (フィールド名は同じでデータ型だけを変更) mysql> alter table hoge_tbl modify hage int; (変更状態を確認) desc hoge_tbl;
データベース名 "hoge_db" のテーブル "hoge_tbl" のフィールド hage を削除
mysql> alter table hoge_tbl drop hage;
スクリプト実行
mysql [-t -N] DB-name < SQL-script.sql [-t | --table]: Show table form [-N | --skip-column-names]: Show values w/o column name
DBのデフォルト設定変更
/etc/my.cnf ないし /etc/my.cnf.d/ 以下 (MariaDBの場合)を修正．
$ sudo vi /etc/my.cnf.d/server.cnf ※デフォルト文字コードをUTF8に設定 --- [mysqld] character-set-server = utf8 --- $ sudo vi /etc/my.cnf ※ファイル末尾に追加．重複設定で無駄かもしれない(未確認)が念の為． -- default-character-set=utf8 character-set-server=utf8 -- $ sudo systemctl restart mariadb.service
なお，もしも既存データベースの文字コードを変更したい場合は下記：
mysql> show variables like 'char%'; ※インストール後に特に設定変更せずに使ってるなら，latin1 とかになってるはず． mysql> alter database db_name character set utf8; ※データベース名 db_name の文字コードを UTF8 に変更． ※ CentOS v8.x では，dnf で入れた MySQL のdefault 文字コードは utf8mb4 (全て4バイトで文字を表現?)だった
DBの置き場所変更
MySQL のデータ，気象 & 大気汚染物質を集めてDB化すると， 自分が持っているものだけでもかなり莫大になる． オリジナルデータの概算だけでも400-500GB程度． 使っていくうちに増えるだろうから，1TB程度まで確保可能なスペースを用意したい． この量では，標準的なデフォルトDB置き場である /var には置ききれない．
現在(2016-06-14)はテスト運用に過ぎない状態だが，近い将来にはもっと活用したいので， DB置き場の移設手順はマスターしておかねばならない．
- mysql の停止
- /etc/my.cnf のバックアップ & 編集
- socket やlog，データディレクトリの場所を変更
- 起動ファイル /etc/??? の編集, datadir の指定を変更
- ディレクトリ移動(引越し)
- mysql の再起動
$ sudo systemctl restart mariadb.service
管理
ユーザ管理
ユーザ作成．この例では，ユーザ名 hayasaki ，パスワード sql_passwdHYSK として作成． テスト用のDBを windas_site (WINDAS観測地点のメタ情報．WMO地点コード，緯度経度情報，地点名などで構成する)とする．
mysql> create user hayasaki@localhost identified by 'sql_passwdHYSK'; Query OK, 0 rows affected (0.01 sec) mysql> create database windas_site; mysql> status (作成したばかりのDBの状況表示) mysql> show tables; Empty set (0.00 sec) (作成したばかりで，テーブルを何も作ってないのでDBはカラ) mysql> grant all privileges on windas_site.* to hayasaki@localhost; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH privileges; Query OK, 0 rows affected (0.01 sec) (ユーザ一覧の表示例；一部修正してるので，実際の名前ではない) mysql> SELECT host,user FROM mysql.user; +-----------+-----------+ | host | user | +-----------+-----------+ | localhost | hogehoge | | localhost | mysql.sys | | localhost | root | | localhost | wp_admin | +-----------+-----------+ 5 rows in set (0.00 sec) (ユーザ一覧とパスワードの表示例．当然ながらパスワードは暗号化してある) (空パスワードが残っていないかをチェックするため) mysql> SELECT host,user,authentication_string FROM mysql.user; +-----------+-----------+-------------------------------------------+ | host | user | authentication_string | +-----------+-----------+-------------------------------------------+ | localhost | root | *5FA4E2A0E4BE5D07562B6D5380B9E1645BA0538E | | localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | localhost | wp_admin | *21F02A970F0FFC522A4F9428D85C96FD50B67B3F | | localhost | hogehoge | *5FA4E2A0E4BE5D07562B6D5380B9E1645BA0538E | +-----------+-----------+-------------------------------------------+ 5 rows in set (0.00 sec) (MySQL v5.7 より前(?)だと password だったようだが，v5.7 以後は authentication_string となってるらしい)
昔のテスト実行記録よりコピー． (Linux_home)/FORM/LAMP_service_test/MySQL/000readme.txt (wrote at 2009-09-29)
GRANT or REVOKE (権限付与と権限削除)
Append "hayasaki" as administrator:
mysql> GRANT ALL PRIVILEGES ON *.* TO hayasaki@"%" \
IDENTIFIED BY 'password' WITH GRANT OPTION;
*** Permit to access from remote host ***
mysql> GRANT ALL PRIVILEGES ON *.* TO hayasaki@localhost \
IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
Public DB user (NOT as root)
mysql> GRANT SELECT,INSERT,UPDATE,DELETE
-> ON db-name.*
-> TO username@localhost
-> IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;
DB管理
テーブル作成，データ登録
mysql> create table windas_site.siteinfo (code int not null primary key, lat float, lon float, hgt int, name varchar(20) );
Query OK, 0 rows affected (0.02 sec)
※1行で書いてもいいが，項目ごとに改行しても次の入力を求めるプロンプトが待ってくれてる．
mysql> load data infile '/Users/hayasaki/tmp/windas_siteinfo.csv' into table siteinfo fields terminated by ',';
Query OK, 31 rows affected (0.00 sec)
Records: 31 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from siteinfo;
+-------+-------+--------+------+--------------------+
| code | lat | lon | hgt | name |
+-------+-------+--------+------+--------------------+
| 47406 | 43.95 | 141.63 | 23 | "RUMOI" |
| 47417 | 42.92 | 143.21 | 38 | "OBIHIRO" |
| 47423 | 42.32 | 140.97 | 3 | "MURORAN" |
| 47585 | 39.65 | 141.96 | 43 | "MIYAKO" |
(中略)
| 47909 | 28.38 | 129.5 | 3 | "NAZE" |
| 47912 | 24.47 | 123.01 | 30 | "YONAGUNIJIMA" |
| 47945 | 25.83 | 131.23 | 16 | "MINAMIDAITOJIMA" |
+-------+-------+--------+------+--------------------+
31 rows in set (0.00 sec)
登録したサンプルデータを使って，MySQL コマンドの実例表示．
mysql> select count(*) from siteinfo; +----------+ | count(*) | +----------+ | 31 | +----------+ 1 row in set (0.00 sec) mysql> show fields from siteinfo; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | code | int(11) | NO | PRI | NULL | | | lat | float | YES | | NULL | | | lon | float | YES | | NULL | | | hgt | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
テーブル操作: 条件付き検索
mysql> select * from siteinfo where name like '%TAKA%'; +-------+-------+--------+------+-------------+ | code | lat | lon | hgt | name | +-------+-------+--------+------+-------------+ | 47612 | 37.11 | 138.25 | 13 | "TAKADA" | | 47891 | 34.32 | 134.05 | 9 | "TAKAMATSU" | +-------+-------+--------+------+-------------+ 2 rows in set (0.00 sec) mysql> select NAME FROM siteinfo; +--------------------+ | NAME | +--------------------+ | "RUMOI" | | "OBIHIRO" | | "MURORAN" | (中略) | "NAZE" | | "YONAGUNIJIMA" | | "MINAMIDAITOJIMA" | +--------------------+ 31 rows in set (0.00 sec) mysql> select * FROM siteinfo where code > 47770; +-------+-------+--------+------+--------------------+ | code | lat | lon | hgt | name | +-------+-------+--------+------+--------------------+ | 47795 | 33.89 | 135.13 | 3 | "WAKAYAMA/MIHAMA" | | 47800 | 34.15 | 129.22 | 130 | "IZUHARA" | | 47805 | 33.36 | 129.55 | 58 | "HIRADO" | | 47815 | 33.24 | 131.62 | 5 | "OITA" | | 47819 | 32.81 | 130.71 | 38 | "KUMAMOTO" | | 47822 | 32.58 | 131.66 | 19 | "NOBEOKA" | | 47836 | 30.38 | 130.66 | 36 | "YAKUSHIMA" | | 47848 | 31.71 | 130.32 | 25 | "KAGOSHIMA/ICHIKI" | | 47891 | 34.32 | 134.05 | 9 | "TAKAMATSU" | | 47893 | 33.57 | 133.55 | 3 | "KOCHI" | | 47898 | 32.72 | 133.01 | 31 | "SHIMIZU" | | 47909 | 28.38 | 129.5 | 3 | "NAZE" | | 47912 | 24.47 | 123.01 | 30 | "YONAGUNIJIMA" | | 47945 | 25.83 | 131.23 | 16 | "MINAMIDAITOJIMA" | +-------+-------+--------+------+--------------------+ 14 rows in set (0.00 sec) mysql> select code FROM siteinfo where code > 47770; +-------+ | code | +-------+ | 47795 | | 47800 | | 47805 | | 47815 | | 47819 | | 47822 | | 47836 | | 47848 | | 47891 | | 47893 | | 47898 | | 47909 | | 47912 | | 47945 | +-------+ 14 rows in set (0.00 sec) mysql> SELECT * FROM siteinfo WHERE code > 47600 AND code < 47800; +-------+-------+--------+------+-------------------+ | code | lat | lon | hgt | name | +-------+-------+--------+------+-------------------+ | 47612 | 37.11 | 138.25 | 13 | "TAKADA" | | 47616 | 36.06 | 136.22 | 9 | "FUKUI" | | 47626 | 36.15 | 139.38 | 30 | "KUMAGAYA" | | 47629 | 36.38 | 140.47 | 29 | "MITO" | | 47636 | 35.17 | 136.96 | 51 | "NAGOYA" | | 47640 | 35.5 | 138.76 | 860 | "KAWAGUCHIKO" | | 47656 | 34.98 | 138.4 | 14 | "SHIZUOKA" | | 47663 | 34.07 | 136.19 | 15 | "OWASE" | | 47674 | 35.15 | 140.31 | 12 | "KATSUURA" | | 47678 | 33.12 | 139.78 | 152 | "HACHIJOJIMA" | | 47746 | 35.53 | 134.2 | 6 | "TOTTORI" | | 47755 | 34.9 | 132.07 | 20 | "HAMADA" | | 47795 | 33.89 | 135.13 | 3 | "WAKAYAMA/MIHAMA" | +-------+-------+--------+------+-------------------+ 13 rows in set (0.00 sec) mysql> SELECT * FROM siteinfo WHERE lat > 35.0 AND lat < 36.0; +-------+-------+--------+------+---------------+ | code | lat | lon | hgt | name | +-------+-------+--------+------+---------------+ | 47636 | 35.17 | 136.96 | 51 | "NAGOYA" | | 47640 | 35.5 | 138.76 | 860 | "KAWAGUCHIKO" | | 47674 | 35.15 | 140.31 | 12 | "KATSUURA" | | 47746 | 35.53 | 134.2 | 6 | "TOTTORI" | +-------+-------+--------+------+---------------+ 4 rows in set (0.00 sec)
テーブル操作: search & 並び替え sort
default は ascending sort
mysql> SELECT * FROM siteinfo ORDER BY lat; +-------+-------+--------+------+--------------------+ | code | lat | lon | hgt | name | +-------+-------+--------+------+--------------------+ | 47912 | 24.47 | 123.01 | 30 | "YONAGUNIJIMA" | | 47945 | 25.83 | 131.23 | 16 | "MINAMIDAITOJIMA" | | 47909 | 28.38 | 129.5 | 3 | "NAZE" | (中略) | 47423 | 42.32 | 140.97 | 3 | "MURORAN" | | 47417 | 42.92 | 143.21 | 38 | "OBIHIRO" | | 47406 | 43.95 | 141.63 | 23 | "RUMOI" | +-------+-------+--------+------+--------------------+ 31 rows in set (0.00 sec)
MySQL 自身の管理
バージョン確認
mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.10 | +-----------+ 1 row in set (0.00 sec)
MySQLサーバ状態確認
$ mysqladmin status -u root -p Enter password: Uptime: 309926 Threads: 1 Questions: 2847 Slow queries: 0 Opens: 293 Flush tables: 1 Open tables: 286 Queries per second avg: 0.009 $ mysqladmin extended-status -u root -p Enter password: +-----------------------------------------------+--------------------------------------------------+ | Variable_name | Value | +-----------------------------------------------+--------------------------------------------------+ | Aborted_clients | 0 | | Aborted_connects | 4 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | (Search by keywords: Show status begins with a character "A") mysql> SHOW STATUS LIKE 'A%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | Aborted_clients | 0 | | Aborted_connects | 1 | +------------------+-------+ 2 rows in set (0.00 sec) mysql> show variables; (MySQL 実行に関与する，多数のパラメータ．多すぎて書けない．）
バックアップ・リストア (dump/restore)
DB名 WordPressDB をバックアップ(ファイル名: WP_DB_${yyyymmdd}.sql)．DB管理者(wp_admin)名義でなく，root でも構わない．
$ mysqldump --single-transaction -u wp_admin -p WordPressDB > WP_DB_20170215.sql Enter password: (wp_admin のパスワード入力を求められる)
MySQL のDB全体をバックアップ(ファイル名: all_DB_${yyyymmdd}.sql)
$ mysqldump --single-transaction -u root -p -x --all-databases > all_DB_20170215.sql Enter password: (root のパスワード入力を求められる)
バックアップファイルからDB名 WordPressDB に復元(リストア)
$ mysql -u wp_admin -p WordPressDB < WP_DB_20170215.sql
MySQL プロンプトにおいて，バックアップファイルからDB名 WordPressDB に復元(リストア)
$ mysql -u wp_admin -p (パスワード入力) mysql> source WP_DB_20170215.sql
関数
文字列
文字列の置換，長さ，書式，大文字小文字変換，結合，などなど
文字数 CHAR_LENGTH('char')
長さ(バイト単位) LENGTH('char')
日本語（マルチバイト文字)だと， CHAR_LENGTH と LENGTH は一致しない．
挿入 INSERT('123456789', 3, 3, 'hoge')
* 上記の例だと，3文字目から3文字分(345)の代わりに hoge で置き換え．
置き換える文字数と置き換える文字列の長さが不一致だと，文字列長さが変わるの注意．
結合 CONCAT('12345','6789')
書式指定(小数点以下の桁数) FORMAT(12345.6789, 2)
小文字化 LOWER('ABCdef')
大文字化 UPPER('ABCdef')
置換 REPLACE('ABCdef', 'A', 'Z')
出現位置 INSTR('ABCdef', 'd')
LEFT('abcdefg',2)
RIGHT('abcdefg',2)
SUBSTR('abcdefg',3,2)
SUBSTRING_INDEX('http://hogehoge.or.jp/aaa/bbb/ccc','/',1)
指定区切り文字の指定回数目より前の文字列を返す
MariaDB [(none)]> select SUBSTRING_INDEX('http://hogehoge.or.jp/aaa/bbb/ccc','/',1);
+------------------------------------------------------------+
| SUBSTRING_INDEX('http://hogehoge.or.jp/aaa/bbb/ccc','/',1) |
+------------------------------------------------------------+
| http: |
+------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> select SUBSTRING_INDEX('http://hogehoge.or.jp/aaa/bbb/ccc','/',2);
+------------------------------------------------------------+
| SUBSTRING_INDEX('http://hogehoge.or.jp/aaa/bbb/ccc','/',2) |
+------------------------------------------------------------+
| http:/ |
+------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> select SUBSTRING_INDEX('http://hogehoge.or.jp/aaa/bbb/ccc','/',3);
+------------------------------------------------------------+
| SUBSTRING_INDEX('http://hogehoge.or.jp/aaa/bbb/ccc','/',3) |
+------------------------------------------------------------+
| http://hogehoge.or.jp |
+------------------------------------------------------------+
1 row in set (0.00 sec)
空白削除 (左側，右側，全て)
LTRIM(' abcdefg ')
RTRIM(' abcdefg ')
TRIM(' abcdefg ')
日付・時刻処理
更新履歴
| Date | Changes |
|---|---|
| 2021-12-14 | 拙いながらも，日常の研究業務でも MySQL を使い始めたので，書きためていた事柄を追記． 現時点では極めて単純な処理（日付に依存・参照する処理の類い．Fortran とか awk でやるには面倒なプロセス）だけだが， 使い込んでいけばより複雑な作業・高速化したい作業が出てくると予想． 現時点では，使用者が自分だけなので，動作速度・計算効率については無視する． |
| 2017-02-13 | DBやテーブル削除などの情報を追加． こんな基本的なことすら書いてなかったのに唖然． |
| 2016-02-14 | DBおよびテーブル操作関連のコマンド実行例を追加． 昔の記述事項を移し替え． |
| 2016-02-12 | PHP版の記述開始．MacOS 上での WordPress の稼働確認やらをやっていて，
MySQL の基本コマンドすら覚えてなかった．
2009年頃に Vine Linux 上でテストしていたが，そこでのメモは単純なテキスト記録．
Linux PC 上で記録していたため，モバイル環境(現在は MacOS)で記載事項を確認できなかった．
ネットワークにつながっていなくても，基本中の基本の事柄だけは調査出来るよう， 自分の手元にも情報を掲載することにした． |
| 2009-09-29 | MySQLの実行テストのメモ書きファイル，最古の日付け． かなり古いが，テストしただけでほとんど使っていなかった(確認日: 2017-02-13) |