Skip to content

Instantly share code, notes, and snippets.

@Max95Cohen
Created January 21, 2023 19:20
Show Gist options
  • Save Max95Cohen/04d304487234f8f92f514b99d9f71bcd to your computer and use it in GitHub Desktop.
Save Max95Cohen/04d304487234f8f92f514b99d9f71bcd to your computer and use it in GitHub Desktop.
_backup.sh mysql backup script
# mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW
# for dumped views, TRIGGER for dumped triggers, LOCK TABLES if the
# --single-transaction option is not used, and (as of MySQL 8.0.21) PROCESS if
# the --no-tablespaces option is not used. Certain options might require other
# privileges as noted in the option descriptions.
# .my.cnf
[mysqldump]
user=backup
password="PASSWORD"
CREATE USER backup@localhost IDENTIFIED BY 'PASSWORD';
GRANT SELECT, LOCK TABLES, TRIGGER, SHOW VIEW, PROCESS ON *.* TO backup@localhost;
# _backup.sh
#!/bin/bash
_PATH=/backups/
mysqldump -u backup --no-tablespaces database > $_PATH"database.dump.sql"
BACKUPTIME=`date +%b-%d-%y_%H-%M-%S`
DESTINATION=$_PATH$BACKUPTIME".tar.gz"
SOURCEFOLDER=$_PATH"all.sql"
echo tar -czf $DESTINATION --absolute-names $SOURCEFOLDER
# find /backups -mtime +10 -delete
# find $BACKUP_PATH -mtime +14 -type f -exec rm -rf {} \;
# find $BACKUP_PATH -mtime +4 | xargs rm -f
sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i database.dump.sql;
mysqldump -v --hex-blob --insert-ignore --skip-lock-tables \
--single-transaction -u root -p database > database.dump.sql;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment