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
4
  • Why is it shutting down? What else is logged? Oct 31, 2014 at 2: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, 2014 at 6: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, 2014 at 7: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, 2014 at 7:18

2 Answers 2

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.

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

1
  • Sorry had not refreshed, see Janne Pikkarainen already answered
    – rezizter
    Oct 31, 2014 at 8:38

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.