|
备份命令： mysqldump --source-data --flush-logs --single-transaction --set-gtid-purged=off --skip-generated-invisible-primary-key -uback -p -h 127.0.0.1 -P 3306 -f -A > test.sql 执行上述备份命令后发现备份会被卡住：  error里面没有多于的报错日志。 卡住的表结构如下：  下面是show engine innodb status的值 =====================================2023-08-24 15:12:33 140448206100224 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 30 seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops: 4526 srv_active, 0 srv_shutdown, 585858 srv_idlesrv_master_thread log flush and writes: 0----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 172911OS WAIT ARRAY INFO: signal count 192735RW-shared spins 0, rounds 0, OS waits 0RW-excl spins 0, rounds 0, OS waits 0RW-sx spins 0, rounds 0, OS waits 0Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx------------TRANSACTIONS------------Trx id counter 1166525716Purge done for trx's n  < 1166521790 undo n < 0 state: running but idleHistory list length 0LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 421929885341992, not startedmysql tables in use 1, locked 00 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885347928, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885326728, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885303832, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885346232, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885325032, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885324184, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885323336, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885322488, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885321640, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885320792, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885319944, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885319096, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885318248, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885317400, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885316552, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885315704, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885314856, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885314008, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885313160, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885312312, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885310616, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885306376, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885302136, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885311464, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885343688, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885342840, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885341144, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885340296, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885339448, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885338600, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885337752, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885336904, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885336056, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885335208, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885331816, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885329272, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885328424, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885327576, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885308920, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885308072, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885307224, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885305528, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885304680, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885302984, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885330968, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885330120, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885334360, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885348776, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885332664, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885325880, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885301288, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 421929885300440, not started0 lock struct(s), heap size 1128, 0 row lock(s)---TRANSACTION 1166521791, ACTIVE 119 secmysql tables in use 1, locked 00 lock struct(s), heap size 1128, 0 row lock(s)MySQL thread id 62837, OS thread handle 140445665384192, query id 2923518 127.0.0.1 backup executingSELECT /*!40001 SQL_NO_CACHE */ * FROM `data_month`Trx read view will not see trx with id >= 1166521791, sees < 1166521791--------FILE I/O--------I/O thread 0 state: waiting for completed aio requests (insert buffer thread)I/O thread 1 state: waiting for completed aio requests (log thread)I/O thread 2 state: waiting for completed aio requests (read thread)I/O thread 3 state: waiting for completed aio requests (read thread)I/O thread 4 state: waiting for completed aio requests (read thread)I/O thread 5 state: waiting for completed aio requests (read thread)I/O thread 6 state: waiting for completed aio requests (write thread)I/O thread 7 state: waiting for completed aio requests (write thread)I/O thread 8 state: waiting for completed aio requests (write thread)I/O thread 9 state: waiting for completed aio requests (write thread)Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's ending flushes (fsync) log: 0; buffer pool: 14216618 OS file reads, 1406075 OS file writes, 165271 OS fsyncs0.00 reads/s, 0 avg bytes/read, 0.65 writes/s, 0.00 fsyncs/s-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf: size 1, free list len 146, seg size 148, 0 mergesmerged operations: insert 0, delete mark 0, delete 0discarded operations: insert 0, delete mark 0, delete 0Hash table size 1328213, node heap has 133 buffer(s)Hash table size 1328213, node heap has 333 buffer(s)Hash table size 1328213, node heap has 110 buffer(s)Hash table size 1328213, node heap has 72 buffer(s)Hash table size 1328213, node heap has 94 buffer(s)Hash table size 1328213, node heap has 34 buffer(s)Hash table size 1328213, node heap has 139 buffer(s)Hash table size 1328213, node heap has 18 buffer(s)26.07 hash searches/s, 0.50 non-hash searches/s---LOG---Log sequence number 456576461720Log buffer assigned up to 456576461720Log buffer completed up to 456576461720Log written up to 456576461720Log flushed up to 456576461720Added dirty pages up to 456576461720Pages flushed up to 456576461720Last checkpoint at 456576461720Log minimum file id is 0Log maximum file id is 14Modified age no less than 456576461720Checkpoint age 0Max checkpoint age 2710905856938906 log i/o's done, 0.00 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total large memory allocated 0Dictionary memory allocated 16720515Buffer pool size 327640Buffer pool size, bytes 5368053760Free buffers 99762Database pages 226945Old database pages 83794Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 16492, not young 16358130.68 youngs/s, 0.00 non-youngs/sPages read 205659, created 28765, written 3448570.00 reads/s, 0.00 creates/s, 0.65 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 10 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 226945, unzip_LRU len: 0I/O sum[160]:cur[0], unzip sum[0]:cur[0]----------------------INDIVIDUAL BUFFER POOL INFO-------------------------BUFFER POOL 0Buffer pool size 40955Buffer pool size, bytes 671006720Free buffers 14064Database pages 26780Old database pages 9904Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 1335, not young 1097410.00 youngs/s, 0.00 non-youngs/sPages read 25254, created 2581, written 236640.00 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 26780, unzip_LRU len: 0I/O sum[20]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 1Buffer pool size 40955Buffer pool size, bytes 671006720Free buffers 12103Database pages 28741Old database pages 10623Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 1871, not young 4728030.00 youngs/s, 0.00 non-youngs/sPages read 25988, created 3909, written 510000.00 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 28741, unzip_LRU len: 0I/O sum[20]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 2Buffer pool size 40955Buffer pool size, bytes 671006720Free buffers 14271Database pages 26568Old database pages 9827Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 2748, not young 1086030.00 youngs/s, 0.00 non-youngs/sPages read 24167, created 3339, written 451850.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 26568, unzip_LRU len: 0I/O sum[20]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 3Buffer pool size 40955Buffer pool size, bytes 671006720Free buffers 9178Database pages 31651Old database pages 11690Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 3559, not young 1182170.68 youngs/s, 0.00 non-youngs/sPages read 27999, created 5521, written 656640.00 reads/s, 0.00 creates/s, 0.65 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 86 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 31651, unzip_LRU len: 0I/O sum[20]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 4Buffer pool size 40955Buffer pool size, bytes 671006720Free buffers 13623Database pages 27222Old database pages 10050Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 1764, not young 958380.00 youngs/s, 0.00 non-youngs/sPages read 24101, created 3896, written 412360.00 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 27222, unzip_LRU len: 0I/O sum[20]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 5Buffer pool size 40955Buffer pool size, bytes 671006720Free buffers 12639Database pages 28193Old database pages 10387Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 1398, not young 1110640.00 youngs/s, 0.00 non-youngs/sPages read 25319, created 3325, written 357750.00 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 28193, unzip_LRU len: 0I/O sum[20]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 6Buffer pool size 40955Buffer pool size, bytes 671006720Free buffers 10883Database pages 29957Old database pages 11038Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 2215, not young 1267580.00 youngs/s, 0.00 non-youngs/sPages read 27328, created 2994, written 334800.00 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 29957, unzip_LRU len: 0I/O sum[20]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 7Buffer pool size 40955Buffer pool size, bytes 671006720Free buffers 13001Database pages 27833Old database pages 10275Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 1602, not young 4927890.00 youngs/s, 0.00 non-youngs/sPages read 25503, created 3200, written 488530.00 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 27833, unzip_LRU len: 0I/O sum[20]:cur[0], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue1 read views open inside InnoDB1 RW transactions active inside InnoDB---OLDEST VIEW---Read view low limit trx n 1166521790Trx read view will not see trx with id >= 1166521791, sees < 1166521791Read view individually stored trx ids:-----------------Process ID=2681, Main thread ID=140448291079936 , state=sleepingNumber of rows inserted 6060518, updated 136451, deleted 3472402, read 113483271316.20 inserts/s, 0.00 updates/s, 0.00 deletes/s, 16.20 reads/sNumber of system rows inserted 37741, updated 16285, deleted 30954, read 35591040.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 27.93 reads/s----------------------------END OF INNODB MONITOR OUTPUT============================ |
合作电话：010-64087828
社区邮箱：greatsql@greatdb.com
