![每五分钟,如果 MySQL 不接受连接,则自动重启](https://linux22.com/image/1127426/%E6%AF%8F%E4%BA%94%E5%88%86%E9%92%9F%EF%BC%8C%E5%A6%82%E6%9E%9C%20MySQL%20%E4%B8%8D%E6%8E%A5%E5%8F%97%E8%BF%9E%E6%8E%A5%EF%BC%8C%E5%88%99%E8%87%AA%E5%8A%A8%E9%87%8D%E5%90%AF.png)
我有一个客户,我为其进行 Web 开发,该客户有一个基本的低流量 WordpRess 网站,在 Ubuntu 12.0.4 上通过 Digitalocean 运行。
他们提供预先构建并安装了 WordPress 的基本 LAMP 堆栈。
大约每周 3-4 次,MySQL DB 会出现故障并在网站上显示以下消息:
建立数据库连接时出错
然后我必须通过 SSH 进入该框并重新启动 MySQL。只需 15 秒,但如果我没有注意到,它可能会停机数小时。这对我的客户不公平,因为它是随机的,有时每周多达 6 次!
我是一名 PHP 和 JavaScript 开发人员,因此我的服务器管理技能有限。
请问有人能帮助我如何找到问题、问题的根源并希望永久解决它吗?
更新
我这里有一个日志文件/var/log/mysql.log
,其中的最后修改日期/时间大约是今天 MySQL 服务器崩溃时。不过它是一个空文件。
按日期时间对该目录中的文件进行排序,/var/log/
我可以看到最近修改的文件之一名为系统日志
我粘贴了系统日志下面提到MySQL所以我觉得如果有人能尝试帮助我理解这一点的话,这可能是相关的?
/var/log/syslog
Jun 24 16:17:01 thomaslastname CRON[928]: (root) CMD ( cd / && run-parts --report /etc/cron.hourly)
Jun 24 16:32:27 thomaslastname kernel: [2738822.445529] type=1400 audit(1435177947.529:25): apparmor="STATUS" operation="profile_replace" profile="unconfined" name="/usr/sbin/mysqld" pid=1048 comm="apparmor_parser"
Jun 24 16:32:29 thomaslastname /etc/mysql/debian-start[1102]: Upgrading MySQL tables if necessary.
Jun 24 16:32:29 thomaslastname /etc/mysql/debian-start[1105]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored
Jun 24 16:32:29 thomaslastname /etc/mysql/debian-start[1105]: Looking for 'mysql' as: /usr/bin/mysql
Jun 24 16:32:29 thomaslastname /etc/mysql/debian-start[1105]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Jun 24 16:32:29 thomaslastname /etc/mysql/debian-start[1105]: This installation of MySQL is already upgraded to 5.5.38, use --force if you still need to run mysql_upgrade
Jun 24 16:32:29 thomaslastname /etc/mysql/debian-start[1116]: Checking for insecure root accounts.
Jun 24 16:32:29 thomaslastname /etc/mysql/debian-start[1122]: Triggering myisam-recover for all MyISAM tables
Jun 24 16:39:01 thomaslastname CRON[1208]: (root) CMD ( [ -x /usr/lib/php5/maxlifetime ] && [ -x /usr/lib/php5/sessionclean ] && [ -d /var/lib/php5 ] && /usr/lib/php5/sessionclean /var/lib/php5 $(/usr/lib/php5/maxlifetime))
Jun 24 16:39:01 thomaslastname postfix/pickup[32544]: 5F48363D60: uid=0 from=<root>
Jun 24 16:39:01 thomaslastname postfix/cleanup[1221]: 5F48363D60: message-id=<20150624203901.5F48363D60@WP-NewBase-052814>
Jun 24 16:39:01 thomaslastname postfix/qmgr[1002]: 5F48363D60: from=<root@WP-NewBase-052814>, size=866, nrcpt=1 (queue active)
Jun 24 16:39:01 thomaslastname postfix/local[1223]: 5F48363D60: to=<root@WP-NewBase-052814>, orig_to=<root>, relay=local, delay=0.02, delays=0.01/0/0/0, dsn=2.0.0, status=sent (delivered to mailbox)
Jun 24 16:39:01 thomaslastname postfix/qmgr[1002]: 5F48363D60: removed
/var/log/mysql/error.log
150624 16:32:27 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
150624 16:32:27 [Note] Plugin 'FEDERATED' is disabled.
150624 16:32:27 InnoDB: The InnoDB memory heap is disabled
150624 16:32:27 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150624 16:32:27 InnoDB: Compressed tables use zlib 1.2.8
150624 16:32:27 InnoDB: Using Linux native AIO
150624 16:32:27 InnoDB: Initializing buffer pool, size = 128.0M
150624 16:32:27 InnoDB: Completed initialization of buffer pool
150624 16:32:27 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 18880361123
150624 16:32:27 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...
InnoDB: Doing recovery: scanned up to log sequence number 18880542657
150624 16:32:27 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
150624 16:32:27 InnoDB: Waiting for the background threads to start
150624 16:32:28 InnoDB: 5.5.38 started; log sequence number 18880542657
150624 16:32:28 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
150624 16:32:28 [Note] - '127.0.0.1' resolves to '127.0.0.1';
150624 16:32:28 [Note] Server socket created on IP: '127.0.0.1'.
150624 16:32:28 [Note] Event Scheduler: Loaded 0 events
150624 16:32:28 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.38-0ubuntu0.14.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
150624 16:32:29 [ERROR] /usr/sbin/mysqld: Table './wordpress/wp_aiowps_login_activity' is marked as crashed and should be repaired
150624 16:32:29 [Warning] Checking table: './wordpress/wp_aiowps_login_activity'
答案1
不幸的是,这里没有足够的信息来全面诊断连接问题。您的 MySQL 错误日志显示 MySQL 崩溃了,但没有说明原因。
如果愿意,您可以继续调查,但同时您可以应用一个解决方案。
每五分钟,如果 MySQL 不接受连接,则自动重启
笔记:这将使 MySQL 停止服务约六分钟。
打开 MySQL 提示符或在 phpMyAdmin 中运行命令(选择一个比更好的字母数字、无空格的密码
abcdefg
):CREATE USER 'hang-check'@'localhost' IDENTIFIED BY 'abcdefg';
确保您可以以身份登录数据库,
hang-check
并且他们看不到您的任何 WordPress 数据库。在服务器上打开终端,然后运行:
sudo -i EDITOR=nano crontab -e
在 crontab 文件中,插入以下行 (更改
abcdefg
为你之前选择的密码),保存(Ctrl+ Othen Enter),然后退出回到终端(Ctrl+ X):*/5 * * * * /usr/bin/mysql --host=127.0.0.1 --port=3306 --connect_timeout=30 --user=hang-check --password='abcdefg' -e '' || (/usr/bin/killall -9 mysqld_safe mysqld; /bin/sleep 15; /usr/sbin/service mysql start)
在同一个终端中运行此命令:
sudo -i service mysql stop killall -9 mysqld_safe mysqld sleep 360; ps -umysql
等待六分钟直至完成。
- 确保最后一条命令以 结尾的行作为响应
mysqld
。如果没有,请运行service mysql start
并在此处注释。 - 关闭终端。