Announcement

Announcement Module
Collapse
No announcement yet.

the mysql often automatically restart and recovery

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • the mysql often automatically restart and recovery

    version: Percona Server5.5.21-55-log Percona Server5.5.21-55
    OS:ubunt 11.10

    During use, the mysql often automatically restart, and automatically restored when you reboot
    Sometimes modify a parameter will automatically restart, does the Han cause?

    my.cnf:

    [mysql]
    port = 3306
    socket = /var/run/mysqld/mysqld.sock
    [mysqld]

    datadir = /var/lib/mysql/
    back_log = 500
    user = mysql
    default_storage_engine = InnoDB
    socket = /var/run/mysqld/mysqld.sock
    pid_file = /var/lib/mysql/gzdb1.pid
    wait_timeout = 10

    key_buffer_size = 32M
    myisam_recover = FORCE,BACKUP
    max_allowed_packet = 1G
    max_connect_errors = 1000000
    skip_name_resolve = 1
    sysdate_is_now = 1
    innodb = FORCE
    innodb_strict_mode = 1

    innodb_data_file_path = ibdata1:6618M;ibdata2:2G;ibdata3:2G:autoextend
    log_bin = /var/lib/mysql/mysql-bin
    expire_logs_days = 14
    sync_binlog = 1

    # CACHES AND LIMITS #
    tmp_table_size = 32M
    max_heap_table_size = 32M
    query_cache_type = 0
    query_cache_size = 0
    max_connections = 1000
    thread_cache_size = 50
    open_files_limit = 65535
    table_definition_cache = 4096
    table_open_cache = 4096

    # INNODB #
    innodb_flush_method = O_DIRECT
    innodb_log_files_in_group = 2
    innodb_log_file_size = 512M
    innodb_flush_log_at_trx_commit = 1
    innodb_file_per_table = 1
    innodb_buffer_pool_size = 54G

    # LOGGING #
    log_error = /var/log/mysql/mysql-error.log
    log_queries_not_using_indexes = 0
    slow_query_log = 1
    slow_query_log_file = /var/log/mysql/mysql-slow.log


    The following is the log:

    120413 10:22:44 InnoDB: Page dump in ascii and hex (16384 bytes):
    len 16384; hex b1adc0360000003900000000000000000000010e588b7a9d00 0200000000 000000000000000000023007304effffffff0000ffffffff00 0000042f4d 000000000000fa2603f20000000100000039002c0000003900 2c00000002 569fb3b300000002569fb3b600000110010000000000000000 0001350000 NV X z ;
    InnoDB: End of page dump
    120413 10:22:44 InnoDB: Page checksum 2980954166 (32bit_calc: 615450524), prior-to-4.0.14-form checksum 2572048032
    InnoDB: stored checksum 2980954166, prior-to-4.0.14-form stored checksum 2572048032
    InnoDB: Page lsn 270 1485535901, low 4 bytes of lsn at page end 1485535901
    InnoDB: Page number (if stored to page already) 57,
    InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
    InnoDB: Page may be an update undo log page
    InnoDB: Page directory corruption: supremum not pointed to
    120413 10:22:44 InnoDB: Page dump in ascii and hex (16384 bytes):
    len 16384; hex b1adc0360000003900000000000000000000010e588b7a9d00 0200000000 000000000000000000023007304effffffff0000ffffffff00 0000042f4d 000000000000fa2603f2000000 NV X z ;
    InnoDB: End of page dump
    120413 10:22:44 InnoDB: Page checksum 2980954166 (32bit_calc: 615450524), prior-to-4.0.14-form checksum 2572048032
    InnoDB: stored checksum 2980954166, prior-to-4.0.14-form stored checksum 2572048032
    InnoDB: Page lsn 270 1485535901, low 4 bytes of lsn at page end 1485535901
    InnoDB: Page number (if stored to page already) 57,
    InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
    InnoDB: Page may be an update undo log page
    02:22:44 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=33554432
    read_buffer_size=131072
    max_used_connections=1001
    max_threads=1000
    thread_count=178
    connection_count=178
    It is possible that mysqld could use up to
    key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2221018 K bytes of memory
    Hope that's ok; if not, decrease some variables in the equation.

    Thread pointer: 0x7fb13c42f6b0
    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 = 7fb185d51e60 thread_stack 0x40000
    /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x7afb4e]
    /usr/sbin/mysqld(handle_fatal_signal+0x484)[0x689064]
    /lib/x86_64-linux-gnu/libpthread.so.0(+0x10060)[0x7fbfbb92c0 60]
    /usr/sbin/mysqld[0x9455d4]
    /usr/sbin/mysqld[0x931511]
    /usr/sbin/mysqld[0x88758a]
    /usr/sbin/mysqld[0x83eaad]
    /usr/sbin/mysqld[0x83f108]
    /usr/sbin/mysqld[0x8419c1]
    /usr/sbin/mysqld[0x942ee8]
    /usr/sbin/mysqld[0x9434bd]
    /usr/sbin/mysqld[0x835fdf]
    /usr/sbin/mysqld[0x81bcfd]
    /usr/sbin/mysqld(_Z15ha_delete_tableP3THDP10handlertonPKcS4_ S4_b+0xcf)[0x68e5bf]
    /usr/sbin/mysqld(_Z23mysql_rm_table_no_locksP3THDP10TABLE_LI STbbbb+0x59c)[0x5f75dc]
    /usr/sbin/mysqld(_Z14mysql_rm_tableP3THDP10TABLE_LISTcc+0x16 5)[0x5f7fe5]
    /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x1505)[0x59 8c55]
    /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x30 [0x59e648]
    /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3 THDPcj+0x15fd)[0x59fced]
    /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x14f)[0x 6375bf]
    /usr/sbin/mysqld(handle_one_connection+0x51)[0x637681]
    /lib/x86_64-linux-gnu/libpthread.so.0(+0x7efc)[0x7fbfbb923ef c]
    /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fbfbb65e89d]

    Trying to get some variables.
    Some pointers may be invalid and cause the dump to abort.
    Query (7fb17c059f70): is an invalid pointer
    Connection ID (thread ID): 193733259
    Status: NOT_KILLED

    The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
    information that should help you find out what is causing the crash.
    120413 10:22:50 mysqld_safe Number of processes running now: 0
    120413 10:22:50 mysqld_safe mysqld restarted
    120413 10:22:50 [Note] Flashcache bypass: disabled
    120413 10:22:50 [Note] Flashcache setup error is : ioctl failed

    120413 10:22:50 [Note] Plugin 'FEDERATED' is disabled.
    120413 10:22:50 InnoDB: The InnoDB memory heap is disabled
    120413 10:22:50 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    120413 10:22:50 InnoDB: Compressed tables use zlib 1.2.3
    120413 10:22:50 InnoDB: Using Linux native AIO
    120413 10:22:51 InnoDB: Initializing buffer pool, size = 54.0G
    120413 10:22:54 InnoDB: Completed initialization of buffer pool
    120413 10:22:54 InnoDB: highest supported file format is Barracuda.
    InnoDB: Log scan progressed past the checkpoint lsn 1363971834720
    120413 10:22:55 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...
    InnoDB: Doing recovery: scanned up to log sequence number 1363977077248
    InnoDB: Doing recovery: scanned up to log sequence number 1363982320128
    InnoDB: Doing recovery: scanned up to log sequence number 1363987563008
    InnoDB: Doing recovery: scanned up to log sequence number 1363992805888
    InnoDB: Doing recovery: scanned up to log sequence number 1363998048768
    InnoDB: Doing recovery: scanned up to log sequence number 1364003291648
    InnoDB: Doing recovery: scanned up to log sequence number 1364008534528
    InnoDB: Doing recovery: scanned up to log sequence number 1364013777408
    InnoDB: Doing recovery: scanned up to log sequence number 1364019020288
    InnoDB: Doing recovery: scanned up to log sequence number 1364024263168
    InnoDB: Doing recovery: scanned up to log sequence number 1364029506048
    InnoDB: Doing recovery: scanned up to log sequence number 1364034748928
    InnoDB: Doing recovery: scanned up to log sequence number 1364039991808
    InnoDB: Doing recovery: scanned up to log sequence number 1364045234688
    InnoDB: Doing recovery: scanned up to log sequence number 1364050477568
    InnoDB: Doing recovery: scanned up to log sequence number 1364055720448
    InnoDB: Doing recovery: scanned up to log sequence number 1364060963328
    InnoDB: Doing recovery: scanned up to log sequence number 1364066206208
    InnoDB: Doing recovery: scanned up to log sequence number 1364071449088
    InnoDB: Doing recovery: scanned up to log sequence number 1364076691968
    InnoDB: Doing recovery: scanned up to log sequence number 1364081934848
    InnoDB: Doing recovery: scanned up to log sequence number 1364087177728
    InnoDB: Doing recovery: scanned up to log sequence number 1364092420608
    InnoDB: Doing recovery: scanned up to log sequence number 1364097663488
    InnoDB: Doing recovery: scanned up to log sequence number 1364102906368
    InnoDB: Doing recovery: scanned up to log sequence number 1364108149248
    InnoDB: Doing recovery: scanned up to log sequence number 1364113392128
    InnoDB: Doing recovery: scanned up to log sequence number 1364118635008
    InnoDB: Doing recovery: scanned up to log sequence number 1364123877888
    InnoDB: Doing recovery: scanned up to log sequence number 1364129120768
    InnoDB: Doing recovery: scanned up to log sequence number 1364134363648
    InnoDB: Doing recovery: scanned up to log sequence number 1364139606528
    InnoDB: Doing recovery: scanned up to log sequence number 1364144849408
    InnoDB: Doing recovery: scanned up to log sequence number 1364150092288
    InnoDB: Doing recovery: scanned up to log sequence number 1364155335168
    InnoDB: Doing recovery: scanned up to log sequence number 1364160578048
    InnoDB: Doing recovery: scanned up to log sequence number 1364165820928
    InnoDB: Doing recovery: scanned up to log sequence number 1364171063808
    InnoDB: Doing recovery: scanned up to log sequence number 1364176306688
    InnoDB: Doing recovery: scanned up to log sequence number 1364181549568
    InnoDB: Doing recovery: scanned up to log sequence number 1364186792448
    InnoDB: Doing recovery: scanned up to log sequence number 1364192035328
    InnoDB: Doing recovery: scanned up to log sequence number 1364197278208
    InnoDB: Doing recovery: scanned up to log sequence number 1364202521088
    InnoDB: Doing recovery: scanned up to log sequence number 1364207763968
    InnoDB: Doing recovery: scanned up to log sequence number 1364213006848
    InnoDB: Doing recovery: scanned up to log sequence number 1364218249728
    InnoDB: Doing recovery: scanned up to log sequence number 1364223492608
    InnoDB: Doing recovery: scanned up to log sequence number 1364228735488
    InnoDB: Doing recovery: scanned up to log sequence number 1364233978368
    InnoDB: Doing recovery: scanned up to log sequence number 1364239221248
    InnoDB: Doing recovery: scanned up to log sequence number 1364244464128
    InnoDB: Doing recovery: scanned up to log sequence number 1364249707008
    InnoDB: Doing recovery: scanned up to log sequence number 1364254949888
    InnoDB: Doing recovery: scanned up to log sequence number 1364260192768
    InnoDB: Doing recovery: scanned up to log sequence number 1364265435648
    InnoDB: Doing recovery: scanned up to log sequence number 1364270678528
    InnoDB: Doing recovery: scanned up to log sequence number 1364275921408
    InnoDB: Doing recovery: scanned up to log sequence number 1364281164288
    InnoDB: Doing recovery: scanned up to log sequence number 1364286407168
    InnoDB: Doing recovery: scanned up to log sequence number 1364291650048
    InnoDB: Doing recovery: scanned up to log sequence number 1364296892928
    InnoDB: Doing recovery: scanned up to log sequence number 1364302135808
    InnoDB: Doing recovery: scanned up to log sequence number 1364307378688
    InnoDB: Doing recovery: scanned up to log sequence number 1364312621568
    InnoDB: Doing recovery: scanned up to log sequence number 1364317864448
    InnoDB: Doing recovery: scanned up to log sequence number 1364323107328
    InnoDB: Doing recovery: scanned up to log sequence number 1364328350208
    InnoDB: Doing recovery: scanned up to log sequence number 1364333593088
    InnoDB: Doing recovery: scanned up to log sequence number 1364338835968
    InnoDB: Doing recovery: scanned up to log sequence number 1364344078848
    InnoDB: Doing recovery: scanned up to log sequence number 1364349321728
    InnoDB: Doing recovery: scanned up to log sequence number 1364354564608
    InnoDB: Doing recovery: scanned up to log sequence number 1364359807488
    InnoDB: Doing recovery: scanned up to log sequence number 1364365050368
    InnoDB: Doing recovery: scanned up to log sequence number 1364370293248
    InnoDB: Doing recovery: scanned up to log sequence number 1364375536128
    InnoDB: Doing recovery: scanned up to log sequence number 1364380779008
    InnoDB: Doing recovery: scanned up to log sequence number 1364386021888
    InnoDB: Doing recovery: scanned up to log sequence number 1364391264768
    InnoDB: Doing recovery: scanned up to log sequence number 1364396507648
    InnoDB: Doing recovery: scanned up to log sequence number 1364401750528
    InnoDB: Doing recovery: scanned up to log sequence number 1364406993408
    InnoDB: Doing recovery: scanned up to log sequence number 1364412236288
    InnoDB: Doing recovery: scanned up to log sequence number 1364417479168
    InnoDB: Doing recovery: scanned up to log sequence number 1364422722048
    InnoDB: Doing recovery: scanned up to log sequence number 1364427964928
    InnoDB: Doing recovery: scanned up to log sequence number 1364433207808
    InnoDB: Doing recovery: scanned up to log sequence number 1364438450688
    InnoDB: Doing recovery: scanned up to log sequence number 1364443693568
    InnoDB: Doing recovery: scanned up to log sequence number 1364448936448
    InnoDB: Doing recovery: scanned up to log sequence number 1364454179328
    InnoDB: Doing recovery: scanned up to log sequence number 1364459422208
    InnoDB: Doing recovery: scanned up to log sequence number 1364464665088
    InnoDB: Doing recovery: scanned up to log sequence number 1364469907968
    InnoDB: Doing recovery: scanned up to log sequence number 1364475150848
    InnoDB: Doing recovery: scanned up to log sequence number 1364480393728
    InnoDB: Doing recovery: scanned up to log sequence number 1364485636608
    InnoDB: Doing recovery: scanned up to log sequence number 1364490879488
    InnoDB: Doing recovery: scanned up to log sequence number 1364496122368
    InnoDB: Doing recovery: scanned up to log sequence number 1364501365248
    InnoDB: Doing recovery: scanned up to log sequence number 1364506608128
    InnoDB: Doing recovery: scanned up to log sequence number 1364511851008
    InnoDB: Doing recovery: scanned up to log sequence number 1364517093888
    InnoDB: Doing recovery: scanned up to log sequence number 1364522336768
    InnoDB: Doing recovery: scanned up to log sequence number 1364527579648
    InnoDB: Doing recovery: scanned up to log sequence number 1364532822528
    InnoDB: Doing recovery: scanned up to log sequence number 1364538065408
    InnoDB: Doing recovery: scanned up to log sequence number 1364543308288
    InnoDB: Doing recovery: scanned up to log sequence number 1364548551168
    InnoDB: Doing recovery: scanned up to log sequence number 1364553794048
    InnoDB: Doing recovery: scanned up to log sequence number 1364559036928
    InnoDB: Doing recovery: scanned up to log sequence number 1364564279808
    InnoDB: Doing recovery: scanned up to log sequence number 1364569522688
    InnoDB: Doing recovery: scanned up to log sequence number 1364574765568
    InnoDB: Doing recovery: scanned up to log sequence number 1364580008448
    InnoDB: Doing recovery: scanned up to log sequence number 1364585251328
    InnoDB: Doing recovery: scanned up to log sequence number 1364590494208
    InnoDB: Doing recovery: scanned up to log sequence number 1364595737088
    InnoDB: Doing recovery: scanned up to log sequence number 1364600979968
    InnoDB: Doing recovery: scanned up to log sequence number 1364606222848
    InnoDB: Doing recovery: scanned up to log sequence number 1364611465728
    InnoDB: Doing recovery: scanned up to log sequence number 1364616708608
    InnoDB: Doing recovery: scanned up to log sequence number 1364621951488
    InnoDB: Doing recovery: scanned up to log sequence number 1364627194368
    InnoDB: Doing recovery: scanned up to log sequence number 1364632437248
    InnoDB: Doing recovery: scanned up to log sequence number 1364637680128
    InnoDB: Doing recovery: scanned up to log sequence number 1364642923008
    InnoDB: Doing recovery: scanned up to log sequence number 1364648165888
    InnoDB: Doing recovery: scanned up to log sequence number 1364653408768
    InnoDB: Doing recovery: scanned up to log sequence number 1364658651648
    InnoDB: Doing recovery: scanned up to log sequence number 1364663894528
    InnoDB: Doing recovery: scanned up to log sequence number 1364669137408
    InnoDB: Doing recovery: scanned up to log sequence number 1364674380288
    InnoDB: Doing recovery: scanned up to log sequence number 1364679623168
    InnoDB: Doing recovery: scanned up to log sequence number 1364684866048
    InnoDB: Doing recovery: scanned up to log sequence number 1364690108928
    InnoDB: Doing recovery: scanned up to log sequence number 1364695351808
    InnoDB: Doing recovery: scanned up to log sequence number 1364700594688
    InnoDB: Doing recovery: scanned up to log sequence number 1364705837568
    InnoDB: Doing recovery: scanned up to log sequence number 1364711080448
    InnoDB: Doing recovery: scanned up to log sequence number 1364716323328
    InnoDB: Doing recovery: scanned up to log sequence number 1364721566208
    InnoDB: Doing recovery: scanned up to log sequence number 1364726809088
    InnoDB: Doing recovery: scanned up to log sequence number 1364730434324
    120413 10:23:22 InnoDB: Starting an apply batch of log records to the database...
    InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
    InnoDB: Apply batch completed
    InnoDB: In a MySQL replication slave the last master binlog file
    InnoDB: position 0 74525746, file name mysql-bin.008444
    InnoDB: and relay log file
    InnoDB: position 0 74525892, file name /var/log/mysql/gzdb1-relay-bin.000290
    InnoDB: Last MySQL binlog file position 0 1017145722, file name /var/lib/mysql/mysql-bin.000133
    120413 10:24:36 InnoDB: Waiting for the background threads to start
    120413 10:24:37 Percona XtraDB (http://www.percona.com) 1.1.8-rel25.0 started; log sequence number 1364730434324
    120413 10:24:37 [Note] Recovering after a crash using /var/lib/mysql/mysql-bin
    120413 10:24:42 [Note] Starting crash recovery...
    120413 10:24:42 [Note] Crash recovery finished.
    120413 10:24:45 [Warning] 'proxies_priv' entry '@ root@gzdb1' ignored in --skip-name-resolve mode.
    120413 10:24:50 [Note] Event Scheduler: Loaded 0 events
    120413 10:24:50 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.5.21-55-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Percona Server (GPL), Release 25.0
    120413 10:24:53 [Note] Start binlog_dump to slave_server(27), pos(mysql-bin.000133, 1017145722)

  • #2
    innodb_force_load_corrupted OFF
    innodb_force_recovery 0

    Comment


    • #3
      Your data is corrupted. You probably have faulty hardware.

      Comment


      • #4
        Should not be a hardware problem, add a field will cause reboot

        Comment


        • #5
          mysql upgrade caused?
          Is the mysql 5.1.48 xtraback backup, then restore percona mysql 5.5.21

          Comment

          Working...
          X