MYSQL 5.7 Percona XtraDBCluster - Cant start MYSQL - Digital Ocean Box

mysql percona-xtradb-cluster
Frank picture Frank · Dec 11, 2017 · Viewed 9.6k times · Source

I rebooted a digital ocean box and now I can't start mysql. When I run start command i get : 

Redirecting to /bin/systemctl restart mysql.service
Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

Result of systemctl status mysql.service

● mysql.service - Percona XtraDB Cluster
   Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Mon 2017-12-11 17:08:18 UTC; 5s ago
  Process: 26300 ExecStopPost=/usr/bin/mysql-systemd stop-post (code=exited, status=0/SUCCESS)
  Process: 26270 ExecStop=/usr/bin/mysql-systemd stop (code=exited, status=2)
  Process: 25674 ExecStartPost=/usr/bin/mysql-systemd start-post $MAINPID (code=exited, status=1/FAILURE)
  Process: 25673 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS)
  Process: 25632 ExecStartPre=/usr/bin/mysql-systemd start-pre (code=exited, status=0/SUCCESS)
 Main PID: 25673 (code=exited, status=0/SUCCESS)

Dec 11 17:08:18 server-name-hidden mysql-systemd[25674]: ERROR! mysqld_safe with PID 25673 has already exited: FAILURE
Dec 11 17:08:18 server-name-hidden systemd[1]: mysql.service: control process exited, code=exited status=1
Dec 11 17:08:18 server-name-hidden mysql-systemd[26270]: WARNING: mysql pid file /var/run/mysqld/mysqld.pid empty or not readable
Dec 11 17:08:18 server-name-hidden mysql-systemd[26270]: ERROR! mysql already dead
Dec 11 17:08:18 server-name-hidden systemd[1]: mysql.service: control process exited, code=exited status=2
Dec 11 17:08:18 server-name-hidden mysql-systemd[26300]: WARNING: mysql pid file /var/run/mysqld/mysqld.pid empty or not readable
Dec 11 17:08:18 server-name-hidden mysql-systemd[26300]: WARNING: mysql may be already dead
Dec 11 17:08:18 server-name-hidden systemd[1]: Failed to start Percona XtraDB Cluster.
Dec 11 17:08:18 server-name-hidden systemd[1]: Unit mysql.service entered failed state.
Dec 11 17:08:18 server-name-hidden systemd[1]: mysql.service failed.

/var/run/mysqld/ is owned by mysql user - but it's empty. If i add a mysqld.pid file it gets removed when i run mysql start.

Does anyone know why a reboot would cause this or give me any next steps. I have reviewed the mysqld.log file and can't see anything of use. here are the last 30 lines 

017-12-11T15:14:42.473164Z 0 [Note] WSREP: Received shutdown signal. Will sleep for 10 secs before initiating shutdown. pxc_maint_mode switched to SHUTDOWN
2017-12-11T15:14:52.474584Z 0 [Note] WSREP: Stop replication
2017-12-11T15:14:52.474691Z 0 [Note] WSREP: Closing send monitor...
2017-12-11T15:14:52.475160Z 0 [Note] WSREP: Closed send monitor.
2017-12-11T15:14:52.475268Z 0 [Note] WSREP: gcomm: terminating thread
2017-12-11T15:14:52.475305Z 0 [Note] WSREP: gcomm: joining thread
2017-12-11T15:14:52.475886Z 0 [Note] WSREP: gcomm: closing backend
2017-12-11T15:14:52.476079Z 0 [Note] WSREP: Current view of cluster as seen by this node
2017-12-11T15:14:52.476491Z 0 [Note] WSREP: gcomm: closed
2017-12-11T15:14:52.476532Z 0 [Note] WSREP: Received self-leave message.
2017-12-11T15:14:52.476559Z 0 [Note] WSREP: Flow-control interval: [0, 0]
2017-12-11T15:14:52.476595Z 0 [Note] WSREP: Trying to continue unpaused monitor
2017-12-11T15:14:52.476602Z 0 [Note] WSREP: Received SELF-LEAVE. Closing connection.
2017-12-11T15:14:52.476608Z 0 [Note] WSREP: Shifting SYNCED -> CLOSED (TO: 13842228)
2017-12-11T15:14:52.476632Z 0 [Note] WSREP: RECV thread exiting 0: Success
2017-12-11T15:14:52.477098Z 0 [Note] WSREP: recv_thread() joined.
2017-12-11T15:14:52.477110Z 0 [Note] WSREP: Closing replication queue.
2017-12-11T15:14:52.477116Z 0 [Note] WSREP: Closing slave action queue.
2017-12-11T15:14:52.477123Z 0 [Note] Giving 63 client threads a chance to die gracefully
2017-12-11T15:14:52.478905Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 21  user: ‘hidden’
2017-12-11T15:14:52.478957Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 22  user: 'hidden'
2017-12-11T15:14:52.478994Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 6387  user: 'hidden'
2017-12-11T15:14:52.479034Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 6367  user: 'hidden'
2017-12-11T15:14:52.479084Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 6373  user: 'hidden'
2017-12-11T15:14:52.479130Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 6368  user: 'hidden'
2017-12-11T15:14:54.479289Z 0 [Note] WSREP: Waiting for active wsrep applier to exit

