RE: how to make replication without taking master down

Let this be a lesson to others -- If you need a 24/7 database, your initial setup must include slave(s). It is too painful or error prone to convert later.

Getting a consistent snapshot while writes are occurring is essentially unsolved with Mysql out-of-the-box. There _may_ be third party packages can do it. They would probably grab all the rows (without locking), meanwhile watching for updates. It is complicated.

For minimal downtime in the future, consider dual-master, single-writer. With such, you can flip between them reasonably fast, and you can take the non-writing one offline for maintenance, upgrades, etc. If you have heavy read traffic, add pure slaves off each of them.

Regardless, you cannot achieve sub-second downtime in all situations; sub-minute is usually achievable.

But then, you are vulnerable to power failures (including the UPS catching on fire), earthquakes, controller failures (SPOF in RAID systems), switcher failure, network outages, etc. Plus the need to reboot virtually anything for an upgrade -- think switch.

> -----Original Message-----
> From: Jay [mailto:waikitz@gmail.com]
> Sent: Thursday, July 19, 2007 8:29 AM
> To: replication@lists.mysql.com
> Subject: how to make replication without taking master down
>
> i have a production mysql server which can't be taken down
> even for one sec.
> i know the basic step to make a replication, but i have trouble making
> a consistent snapshot without taking down mysql server or do a READ
> LOCK.
> i dump my master DB with the following command(it uses innodb):
> mysqldump -q -A -F --single-transaction --no-autocommit
> --master-data=1 > test.sql
> then rsync the test.sql to slave server,import it:
> mysql < test.sql
>
> then i tried to "start slave",soon after the slave try to catch up
> with master,it runs into an duplicated key error. I've also tried to
> dump the DB with "-B AAA BBB" instead of "-A",but it still ran into
> the same error.
> If I bring down the master server and do the same thing,i dont get any
> error and sucessfully have the slave catch up with my master.
>
> does any one have idea how to achieve the goal? thx a lot!
>
> --
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe:
> http://lists.mysql.com/replication?unsub=rjames@yahoo-inc.com
>
>

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