MySQL ODBC Connector - Database backups

Author

Database backups

George Law

2006-01-23, 9:23 am

Just a quick question regarding mysql backups.

I know myisam tables are portal from machine to machine, but I know
there are some differences
Between innodb tables.=20

I am running mysql 5.0.18 on suse linux 10. I have innodb set up so it
stores each table in its own
..idb file. =20

I've read that innodb tables are not portable from server to server, my
question is if I grab the whole
mysql/data directory, can it be restored back on the same computer in
the event of a crash. Do I need
to enable binlog to do this?

I plan on giving myself about a 1 hour maintenance window where all my
import scripts skip importing and
then just copying the entire mysql/data directory to a back up server
where I will tar/gzip the data and push
it out to a back up directory so it will get dumped to tape.

--
Geo

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Gleb Paharenko

2006-01-24, 7:23 am

Hello.

Have a look here:
http://dev.mysql.com/doc/refman/5.0/en/backing-up.html

George Law wrote:
> Just a quick question regarding mysql backups.
>
> I know myisam tables are portal from machine to machine, but I know
> there are some differences
> Between innodb tables.
>
> I am running mysql 5.0.18 on suse linux 10. I have innodb set up so it
> stores each table in its own
> .idb file.
>
> I've read that innodb tables are not portable from server to server, my
> question is if I grab the whole
> mysql/data directory, can it be restored back on the same computer in
> the event of a crash. Do I need
> to enable binlog to do this?
>
>
> I plan on giving myself about a 1 hour maintenance window where all my
> import scripts skip importing and
> then just copying the entire mysql/data directory to a back up server
> where I will tar/gzip the data and push
> it out to a back up directory so it will get dumped to tape.
>
> --
> Geo
>

--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

sheeri kritzer

2006-01-26, 4:56 pm

The quick answer is: it depends. If you read in the manual the
documentation for innodb and myisam tables, you'll see that they're
both stored in an operating-system (somewhat) independent way.

http://dev.mysql.com/doc/refman/5.0/en/moving.html

That references the page Gleb replied with, but it also gives a bit more de=
tail.

-Sheeri

On 1/23/06, George Law <glaw@ionosphere.net> wrote:
> Just a quick question regarding mysql backups.
>
> I know myisam tables are portal from machine to machine, but I know
> there are some differences
> Between innodb tables.
>
> I am running mysql 5.0.18 on suse linux 10. I have innodb set up so it
> stores each table in its own
> .idb file.
>
> I've read that innodb tables are not portable from server to server, my
> question is if I grab the whole
> mysql/data directory, can it be restored back on the same computer in
> the event of a crash. Do I need
> to enable binlog to do this?
>
>
> I plan on giving myself about a 1 hour maintenance window where all my
> import scripts skip importing and
> then just copying the entire mysql/data directory to a back up server
> where I will tar/gzip the data and push
> it out to a back up directory so it will get dumped to tape.
>
> --
> Geo
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql? unsub...mail
.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

alexj

2006-01-26, 4:56 pm

On Mon, 23 Jan 2006, George Law wrote:

> Just a quick question regarding mysql backups.
>
> I know myisam tables are portal from machine to machine, but I know
> there are some differences
> Between innodb tables.
>
> I am running mysql 5.0.18 on suse linux 10. I have innodb set up so it
> stores each table in its own
> .idb file.
>
> I've read that innodb tables are not portable from server to server, my
> question is if I grab the whole
> mysql/data directory, can it be restored back on the same computer in
> the event of a crash. Do I need
> to enable binlog to do this?

Yes you can do a restore if you backup the mysql data directory. But
ensure that the server is down for a consistent backup.

Binlogs are required only when you have , if you want to trach the updates
and some point in time recovery issues. So binlogs req might be based on
the backup / recovery strategy.

>
>
> I plan on giving myself about a 1 hour maintenance window where all my
> import scripts skip importing and
> then just copying the entire mysql/data directory to a back up server
> where I will tar/gzip the data and push
> it out to a back up directory so it will get dumped to tape.

The time required will be based on the database size.

Thanx
Alex
MySQL DBA
Yahoo!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links