データーベース管理 Linux

この記事のポイント

・vacuumdbはVACUUM SQLをCLIから実行するPostgreSQL付属のコマンド
・--fullオプションでテーブルを書き直し、OS側にディスク領域を返却できる
・--allで全データベースを一括クリーンアップし、メンテナンスを効率化できる
・--jobs=Nで複数テーブルを並列処理し、大規模DBの処理時間を短縮できる

この記事のポイント

・MySQLのInnoDBはPK順にデータを物理配置するクラスタードインデックス構造
・PostgreSQLはヒープ構造でdead tupleが蓄積するためVACUUMが運用上の必須コスト
・ロック競合の調査はMySQL: SHOW ENGINE INNODB STATUS / PostgreSQL: pg_locksで行う
・書き込み多め・PK検索中心→MySQL、複雑クエリ・整合性重視→PostgreSQLが実務の目安

この記事のポイント

・xtrabackup --incremental でフル取得後の差分だけを効率よく取得できる
・差分は LSN（ログシーケンス番号）を基点に取得するので整合性が保証される
・リストアは「フル適用 → 差分順に適用 → prepare → コピーバック」の4ステップ
・週1フル＋日次差分の組み合わせがバックアップ時間と復元容易性のバランスが取れる

この記事のポイント

・mariadb-dump は MariaDB 11 の正式名称で、mysqldump はシンボリックリンクに格下げされた
・InnoDB テーブルは --single-transaction を付けることで無停止バックアップが取れる
・--events --routines を忘れるとストアドプロシージャとイベントがダンプに含まれない
・リストアは mariadb コマンドにダンプファイルをリダイレクトするだけで完了する

この記事のポイント

・公式tarballを /usr/local/mariadb-XX.X.X/ に展開しシンボリックリンクで切り替える設計
・mariadb-install-db でデータディレクトリを初期化し、systemd Unit ファイルで起動管理する
・バージョン更新はシンボリックリンクの張り替えだけ、ロールバックも同様に即座にできる
・パッケージマネージャー不使用なので複数バージョン共存・厳密なバージョン固定が可能

この記事のポイント

・mysql -V はサービス停止中でもMariaDBバージョンを即確認できる
・mysqladmin version はバージョン＋サーバー稼働時間も同時に取得できる
・SHOW VARIABLES LIKE 'version%'; でSSLライブラリ等のコンパイル詳細も確認できる
・rpm -q または dpkg -l でOSパッケージ管理側のバージョンも確認できる

この記事のポイント

・psql --versionとSELECT version()でpostgresqlバージョン確認が即座にできる
・rpm -qとdpkg -lでパッケージレベルのバージョンも把握できる
・SSHループスクリプトで複数サーバーを一括取得してCSVに記録できる
・cronで定期実行しバージョン変化を自動検知してメール通知できる

この記事のポイント

・pg_stat_statementsはPostgreSQLの実行SQLを正規化して統計集計する公式エクステンション
・postgresql.confに1行追加して再起動するだけで有効化でき、追加パッケージは不要
・累積実行時間・平均実行時間・バッファキャッシュヒット率でクエリをランキングできる
・pg_stat_statements_reset()で統計をリセットし、任意のタイミングから計測をやり直せる

この記事のポイント

・pgBouncerをアプリとPostgreSQLの間に挟むことで、PostgreSQL本体の同時接続数を削減できる
・pgbouncer.iniの[databases]と[pgbouncer]セクションを設定し、userlist.txtでパスワードを登録する
・pool_modeはtransactionが最も効率的だが、prepared statementの扱いに注意が必要
・SHOW STATSとSHOW POOLSでリアルタイムに接続状況を監視できる

この記事のポイント

・WALアーカイブを有効にして変更ログを継続保存する
・pg_basebackupで定期的なベースバックアップを取得する
・recovery.signalを置き、recovery_target_timeで任意時点に復元
・pg_stat_archiverでアーカイブの失敗を常時監視する

この記事のポイント

・MySQLイベントスケジューラーはcronを使わずDBの中で定期処理を自動化できる
・event_scheduler=ONをmy.cnfに設定して永続化することが前提
・CREATE EVENTでSQL文を定期実行し、SHOW EVENTSで状態を確認できる
・実行ログはperformance_schemaのevents_statements_historyで追うことができる

