Back up Code42 server database dumps to another location

Video

Watch the short video below to learn more about how to dump the database to a secure location. For more videos, visit the Code42 University. 

Before you begin

• This tutorial uses Linux as the example operating system, but the general principles apply to other server operating systems.
• You should be familiar with the Linux or Unix command line and the SSH File Transfer Protocol (SFTP).
• You should be familiar with the cron job scheduler used by Linux and Unix, and know how to add and modify crontab files.
• Your system must have OpenSSH installed.

Step 1: Set up an SFTP server on the source enterprise server

1. Create a directory database dumps by entering the following command in your terminal window as root:
   mkdir -p /sftpuser/dumps
2. Create an sftp user group with this command:
   groupadd sftpusers
3. Create the user with the command:
   useradd -G sftpusers -d /sftpuser sftpuser
4. Create a password for the user:
   passwd sftpuser
   If your source server does not have SFTP installed, install it before continuing.
5. Using a text editor such as vim, modify /etc/ssh/sshd_config:
   • Comment out the following line so that it looks like this:

     #Subsystem sftp /usr/libexec/openssh/sftp-server

   • Add the following lines to the end of /etc/ssh/sshd_config

     Subsystem sftp internal-sftp
     Match Group sftpusers
     ChrootDirectory /sftpuser
     ForceCommand internal-sftp

6. Restart sshd with the following command:
   /etc/init.d/ssh restart
   The server responds: [ ok ] Restarting OpenBSD Secure Shell server: sshd.
7. Create a bind mount to the database dump directory on your source server.
   A bind mount is necessary as symbolic links do not work with sftp.
   • Using a text editor such as vim, add the following line to /etc/fstab:

     /var/opt/proserver/dumps /sftpuser/dumps none bind

     Replace /var/opt/proserver/dumps with the actual path to your database dumps directory on your source server if it uses a non-default path.
   • To put the changes to /etc/fstab into effect now, enter the following command:
     mount -a
8. Enter the command:
   ls /sftpuser/dumps
   You should see a listing of the database dumps:

   proserver-db_1375333203614_2014-01-04_033207.sql.gz
   proserver-db_1375333203614_2014-01-05_025342.sql.gz
   proserver-db_1375333203614_2014-01-06_021834.sql.gz
   proserver-db_1375333203614_2014-01-07_024713.sql.gz
   proserver-db_1375333203614_2014-01-08_024248.sql.gz
   proserver-db_1375333203614_2014-01-15_022254.sql.gz
   proserver-db_1375333203614_2014-01-16_020101.sql.gz
   proserver-db_1375333203614_2014-01-17_020101.sql.gz
   proserver-db_1375333203614_2014-01-30_022520.sql.gz

9. As root, change permissions for the dumps directory to allow remote user access with the following command:
   chown -R root:sftpusers /sftpuser/dumps

Step 2: Retrieve the database dumps on the destination server

Test the ability to use your SFTP client on the destination server to retrieve the database dumps from the source server. Here is an example test from the destination server.

1. Run the following command replacing the IP address or hostname with the IP address or hostname of your source server:

   myserver:~ user$ sftp sftpuser@192.0.2.10

   You will be prompted for the sftpuser's password.
2. List the directory contents of the dumps folder with the following command:
   ls dumps
   You should see a listing of the database drops.

   sftp> ls dumps
   dumps/proserver-db_1375333203614_2014-01-04_033207.sql.gz
   dumps/proserver-db_1375333203614_2014-01-05_025342.sql.gz
   dumps/proserver-db_1375333203614_2014-01-06_021834.sql.gz
   dumps/proserver-db_1375333203614_2014-01-07_024713.sql.gz
   dumps/proserver-db_1375333203614_2014-01-08_024248.sql.gz
   dumps/proserver-db_1375333203614_2014-01-15_022254.sql.gz
   dumps/proserver-db_1375333203614_2014-01-16_020101.sql.gz
   dumps/proserver-db_1375333203614_2014-01-17_020101.sql.gz
   dumps/proserver-db_1375333203614_2014-01-30_022520.sql.gz

   This example shows the "sftpuser" listing the contents of the dumps directory. The contents of the dumps directory are now available for downloading to your destination (remote) server.

Step 3: Automate the retrieval of database dumps

