0

On my VPS, mysqld starts and right after that, it automatically stops and then starts again automatically.

I tried sudo service mysqld stop and it succeeded. After I started it manually and succeeded. But it suddenly stopped and started and keeping starting and stopping.

Every time I got different PIDs.

How can I fix this?

I started mysqld manually and got these errors:

2018-12-30 16:51:00 4535 [Note] Plugin 'FEDERATED' is disabled.
2018-12-30 16:51:00 4535 [Warning] option 'innodb-buffer-pool-size': signed value 65536 adjusted to 5242880
2018-12-30 16:51:00 4535 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-12-30 16:51:00 4535 [Note] InnoDB: The InnoDB memory heap is disabled
2018-12-30 16:51:00 4535 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-12-30 16:51:00 4535 [Note] InnoDB: Memory barrier is not used
2018-12-30 16:51:00 4535 [Note] InnoDB: Compressed tables use zlib 1.2.11
2018-12-30 16:51:00 4535 [Note] InnoDB: Using Linux native AIO
2018-12-30 16:51:00 4535 [Note] InnoDB: Using CPU crc32 instructions
2018-12-30 16:51:00 4535 [Note] InnoDB: Initializing buffer pool, size = 5.0M
2018-12-30 16:51:00 4535 [Note] InnoDB: Completed initialization of buffer pool
2018-12-30 16:51:00 4535 [Note] InnoDB: Highest supported file format is Barracuda.
2018-12-30 16:51:00 4535 [Note] InnoDB: 128 rollback segment(s) are active.
2018-12-30 16:51:00 4535 [Note] InnoDB: 5.6.42 started; log sequence number 4940394469
2018-12-30 16:51:00 4535 [Note] InnoDB: !!! innodb_force_recovery is set to 3 !!!
2018-12-30 16:51:00 4535 [Note] Server hostname (bind-address): '*'; port: 3306
2018-12-30 16:51:00 4535 [Note] IPv6 is available.
2018-12-30 16:51:00 4535 [Note]   - '::' resolves to '::';
2018-12-30 16:51:00 4535 [Note] Server socket created on IP: '::'.
2018-12-30 16:51:00 4535 [Warning] 'user' entry 'root@v4452.vir.kagoya.net' ignored in --skip-name-resolve mode.
2018-12-30 16:51:00 4535 [Warning] 'user' entry '@v4452.vir.kagoya.net' ignored in --skip-name-resolve mode.
2018-12-30 16:51:00 4535 [Warning] 'proxies_priv' entry '@ root@v4452.vir.kagoya.net' ignored in --skip-name-resolve mode.
2018-12-30 16:51:00 4535 [Note] Event Scheduler: Loaded 0 events
2018-12-30 16:51:00 4535 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.42'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2018-12-30 16:51:15 4535 [ERROR] /usr/sbin/mysqld: Table './mylife/wp_options' is marked as crashed and should be repaired
2018-12-30 16:51:15 4535 [ERROR] /usr/sbin/mysqld: Table './mylife/wp_options' is marked as crashed and should be repaired
2018-12-30 16:51:15 4535 [ERROR] /usr/sbin/mysqld: Table './mylife/wp_options' is marked as crashed and should be repaired
2018-12-30 16:53:56 4535 [ERROR] /usr/sbin/mysqld: Table './mylife/wp_options' is marked as crashed and should be repaired
2018-12-30 16:53:56 4535 [ERROR] /usr/sbin/mysqld: Table './mylife/wp_options' is marked as crashed and should be repaired
2018-12-30 16:53:56 4535 [ERROR] /usr/sbin/mysqld: Table './mylife/wp_options' is marked as crashed and should be repaired
2018-12-30 16:54:16 4535 [ERROR] /usr/sbin/mysqld: Table './mylife/wp_options' is marked as crashed and should be repaired
2018-12-30 16:54:16 4535 [ERROR] /usr/sbin/mysqld: Table './mylife/wp_options' is marked as crashed and should be repaired
2018-12-30 16:54:16 4535 [ERROR] /usr/sbin/mysqld: Table './mylife/wp_options' is marked as crashed and should be repaired
2018-12-30 16:54:32 4535 [ERROR] /usr/sbin/mysqld: Table './mylife/wp_options' is marked as crashed and should be repaired
2018-12-30 16:54:32 4535 [ERROR] /usr/sbin/mysqld: Table './mylife/wp_options' is marked as crashed and should be repaired
2018-12-30 16:54:32 4535 [ERROR] /usr/sbin/mysqld: Table './mylife/wp_options' is marked as crashed and should be repaired
2018-12-30 16:54:33 4535 [ERROR] /usr/sbin/mysqld: Table './mylife/wp_options' is marked as crashed and should be repaired
2018-12-30 16:54:33 4535 [ERROR] /usr/sbin/mysqld: Table './mylife/wp_options' is marked as crashed and should be repaired
2018-12-30 16:54:33 4535 [ERROR] /usr/sbin/mysqld: Table './mylife/wp_options' is marked as crashed and should be repaired
2018-12-30 16:56:18 4535 [ERROR] /usr/sbin/mysqld: Table './mylife/wp_options' is marked as crashed and should be repaired
2018-12-30 16:56:18 4535 [ERROR] /usr/sbin/mysqld: Table './mylife/wp_options' is marked as crashed and should be repaired

