- 追加された行はこの色です。
- 削除された行はこの色です。
#author("2019-01-24T01:20:56+00:00","default:wikiadmin","wikiadmin")
#author("2023-07-14T10:54:19+00:00","default:wikiadmin","wikiadmin")
#contents
*import [#o57fe95a]
**pvを挟んで進捗を見る [#u7269b94]
yum install pv
pv mysqldump.sql | mysql -uroot -phogehoge
|innodb_flush_log_at_trx_commit = 1|3:44|
|innodb_flush_log_at_trx_commit = 0|3:42|
|innodb_flush_log_at_trx_commit = 2|3:41|
|binlog無効|3:03|
|xtrabackup|16秒!|
innodb_flush_log_at_trx_commitだけでは、binlog書き込みがボトルネックなので有意差なし。
**mydumper [#gc44ad30]
-インストールはRPMファイルがある
https://github.com/maxbube/mydumper/releases
**MySQL Dump [#i067cacc]
# エラーは-fで無視する
mysql -u root -phogehoge -f < mysqldump.sql
**Aurora [#ue5f397d]
+Replication停止はできない
https://blog.manabusakai.com/2017/03/difference-between-mysql-and-aurora/
+Binlog停止はできる
https://helen.hatenablog.com/entry/2019/06/13/200022
*mysqlpump [#w242dad7]
-MySQL 5.7から導入されたmysqldumpよりも高機能なツール
-MariaDBには使えない!
Server version is not compatible. Server version should be 5.6.46 or above.
-MariaDBのデータインポートした状態で実施したらSegmentation Faultが出るなどまだまだ不安定
**コマンド例 [#b2a84e7e]
|特定のDBかつ別ホストのDBかつ通信を圧縮する|mysqlpump -u root -p -h 192.168.11.157 --databases cakephp --compress|
*Xtrabackup [#e42a02c7]
-MySQL or PerconaServer対応。Mariadbもバージョンが古いのは対応しているが、新しいものはforkしたmariabackupを使うべし
-物理ファイルを含むため、mysqldumpを圧縮したのに比べて二倍ほど(160M vs 340M)
-ラッパーperlとしてinnobackupexもあるが、違いはなんだ(stream流して圧縮しながらバックアップできる?)
-全体バックアップ(テーブルロックなし)
xtrabackup --backup -u root -pパスワード --target-dir /tmp/backup
**Auroraへのimport [#fa7f1623]
***export [#n03e0948]
mkdir -p /tmp/aurora/backup
xtrabackup --backup --user=root --password=XXXX --stream=xbstream \
--target-dir=/tmp/aurora/ | split -d --bytes=500MB \
- /tmp/aurora/backup/onamae.xbstream
***S3 copy [#gfce3983]
aws s3 sync /tmp/aurora/backup/ s3://DEST_BUCKET/ --profile=xxx
*mariabackup [#ebd0e287]
-全体バックアップ(テーブルロックあり)
mariabackup --backup -u root -pパスワード --target-dir /tmp/backup
-バックアップの整合性チェック(取得中に更新があったものをロールフォワードする)
mariabackup --prepare --target-dir /tmp/backup
-バックアップ時点でのbinlog情報
cat /tmp/backup/xtrabackup_binlog_info
-リストア
systemctl stop mariadb
rm -rf /var/lib/mysql/*
mariabackup --copy-back --target-dir /tmp/backup
chown -R mysql:mysql /var/lib/mysql/*
systemctl start mariadb
*mysqldump [#d40500ab]
** [#reaa0d8d]
-指定されたDB/Tableについて、SQLを吐き出すだけ。単純だがSQLなので汎用性がたかいね。
**オプション [#pdb550a1]
**一貫性を保つために [#reaa0d8d]
5.7より前(つまり2019年でも)一貫性を保つためにmysqldumpのオプション指定が必要
**スキーマのみデータのみdump [#k7f36bc3]
mysqldump --no-data DBNAME > schema_only.sql
--no-data
--no-create-info
**オプション一覧 [#p5aa1d11]
|mysqldump --all-databases --lock-all-tables|全テーブルロックで一番安全。ダンプ中にアプリケーションから更新処理が走る場合は下の選択肢。xでも同じことだがあとで見た時に意味不明なのでやめる|
|mysqldump --all-databases --single-transaction|ダンプ中にMyIsamテーブルを更新しない、innoDBスキーマを変更しない|
|--flush-logs|ダンプ時にバイナリログをリセットする|
|--master-data=2|バイナリログの位置をコメントで出力。レプリケーションやPITRに便利|
|--events|イベント関連のテーブルを明示的にバックアップする。 --all-databasesをつけていると除外されてワーニングがでるので、一緒につけることが多い|
*インポート、エクスポート [#a221f3ad]
**CSVインポート [#n9393c5b]
-デリミターを指定しない場合はタブ区切りとなる。
-日本語関係のフィールドをロードする場合はクライアントの文字コードをセットしておいた方が良いかも。
LOAD DATA LOCAL INFILE 'car_import.csv' INTO TABLE テーブル名 FIELDS TERMINATED BY ',';
-フィールドを指定する場合
LOAD DATA LOCAL INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);
-autoincrement列を除外する場合はその列を除外すれば自動的に連番がふられる(insert分でも同様)
LOAD DATA LOCAL INFILE 'test.txt' INTO TABLE test (col1);
*バックアップ&リカバリー [#e852c036]
**エクスポート [#e41d46fb]
標準出力にでるのでリダイレクトで保存すること。以下のコマンドを追加して、圧縮しつつバックアップを取るとよい。
| gzip > dump.sql.gz
|単位|コマンド|備考|
|テーブル|mysqldump -u root -p --add-drop-table DB名 テーブル名|drop tableを入れるオプション付き|
|テーブル where条件付き|mysqldump -u root -p --add-drop-table DB名 テーブル名 --where 'is_delete = 0'|drop tableを入れるオプション付き|
|データベース|mysqldump -u root -p DB名|dropテーブルは自動で入るようなので、空じゃないDBに入れるときは既存データなくなる!|
***スキーマのみ [#a7d77090]
--no-dataのオプションをつける
**テーブル単位 [#la55d36c]
-主キーがかぶらないようにdrop tableして作り直す構文つきで出力
mysqldump -u root -p --add-drop-table DB名 テーブル名 > /var/tmp/rooms.sql
-戻すときはデータベースの指定だけでOK
mysql -u root -p DB名 < /var/tmp/rooms.sql
**データベース単位 [#lf547451]
***バックアップ [#b7780581]
-バックアップ対象はデータベースmysqlとし、出力ファイル名はmysql.sqlとする。
mysqldump -u root -p データベース名 > mysql.sql
-危険を承知でパスワードをコマンドに入れてしまう場合はこちらで
mysqldump -u root --password=パスワード データベース名 > mysql.sql
-圧縮しつつバックアップは以下の通り
mysqldump -u root -p データベース名 | gzip > mysql.sql.gz
***リカバリー [#t7f42718]
-リカバリー対象データベースはcreateしておく。
mysql> create database mysql2;
mysql> exit
mysql -u root -p mysql2 < mysql.sql
-圧縮したもののリカバリーは以下の通り
zcat mysql.sql.gz | mysql -u root -p データベース名
**全部のデータベース [#k87164b0]
***バックアップ [#bfdcb4d8]
-全部のデータベースを出力
mysqldump -u root -x --all-databases > dump.sql
***リカバリ [#d9c135f3]
-全部のデータベースをインポート(もとあるDBは名前がかぶらなければ残る)
mysql -u root -p < dump.sql
-リカバリーした後は''rootでログインして、flush privilegesすること''。さもないと移行されたユーザでログインできない。もしくはMySQLの再起動で反映される。
#counter