前段时间机器上装的数据库出问题了,连接不上。我把目录下的所有文件都考出来了,其中有数据库表的结构文件(。frm)还有一个非常大的ibdata1文件(我觉得应该是数据)。
我在另一机器上重新装了数据库,将数据表结构文件拷贝到/data目录下面之后,能够连接mysql。可以看到里面有我原来的数据库,但是没有表。之后网上说将ibdata1拷贝到/data下就行了,但是我复制过程中原来的ibdata1不能被覆盖,有程序正在使用,我将mysql关掉之后能够覆盖了,但是覆盖之后数据库连接不上了。
求高手指点,甚急!!!!!!!!!!!!!!!
我在另一机器上重新装了数据库,将数据表结构文件拷贝到/data目录下面之后,能够连接mysql。可以看到里面有我原来的数据库,但是没有表。之后网上说将ibdata1拷贝到/data下就行了,但是我复制过程中原来的ibdata1不能被覆盖,有程序正在使用,我将mysql关掉之后能够覆盖了,但是覆盖之后数据库连接不上了。
求高手指点,甚急!!!!!!!!!!!!!!!
请问日志文件是哪一个,我这找不到
110803 17:44:40 InnoDB: Started; log sequence number 0 43655
110803 17:44:40 [Note] /usr/sbin/mysqld-max: ready for connections.
Version: '5.1.7-beta' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Max (GPL)
A mysqld process already exists at Wed Aug 3 17:48:45 CST 2011
A mysqld process already exists at Fri Aug 5 21:19:49 CST 2011
110929 21:06:39 [Note] /usr/sbin/mysqld-max: Normal shutdown110929 21:06:39 InnoDB: Warning: MySQL is trying to drop table `weibo/@0023sql@002d6127_1aaef`
InnoDB: though there are still open handles to it.
InnoDB: Adding the table to the background drop queue.
110929 21:06:41 InnoDB: Starting shutdown...
110929 21:06:41 InnoDB: Dropped table weibo/@0023sql@002d6127_1aaef in background drop queue.
110929 21:06:44 InnoDB: Shutdown completed; log sequence number 24 622925570
110929 21:06:44 [Note] /usr/sbin/mysqld-max: Shutdown complete110929 21:06:44 mysqld ended110929 21:09:37 mysqld started
110929 21:09:37 InnoDB: Started; log sequence number 24 622925570
110929 21:09:37 [Note] /usr/sbin/mysqld-max: ready for connections.
Version: '5.1.7-beta' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Max (GPL)
111003 17:16:57 InnoDB: ERROR: the age of the last checkpoint is 9433680,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.
111011 20:49:14 InnoDB: WARNING: over 4 / 5 of the buffer pool is occupied by
InnoDB: lock heaps or the adaptive hash index! Check that your
InnoDB: transactions do not set too many row locks.
InnoDB: Your buffer pool size is 8 MB. Maybe you should make
InnoDB: the buffer pool bigger?
InnoDB: Starting the InnoDB Monitor to print diagnostics, including
InnoDB: lock heap and hash index sizes.=====================================
111011 20:49:24 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 8 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 36857478, signal count 36777570
Mutex spin waits 1661983269, rounds 4094813281, OS waits 15484348
RW-shared spins 7297080, OS waits 3397049; RW-excl spins 23626779, OS waits 15703582
------------
TRANSACTIONS
------------
Trx id counter 0 198586
Purge done for trx's n:o < 0 173442 undo n:o < 0 0
History list length 8
Total number of lock structs in row lock hash table 77666
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 12502, OS thread id 4051409808
mysql tables in use 1, locked 1
MySQL thread id 689, query id 127 localhost root Table lock
LOCK TABLES `relation` READ /*!32311 LOCAL */
---TRANSACTION 0 0, not started, process no 12502, OS thread id 4051008400
mysql tables in use 1, locked 1
MySQL thread id 573, query id 109 localhost root Table lock
create index secondindex on relation(resid)
---TRANSACTION 0 195608, not started, process no 12502, OS thread id 4051209104
mysql tables in use 1, locked 1
MySQL thread id 572, query id 100 localhost root Table lock
create index secondindex on relation(resid)
---TRANSACTION 0 0, not started, process no 12502, OS thread id 4052011920
mysql tables in use 1, locked 1
MySQL thread id 570, query id 90 localhost root Table lock
create index secondindex on relation(resid)
---TRANSACTION 0 0, not started, process no 12502, OS thread id 4051811216
mysql tables in use 1, locked 1
MySQL thread id 183, query id 54 localhost root Table lock
create index secondindex on relation(resid)
---TRANSACTION 0 198585, ACTIVE 409 sec, process no 12502, OS thread id 4049599376 fetching rows
mysql tables in use 1, locked 1
77444 lock struct(s), heap size 6860096
MySQL thread id 700, query id 144 localhost root Sending data
insert into relation_test select srcid,desid,weight from relation
---TRANSACTION 0 198584, ACTIVE 414 sec, process no 12502, OS thread id 4051610512 inserting
mysql tables in use 2, locked 2
226 lock struct(s), heap size 27968, undo log entries 67981
MySQL thread id 40, query id 39 localhost root copy to tmp table
create index secondindex on relation(desid)
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
565410139 OS file reads, 399368538 OS file writes, 13682033 OS fsyncs
893.14 reads/s, 20242 avg bytes/read, 655.42 writes/s, 86.24 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 266, free list len 137, seg size 404, is not empty
Ibuf for space 0: size 266, free list len 137, seg size 404,
745586832 inserts, 745532250 merged recs, 123470783 merges
Hash table size 34679, used cells 1, node heap has 1 buffer(s)
88.61 hash searches/s, 947.01 non-hash searches/s
---
LOG
---
Log sequence number 67 1647427543
Log flushed up to 67 1647427244
Last checkpoint at 67 1647323431
0 pending log writes, 0 pending chkp writes
4964820 log i/o's done, 31.12 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 17062870; in additional pool allocated 991488
Buffer pool size 512
Free buffers 0
Database pages 91
Modified db pages 15
Pending reads 1
Pending writes: LRU 0, flush list 0, single page 0
Pages read 586845635, created 9446463, written 451751334
1109.74 reads/s, 1.62 creates/s, 628.05 writes/s
Buffer pool hit rate 984 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 12502, id 4069927824, state: sleeping
Number of rows inserted 1028226311, updated 0, deleted 0, read 1828706540
138.48 inserts/s, 0.00 updates/s, 0.00 deletes/s, 61954.63 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
=================================================================
111011 20:49:40 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 36861251, signal count 36781334
Mutex spin waits 1662060528, rounds 4095139013, OS waits 15486528
RW-shared spins 7297451, OS waits 3397226; RW-excl spins 23626847, OS waits 15703624
------------
TRANSACTIONS
------------
Trx id counter 0 198586
Purge done for trx's n:o < 0 173442 undo n:o < 0 0
History list length 8
Total number of lock structs in row lock hash table 80943
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 12502, OS thread id 4051409808
mysql tables in use 1, locked 1
MySQL thread id 689, query id 127 localhost root Table lock
LOCK TABLES `relation` READ /*!32311 LOCAL */
---TRANSACTION 0 0, not started, process no 12502, OS thread id 4051008400
mysql tables in use 1, locked 1
MySQL thread id 573, query id 109 localhost root Table lock
create index secondindex on relation(resid)
---TRANSACTION 0 195608, not started, process no 12502, OS thread id 4051209104
mysql tables in use 1, locked 1
MySQL thread id 572, query id 100 localhost root Table lock
create index secondindex on relation(resid)
---TRANSACTION 0 0, not started, process no 12502, OS thread id 4052011920
mysql tables in use 1, locked 1
MySQL thread id 570, query id 90 localhost root Table lock
create index secondindex on relation(resid)
---TRANSACTION 0 0, not started, process no 12502, OS thread id 4051811216
mysql tables in use 1, locked 1
MySQL thread id 183, query id 54 localhost root Table lock
create index secondindex on relation(resid)
---TRANSACTION 0 198585, ACTIVE 425 sec, process no 12502, OS thread id 4049599376 fetching rows
mysql tables in use 1, locked 1
80715 lock struct(s), heap size 7155008
MySQL thread id 700, query id 144 localhost root Sending data
insert into relation_test select srcid,desid,weight from relation
---TRANSACTION 0 198584, ACTIVE 430 sec, process no 12502, OS thread id 4051610512 inserting
mysql tables in use 2, locked 2
232 lock struct(s), heap size 27968, undo log entries 70043
MySQL thread id 40, query id 39 localhost root copy to tmp table
create index secondindex on relation(desid)
这是一部分
111023 18:04:01 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...
111023 18:04:01 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 73 3467443339.
InnoDB: Doing recovery: scanned up to log sequence number 73 3468869165
InnoDB: 2 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 1916 row operations to undo
InnoDB: Trx id counter is 0 363264
111023 18:04:01 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 111023 18:04:01 InnoDB: Error: page 229888 log sequence number 73 3468933185
InnoDB: is in the future! Current system log sequence number 73 3468869165.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
0 111023 18:04:01 InnoDB: Error: page 27618 log sequence number 73 3468920032
InnoDB: is in the future! Current system log sequence number 73 3468869165.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
1 2 111023 18:04:02 InnoDB: Error: page 3075744 log sequence number 73 3468871093
InnoDB: is in the future! Current system log sequence number 73 3468869165.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
111023 18:04:02 InnoDB: Error: page 1441793 log sequence number 73 3468908693
InnoDB: is in the future! Current system log sequence number 73 3468869165.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
3 4 5 111023 18:04:02 InnoDB: Error: page 2648409 log sequence number 73 3468902089
InnoDB: is in the future! Current system log sequence number 73 3468869165.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
6 7 8 InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 3383.
InnoDB: You may have to recover from a backup.
111023 18:04:03 InnoDB: Page dump in ascii and hex (16384 bytes):
InnoDB: End of page dump
111023 18:04:03 InnoDB: Page checksum 1208781666, prior-to-4.0.14-form checksum 2258045445
InnoDB: stored checksum 1208781666, prior-to-4.0.14-form stored checksum 1409092962
InnoDB: Page lsn 73 3468423491, low 4 bytes of lsn at page end 3468757466
InnoDB: Page number (if stored to page already) 3383,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an index page where index id is 4294967295 0
InnoDB: (index CLUST_IND of table SYS_IBUF_TABLE_0)
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 3383.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.
111023 18:04:03 mysqld ended