HOWTO: Auto-backup database | transfer database (with pics)

[galaxyAbstractor]

I am writing this guide for you who are a bit unsure of how to do it, and for those who didn't think of doing regular backups of the database. Remember, backups can save you a lot of trouble, nothing is bugfree, servers can go corrupt and beyond repairable. Under my time as staff I seen a lot of people lose their databases because of different reasons, suspensions, corruption of harddrives etc.

Transferring a database - Restoring a backup
This is a very easy step (unless the database is ridiculously large, if it is, you have to split it in lesser parts for the system to handle).

First, go to the phpMyAdmin where you want to transfer from. phpMyAdmin is often found under the "Databases" category in various panels. If you are unsure where it is, try to ask your host (in case this isn't x10 or your local webserver).

Now, locate the "Export" tab in the top menu. Check or uncheck "Save As File" depending on if you rather copy/paste the data, or upload it as a file when importing it. I would suggest to copy it to clipboard if it's small, and save it as file if it is really large (you don't want a 100 mb copy in your clipboard xD).


This is what is seen when exporting with Save To File unchecked

Now when you have exported it, copy the code. Now go to the phpMyAdmin where you want to transfer the database to, and chose the database you want it to be saved in"

If you choose to copy the sql to your clipboard, go to the "SQL" tab and paste the text into it, and press "RUN". If you choose to save it as file, you press the "Import" tab and upload the file.

To restore a backup, just upload the file in the "Import" tab. If there are already tables in the database, you would need to delete them before (I assume if there is tables in your database, you are restoring a older backup because something went wrong, it's recommended that you take another backup before removing them, if you someday change your mind).

Backup database - Manually
Same as the section before, but you save the SQL to a file, and don't import it.

Backup database - Automatically
I strongly suggest using this method over manually, as it will make your life much easier, after it is setup. The setup can be a bit tricky, but it's 100% doable.

I suggest using http://www.phpmybackuppro.net/download.php - version 2.1.

Upload the files to a direcroy under "public_html", let's call it "backup". CHMOD the export dir to 777 and the global_conf.php to 777.

Now, go to that directory in your browser, and press the "Configuration" tab.

Fill in your mySQL details, you don't need to fill in database or FTP info. The email function seems borked as I wasn't able to get it to work, but you might have better luck I don't know.


The configuration page

Now, to make it backup your databases we need to set up a cron.

Go to your cPanel, and at the bottom choose "Cron jobs". Choose to run it every day or week or so, up to you. Running it every minute will get you suspended, running it every 5 min is pretty useless, and also might get you suspended.

Enter this as command (but modify it for your needs)

php -q  /home/*your cpanel username*/public_html/backup/backup.php *cpanelusername_databasename* 1 1 1

If you have several databases, separate them with a comma, like:

  
php -q  /home/*your cpanel username*/public_html/backup/backup.php  *cpanelusername_databasename1*,*cpanelusername_databasename2* 1 1 1

That's mostly it.

IMPORTANT
Create a .htaccess file in the export directory containing:

order deny, allow
deny from all

[djalam]

interesting read, very nicely done, thanx

[scc123]

I have to say thanks for that, I did have my own script but it wasn't quite as good lol.

[lemon-tree]

1.) For security reasons move the file global_conf.php and the directory export to another directory and maybe change the names.
Best choice is a directory which is not public. (E.g. xxx/Apache/files/)
Attention:
On some systems phpMyBackupPro seems not to get write permissions for the export directory when it is located outside the phpMyBackupPro directory.
In this case don't move the directory but move the global_conf.php file.

Make sure you move global_conf.php as well.