How To Backup MySQL Database?

[RrCoX22]

I read the sticky as I was somewhat confusing. I have cPanel X and just created a forum and want to know how to I suppose back it up to my computer as it seems to be a concern.

[Steve Machol]

The only consistently reliable method of backing up and restoring a database is with shell access via telnet or ssh. This is because backing up with a PHP script like that in the Admin CP or phpMyAdmin can result in PHP timeout errors and an incomplete backup file.

Please see the instructions here:

Backup:
http://www.vbulletin.com/docs/html/maintenance_ssh_backup

Restore:
http://www.vbulletin.com/docs/html/maintenance_ssh_restore

If you don't have telnet or SSH access, some people have reported success with these scripts:

MySQLDumper:
http://www.mysqldumper.de/en/index.php

MySQLHotxcopy:
http://www.vbulletin.com/forum/showthread.php?t=134821&highlight=mysqlhotcopy

P.S. cPanel does have it's own database backup and restore function which I have used in the past and seemed to work fine.

[Dark Lord]

I read the sticky as I was somewhat confusing. I have cPanel X and just created a forum and want to know how to I suppose back it up to my computer as it seems to be a concern.

Same here, thought I'd try my first upgrade today and found it pretty confusing too.

cPanel seems like the easiest option. In fact, it seems rather too easy which makes me wonder why others don't use this option. What's wrong doing it this way exactly ?

[eva2000]

Same here, thought I'd try my first upgrade today and found it pretty confusing too.

cPanel seems like the easiest option. In fact, it seems rather too easy which makes me wonder why others don't use this option. What's wrong doing it this way exactly ?
I guess it's just peace of mind.. i know i prefer to use mysqldump than rely on other backup scripts.

If your forum database is important, then it doesn't hurt to have more than one backup method either :)

[blackit]

for small boards (small databases in general) mysqldump is IMO the best way to go.

For big boards (database greater then 1 GB) mysqldump locks the DB for too much time (if you omit lock options you can have inconsistent backups). In this case I prefer to have a mysql slave to handle backups, you can use it in case of failure of the main db server too.
Mysql slave data is not a backup it's just a nice way to run mysqldump against it without locking the main db (and consequentally block all the users from accessing the forum).

All the ways in the middle are (IMO) a compromise with the reliability of the backups done (LVM snapshots without DB locks for example...).

and never forget that backups are more important then the data itself you've to backup :)

[masterross]

For big boards (database greater then 1 GB) mysqldump locks the DB for too much time (if you omit lock options you can have inconsistent backups). In this case I prefer to have a mysql slave to handle backups, you can use it in case of failure of the main db server too.
Mysql slave data is not a backup it's just a nice way to run mysqldump against it without locking the main db (and consequentally block all the users from accessing the forum).



Hi,

Can you explain how to do it?

[blackit]

it's not a thing to explain in a post; you can google "how to setup mysql replication"; eg. look at this page (http://www.howtoforge.com/mysql_database_replication)

[Hayk]

I am sure the best way is SSH. Choose the host that have SSH or ask your host to enable it and do backups and restores via SSH, using e.g. Putty, no matter your db is small or large. SSH is safe, easy and highly reliable:)

[Dark Lord]

I contacted my web space provider today and asked them if they did backups themselves. They said they did them daily, weekly and monthly.

What is the point in me making backups if they keep them too ?

[blackit]

your web space provider can make backups in the same way you do and with the same problems.

I've seen many providers doing backups through a simple copy of files (including db files). this could work but can create huge inconsistencies and you could discover when it's too late that the backup was not done properly

there isn't any secret way to take backups from a single live mysql installation without creating either locks or inconsistencies.

usually I suggest to not give too trustness to providers - it's always better to know what is happening and what are the emergency procedures (if you care about your data)