Backing Up - How to do it..!

[Mikey]

Well one of the more common questions I see asked here on xenfans, and also something my clients often ask me is "How do I back up my database".

In this thread I hope to provide a little mini guide which caters for most people, whether you have cPanel, or direct command line access.

With SSH access, you can simply type this into the command line:
mysqldump --opt -Q -u dbusername -p databasename > backupname.sql



If you don't have SSH access, but do have cPanel, you can also go to yourdomain.com/cpanel - choose the backup manager, and only choose the database backup option.




If you don't have cPanel, but have access to phpMyAdmin, you can simply load your database, dont click any tables, and click the "Export" button. Make sure every table is checked, and download as a sql file





Edit - For windows server users, check out this useful blog post; http://bobschwarz.com/batch-file-to-backup-your-windows-server-database/

 

[Floris]

To ensure a proper back up I strongly recommend to get shell access and use the mysqldump method

 

[Mikey]

Oops. I forgot screenshots, I've just updated the first post with visual representations of what I was talking about.

 

[Mikey]

For the more "advanced" members, who also are no longer on shared hosting, there are a number of scripts posted in this topic which will allow you to create a daily backup cronjob, so you have no worries about data loss. For those who are on a limited space VPS or Dedicated solution though, make sure that you clear out your backups directory every so often, as databases can get big (I have around 25 db's in my MySQL server, all over 50MB each).

 

[South Fork]

From shell, after closing the forum and other applications referencing the forum DB...

mysqldump --user=USERNAME --password=PASSWORD --quick --add-drop-table --add-locks --extended-insert --lock-tables --all DATABASE | gzip > /home/path/path/backup/DATABASE-`date -I`.gz

 

[Floris]

I would recommend against including the password in the shell. Shared hosting and otherwise compromises being able to cat .bash_history will disclose the password incrementing compromise on the system.

 

[GoMySQL]

I wrote a shell script some time ago to do most of this for me.

I've modified it a bit (it will be attached)

There are also other (far better) alternatives for backups, but they are a tad more complicated. If you'd like hot backups, you can use XtraBackup from Percona (Hover over the Software link at the top, I think).

EDIT:
Duh, forgot to attach it.

 

[trilogy33]

What I love about XenForo (sorry slightly off topic here) is that the actual (example) db.sql is so small, with not carrying the bulky attachments by default, is that it takes a tiny amount of time even to manually run a cPanel backup.
Ideal for newbs. >>> *gives a knowing nod*

 

[Floris]

Just don't forget to backup data/

 

[trilogy33]

Just don't forget to backup data/

Oh definitely.

 

[Steven Moore]

Nice discussion here, I have always used BigDump which works for any size of sql files. I don't like doing it through SSH too much and my cPanel takes forever to backup so I use that PHP script and its secure just be sure to password protect the directory so no one else can steal your databases.

 

[trilogy33]

I would recommend against including the password in the shell. Shared hosting and otherwise compromises being able to cat .bash_history will disclose the password incrementing compromise on the system.

I seem to recall bash_history gathering up passwords, collating them with the commands as it went?
Unless one manually deletes them of course.

 

[Floris]

If you have a shell and you type commands in the bash shell, .. like

cd
cd backups
./back.sh --pass=something --database=somethingelse
cd
ls -all

then you can log out, and log back in .. and type:

cd
cat .bash_history |grep "pass"

and the line from back.sh where you manually typed in the pass , will come back, disclosing it.

 

[GoMySQL]

Quick fix to that problem:

Make a .logout file that executes:

> ~/.bash_history #Yes, just like that.