I have MySQL (5.5) in master-slave setup and created another slave server.
I stopped the original slave, dumped the data, copied and reimported and it worked fine. I noted the master_log pos of the original slave and used these commands to set it on the new slave
CHANGE MASTER TO MASTER_HOST='<ipaddress>',
MASTER_USER='<username>', MASTER_PASSWORD='<password>',
MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000851',
MASTER_LOG_POS=15824150,
MASTER_CONNECT_RETRY=10;
When I started the new slave I got
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master'
However when I started the original slave, it caught up just fine, and is now in sync.
So the questions:
-
the current value is 16M, how do I know how big to go? (I would rather avoid trial and error with a production server).
-
why do I need to increase the value on the master when the original slave coped just fine, could the problem really be with the new slave?
update
I increased the max_allowed_packet to 1073741824 as Rolando suggested on the master, old slave and new slave, and restarted them (SET GLOBAL max_allowed_packet = 1073741824;
for some reason didn't seem to take)
now the last IO error is the same as before, but now I see
Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
If I do a mysqlbinlog on the master's file, it scrolls past with commands quite happily for ages – the file is 722M – if I do that for the slave relay log I get
ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 38916267, event_type: 69
ERROR: Could not read entry at offset 253: Error in log format or read error.
I checked the variables and the changes worked however
mysql> show variables LIKE '%max_allowed_packet%';
on the new slave showed max_allowed_packet
AND slave_max_allowed_packet
where as on the master it only has max_allowed_packet
so I did a version check on the master:
mysql> show variables LIKE '%version%';
+-------------------------+--------------------------------------+
| Variable_name | Value |
+-------------------------+--------------------------------------+
| innodb_version | 1.1.6 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.11-log |
| version_comment | MySQL Community Server (GPL) by Remi |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+--------------------------------------+
and on the new slave
mysql> show variables LIKE '%version%';
+-------------------------+--------------------------------------+
| Variable_name | Value |
+-------------------------+--------------------------------------+
| innodb_version | 5.5.32 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.32-log |
| version_comment | MySQL Community Server (GPL) by Remi |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+--------------------------------------+
Are these 2 versions too far apart?
Best Answer
It's OK to max out the
max_allowed_packet
to 1G. Whenever a MySQL Packet is constructed, it will not jump to 1G from the start. Why?First you need to know what a MySQL Packet. Page 99 of the Book
explains it in paragraphs 1-3 as follows:
Compare that with the MySQL Documentation on
max_allowed_packet
:Given this information, you should be glad MySQL will expand and contract the MySQL Packet as needed. Therefore, go ahead and
max_allowed_packet
to 1G on both Master and Slavenet_buffer_length
to its max value of 1M on both Master and SlaveMaster and Slave should match in terms of who they transmit data, especially BLOB data.
UPDATE 2013-07-04 07:03 EDT
From your messages concerning the relay log, it looks like you have the following
SUGGESTION
Running
CHANGE MASTER TO
clears all relay logs and starts with a new one. You will be replicating from the Last Master BinLog Event (BinLog,Position) that executed on the Slave.Give it a Try !!!