Mysql database should be backed up daily or periodically. It is possible to back up regularly with a script that we will create in the operating system for the backup process. We can use the mysqldump tool for backup.
We can create and schedule a shell script like the one below.
Backup All MySQL Databases
Create a script named mysql_backup.sh with the following content. The script will create a backup file for all databases.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
#! /bin/bash TIMESTAMP=$(date +"%F") BACKUP_DIR="/backup" MYSQL_USER="root" MYSQL=/usr/bin/mysql MYSQL_PASSWORD="root_password" MYSQLDUMP=/usr/bin/mysqldump mkdir -p "$BACKUP_DIR/$TIMESTAMP" databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|oldiblog|ndbinfo|ndb_2_fs|backup)"` for db in $databases; do $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/mysql/$db.gz" done # delete backups created before 7 days find $BACKUP_DIR -ctime +7 -type f -delete |
Let’s make the script executable.
|
1 |
chmod 755 mysql_backup.sh |
mysqldump Exclude Database
With the grep -Ev command, we can specify directories that are not associated with the database in the data directory, or databases that we do not want to back up.
Create a Crontab For Backing up Databases Regularly
FAdd the script to crontab to back it up periodically.
|
1 2 3 |
[root@mysqldb1 mysql]# crontab -e 0 0 * * * /root/scripts/mysql_backup.sh |
|
1 2 |
[root@mysqldb1 mysql]# crontab -l 0 0 * * * /root/scripts/mysql_backup.sh |
