MySQL

概要

小規模なWebアプリ用の基本構成は、OS標準パッケージ、最小権限のDBユーザー、HTTPS、バックアップ、更新管理を前提にします。

実施例

sudo apt update
sudo apt install apache2 mysql-server php libapache2-mod-php php-mysql
sudo systemctl enable --now apache2 mysql
sudo apachectl configtest

注意点

公開前にファイアウォール、DBの外部公開可否、管理画面のアクセス制限、復元テストまで確認します。

参考: 公式ドキュメント

概要

文字コード移行は必ずバックアップと検証環境で行います。現在はutf8mb3や曖昧なutf8指定ではなくutf8mb4を明示します。

実施例

mysqldump --single-transaction --routines appdb > appdb-before-charset.sql
mysql appdb -e "ALTER TABLE articles CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;"
mysql appdb -e "SHOW FULL COLUMNS FROM articles;"

注意点

実データがlatin1として誤認識されているケースは別手順です。少量の実データで往復検証してから本番へ進めます。

参考: 公式ドキュメント

概要

PerlではDBIの同一ハンドルでINSERTを実行し、使用中のドライバーが提供するlast_insert_idを利用します。SQL文字列の連結は避けます。

実施例

my $sth = $dbh->prepare('INSERT INTO entries (title) VALUES (?)');
$sth->execute($title);
my $id = $dbh->last_insert_id(undef, undef, 'entries', 'id');

注意点

last_insert_idの対応範囲はDBドライバーに依存します。対象DBでテストし、トランザクションの接続を途中で切り替えないでください。

参考: 公式ドキュメント

概要

phpMyAdminは管理用ツールです。インターネットへ無制限に公開せず、VPN、IP制限、多要素認証などで到達経路を限定します。

実施例

sudo apt update
sudo apt install phpmyadmin
sudo a2enconf phpmyadmin
sudo apachectl configtest
sudo systemctl reload apache2

注意点

rootでの直接ログインを避け、用途ごとに最小権限のDBユーザーを発行してください。

参考: 公式ドキュメント

GRANT USAGE ON `<データベース名>`.* TO '<ユーザー名>'@'%';
GRANT ALL PRIVILEGES ON `<データベース名>`.* TO '<ユーザー名>'@'%' WITH GRANT OPTION;

MySQL で、データベースをテキストデータにエクスポート（バックアップ）する方法は、mysqldump コマンドを使えばよい。以下のようにする。

mysqldump -u root -p your_database_name > your_database_name.sql

MySQL （まぁ、単なる SQL）で、データベース中の重複レコードを除いて検索するには distinct を使うのが定石だが、その逆に重複レコードのみ抽出・取得したい場合はどのようにしたらよいのだろう？結論は、以下のようにすればよい。

SELECT * FROM テーブルA
WHERE 列A in
(SELECT 列A FROM テーブルA GROUP BY 列A
HAVING COUNT(*) > 1);

基本的な手順は

1. 対象のテーブルから重複レコードを除いて新しいテーブルにすべてのレコードを重複なしで書き出す
2. もとのテーブルを削除
3. 新しいテーブル名を変更

しかしこれはあまりスマートな方法を思えなかったので、他に方法はないか、あちこち探し回ってみたものの、うまい方法が見つからなかった。 SQL 文としては、以下のようになる。field_1、field_2（増やしてもよい）は、重複に関係するフィールドである。

CREATE TABLE temp_table as SELECT * FROM main_table GROUP BY field_1, field_2;
DROP TABLE main_table;
ALTER TABLE temp_table RENAME TO main_table;

でテーブル名は変更できるのだが、テーブルが1つにとどまらない場合、テキストファイルでまずは1行に1テーブル名のリストを作って、後は秀丸エディタを使って置換する。

たとえば、Drupal でテーブル名にプリフィックス yourname_ を使っていたとして、 本来のテーブル名はそのままで、myname_ のプリフィックスに変えたい場合、秀丸エディタを使って置換するときは、正規表現を有効にして、置換前の文字列を