Database - Backup
|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 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)
Method 1 – Using phpMyAdmin
The value of this method is that it gives you a file from which to restore. Let’s begin…
|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.)|
Once you have logged in, you will see the main phpMyAdmin screen. Down the left-hand side will be a list of your databases.
The top of the table listing looks like this.
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.
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.)
This is part of a .sql file
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.
|The cPanel provides you with a backup Wizard in the Files section. Click on Backup Wizard.|
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.
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
This method is launched from within TNG.
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.
Method 5 - Regular Backups
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.
- In cPanel, click on Cron Jobs. You may need to scroll down to the Advanced section (if there is one).
- 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 "*".
- 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.
- mysqldump is the name of the program which should be installed on your server.
- --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.
- my_tng_db is the name of your database within MySQL.
- "|" pipes the output from the dump program to the zip program.
- zip compresses the output. You can substitute your favorite program here.
- ">" directs the output to the file named afterwards.
- /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.
- 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.
- 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):
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.
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.