5.3-Nano: Database Backup: Difference between revisions

From The Uniform Server Wiki
Jump to navigation Jump to search
(New page: {{Nav 5.3-Nano}} '''Uniform Server and Database Backup''' Uniform Servers database backup allows for either manual or periodic backups. Each database is extracted from the MySQL server in...)
 
(Minor grammar changes, and some rewriting for clarity)
Line 2: Line 2:
'''Uniform Server and Database Backup'''
'''Uniform Server and Database Backup'''


Uniform Servers database backup allows for either manual or periodic backups. Each database is extracted from the MySQL server in SQL format and compressed using 7z these are then archived into a single file (7z).
Uniform Server's database backup allows for either manual or periodic backups. Each database is extracted from the MySQL server in SQL format and compressed using 7z.  These are then archived into a single file (7z).


Archive files consist of a base name (changeable) with a time stamp appended. These are saved to folder UniServer\db_backup\archive (automatically created).
Archive files consist of a base name (changeable) with a time stamp appended. These are saved to folder UniServer\db_backup\archive (automatically created).


Archive files can become large hence eat disk space. To minimise this, the archive folder implements a FIFO (first in first out) total number of files is user specified. On reaching this value the eldest file is deleted to make way for a new archive file.
Archive files can become large (they eat disk space). To minimise this, the archive folder implements a FIFO (first in first out) deletion. The total number of files is user specified. On reaching this value the eldest file is deleted to make way for a new archive file.


The above applies to both manual and automatic updates.
The above applies to both manual and automatic updates.
Line 20: Line 20:
<pre>
<pre>
   Fifo = 6                        ; How many archive files to save.  
   Fifo = 6                        ; How many archive files to save.  
   Archive_name = dbs_              ; Archive name a timesramp will be added
   Archive_name = dbs_              ; Archive name (a timestamp will be added)
   Excludes[] = information_schema  ; Do not remove  
   Excludes[] = information_schema  ; Do not remove  
</pre>
</pre>
Line 27: Line 27:
{|
{|
|-
|-
|Fifo = 6||This defines total number of archives to save. When limit is reached eldest archive is deleted to
|Fifo = 6||This defines total number of archives to save. When limit is reached, eldest archive is deleted to


make way for a new archive. Fifo stands for first in first out (deleted)   
make way for a new archive. Fifo stands for first in first out (deleted)   
|-
|-
| Archive_name = dbs||This is part of an archive’s name. A timestamp is added for example: dbs_2009-09-26-18-56-32.7z
| Archive_name = dbs||This is a prefix of an archive’s name. A timestamp is added; for example: dbs_2009-09-26-18-56-32.7z
|-
|-
|Excludes[] = db_name|| If you wish to exclude databases from being backed up add each one to the list with format shown.
|Excludes[] = db_name|| If you wish to exclude databases from being backed up, add each one to the list with format shown.


The fist in this list is '''information_schema''' it is a built-in virtual database which cannot be archived hence excluded.
The fist in this list is '''information_schema'''. It is a built-in virtual database which cannot be archived, so it is excluded.
                        
                        
Example: To exludes databases fred123 and books. List as follows:                         
Example: To exclude databases fred123 and books. List as follows:                         
{|
{|
|-
|-
Line 48: Line 48:
|}
|}
|}
|}
'''''Note'':'''
'''''Notes'':'''


Fifo size is a compromise between database size, disk space and how often backups are required.
Fifo size is a compromise between database size, disk space and how often backups are required.


Six was only a test value hence it needs changing to suit your particular requirements.
Six was only a test value and should be changed to suit your particular requirements.


'''''[[#top | Top]]'''''
'''''[[#top | Top]]'''''
Line 58: Line 58:


=== Manual Update ===
=== Manual Update ===
You can run the following batch file to manually force a backup,
You can run the following batch file to manually force a backup:


