宝塔数据库无法启动傻瓜解决办法
数据库无法启动,今天无缘无故启动不起来了。
手动尝试了很多的修改,还是无法启动
结果用官方的脚本解决了。
使用方法
在ssh输入:wget -O sql-repair.sh http://download.bt.cn/install/sql-repair.sh && sh sql-repair.sh
运行宝塔检测脚本,然后可以排除错误。一般来说可以启动数据库了。
也可以自己创建一个脚本然后运行,代码如下
#!/bin/bash PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:~/bin export PATH Mysql_Check(){ if [ ! -f "/www/server/mysql/bin/mysql" ]; then echo "此服务器并没有安装宝塔mysql!" exit; fi ps -ef |grep /www/server/mysql |grep -v grep > null if [ $? -ne 1 ]; then echo "mysql is running! exit;" exit; fi } #硬盘检测 Disk_Check(){ DiskFree=`df -h | awk ' /\// {print $5, $6 "DiskCheck"}' | awk '/\/wwwDiskCheck/ {print $1}'` DiskInodes=`df -i | awk '/\/www/ {print $5, $6 "diskTest"}' |awk '/\/wwwdiskTest/ {print $1}'` MysqlBinDisk=`du -s /www/server/data/mysql-bin.* | awk '{size = size + $1} END {print size}'` MysqlDataDisk=`df /www/server/data | awk 'NR==2 {printf ("%.0f", $2/10)}'` if [ "${DiskFree}" == "100%" ] && [ "${MysqlBinDisk}" -gt "${MysqlDataDisk}" ]; then read -p "检测到可能因mysql日志导致磁盘占满,需要清理mysql日志尝试启动吗?(y/n):" clear if [ "${clear}" = "y" ]; then rm -f /www/server/data/mysql-bin.* rm -f /www/server/data/ib_* sleep 2 /etc/init.d/mysqld start exit; fi fi if [ "${DiskFree}" == "100%" ]; then df -h echo -e "============================================================" echo -e "磁盘已满导致Mysql无法正常启动" echo -e "你可以输入以下命令清除后启动mysql" echo -e "清空回收站->>\033[31mrm -rf /www/Recycle_bin/*\033[0m" echo -e "清理系统垃圾及网站日志->>\033[31mpython /www/server/panel/tools.py clear\033[0m" echo -e "启动mysql->>\033[31m/etc/init.d/mysqld start\033[0m" exit; fi if [ "${DiskInodes}" == "100%" ]; then df -i echo -e "============================================================" echo -e "磁盘inodes已满导致Mysql无法正常启动" echo -e "你可以输入以下命令尝试清除后启动mysql" echo -e "清理系统垃圾及网站日志->>\033[31mpython /www/server/panel/tools.py clear\033[0m" echo -e "启动mysql->>\033[31m/etc/init.d/mysqld start\033[0m" exit; fi } #根据机器配置调整配置文件 MySQL_Opt() { MemTotal=`free -m | grep Mem | awk '{print $2}'` if [[ ${MemTotal} -gt 1024 && ${MemTotal} -lt 2048 ]]; then sed -i "s#^key_buffer_size.*#key_buffer_size = 32M#" /etc/my.cnf sed -i "s#^table_open_cache.*#table_open_cache = 128#" /etc/my.cnf sed -i "s#^sort_buffer_size.*#sort_buffer_size = 768K#" /etc/my.cnf sed -i "s#^read_buffer_size.*#read_buffer_size = 768K#" /etc/my.cnf sed -i "s#^myisam_sort_buffer_size.*#myisam_sort_buffer_size = 8M#" /etc/my.cnf sed -i "s#^thread_cache_size.*#thread_cache_size = 16#" /etc/my.cnf sed -i "s#^query_cache_size.*#query_cache_size = 16M#" /etc/my.cnf sed -i "s#^tmp_table_size.*#tmp_table_size = 32M#" /etc/my.cnf sed -i "s#^innodb_buffer_pool_size.*#innodb_buffer_pool_size = 128M#" /etc/my.cnf sed -i "s#^innodb_log_file_size.*#innodb_log_file_size = 32M#" /etc/my.cnf elif [[ ${MemTotal} -ge 2048 && ${MemTotal} -lt 4096 ]]; then sed -i "s#^key_buffer_size.*#key_buffer_size = 64M#" /etc/my.cnf sed -i "s#^table_open_cache.*#table_open_cache = 256#" /etc/my.cnf sed -i "s#^sort_buffer_size.*#sort_buffer_size = 1M#" /etc/my.cnf sed -i "s#^read_buffer_size.*#read_buffer_size = 1M#" /etc/my.cnf sed -i "s#^myisam_sort_buffer_size.*#myisam_sort_buffer_size = 16M#" /etc/my.cnf sed -i "s#^thread_cache_size.*#thread_cache_size = 32#" /etc/my.cnf sed -i "s#^query_cache_size.*#query_cache_size = 32M#" /etc/my.cnf sed -i "s#^tmp_table_size.*#tmp_table_size = 64M#" /etc/my.cnf sed -i "s#^innodb_buffer_pool_size.*#innodb_buffer_pool_size = 256M#" /etc/my.cnf sed -i "s#^innodb_log_file_size.*#innodb_log_file_size = 64M#" /etc/my.cnf elif [[ ${MemTotal} -ge 4096 && ${MemTotal} -lt 8192 ]]; then sed -i "s#^key_buffer_size.*#key_buffer_size = 128M#" /etc/my.cnf sed -i "s#^table_open_cache.*#table_open_cache = 512#" /etc/my.cnf sed -i "s#^sort_buffer_size.*#sort_buffer_size = 2M#" /etc/my.cnf sed -i "s#^read_buffer_size.*#read_buffer_size = 2M#" /etc/my.cnf sed -i "s#^myisam_sort_buffer_size.*#myisam_sort_buffer_size = 32M#" /etc/my.cnf sed -i "s#^thread_cache_size.*#thread_cache_size = 64#" /etc/my.cnf sed -i "s#^query_cache_size.*#query_cache_size = 64M#" /etc/my.cnf sed -i "s#^tmp_table_size.*#tmp_table_size = 64M#" /etc/my.cnf sed -i "s#^innodb_buffer_pool_size.*#innodb_buffer_pool_size = 512M#" /etc/my.cnf sed -i "s#^innodb_log_file_size.*#innodb_log_file_size = 128M#" /etc/my.cnf elif [[ ${MemTotal} -ge 8192 && ${MemTotal} -lt 16384 ]]; then sed -i "s#^key_buffer_size.*#key_buffer_size = 256M#" /etc/my.cnf sed -i "s#^table_open_cache.*#table_open_cache = 1024#" /etc/my.cnf sed -i "s#^sort_buffer_size.*#sort_buffer_size = 4M#" /etc/my.cnf sed -i "s#^read_buffer_size.*#read_buffer_size = 4M#" /etc/my.cnf sed -i "s#^myisam_sort_buffer_size.*#myisam_sort_buffer_size = 64M#" /etc/my.cnf sed -i "s#^thread_cache_size.*#thread_cache_size = 128#" /etc/my.cnf sed -i "s#^query_cache_size.*#query_cache_size = 128M#" /etc/my.cnf sed -i "s#^tmp_table_size.*#tmp_table_size = 128M#" /etc/my.cnf sed -i "s#^innodb_buffer_pool_size.*#innodb_buffer_pool_size = 1024M#" /etc/my.cnf sed -i "s#^innodb_log_file_size.*#innodb_log_file_size = 256M#" /etc/my.cnf elif [[ ${MemTotal} -ge 16384 && ${MemTotal} -lt 32768 ]]; then sed -i "s#^key_buffer_size.*#key_buffer_size = 512M#" /etc/my.cnf sed -i "s#^table_open_cache.*#table_open_cache = 2048#" /etc/my.cnf sed -i "s#^sort_buffer_size.*#sort_buffer_size = 8M#" /etc/my.cnf sed -i "s#^read_buffer_size.*#read_buffer_size = 8M#" /etc/my.cnf sed -i "s#^myisam_sort_buffer_size.*#myisam_sort_buffer_size = 128M#" /etc/my.cnf sed -i "s#^thread_cache_size.*#thread_cache_size = 256#" /etc/my.cnf sed -i "s#^query_cache_size.*#query_cache_size = 256M#" /etc/my.cnf sed -i "s#^tmp_table_size.*#tmp_table_size = 256M#" /etc/my.cnf sed -i "s#^innodb_buffer_pool_size.*#innodb_buffer_pool_size = 2048M#" /etc/my.cnf sed -i "s#^innodb_log_file_size.*#innodb_log_file_size = 512M#" /etc/my.cnf elif [[ ${MemTotal} -ge 32768 ]]; then sed -i "s#^key_buffer_size.*#key_buffer_size = 1024M#" /etc/my.cnf sed -i "s#^table_open_cache.*#table_open_cache = 4096#" /etc/my.cnf sed -i "s#^sort_buffer_size.*#sort_buffer_size = 16M#" /etc/my.cnf sed -i "s#^read_buffer_size.*#read_buffer_size = 16M#" /etc/my.cnf sed -i "s#^myisam_sort_buffer_size.*#myisam_sort_buffer_size = 256M#" /etc/my.cnf sed -i "s#^thread_cache_size.*#thread_cache_size = 512#" /etc/my.cnf sed -i "s#^query_cache_size.*#query_cache_size = 512M#" /etc/my.cnf sed -i "s#^tmp_table_size.*#tmp_table_size = 512M#" /etc/my.cnf sed -i "s#^innodb_buffer_pool_size.*#innodb_buffer_pool_size = 4096M#" /etc/my.cnf sed -i "s#^innodb_log_file_size.*#innodb_log_file_size = 1024M#" /etc/my.cnf fi } #修复配置文件 Mycnf_repair(){ Setup_Path=/www/server/mysql Data_Path=/www/server/data mysql_version=`cat /www/server/mysql/version.pl` if [[ "${mysql_version}" =~ mariadb ]]; then cat > /etc/my.cnf<<EOF [client] #password = your_password port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock user = mysql datadir = ${Data_Path} basedir = ${Setup_Path} log_error = ${Data_Path}/mariadb.err #pid-file = ${Data_Path}/mariadb.pid default_storage_engine = MyISAM #skip-external-locking #loose-skip-innodb key_buffer_size = 16M max_allowed_packet = 100G table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M thread_cache_size = 8 query_cache_size = 8M tmp_table_size = 16M sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #skip-networking #skip-name-resolve max_connections = 500 max_connect_errors = 100 open_files_limit = 65535 log-bin=mysql-bin binlog_format=mixed server-id = 1 expire_logs_days = 10 default_storage_engine = InnoDB innodb_data_home_dir = ${Data_Path} innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = ${Data_Path} innodb_buffer_pool_size = 16M innodb_additional_mem_pool_size = 2M innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout EOF elif [[ "${mysql_version}" =~ AliSQL ]]; then cat > /etc/my.cnf<<EOF [client] sdafds #password = your_password port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock datadir = ${Data_Path} skip-external-locking performance_schema_max_table_instances=400 table_definition_cache=400 table_open_cache=32 key_buffer_size = 4M max_allowed_packet = 100G table_open_cache = 32 sort_buffer_size = 256K net_buffer_length = 8K read_buffer_size = 128K read_rnd_buffer_size = 256K myisam_sort_buffer_size = 4M thread_cache_size = 4 query_cache_size = 4M tmp_table_size = 8M explicit_defaults_for_timestamp = true #skip-networking #skip-name-resolve max_connections = 500 max_connect_errors = 100 open_files_limit = 65535 sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES log-bin=mysql-bin binlog_format=mixed server-id = 1 expire_logs_days = 10 #loose-innodb-trx=0 #loose-innodb-locks=0 #loose-innodb-lock-waits=0 #loose-innodb-cmp=0 #loose-innodb-cmp-per-index=0 #loose-innodb-cmp-per-index-reset=0 #loose-innodb-cmp-reset=0 #loose-innodb-cmpmem=0 #loose-innodb-cmpmem-reset=0 #loose-innodb-buffer-page=0 #loose-innodb-buffer-page-lru=0 #loose-innodb-buffer-pool-stats=0 #loose-innodb-metrics=0 #loose-innodb-ft-default-stopword=0 #loose-innodb-ft-inserted=0 #loose-innodb-ft-deleted=0 #loose-innodb-ft-being-deleted=0 #loose-innodb-ft-config=0 #loose-innodb-ft-index-cache=0 #loose-innodb-ft-index-table=0 #loose-innodb-sys-tables=0 #loose-innodb-sys-tablestats=0 #loose-innodb-sys-indexes=0 #loose-innodb-sys-columns=0 #loose-innodb-sys-fields=0 #loose-innodb-sys-foreign=0 #loose-innodb-sys-foreign-cols=0 default_storage_engine = InnoDB innodb_data_home_dir = ${Data_Path} innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = ${Data_Path} innodb_buffer_pool_size = 16M innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout EOF elif [[ "${mysql_version}" =~ 5.1 ]]; then cat > /etc/my.cnf<<EOF [client] #password = your_password port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock datadir = ${Data_Path} default_storage_engine = MyISAM #skip-external-locking #loose-skip-innodb key_buffer_size = 8M max_allowed_packet = 100G table_open_cache = 32 sort_buffer_size = 256K net_buffer_length = 4K read_buffer_size = 128K read_rnd_buffer_size = 256K myisam_sort_buffer_size = 4M thread_cache_size = 4 query_cache_size = 4M tmp_table_size = 8M #skip-networking #skip-name-resolve max_connections = 500 max_connect_errors = 100 open_files_limit = 65535 log-bin=mysql-bin binlog_format=mixed server-id = 1 expire_logs_days = 10 innodb_data_home_dir = ${Data_Path} innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = ${Data_Path} innodb_buffer_pool_size = 16M innodb_additional_mem_pool_size = 2M innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout EOF elif [[ "${mysql_version}" =~ 5.5 ]]; then cat > /etc/my.cnf<<EOF [client] #password = your_password port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock datadir = ${Data_Path} default_storage_engine = MyISAM #skip-external-locking #loose-skip-innodb key_buffer_size = 8M max_allowed_packet = 100G table_open_cache = 32 sort_buffer_size = 256K net_buffer_length = 4K read_buffer_size = 128K read_rnd_buffer_size = 256K myisam_sort_buffer_size = 4M thread_cache_size = 4 query_cache_size = 4M tmp_table_size = 8M #skip-networking #skip-name-resolve max_connections = 500 max_connect_errors = 100 open_files_limit = 65535 log-bin=mysql-bin binlog_format=mixed server-id = 1 expire_logs_days = 10 default_storage_engine = InnoDB innodb_data_home_dir = ${Data_Path} innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = ${Data_Path} innodb_buffer_pool_size = 16M innodb_additional_mem_pool_size = 2M innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout EOF elif [[ "${mysql_version}" =~ 5.6 ]]; then cat > /etc/my.cnf<<EOF [client] #password = your_password port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock datadir = ${Data_Path} skip-external-locking performance_schema_max_table_instances=400 table_definition_cache=400 table_open_cache=32 key_buffer_size = 8M max_allowed_packet = 100G table_open_cache = 32 sort_buffer_size = 256K net_buffer_length = 8K read_buffer_size = 128K read_rnd_buffer_size = 256K myisam_sort_buffer_size = 4M thread_cache_size = 4 query_cache_size = 4M tmp_table_size = 8M explicit_defaults_for_timestamp = true #skip-networking #skip-name-resolve max_connections = 500 max_connect_errors = 100 open_files_limit = 65535 sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES log-bin=mysql-bin binlog_format=mixed server-id = 1 expire_logs_days = 10 #loose-innodb-trx=0 #loose-innodb-locks=0 #loose-innodb-lock-waits=0 #loose-innodb-cmp=0 #loose-innodb-cmp-per-index=0 #loose-innodb-cmp-per-index-reset=0 #loose-innodb-cmp-reset=0 #loose-innodb-cmpmem=0 #loose-innodb-cmpmem-reset=0 #loose-innodb-buffer-page=0 #loose-innodb-buffer-page-lru=0 #loose-innodb-buffer-pool-stats=0 #loose-innodb-metrics=0 #loose-innodb-ft-default-stopword=0 #loose-innodb-ft-inserted=0 #loose-innodb-ft-deleted=0 #loose-innodb-ft-being-deleted=0 #loose-innodb-ft-config=0 #loose-innodb-ft-index-cache=0 #loose-innodb-ft-index-table=0 #loose-innodb-sys-tables=0 #loose-innodb-sys-tablestats=0 #loose-innodb-sys-indexes=0 #loose-innodb-sys-columns=0 #loose-innodb-sys-fields=0 #loose-innodb-sys-foreign=0 #loose-innodb-sys-foreign-cols=0 default_storage_engine = InnoDB innodb_data_home_dir = ${Data_Path} innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = ${Data_Path} innodb_buffer_pool_size = 16M innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout EOF elif [[ "${mysql_version}" =~ 5.7 ]]; then cat > /etc/my.cnf<<EOF [client] #password = your_password port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock datadir = ${Data_Path} skip-external-locking performance_schema_max_table_instances=400 table_definition_cache=400 table_open_cache=256 key_buffer_size = 16M max_allowed_packet = 100G table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M thread_cache_size = 8 query_cache_size = 8M tmp_table_size = 16M sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES explicit_defaults_for_timestamp = true #skip-networking max_connections = 500 max_connect_errors = 100 open_files_limit = 65535 log-bin=mysql-bin binlog_format=mixed server-id = 1 expire_logs_days = 10 early-plugin-load = "" #loose-innodb-trx=0 #loose-innodb-locks=0 #loose-innodb-lock-waits=0 #loose-innodb-cmp=0 #loose-innodb-cmp-per-index=0 #loose-innodb-cmp-per-index-reset=0 #loose-innodb-cmp-reset=0 #loose-innodb-cmpmem=0 #loose-innodb-cmpmem-reset=0 #loose-innodb-buffer-page=0 #loose-innodb-buffer-page-lru=0 #loose-innodb-buffer-pool-stats=0 #loose-innodb-metrics=0 #loose-innodb-ft-default-stopword=0 #loose-innodb-ft-inserted=0 #loose-innodb-ft-deleted=0 #loose-innodb-ft-being-deleted=0 #loose-innodb-ft-config=0 #loose-innodb-ft-index-cache=0 #loose-innodb-ft-index-table=0 #loose-innodb-sys-tables=0 #loose-innodb-sys-tablestats=0 #loose-innodb-sys-indexes=0 #loose-innodb-sys-columns=0 #loose-innodb-sys-fields=0 #loose-innodb-sys-foreign=0 #loose-innodb-sys-foreign-cols=0 default_storage_engine = InnoDB innodb_data_home_dir = ${Data_Path} innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = ${Data_Path} innodb_buffer_pool_size = 16M innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout EOF fi MySQL_Opt } #设置权限 Set_permission() { chown -R mysql:mysql /www/server/data chown -R mysql:mysql /www/server/mysql chmod 777 /tmp } #清除二进制日志 logs_clear(){ if [ ! -d "/www/server/data/logBackup" ]; then mkdir -p /www/server/data/logBackup mv /www/server/data/mysql-bin.* /www/server/data/logBackup mv /www/server/data/ib_* /www/server/data/logBackup fi } Mysql_Check Disk_Check myCnf=`cat /etc/my.cnf` if [ ! -f "/etc/my.cnf" ]; then Mycnf_repair else mv /etc/my.cnf /etc/my.cnf.backup Mycnf_repair fi /etc/init.d/mysqld stop if [ -f "/tmp/mysql.sock" ]; then rm -f /tmp/mysql.sock fi Set_permission logs_clear /etc/init.d/mysqld start sleep 3 ps -fe|grep mysql |grep -v grep if [ $? -ne 0 ]; then echo "==========================================================================================" cat /www/server/data/*.err | grep ERROR echo "==========================================================================================" echo "mysql start error." echo "mysql无法正常启动,请将以上错误信息截图发至论坛寻求帮助" else echo "==========================================================================================" echo "mysql is running" fi rm -f sql-repair.sh
版权声明:
作者:applek
链接:http://www.lovestu.com/btdbstart.html
文章版权归作者所有,未经允许请勿转载。
THE END