Fli
08-15-2013, 03:08 PM
Hi, backups are necessary, one should backup files offsite/offserver, but at this time we will talk on how to backup all mysqls on same VPS/Dedicated server into speciffic directory. (Gziped mysql backup files)
Warning: follow this guide only if you are knowing what you are doing. You may delete your server files accidentally. So continue at your own risk.
Create shell script (vi mysqlsbackupscript.sh) with content:
#!/bin/bash
TIMESTAMP=$(date +"%F")
BACKUP_DIR="/home/mysqlsbackup/$TIMESTAMP"
MYSQL_USER="root"
MYSQL_PASSWORD="*******"
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump
mkdir -p $BACKUP_DIR
databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`
for db in $databases; do
echo $db
$MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/$db.gz"
done
1. vi mysqlsbackupscript.sh
2. "a" key
3. paste above code
4. instead of *******, add your mysql root password, if you dont know it, here is how to reset it (http://internetlifeforum.com/general-server-webmaster-discussion/429-how-reset-mysql-password-via-linux-command-line/).
5. Ctrl+C (to stop writing into file)
6. save and quit editing file by writing and executing (enter): :wq
Now you have shell script file created, this file can backup your mysql databases into speciffied folder when executed. Make it executable by: chmod -x mysqlsbackupscript.sh
U can try to execute the file to test if it working by sh mysqlsbackupscript.sh
When script will run, it will create backup gzipped files in /home/mysqlsbackup/backupdate/backupfilenamewithdateandtime.gz
Now we set this shell script to run periodically by copying it into /etc/cron.daily folder (cp mysqlsbackupscript.sh /etc/cron.daily)
But be carefull, now every day script will generate new backup files, so size of that folder will grow!
We need to setup another cronjob which will empty the folder like every month. This way when there is any fault with mysql, you should have some time to restore mysql backup file (if the fault dont happen right after cronjob run... then there would be no files to restore)
How to setup cronjob to empty that /home/mysqlsbackup folder?
Create file emptymysqlbackupfolder.sh by similar way like before and paste this content into is:
#!/bin/bash
find /path/to/directory/ -delete
done
(replace /path/to/directory/ by your mysqlbackupdirectory path, in our example case: /home/mysqlsbackup/)
When u have shell script file saved with this content, make it executable (chmod -x emptymysqlbackupfolder.sh). Then copy it into /etc/cron.monthly to make it executed monthly (cp emptymysqlbackupfolder.sh /etc/cron.monthly).
-----
Please if you have any improvements and experience, please share to help others.
Warning: follow this guide only if you are knowing what you are doing. You may delete your server files accidentally. So continue at your own risk.
Create shell script (vi mysqlsbackupscript.sh) with content:
#!/bin/bash
TIMESTAMP=$(date +"%F")
BACKUP_DIR="/home/mysqlsbackup/$TIMESTAMP"
MYSQL_USER="root"
MYSQL_PASSWORD="*******"
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump
mkdir -p $BACKUP_DIR
databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`
for db in $databases; do
echo $db
$MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/$db.gz"
done
1. vi mysqlsbackupscript.sh
2. "a" key
3. paste above code
4. instead of *******, add your mysql root password, if you dont know it, here is how to reset it (http://internetlifeforum.com/general-server-webmaster-discussion/429-how-reset-mysql-password-via-linux-command-line/).
5. Ctrl+C (to stop writing into file)
6. save and quit editing file by writing and executing (enter): :wq
Now you have shell script file created, this file can backup your mysql databases into speciffied folder when executed. Make it executable by: chmod -x mysqlsbackupscript.sh
U can try to execute the file to test if it working by sh mysqlsbackupscript.sh
When script will run, it will create backup gzipped files in /home/mysqlsbackup/backupdate/backupfilenamewithdateandtime.gz
Now we set this shell script to run periodically by copying it into /etc/cron.daily folder (cp mysqlsbackupscript.sh /etc/cron.daily)
But be carefull, now every day script will generate new backup files, so size of that folder will grow!
We need to setup another cronjob which will empty the folder like every month. This way when there is any fault with mysql, you should have some time to restore mysql backup file (if the fault dont happen right after cronjob run... then there would be no files to restore)
How to setup cronjob to empty that /home/mysqlsbackup folder?
Create file emptymysqlbackupfolder.sh by similar way like before and paste this content into is:
#!/bin/bash
find /path/to/directory/ -delete
done
(replace /path/to/directory/ by your mysqlbackupdirectory path, in our example case: /home/mysqlsbackup/)
When u have shell script file saved with this content, make it executable (chmod -x emptymysqlbackupfolder.sh). Then copy it into /etc/cron.monthly to make it executed monthly (cp emptymysqlbackupfolder.sh /etc/cron.monthly).
-----
Please if you have any improvements and experience, please share to help others.