_backup.sh mysql backup script

_backup.sh

# 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;