Starting: 1st of Every Month 
+91 8409492687 
Contact@DevOpsSchool.com
Script
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/bin/bash | |
| # Variables | |
| BACKUP_DIR="/path/to/backup/directory" # Replace with your desired backup location | |
| MYSQL_USER="your_username" # Replace with your MySQL username | |
| MYSQL_PASS="your_password" # Replace with your MySQL password | |
| TIMESTAMP=$(date +%F_%T) | |
| # Create backup directory if not exists | |
| mkdir -p "$BACKUP_DIR" | |
| # Fetch all databases | |
| databases=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|sys)") | |
| # Loop through databases and back them up | |
| for db in $databases; do | |
| echo "Backing up database: $db" | |
| mysqldump -u"$MYSQL_USER" -p"$MYSQL_PASS" "$db" > "$BACKUP_DIR/${db}_backup_$TIMESTAMP.sql" | |
| if [ $? -eq 0 ]; then | |
| echo "Backup for database $db completed successfully." | |
| else | |
| echo "Error backing up database $db." | |
| fi | |
| done | |
| echo "All backups are completed. Files are stored in $BACKUP_DIR." |
by
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/bin/bash | |
| USER="zend" | |
| PASSWORD="" | |
| ExcludeDatabases="Database|information_schema|performance_schema|mysql" | |
| databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | egrep -v $ExcludeDatabases` | |
| for db in $databases; do | |
| echo "Dumping database: $db" | |
| mysqldump -u $USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql | |
| done |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Based on these answers I've made script which backups all databases into separate files, but then compress them into one archive with date as name. | |
| This will not ask for password, can be used in cron. To store password in .my.cnf check this answer https://serverfault.com/a/143587/62749 | |
| Made also with comments for those who are not very familiar with bash scripts. | |
| #!/bin/bash | |
| # This script will backup all mysql databases into | |
| # compressed file named after date, ie: /var/backup/mysql/2016-07-13.tar.bz2 | |
| # Setup variables used later | |
| # Create date suffix with "F"ull date format | |
| suffix=$(date +%F) | |
| # Retrieve all database names except information schemas. Use sudo here to skip root password. | |
| dbs=$(sudo mysql --defaults-extra-file=/root/.my.cnf --batch --skip-column-names -e "SHOW DATABASES;" | grep -E -v "(information|performance)_schema") | |
| # Create temporary directory with "-d" option | |
| tmp=$(mktemp -d) | |
| # Set output dir here. /var/backups/ is used by system, | |
| # so intentionally used /var/backup/ for user backups. | |
| outDir="/var/backup/mysql" | |
| # Create output file name | |
| out="$outDir/$suffix.tar.bz2" | |
| # Actual script | |
| # Check if output directory exists | |
| if [ ! -d "$outDir" ];then | |
| # Create directory with parent ("-p" option) directories | |
| sudo mkdir -p "$outDir" | |
| fi | |
| # Loop through all databases | |
| for db in $dbs; do | |
| # Dump database to temporary directory with file name same as database name + sql suffix | |
| sudo mysqldump --defaults-extra-file=/root/.my.cnf --databases "$db" > "$tmp/$db.sql" | |
| done | |
| # Go to tmp dir | |
| cd $tmp | |
| # Compress all dumps with bz2, discard any output to /dev/null | |
| sudo tar -jcf "$out" * > "/dev/null" | |
| # Cleanup | |
| cd "/tmp/" | |
| sudo rm -rf "$tmp" |
Commands
$ mysqldump -u root -p --all-databases > alldb.sql
Look up the documentation for mysqldump. You may want to use some of the options mentioned in comments:
$ mysqldump -u root -p --opt --all-databases > alldb.sql
$ mysqldump -u root -p --all-databases --skip-lock-tables > alldb.sql
Import:
$ mysql -u root -p < alldb.sql
I’m a DevOps/SRE/DevSecOps/Cloud Expert passionate about sharing knowledge and experiences. I am working at Cotocus. I blog tech insights at DevOps School, travel stories at Holiday Landmark, stock market tips at Stocks Mantra, health and fitness guidance at My Medic Plus, product reviews at I reviewed , and SEO strategies at Wizbrand.
Please find my social handles as below;
Rajesh Kumar Personal Website
Rajesh Kumar at YOUTUBE
Rajesh Kumar at INSTAGRAM
Rajesh Kumar at X
Rajesh Kumar at FACEBOOK
Rajesh Kumar at LINKEDIN
Rajesh Kumar at PINTEREST
Rajesh Kumar at QUORA
Rajesh Kumar at WIZBRAND
