Backup: Copy MYSQL Directory or use MysqlDump

[Andrew Rosolino]

I want to create a backup for my MySQL database every single night.

I am currently running on a linux box. I have a backup drive located as /backup

Would it be more effecient to use mysqldump tool, or use the unix command "dump or cp"

Andrew

[ViSolve DB Team-2]

Hi,

Hope I have faced this:

If we copy the files with 'cp' command, the permissions will not be
retained.  You have to assign it on restoring.  But in the mysqldump
utility, everything are retained as it is.  Hope, mysqldump utility provides
more options related to db than that of 'cp' command.

Thanks
Visolve DB Team.
----- Original Message -----
From: "altendew" <andrew@...>
To: <mysql@...>
Sent: Tuesday, January 23, 2007 6:10 AM
Subject: Backup: Copy MYSQL Directory or use MysqlDump

>
> I want to create a backup for my MySQL database every single night.
>
> I am currently running on a linux box. I have a backup drive located as
> /backup
>
> Would it be more effecient to use mysqldump tool, or use the unix command
> "dump or cp"
>
> Andrew
>
> --
> View this message in context:
> http://www.nabble.com/Backup%3A-Copy-MYSQL-Directory-or-use-MysqlDump-tf3061490.html#a8513292
> Sent from the MySQL - General mailing list archive at Nabble.com.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=mysqlsupport@...
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[Alex Arul Lurthu-2]

Hi,

MySQL Dump is logical. Hence it is mostly slower than locking the MyISAM
tables and copying them or shutting down the server and taring the entire
MySQL directory if you are using innodb. If you are using innodb tables only
you can run mysqldump with --single-transaction option to take a consistent
backup even when the server is running. Hence both has advantages and
disadvantages. It will more of depend on your use case.

Thanks
Alex

On 1/23/07, ViSolve DB Team <mysqlsupport@...> wrote:

>
> Hi,
>
> Hope I have faced this:
>
> If we copy the files with 'cp' command, the permissions will not be
> retained.  You have to assign it on restoring.  But in the mysqldump
> utility, everything are retained as it is.  Hope, mysqldump utility
> provides
> more options related to db than that of 'cp' command.
>
> Thanks
> Visolve DB Team.
> ----- Original Message -----
> From: "altendew" <andrew@...>
> To: <mysql@...>
> Sent: Tuesday, January 23, 2007 6:10 AM
> Subject: Backup: Copy MYSQL Directory or use MysqlDump
>
>
> >
> > I want to create a backup for my MySQL database every single night.
> >
> > I am currently running on a linux box. I have a backup drive located as
> > /backup
> >
> > Would it be more effecient to use mysqldump tool, or use the unix
> command
> > "dump or cp"
> >
> > Andrew
> >
> > --
> > View this message in context:
> >
> http://www.nabble.com/Backup%3A-Copy-MYSQL-Directory-or-use-MysqlDump-tf3061490.html#a8513292
> > Sent from the MySQL - General mailing list archive at Nabble.com.
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=mysqlsupport@...
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=alex.lurthu@...
>
>

[Daniel da Veiga]

On 1/23/07, Alex Arul <alex.lurthu@...> wrote:
> Hi,
>
> MySQL Dump is logical. Hence it is mostly slower than locking the MyISAM
> tables and copying them or shutting down the server and taring the entire
> MySQL directory if you are using innodb. If you are using innodb tables only
> you can run mysqldump with --single-transaction option to take a consistent
> backup even when the server is running. Hence both has advantages and
> disadvantages. It will more of depend on your use case.
>

Alex is right. Depending on how you'll want to be able to restore, and
how big your database is you may use different methods for backup. I
usually use mysqldump along with bzip2 for maximum compression, but I
have used tar in the past. "cp -av" retains permissions, making it
easy for restore, but its not a good option, as you can't compress
data nor restore pieces of information in an easy way.

You'll have to test and choose from multiple options in this matter.

--
Daniel da Veiga
Computer Operator - RS - Brazil
-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
------END GEEK CODE BLOCK------

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[Andrew Rosolino]

I used mysqlhotcopy and all is fine.

Daniel da Veiga wrote:

On 1/23/07, Alex Arul <[e-mail]> wrote:
> Hi,
>
> MySQL Dump is logical. Hence it is mostly slower than locking the MyISAM
> tables and copying them or shutting down the server and taring the entire
> MySQL directory if you are using innodb. If you are using innodb tables only
> you can run mysqldump with --single-transaction option to take a consistent
> backup even when the server is running. Hence both has advantages and
> disadvantages. It will more of depend on your use case.
>

Alex is right. Depending on how you'll want to be able to restore, and
how big your database is you may use different methods for backup. I
usually use mysqldump along with bzip2 for maximum compression, but I
have used tar in the past. "cp -av" retains permissions, making it
easy for restore, but its not a good option, as you can't compress
data nor restore pieces of information in an easy way.

You'll have to test and choose from multiple options in this matter.

--
Daniel da Veiga
Computer Operator - RS - Brazil
-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
------END GEEK CODE BLOCK------

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=[e-mail]