Database - Backup

From TNG_Wiki
Jump to: navigation, search
Ambox notice.png This page provides some of the considerations for creating TNG Backups and different methods for doing the Database Backup. See Database - Restore for the database restore methods.
TNG All


TNG Maintenance – backup and restore

It takes a lot of work and effort to collect family information. Being able to store it in an application like TNG makes it easy to view, share and expand. And the prudent researcher keeps a second copy of all the data as insurance, just in case the first copy gets lost or damaged.

TNG is designed for use with a web server, whether it is on a distant host or one you have at home. This makes taking a copy just a little more complicated, but quite within the ability of anyone here.

For our purposes, there are 2 parts to TNG.

  • The first part is the scripts – TNG itself, the application. This is the part you buy and load to your server.
  • The second part is the SQL database, which is created and filled (“populated”) with your data. This is the truly valuable part of your work.

Some of the work involved in backing up requires use of FTP (File Transfer Protocol), a means of moving files to and from a server. If you are not familiar with the process, there are instructions for the most used, see FileZilla_-_FTP_Client or at Using FileZilla or for WinSCP at WinSCP Guide

Depending on your host, you will have other tools available to you. The most common are phpMyAdmin (which provides direct access to the SQL database), and cPanel (a rollup of useful tools, including phpMyAdmin.)

This document will assist you to make backup files. These will not be operative in themselves, but will allow you to restore TNG data if required.

There is a technique of backing up so that your data is operative in a different environment – say, a backup from online to an offline copy of TNG. This topic is reported at MoWeS

Let’s begin by exporting your data. If you have already done this and want to restore your files, please follow this link (this link)

Backup

Method 1 – Using phpMyAdmin

The value of this method is that it gives you a file from which to restore. Let’s begin…

Launch phpMyAdmin
Start phpMyAdmin. You will have been given your username and password by your system administrator (or you have chosen your own, if you are running on a home sever.)
List of databases

Once you have logged in, you will see the main phpMyAdmin screen. Down the left-hand side will be a list of your databases.

This instance of TNG stores its data in yourname_05, so we will back that up. Click on yourname_05

Find the Export tab

The top of the table listing looks like this.

Click on the ‘Export’ tab

Choose the output type

This is the heart of the action. Make sure you can see the list of tables (in blue here), select the ‘SQL’ button, and put a tick in the ‘Save as file’ box. Then (not shown) click the ‘Go’ button.

Different versions of phpMyAdmin have slightly different options. Ignore them, just select SQL.

Save it someplace

Now you will be asked to Open or Save the file. Select Save file, and then click OK. The file, as named, will be saved to wherever you usually save downloads (your desktop, or in your Downloads folder.)

What's in the file?

This is part of a .sql file

Open the newly created .sql file. Scroll down a few lines, and you will see something like this. What is says (in English) is to make a new table, unless one already exists, named 'tng_addresses'. Then, if there is some backed up data, insert it into the table. This file will be used later on to rebuild or copy your database and the data in it.


Recent releases of phpMyAdmin (since about June 2010) have included a 'synchronize' function.
This feature is helpful for anyone who needs to synchronize their database(s) with other database(s). 
This can be used for both structure and data. It also allows subsets of the original database(s)to be synchronized.
Synchronizing within the same server is simple and effective.
Synchronizing between a remote server and another server (e.g., a home server) does not seem possible.

Method 2 – Using cPanel

cPanel provides a quite awesome backup system, enabling replication of data, scripts and other server-side applications, like email.

cPanel main menu
The cPanel provides you with a backup Wizard in the Files section. Click on Backup Wizard.
Backup/Restore Step 1

Step 1: At the left, you have the Backup option, with an explanation of what it can do. Note that the file created is a tar.gz type (which is a sort of zip file), so you might need a competent utility to allow you to see the contents.

Backup/Restore Step 2

Step 2: Time to select what you want to backup. Choose MySQL Databases.

Step 3 takes you to the download action. Usually, cPanel stores the backup on the same server with the original data. See below (FTP) for what to do next.


Method 3 - Using TNG’s internal system

TNG Backup

This method is launched from within TNG.

  • Go to Admin > Utilities
  • Click the Select All button
  • Select Backup from the With Selected pull down list
  • Click the Go button.
  • When the backup has finished, log into your site either by FTP software or through your site's cPanel File Manager, and download a copy of all of the ***.bak files that are in your TNG Backups folder. Save these files to your computer.


Note that TNG provides a Make Folder button in the Admin > Setup > General Settings > Folders and Paths section. You may want to create a new backup folder every time that you upgrade TNG so that you can restore the database in case you need to back off the upgrade.

Method 4 – Using MySQLDumper

This is free software, available from http://www.mysqldumper.net It is a complete backup/restore utility.

