Automatic backup of the MySQL database

In this article we'll see how to do an automatic backup of the database to generate a zip file directly from the system.

The most recommended is exporting the database as a dump SQL then make it as download link.

The export will be complicated to do manually through the PHP, but it’ll be very simple through command prompt or shell.

The MySQL contains an app that permits to connect, export and import SQL data from the database.

In case you use Linux, the export is simpler, because the MySQL will generate a compact dump.

Example of the command:

mysqldump -uroot -plookupsc. nm_scriptcase5nm | gzip > nm_scriptcase5nm.sql.gz (export compact file).
mysqldump -uroot -plookupsc. nm_scriptcase5nm > nm_scriptcase5nm.sql (export file not compact).

After the test of the command, do the same command in the scriptcase using the macros: shel_exec(), exec() and others.

http://php.net/manual/en/function.shell-exec.php
http://php.net/manual/en/function.exec.php

Example:

shell_exec("mysqldump -uroot -plookupsc. nm_scriptcase5nm | gzip > nm_scriptcase5nm.sql.gz");

In case you want to specify the directory you want to save the generated file, inform the full location before the name of the new file.

shell_exec("mysqldump -uroot -plookupsc. nm_scriptcase5nm | gzip > /folder to saved/nm_scriptcase5nm.sql.gz");