Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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 |