この記事のポイント

・SET GLOBAL general_log = ONで即時有効化・OFFで即時無効化できる
・出力先はファイルとテーブル（mysql.general_log）の2択で切り替え可能
・本番DBでは短時間だけグローバルONにし、調査後すぐOFFに戻すのが安全
・常時ONにする場合はlogrotateとFLUSH GENERAL LOGSでローテーション必須

この記事のポイント

・SHOW ENGINE INNODB STATUSのLATEST DETECTED DEADLOCKでデッドロックの詳細を確認できる
・innodb_print_all_deadlocks=ONでエラーログに全件を記録して調査履歴を残せる
・異なる順序でのロック取得・ギャップロック・外部キーがデッドロックの3大原因
・トランザクション内のロック順序を統一し短く保つことが根本的な再発防止策になる

この記事のポイント

・binlog_expire_logs_secondsで古いバイナリログを自動削除できる
・PURGE BINARY LOGSで今すぐ手動削除する方法も解説
・レプリケーション中は削除前にSHOW REPLICA STATUSで確認が必須
・SHOW BINARY LOGSでサイズと本数を把握してから操作する

この記事のポイント

・pg_database_size()関数でDBごとのサイズをバイト単位で取得できる
・psqlの\l+でSQL不要でデータベース一覧＋サイズをまとめて確認できる
・pg_size_pretty()と組み合わせると「1547 MB」形式で読みやすくなる
・template0/template1はシステム用DBなので監視対象から除外してよい

この記事のポイント

・bind-address を 0.0.0.0 に変更するとリモート接続を受け付けるようになる
・firewalld/ufw でポート 3306 を開放しMariaDBを再起動する
・GRANT 文でリモートホストへのユーザー権限を付与する（接続元IP制限推奨）
・ポート番号を変更する場合はSELinuxのラベル追加が必要（RHEL系）

この記事のポイント

・mysqldumpのラッパースクリプトをcronに登録して完全無人化できる
・日次7世代・週次4世代の3層ローテーションでディスク枯渇を防ぐ
・復元テストをcronに組み込むことで「戻せる」を定期的に証明できる
・スクリプト失敗時のメール通知設定でサイレント障害を防ぐ

この記事のポイント

・CREATE ROLE 名前 LOGIN PASSWORD 'パスワード' でアプリ専用ユーザーを作成できる
・権限はDB接続→スキーマ使用→テーブル操作の3段階で付与する必要がある
・GRANT ON ALL TABLESで既存テーブルを一括付与、DEFAULT PRIVILEGESで新規テーブルに自動付与
・permission denied for tableはスキーマのUSAGE権限が抜けているケースが多い

この記事のポイント

・mysql --version はクライアント、mysqladmin version はサーバーのバージョンを示す
・MySQL 8.0 は 2026年4月にEOL済み。今から採用するなら 8.4 LTS が第一選択
・安定運用なら 8.4 LTS（2032年サポート）、最新機能なら 9.x イノベーションを選ぶ
・rpm -qa でパッケージ単位のバージョンも確認でき、複数パッケージの混在を検出できる

この記事のポイント

・ANALYZE でプランナ統計を更新すればプラン選択ミスを直せる
・EXPLAIN ANALYZE の推定行数と実測行数のずれが診断の入口
・default_statistics_target を上げると統計精度が向上する
・列ごとに統計精度を個別調整する ALTER TABLE SET STATISTICS も有効

この記事のポイント

・PostgreSQLはMVCC+プロセスモデル、MySQLはスレッドモデルで根本から別物
・ギャップロックの有無がデッドロック発生パターンを左右する
・複雑な集計・JSONB・地理情報ならPostgreSQL、高速CRUD・大量接続はMySQL
・VACUUM管理と移行コストがセルフホスト選定で見落とされやすい注意点

この記事のポイント

・OS標準リポジトリを使わず公式スクリプトでMariaDB 11.4 LTSを固定する
・Ubuntu（APT）とRocky Linux（DNF）の両方に対応した手順を掲載している
・mariadb-secure-installationで匿名ユーザー・テストDBを除去する
・apt-mark hold / dnf versionlockでバージョン系列を固定して誤アップグレードを防ぐ

この記事のポイント

