[MySQL]DBのdumpとrestore

LINEで送る
Pocket

  • dump(メンテナンスしない場合)
$ mysqldump -u root -p -h xxxxxxxxxxxx  --skip-lock-tables --single-transaction -v -P xxxxx DB-name table-name | gzip > ~/backup/$(date "+%Y%m%d")/bk.$(date "+%Y%m%d").gz 
$ ll ~/backup/$(date "+%Y%m%d")/bk.$(date "+%Y%m%d").gz
  • dump(メンテする場合)
mysqldump --extended-insert --quick --quote-names --single-transaction --set-gtid-purged=OFF -u hoge -h hoge.ap-northeast-1.rds.amazonaws.com -p hoge-db | gzip > ./$(date "+%Y%m%d").sql.gz
  • restore
gunzip bk.$(date "+%Y%m%d").gz
mysql -h xxxxxxxxxx -P xxxxxx -u user -p table-name < ~/backup/$(date "+%Y%m%d")/bk.$(date "+%Y%m%d")

  • mysql8 dump
mysqldump db -u hoge -h hoge -P 1234 -p --set-gtid-purged=OFF > hoge.sql

  • シンプル dump 
$ mysqldump -u root -p -h xxxxxxxx --skip-lock-tables -v hoge | gzip > bk.$(date "+%Y%m%d").gz
  • シンプルrestore
$ zcat bk.20200907.gz | mysql -u root -h xxxxxxxxxxxxx -p hoge

Was this helpful?

0 / 0

コメントを残す 0

Your email address will not be published. Required fields are marked *