I've also been told to try : systemctl start mysql@bootstrap however this fails with the same error. here is the result from journalctl -xe

Dec 11 17:24:58 dropletname systemd[1]: [email protected]: control process exited, code=exited status=1
Dec 11 17:24:58 dropletname mysql-systemd[28663]: WARNING: mysql pid file /var/run/mysqld/mysqld.pid empty or not readable
Dec 11 17:24:58 dropletname mysql-systemd[28663]: ERROR! mysql already dead
Dec 11 17:24:58 dropletname systemd[1]: [email protected]: control process exited, code=exited status=2
Dec 11 17:24:58 dropletname mysql-systemd[28694]: WARNING: mysql pid file /var/run/mysqld/mysqld.pid empty or not readable
Dec 11 17:24:58 dropletname mysql-systemd[28694]: WARNING: mysql may be already dead
Dec 11 17:24:58 dropletname systemd[1]: Failed to start Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap.


Dec 11 17:24:58 dropletname systemd[1]: Unit [email protected] entered failed state.
Dec 11 17:24:58 dropletname systemd[1]: [email protected] failed.
Dec 11 17:24:58 dropletname polkitd[510]: Unregistered Authentication Agent for unix-process:28009:649814 (system bus name :1.159, object path /org/freedesktop/PolicyKit1/AuthenticationAgent, locale en_GB.UTF-8) (disconnected from bus)
Dec 11 17:25:02 dropletname sendmail[27970]: unable to qualify my own domain name (dropletname) -- using short name
Dec 11 17:25:02 dropletname sendmail[27970]: vBBHP2cx027970: from=hidden, size=1655, class=-60, nrcpts=1, msgid=<201712111725.vBBHP2cx027970@dropletname>, relay=hidden@localhost
Dec 11 17:25:02 dropletname sendmail[28728]: vBBHP26v028728: from=<hidden@dropletname>, size=1935, class=-60, nrcpts=1, msgid=<201712111725.vBBHP2cx027970@dropletname>, proto=ESMTP, daemon=MTA, relay=dropletname [127.0.0.1]
Dec 11 17:25:02 dropletname sendmail[27970]: vBBHP2cx027970: to=hidden, ctladdr=hidden (1000/1000), delay=00:00:00, xdelay=00:00:00, mailer=relay, pri=139655, relay=[127.0.0.1] [127.0.0.1], dsn=2.0.0, stat=Sent (vBBHP26v028728 Message accepted for del
Dec 11 17:25:02 dropletname sendmail[28729]: vBBHP26v028728: to=<hidden@dropletname>, ctladdr=<hidden@dropletname> (1000/1000), delay=00:00:00, xdelay=00:00:00, mailer=local, pri=140148, dsn=2.0.0, stat=Sent
Dec 11 17:25:02 dropletname systemd[1]: Removed slice User Slice of hidden

ps aux|grep mysql 

root     11590  0.0  0.0 107924   608 pts/0    T    15:43   0:00 cat /var/log/mysqld.log
root     24666  0.0  0.0 107924   612 pts/0    T    16:52   0:00 cat /var/log/mysqld.log
root     32182  0.0  0.0 112664   972 pts/2    S+   17:59   0:00 grep --color=auto mysq

Answer

Frank picture Frank · Dec 11, 2017

I managed to resolve this in the end however i'm not sure what 100% did it. I changed /etc/my.conf to look like this : 

!includedir /etc/my.cnf.d/
!includedir /etc/percona-xtradb-cluster.conf.d/

[client]
socket=/var/lib/mysql/mysql.sock

[mysqld]
server-id=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7
innodb_strict_mode=OFF

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

I created a mysqld.pid and mysqld.lock file in /var/run/mysqld/ ensuring the owner and group was mysql:mysql

touch /var/run/mysqld/mysqld.pid;
touch /var/run/mysqld/mysqld.lock;
chown -R mysql:mysql /var/run/mysqld/;

I then ran the following two commands which seems to fail but may have helped

service mysql start --wsrep-cluster-address="gcomm://";
systemctl start mysql --wsrep-new-cluster;

Finally I ran 

systemctl start [email protected]
service mysql start

This had previously failed every time I tried. Subsequently I then found that the /var/lib/mysql/mysql.sock had been created which previously did not exist.

Related questions

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
MySQL error code: 1175 during UPDATE in MySQL Workbench

I'm trying to update the column visited to give it the value 1. I use MySQL workbench, and I'm writing the statement in the SQL editor from inside the workbench. I'm writing the following command: UPDATE tablename SET columnname=1; It gives …

Read More