Backup restore recovery

How to backup SQL with CustomBuild

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.

cd /usr/local/directadmin/custombuild
./build set mysql_backup yes
./build mysql_backup

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 database 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.

Backups have two configured options in options.conf file:

• mysql_backup_gzip=no/yes , if you want saved dumps to be gzipped (sql.gz)
• mysql_backup_dir=/usr/local/directadmin/custombuild/mysql_backups , if you want backups to be done to another directory

How to restore SQL with CustomBuild

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 https://files.directadmin.com/services/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 system mysql.* tables (usually avoid doing this unless you've lost all of your MySQL user/passwords), then you'd call the script like so:

./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.

How to manually restore a database.gz file via SSH

If you've got a very large database.gz file, it's often easier to upload first, and restore in a separate step.

1. Once you've got the gz file uploaded onto your server, log in via SSH and cd to the path where the file is located.

cd /path/to/your/gz/file

2. The next step is to extract the gz file into its raw sql form (text). To do that, use the gunzip program, e.g.,

gunzip < database.gz database.sql

This should have extracted the gz file into the sql in plain text form. Please note that it could be up to 10x larger in size than the original .gz file .

3. Before you can load the sql file into mysql, you'll need to ensure you have the database and database user created. Go to User Level -> MySQL Management, and create the empty database normally through DA.

4. To restore the sql to mysql, run the following command:

mysql -udbusername -ppassword username_dbname < database.sql

where you'd replace the username, password and database name entries with the applicable values.

This could can be combined into a more simple pipe form to offer fewer steps, but this method has the benefit of being able to show you any errors at each step to ensure everything is as it should be.

Restore just the .sql files from multiple DirectAdmin tar.gz backups

Say you have a large list of files in /home/admin/admin_backups and you wish to restore the databases from those backups, but not the rest of the data. You can use this script for a quick restore.

Note:

• This restore will not restore the mysql.user nor mysql.db tables. Only the username_db databases.
• This will overwrite any current tables in the existing databases.
• As it processes each tar.gz file, that tar.gz will be moved to the "done" folder, so you can run it multiple times, or stop it ad resume later if needed.

A sample script that can do this, might look like this:

#!/bin/sh

P=/home/admin/admin_backups
DB_USER=da_admin
DB_PASS=12345

mkdir -p done

for i in `ls *.tar.gz`; do
{
       echo $i;
       cd $P
       mkdir -p temp
       cd temp

       tar xvzf ../$i backup/*.sql 2>/dev/null

       if [ -d backup ]; then
               ls -la backup
               cd backup
               for d in `ls *.sql`; do
               {
                       D_NAME=`echo $d | cut -d. -f1`
		       mysql -u${DB_USER} -p${DB_PASS} -e "CREATE DATABASE \`${D_NAME}\`;";
                       mysql -u${DB_USER} -p${DB_PASS} $D_NAME < $d
               };
               done;
       else
               cd $P
               echo "No .sql files"
       fi

       cd $P
       mv $i done
       rm -rf temp
};
done;
exit 0;

Moving a database to a different user

If you want to move the databases from one user to another user, you can do so with the following command:

VERBOSE=1 DBUSER="da_admin" DBPASS="da_adminpass" USERNAME="username" NEWUSERNAME="newusername" /usr/local/bin/php /usr/local/directadmin/scripts/change_database_username.php

Where you can grab the da_adminpass from /usr/local/directadmin/conf/mysql.conf, and of course, replace names with the appropriate info.

MySQL Restore error: Access denied; you need the SUPER privilege

If you run into the "SUPER privilege" error when running a MySQL restore, it would be because the restores do not run with a root/super level privilege for security reasons.

UPDATE: as of DirectAdmin 1.49.2, you can set an Admin Level restore to run with da_admin to overcome this issue. The option must be enabled in the directadmin.conf to do this: https://www.directadmin.com/features.php?id=1817

How to manually transfer large databases between boxes

If you have an excessively large database, or you want to keep multiple databases backed up or synced, you can transfer them manually with a basic command. In this example, we will connect to a remote box, and download a remote database to a local database directly from mysql to mysql.

1. First create the empty database and all users
2. Make sure you have an access host on the remote box to allow a remote connection.
3. You can either upload (send) or download it (get). It doesn't make much difference (assuming you've set up your access host).

So, to download from a remote box to a local db, you'd use:

mysqldump -uremoteuser -premotepass -hremote.host.com dbname | mysql -ulocaluser -plocalpass dbname

mysqldump will connect to the remote box, with the remote user/host/pass and dump the output to stdout, thus the | (pipe) will redirect the stdout to the stdin of the 2nd part, which runs all sql commands from that output into the local database.