场景:
远程拷贝的 / var/lib/MySQL/* 的所有文件打包后拖到本地后无法启动.(导表因为数据库太大导表相当漫长, 偷懒了.... 启动时一同报错....),
版本:
- sys:
- Linux console 3.10.0-862.el7.x86_64 #1 SMP Fri Apr 20 16:44:24 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
- DB:
- Server version: 5.5.60-MariaDB MariaDB Server
报错信息:
数据库日志:
- #tailf /var/log/mariadb/mariadb.log
- Server version: 5.5.60-MariaDB
- key_buffer_size=134217728
- read_buffer_size=131072
- max_used_connections=0
- max_threads=153
- thread_count=0
- It is possible that mysqld could use up to
- key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 466718 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 = 0x0 thread_stack 0x48000
- 190119 12:54:23 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
- 190119 12:55:54 mysqld_safe Starting mysqld daemon with databases from /var/lib/MySQL
- 190119 12:55:54 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 10885 ...
- 190119 12:55:54 InnoDB: The InnoDB memory heap is disabled
- 190119 12:55:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins
- 190119 12:55:54 InnoDB: Compressed tables use zlib 1.2.7
- 190119 12:55:54 InnoDB: Using Linux native AIO
- 190119 12:55:54 InnoDB: Initializing buffer pool, size = 128.0M
- 190119 12:55:54 InnoDB: Completed initialization of buffer pool
- 190119 12:55:54 InnoDB: highest supported file format is Barracuda.
- 190119 12:55:54 InnoDB: Starting crash recovery from checkpoint LSN=25825591529
- InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
- 190119 12:55:54 InnoDB: Starting final batch to recover 15 pages from redo log
- 190119 12:55:54 [ERROR] 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.
- To report this bug, see http://kb.askmonty.org/en/reporting-bugs
- 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.
- Server version: 5.5.60-MariaDB
- key_buffer_size=134217728
- read_buffer_size=131072
- max_used_connections=0
- max_threads=153
- thread_count=0
- It is possible that mysqld could use up to
- key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 466718 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
服务状态:
#systemctl status mariadb
● mariadb.service - MariaDB database server
- Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
- Active: failed (Result: exit-code) since Sat 2019-01-19 12:49:08 CST; 3s ago
- Process: 9550 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=1/FAILURE)
- Process: 9549 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS)
- Process: 9517 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
- Main PID: 9549 (code=exited, status=0/SUCCESS)
- Jan 19 12:49:06 console systemd[1]: Starting MariaDB database server...
- Jan 19 12:49:06 console mariadb-prepare-db-dir[9517]: Database MariaDB is probably initialized in /var/lib/MySQL alre
- Jan 19 12:49:06 console mariadb-prepare-db-dir[9517]: If this is not the case, make sure the /var/lib/MySQL is empty
- Jan 19 12:49:06 console mysqld_safe[9549]: 190119 12:49:06 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
- Jan 19 12:49:06 console mysqld_safe[9549]: 190119 12:49:06 mysqld_safe Starting mysqld daemon with databases from /va
- Jan 19 12:49:08 console systemd[1]: mariadb.service: control process exited, code=exited status=1
- Jan 19 12:49:08 console systemd[1]: Failed to start MariaDB database server.
- Jan 19 12:49:08 console systemd[1]: Unit mariadb.service entered failed state.
- Jan 19 12:49:08 console systemd[1]: mariadb.service failed.
解决方式:
(1) 修改配置文件:
添加参数:
- innodb_force_recovery = 6 (此项由 1---6 个级别一次往上修改参数在失败的情况下)
- innodb_purge_threads = 0
(2) 修改刚才解压的文件属主属组为 MySQL 用户.
我的数据库配置文件:
- [mysqld]
- datadir=/var/lib/MySQL
- socket=/var/lib/MySQL/MySQL.sock
- lower_case_table_name=1
- character_set_server=utf8
- skip_name_resolve
- bind-address = 0.0.0.0
- innodb_force_recovery = 6
- innodb_purge_threads = 0
- # Disabling symbolic-links is recommended to prevent assorted security risks
- symbolic-links=0
- # Settings user and group are ignored when systemd is used.
- # If you need to run mysqld under a different user or group,
- # customize your systemd unit file for mariadb according to the
- # instructions in http://fedoraproject.org/wiki/Systemd
- [mysqld_safe]
- log-error=/var/log/mariadb/mariadb.log
- pid-file=/var/run/mariadb/mariadb.pid
- #
- # include all files from the config directory
- #
- !includedir /etc/my.cnf.d
来源: http://www.bubuko.com/infodetail-2928559.html