# free -m total used free shared buffers cached Mem: 48289 35288 13000 0 347 30399 -/+ buffers/cache: 4541 43747 Swap: 8189 51 8137
MySQL cannot start with below errors in
It can be only started when adding
innodb_force_recovery = 1 to
my.cnf, but then I get another error when starting the server: http://fpaste.org/6azJ/
This server used to be the master, but I was able to promote a slave as the new master. Currently I am trying to set this failed-master up as a new slave, but I can’t get it to start.
What should I do now?
UPDATE Thu Jul 19 23:50:17 ICT 2012:
It was started successfully with
innodb_force_recovery=2, but MySQL go away when doing a
mysql> drop table reportingdb.bigdata_banner_scheduler; ERROR 2013 (HY000): Lost connection to MySQL server during query
Here’re the logs: http://fpaste.org/M82a
UPDATE Fri Jul 20 08:02:57 ICT 2012:
I’ve been trying to rebuild replication using Percona Xtrabackup. At the first time, I get this bug when copying with
innobackupex. Thanks to @DTest who suggest increase
innodb_log_file_size to 1GB and it’s OK.
Please noted that: you should copy
innodb_* settings from the Master to Slave and run
innobackupex --apply-log /path/to/datadir on the Slave if you don’t want to get the below errors:
120720 6:18:50 InnoDB: Error: page 3670052 log sequence number 8078993744933 InnoDB: is in the future! Current system log sequence number 8078561559052. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html InnoDB: for more information. InnoDB: Error: trying to access page number 2175909760 in space 0, InnoDB: space name ./ibdata1, InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10. InnoDB: If you get this error at mysqld startup, please check that InnoDB: your my.cnf matches the ibdata files that you have in the InnoDB: MySQL server. 120720 6:18:50 InnoDB: Assertion failure in thread 47633462918272 in file fil0fil.c line 4434 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 23:18:50 UTC - mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail.
But the game is NOT finish: slave continue crashing after some minutes:
120720 7:58:28 [Warning] Slave SQL: Could not execute Write_rows event on table reportingdb.ox_banners; Duplicate entry '14 5928' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000999, end _log_pos 337836040, Error_code: 1062 120720 7:58:28 [Warning] Slave SQL: Could not execute Write_rows event on table reportingdb.selfserving_img_signatures; Dup licate entry '145928' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql -bin.000999, end_log_pos 337843612, Error_code: 1062 120720 7:58:28 [Warning] Slave SQL: Could not execute Write_rows event on table reportingdb.selfserving_email_log; Duplicat e entry '173213' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin. 000999, end_log_pos 337844062, Error_code: 1062 00:58:29 UTC - mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=1048576 read_buffer_size=1048576 max_used_connections=4 max_threads=2000 thread_count=2 connection_count=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 4119820 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x11cc5f20 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 40c73a78 thread_stack 0x40000 /usr/libexec/mysqld(my_print_stacktrace+0x2e)[0x7af52e] /usr/libexec/mysqld(handle_fatal_signal+0x3e2)[0x67c242] /lib64/libpthread.so.0[0x3fed00ebe0] /usr/libexec/mysqld(_ZN13st_select_lex17mark_as_dependentEPS_+0x4d)[0x568a3d] /usr/libexec/mysqld[0x68cc02] /usr/libexec/mysqld(_ZN10Item_field15fix_outer_fieldEP3THDPP5FieldPP4Item+0x670)[0x690c90] /usr/libexec/mysqld(_ZN10Item_field10fix_fieldsEP3THDPP4Item+0x351)[0x691361] /usr/libexec/mysqld(_ZN9Item_func10fix_fieldsEP3THDPP4Item+0x1d3)[0x6cb433] /usr/libexec/mysqld(_Z11setup_condsP3THDP10TABLE_LISTS2_PP4Item+0x1a5)[0x53aae5] /usr/libexec/mysqld(_Z20mysql_prepare_updateP3THDP10TABLE_LISTPP4ItemjP8st_order+0x118)[0x5df3e8] /usr/libexec/mysqld(_Z12mysql_updateP3THDP10TABLE_LISTR4ListI4ItemES6_PS4_jP8st_ordery15enum_duplicatesbPySB_+0x2b4)[0x5e0134] /usr/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x239b)[0x575c5b] /usr/libexec/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x10a)[0x57994a] /usr/libexec/mysqld(_ZN15Query_log_event14do_apply_eventEPK14Relay_log_infoPKcj+0xc57)[0x734757] /usr/libexec/mysqld(_Z26apply_event_and_update_posP9Log_eventP3THDP14Relay_log_info+0x16e)[0x516fce] /usr/libexec/mysqld[0x51e631] /usr/libexec/mysqld(handle_slave_sql+0xc46)[0x51f946] /lib64/libpthread.so.0[0x3fed00677d] /lib64/libc.so.6(clone+0x6d)[0x3fec8d325d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (128380d7): UPDATE `ox_banners` A SET A.locationAd=@locCP WHERE A.zoneId = NAME_CONST('_zoneid',2452) Connection ID (thread ID): 2061 Status: NOT_KILLED
slave-skip-errors = 1062 seems not to work.
I’m going to take Master’s snapshot using
mysqldump, hope it can solve the crashing problem.
From a chat discussion, the first error is because the file
./reportingdb/bigdata_banner_scheduler.ibd is missing. Just copying this file from the master won’t work, though. You’ll need to drop the table on the slave and then dump the table from the master.
But that assert error is a different matter. You can start in force_recovery mode 1, but something is killing the
mysqld process, and it doesn’t appear to be memory (misconfiguration).
Since you are trying to set this up as a slave of the recently-promoted master, I would actually wipe the data, and re-install MySQL and start from a fresh copy of the master.
If for whatever reason you’d like to try to get it working without dumping the entire master (not recommended) my steps would be this:
skip-slave-startin the my.cnf to disable the slave from starting automatically
- Take out
innodb-force-recoveryfrom the my.cnf
- Copy all the files from the
datadirto a separate location on your slave server
- Re-install mysql (steps for this depends on your OS)
- Copy the
performance_schemadirectories from the old install back into the freshly installed
- Start the mysql server to make sure the server starts normally and without issue.
- If it does, stop the server again and continue these steps
innodb-force-recoveryto 1 in the my.cnf
- Copy all the other files from your backup back into the
- Start the server. This should put you in a state where you can
DROP TABLE reportingdb.bigdata_banner_scheduler
- Stop the server
- Remove the
- Start the server.
At this point, if all went well, you should have a working ‘slave’ server without the
reportingdb.bigdata_banner_scheduler table, and slave should still be disabled (not reading from the master’s binlog).
The steps I would do to get the table back on the slave are:
- From the master, take a dump of the table structure and data:
mysqldump -u.. -p reportingdb bigdata_banner_scheduler > reportingBigData.sql
- Copy the dump to the slave
- Import the dump back into the slave:
mysql -u... -p reportingdb < reportingBigData.sql
- Then start slave to have it start catching up on missing binlog events