2

When I execute systemctl start mysql it starts MySQL but I don't get command prompt back, it just stays like that. Database starts normally. After couple of minutes database automatically shutdowns and systemctl reports failure.

I have also tried systemctl start mysql --no-block it brings command prompt back immediately but after couple of minutes database is again shutdown.

Database does not crash, it is a clean shutdown so I suspect systemctl is issuing shutdown signal after some time.

2014-10-30 20:03:37 7401 [Note] /usr/sbin/mysqld: Normal shutdown

Installed RPMS

Percona-XtraDB-Cluster-galera-3-debuginfo-3.7-1.3254.rhel7.x86_64
Percona-XtraDB-Cluster-56-debuginfo-5.6.20-25.7.888.el7.x86_64
Percona-XtraDB-Cluster-garbd-3-3.7-1.3254.rhel7.x86_64
Percona-XtraDB-Cluster-test-56-5.6.20-25.7.888.el7.x86_64
percona-release-0.1-3.noarch
Percona-XtraDB-Cluster-galera-3-3.7-1.3254.rhel7.x86_64
Percona-XtraDB-Cluster-client-56-5.6.20-25.7.888.el7.x86_64
percona-xtrabackup-2.2.5-5027.el7.x86_64
Percona-XtraDB-Cluster-server-56-5.6.20-25.7.888.el7.x86_64
Percona-XtraDB-Cluster-full-56-5.6.20-25.7.888.el7.x86_64
Percona-XtraDB-Cluster-shared-56-5.6.20-25.7.888.el7.x86_64

OS

CentOS Linux release 7.0.1406 (Core)

How do I make sure MySQl does not shutdown?

UPDATE 1

[root@n5 ~]# systemctl start mysql  (Time 23:31)


Job for mysql.service failed. See 'systemctl status mysql.service' and 'journalctl -xn' for details. (Time 23:46)
[root@n5 ~]# 
[root@n5 ~]# 
[root@n5 ~]# systemctl status mysql.service
mysql.service - Percona XtraDB Cluster
   Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled)
   Active: failed (Result: exit-code) since Thu 2014-10-30 23:46:47 MDT; 24min ago
  Process: 22821 ExecStopPost=/usr/bin/mysql-systemd stop-post (code=exited, status=0/SUCCESS)
  Process: 22778 ExecStop=/usr/bin/mysql-systemd stop (code=exited, status=2)
  Process: 18202 ExecStartPost=/usr/bin/mysql-systemd start-post $MAINPID (code=exited, status=1/FAILURE)
  Process: 18201 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS)
  Process: 18168 ExecStartPre=/usr/bin/mysql-systemd start-pre (code=exited, status=0/SUCCESS)
 Main PID: 18201 (code=exited, status=0/SUCCESS)

Oct 30 23:31:27 n5 rsyncd[19257]: sent 39 bytes  received 181 bytes  total size 48
Oct 30 23:46:29 n5 mysql-systemd[18202]: ERROR!
Oct 30 23:46:29 n5 systemd[1]: mysql.service: control process exited, code=exited status=1
Oct 30 23:46:29 n5 mysql-systemd[22778]: ERROR! mysql pid file /var/lib/mysql /var/lib/mysql/mysqld.pid empty or not readable
Oct 30 23:46:29 n5 mysql-systemd[22778]: WARNING: mysql may be already dead
Oct 30 23:46:29 n5 systemd[1]: mysql.service: control process exited, code=exited status=2
Oct 30 23:46:47 n5 mysqld_safe[18201]: 141030 23:46:47 mysqld_safe mysqld from pid file /var/lib/mysql/mysqld.pid ended
Oct 30 23:46:47 n5 mysql-systemd[22821]: SUCCESS!
Oct 30 23:46:47 n5 systemd[1]: Failed to start Percona XtraDB Cluster.
Oct 30 23:46:47 n5 systemd[1]: Unit mysql.service entered failed state.

From MySQL Log

2014-10-30 23:31:49 19205 [Note] /usr/sbin/mysqld: ready for connections.
2014-10-30 23:46:29 19205 [Note] /usr/sbin/mysqld: Normal shutdown

From message log

