Errors after starting new slave server replication using Percona XtraBackup's backup

MySQL & MariaDB Other MySQL® Questions
1

Hey!

We have one master MySQL server and one slave MySQL server, everything works fine. Now we need to start second slave server. So I made backup with Percona XtraBackup from slave server using Percona’s manual. I use this command:
sudo innobackupex --defaults-file=/etc/my.cnf --parallel=4 --password=‘mypass’ --slave-info --safe-slave-backup --compress --compress-threads=4 --rsync --throttle=30 /backups/test/
Then I do decompress and applylog, copying files to new slave, change directory rights, start MySQL, it works fine. Then I do RESET SLAVE ALL; on new slave server, copy my.cnf from first slave server, add there server-id = 3 and skip-slave-start option. Then I do

CHANGE MASTER TO
MASTER_HOST=‘master_ip’,
MASTER_USER=‘replicationuser’,
MASTER_PASSWORD=‘replicationpass’,
MASTER_LOG_FILE=‘(from above)’,
MASTER_LOG_POS= (from above);

With correct and fresh settings from master server using SHOW SLAVE STATUS\G command on it. Then, in 2 hours from backup creation, I start new slave, first 30 second everything in SHOW SLAVE STATUS\G works fine, LOG_POS is changing, but then I see this error:
[URL=“http://s15.postimg.org/twk7u6ybv/image.jpg”]http://s15.postimg.org/twk7u6ybv/image.jpg[/URL]
I try to ignore 1062 error using my.cnf, but in this case I see:
[URL=“http://s4.postimg.org/mvlugcg6l/image.jpg”]http://s4.postimg.org/mvlugcg6l/image.jpg[/URL]

So I think that something wrong with this replication or new slave server. Other slave server is working perfectly. Do you have any ideas what should I check or do?

2

Hi,

is the slave virtual? In my experience it look like some limits virtualization (for example “num file locks”). Or try find in binlog the “duplicate entry”.

3

Yes, virtual. Will try to find information about this, thanks.

4

Hi Dissident;

A few things to check:

  1. Make sure that you are getting your replication coordinates from xtrabackup_slave_info (points to the master of the slave you backed up) and not xtrabackup_binlog_info (points to the slave itself that you backed up).

  2. Make sure that you have the correct master IP / port to go along with the above replication coordinates.

  3. Make sure the zabbix is not somehow writing to any of the slaves (unlikely, but you never know).

Chances are it’s one of the first two though if I had to bet.

-Scott

5

Thanks, Scott!

Problem was that I took coordinates from working slave server, not from xtrabackup_slave_info. Now everything is fine.
Thanks again.

Viktor

6

And one more question, if possible.
Now there are 2 working slave servers, on the first I see:
Exec_Master_Log_Pos: 980539623
Relay_Log_Space: 980539968

But on the second:
Exec_Master_Log_Pos: 980571400
Relay_Log_Space: 120415791

Both slave servers have
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

What do you think, is that ok that Relay_Log_Space on two slave servers are different? On the first it’s like Exec_Master_Log_Pos, but on the second it’s much much smaller…

7

Hi Dissident;

What does your Seconds_behind_master value say? As long as the slave is replicating and catching up, it should not be an issue. The “Relay_Log_Space” value is just the size in bytes of all the relay log related files on disk, which gets cleaned up as the logs are processed.

-Scott

8

Thanks, Scott!