Welcome to the Geeks & God Static Archive. Read more »

mySQL backup questions

Joined: 11/28/2008

Hey all,

I'm working on backing up databases (better sooner than later, right?)... Anyway, I'm wondering what tools you all use? I'll try to explain some of the problems I'm having:

1) Things are being messed up.
2) User error

/laugh.gif" style="vertical-align:middle" emoid=":lol:" border="0" alt="laugh.gif" />

Seriously though... certain characters are not being copied correctly. For example:

QUOTE
'Great Commission'

becomes

QUOTE
?Great Commission?

I've been using the phpMyAdmin functions for exporting the dbase.

As for the user errors, well, practice makes perfect.... /smile.gif" style="vertical-align:middle" emoid=":)" border="0" alt="smile.gif" /> hehe

Anyway, if anyone has any experiance with backing up databases, I would appriciate and tips you may have!

Oh, one last thing I should mention, the actual ".FRM" and ".MYI" files are stored on a server far out of my reach, so I cannot just download the actual dbase :-| Gotta talk to my host about that....

Thanks so much!
- Luke

--
iLuke

Joined: 11/28/2008
Consider using XML for

Consider using XML for backup's. There are free scripts which convert mySQL tables into XML and vice versa. If it's in XML format, then you can go so far as copy the backup to a CD and store it on your shelf.

I don't have any scripts handy, just search hotscripts.com and you should do well.

->Integrity Online: Make the Internet a safe, clean and reliable destination

Joined: 11/28/2008
Hello Luke: I have used

Hello Luke:

I have used PHPmyAdmin for backups for a long time and it works well.

I usually just save it as a .sql file (basically a text file). For Chris, it also has th export to XML option.

So, if I understand correctly, you are getting an error where the ' symbol is becoming a question mark?

Where are you seeing the ? in the text file of the backup or in the website/application after restoring from a backup?

Joined: 11/28/2008
Hello Luke:I have used
QUOTE
Hello Luke:

I have used PHPmyAdmin for backups for a long time and it works well. 

I usually just save it as a .sql file (basically a text file).  For Chris, it also has th export to XML option. 

So, if I understand correctly, you are getting an error where the ' symbol is becoming a question mark?

Where are you seeing the ? in the text file of the backup or in the website/application after restoring from a backup?

Hey guys, thanks for your replys. I hadn't considered XML... I'll look into it /smile.gif" style="vertical-align:middle" emoid=":)" border="0" alt="smile.gif" />

Yes, the ' symbol is being converted to question marks (?). The change happens when I actually export the database (or rather, just the table -- I haven't tried doing the entire database at once yet). So the file that I actually save as the .sql file is "corrupted" (for lack of a better word).

The first time I tried to do a backup, it changed many characters, including: ', ", -, --, and .. (double periods). It's pretty weird. I changed some of the settings (like using the back-quote for field names rather than the normal quote) and that seemed to clear up the problems I was having with the other characters, but not the single quote mark...

I'm using phpMyAdmin 2.2.4

For those of you familiar with phpMyAdmin, here are the options I am using for the export:

Structure and data
Fileds terminated by: ;
Fields enclosed by: "
Fields escaped by:
Lines terminated by: rn
Add 'drop table': yes
complete inserts: yes
Extended inserts: yes
Use backquotes with tables and field' names: yes

Thanks so much!
-Luke

--
iLuke

Joined: 11/28/2008
On these forums all the 's

On these forums all the 's are ?s for me...

e.g.

QUOTE
When you don?t know where to post your web development issues, post here.

I always thought that was a bit odd - I guess this must be why. :? /biggrin.gif" style="vertical-align:middle" emoid=":D" border="0" alt="biggrin.gif" />

Blessings
Chris

Joined: 11/28/2008
Do yopu have the option of

Do yopu have the option of using the commandline for backups? It's a much cleaner backup then.

CODE
mysqldump -uusername -p databasename > db.sql

Then you can import it using

CODE
mysql -uusername -p dbname < db.sql

Aaron

Aaron Brazell
"I would Rather live my life on the edge, confident of the grace of God, than live my life in a pietistic manner." ~Rich Mullins
Emmense Technologies |

Joined: 11/28/2008
Do yopu have the option
QUOTE
Do yopu have the option of using the commandline for backups? It's a much cleaner backup then.
CODE
mysqldump -uusername -p databasename > db.sql