・ pg_roles で全ロールを一覧表示し、rolcanlogin でログイン可否を確認できる
・ rolvaliduntil でパスワード有効期限の切れそうなアカウントを事前に検知できる
・ pg_auth_members を JOIN すれば所属グループもまとめて確認できる
・ 棚卸し用の総合クエリ1本で「誰が・いつまで・どのグループか」を把握できる

この記事のポイント

・SHOW TABLES でデータベース内のテーブル名を一覧表示できる
・SHOW TABLE STATUS でサイズ・行数・ストレージエンジンを一括確認できる
・information_schema.TABLES のSQLで複数DBを横断・ソートして棚卸しできる
・mysql -e オプションでシェルスクリプトから非対話的に実行できる

この記事のポイント

・pg_upgrade --checkで事前に互換性問題を洗い出してから本番作業に入る
・pg_dumpallで論理バックアップを取得しておけば確実な切り戻しができる
・PostgreSQL 14は2026年11月でEOL。早めの移行計画が必要
・pg_upgradeはインプレース移行のため、pg_dump/restoreより大幅に速い

この記事のポイント

・MariaDB 10.x と MySQL 5.7 は高互換だが、MySQL 8.0 との差異は認証・GTID・JSONで大きい
・GTIDの形式が異なるため MySQL↔MariaDB 間の混在レプリケーションは設定に注意が必要
・my.cnfで MySQL 8.0 廃止のオプションが MariaDB では継続使用できるケースが多い
・mysqldump を使えばデータ移行自体は単純だが、文字セットと SQLモードを事前確認すること

この記事のポイント

・\dt でテーブル一覧、\dt *.* で全スキーマを一括表示できる
・\dn でスキーマ一覧を確認してから絞り込むのが現場の基本手順
・pg_stat_user_tables と pg_total_relation_size で行数とサイズを把握できる
・information_schema.tables で複数スキーマをSQL一発で洗い出せる

この記事のポイント

・mariabackupはInnoDBをロックなしでホットバックアップできるMariaDB公式ツール
・mariadb-backupパッケージをdnfで導入し、専用バックアップユーザーで実行する
・リストアは--prepare（前処理）→ --copy-back（データ戻し）の2ステップで完結する
・増分バックアップを組み合わせると毎日の差分だけ保存でき、バックアップ容量を削減できる

この記事のポイント

・ CREATE USER ～ GRANT ～ FLUSH PRIVILEGES 不要（MySQL 8.0）の正しい流れ
・ '%' と 'localhost' の違いを誤ると接続できない現象が起きる
・ SHOW GRANTS で付与済み権限を即確認、REVOKE で即剥奪できる
・ 最小権限（SELECT/INSERT/UPDATE/DELETE のみ）設計が本番の基本

この記事のポイント

・CREATE INDEX文でカラムへのインデックスを追加できる
・SHOW INDEX FROMで既存インデックスの構造を確認する
・EXPLAINのtypeがALLからrefに変わればインデックスが使われている
・更新頻度が高いカラムや選択性が低いカラムへの追加は逆効果になる

この記事のポイント

・type列が「ALL」ならフルテーブルスキャン——まず疑う
・possible_keysにあってkeyがNULLなら候補があっても使われていない
・Extraに「Using filesort」「Using temporary」が出たら要注意
・EXPLAIN ANALYZEで推定コストと実行コストの乖離を実測できる

この記事のポイント

・shared_buffers は「搭載RAM ÷ 4」が実務の出発点（デフォルト128MBは小さすぎる）
・work_mem は接続数とクエリの複雑さで倍増するため、計算式で保守的に見積もる
・shared_buffers の変更には再起動が必要、work_mem はリロードだけで反映できる
・設定後は SHOW 文または pg_settings ビューで反映を必ず確認する

この記事のポイント

・pg_basebackup -R でstandby.signalとprimary_conninfoが自動生成される
・プライマリ側はwal_level・max_wal_senders・listen_addresses・pg_hba.confの4か所を設定する
・pg_stat_replicationのstateが「streaming」になれば同期成立の合図
・sent_lsn・flush_lsn・replay_lsnの3列が一致していれば遅延なし、write_lag/replay_lagで秒単位確認も可能
・スタンバイ昇格はpg_ctl promoteまたはSELECT pg_promote()で実行できる

