There are 2 ways to do a MySQL dump with PHP.

- 1st. Use the mysqldump command to perform a full dump on the database and use PHP system() to execute the command. The problem with this is that not all servers are going to allow a script to perform a system call (for security reasons). The good part is that with not more than 5 lines of code you can generate a dump. You can get more info here.

- 2nd.  Use MySQL command queries to retrieve all tables from the database and retrieve the data inside each table. The only problem with this is that is loop intensive. The good part of this is being able to generate the dump in any server all you need is access to the server.

I have tried the first one and I could not generate any  dump because the server cannot use the PHP system() function to perform the mysqldump, so go to 2nd option and this is the option that I’m going to explain.

I’m going to divide this tutorial in 2 parts. The first one (this one) to explain all parts of the script and the second part (Part II) with the script.

The script is simple. You first need to retrieve the tables of the database to retrieve the data inside them.  You can use mysql_list_tables function, but is deprecated (not recommended to use), so use the following statement:

SHOW TABLES FROM `databasename`;

This is going to retrieve a list of all tables from the database and with this list we can retrieve the structure of each and the data.

Now the issue is going to be storing the information and avoiding the script die in timeout.

To store the information there are 2 ways: file and string. I have used both, so I can print on the screen and save the backup into a file.

I’m checking if the file already exists an in that case, writes on the end of the file, otherwise, creates a new one. This is important because when the script is checking for big tables, it is going to create them later and it’s going to append at the end of the file that was first created.

Now we got the tables and the files, so let’s retrieve the table structure. For that you just need to execute this statement:

SHOW CREATE TABLE `table_name`

This will output the whole create table script in the way that is needed to be for the selected table. Just a tip, always use the ` to wrap database and table names on these scripts, otherwise it will break in MySQL.

Now to create the insert statements, we need all the data and fields from the table. If you have created a export CSV script the concept will be the same.

You will need to:

1. Do a full SELECT on the table
2. Get the number of fields of the table. For this you can use msyql_num_fields($query).
3. Calculate how big the table is (only for performance).

With the SELECT you will retrieve the whole data. With the mysql_num_fields you will retrieve the number of fields of the table, so create the INSERT statement using both. This is the part that is extremely consuming.
Because of this part I’m checking before if the number of rows on the table is bigger than 20,000. In this case I’m saving this table name on a global array (session) and performing only that table latter on. This will avoid the script to die because a timeout.

Last part. Now you have all the tables that have not been processed, so do a loop on the array, and call the function (script before) on that table. It will save on the same file name and the SQL dump will be complete.

To preview if the database is too big you can use the “SHOW TABLE STATUS FROM `dbdatabase`”. This will return all tables and how many rows each table has as it data.

This was a big post, so just click here to get the script.