Use the Linux cron daemon to automatically retrieve database dumps from the source server.

1. Create a shell script named "sftpBackup".
2. Enter the following commands, replacing the variables below for your environment:
   • Directory (/home/proserver/DB_Backups) with your database backup directory.
   • SSHPASS (examplePassword) with your password for the user "sftpuser".
   • SSHD host location (192.0.2.10) with the IP or FQDN of your source server.

     #!/bin/sh
     export SSHPASS=examplePassword
     sshpass -e sftp -oBatchMode=no -b - sftpuser@192.0.2.10 << !
     lcd /home/proserver/DB_backups
     get -r dumps
     bye
     !

3. Save your shell script.
4. Schedule your shell script by adding the entry below to crontab:
   • The path to the script sftpBackup should be replaced with the path to your working script.
   • This entry is configured to run the script every day at 8:00 p.m. US Central Time. The values for the dates and times in the crontab entry should be modified so that it is suitable for your environment.

     00 20 * * * /usr/local/bin/sftpBackup

Step 4: Public/private key access (optional)

Public/private key access is more secure than password authentication, so you can optionally configure SFTP to use key-based authentication.

1. Enter the following commands as root on the source server (source of the database dump files and has the user "sftpuser"):
   mkdir /sftpuser/.ssh
cd /sftpuser/.ssh
ssh-keygen -t rsa -f /sftpuser/.ssh/id_rsa
cat /sftpuser/.ssh/id_rsa.pub >> /sftpuser/.ssh/authorized_keys
chmod 700 /sftpuser/.ssh/
chown -R sftpuser:sftpuser /sftpuser/.ssh
2. Copy id_rsa (the private key file) to the server that will serve as the destination for the database dumps, and delete it from the source server. After copying the private key file to the destination server, enter the following command:
   rm /sftpuser/.ssh/id_rsa
3. From the destination server, you can now log in securely with the following command on the source server:
   sftp -i ~/.ssh/id_rsa sftpuser@exampleServer
4. Change your shell scripts and/or crontab files to reflect that fact that you are now using an identity file.

Technical note

You may choose to use rsync, rather than sftp, to transfer the database dumps. In this case, rsync could be used to mirror the database dump directory from one server to another. For example, you could mirror the database dumps from an active authority server to a standby authority server. 

Before you begin

• This tutorial uses Windows as the example operating system, but the general principles apply to other server operating systems.
• You should be familiar with the Windows PowerShell command line and Windows networking.
• You should be familiar with Task Scheduler used by Windows, and know how to add and modify scheduled tasks.
• Your system must have Windows PowerShell installed.
• This tutorial assumes that you have already configured a separate Windows server with a network share to receive the database dumps.

Step 1: Create a copy-item script in PowerShell

Windows PowerShell script files are plain-text files with the extension .ps1. The script below uses the Copy-Item cmdlet to copy your database dumps to the network share you created.

1. Open a text editor.
2. Enter the following text and replace the variables below:
   The database dumps location varies in different versions of Windows.

   Copy-Item -path <path_to_the_database_dump_folder>* -destination <path_to_the_secondary location> -force
   

3. Save the file with the .ps1 extension.

Copy-Item -path C:\ProgramData\PROServer\dumps\* -destination \\exampleserver\network\share\ -force

New-PSDrive –Name “K” –PSProvider FileSystem –Root “\\exampleserver\network\share\” –Persist
Copy-Item -path C:\ProgramData\PROServer\dumps\* -destination K: -force

Step 2: Automate the script with task scheduler

Task Scheduler runs scheduled scripts or launches programs at specified times or intervals.

1. Open Task Scheduler.
2. Click Create Basic Task and enter the following parameters:
   • Create A Basic Task: Name for the task.
     (Optional) Enter a description of the task.
   • Trigger: Daily.
   • Daily: Select a time.
   • Action: Start a program.
   • Program/script: PowerShell.exe
   • Add arguments: Enter the following:

     -ExecutionPolicy ByPass -file C:\Location\of\your\powershell\script.ps1

3. Click Finish to save the task.

Step 3: Test the configuration

1. Click Task Scheduler Library.
2. Select the script you created.
3. Click Run under Selected Item.
   Your script copies the file to the location you specified.
4. Verify that the database dump has been copied to the new location.