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
master.
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
Jan
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:
>> mysql> FLUSH TABLES WITH READ LOCK;
>>
>> mysql > SHOW MASTER STATUS;
>> +---------------+----------+--------------+------------------+
>> | 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:
>> mysql> CHANGE MASTER TO
>> -> 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
>> http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html it is a
>> really
>> good guide.
>>
>> Bye.
>>
>> On 21/11/2007, FMGreen <florinda.green@stripped> 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:
>>>
>>
> http://www.nabble.com/replication-on-Linux-broken-after-db-restore-tf4850053.html#a13876896
>>
>>> Sent from the MySQL - Replication mailing list archive at Nabble.com.
>>>