Batch file: UniServer\plugins\db_backup\'''Run_db_backup.bat'''  
Batch file: UniServer\plugins\db_backup\'''Run_db_backup.bat'''  


=== Cron - Automatic backups ===
=== Cron - Automatic backups ===
Cron provides a periodic tick to automtically run scripts. The database backup
Cron provides a periodic tick to automatically run scripts. The database backup
 
script has been pre-configured in Cron, however it is disabled by default.
script has been pre-configured in Cron however it is disabled by default.


To enable db_backup edit file UniServer\unicon\main\'''cron.ini'''
To enable db_backup, edit file UniServer\unicon\main\'''cron.ini'''


Locate the db_backup block:
Locate the db_backup block:
Line 93: Line 92:
</pre>
</pre>
|}
|}
Optionally change the start time. If you do not change this the current time will be used. When Cron is initially run db_backup will immediately run thereafter runs every hour.
The current time will be used as the start time unless you change it to a future time.  Since the start time in the example has passed, when Cron is started, db_backup will run immediately and run every hour thereafter.


You can change the period to suit your specific requirements.  
You can change the period to suit your specific requirements.  
Line 99: Line 98:
'''''Note'':'''
'''''Note'':'''


You need to start Cron either manually or automatically refer to [[5.3-Nano: Portable Cron | '''Cron page''' ]]for details.
You need to start Cron either manually or automatically. Refer to [[5.3-Nano: Portable Cron | '''Cron page''']] for details.


'''''[[#top | Top]]'''''
'''''[[#top | Top]]'''''
Line 106: Line 105:
Logging is enabled by default. Log file: UniServer\db_backup\db_backup.log
Logging is enabled by default. Log file: UniServer\db_backup\db_backup.log


Disable enable logging edit file: UniServer\plugins\db_backup\'''db_backup.php'''
To disable/enable logging, edit file: UniServer\plugins\db_backup\'''db_backup.php'''


Change the following line as required:
Change the following line as required:
Line 121: Line 120:
Background 7z was chosen because of its impressive compression and provides a 7-Zip Standalone Console application 7za.exe.
Background 7z was chosen because of its impressive compression and provides a 7-Zip Standalone Console application 7za.exe.


Of real importance it is fully portable.
Of real importance is it is fully portable.


