Most of the time, you’re going to be able to backup your MySQL database using the control panels. Sometimes that process just doesn’t work. It can just fail outright. Sometimes it times out if you’ve got a pretty big database. To get around these issues, having a PHP script in place that will help you invoke a MySQL dump to give you a backup file in your private directory is the solution that you need. You can also set up the PHP to dump one or more of your tables or just a select number of your databases if you have more than one on a server.
1. A Simple, Basic Script
One of the easiest ways to accomplish this is to just add your own script to the existing PHP file that you have so that you can get the backup done. No matter what platform you’re using for MySQL, the following line can be added to your PHP file so that you can run the backup by visiting the PHP URL through your browser.
/user/local/www/vhosts/YOURDOMAIN.COM/private/BACKUPFILENAME.sql”);
?>
2. Use MySQL Hotcopy
If you are performing a backup on your server and the tables that you have on that server are all MyISAM tables, then you can use MySQL Hotcopy in order to do the backup as well. This process gives you a number of different options to backup the tables, including the ability to keep the target direct name intact, insert checkpoint entries, or even reset the binary log after you lock all of the tables.
If you have anything on your server besides the MyISAM tables, however, you won’t be able to use this process.
3. Use Exec() For External Commands
Depending on the system that you are using, you may need to utilize an execute command in your scripts to be able to successfully get the file dump to start. That’s typically seen in Linux systems and it would also allow you to run it directly from the internet. In this instance, your script would look like this instead.
exec(‘mysqldump -user= –password= –host= DATABASE_NAME > /path/to/output/file.sql’);
4. Use Shell Commands
In the open source MySQL system, you might want to setup the option of being able to download something specific instead of having to download the entire server. This is especially beneficial if certain tables are updated daily, while others might be updated weekly, monthly, or on a different timing system. To do so, you might consider utilizing the Shell commands with your script, as you can see below.
shell> mysqldump [options] database_name [table_name]
shell> mysqldump [options] -databases name
shell> mysqldump [options] -all-databases
If you want to dump an entire database, then just don’t name any tables following the database name. You can also use the all-databases option if you prefer. It is important to note that the mysqldum won’t include the information-schema database unless you specifically name it in the command line and you’ve got to use the skip/lock/tables option to do it.
