[Box Backup] backing up mysql database

Wed, 27 Oct 2004 00:57:14 -0700

This is a multi-part message in MIME format.
--------------080009010104020905000608
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 10/27/04 12:36 AM, Martin Ebourne wrote:

>On Wed, 2004-10-27 at 06:16, Michel CERDINI wrote:
>  
>
>>  Now I wanted to backup a big mysql database. If I put /var/lib/mysql in 
>>the bbackupd.conf, the whole database will be backuped regularly, so it 
>>will take a lot of place on the server, isn't it ? Or when I read "Only 
>>changes within files are sent to the server, just like rsync", does it mean 
>>that only changes within files are also STORED on the server side ?
>>    
>>
>
>Yes, as of 0.8 only changes will be stored on the server, so storage
>requirements should not be too high.
>
>If it does work out that it is still taking up too much space then one
>possibility is to dump the database out to text regularly and back that
>up instead. Typically that will diff well and it'll be compressed on the
>server too. Not sure how to do that with mysql, but pg_dump will do that
>for PostgreSQL. I'm sure you'll be fine backing up the raw files though.
>
For MySQL, I use mysqlhotcopy and back up the hot-copy files. I don't 
think it would be a good idea to back up the database files directly, 
since they will most likely be in an inconsistent state.

I have a script that is cron'ed to run once a day (or more if you like) 
looks something like this:

#!/bin/bash

/usr/bin/mysqlhotcopy --quiet --allowold mysql /home/mysqlbackups
/usr/bin/mysqlhotcopy --quiet --allowold foo /home/mysqlbackups
/usr/bin/mysqlhotcopy --quiet --allowold bar /home/mysqlbackups

Hope this helps.

Thanks,
Per Thomsen

-- 
Per Reedtz Thomsen | The Reedtz Corporation | F: 209 883 4119
V: 209 883 4102    |   pthomsen@reedtz.com  | C: 209 996 9561
GPG ID: 1209784F   |  Yahoo! Chat: pthomsen | AIM: pthomsen

--------------080009010104020905000608
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
On 10/27/04 12:36 AM, Martin Ebourne wrote:
<blockquote cite="mid1098862572.30453.56.camel@avenin.ebourne.me.uk"
 type="cite">
  <pre wrap="">On Wed, 2004-10-27 at 06:16, Michel CERDINI wrote:
  </pre>
  <blockquote type="cite">
    <pre wrap="">  Now I wanted to backup a big mysql database. If I put /var/lib/mysql in 
the bbackupd.conf, the whole database will be backuped regularly, so it 
will take a lot of place on the server, isn't it ? Or when I read "Only 
changes within files are sent to the server, just like rsync", does it mean 
that only changes within files are also STORED on the server side ?
    </pre>
  </blockquote>
  <pre wrap=""><!---->
Yes, as of 0.8 only changes will be stored on the server, so storage
requirements should not be too high.

If it does work out that it is still taking up too much space then one
possibility is to dump the database out to text regularly and back that
up instead. Typically that will diff well and it'll be compressed on the
server too. Not sure how to do that with mysql, but pg_dump will do that
for PostgreSQL. I'm sure you'll be fine backing up the raw files though.</pre>
</blockquote>
For MySQL, I use mysqlhotcopy and back up the hot-copy files. I don't
think it would be a good idea to back up the database files directly,
since they will most likely be in an inconsistent state. 
 
I have a script that is cron'ed to run once a day (or more if you like)
looks something like this: 
 
#!/bin/bash 
 
/usr/bin/mysqlhotcopy --quiet --allowold mysql /home/mysqlbackups 
/usr/bin/mysqlhotcopy --quiet --allowold foo /home/mysqlbackups 
/usr/bin/mysqlhotcopy --quiet --allowold bar /home/mysqlbackups 
 
 
Hope this helps. 
 
Thanks, 
Per Thomsen 
 
 
 
<pre class="moz-signature" cols="72">-- 
Per Reedtz Thomsen | The Reedtz Corporation | F: 209 883 4119
V: 209 883 4102 | <a class="moz-txt-link-abbreviated" href="mailto:pthomsen@reedtz.com">pthomsen@reedtz.com</a> | C: 209 996 9561
GPG ID: 1209784F | Yahoo! Chat: pthomsen | AIM: pthomsen
</pre>
</body>
</html>

--------------080009010104020905000608--