Page 1 of 1

Backup and Restore from Lazarus, Zeos and MySQL solved

Posted: 18.04.2009, 19:53
by aigartua
I am using Lazarus 9.27, Zeos 6.6.4 stable and MySQL 5.1
The initial idea was to make 2 procedures (backup and restore) from the application, the backup was done using mysqldump and the restore with ZQuery, I try to make it with ZSQLProcessor but it stop working, the code is the following:

For Backup:

Code: Select all

procedure TMain.BackupClick(Sender: TObject);
const
   READ_BYTES = 2048;

var
  command: TProcess;
  s: string;
  output: TStringList;
  BytesRead, n: LongInt;
  m: TMemoryStream;

begin
  if not (ZConnection1.User = 'root') then begin
    ShowMessage('Only root could access.');
    Exit
  end;
  SaveDialog1.Free;
  SaveDialog1 := TSaveDialog.Create(Self);
  SaveDialog1.InitialDir:= GetUserDir + 'My Documents\';
  SaveDialog1.Filter:='Backup Files|*.bfl';
  if SaveDialog1.Execute then begin
    s := SaveDialog1.FileName;
    if not (copy(s,Length(s)-3,Length(s)-1) = '.bfl') then
      s := s + '.bfl';
    command := TProcess.Create(Nil);
    output := TStringList.Create;
    M := TMemoryStream.Create;
    BytesRead := 0;
    command.CommandLine := 'c:\mysqldump -u root -p'+ZConnection1.Password+' -h '+ZConnection1.HostName+' database';
    command.Options := command.Options + [poUsePipes];
    command.Execute;
    while command.Running do begin
      // ensure the space
      M.SetSize(BytesRead + READ_BYTES);
      // we try to read
      n := command.Output.Read((M.Memory + BytesRead)^, READ_BYTES);
      if n > 0 then
        Inc(BytesRead, n)
      else
        // whitout data, wait 100 ms
        Sleep(100);
    end;
    // we read the last part
    repeat
      // ensure the space
      M.SetSize(BytesRead + READ_BYTES);
      // we try to read
      n := command.Output.Read((M.Memory + BytesRead)^, READ_BYTES);
      if n > 0 then
       Inc(BytesRead, n);
    until n <= 0;
    M.SetSize(BytesRead);
    output.LoadFromStream(M);
    output.SaveToFile(s);
    output.Free;
    command.Free;
    M.Free;
    ShowMessage('Backup ready.')
  end else
    ShowMessage('Backup canceled.');
end;
For Restore:

Code: Select all

procedure TFMain.restoreClick(Sender: TObject);
var
  s: string;
  tfFile: TextFile;
begin
  if not (ZConnection1.User = 'root') then begin
    ShowMessage('Only root could access.');
    Exit
  end;
  OpenDialog1.Free;
  OpenDialog1 := tOpenDialog.Create(Self);
  OpenDialog1.InitialDir:=GetUserDir+'My Documents\';
  OpenDialog1.Filter:='Backup Files|*.bfl';
  if OpenDialog1.Execute then begin
    AssignFile(tfFile, OpenDialog1.FileName);
    if not FileExists(OpenDialog1.FileName) then
      exit;
    reset(tfFile);
    ZQuery1.SQL.Clear;
    readln(tfFile,s);
    While not eof(tfFile) do begin
      Readln(tfFile,s);
      if length(s) > 0 then begin
        s := StringReplace(s, #10, '', [rfReplaceAll]);
        s := StringReplace(s, #13, '', [rfReplaceAll]);
        s := StringReplace(s, #9, ' ', [rfReplaceAll]);
        if not ((s[1]+s[2]) = '--') then begin
          ZQuery1.SQL.Add(s);
          if s[length(s)] = ';' then begin
            ZQuery1.ExecSQL;
            ZQuery1.SQL.Clear
          end
        end
      end
    end;
    CloseFile(tfFile);
    ShowMessage('Restauration done.');
  end else
    ShowMessage('Restauration canceled.');
end;
Sorry for my english. i Hope this help you.

Posted: 19.04.2009, 20:12
by mdaems
aigartua,

Thanks for your contribution. I hope others can use it as well.

I have a few additional comments. Just see what you want to use from it. :)
- For the backup, why don't you just redirect the output to the output file by adding '>'+filename. No need to read standardoutput by your program and writing it to a file again.
- For the restore : consider using Connection.ExecuteDirect. That saves you the overhead of the TDataset descendent TZQuery. Additional benefit is that this method returns a boolean result and eventually returns the number of affected rows.
- Second sugestion for the restore. If you want to depend on mysqldump for the backup you could also use the mysql.exe client for the restore as well. No need for zeoslib components at all then. Not for backup, not for restore.

Finally, there exist some very old components based on zeoslib, named MyBackup. I just added it to the Downloads/Contributions section of this forum. Have a look at it. It may be a good idea to adapt it to the current Zeoslib component version.

Mark

About coments

Posted: 20.04.2009, 04:13
by aigartua
of course I try to use > filename, but for some reason mysqldump send my a error (that the database '>' or '>filename' or '>' and 'filename' does not exist) .

About restore, well a lot a people is using mysqldump for the backup, this procedure works for that backup files.

I am going to check the old control, I hope to help.

Tanks for you opinion.

Posted: 18.09.2010, 13:11
by putranovan
tyvm ,,
restore database work great in D7 but not for backup , didn't find TProcess..