Automatic Backup of Database

I want to implement an automatic MySQL backup for my free plan account.

Since it is not offered by the plan itself, I tried to implement it by myself using SELECT * INTO OUTFILE, but I get this error: Access denied for user 'epiz_12345678'@'192.168.0.%' (using password: YES).

I think this is because, as said by @Admin on another post, the database is stored on a different server

Is there a way to make it work? A backup of all my data is essential and cannot manually click on export inside *phpMyAdmin * every 6 hours…

No because Admin said that the database is stored on a different server.

@katufo so how can I backup the database? Should I loop through every single table and copy it?

Read this:

Backup Database backup including

Importing

1 Like

SELECT ... INTO OUTFILE is not allowed. That’s not just a permissions issue: if we would let you run that query, you would just create a file on the database server, to which you don’t have file access.

Theoretically, it should be possible to create automatic backups from PHP on a remote server. After all, that’s what phpMyAdmin does as well. But I don’t know of any pure PHP backup tools.

Another issue you’ll run into that there is a 20 second PHP script time limit on our hosting. If the backup takes longer than that, it’s killed. And if you’re going to run it through our cron jobs, it’s not even going to last that long. And that’s not to mention the database and CPU load of making a full database dump multiple times per day.

This is the part where I’m supposed to say premium hosting is better. But to be honest, having your own, off site backups on premium hosting isn’t fun either. Being able to run mysqldump from cron really helps, and so does remote database access. But it’s not going to be clean and pretty either way.

In my experience, having your own off-site backups is easiest if you’re also the one maintaining the database servers. If not, then relying on your provider to take care of your database is usually the best option.

4 Likes

I wouldn’t care about timing because there’s an option for SELECT INTO OUTFILE for backing up only the new lines (I don’t actually remember the syntax but I’m pretty sure about it). Furthermore, I’m using Easycron for cron jobs because of their increased flexibility…

Anyway, I did not understand this part:

mysqldump is not allowed on free hosting since it is a shell script right?

And what do you mean with relying on my database provider?

you could place a txt file on your server say database.txt, then use fopen(‘database.txt’, ‘w’), then use standard php to populate the database text file with your database export copy, this then can be used with create a copy on another server using the database.txt file

1 Like

You can’t run any command line tools here, which includes mysqldump.

So it’s not about being allowed to or not, but being able to use it at all even if you tried.

If you’re using a hosting service which includes database hosting, then you should trust that your hosting service has taken precautions to prevent data loss on their servers. If you can’t trust them with that, then maybe consider not using them to host important data.

I know that this goes against the general advice of “always keep your own backups”. But I’ve yet to see the first managed database service that makes it easy to make backups to your own, off site location.

4 Likes

Hey @SimonePellegatta. Backuping Databases and other user data is your responsibility as a client. If there is automatic backup tool then it is too much for free services.

That’s not about the fact of trusting my host provider, but about the fact that everyone makes error, including me.

So the reason why I want to make backups is to prevent a typo inside a query or a simple misclick from completely destroying my website.

That said, I think that at this point I will periodically make manual backups… As @JavesPotato said, I cannot expect too much from a free service, which actually does its job pretty well

1 Like
Summary

All I mean is:

  1. As a client, we need to do manual backups in client level.
  2. In the network level, they’re also do backups but it still not safe(corruption problems).

Read the TOS

Terms of Service - InfinityFree.

3 Likes

@JavesPotato thank you very much, I didn’t notice that…

This topic was automatically closed 15 days after the last reply. New replies are no longer allowed.