AutoMySQLBackup
During working with CentOS CWP 6.9, I found the built-in backup method is a little bit plain. While searching for an alternative solution, I came across AutoMySQLBackup. A very handy and simple to use tool. It only uses mysqldump which part of mysql-client. The configuration file lets the user customize the backup interval, excluded databases, backup location, backup rotations and more.
Installation
First download the package from Sourceforge unpack it:
|
1 |
$ tar -zxvf automysqlbackup-v3.0_rc6.tar.gz |
Run the extracted install script:
|
1 |
$ ./install.sh |
It will move 2 files to the appropriate directories.
/usr/local/bin/automysqlbackup
/etc/automysqlbackup/automysqlbackup.conf
Configuration
Let’s open the configuration file with an editor.
|
1 |
$ vi /etc/automysqlbackup/automysqlbackup.conf |
Most important part is the correct credentials for the database connection. For that I set the username, password and the DB location.
|
1 2 3 4 5 6 7 8 |
# Username to access the MySQL server e.g. dbuser CONFIG_mysql_dump_username='backupuser' # Password to access the MySQL server e.g. password CONFIG_mysql_dump_password='backupuserpassword' # Host name (or IP address) of MySQL server e.g localhost CONFIG_mysql_dump_host='localhost' |
Now the location of the database backups. I configured mine to be within the /home directory instead /var. It is important that the database and user accounts share the same parent directory (see later). By adding more and more data to the user accounts, the backups started to growing. Then my root logical volume ran out of space and the whole server crashed. To change the location I modified the following line:
|
1 2 |
# Backup directory location e.g /backups CONFIG_backup_dir='/home/backup/db' |
You can also configure databases which are not required to be backed up.
|
1 2 |
# List of DBNAMES to EXLUCDE if DBNAMES is empty, i.e. (). CONFIG_db_exclude=( 'information_schema' 'mysql' 'performance_schema' 'phpmyadmin' 'postfix' 'root_cwp' 'roundcube' ) |
The Rotation Settings describe the scheduling and number of backups to be kept. I wanted to do the monthly backup on the 1st of every months and keep 6 backups. So I set the monthly rotation to 150.
|
1 2 3 4 5 |
# Set to 0 to disable monthly backups. CONFIG_do_monthly="01" # Set rotation for monthly backups. VALUE*24hours CONFIG_rotation_monthly=150 |
Keep 6 weekly backups which are run on every Fridays and the same rotation for the daily schedule.
|
1 2 3 4 5 6 7 8 9 10 |
# Which day do you want weekly backups? (1 to 7 where 1 is Monday) # Set to 0 to disable weekly backups. CONFIG_do_weekly="5" # Set rotation for weekly backups. VALUE*24hours CONFIG_rotation_weekly=35 # Set rotation of daily backups. VALUE*24hours # If you want to keep only today's backups, you could choose 1, i.e. everything older than 24hours will be removed. CONFIG_rotation_daily=6 |
The Backup Script
After the MySQL DB backup was set, I wanted to do the same with all the user data. User accounts can be backed up from CWP. However, it doesn’t give a good choice of options regarding the scheduling and backup rotations. The following script package gives more freedom over it. It contains 1 configuration and 2 bash files.The files can be downloaded from 
To make this work with AutoMySQLBaskup, I’d recommend to set the PARENT_DIR to share it with the database backup files. So if the database is backed up to /home/backup/db then the user account should be saved to /home/backup/account. In the config file it would look like this:
|
1 2 3 4 5 6 7 |
# # Backup directory of the User Accounts # Recommended location is within the /home directory. # e.g. /home/backup/account # PARENT_DIR=/home/backup BACKUP_DIR=/account |
The full configuration file account-backup-cron.conf can be viewed below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
# # ACCOUNT BACKUP CONFIGURATION FILE # # --------------------------------- # by gellai.com # --------------------------------- # # 2017 May # # # Backup directory of the User Accounts # Recommended location is within the /home directory. # e.g. /home/backup/account # PARENT_DIR=/home/backup BACKUP_DIR=/account # # Number of daily backups to be kept # COUNT_DAILY=6 # # The day when to do the weekly backup (only 1 day) # eg. Mon,Tue,Wed,Thu,Fri,Sat,Sun # WEEKLY_DAY=Sun # # Number of weekly backups to be kept # COUNT_WEEKLY=2 # # The date of the month to do the monthly backup (only 1 day) # If the month is shorter than the last day given, the backup # will be skipped. # eg. 01,05,15,... # MONTHLY_DAY=15 # # Number of monthly backups to be kept # COUNT_MONTHLY=1 # # FTP Server Address # FTP_SERVER=0.0.0.0 # # FTP Port # FTP_PORT=21 # # Remote Folder # FTP_FOLDER=/ # # User Name # FTP_USER=ftpuser # # Password # FTP_PASS=ftpuserpassword |
FTP server can be configured so all the backups will be synchronized according to the settings in the configuration. This is done by ftp-backup-cron.sh.
What does account-backup-cron.sh do?
- Connect to MySQL database and retrieve a list of the user accounts.
- Copy and compress the data from the accounts.
- Synchronize the number of saved backups.
- Do the weekly backup if it’s due.
- Do the monthly backup if it’s due.
What does ftp-backup-cron.sh do?
- Connect to MySQL database and retrieve a list of the user accounts.
- Create a temporary folder where the FTP location will be mounted.
- Connect to FTP and mount the connected location.
- Synchronize the content of the PARENT_DIR and the number of saved backups.
- Retrieve a list of all the existing database names from MySQL server.
- Delete the excluded ones from the list (configured in AutoMySQLBackup).
- Synchronize the database backups with the FTP server (daily, weekly, monthly).
- Maintain miscellaneous folders created during backup.
The last thing I had to do was to make sure that these will run every day in a certain time. I added the followings to the Crontab.
|
1 |
$ crontab -e |
|
1 2 3 |
30 23 * * * /usr/local/bin/automysqlbackup >> /dev/null 2>&1 30 0 * * * /usr/local/bin/account-backup-cron.sh >> /dev/null 2>&1 30 1 * * * /usr/local/bin/ftp-backup-cron.sh >> /dev/null 2>&1 |
- AutoMySQLBackup backups the database at 23:30.
- User accounts are backed up at 0:30.
- The parent backup directory is uploaded and synchronized via FTP at 1:30.
The timings can be various according to the server’s bottleneck.
Final Thoughts
I cannot guarantee that this solution will work on every CWP servers. Configurations could differ from the one I used to create the scripts. It has been working for me for years, however sometimes after a server update I have to tweak things.