List:Replication« Previous MessageNext Message »
From:Johan De Meersman Date:July 18 2013 12:13pm
Subject:Re: 2 slaves - one Master; continues to fail
View as plain text  
Please leave the list in CC, Jamen - other people may either learn something or add their
two cents. 

I'm not particularly familiar with innobackupex; but what you're showing sounds to me as
if the data dictionary isn't in sync with what's on disk. You see the table, because
that's mysqld's province: there's a diglib/systemlog.frm file in your datadir. 

However, try to select from the table. If that fails, too, it's likely that the InnoDB
dictionary thinks the table isn't there, or it has a different internal identifier than
the one in your (new) datafile. Again, I'm not familiar with innobackupex, maybe someone
else can shed some light. 

----- Original Message -----

> From: "Jamen McGranahan" <jamen.mcgranahan@stripped>
> To: "Johan De Meersman" <vegivamp@stripped>
> Sent: Thursday, 18 July, 2013 1:51:19 PM
> Subject: RE: 2 slaves - one Master; continues to fail

> Johan,

> Sorry for the continued messages. I * thought * I had this beat last
> night after reading through the various options in the innobackupex.
> I issued the following command, which I thought was going to work.
> It only took a couple of hours and did not put too much of a stain
> on the server:

> innobackupex --defaults-file=/opt/pkgs/mysql/my.cnf --rsync
> --safe-slave-backup --compress --compress-threads=4 --user=USER
> --password=PWD /apps/Dbbackup/jamen/

> Then I tar’d & gzipped the data in
> /apps/Dbbackup/jamen/2013-07-17_21-51-42, then scp’d it to my slave
> server. On the slave, I unzipped & unpacked the file and made it my
> data directory for mysql. I checked the xtrabackup_binlog_info to
> get the right filename & starting location and issued this CHANGE
> statement in mysql:

> CHANGE MASTER TO MASTER_HOST='mysql.library.vanderbilt.edu',
> MASTER_USER='slave3', MASTER_PASSWORD=’PWD’,
> MASTER_LOG_FILE='mysql-bin.000470', MASTER_LOG_POS=671478277;

> Then started the slave and it seemed to work – at least for a few
> minutes. Now I’m getting errors like this:

> Error 'Table 'diglib.systemlog' doesn't exist' on query. Default
> database: 'diglib'. Query: 'INSERT INTO systemlog
> (TimeStamp,IP,SubSystem,Page,Message,Count,RecordID,Referrer,OperatorID,CustomerID)
> VALUES (NULL,'180.76.5.99','archiveimages','ProcessQuery','the Title
> of the volume includes <strong>Livro de Óbitos de Escravos e Livres
> (1854-1867), parte 4, 1865-09-20 a 1867-12-05, Freguesia de Nossa
> Senhora da Assumpção de Cabo Frio</strong>',98,NULL,'',0,0)'

> Which doesn’t make sense because if I issue USE diglib; and then SHOW
> TABLES;, the table is listed! Ugh! I just don’t know what I am doing
> wrong! Any ideas would be greatly appreciated. Thanks!

> Jamen McGranahan
> Systems Services Librarian
> Vanderbilt University Library

> From: Johan De Meersman [mailto:vegivamp@stripped]
> Sent: Wednesday, July 17, 2013 3:44 AM
> To: McGranahan, Jamen
> Subject: Re: 2 slaves - one Master; continues to fail

> Hah, yeah. I guess nobody thought to mention this, but --master-data
> does cause a global lock, as it needs a consistent snapshot.

> Assuming this is on *nix, use either disk snapshots or rsync. You'll
> need plenty of diskspace for rsync, though, as well as two separate
> consoles - one logged in to mysql and one terminal.

> Snapshots are easy:

> * mysql> flush tables with read lock
> * $> lvm create snapshot mysql-data (or whatever syntax)
> * mysql> show master status
> * mysql> unlock tables

> Rsync is slightly more troublesome:
> * mysql> flush tables (not required, but pushes maximum to disk pre
> first copy - read up on flush for possible downsides)
> * $> rsync -KaP /data/mysql /snap/mysql (initial copy to reduce lock
> time)
> * mysql> flush tables with read lock
> * $> rsync -KaP /data/mysql /snap/mysql (update the copy with the
> flushed data - may still take a moment, but a lot less than the
> first rsync)
> * mysql> show master status
> * mysql> unlock tables

> You now have a consistent snapshot of your datafiles and the exact
> master position of your snapshot. Apply to slaves (stop replication,
> set master position, shutdown mysqld, copy files, startup mysqld,
> check replication)

> Try the dump procedure on a slave first, if you can - that'll give
> you a feel for the lock times involved.

> Also, note that InnoDB isn't quiesced quite entirely with that flush
> tables statement, so the slave will start recovery. No data should
> be lost (I never had problems) but it *is* technically possible. For
> 100% clean snapshots, you'll need to shut down the mysqld entirely
> before snapshotting.

> ----- Original Message -----

> --

> Unhappiness is discouraged and will be corrected with kitten
> pictures.
-- 

Unhappiness is discouraged and will be corrected with kitten pictures. 

Thread
2 slaves - one Master; continues to failJamen McGranahan14 Jul
  • Re: 2 slaves - one Master; continues to failSuresh Kuna14 Jul
    • RE: 2 slaves - one Master; continues to failJamen McGranahan14 Jul
      • Re: 2 slaves - one Master; continues to failSuresh Kuna14 Jul
  • RE: 2 slaves - one Master; continues to failJamen McGranahan15 Jul
    • RE: 2 slaves - one Master; continues to failJohan De Meersman15 Jul
    • Re: 2 slaves - one Master; continues to failTruls Bergskaug15 Jul
  • Re: 2 slaves - one Master; continues to failJesper Wisborg Krogh15 Jul
    • Re: 2 slaves - one Master; continues to failDivesh Kamra16 Jul
Re: 2 slaves - one Master; continues to failJohan De Meersman15 Jul
  • RE: 2 slaves - one Master; continues to failJamen McGranahan15 Jul
    • Re: 2 slaves - one Master; continues to failJohan De Meersman15 Jul
      • Re: 2 slaves - one Master; continues to failSuresh Kuna15 Jul
        • RE: 2 slaves - one Master; continues to failJamen McGranahan15 Jul
          • Re: 2 slaves - one Master; continues to failSuresh Kuna15 Jul
            • RE: 2 slaves - one Master; continues to failJamen McGranahan15 Jul
              • Re: 2 slaves - one Master; continues to failSuresh Kuna15 Jul
Re: 2 slaves - one Master; continues to failJohan De Meersman17 Jul
Re: 2 slaves - one Master; continues to failJohan De Meersman18 Jul
  • RE: 2 slaves - one Master; continues to failJamen McGranahan18 Jul
    • Re: 2 slaves - one Master; continues to failMarcus Bointon18 Jul
      • RE: 2 slaves - one Master; continues to failJamen McGranahan18 Jul
        • Re: 2 slaves - one Master; continues to failSuresh Kuna18 Jul
          • RE: 2 slaves - one Master; continues to failJamen McGranahan18 Jul
        • Re: 2 slaves - one Master; continues to failMarcus Bointon18 Jul
          • RE: 2 slaves - one Master; continues to failJamen McGranahan18 Jul
            • Re: 2 slaves - one Master; continues to failMarcus Bointon18 Jul
              • RE: 2 slaves - one Master; continues to failJamen McGranahan18 Jul
              • RE: 2 slaves - one Master; continues to failJamen McGranahan22 Jul
                • Re: 2 slaves - one Master; continues to failSuresh Kuna22 Jul