SQL database backups via cron

[bmolnar]

I want to make backups of my site's database every few days via a cron job. What is the best way to go about doing this? I've founds some PHP scripts and some shell scripts that both look like promising. Would either one work on a shared account? Thanks

[stocktrader]

Your code

 

mysqldump -Q -h localhost -u DB_Username -pDB_Password DB_Name > path/backup.sql

 

The code I am using is this:

 

mysqldump --opt -u DB_Username -pDB_password DB_Name | gzip > path/mqsql_dump.gz

 

I'm not familiar with the syntax of cron job commands (I just Googled the code above). I was wondering if there was any significant difference between our codes. For example, "-Q" and "-h"?

[Michael D.]

Yep, all that does is gzip the .sql

[cnywrestling]

I set this command up as a cron (just copied and pasted it), but all I get in the backup files are:

 

-- MySQL dump 10.13  Distrib 5.1.54, for unknown-linux-gnu (x86_64)
--
-- Host: localhost    Database: cpg
-- ------------------------------------------------------
-- Server version	5.1.54-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

Don't see an error anywhere, but that's all I get.

 

Any ideas?