- 投稿日：2020-09-17T20:19:47+09:00
MySQLの設定で、許可するポートを変更する。
MySQLで許可するポートを変更する。
今回はポート33069を許可する。1. 設定
MySQLをインストールしたインスタンスにSSH接続する。
# vi /etc/mysql/mysql.conf.d/mysqld.cnf/etc/mysql/mysql.conf.d/mysqld.cnf# port = 3306この行を下記に変更する。
/etc/mysql/mysql.conf.d/mysqld.cnfport = 33069MySQLを再起動する。
sudo service mysql restart2. 確認
外部インスタンスからMySQLへの疎通を確認する。
外部インスタンスへSSH接続し、下記のコマンドを実行します。$ mysqladmin ping -h xxx.xxx.xxx.xxx --port 33069 -u username -p Enter password:MySQLのusernameのパスワードを入力してEnter。
mysqld is alive疎通できているならこんな風になる。
- 投稿日：2020-09-17T19:10:41+09:00
MySQLのタイムゾーンを日本時間に修正する（Herokuも対応できるのか？）
問題
HerokuにデプロイしたWebアプリ、MySQLのDBの時間がどうも日本時間と９時間ずれている...問題はここの設定のようです↓
mysql> show variables like "%time_zone%"; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | UTC | | time_zone | SYSTEM | +------------------+--------+本記事の読み方
結論から言うと、HerokuではClearDBのようなAdd-onのDBのタイムゾーンは直せないみたいです。詳細は後述します。
ただし、MySQL自体のタイムゾーンは変更できました。まず私が解決できた方法をシェアし、次にうまくいかなかった方法もメモしておきます。
この問題の解決に関して、またしてもYouTube万屋エンジニアチャンネル様の協力をいただきました。いつもありがとうございます。環境
MySQL, Heroku, ClearDB MySQL, MacOS
私の解決方法
公式ドキュメントを読むと、タイムゾーンテーブルのロードが必要で、そのためにmysql_tzinfo_to_sqlプログラムを使用するべきかな？と推測し下記コマンドを実行。
MySQL のインストール手順では、mysql データベース内にタイムゾーンテーブルを作成しますが、これらをロードしません。次の手順を使用して、手動でこれを行う必要があります。
（「10.6 MySQL Server でのタイムゾーンのサポート」より抜粋）$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql Warning: Unable to load '/usr/share/zoneinfo/+VERSION' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.↑Warningが出ている。/usr/share/zoneinfoのディレクトリには変化なし...でもOKらしい。
↓MySQLにrootユーザーでログインし、mysql.time_zoneテーブルを呼び込むと縦長にどかっとNだらけのテーブルが出てきます。これがタイムゾーンテーブルのデータらしいです。
$ mysql -u root （中略） mysql> select * from mysql.time_zone; +--------------+------------------+ | Time_zone_id | Use_leap_seconds | +--------------+------------------+ | 1 | N | | 2 | N | | 3 | N | | 4 | N | 中略 | 593 | N | | 594 | N | | 595 | N | | 596 | N | +--------------+------------------+ 596 rows in set (0.03 sec)文字コードの設定の時と同様、MySQLの設定ファイル（/usr/local/etc/my.cnf）を編集します。MySQL設定ファイルに関しては、その記事の中で詳しく書いたのでよろしければご参照ください→#MySQLの設定ファイルを探せ！
$ vi /usr/local/etc/my.cnfでファイルを開き、iで編集開始、下記のように[mysqld]の箇所に追記。終了後、escボタン>:wq（保存して終了）>Enterボタン。
ちなみにviとはUNIX系OSで使われるテキストエディタの一種です。/usr/local/etc/my.cnf[mysqld] default-time-zone = 'Asia/Tokyo'↓MySQLを再起動。sudoはなくてもいいのかもです。（sudo = superuser do, substitute user doという意味のコマンド。ログインしているユーザーが別の管理者権限で実行すること）
$ sudo mysql.server stop Password:→パスワード入力 Shutting down MySQL .. SUCCESS! $ sudo mysql.server start Starting MySQL .. SUCCESS!↓これでもう一度rootユーザーでログインして設定を確認すると...
mysql> show variables like "%time_zone%"; +------------------+------------+ | Variable_name | Value | +------------------+------------+ | system_time_zone | JST | | time_zone | Asia/Tokyo | +------------------+------------+↑きちんと設定が変更されています！しかしながら、HerokuのMySQL（ClearDB）は変わってませんでした。
HerokuのClearDB MySQLは変更できず
文字コードの設定の時は、ClearDB MySQLもきちんと設定が変わったのですが、タイムゾーンは直りませんでした。
ひょっとしたら、先にタイムゾーンテーブルのロードをした時にrootと指定しているからか？と思い、rootをClearDBのDB_USERNAMEに変更したり、-pオプション、-hオプションを加えたりしてトライしましたがダメでした。$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u 私のDB_USERNAME mysql Warning: Unable to load '/usr/share/zoneinfo/+VERSION' as time zone. Skipping it. ERROR 1045 (28000): Access denied for user '私のDB_USERNAME'@'localhost' (using password: NO) $ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u 私のDB_USERNAME -p mysql Warning: Unable to load '/usr/share/zoneinfo/+VERSION' as time zone. Skipping it. Enter password: ERROR 1045 (28000): Access denied for user '私のDB_USERNAME'@'localhost' (using password: YES) $ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u 私のDB_USERNAME -h 私のDB_HOST -p mysql Warning: Unable to load '/usr/share/zoneinfo/+VERSION' as time zone. Skipping it. Enter password: ERROR 1044 (42000): Access denied for user '私のDB_USERNAME'@'%' to database 'mysql'どうしても時間を直さないといけない場合は、プログラムの方でなんとかする方が良いみたいです。
うまくいかなかった方法、やらなくてもよかったこと
ここからは自分用のメモという意味合いが強いです。
タイムゾーンの変更の仕方をネット上で探すと下記コマンドがよく提示されていますが、私の場合はやってもエラーになり、やる必要もありませんでした。$ /usr/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo > ~/timezone.sql $ mysql -u root -p -Dmysql < ~/timezone.sql一行目のコマンドでダメでした...↓
$ /usr/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo > ~/timezone.sql -bash: /usr/bin/mysql_tzinfo_to_sql: No such file or directoryそれもそのはず指定したディレクトリにそんなファイルはなかったです↓
$ cat /usr/bin/mysql_tzinfo_to_sql cat: mysql_tzinfo_to_sql: No such file or directoryご参考にzoneinfoの様子↓
$ ls /usr/share/zoneinfo +VERSION Australia EET Factory Greenwich Jamaica Mexico Poland US leapseconds Africa Brazil EST GB HST Japan NZ Portugal UTC posixrules America CET EST5EDT GB-Eire Hongkong Kwajalein NZ-CHAT ROC Universal zone.tab Antarctica CST6CDT Egypt GMT Iceland Libya Navajo ROK W-SU Arctic Canada Eire GMT+0 Indian MET PRC Singapore WET Asia Chile Etc GMT-0 Iran MST PST8PDT Turkey Zulu Atlantic Cuba Europe GMT0 Israel MST7MDT Pacific UCT iso3166.tabこちらはファイルがあったが中身は空↓
$ cat timezone.sqlそういや以前別件MySQLの中を漁っていたとき、mysql_tzinfo_to_sqlというファイルがここにありました↓
9 10 19:01 mysql_tzinfo_to_sql -> ../Cellar/mysql/8.0.21_1/bin/mysql_tzinfo_to_sql 9 10 19:01 mysql_tzinfo_to_sql -> ../Cellar/mysql/8.0.21_1/bin/mysql_tzinfo_to_sql cd /usr/local/Cellar/mysql/8.0.21_1最後に
もし上記のようにやってもうまくいかなかったり、ご指摘あればぜひコメントください。
- 投稿日：2020-09-17T19:08:13+09:00
テーブルにコメントを付ける
- 投稿日：2020-09-17T18:37:13+09:00
外部インスタンスからMySQLへの疎通を確認する。
$ mysqladmin ping -h xxx.xxx.xxx.xxx --port 33069 -u username -p Enter password:MySQLのusernameのパスワードを入力してEnter。（33069はポートです）
mysqld is alive疎通できているならこんな風になる。
- 投稿日：2020-09-17T17:35:41+09:00
WordPressをSQLで色々変換するクエリ
めったにないのですが、たまにあるのが投稿タイプの変更だったり、一括でカテゴリーをつけたりする事がありますね。
豆腐を買い忘れたくらいの感じでそういうオーダーが来ますよね。そんな時にsqlから一括更新すると楽なので、いくつかメモしておきます。
自分はphpMyAdminのSQLに貼り付けて更新してます。（変更前にバックアップ必須です）カスタム投稿タイプの変更
Use SQL Query In phpMyAdmin To Change Custom Post Types in WordPress
UPDATE `wp_posts` SET `post_type` = 'photos' WHERE `post_type` = 'members';投稿タイプ「members」を「photos」へ変更します。通常投稿から変更する場合は、whereのところを「post」ですね。
taxonomy名の変更
https://wordpress.stackexchange.com/questions/1037/renaming-custom-post-types-and-taxonomies
UPDATE `wp_term_taxonomy` SET `taxonomy` = 'new_taxonomy_name' WHERE `taxonomy` = 'old_taxonomy_name';こちらはタクソノミーの変更です。通常投稿のカテゴリーからカスタムタクソノミーへ変更する時に使います。カスタム投稿タイプの変更とセットでよく利用しますね。
特定のカテゴリーに属する投稿を任意のカスタム投稿タイプに変換する SQL
https://gist.github.com/wokamoto/4159068
update wp_posts set post_type = 'faq' where post_type = 'post' and exists (select object_id from wp_term_taxonomy as t inner join wp_term_relationships as r on t.term_taxonomy_id = r.term_taxonomy_id where t.term_id = 2 and r.object_id = wp_posts.ID);上記例は、カテゴリーID「2」に属する通常投稿を、カスタム投稿タイプ「faq」へ変更します。
この後、term_id 2のtaxonomyをcategoryからfaq_categoryとかに変更します。上記の反対（任意のカスタム投稿タイプを通常投稿、特定のカテゴリーに変換）
INSERT INTO wp_term_relationships SELECT id,7,0 FROM wp_posts WHERE `post_type` = 'announcement'; UPDATE `wp_posts` SET `post_type` = 'post' WHERE `post_type` = 'announcement';最初にカテゴリーをセットして、そこから変換します。
カテゴリーをセットしてある場合は、先ほどのを利用出来ます。
- 投稿日：2020-09-17T12:39:21+09:00
phpMyAdminが対応するPHPとMySQLのバージョン一覧
phpMyAdmin PHP MySQL 5.0.2 7.1 and newer 5.5 and newer 5.0.1 7.1 and newer 5.5 and newer 5.0.0 7.1 and newer 5.5 and newer 5.0.0-rc1 7.1 and newer 5.5 and newer 5.0.0-alpha1 7.1 and newer 5.5 and newer 4.9.5 5.5 to 7.4 5.5 and newer 4.9.4 5.5 to 7.4 5.5 and newer 4.9.3 5.5 to 7.4 5.5 and newer 4.9.2 5.5 to 7.4 5.5 and newer 4.9.1 5.5 to 7.4 5.5 and newer 4.9.0.1 5.5 to 7.4 5.5 and newer 4.9.0 5.5 to 7.4 5.5 and newer 4.8.5 5.5 to 7.2 5.5 and newer 4.8.4 5.5 to 7.2 5.5 and newer 4.8.3 5.5 to 7.2 5.5 and newer 4.8.2 5.5 to 7.2 5.5 and newer 4.8.1 5.5 to 7.2 5.5 and newer 4.8.0.1 5.5 to 7.2 5.5 and newer 4.8.0 5.5 to 7.2 5.5 and newer 4.8.0-rc1 5.5 to 7.2 5.5 and newer 4.8.0-alpha1 5.5 to 7.2 5.5 and newer 4.7.9 5.5 to 7.2 5.5 and newer 4.7.8 5.5 to 7.2 5.5 and newer 4.7.7 5.5 to 7.2 5.5 and newer 4.7.6 5.5 to 7.2 5.5 and newer 4.7.5 5.5 to 7.2 5.5 and newer 4.7.4 5.5 to 7.2 5.5 and newer 4.7.3 5.5 to 7.1 5.5 and newer 4.7.2 5.5 to 7.1 5.5 and newer 4.7.1 5.5 to 7.1 5.5 and newer 4.7.0 5.5 to 7.1 5.5 and newer 4.7.0-rc1 5.5 to 7.1 5.5 and newer 4.7.0-beta1 5.5 to 7.1 5.5 and newer 4.6.6 5.5 to 7.1 5.5 and newer 4.6.5.2 5.5 to 7.1 5.5 and newer 4.6.5.1 5.5 to 7.1 5.5 and newer 4.6.5 5.5 to 7.1 5.5 and newer 4.6.4 5.5 to 7.1 5.5 and newer 4.6.3 5.5 to 7.1 5.5 and newer 4.6.2 5.5 to 7.1 5.5 and newer 4.6.1 5.5 to 7.1 5.5 and newer 4.6.0 5.5 to 7.1 5.5 and newer 4.6.0-rc2 5.5 to 7.1 5.5 and newer 4.6.0-rc1 5.5 to 7.1 5.5 and newer 4.6.0-alpha1 5.5 to 7.1 5.5 and newer 4.5.5.1 5.5 to 7.0 5.5 4.5.5 5.5 to 7.0 5.5 4.5.4.1 5.5 to 7.0 5.5 4.5.4 5.5 to 7.0 5.5 4.5.3.1 5.5 to 7.0 5.5 4.5.3 5.5 to 7.0 5.5 4.5.2 5.5 to 7.0 5.5 4.5.1 5.5 to 7.0 5.5 4.5.0.2 5.5 to 7.0 5.5 4.5.0.1 5.5 to 7.0 5.5 4.5.0 5.5 to 7.0 5.5 4.4.15.10 5.3.7 to 7.0 5.5 4.4.15.9 5.3.7 to 7.0 5.5 4.4.15.8 5.3.7 to 7.0 5.5 4.4.15.7 5.3.7 to 7.0 5.5 4.4.15.6 5.3.7 to 7.0 5.5 4.4.15.5 5.3.7 to 7.0 5.5 4.4.15.4 5.3.7 to 7.0 5.5 4.4.15.3 5.3.7 to 7.0 5.5 4.4.15.2 5.3.7 to 7.0 5.5 4.4.15.1 5.3.7 to 7.0 5.5 4.4.15 5.3.7 to 7.0 5.5 4.4.14.1 5.3.7 to 7.0 5.5 4.4.14 5.3.7 to 7.0 5.5 4.4.13.1 5.3.7 to 7.0 5.5 4.4.13 5.3.7 to 7.0 5.5 4.4.12 5.3.7 to 7.0 5.5 4.4.11 5.3.7 to 7.0 5.5 4.4.10 5.3.7 to 7.0 5.5 4.4.9 5.3.7 to 7.0 5.5 4.4.8 5.3.7 to 7.0 5.5 4.4.7 5.3.7 to 7.0 5.5 4.4.6.1 5.3.7 to 7.0 5.5 4.4.6 5.3.7 to 7.0 5.5 4.4.5 5.3.7 to 7.0 5.5 4.4.4 5.3.7 to 7.0 5.5 4.4.3 5.3.7 to 7.0 5.5 4.4.2 5.3.7 to 7.0 5.5 4.4.1.1 5.3.7 to 7.0 5.5 4.4.1 5.3.7 to 7.0 5.5 4.4.0 5.3.7 to 7.0 5.5 4.3.13.3 5.3 5.5 4.3.13.2 5.3 5.5 4.3.13.1 5.3 5.5 4.3.13 5.3 5.5 4.3.12 5.3 5.5 4.3.11.1 5.3 5.5 4.3.11 5.3 5.5 4.3.10 5.3 5.5 4.3.9 5.3 5.5 4.3.8 5.3 5.5 4.3.7 5.3 5.5 4.3.6 5.3 5.5 4.3.5 5.3 5.5 4.3.4 5.3 5.5 4.3.3 5.3 5.5 4.3.2 5.3 5.5 4.3.1 5.3 5.5 4.3.0 5.3 5.5 4.2.13.3 5.3 5.5 4.2.13.2 5.3 5.5 4.2.13.1 5.3 5.5 4.2.13 5.3 5.5 4.2.12 5.3 5.5 4.2.11 5.3 5.5 4.2.10.1 5.3 5.5 4.2.10 5.3 5.5 4.2.9.1 5.3 5.5 4.2.9 5.3 5.5 4.2.8.1 5.3 5.5 4.2.8 5.3 5.5 4.2.7.1 5.3 5.5 4.2.7 5.3 5.5 4.2.6 5.3 5.5 4.2.5 5.3 5.5 4.2.4 5.3 5.5 4.2.3 5.3 5.5 4.2.2 5.3 5.5 4.2.1 5.3 5.5 4.2.0 5.3 5.5 4.1.14.8 5.3 5.5 4.1.14.7 5.3 5.5 4.1.14.6 5.3 5.5 4.1.14.5 5.3 5.5 4.1.14.4 5.3 5.5 4.1.14.3 5.3 5.5 4.1.14.2 5.3 5.5 4.1.14.1 5.3 5.5 4.1.14 5.3 5.5 4.1.13 5.3 5.5 4.1.12 5.3 5.5 4.1.11 5.3 5.5 4.1.10 5.3 5.5 4.1.9 5.3 5.5 4.1.8 5.3 5.5 4.1.7 5.3 5.5 4.1.6 5.3 5.5 4.1.5 5.3 5.5 4.1.4 5.3 5.5 4.1.3 5.3 5.5 4.1.2 5.3 5.5 4.1.1 5.3 5.5 4.1.0 5.3 5.5 4.0.10.20 5.2 (Does not support 5.5 or newer) 5 4.0.10.19 5.2 (Does not support 5.5 or newer) 5 4.0.10.18 5.2 (Does not support 5.5 or newer) 5 4.0.10.17 5.2 (Does not support 5.5 or newer) 5 4.0.10.16 5.2 (Does not support 5.5 or newer) 5 4.0.10.15 5.2 (Does not support 5.5 or newer) 5 4.0.10.14 5.2 (Does not support 5.5 or newer) 5 4.0.10.13 5.2 (Does not support 5.5 or newer) 5 4.0.10.12 5.2 (Does not support 5.5 or newer) 5 4.0.10.11 5.2 (Does not support 5.5 or newer) 5 4.0.10.10 5.2 (Does not support 5.5 or newer) 5 4.0.10.9 5.2 (Does not support 5.5 or newer) 5 4.0.10.8 5.2 (Does not support 5.5 or newer) 5 4.0.10.7 5.2 (Does not support 5.5 or newer) 5 4.0.10.6 5.2 (Does not support 5.5 or newer) 5 4.0.10.5 5.2 (Does not support 5.5 or newer) 5 4.0.10.4 5.2 (Does not support 5.5 or newer) 5 4.0.10.3 5.2 (Does not support 5.5 or newer) 5 4.0.10.2 5.2 (Does not support 5.5 or newer) 5 4.0.10.1 5.2 (Does not support 5.5 or newer) 5 4.0.10 5.2 (Does not support 5.5 or newer) 5 4.0.9 5.2 (Does not support 5.5 or newer) 5 4.0.8 5.2 (Does not support 5.5 or newer) 5 4.0.7 5.2 (Does not support 5.5 or newer) 5 4.0.6 5.2 (Does not support 5.5 or newer) 5 4.0.5 5.2 (Does not support 5.5 or newer) 5 4.0.4.2 5.2 (Does not support 5.5 or newer) 5 4.0.4.1 5.2 (Does not support 5.5 or newer) 5 4.0.4 5.2 (Does not support 5.5 or newer) 5 4.0.3 5.2 (Does not support 5.5 or newer) 5 4.0.2 5.2 (Does not support 5.5 or newer) 5 4.0.1 5.2 (Does not support 5.5 or newer) 5 4.0.0 5.2 (Does not support 5.5 or newer) 5 3.5.8.2 5.2 5 3.5.8.1 5.2 5 3.5.8 5.2 5 3.5.7 5.2 5 3.5.6 5.2 5 3.5.5 5.2 5 3.5.4 5.2 5 3.5.3 5.2 5 3.5.2.2 5.2 5 3.5.2.1 5.2 5 3.5.2 5.2 5 3.5.1 5.2 5 3.5.0 5.2 5 3.4.11.1 5.2 5 3.4.11 5.2 5 3.4.10.2 5.2 5 3.4.10.1 5.2 5 3.4.10 5.2 5 3.4.9 5.2 5 3.4.8 5.2 5 3.4.7.1 5.2 5 3.4.7 5.2 5 3.4.6 5.2 5 3.4.5 5.2 5 3.4.4 5.2 5 3.4.3.2 5.2 5 3.4.3.1 5.2 5 3.4.3 5.2 5 3.4.2 5.2 5 3.4.1 5.2 5 3.4.0 5.2 5 3.3.10.5 5.2 5 3.3.10.4 5.2 5 3.3.10.3 5.2 5 3.3.10.2 5.2 5 3.3.10.1 5.2 5 3.3.10 5.2 5 3.3.9.2 5.2 5 3.3.9.1 5.2 5 3.3.9 5.2 5 3.3.8.1 5.2 5 3.3.8 5.2 5 3.3.7 5.2 5 3.3.6 5.2 5 3.3.5.1 5.2 5 3.3.0 5.2 5 3.2.0 5.2 5 3.1.0 5.2 5 3.0.0 5.2 5 2.11.11.3 4+ 3+ 2.11.11.2 4+ 3+ 2.11.11.1 4+ 3+ 2.11.11 4+ 3+ 2.11.10.1 4+ 3+ 2.10.3 4+ 3+ 2.10.2 4+ 3+ 2.9.0 4+ 3+ 2.8.0 4+ 3+ 2.7.0 4+ 3+ 2.6.0 4+ 3+ 2.5.0 4+ 3+ 2.4.0 4+ 3+ 2.3.0 4+ 3+ 2.2.0 4+ 3+ 2.1.0 4+ 3+ 2.0.5 4+ 3+ 1.3.1 1.3.0 1.2.0 1.1.0 0.9.0
- 投稿日：2020-09-17T11:19:01+09:00
「知らぬ間によくわからないDBスキーマが追加されてるんだけど・・・」みたいなレガシー環境でDBテーブル定義をバージョン管理したい
やりたかったこと
- DBテーブル定義をバージョン管理したい
- 修正用をレビューしたい
（今時の新規サービスですと少ないかと思いますが）
テーブル定義のマスターがDBにあるのみで定義の変更を各々の開発者が行う（他の開発者は認識しづらい）という運用になってしまっているサービスだと便利かと思います※MySQL系でのみ動作確認済みです、最低限になるので必要に応じてカスタマイズしてください
エクスポートコード
こちらは初回のみ実行することを想定してます
以後のテーブル定義変更は、出力されたファイルを変更していく形です"""export_database_schema.py データベースからCREATE TABLE文をエクスポートするのに利用します optional arguments: -h, --help show this help message and exit --user USER データベースのユーザ名 --password PASSWORD データベースのパスワード --host HOST データベースのホスト名 --port PORT データベースのポート番号 --charset CHARSET データベースの文字コード --target TARGET [TARGET ...] エクスポート対象のDBリスト(スペース区切り） """ import os import sys import shutil import pymysql import argparse ROOT_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) # 引数の設定 parser = argparse.ArgumentParser() parser.add_argument("--user", help="データベースのユーザ名", default="root") parser.add_argument("--password", help="データベースのパスワード", default="password") parser.add_argument("--host", help="データベースのホスト名", default="127.0.0.1") parser.add_argument("--port", help="データベースのポート番号", default=3306) parser.add_argument("--charset", help="データベースの文字コード", default="utf8mb4") parser.add_argument("--target", help="エクスポート対象のDBリスト(スペース区切り）", default=["users", "shops"], nargs="+") args = parser.parse_args() print("Connection: mysql://%s:%s@%s:%s?charset=%s" % ( args.user, args.password, args.host, args.port, args.charset)) con = pymysql.connect( user=args.user, password=args.password, host=args.host, port=args.port, charset=args.charset) with con.cursor() as cursor: sql = "SHOW DATABASES" cursor.execute(sql) databases = cursor.fetchall() # 指定されたDBが存在する場合のみエクスポート処理を行う for d in databases: if d[0] not in args.target: continue # DBごとにディレクトリ作成(存在していたら一度削除) currentDirName = "%s/%s" % (ROOT_DIR, d[0]) if os.path.isdir(currentDirName): shutil.rmtree(currentDirName) os.mkdir(currentDirName) dirname = "%s/schemas" % (currentDirName) os.mkdir(dirname) print("Export database: ", d[0]) sql = "USE %s" % d[0] cursor.execute(sql) sql = "SHOW TABLES" cursor.execute(sql) tables = cursor.fetchall() for t in tables: print("\tExporting ", t[0]) sql = "SHOW CREATE TABLE %s" % t cursor.execute(sql) schema = cursor.fetchone() filename = "%s/%s.sql" % (dirname, t[0]) with open(filename, mode="w") as f: f.write(schema[1] + ";\n\n") con.close()例として、
python export_database_schema.py --target users shops
を実行することで下記のようなディレクトリ構成でCREATE文ファイルが設置されます├── users │ └── schemas # users DB内のテーブルのCREATE文が格納 └── shops └── schemas # shops DB内のテーブルのCREATE文が格納インポートコード
#!/bin/bash usage() { cat << EOS Usage: $0 [option] -t TARGET 対象のテーブル定義ディレクトリ -d DATABASE インポート先データベース名 -H HOST データベースのホスト名 -u USER データベースのユーザ名 -p PASSWORD データベースのパスワード -P PORT データベースのポート番号 -y 実行確認をプロンプト省略 EOS exit 1 } TARGET="" DATABASE_NAME="" HOST="127.0.0.1" USER="root" PASSWORD="" PORT="3306" AUTO_YES=false while getopts d:t:H:u:p:P:yh OPT do case $OPT in d) DATABASE_NAME=$OPTARG ;; t) TARGET=$OPTARG ;; H) HOST=$OPTARG ;; u) USER=$OPTARG ;; p) PASSWORD=$OPTARG ;; P) PORT=$OPTARG ;; y) AUTO_YES=true ;; h) usage ;; esac done ROOT_PATH=$(dirname "$(cd "$(dirname "${BASH_SOURCE:-$0}")" && pwd)") if [ "${DATABASE_NAME}" == "" ]; then echo "インポート対象のデータベースを指定してください。" echo " ./$0 -d DATABASE_NAME" exit 1 fi if [ ! -d $ROOT_PATH/$TARGET/schemas ]; then echo "指定されたテーブル定義ディレクトリが存在しません" echo "${ROOT_PATH}下にディレクトリが存在するか確認してください" echo " ./$0 -t TARGET" exit 1 fi echo "パラメータ -------------------------" echo "TARGET: $TARGET" echo "DATABASE_NAME: $DATABASE_NAME" echo "HOST: $HOST" echo "USER: $USER" echo "PASSWORD: $PASSWORD" echo "PORT: $PORT" echo "------------------------------------" # 確認 if ! "${AUTO_YES}"; then read -p "\"$DATABASE_NAME\" データベースを初期化していいですか？(y/N)" yn case "$yn" in [yY]*) ;; *) exit 1;; esac fi echo "データベース初期化中..." CMD="mysql -h$HOST" if [ "$USER" != "" ]; then CMD="$CMD -u$USER" fi if [ "$PASSWORD" != "" ]; then CMD="$CMD -p$PASSWORD" fi if [ "$PORT" != "" ]; then CMD="$CMD -P$PORT" fi echo 'SET FOREIGN_KEY_CHECKS = 0;' > "${ROOT_PATH}/tmp.sql" cat $ROOT_PATH/$TARGET/schemas/*.sql >> "${ROOT_PATH}/tmp.sql" `$CMD -e "set FOREIGN_KEY_CHECKS=0; DROP DATABASE IF EXISTS $DATABASE_NAME;"` `$CMD -e "CREATE DATABASE $DATABASE_NAME;"` `$CMD -t $DATABASE_NAME < "${ROOT_PATH}/tmp.sql"` rm "${ROOT_PATH}/tmp.sql" echo "完了" # テスト用に初期データを入れたい場合は下記のような形でファイルを配置し、一緒にインポートする # echo "初期データ作成中..." # `$CMD -t $DATABASE_NAME < "${ROOT_PATH}/${TARGET}/testdata/dump.sql"` # echo "完了" exit 0インポートは
例として、./scripts/import_database_schema.sh -t users -d users -y
という形で行います。実運用の際は、下記のようにテストデータを設置し、インポート時にテストデータも一緒にインポートするような形にしてgithub actionsでテストを回すような運用をしています
├── users │ ├── testdata # users DBのテストデータを格納 │ └── schemas # users DB内のテーブルのCREATE文が格納 └── shops ├── testdata # shops DBのテストデータを格納 └── schemas # shops DB内のテーブルのCREATE文が格納
- 投稿日：2020-09-17T09:05:15+09:00
MySQL入門！データ型 編
MySQLが扱えるデータ型（一部）
整数
- TINYINT ⇒ -128 ~ +127
- INT ⇒ -21億 ~ +21億
- BIGINT ⇒ -922京 ~ +922京
マイナスの値を扱うことがない場合は、データ型に
UNSIGNEDキーワードを付けてあげることによってプラスの値しか扱わずなおかつ、扱える数字の幅が広くなる。
例
TINYINT UNSIGNED ⇒ 0 ~ 255実数
- DECIMAL ⇒ 固定小数点
- FLOAT ⇒ 浮動小数点
- DOUBLE ⇒ 浮動小数点（高精度）
文字列
- CHAR ⇒ 0 ~ 255文字（主に商品コードなど固定長のデータに使用）
- VARCHAR ⇒ 0 ~ 65535文字（文字数がバラバラになるようなデータに使用）
- TEXT ⇒ 65535文字以上（VARCHARよりも長くなるようなデータに使用）
ENUM ⇒ 特定の文字列の内、1つだけの値を格納したいときに使用
CREATE TABLE posts ( greeting ENUM("Hello", "Bye", "Hey") ); INSERT INTO posts (greeting) VALUES ("Hello"), ("Bye"), ("Hey");ちなみに、インデックス番号を指定しても値の取り出しは可能
CREATE TABLE posts ( greeting ENUM("Hello", "Bye", "Hey") ); -- インデックス番号でも取り出せる INSERT INTO posts (greeting) VALUES (1), (2), (3);
SET ⇒ 特定の文字列の内、複数の値を格納したいときに使用
CREATE TABLE posts ( greetings SET("Hello", "Bye", "Hey") ); INSERT INTO posts (greetings) VALUES ("Hello,Bye"), ("Hey"), ("Hey,Hello");わかりやすいようにフィールド名を複数形に
データを挿入する際、,以外に空白などを入れてはいけないので注意SET型は内部的に値を数値で管理していて、その数値を利用して値を取り出せる
CREATE TABLE posts ( greetings SET("Hello", "Bye", "Hey") -- 左から2の0乗,2の1乗,2の2乗... ); -- INSERT INTO posts (greetings) VALUES -- ("Hello,Bye"), -- ("Hey"), -- ("Hey,Hello"); -- 2つ並んだ値はそれぞれが持つ内部数字を足す INSERT INTO posts (greetings) VALUES (3), (4), (5);真偽値
BOOL ⇒ TRUE / FALSE（1がTRUE、0がFALSEで表される）
CREATE TABLE posts ( boolean BOOL ); INSERT INTO posts (boolean) VALUES (TRUE), (FALSE), (0);日時
- DATE ⇒「日付」を扱いたいときに使用
- TIME ⇒「時間」を扱いたいときに使用
DATETIME ⇒「日時」を扱いたいときに使用
CREATE TABLE posts ( created DATETIME ); INSERT INTO posts (created) VALUES ("2020-11-11 12:22:03"), ("2020-03-03"), (NOW());終わりに
データ型はここで紹介した以外にもあります。
わかりやすくまとまっているサイトがあるので、こちらもぜひ参考にしてみてください。
DBOnline