Skip to content

Instantly share code, notes, and snippets.

@leewin12
Last active November 5, 2021 12:27
Show Gist options
  • Save leewin12/66a8424397458766bfca209bc3568044 to your computer and use it in GitHub Desktop.
Save leewin12/66a8424397458766bfca209bc3568044 to your computer and use it in GitHub Desktop.
Backup limited rows with `REPLACE` instead of `INSERT` via mysqldump (MySQL 5.7)
#!/bin/bash
#
# How to restore?
# $ zcat backup.sql.gz | mysql -h{} -u{} -p --database {dbname}
#
# --set-gtid-purged=OFF → https://stackoverflow.com/a/49059063/1378965
# --column-statistics=OFF → https://serverfault.com/a/912677/538646
# --single-transaction → https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_single-transaction
# --no-create-db → https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_no-create-db
# --skip-add-drop-table → https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_add-drop-table
# --no-create-info → https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_no-create-info
# --skip-add-locks → https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_add-locks
# --complete-insert → https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_complete-insert
# --replace → https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_replace
# --quick → https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_quick
# -w --where → https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_where
mysqldump -h{host} -u{user} -p{pw} \
--set-gtid-purged=OFF --column-statistics=OFF \
--single-transaction \
--no-create-db --skip-add-drop-table \
--no-create-info --skip-add-locks \
--complete-insert --replace \
--quick \
--databases survey2 --tables var -w"no IN
(SELECT x.no FROM (
SELECT no FROM survey2.var
WHERE vtl_var IS NULL
UNION
SELECT no FROM survey2.var
WHERE vtl_var IN (
SELECT v.vtl_var FROM survey2.var v
GROUP BY v.vtl_var
HAVING count(v.no) > 2
)
ORDER BY no
limit 10
) x)" | gzip > backup.sql.gz
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment