Backup Large mySQL database

[Codehusker]

Hi, I run a decent sized forum (1GB database), and I'm trying to figure out a way to make daily backups. mysqldump is too retourse intensive to run daily, even overnight. Is there a way to make incremental backups?

[NenaDice]

Hi, I run a decent sized forum (1GB database), and I'm trying to figure out a way to make daily backups. mysqldump is too retourse intensive to run daily, even overnight. Is there a way to make incremental backups?

Do you have SSH/Telnet access? if you do, then it would be ebst to backup your dataabse that way. If you do let us know and me or someone else can guide you through on how to do it.

[Codehusker]

Hopefully no one still uses telnet, but yeah I have SSH access.

[NenaDice]

Hopefully no one still uses telnet, but yeah I have SSH access.

Follow these steps and let us know how it will go:

Open your SSH/Telnet client and log into your website. The command line prompt you will see will vary by OS.
For most hosting companies, this will bring you into the FTP root folder.

Type in the following to create a backup in the current directory:

mysqldump --opt -Q -u dbusername -p databasename > backupname.sql

Or to create a backup in a separate directory (signified by /path/to/) type:

mysqldump --opt -Q -u dbusername -p databasename > /path/to/backupname.sql

You will be prompted for the database password. Enter it and the database will backup.

If your hosting company has you on a remote MySQL server, such as mysql.yourhost.com, you will need to add the servername to the command line. The command line will be:

Current directory:

mysqldump --opt -Q -h servername -u dbusername -p databasename > backupname.sql

Separate directory:

mysqldump --opt -Q -h servername -u dbusername -p databasename > /path/to/backupname.sql

[Codehusker]

Thanks for both of your replies, but my database is too large for a full mysqldump on my VPS.

mysqldump is too retourse intensive to run daily, even overnight.

[rct2·com]

If mysqldump (which is the most efficient mysql backup capability) is too resource intensive, I suggest that you need to increase your resources.

To lower resource requirements, you could write a little script that takes your board offline by replacing the index.php with a static page. The script could then run the backup before reinstating index.php.

Anothr thing you could look at are opportunities to reduce the database size by pruning logs, PMs and inactive Members for example.

[.Nuno.]

Or you could use a smaller vps, setup a mysql replication and make the backup from the slave:

http://onlamp.com/pub/a/onlamp/2005/06/16/MySQLian.html
http://www.howtoforge.com/back_up_mysql_dbs_without_interruptions

[Robulosity2]

Hi, I run a decent sized forum (1GB database), and I'm trying to figure out a way to make daily backups. mysqldump is too retourse intensive to run daily, even overnight. Is there a way to make incremental backups?

I find that hard to believe, mysql dump is just going to more or less dump the contents of your mysql db folder into a query structure file..
So if thats taking your VPS down than you either have a horrible VPS, a horrible provider or both

That said, just /etc/init.d/mysqld (or the equiv on your distro) stop than just gzip the contents of the mysql db completely or copy them than zip it after it's done restart mysqld

Your sites that rely on MySQL will fail for the duration