Automatic Backup of Mysql Database from VFP

[Jay9988]

Hi Experts,

I need to construct the feature of automatic backup of Mysql Database from my VFP Application.
This feature will periodically backup the database (in example: for every 5 hours or other period of time)

My Idea is to create a form with timer Event to execute mysqldump command using ShellExecute()

Is there any best option?

Many thanks

 

[Chris Miller]

That's the job for a cronjob, not the VFP application using a database.

More generally, the job of a backup is always strongly related to the database and best done with the means of itself, not applications using the server or database, even if they are the only systems using the database and it seems logical to you that's a feature of the application.

Just like you don't let Word do backups of its docx files, your mail client make backups of its mails, the audio play make backups of the mp3 files, but use and configure a backup of the whole system. So a backup of all databases off a server is the natural thing to configure and have as a server-side job done periodically.

Chriss

 

[Mike Lewis]

As with any back-end database, backing up a live database is never as simple as copying individual tables and indexes. There are quite a few factors to take into account, not least of which is whether you are able to halt the database during the backup to prevent the data being updated in a way that might break data integrity. Your approach will also depend on whether you plan to do incremental backups for full backups, and the format and location of the backed up data.

There are a couple of good articles in the MySQL documentation that discuss these issues:

Backup and Recovery Types

Database Backup Methods

In general, the advice is that backups should be done on the server, using the relevant features of MySQL, rather than in the client (VFP in this case).

Mike





__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Gerrit Broekhuis]

I’ve created a systree application in VFP that creates a logical backup (tables + data), even with multiple databases. It has a scheduler to perform back-ups at a specific time each day.

This has been running now for quite a few years without any problem.

Regards, Gerrit

 

[Chris Miller]

Gerrit, that's fine, as your VFP backp tool is a) scheduled by the system task scheduler, that's what's a cronjob is, and b) is not the application.

In fact I have also managed an application were the original developer was doing the backup of a database when the last user left the application on a day. But that has caveats. If a user left the app open no backup was done.

So I still stand by the recommendation that the app that uses a database, even the only app using it, should not be made responsible for the backup task. It's not because of it being technically impossible, but having a flaw of logic means no backup is done. So the reponsibility of the backup is on the server system. Now it depends on whether it being a Linux or Windows server, what tool is available for cronjobs.

If you have MySQL on a Windows server, maybe even on premise then I think cron is still available through the MySQL server itself as it is an always running service and has an event scheduler option within it:

https://dev.mysql.com/doc/refman/8.0/en/events-overview.html.

But you could use the Windows task scheduler. And the straightforward backup tool is indeed mysqldump. That could also be started from the task scheduler without needing a specific VFP exe for that, as a cmd/bat file. But surely you won't start the application.exe to use it's backup feature. You're overall not depending on VFP and using VFP here boild down to the conveniece of knowing VFP in and out. If your VFPmysqlbackup.exe only uses myqldump, that's just crying out loud you don't want to write batch files.

But let's go further and look into the options of MySQL backups. As far as I know MySQL's backup documentation it says that myssqldump is a best option aside from binary file backups at least from time to time - let me lookup where it says so.

Found it at

https://dev.mysql.com/doc/refman/8.0/en/innodb-backup.html

Logical Backups Using mysqldump
In addition to physical backups, it is recommended that you regularly create logical backups by dumping your tables using mysqldump. A binary file might be corrupted without you noticing it. Dumped tables are stored into text files that are human-readable, so spotting table corruption becomes easier. Also, because the format is simpler, the chance for serious data corruption is smaller.

And while that's written in the topic about InnoDB backup specifically, this is a good recommendation generally.

