MySQL Backup using PDO

[mongoose00318]

What is the best way to perform automated backups of an entire MySQL database using PDO?

[benanamen]

The best way to do DB backups is not with PDO. What you want to do is write a shell script that acts directly on the DB and set up a CRON job to run it at whatever intervals.

[mongoose00318]

@benanamen Okay, I was only asking because somethings there are events that happen in the software where I would want a DB backup triggered before any alteration of the data is allowed. I would want regularly timed backups as well; as you suggested above.

[kicken]

You can still trigger backups whenever you want, it's just the PDO is the wrong tool for the job. 

Backups for mysql are typically done by using the mysqldump program to generate a SQL script containing all the database content.  You would just need to execute this whenever you want a backup made.

If your DB gets large, the a backup using mysqldump could take a while to run.  There are other backup methods that could be explored then.

[benanamen]

You may also want to look into Temporal Data Tables or Audit Logging depending on your specific needs.

[mongoose00318]

@kicken How would I active a shell script through PHP if I wanted to trigger the backup based on certain events that happen?

@benanamen Audit Logging looks really interesting. I'd probably need something that parses the logs and makes it easier to search them, etc...something like Monyog? I'm not too familiar with a lot of these more advanced performance and logging features of MySQL. Any additional advice would be welcome!

[kicken]

2 hours ago, mongoose00318 said:

How would I active a shell script through PHP

exec()