Then you can import it using

CODE
mysql -uusername -p dbname < db.sql

Aaron

nope, thanks so much for the suggestion, but this isn't an option for me :-|

Thanks for the idea though!
- Luke

--
iLuke

Joined: 11/28/2008
g'day one and all, just

g'day one and all, just digging up a very old post here /smile.gif" style="vertical-align:middle" emoid=":)" border="0" alt="smile.gif" />

I am trying to set up a script that will automatically backup a clients databases... this is what I have currently, courtesy of sitepoint's book (The PHP Anthology):

class:

CODE
<?php

    class MySQLDump
    {
        private $cmd;
        
        public function __construct($dbUser, $dbPass, $dbName, $dest, $zip = 'gz')
        {
            $zip_util = array('gz' => 'gzip', 'bz2' => 'bzip2');
            if(array_key_exists($zip, $zip_util))
            {
                $fname = $dbName . '.' . date('w') . '.sql.' . $zip;
                $this->cmd = 'mysqldump -u' . $dbUser . ' -p' . $dbPass . ' ' . $dbName . '| ' . $zip_util[$zip] . ' >' . $dest . '/' . $fname;
            }
            else
            {
                $fname = $dbName . '.' . date('w') . '.sql';
                $this->cmd = 'mysqldump -u' . $dbUser . ' -p' . $dbPass . ' ' . $dbName . ' >' . $dest . '/' . $fname;
            }
        }
        
        public function backup()
        {
            exec($this->cmd, $error);
            if($error)
            {
                trigger_error('Backup failed: ' . $error);
            }
        }
    }

?>

Usage:

CODE
<?php

    //include class
    require_once('MySQLDump.class.php');
    
    $db =     array(
                'dbUser' => '****',
                'dbPass' => '****',
                'dest' => '****',
                'zip' => 'bz2'
            );
    
    //cmm_com_au_mediscreen
    $mysqlDump = new MySQLDump($db['dbUser'], $db['dbPass'], 'cmm_com_au_mediscreen', $db['dest'], $db['zip']);
    $mysqlDump->backup();
    
    //cmmmigrate_com_au_b2b1
    $mysqlDump = new MySQLDump($db['dbUser'], $db['dbPass'], 'cmmmigrate_com_au_b2b1', $db['dest'], $db['zip']);
    $mysqlDump->backup();
    
    //cmmmigrate_com_au_training
    $mysqlDump = new MySQLDump($db['dbUser'], $db['dbPass'], 'cmmmigrate_com_au_training', $db['dest'], $db['zip']);
    $mysqlDump->backup();

?>

It seems to work fine, creates 3 files, but this is the contents of each file:

CODE
-- MySQL dump 10.13  Distrib 5.1.23-rc, for redhat-linux-gnu (i686)
--
-- Host: localhost    Database: cmm_com_au_mediscreen
-- ------------------------------------------------------
-- Server version    5.1.23-rc-community-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 */;

Can anyone guide me as to the possible cause of no data being exported?

Thanks

Patrick

Joined: 11/28/2008
Oh, old post lol :$You've

Oh, old post lol :$

You've really dug this one up Pat!

Wondering if you can set up cron jobs, via cPanel or otherwise, as this would be a simple shell script to create automatic scheduled backups.

~Andrew~

Joined: 11/28/2008
what happens when you do it

what happens when you do it yourself on the shell?

Paul Davey
Whitford Church
"Everyone who calls on the name of the Lord will be saved." Romans 10:13
"For all have sinned and fall short of the glory of God, and are justified

Joined: 11/28/2008
You can set up a bash script,

You can set up a bash script, and call it on a cron job to automatically back up the mysql dump file to your server. You could probably even have the script call it to e-mail the dump file to you, depending on the size of the backup. Most e-mail servers have a limit on the size of file attachments that can pass through. Here's a link to a tutorial on backups with a bash script and cron:

http://www.base64.co.uk/mysql-dump-backup/

Joined: 11/28/2008
I ran into the same problem

I ran into the same problem with not having permissions to run mysqldump on a shared host. To get around it, I created a PHP script to mimic what mysqldump does. I posted about it on my blog a week ago or so. The Bash script is a good idea too if you can set it up as a cron job.

Unable to Use mysqldump on a Shared Host? Here's an Alternative