Before any changes, or for regular intervals, you might want to make backups of your database, which are not specific to the normal DirectAdmin User backup system. This guide will explain how to backup the .sql files on their own using CustomBuild.
Backup
cd /usr/local/directadmin/custombuild
./build set mysql_backup yes
./build mysql_backup
and if you want to prevent future calls to ./build mysql_backup from overwriting these files, rename the backup folder
mv mysql_backups mysql_backups.`date +%F`
Note that updating mysql, with mysql_backup=yes set in the options.conf will re-dump the databse to the mysql_backups directory.
Note: These .sql files contain the “DROP DATABASE” and “CREATE DATABASE” commands, unlike the .sql files in the DA User backups, so they cannot be easily interchanged.
Restore
If disaster hits, and you need to restore these .sql files, once mysql is up and running and the da_admin user/pass is working correctly, you can run:
cd /usr/local/directadmin/custombuild/mysql_backups
wget http://files1.directadmin.com/services/all/mysql/restore_sql_files.sh
chmod 755 restore_sql_files.sh
./restore_sql_files.sh
which restores all User databases.
If you also need to restore the mysql.* tables (usually avoid doing this unless you’ve lost all your mysql user/passwords), then you’d call the script like
./restore_sql_files.sh with_mysql
which will include the mysql.sql file for the restore, but will end up overwriting the da_admin password, so you may need to reset that if it was changed.
IMPORTANT
The DirectAdmin backup/restores are database type/version independent. However, these backups are not always universally interchangeable between databases. The User databases typically are fine, but the mysql tables in mysql.sql vary per version, so worst case you might not be able to (easily) restore the mysql.sql file into your database.
MySQL 5.7 uses a different password column name (replaced “password” with “authentication_string”) so restoring the mysql.user table wouldn’t work, just as one example.
When possible, use the same version of MySQL or MariaDB used to create these sql files.