この記事のポイント

・VACUUMはデッドタプルを再利用可能にし、VACUUM FULLは物理圧縮するが排他ロックが必要
・autovacuumの起動条件はthreshold + scale_factor × 行数で決まる
・XID周回障害を防ぐにはage(relfrozenxid)を定期監視してFreeze VACUUMを計画的に実行
・大量更新テーブルはALTER TABLEでautovacuumパラメータをテーブル単位で上書きする

この記事のポイント

・pg_dump -Fc（カスタム形式）が本番運用での推奨フォーマット
・pg_dump -Fd -j N でディレクトリ形式の並列バックアップも可能（大規模DB向け）
・pg_restore -j N で並列リストアするとリストア時間を大幅短縮できる
・全DBを一括バックアップするにはpg_dumpallを使う
・cronで自動化する際は.pgpassファイルでパスワードを安全に管理する

この記事のポイント

・pg_hba.confのエントリが一致しない限り接続は即拒否される
・PostgreSQL 10以降の推奨認証はscram-sha-256（md5は非推奨）
・接続FATALエラーはメッセージ文言で原因をすぐ特定できる
・設定変更後は再起動不要。pg_ctl reloadで即時反映できる

この記事のポイント

・psql接続は「su - postgres → psql」の2ステップが基本
・CREATE ROLE 名前 WITH LOGIN PASSWORD でアプリ用ロールを作る
・権限付与はCONNECT→USAGE→テーブル権限の3段階で行う
・PostgreSQL 15以降はpublicスキーマのデフォルトCREATE権限が変わった

この記事のポイント

・datadir 変更後に SELinux ラベルを付け直さないと mysqld は起動しない
・semanage fcontext -a -t mysqld_db_t でラベル登録、restorecon -Rv で実際に適用する
・データコピーは rsync -av /var/lib/mysql/ /data/mysql/ で実施（末尾スラッシュ必須）
・作業前に mysqldump で全データのバックアップを取ってから進めること

この記事のポイント

・mysqlbinlog ＋ フルバックアップで誤操作直前まで復旧できる
・--stop-position でDROP/DELETE文だけをスキップして再適用できる
・バイナリログが期限切れで削除されると復旧不可能になる
・ROW形式のバイナリログ確認には --base64-output=DECODE-ROWS が必要

この記事のポイント

・MySQLレプリケーションはbinlogを介してソースからレプリカへ更新を自動同期する仕組み
・binlogポジション方式はFile名+位置で同期、GTID方式は一意IDで自動追跡する
・MySQL 8.4ではCHANGE MASTER TO等の旧構文が廃止。新コマンドへの対応が必須
・SHOW REPLICA STATUSでIO/SQLスレッド両方がYesなら正常動作している

この記事のポイント

・「Too many connections」は max_connections 上限到達で発生する
・SET GLOBAL max_connections = 値; で再起動なしに即時変更できる
・ゾンビ接続は SHOW PROCESSLIST で特定し KILL コマンドで切断する
・恒久対処は my.cnf の max_connections と wait_timeout のセット設定が基本

この記事のポイント

・innodb_buffer_pool_sizeは「空きRAMの70～80%」が実務のセオリー
・max_connectionsはper-threadバッファで上限メモリを試算してから決める
・mysqld --validate-configで再起動前に構文エラーを事前確認できる
・SHOW STATUSのヒット率でバッファプールの効果を定量的に確認できる
・MySQL 5.7.5以降はSET GLOBALで再起動なしにinnodb_buffer_pool_sizeを動的変更できる

この記事のポイント

・PGDGリポジトリを使えばLinuxに最新版PostgreSQLを導入できる
・RHEL9系はdnf module disable postgresqlの実行が必須
・Ubuntu 24.04 LTSはインストール後に自動でクラスタ初期化される
・pg_hba.confを編集してパスワード認証を有効化する

この記事のポイント

・information_schema.columnsをSELECTすればMySQL内のカラム検索が一発でできる
・table_schemaを条件に加えると、特定データベースに絞った検索が可能
・LIKE演算子で部分一致検索（user_%・%_idなど）にも対応
・MariaDB / MySQL 8.0 でも同じSQLがそのまま動く（標準化済みのスキーマ）