First, Thank You all guys, I really appreciate your great answers.
Second in my experience this is one of the most challenging and frequent
things with mysql on production servers,
once you have the slave practically you have online backups
I will try to answer one by one.
Jake Maul wrote:
If you're making backups of the DB, it might be possible to use the
backup data as a replication snapshot for kickstarting the slave. You
If I only had a kickstart backup....! :))))
You are making backups, right? :)
ehm....the problems is exactly that. On production server you cannot
stop or lock the server so I need
the replication slave mainly for backups (actually MySQL replication is
simply great for this)
Here's another possibility, although not one I would really recommend:
I will give it a try, I am really interested in seeing what happens
locking one table per time.
NOTE:
I have tried Innodb Hotbackup Tool today but it was locking the
production server!
and the strange thing is that it was locking while doing a 'cp' of a
.MYI file, pretty weird,
I would definitely not copy indexes but rather rebuild them offline,
easily on the slave.
from the InnoDB Hot Backup site:
* Online backup of InnoDB tables — the backup takes place entirely
online, without preventing queries or updates.
* Online backup of MyISAM tables — during the backup of InnoDB
tables, read and write access is permitted to MyISAM tables. While
the MyISAM tables are being copied, updates (but not reads) to the
MyISAM tables are precluded.
Jake
Thank you man
===================================================================================
Baron Schwart
We get asked to do this a lot :)
A: (I know!!!)
There's a bunch of different cases.
What storage engines are you using?
A: MyISAM and InnoDB
Do you have LVM with free space
on the volume group, or another way to take snapshots such as a SAN?
A: Let's dont take it as an option since I could have it on this one but not on
other servers(I have about 60 servers in 10 replication clusters)
What I am looking for is a standard clean solution useful in any case and I
also think important for the whole MySQL community.
Sorry if I repeat myself but once you have a slave running you have online
backups(apart from load balancing), and the only difficulty
I see is when you have a production server with a pretty big database and you
cannot stop or lockit.
Innodb Hotbackup today locked production and we had to kill it.
Great Baron, thanks.
========================================================================================
Jed:
If you are using InnoDB, there is a --single-transaction method backup
( http://lists.mysql.com/replication/1235 ) however,
A: I Will consider this
If you are using LVM, you might consider snapshotting,
A: Lets not consider this option
Mysql-hot-copy would probably be better,
A: Yes mysqlhotcopy locks the tables and work only for myisam and archive :(
In contrast, InnoDB actually needs to "shut down" to cleanly close its
table structures before you can physically copy the filesystem.
A: This is the challenge!
I use a method where I flush the tables, firewall off the system, shut
down mysql, do LVM snapshot, start mysql server, and then copy the
snapshot before unfirewalling it.
A: and you do this all on a production server? this is luxury!
I would have a business level meeting with stakeholders.....
A: DEFINITELY!
Good luck!
Jed
Nice from you too Jed!
Again, great to have 'collegues' as you!
Claudio
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
