0

First off, some versions: Centos 6, mysql 5.1 are what I'm stuck with using for now.

So - The problem is that when I replicate using either of these methods:

A: From the manual, unavailable via Oracle's website but thankfully mirrored by some university in Namibia: http://download.nust.na/pub6/mysql/doc/refman/5.1/en/replication.html

B: Via Percona's tools, the preferred method as it doesn't result in even a second of downtime, which is impossibly costly: https://www.percona.com/doc/percona-xtrabackup/2.3/howtos/setting_up_replication.html

That on the slave I get an error telling me that the binlog can't be found on the master. Checking the path where the binlogs are stored, the filename ends up being something different than when I start the dump or innobackupex. My mysql configuration looks like this on the master:

# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
thread_cache_size = 60

symbolic-links=0
innodb_buffer_pool_size=6000M
#query_cache_size = 2500M
table_cache = 800M
open_files_limit = 20000
key_buffer_size=500M
log-slow-queries = /var/log/mysql_slow_query.log
long_query_time = 6
# general_log = 1
max_connections = 500
innodb_thread_concurrency = 18
join_buffer_size = 10M
#log = /var/log/mysql_queries.log
log_bin = /var/lib/mysql_dump/bin/bin
skip-name-resolve

# Replication
server-id = 1
innodb_flush_log_at_trx_commit= 1
sync_binlog= 1
max_allowed_packet=1024M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

And on the slave:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

server-id=2

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Results of these queries as well:

mysql> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

And this is what comes up for the master status:

mysql> SHOW MASTER STATUS;
+------------+----------+--------------+------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------+----------+--------------+------------------+
| bin.023469 | 44272212 |              |                  |
+------------+----------+--------------+------------------+
1 row in set (0.00 sec)

And on the slave:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
           Slave_IO_State: 
              Master_Host: [redacted for the public :) ]
              Master_User: repl0
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: bin.023468
      Read_Master_Log_Pos: 15943086
           Relay_Log_File: mysqld-relay-bin.000002
            Relay_Log_Pos: 4
    Relay_Master_Log_File: bin.023468
         Slave_IO_Running: No
        Slave_SQL_Running: Yes
          Replicate_Do_DB: 
      Replicate_Ignore_DB: 
       Replicate_Do_Table: 
   Replicate_Ignore_Table: 
  Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
               Last_Errno: 0
               Last_Error: 
             Skip_Counter: 0
      Exec_Master_Log_Pos: 106
          Relay_Log_Space: 106
          Until_Condition: None
           Until_Log_File: 
            Until_Log_Pos: 0
       Master_SSL_Allowed: No
       Master_SSL_CA_File: 
       Master_SSL_CA_Path: 
          Master_SSL_Cert: 
        Master_SSL_Cipher: 
        Master_SSL_Key: 
    Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 1236
            Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
           Last_SQL_Errno: 0
           Last_SQL_Error: 

And then this:

$ cat /var/lib/mysql/xtrabackup_binlog_info
bin.023468      15943086

It was largely the same issue when I did it Oracle's way - Simply had a different filename for the binlog than when I had done the dump.

So - how to make sure that this doesn't go wrong on my third attempt?

  • Can you provide the name of your binlogs and determine a pattern of changing? – Josh Bonello Jan 9 '17 at 21:51
  • Certainly, added the extra info in an edit just now – Dmitri DB Jan 9 '17 at 22:08
  • Do you happen to have any filesystem cleanup jobs launching from crontab/jenkins or similar scheduler? This could explain why the an "old" binlog has been cleaned up before your slave could use it. – Josh Bonello Jan 23 '17 at 15:57
  • I am not finding anything in the crontab and don't think any other scheduler may be running, but I'll take a closer look. – Dmitri DB Jan 23 '17 at 17:22
  • Shut down the only scheduled app running on there (Backup service) as it seemed to be the only thing working with the binlog, but that didn't work either. Found a pattern that it's happening at around midnight, though. Trying my best to get the database from one DC to another in a 3rdworld country, where transfer before filename rollover is the real culprit here. – Dmitri DB Jan 24 '17 at 21:05
1
+100

Several issues, though perhaps none solve your problem:

  • What is the value of sync_binlog? Has the Master crashed? Off and Yes usually lead to "illegal position".

  • What are the settings of the parameters that define the binlog names?

  • Does replication work some of the time?

  • Are the server-id values different on the different servers?

  • This is a puzzling combination: 23468 binlogs, yet Pos is in the millions. When all goes well, a binlog fills up to 100MB (or 1GB), then a new one is started. With those numbers, that would be many TB.

  • How long have you had Replication running? Years?

  • Is there any pattern to the size of the old binlogs?

  • Are you saying that if the binlog hits 100MB, a new one gets started and the filename will increment? Because that would probably completely explain the problem here... it's a huge database which takes a super long time to transfer, I wouldn't be surprised if that's what I'm up against here. – Dmitri DB Jan 22 '17 at 23:56
  • The binlog files roll over in that way. But normally the size, etc, is irrelevant. The SLAVE STATUS shows that it is at byte 15,943,086. A "database" is not normally "transferred" via the binlog; what is going on? – Rick James Jan 23 '17 at 0:18
  • I think the binlog_info is merely the pointer into the binlogs. – Rick James Jan 23 '17 at 0:19
  • The "master position" is 44MB into the next binlog, the slave seems to be about 130MB behind?? But currently stuck. – Rick James Jan 23 '17 at 0:20
  • Looks like the binlog filesize was 1GB, I bumped it up higher. This database again is pretty large and always growing – Dmitri DB Jan 23 '17 at 0:31
0

It was an errant mysql full backup running with these flags doing it:

--flush-logs and --delete-master-logs 

Disabling this fixed the issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.