To extract archive files you require 7-Zip Portable this can be found on [http://portableapps.com/ PortableApps website]
To extract archive files you require 7-Zip Portable. This can be found on [http://portableapps.com/ PortableApps website]


Download 7-Zip Portable from the following page:
Download 7-Zip Portable from the following page:
Line 129: Line 128:
(http://portableapps.com/apps/utilities/7-zip_portable)
(http://portableapps.com/apps/utilities/7-zip_portable)


The advantage of using 7-Zip Portable it allows you to view archive content and to extract a single database if you wish.
The advantage of using 7-Zip Portable is it allows you to view the archive content and to extract a single database if you wish.


== Warning ==
== Warning ==
To ensure that archived data can be retrieved and installed always perform several test runs. Create a duplicate server and check backups from prime server can be installed on this server.
To ensure that archived data can be retrieved and installed, always perform several test runs. Create a duplicate server and check that backups from the prime server can be installed on this server.


You don’t want any unexpected surprises when a backup becomes essential.
You don’t want any unexpected surprises when a backup becomes essential.
Line 138: Line 137:
'''''[[#top | Top]]'''''
'''''[[#top | Top]]'''''
== Summary ==
== Summary ==
The script is not written it stone hence change it as required.
The script is not written it stone, so change it as you require.


[[5.3-Nano: msmtp | '''Next page''']] covers configuring msmtp an SMTP client now integrated into Uniform Server.   
[[5.3-Nano: msmtp | '''Next page''']] covers configuring msmtp, an SMTP client now integrated into Uniform Server.   


'''''[[#top | Top]]'''''
'''''[[#top | Top]]'''''

Revision as of 12:10, 25 January 2010

 

Uniform Server and Database Backup

Uniform Server's database backup allows for either manual or periodic backups. Each database is extracted from the MySQL server in SQL format and compressed using 7z. These are then archived into a single file (7z).

Archive files consist of a base name (changeable) with a time stamp appended. These are saved to folder UniServer\db_backup\archive (automatically created).

Archive files can become large (they eat disk space). To minimise this, the archive folder implements a FIFO (first in first out) deletion. The total number of files is user specified. On reaching this value the eldest file is deleted to make way for a new archive file.

The above applies to both manual and automatic updates.

Top

Database Backup Configuration

Edit the following file: UniServer\plugins\db_backup\db_backup.ini

Default settings as shown below: 

  Fifo = 6                         ; How many archive files to save. 
  Archive_name = dbs_              ; Archive name (a timestamp will be added)
  Excludes[] = information_schema  ; Do not remove

Explanation:

Fifo = 6 This defines total number of archives to save. When limit is reached, eldest archive is deleted to

make way for a new archive. Fifo stands for first in first out (deleted)

Archive_name = dbs This is a prefix of an archive’s name. A timestamp is added; for example: dbs_2009-09-26-18-56-32.7z
Excludes[] = db_name If you wish to exclude databases from being backed up, add each one to the list with format shown.

The fist in this list is information_schema. It is a built-in virtual database which cannot be archived, so it is excluded.

Example: To exclude databases fred123 and books. List as follows: 

 Excludes[] = information_schema  ; Do not remove
 Excludes[] = fred123
 Excludes[] = books

Notes:

Fifo size is a compromise between database size, disk space and how often backups are required.

Six was only a test value and should be changed to suit your particular requirements.

Top

Running Database Backup Script

Manual Update

You can run the following batch file to manually force a backup:

Batch file: UniServer\plugins\db_backup\Run_db_backup.bat

Cron - Automatic backups

Cron provides a periodic tick to automatically run scripts. The database backup script has been pre-configured in Cron, however it is disabled by default.

To enable db_backup, edit file UniServer\unicon\main\cron.ini

Locate the db_backup block: 

  ;[db_backup]
  ;start  = 2009-09-21 2:56:52
  ;period = hourly
  ;path   = ..\..\plugins\db_backup\db_backup.php
  ;ref =

Uncomment to enable shown below: 

  [db_backup]
  start  = 2009-09-21 2:56:52
  period = hourly
  path   = ..\..\plugins\db_backup\db_backup.php
  ref =

The current time will be used as the start time unless you change it to a future time. Since the start time in the example has passed, when Cron is started, db_backup will run immediately and run every hour thereafter.

You can change the period to suit your specific requirements.

Note:

You need to start Cron either manually or automatically. Refer to Cron page for details.

Top

Logging

Logging is enabled by default. Log file: UniServer\db_backup\db_backup.log

To disable/enable logging, edit file: UniServer\plugins\db_backup\db_backup.php

Change the following line as required: 

$logging   = true;  // true = Enable logging false = disable logging

Top

Extracting Files from Archive

Background 7z was chosen because of its impressive compression and provides a 7-Zip Standalone Console application 7za.exe.

Of real importance is it is fully portable.

To extract archive files you require 7-Zip Portable. This can be found on PortableApps website

Download 7-Zip Portable from the following page:

(http://portableapps.com/apps/utilities/7-zip_portable)

The advantage of using 7-Zip Portable is it allows you to view the archive content and to extract a single database if you wish.

Warning

To ensure that archived data can be retrieved and installed, always perform several test runs. Create a duplicate server and check that backups from the prime server can be installed on this server.

You don’t want any unexpected surprises when a backup becomes essential.

Top

Summary

The script is not written it stone, so change it as you require.

Next page covers configuring msmtp, an SMTP client now integrated into Uniform Server.

Top

Retrieved from "https://wiki.uniformserver.com/index.php?title=5.3-Nano:_Database_Backup&oldid=4029"