I also did in the MySQL CLI:

mysql> use mylife;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1

The mylife DB has InnoDB tables.

  • Have you checked the mysql error log to see why it's crashing? – jordanm Dec 30 '18 at 7:40
  • @jordanm I haven't. So I started mysqld manually and pasted it at pastebin.com/c52FxhBZ . And I also posted what I did in MySQL CLI at pastebin.com/fYvV86f0 . – Masaru Kitajima Dec 30 '18 at 7:59
  • Are those myisam tables? – Rui F Ribeiro Dec 30 '18 at 8:09
  • @RuiFRibeiro No, all databases' tables are InnoDB. – Masaru Kitajima Dec 30 '18 at 8:11
  • Care to tell us what happened before? I think you are not telling us the whole story. Restoring a backup? – Rui F Ribeiro Dec 30 '18 at 8:14
2

You have a corrupt table; innodb_force_recovery is not meant to be used with the normal MySQL startup, and innodb_force_recovery is set to an invalid value, hence MySQL giving up on running.

From your MySQL logs:

  • Your WordPress DB table /mylife/wp_options is marked as corrupted;
  • innodb_force_recovery is set to an invalid value.

Setting innodb_force_recovery to 1 and starting MySQL might allow you to repair the table manually; however MySQL is not started normally.

Start with with the following command:

/usr/sbin/mysqld --skip-grant-tables --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/HOSTNAME.pid --skip-external-locking --port=3306 --socket=/var/lib/mysql/mysql.sock

Then do:

# mysql
> use mylife;
> repair table wp_options;
> exit;

Now, REMOVE the innodb_force_recovery setting. You will be able to restart MySQL afterwards.

For reference, see mysql Innodb – Table ‘user’ is marked as crashed and should be repaired

On the event MySQL does not manage to recover the table (unlikely), OR you tried to restore the table file manually, you have to restore the table wp_options from a DB dump before MySQL goes up. InnoDB tables are not meant to be restored as individual table files as MyISAM was, it is a common mistake I have seen people doing over and over again.

Relevant logs:

InnoDB: !!! innodb_force_recovery is set to 3 !!!  
[ERROR] /usr/sbin/mysqld: Table './mylife/wp_options' is marked as crashed and should be repaired

Furthermore, if you are using the option --skip-name-resolve, you have to do your grants by IP address and not by DNS name, according to following logs. Mind you this is a warning, and might be remnants of a previous setup; however for the lack of a better context on your question, we cannot be sure what to say about it.

[Warning] 'user' entry 'root@v4452.vir.kagoya.net' ignored in --skip-name-resolve mode.
[Warning] 'user' entry '@v4452.vir.kagoya.net' ignored in --skip-name-resolve mode.
[Warning] 'proxies_priv' entry '@ root@v4452.vir.kagoya.net' ignored in --skip-name-resolve mode.

As for getting different PIDs, if a service is dying / restarting, it is normal behaviour to get different PIDs into each subsequent run.

  • I followed your kind suggestions. But I couldn't start MySQL. It's impossible to paste the messages here, so I put it at pastebin.com/4WnZ2r5q . – Masaru Kitajima Dec 30 '18 at 10:37
  • So, it mysql working now? – Rui F Ribeiro Dec 30 '18 at 10:42
  • No, when tried mysql, I got an error. ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) – Masaru Kitajima Dec 30 '18 at 10:44
  • I would restore that table from a backup dump then. Watch out for any new errors – Rui F Ribeiro Dec 30 '18 at 10:48
  • I have no backup. I know it was my big mistake that I haven't set any regular back up. And I couldn't use mysqldump now. – Masaru Kitajima Dec 30 '18 at 10:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.