Oct 30 23:31:25 n5 mysql-systemd: /usr/bin/mysql-systemd: line 31: [: /var/lib: binary operator expected
Oct 30 23:31:25 n5 mysql-systemd: /usr/bin/mysql-systemd: line 39: [: /var/lib: binary operator expected
Oct 30 23:31:25 n5 mysql-systemd: /usr/bin/mysql-systemd: line 45: [: /var/log/mysqld.log: binary operator expected
Oct 30 23:31:26 n5 mysqld_safe: 141030 23:31:26 mysqld_safe Logging to '/var/log/mysqld.log'.
Oct 30 23:31:26 n5 mysqld_safe: 141030 23:31:26 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Oct 30 23:31:26 n5 mysqld_safe: 141030 23:31:26 mysqld_safe Skipping wsrep-recover for 6da46c5b-9acc-11e3-9d28-9f9960e4e0f4:2978271210 pair
Oct 30 23:31:26 n5 mysqld_safe: 141030 23:31:26 mysqld_safe Assigning 6da46c5b-9acc-11e3-9d28-9f9960e4e0f4:2978271210 to wsrep_start_position
Oct 30 23:46:29 n5 mysql-systemd: ERROR!
Oct 30 23:46:29 n5 systemd: mysql.service: control process exited, code=exited status=1
Oct 30 23:46:29 n5 mysql-systemd: ERROR! mysql pid file /var/lib/mysql /var/lib/mysql/mysqld.pid empty or not readable
Oct 30 23:46:29 n5 mysql-systemd: WARNING: mysql may be already dead
Oct 30 23:46:29 n5 systemd: mysql.service: control process exited, code=exited status=2
Oct 30 23:46:47 n5 mysqld_safe: 141030 23:46:47 mysqld_safe mysqld from pid file /var/lib/mysql/mysqld.pid ended
Oct 30 23:46:47 n5 mysql-systemd: SUCCESS!
Oct 30 23:46:47 n5 systemd: Unit mysql.service entered failed state.

UPDATE 2 Server mem info - Server has 256GB RAM and 4GB swap

top - 01:15:27 up 14:24,  2 users,  load average: 0.00, 0.01, 0.05
Tasks: 314 total,   1 running, 313 sleeping,   0 stopped,   0 zombie
%Cpu(s):  0.0 us,  0.1 sy,  0.0 ni, 99.9 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem:  26394486+total, 24065484 used, 23987937+free,      688 buffers
KiB Swap:  4194300 total,        0 used,  4194300 free. 21135896 cached Mem

my.cnf

[MYSQLD]
user=mysql
datadir=/var/lib/mysql
log_error=/var/log/mysqld.log
log_warnings=2
#log_output=FILE
bind_address=192.168.1.15

### INNODB OPTIONS 
innodb_buffer_pool_size=180G
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1
innodb_data_file_path = ibdata1:100M:autoextend
## You may want to tune the below depending on number of cores and disk sub
innodb_read_io_threads=4
innodb_write_io_threads=4
innodb_io_capacity=400
innodb_doublewrite=1
innodb_log_file_size=1024M
innodb_log_buffer_size=96M
innodb_buffer_pool_instances=8
innodb_log_files_in_group=2
innodb_thread_concurrency=0
#innodb_file_format=barracuda
innodb_flush_method = O_DIRECT
innodb_autoinc_lock_mode=2
## avoid statistics update when doing e.g show tables
innodb_stats_on_metadata=0
default_storage_engine=innodb
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_dump_at_shutdown=1

#Time
wait_timeout = 300
connect_timeout=60
interactive_timeout=300

# CHARACTER SET
collation_server = utf8_unicode_ci
init_connect='SET NAMES utf8'
character_set_server = utf8


# REPLICATION SPECIFIC
binlog_format=ROW

# OTHER THINGS, BUFFERS ETC
key_buffer_size = 24M
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 512M
#sort_buffer_size = 256K
#read_buffer_size = 256K
#read_rnd_buffer_size = 512K
#myisam_sort_buffer_size = 8M
skip_name_resolve
max_connect_errors = 100000000
sql_mode = ''
sysdate_is_now=1
max_connections=200
thread_cache_size=512
query_cache_type = 0
query_cache_size = 0
table_open_cache=1024
lower_case_table_names=0
# 5.6 backwards compatibility
explicit_defaults_for_timestamp=1
##
## WSREP options
##
# Full path to wsrep provider library or 'none'
wsrep_provider=/usr/lib64/libgalera_smm.so

wsrep_node_address=192.168.1.15
# Provider specific configuration options
wsrep_provider_options="gcache.size=32768M;gcs.fc_limit = 256; gcs.fc_factor = 0.99"

# Logical cluster name. Should be the same for all nodes.
wsrep_cluster_name="mh1"

# Group communication system handle
wsrep_cluster_address="gcomm://192.168.1.11,192.168.1.13,192.168.1.15"

# Human_readable node name (non-unique). Hostname by default.
#wsrep_node_name=

# Address for incoming client connections. Autodetect by default.
#wsrep_node_incoming_address=

# How many threads will process writesets from other nodes
wsrep_slave_threads=48

# DBUG options for wsrep provider
#wsrep_dbug_option

# Generate fake primary keys for non-PK tables (required for multi-master
# and parallel applying operation)
wsrep_certify_nonPK=1

# Location of the directory with data files. Needed for non-mysqldump
# state snapshot transfers. Defaults to mysql_real_data_home.
#wsrep_data_home_dir=

# Maximum number of rows in write set
wsrep_max_ws_rows=131072

# Maximum size of write set
wsrep_max_ws_size=1073741824

# to enable debug level logging, set this to 1
wsrep_debug=1

# convert locking sessions into transactions
wsrep_convert_LOCK_to_trx=0

# how many times to retry deadlocked autocommits
wsrep_retry_autocommit=1

# change auto_increment_increment and auto_increment_offset automatically
wsrep_auto_increment_control=1

# replicate myisam
wsrep_replicate_myisam=1
# retry autoinc insert, which failed for duplicate key error
wsrep_drupal_282555_workaround=0

# enable "strictly synchronous" semantics for read operations
wsrep_causal_reads=0

# Command to call when node status or cluster membership changes.
# Will be passed all or some of the following options:
# --status  - new status of this node
# --uuid    - UUID of the cluster
# --primary - whether the component is primary or not ("yes"/"no")
# --members - comma-separated list of members
# --index   - index of this node in the list
#wsrep_notify_cmd=

##
## WSREP State Transfer options
##

# State Snapshot Transfer method
wsrep_sst_method=rsync

# Address on THIS node to receive SST at. DON'T SET IT TO DONOR ADDRESS!!!
# (SST method dependent. Defaults to the first IP of the first interface)
#wsrep_sst_receive_address=

# SST authentication string. This will be used to send SST to joining nodes.
# Depends on SST method. For mysqldump method it is root:<root password>
wsrep_sst_auth="xxxx:xxxx"

# Desired SST donor name.
#wsrep_sst_donor=

# Protocol version to use
# wsrep_protocol_version=

[MYSQL]
socket=/var/lib/mysql/mysql.sock
default_character_set=utf8

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

[mysqldump]
max_allowed_packet = 512M
socket=/var/lib/mysql/mysql.sock
default_character_set=utf8

[MYSQLD_SAFE]
pid_file=mysqld.pid
log_error=/var/log/mysqld.log
datadir=/var/lib/mysql
Jai
  • 141
  • 3
  • 14
  • Why is it shutting down? What _else_ is logged? – Michael Hampton Oct 31 '14 at 02:23
  • @MichaelHampton Updating question with more info. If you notice MySQL starts at about 23:31 and automatically shuts down at about 23:46. – Jai Oct 31 '14 at 06:37
  • How much swap space do you have? can you post your my.cnf file in your question - had this issue on a centos 6 server using perconda with no swap allocated – rezizter Oct 31 '14 at 07:09
  • @rezizter Updated question. This same server config runs fine on centos 6 and I never encountered this kind of problem. It seems like this is related with systemctl, I am not sure, centos 7 is new for me. – Jai Oct 31 '14 at 07:18

2 Answers2

3

What if you change this part in my.cnf

[MYSQLD_SAFE]
pid_file=mysqld.pid

to be

[MYSQLD_SAFE]
pid_file=/var/lib/mysql/mysqld.pid

I guess systemd did kill MySQL because it did not find a proper pid file containing a process number in that location.

Janne Pikkarainen
  • 31,454
  • 4
  • 56
  • 78
1

In your my.cnf file change the following line

[MYSQLD_SAFE]
pid_file=mysqld.pid

to your actual path

on mine it is

pid_file=/var/lib/mysql/mysqld.pid

please comment if it works for you

rezizter
  • 228
  • 3
  • 9