9

I'm trying to restart MySQL after saving a script. The error states
Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

When running systemctl status mysql.service here's what I get:

Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: activating (start-post) (Result: exit-code) since Isn 2016-09-26 17:15:28 MYT; 17s ago
  Process: 17478 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
  Process: 17474 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 17478 (code=exited, status=1/FAILURE);         : 17479 (mysql-systemd-s)
   CGroup: /system.slice/mysql.service
           └─control
             ├─17479 /bin/bash /usr/share/mysql/mysql-systemd-start post
             └─17516 sleep 1

I'm not sure where to start fixing this.

This is the script I saved prior to restarting mysql:

[mysqld]
innodb_rollback_on_timeout=1
innodb_lock_wait_timeout=600
max_connections=350
log-bin=mysql-bin
binlog-format = 'ROW'

Running journalctl -xe | tail -20 gives me this:

Sep 26 17:46:29 guest mysqld[21937]: 2016-09-26T09:46:29.037709Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
Sep 26 17:46:29 guest mysqld[21937]: 2016-09-26T09:46:29.037798Z 0 [Warning] Changed limits: max_connections: 214 (requested 350)
Sep 26 17:46:29 guest mysqld[21937]: 2016-09-26T09:46:29.037809Z 0 [Warning] Changed limits: table_open_cache: 400 (requested 2000)
Sep 26 17:46:29 guest mysqld[21937]: 2016-09-26T09:46:29.187886Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
Sep 26 17:46:29 guest mysqld[21937]: 2016-09-26T09:46:29.189296Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.15-0ubuntu0.16.04.1-log) starting as process 21937 ...
Sep 26 17:46:29 guest mysqld[21937]: 2016-09-26T09:46:29.191216Z 0 [ERROR] You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server start-up parameters documentation
Sep 26 17:46:29 guest mysqld[21937]: 2016-09-26T09:46:29.191242Z 0 [ERROR] Aborting
Sep 26 17:46:29 guest mysqld[21937]: 2016-09-26T09:46:29.191255Z 0 [Note] Binlog end
Sep 26 17:46:29 guest mysqld[21937]: 2016-09-26T09:46:29.191300Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
Sep 26 17:46:29 guest systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
Sep 26 17:46:32 guest NetworkManager[878]: <info>  [1474883192.2910] device (eth0): Lowering IPv6 MTU (9000) to match device MTU (1500)

Running ulimit && ulimit -Sa gives me:

unlimited
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 128071
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 128071
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
  • 1
    Can you append the output of journalctl -xe | tail -50 to your question? – L. D. James Sep 26 '16 at 9:40
  • @L.D.James the whole output? It's pretty large. Or is it just the last few bits? – Edd Sep 26 '16 at 9:42
  • The output will be 50 lines. Actually 15 or 20 lines should suffice. You can change the -50 to -15. I just wanted to be sure to include the line where the actually problem is occurring. – L. D. James Sep 26 '16 at 9:45
  • @L.D.James I see, so that's what the tail command is for. I've added a journalctl -xe | tail -20 output. – Edd Sep 26 '16 at 9:50
  • 2
    What about the "ERROR: You have enabled the binary log..." message? Did you intend to do that? MySQL expects a server id there, which I only know from master-slave setups. – Jos Sep 26 '16 at 11:08
4

Fatal Error:

MYQL is failing to start because as the Journalctl output indicates, but you haven't provided the mandatory server-id. Add the mandatory id as indicated in the code below.

/etc/mysql/mysql.conf.d/mysqld.cnf
Change from:

log-bin=mysql-bin
binlog-format = 'ROW'

Change to:

server-id=master-01
log-bin=mysql-bin
binlog-format = 'ROW'

This will get your server running while you address the proper formatting of your desired logging.

non-fatal warnings:

For the open files limit of your error you will have to either decrease the demand in the configuration you edited or increase the system's limits with:

/etc/security/limits.conf

* soft nofile [new number]
* hard nofile [new number]

The first column describes WHO the limit is to apply for. '' is a wildcard, meaning all users. To raise the limits for root, you have to explicitly enter 'root' instead of ''.

Taken from:
https://askubuntu.com/a/162230/29012

  • Worked! Is it because of explicitly stating it's the master device? I tried putting in server-id = 1 before this but it didn't work. – Edd Sep 27 '16 at 3:37
  • in which file these things should change? – Vijay Sharma Nov 2 '17 at 9:04
  • For the mysql configuration edit /etc/mysql/mysql.conf.d/mysqld.cnf. For the system's open files limit look at: /etc/security/limits.conf . I'll update my answer with this information a little later. Can you let me know if your issue becomes resolved? This way I can include any other pertinent information in the answer. – L. D. James Nov 2 '17 at 9:40
1

Got the same error, but in my case simply the disk was full. Make also sure to check this.

df -h --total
  • +1 This was it for me. Free up some space and start the service. – Saif Nov 30 '18 at 20:12
0

Resolve the issue by:

  1. Adding Swapfile
  2. Adding innodb_buffer_pool_size = 20M to configuration file /etc/mysql/my.cnf making sure it is in a group
0

My problem is the host's IP is changed.

So edit the bind-address in /etc/mysql/my.cnf to the correct one.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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