22.12 Backing up databases and DB tables (dump)

It is a good idea to back up your databases or DB tables at regular intervals. You should

This chapter presents a project that backs up a complete database of the DBMS SQLite, PostgreSQL and MySQL in an SQL file. The chosen format has the advantage that you can easily restore the backup if necessary.

Attention:

You must adapt the project to the DBMS used! You will not be able to avoid commenting out or deleting the source code sections that are not required. The MDBS module, in which you define the access data for the database to be backed up and its name, requires particular care.

Here is the source code excerpt from the MDBS.module for the DBMS PostgreSQL:

If Not $DBConPG1.Opened Then
   $DBConPG1.Type = "postgresql" 
   $DBConPG1.Port = 5432
   $DBConPG1.User = "test"
   $DBConPG1.Password = "test"
   $DBConPG1.Host = "localhost"     '-- DBHost is `localhost ` or  an IP adddress
   $DBConPG1.Name = "test"          '-- Name of the database
   If $DBConPG1.User = Null Or If $DBConPG1.Password = Null Then
      Error.Raise(("No database credentials"))
   Endif
   $DBConPG1.Open()
Endif

The programme interface is simply structured:

GUI

Figure 22.12.1: Programme interface

The programme has a programme help function. It also checks whether the programme can access the network. This may be necessary for PostgreSQL and MySQL if you specify an IP address for the host property. It also checks whether the database servers for PostgreSQL and MySQL are installed and started. Syntax errors in the shell statements are also detected and displayed.

B

Figure 22.12.2: Error message: PostgreSQL server not started

B2

Figure 22.12.3: Error message due to a syntax error

The main load in the programme is carried by the procedure DBDumpShell(argDBType As String, argDBName As String) in the module MDBS.module, whose source code is given in full and then commented:

[1]  ' Gambas class file
[2] 
[3]  Private $hProcess As Process
[4]  Private $aErrorMessages As String[]
[5]  ...
[6] 
[7]  '' Argument 1:argDBType -> DBMS type<br>''Argument 2:argDBName -> Database-Name
[8]  Public Sub DBDumpShell(argDBType As String, argDBName As String)
[9]   
[10] Dim sShellCommand, sMessage As String
[11] Dim sDumpFilePath, sDatabasePath As String
[12]     
[13] $aErrorMessages = New String[]
[14] 
[15] Select Case Lower(argDBType)
[16]   Case "sqlite"
[17]     MAdditional.IsInstalled("sqlite3")
[18]     sDatabasePath = Shell$(MCreateDir.DBHost &/ argDBName)
[19]     sDumpFilePath = Shell$(MCreateDir.DataDir &/ "dump_sqlite." & argDBName & ".sql")
[20]     If Exist(sDumpFilePath) Then Kill sDumpFilePath
[21]     
[22]     sShellCommand = Subst("&1 &2 &3 &4 &5", "sqlite3", sDatabasePath, ".dump", " > ", sDumpFilePath)
[23]     
[24]     $hProcess = Shell sShellCommand For Read As "ShellProcess"
[25]     $hProcess.Wait()
[26]         
[27]     If $hProcess.State = 0 And $hProcess.Value = 0 Then 
[28]        sMessage = Subst(("The backup of the SQLite3 database &1 was successful!"), "<br><b>" & argDBName & "</b><br>")
[29]        Message.Info(sMessage)
[30]     Else 
[31]        If $aErrorMessages.Count > 0 Then Message.Error($aErrorMessages.Join("\n"))
[32]     Endif       
[33] '---------------------------------------------------------------------------------------------------------------------- 
[34]   Case "postgresql"
[35]     MAdditional.IsInstalled("pg_dump")
[36]     sDumpFilePath = Shell$(MCreateDir.DataDir &/ "dump_pg_" & argDBName & ".sql")
[37]     If Exist(sDumpFilePath) Then Kill sDumpFilePath
[38] 
[39]     sShellCommand = Subst("PGPASSWORD=&1 pg_dump --blobs --column-inserts", CDBS.DBConPG1.Password)
[40]     sShellCommand &= Subst(" --dbname=&1 --host=&2", argDBName, CDBS.DBConPG1.Host)
[41]     sShellCommand &= Subst(" --port=&1", CDBS.DBConPG1.Port)
[42]     sShellCommand &= Subst(" --username=&1 --file=&2", CDBS.DBConPG1.User, sDumpFilePath)
[43] 
[44]     $hProcess = Shell sShellCommand For Read As "ShellProcess"
[45]     $hProcess.Wait()
[46]         
[47]     If $hProcess.State = 0 And $hProcess.Value = 0 Then 
[48]        sMessage = Subst(("The backup of the PG database &1 was successful!"), "<br><b>" & argDBName & "</b><br>")
[49]        Message.Info(sMessage)
[50]     Else 
[51]        If $aErrorMessages.Count > 0 Then Message.Error($aErrorMessages.Join("\n"))
[52]     Endif
[53]         
[54] '-- Only deletes the history of the *current* session!
[55]     Shell "unset HISTFILE && exit"
[56] '---------------------------------------------------------------------------------------------------------------------
[57]   Case "mysql"
[58]     MAdditional.IsInstalled("mysqldump")
[59]     sDumpFilePath = Shell$(MCreateDir.DataDir &/ "dump_mysql_" & argDBName & ".sql")
[60]     If Exist(sDumpFilePath) Then Kill sDumpFilePath
[61]         
[62]     sShellCommand = "mysqldump --comments --dump-date --no-tablespaces"
[63]     sShellCommand &= Subst(" --host=&1 --port=&2", CDBS.DBConMySQL1.Host, CDBS.DBConMySQL1.Port)
[64]     sShellCommand &= Subst(" --user=&1 -p&2", CDBS.DBConMySQL1.User, CDBS.DBConMySQL1.Password)
[65]     sShellCommand &= Subst(" &1 &2 &3", argDBName, ">", sDumpFilePath)
[66] 
[67]     $hProcess = Shell sShellCommand For Read As "ShellProcess"
[68]     $hProcess.Wait()
[69]         
[70]     If $hProcess.State = 0 And $hProcess.Value = 0 Then
[71]        sMessage = Subst(("The backup of the MySQL database &1 was successful!"), "<br><b>" & argDBName & "</b><br>")
[72]        Message.Info(sMessage)
[73]     Else 
[74]        If $aErrorMessages.Count > 0 Then Message.Error($aErrorMessages.Join("\n"))
[75]     Endif
[76] 
[77] '-- Only deletes the history of the *current* session!
[78]     Shell "unset HISTFILE && exit"
[79]         
[80] End Select 
[81]    If $hProcess Then $hProcess.Kill()  
[82] End
[83] 
[84] Public Sub ShellProcess_Error(argError As String)
[85] 
[86] '-- Print argError
[87]     $aErrorMessages.Push(argError)
[88]     
[89] End 

Comment - related to the DBMS PostgreSQL:

PG_DUMP

Figure 22.12.4: Error message: The pg_dump programme is not installed

OK

Figure 22.12.5: The backup of the PostgreSQL database ‘test’ was successful

It would go beyond the scope of this chapter if further variants of exporting and importing DB data were to be described in detail here. This is also due to the fact that the individual commands for exporting and importing differ considerably for the various DBMSs that are available for backing up complete databases, individual DB tables, schemas and for different backup file formats such as dump*.sql or dump*.csv.

Download