home/user/honma-h/メモ/2021/05/06/

MariaDB（MySQL）のTips

バックアップ・リストア

バックアップ

バックアップを圧縮して取得する

mysqldump --all-databases -uroot -p"**********" | pv | gzip -c > /var/backup/database/SERVER.mysqldump.gz

※pvコマンドを挟んで処理したデータ量と転送スピードをリアルタイムで確認

リストア

フルダンプのデータから特定のテーブルのみをリストア

フルダンプファイルがexment-mysql-20210722-0532.mysqldumpだった場合の例

# csplit でファイルを分割（`xx〇〇`という〇〇が数値のファイル名に分割される）
csplit exment-mysql-20210722-0532.mysqldump '/DROP TABLE IF EXISTS/' {*}

# 対象のテーブルが含まれるファイルを探す（テーブル名をバッククォートで括る必要あり）
grep -il 'create table `＜テーブル名＞`' *

# 対象のファイルからDBを指定してリストア実施（文字コード指定せずに文字化けするのあるある）
mysql --default-character-set=utf8 -u root -p"**********" exment_database < xx78

断片化した InnoDB テーブルを最適化

断片化の確認

  • NextCloudの例（テーブル名としてnextcloudを指定した場合）
MariaDB [(none)]> SELECT table_schema, table_name, data_free, table_rows
FROM information_schema.tables
WHERE table_schema = 'nextcloud';

最適化

mysqlcheckコマンドを使った最適化

データベース指定

mysqlcheck -o ＜データベース名＞

全てのデータベースに対して実施

mysqlcheck -uroot -p'**********' -o --all-databases

note : Table does not support optimize, doing recreate + analyze insteadというメッセージが表示されることがあるが、最適化は実施されているので問題なし

SQL（ALTER or OPTIMIZE）による最適化

テーブル単位なので全体に実施するにはループで回すなど一工夫がいりそう

ALTER TABLE ＜テーブル名＞ ENGINE INNODB;
OPTIMIZE TABLE ＜テーブル名＞;