List:Replication« Previous MessageNext Message »
From:Jamen McGranahan Date:July 18 2013 12:19pm
Subject:RE: 2 slaves - one Master; continues to fail
View as plain text  
Here’s what I had followed:

http://www.percona.com/doc/percona-xtrabackup/2.1/howtos/setting_up_replication.html

Jamen McGranahan
Systems Services Librarian
Vanderbilt University Library

From: Johan De Meersman [mailto:vegivamp@stripped]
Sent: Thursday, July 18, 2013 7:14 AM
To: McGranahan, Jamen
Cc: Replication
Subject: Re: 2 slaves - one Master; continues to fail

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.

________________________________
From: "Jamen McGranahan" <jamen.mcgranahan@stripped<mailto:jamen.mcgranahan@stripped>>
To: "Johan De Meersman" <vegivamp@stripped<mailto: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 Assumpsn’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.


________________________________



--
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