Troubleshooting MySQL on Ubuntu

Environment

Operating System     : Ubuntu Server 16.10 Yakkety Yak 64-bit Server Edition
MySQL Server Version : Ver 14.14 Distrib 5.7.16, for Linux (x86_64)
  • Slove MySQL Server Keeps Stopping Unexpectedly

Today I upgrade my server to Ubuntu 16.10, but MySQL server keeps stopping unexpectedly. I sloved by add Swap on Ubuntu and decrease the innodb_buffer_pool_size for InnoDB stuff. The configuration file name and directory change in Ubuntu 16.10. Add or replace existing line to the /etc/mysql/mysql.conf.d/mysqld.cnf:

innodb_buffer_pool_size = 20M
  • Slove Error in Log: [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

Add following line to [mysqld] section to the /etc/mysql/mysql.conf.d/mysqld.cnf:

explicit_defaults_for_timestamp = 1 
  • Slove Error in Log: [Warning] Changed limits: max_open_files: 1024 (requested 5000)

The limits for the MySQL service are defined in the Systemd configuration file: /lib/systemd/system/mysql.service, add the following lines to the bottom of the file:

LimitNOFILE=infinity
LimitMEMLOCK=infinity

You could also set a numeric limit, eg LimitNOFILE=4510.

Now reload the Systemd configuration with:

$ sudo systemctl daemon-reload

Restart MySQL and it should now obey the max_open_files or table_open_cache directive.

Troubleshooting MySQL on Ubuntu
2 votes, 5.00 avg. rating (98% score)