Description:
I have problem at my replicated slaves. There are rebooted without reasons approximately one time at 30 minutes.
Size of databases approx. 600Mb in dump.
Consist of 5 databases and ~60 tables in total (only 4 of it are InnoDB, others has MyISAM type).
Load of each slave is 50-150 queries per second. Updates not so often.
A few days ago I has been looked at .err file and find such strings:
090416 18:22:06 mysqld restarted
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
090416 18:22:06 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
090416 18:22:06 InnoDB: Started; log sequence number 0 72443
090416 18:22:06 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=beta23-relay-bin' to avoid this problem.
090416 18:22:06 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.0.77-log' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port: mysql-server-5.0.77_1
090416 18:22:06 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000576' at position 445546465, relay log './beta23-relay-bin.000011' position: 46472
090416 18:22:06 [Note] Slave I/O thread: connected to master 'beta23@beta27.rambler.ru:3306', replication started in log 'mysql-bin.000576' at position 445546465
090416 18:31:15 mysqld restarted
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
090416 18:31:15 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
090416 18:31:15 InnoDB: Started; log sequence number 0 72453
090416 18:31:15 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=beta23-relay-bin' to avoid this problem.
090416 18:31:15 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.0.77-log' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port: mysql-server-5.0.77_1
090416 18:31:15 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000576' at position 445592521, relay log './beta23-relay-bin.000013' position: 46291
090416 18:31:15 [Note] Slave I/O thread: connected to master 'beta23@beta27.rambler.ru:3306', replication started in log 'mysql-bin.000576' at position 445592521
090416 18:47:38 mysqld restarted
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
090416 18:47:38 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
090416 18:47:38 InnoDB: Started; log sequence number 0 72473
090416 18:47:38 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=beta23-relay-bin' to avoid this problem.
090416 18:47:38 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.0.77-log' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port: mysql-server-5.0.77_1
090416 18:47:38 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000576' at position 446007370, relay log './beta23-relay-bin.000015' position: 415084
090416 18:47:38 [Note] Slave I/O thread: connected to master 'beta23@beta27.rambler.ru:3306', replication started in log 'mysql-bin.000576' at position 446007370
090416 19:08:16 mysqld restarted
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
090416 19:08:16 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
090416 19:08:16 InnoDB: Started; log sequence number 0 72493
090416 19:08:17 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=beta23-relay-bin' to avoid this problem.
090416 19:08:17 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.0.77-log' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port: mysql-server-5.0.77_1
090416 19:08:17 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000576' at position 446420360, relay log './beta23-relay-bin.000017' position: 413225
090416 19:08:17 [Note] Slave I/O thread: connected to master 'beta23@beta27.rambler.ru:3306', replication started in log 'mysql-bin.000576' at position 446420360
It rebooted without reasons. So I tried to recompile my mysql server and recreate slave from master but problem are left.
How to repeat:
Mysql has been rebooted with no data ever! I stoped slave, drop all databases and create default DBs by mysql_install_db script and restart mysqld. It still rebooted.
My my.cnf:
[mysqld]
server-id=23
thread_cache_size=12
thread_concurrency=3
interactive_timeout=25
wait_timeout=10
max_connections=100
connect_timeout=5
join_buffer=8M
record_buffer=64M
key_buffer=128M
sort_buffer=16M
read_buffer=1M
#write_buffer=2M
myisam_sort_buffer_size =32M
table_cache = 1024
query_cache_size = 256M
query_cache_limit = 1M
concurrent_insert=2
low_priority_updates=1
# for text indexes
ft_min_word_len = 3
# log slow queries
log-slow-queries = log-slow-queries.log
long_query_time = 1
# log-queries-not-using-indexes
innodb_flush_log_at_trx_commit = 1