Full instructions are provided in the files. In brief, you download the application and FTP it to a new folder on your host server. You then tell it the address, user name and password of your host, and it will connect and show you a list of any databases it finds. From there, you may select some or all of your data. The backup process is very quick, and you will have a file (.sql like the one in Method 1 above) that contains table structure and the current data in that table.

See MySQLDumper

Method 5 - Regular Backups

Dialog in cPanel for cron jobs showing default for weekly task

If you have the ability to create cron jobs (on Linux-based servers), you can set up a regular job to backup your database automatically.

  1. In cPanel, click on Cron Jobs. You may need to scroll down to the Advanced section (if there is one).
  2. Scroll down to Add a New Cron Job and in the Common Settings pulldown select the frequency you want to do the backup. e.g. once per week, once per month etc. This will fill in sample values for all the time values on the left. "All" is represented by a "*".
  3. Adjust the times to something convenient (possibly the middle of the night but avoid midnight and in 1 minute's time)

The key part is now to add a Linux command to do the backup in the Command box. Adjust the following command (on a single line) changing /home/myname to the base directory of your user space on the server. To find the name of the base directory, click on the File Manager icon in cPanel and the home directory is printed at the head of the directory tree on the left hand side.

mysqldump --defaults-file=/home/myname/mysqldefaults.txt my_tng_db | zip > /home/myname/backups/backup-$RANDOM.sql.zip

The following will explain the details of this command in case you need to make any other changes.

  1. mysqldump is the name of the program which should be installed on your server.
  2. --defaults-file= is used to hide the name and password for your mysql server so it doesn't need to appear in the server line (see below). This will avoid security lapses if the results of the command are emailed to you afterwards.
  3. my_tng_db is the name of your database within MySQL.
  4. "|" pipes the output from the dump program to the zip program.
  5. zip compresses the output. You can substitute your favorite program here.
  6. ">" directs the output to the file named afterwards.
  7. /home/myname/backups (no space after) is the name of a directory which you should create before running the program to store the backups, which can be outside the public part of the webserver.
  8. backup-$RANDOM generates a fresh name for the backup each time with digits after the name. You can use the creation time of the file to distinguish each backup.
  9. You can add 2>null to suppress any error messages from the process which would be sent by email to the address specified at the top of the cron jobs section.

When you've put in the command click the Add New Cron Job button and the job will be scheduled. It will then appear in the Current Cron Jobs section below and may be changed at any time by hitting the Edit button opposite it or deleted with the Delete button. When you've made changes hit the Edit Line button to confirm them or Cancel if you don't want to change it.

The default-file named in the command can be used to customize the dump in any way explained in the documentation, but we are using it simply to keep the MySQL user name and password secret. It is a simple text file and you can give it any name you want and it should be in the directory specified. It need only contain three lines (filled in appropriately):

[mysqldump]
user=<name>
password=<password>

The normal behavior is to send an email to the address specified at the beginning of the Cron Jobs section if there is an error in executing the commands, but these can be suppressed as in (9) above.

You can FTP the resultant file to your home system and import it there, or restore it using phpmyadmin. You will need to delete old backups periodically to avoid filling up your space. For very large databases, mysqldump includes the ability to dump specific tables (the --tables option) and there is a more powerful mysqlbackup command available on some systems.

To restore a file on a local server, use the mysql command:

unzip filename.sql.zip | mysql -u username my_tng_db -p

The -p without a password will cause a password prompt to be given by mysql.

If you don't have access to the shell, you can set up a cron job for a once-off use. Choose a time at least 5 minutes in the future with, say, a weekly repeat and delete it after use. Remember to use full path names and use the default-file parameter as with the mysqldump command, but provide an extra [mysql] section in the file.

unzip /home/myname/backups/filename.sql.zip | mysql --defaults-file=/home/myname/mysqldefaults.txt my_tng_db

Saving your TNG scripts

TNG stores most of its data in the SQL database. But some of it is stored in the script area (eg, photos, TNG-generated backups)(and what else?) so you need to consider how far you need to go.

One way to be quite sure is to simply FTP (download) everything to a safe location – eg, your desktop. That gives you 100% surety. Otherwise, if you are confident (and have looked into each of your folders on the server to see if there are any recent files there) just copy what you need.

When ever you do a TNG upgrade, you should also download the TNG full install zip file at the time which would provide a backup of the TNG scripts prior to any modifications you make. Hopefully, you use the TNG Mod Manager to make your changes.

FTP

Methods 2 and 3 (and 4?) leave the backup files on the source server – where the originals are stored. The same goes for your scripts. You need to recover/copy the files from there to a safe (read independent) location so that loss of the original doesn’t lose the backups.

Where to store them is your decision, of course. But please don’t forget to take this step.

That's all for now with BACKUP. RESTORE is at the Restore page.

Related Links

Data Protection and Recovery Plan


Database - Restore

FileZilla_-_FTP_Client

MoWeS

MySQLDumper