[已解决] MYSQL启动不了

942 2 2023-11-2 10:22

本帖最后由 fengzhencai 于 2023-11-2 10:25 编辑

背景：
通过如下语句导数据，
alter table table discard tablespace;
alter table table import tablespace;
的方式导入数据后，再建立主从方式从主库同步，就开始报这个错误，然后以为是这些表的问题，使用[size=12.8304px]innodb_force_recovery[size=12.8304px] [size=12.8304px]= 4的方式强制启动后删除所有业务表，
[size=12.8304px]目前是一个空库，结果还是启动不了，报错信息如下：
[size=12.8304px]请问这是什么原因导致数据库启动不了呢，是磁盘的问题吗？

2023-11-02T02:18:38.328520Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-11-02T02:18:38.329938Z 0 [Note] mysqld (mysqld 5.7.25-28-log) starting as process 1 ...
2023-11-02T02:18:38.333397Z 0 [Note] InnoDB: PUNCH HOLE support available
2023-11-02T02:18:38.333433Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2023-11-02T02:18:38.333438Z 0 [Note] InnoDB: Uses event mutexes
2023-11-02T02:18:38.333444Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2023-11-02T02:18:38.333449Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2023-11-02T02:18:38.333452Z 0 [Note] InnoDB: Using Linux native AIO
2023-11-02T02:18:38.335665Z 0 [Note] InnoDB: Number of pools: 1
2023-11-02T02:18:38.335775Z 0 [Note] InnoDB: Using CPU crc32 instructions
2023-11-02T02:18:38.337966Z 0 [Note] InnoDB: Initializing buffer pool, total size = 15G, instances = 8, chunk size = 128M
2023-11-02T02:18:38.808336Z 0 [Note] InnoDB: Completed initialization of buffer pool
2023-11-02T02:18:38.968885Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2023-11-02T02:18:38.981988Z 0 [Note] InnoDB: Recovering partial pages from the parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite
2023-11-02T02:18:38.998174Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2023-11-02T02:18:39.188816Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite, size 31457280 bytes
2023-11-02T02:18:39.236036Z 0 [Note] InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 83 row operations to undo
2023-11-02T02:18:39.236116Z 0 [Note] InnoDB: Trx id counter is 1283299584
2023-11-02T02:18:39.487454Z 0 [Note] InnoDB: Starting in background the rollback of uncommitted transactions
2023-11-02T02:18:39.500956Z 0 [Note] InnoDB: Rolling back trx with id 1283286505, 83 rows to undo
2023-11-02T02:18:39.496363Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2023-11-02T02:18:39.504621Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2023-11-02T02:18:39.504719Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2023-11-02 10:18:39 0x7fd0e1050700 InnoDB: Assertion failure in thread 140535105128192 in file btr0cur.cc line 334
InnoDB: Failing assertion: btr_page_get_next( latch_leaves.blocks[0]->frame, mtr) == page_get_page_no(page)
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/ ... nnodb-recovery.html
InnoDB: about forcing recovery.
02:18:39 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.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.
Please help us make Percona Server better by reporting any
bugs at https://bugs.percona.com/

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=0
max_threads=6001
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 16221237 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
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 = 0 thread_stack 0x40000
mysqld(my_print_stacktrace+0x3b)[0xf142ab]
mysqld(handle_fatal_signal+0x471)[0xd4e7a1]
/lib64/libpthread.so.0(+0xf5d0)[0x7fd4f01525d0]
/lib64/libc.so.6(gsignal+0x37)[0x7fd4ee253207]
/lib64/libc.so.6(abort+0x148)[0x7fd4ee2548f8]
mysqld[0x77ba08]
mysqld(_Z20btr_cur_latch_leavesP11buf_block_tRK9page_id_tRK11page_size_tmP9btr_cur_tP5mtr_t+0xc60)[0x11daa00]
mysqld(_Z27btr_cur_search_to_nth_levelP12dict_index_tmPK8dtuple_t15page_cur_mode_tmP9btr_cur_tmPKcmP5mtr_t+0x1b90)[0x11e2120]
mysqld(_Z30btr_pcur_restore_position_funcmP10btr_pcur_tPKcmP5mtr_t+0x428)[0x11e9df8]
mysqld[0x1315775]
mysqld(_Z12row_undo_insP11undo_node_tP9que_thr_t+0x3aa)[0x1315dfa]
mysqld(_Z13row_undo_stepP9que_thr_t+0x425)[0x1143f15]
mysqld(_Z15que_run_threadsP9que_thr_t+0x897)[0x10cbbf7]
2023-11-02T02:18:39.524811Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
mysqld(_Z31trx_rollback_or_clean_recoveredm+0xd2f)[0x119a95f]
mysqld(trx_rollback_or_clean_all_recovered+0x4e)[0x119b89e]
2023-11-02T02:18:39.525797Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
/lib64/libpthread.so.0(+0x7dd5)[0x7fd4f014add5]
2023-11-02T02:18:39.525812Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
/lib64/libc.so.6(clone+0x6d)[0x7fd4ee31aead]
You may download the Percona Server operations manual by visiting
http://www.percona.com/software/percona-server/. You may find information
in the manual which will help you identify the cause of the crash.
2023-11-02T02:18:39.526254Z 0 [Note] InnoDB: Waiting for purge to start

数据库配置信息：

[mysqld]
server-id=53793
init-connect='SET NAMES utf8'
character-set-server=utf8
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
skip-name-resolve
log-error=error.log

innodb_file_per_table=1
innodb_buffer_pool_size=20G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=64M
innodb_log_file_size=2G
innodb_open_files=500
innodb_log_files_in_group=2
innodb_max_dirty_pages_pct=90
innodb_lock_wait_timeout=120
innodb_io_capacity=800
innodb_read_io_threads=6

gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE

table_open_cache=12000
thread_cache_size=64
wait_timeout=28800
interactive_timeout=28800
max_connections=8000
max_connect_errors=100
tmp_table_size=512m
max_heap_table_size=256m
max_allowed_packet=512M

#innodb_force_recovery=4

sync-master-info=1
slave-parallel-workers=0
sync_binlog=0
expire_logs_days=1
log-bin=mysql-bin
binlog-format=row
event_scheduler=ON

slow_query_log=1
long_query_time=2
slow_query_log_file=slow-query.log
lower_case_table_names=1

extra_port=3336
extra_max_connections=5

tmpdir=/var/lib/mysql

数据库, MySQL

全部回复(2)

只看楼主