The documentation also says (

https://dev.mysql.com/doc/refman/8.0/en/backup-types.html):

...Physical backup methods are faster than logical ones because they involve only file copying without conversion.

So you can also look from that perspective and not use mysqldump for every backup but use a combination of physical (binary) backups, also incremental backups and use mysqldump for logical backups. A plan could use a full physical backup every 8 hours, an incremental backup every hour except every 8th hour, and once a day a mysqldump. Don't underestimate how the database can grow and spike up the time necessary for a mysqldump.

After the quote I gave from

https://dev.mysql.com/doc/refman/8.0/en/innodb-backup.html

this paragraph continues:

mysqldump also has a --single-transaction option for making a consistent snapshot without locking out other clients.

So that makes it viable to use during application and database usage, too. But it's not an argument of using it for every backup, you could get to a day where this snapshot mysqldump then backs up is taking it long to keep up the snapshot in memory, and that reduces the memory available for caching and other MySQL optimizations. It's, therefore, better to only need this once each day, even with this option. Especially as you want to back up more than once a day. Higher frequency backups also ask for incremental backups only. In MSSQL Server you'd do that for log backups also to be able to truncate the log more often.

In a very large system, I'd also rather do replication and have the replicated database or even multiple replications as both safety copies and therefore backups and also for high availability and performance by load balancing queries to multiple instances. You can also easily do a backup of a replicated version without any influence on the main database server and performance. But that would all point to an Enterprise MySQL license and NDB cluster usage. I doubt you're anywhere near such a MySQL usage.

Chriss

 

[Gerrit Broekhuis]

Hi Chriss,

Thanks for your comments. My back-up application is not the user application and is running on the server (not on a client).

We decided to use our own scheduler as we found out that the Windows task scheduler can get corrupted, with the result that no backu-ups are made. We don’t want to do a system’s engineers work to fix the task scheduler, so it’s much easier to support our own scheduled back-up application.

Another option can be to use NAVICAT for MySQL maintenance and automated backups.

Regards, Gerrit

 

[Mike Lewis]

Another factor to take into account is whether you only want to back up the data, or the whole database schema. In other words, do you envisage having to recreate the database, and all the tables, indexes, etc., before restoring the data?

This is in fact what my own MySQL backup does. Essentially, the backup is a text file containing all the SQL code needed to recreate the database and restore the data. However, I run this manually from the admin console, not as part of a VFP application, which is not what you want.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

My back-up application is not the user application and is running on the server (not on a client).

The comment about not using the application to run the backup was directed to Jay, not you. He thought of a form with a timer in his application (presumably). That would not do well, even within an extra EXE. Just imagine that tsk being killed and the backups don't run.

I don't know how the task scheduler or a task within the scheduler can become corrupted, so maybe thanks for pointing that out. I don't have that experience and wonder if you're just victim of misinformation about that, for example by a vendor of a scheduling alternative. I have seen so many things.

I also mentioned MYSQLs ability to schedule tasks in itself. Well, and there surely are many third-party tools doing backups, I usually would not use them when there is no striking advantage. There could easily be. I can see a motivation to do a tool in VFP itself, too, even without task scheduler, just to have a tool with a user/admin friendly interface to plan backups. But believe me, admins also know their part of the job and actually dislike tools like that as they are used to standards of Windows and while such tools might be intuitive to use for beginners, they just hide away what they actually do unless they are at least as admin friendly as writing out batch files the admin can then manage or modify with their toolbelt.

Chris

 

[Chris Miller]

Unrelated to MySQL but to illustrate a point:

I know of the case of a graphics card driver installation with an update feature that created a Windows user in the admin group just to enable automatic driver updates. I'm not a programmer of hardware drivers, but regarding administration and updates, I'm sure there are better procedures. It's good for home end users, untechnical users, or small businesses to not need to think about MySQL administration and when your software also installs a MySQL server for your database also taking the responsibility to do backups is fine, but think on a larger scale when your software should be considered by a corporation with an IT department they want to keep all administrative tasks in their hands and knowledge, alone scheduling times can be a point they'd like to adapt to their schedules, too.

With that, I want to refer back to what I recommended in the last two posts of thread184-1820044. If everyone thinks about encapsulating a complete solution, you end up with a lot of MySQL installations on a client or on servers. That already has been a problem with SQLExpress or way back with the Microsoft SQL Server Data Engine that also was part of VFP7. It leads to many server instances that could all be done with one, just because application developers were not thinking about involvement with an existing administration.

Chriss

 

[Jay9988]

Hi all,

Thank you so much for your expert opinion.

Like @Chriss and @gerrit discussion, Actually, I do have similar idea that mysqldump from my VFP Application will run on the server.
At the beginning, I just want to make easier solution for my client, since they do not have personnel to task database administration.
And if use VFP to wrap the mysqldump command, then the client do not have to know the admin user and password to mysql database.

Following up all of your opinion, I will put the cronjob at first priority then.
@Mike, thank you very much for the article. And I have been thinking also the issue database integrity if I schedule mysqldump periodically. And if I am not misreading, the answer is to use MySQL Enterprise Backup (MEB)

I am aware that the most recent version is MEB version 8 for MySQL Server 8, and in the documentation said MEB version 4.1 is available for Mysql 5.7. Since my SQL server is still on Mysql 5.0, is anyone know if the MEB 4.1 can also be used for Mysql Server 5.0?

Many thanks again guys...

 

[Chris Miller]

MySQL server is still on Mysql 5.0

Seriously? Upgrade the server, even if it's not in the internet and just on their server, you can't use an outdated version with security vulnerabilites.

they do not have personnel to task database administration.

the client do not have to know the admin user and password to mysql database.

Imagine you have an accident. They need to know the password to be able to let a successor of you service thee application. That's not just a case of not knowing anthing about administration. It's a case for full documentation and I don't even know what to say when they themselves don't care about this topic of your passing out of the whole project.

Really, the point is that anyboddy taking over the maintenance of the overall system should not be confronted with a situation that beriddles them with off norm structuring, i.e. the backup of a databse being done by an EXE that becomes a scary dependency because only it knows the database credentials. That's wrong thinking about security.


Chriss

 

[Jay9988]

HI Chriss,

Thank you for sharing your point of view.