How to Back Up SQL Databases and Perform Command-Line Backups in DirectAdmin

1. Backing Up SQL Files Using CustomBuild

DirectAdmin allows you to generate standalone SQL backups using the mysql_backup option in CustomBuild. These backups are separate from the standard DirectAdmin User backups.

1.1 Creating SQL Backups

da build set mysql_backup yes
da build mysql_backup

To prevent future backups from overwriting the existing SQL dumps, rename the backup directory:

mv mysql_backups mysql_backups.`date +%F`

Note: SQL files generated by CustomBuild include DROP DATABASE and CREATE DATABASE commands, unlike DirectAdmin User backups. They cannot be interchanged.

2. Restoring SQL Files

Once MySQL is running and the da_admin credentials are valid, restore SQL files using:

cd /usr/local/directadmin/custombuild/mysql_backups
wget http://files.directadmin.com/services/mysql/restore_sql_files.sh
chmod 755 restore_sql_files.sh
./restore_sql_files.sh

To also restore the mysql.* tables (only if absolutely necessary):

./restore_sql_files.sh with_mysql

Warning: Restoring mysql.sql overwrites the da_admin password.

2.1 Version Compatibility Warning

SQL backups are not always compatible across MySQL/MariaDB versions. For example:

• MySQL 5.7 replaced the password column with authentication_string.

Always restore using the same database version when possible.

3. Creating Full Backups via Command Line

3.1 Full Backup of All Users

/usr/local/directadmin/directadmin admin-backup --destination=/home/admin/admin_backups

3.2 Backup Specific Users

/usr/local/directadmin/directadmin admin-backup --destination=/home/admin/admin_backups --user=testuser1 --user=testuser2

3.3 Restoring a Single User

echo "action=restore&ip%5Fchoice=file&local%5Fpath=%2Fhome%2Fadmin%2Fadmin%5Fbackups&owner=admin&select%30=user%2Eadmin%2Etestuser%2Etar%2Egz&type=admin&value=multiple&when=now&where=local" >> /usr/local/directadmin/data/task.queue

To restore to a specific IP:

ip_choice=select&ip=1.2.3.4

4. Automated Restore Script Example

#!/bin/sh
OWNER=admin
LOCAL_PATH=/home/${OWNER}/admin_backups
IP_CHOICE=select
IP=1.2.3.4

echo -n "action=restore&local_path=${LOCAL_PATH}&owner=${OWNER}&when=now&where=local&type=admin";

if [ "${IP_CHOICE}" = "select" ]; then
       echo -n "&ip_choice=select&ip=${IP}";
else
       echo -n "&ip_choice=${IP_CHOICE}";
fi

cd ${LOCAL_PATH}
COUNT=0
for i in `/bin/ls *.gz`; do
{
       echo -n "&select${COUNT}=$i";
       COUNT=$(( $COUNT + 1 ))
};
done;

echo "";
exit 0;

Then pipe the output to the task queue:

/root/restore_all.sh >> /usr/local/directadmin/data/task.queue

5. Checking Disk Usage Before Running Backups

This script prevents backups from running if disk usage exceeds a threshold.

#!/bin/sh
PARTITION=/dev/mapper/VolGroup00-LogVol00
MAXUSED=90

checkfree()
{
        DISKUSED=`df -P $PARTITION | awk '{print $5}' | grep % | cut -d% -f1`
        echo "$DISKUSED < $MAXUSED" | bc
}
if [ `checkfree` -eq 0 ]; then
        echo "$PARTITION disk usage is above $MAXUSED% Aborting backup.";
        exit 1;
fi

exit 0;

Make executable:

chmod 755 /usr/local/directadmin/scripts/custom/user_backup_pre.sh

6. Backing Up /home Using rsync

This method is ideal for large servers or when you want minimal system load.

#!/bin/sh

BACKUP_SOURCE="/home"

DAY_OF_WEEK=`date +%w`
ZERO_ONE=$(($DAY_OF_WEEK % 2))
BACKUP_DESTINATION="/backup/$ZERO_ONE"

mkdir -p ${BACKUP_DESTINATION}

ionice -c3 nice -n 19 rsync -q -a -W --delete $BACKUP_SOURCE $BACKUP_DESTINATION >/var/log/rsync.log 2>&1

echo `date` > ${BACKUP_DESTINATION}/last_rsync.txt

Enable script:

chmod 700 /root/rsync.sh

Schedule via cron:

30 4 * * 2,5 /root/rsync.sh