List:Replication« Previous MessageNext Message »
From:Jan Bruvoll Date:November 23 2007 10:12am
Subject:Re: replication on Linux broken after db restore
View as plain text  
Depends how you make that dump. If you follow the description below and 
lock the tables, and then try and make a dump with mysqldup, nothing 
much will happen since mysqldump won't get access to the tables. My 
quick-and-dirty solution to this is to create a small script that runs 
mysqldump and then immediately after does a show master status on the 

If your problem is that you have too short windows to use mysqldump, I 
would recommend that you have a look at LVM. I've been through three 
stages of mysql backup:
 1) mysqldump - this is very slow in any case, and especially if you 
cannot afford locked tables
 2) locked tables and a file copy "underneath" mysql - this is ok for a 
while, but also ends up being too slow for high traffic sites
 3) the current solution - LVM snapshots. this solution gives me 100% 
consistent data tables, 1 sec of lock time (since I need to check what 
the slaves are up to), and then "all the time in the world" to do my 
backups and release the snapshot.

Give me a shout off-list if you need further hints.

Best regards

FMGreen wrote:
> "make a dump of master database"
> Hi there,
> I gave your solution a go this morning.  However, the 'dump of master
> database' after locking it was either hanging or taking forever when usually
> dumps take only a minute.  Unfortunately I have very little time when I can
> block access to the system to work on it so I had to kill the process and
> give up until my next admin window.  Can you tell me if it is usual for a
> locked database to take longer to dump or would you suspect that something
> was not right.
> Thanks!
> martín arrieta wrote:
>> Hi,
>> If you have all config (in my.cnf files) done. You can do:
>> In master db run:
>> +---------------+----------+--------------+------------------+
>> | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
>> +---------------+----------+--------------+------------------+
>> | mysql-bin.003 | 73       | test         | manual,mysql     |
>> +---------------+----------+--------------+------------------+
>> make a dump of master database
>> mysql> UNLOCK TABLES;
>> Then you have to restore the slave db from the master db dump
>> In slave db run:
>>     ->     MASTER_HOST='master_host_name',
>>     ->     MASTER_USER='replication_user_name',
>>     ->     MASTER_PASSWORD='replication_password',
>>     ->     MASTER_LOG_FILE='mysql-bin.003',
>>     ->     MASTER_LOG_POS=73;
>> (replace this values with the result of "show master status command" )
>> mysql>START SLAVE;
>> done
>> Anyway I recommend you to check
>> it is a
>> really
>> good guide.
>> Bye.
>> On 21/11/2007, FMGreen <> wrote:
>>> Hi there,
>>> I have inherited two Redhat replicated servers running a helpdesk app
>>> (the
>>> person who set them up has left the company) and replication has
>> broken.  I
>>> know next to nothing about mysql/replication so am in need of help.  The
>>> problem seemed to originate with a crash which caused a duplicate entry
>> into
>>> the db.  Anyway, I have restored the db on each server (using the same
>>> mysqldump) so they should be identical.  The restore is from Nov
>> 14th.  What
>>> is confusing me is that when I do 'show slave status' it gives an error
>>> in
>>> the database with a 'ticket create date' (as I said, it's a helpdesk
>> system)
>>> of Nov 16th - 2 days later than my restore.  It's as if replication is
>>> picking up a cached version of the original database rather than using
>>> the
>>> restored version.  I'm a newbie so if anyone could shed some light on
>>> what
>>> is happening it would be much appreciated.  Many thanks!
>>> --
>>> View this message in context:
>>> Sent from the MySQL - Replication mailing list archive at

replication on Linux broken after db restoreFMGreen21 Nov
  • Re: replication on Linux broken after db restoremartín arrieta21 Nov
    • Re: replication on Linux broken after db restoreFMGreen23 Nov
      • Re: replication on Linux broken after db restoreJan Bruvoll23 Nov
      • Re: replication on Linux broken after db restoreMarcus Bointon23 Nov
        • Re: replication on Linux broken after db restoreFMGreen23 Nov
    • Re: replication on Linux broken after db restoreFMGreen23 Nov
  • Re: replication on Linux broken after db restoreChristian Schramm21 Nov
    • Re: replication on Linux broken after db restoreFMGreen22 Nov
      • Re: replication on Linux broken after db restoreChristian Schramm22 Nov