0

I have the following example for running;

service mysql status

and would appreciate some feedback on exactly what I am seeing.

I've recently brought this system back up after a failure and things not quite happening as they should, so my limited investigation is leading me to believe there is a database issue.

Firstly, service mysql status which to be honest with you, dont really think matters as there is no my.cnf file, but have included it for completeness.

service mysql status
● mysql.service - LSB: Start and stop the mysql database server daemon
   Loaded: loaded (/etc/init.d/mysql)
   Active: active (running) since Tue 2021-01-12 16:16:36 GMT; 1 day 16h ago
  Process: 7366 ExecStop=/etc/init.d/mysql stop (code=exited, status=1/FAILURE)
  Process: 7500 ExecStart=/etc/init.d/mysql start (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/mysql.service
           ├─2102 /bin/sh /usr/bin/mysqld_safe
           └─2169 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/dlm05u.err --pid-file=/var/lib/mysql/dlm05u.pid

Jan 12 16:16:36 dlm05u mysql[7500]: /etc/init.d/mysql: WARNING: /etc/mysql/my.cnf cannot be read. See README.Debian.gz ... (warning).
Jan 12 16:16:36 dlm05u mysql[7500]: Starting MySQL database server: mysqld already running.
Jan 12 16:16:36 dlm05u systemd[1]: Started LSB: Start and stop the mysql database server daemon.

Next up, service mysql_master status

service mysql_master status
● mysql_master.service - LSB: Start and stop the mysql database server daemon
   Loaded: loaded (/etc/init.d/mysql_master)
   Active: active (exited) since Tue 2021-01-12 16:16:44 GMT; 1 day 16h ago
  Process: 7428 ExecStop=/etc/init.d/mysql_master stop (code=exited, status=1/FAILURE)
  Process: 7568 ExecStart=/etc/init.d/mysql_master start (code=exited, status=0/SUCCESS)

Jan 12 16:16:44 dlm05u mysql_master[7568]: Starting MySQL database server: mysqld already running.
Jan 12 16:16:44 dlm05u mysql_master[7568]: [61B blob data]
Jan 12 16:16:44 dlm05u mysql_master[7568]: error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'
Jan 12 16:16:44 dlm05u systemd[1]: Started LSB: Start and stop the mysql database server daemon.

Finally, service mysql_slave status

service mysql_slave status
● mysql_slave.service - LSB: Start and stop the mysql database server daemon
   Loaded: loaded (/etc/init.d/mysql_slave)
   Active: failed (Result: exit-code) since Tue 2021-01-12 15:53:14 GMT; 1 day 16h ago
  Process: 6379 ExecStart=/etc/init.d/mysql_slave start (code=exited, status=1/FAILURE)

Jan 12 15:53:00 dlm05u mysqld[6842]: 210112 15:53:00 [Note] Plugin 'InnoDB' is disabled.
Jan 12 15:53:00 dlm05u mysqld[6842]: 210112 15:53:00 [Note] Plugin 'FEDERATED' is disabled.
Jan 12 15:53:00 dlm05u mysqld[6842]: 210112 15:53:00 [ERROR] Unknown/unsupported storage engine: InnoDB
Jan 12 15:53:00 dlm05u mysqld[6842]: 210112 15:53:00 [ERROR] Aborting
Jan 12 15:53:00 dlm05u mysqld[6842]:
Jan 12 15:53:00 dlm05u mysqld[6842]: 210112 15:53:00 [Note] /usr/sbin/mysqld: Shutdown complete
Jan 12 15:53:14 dlm05u mysql_slave[6379]: Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!
Jan 12 15:53:14 dlm05u systemd[1]: mysql_slave.service: control process exited, code=exited status=1
Jan 12 15:53:14 dlm05u systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon.
Jan 12 15:53:14 dlm05u systemd[1]: Unit mysql_slave.service entered failed state.

The master instance should be running on 3307 with slave on 3306 I believe.

When connecting using;

mysql -u validuser -p

it only connects to the slave on 3306 which I guess is as expected, but I am unable to connect to 3307 using a number of different variations on;

mysql -h 127.0.0.1 -P 3307 -u validuser -p

which is what led me to investigate and I'm really not sure what the above output is telling me.

Interestingly when connected using mysql -u validuser -p I am not seeing the databases I'm expecting, only mysql, information_schema and performance_schema, certainly not those in the datadir of the my_slave.cnf file.

I don't believe /etc/init.d/mysql should be running as there is now my.cnf file as noted in the first status from above and I've really only included it for completeness. I belive the important two instances are mysql_master and mysql_slave, both of which point to their separate cnf files and the contents of these are as below;

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port            = 3307
socket          = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld_master.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3307
basedir         = /usr
datadir         = /master/data
tmpdir          = /tmp
#language       = /usr/share/mysql/english
language        = /usr/share/mysql/
skip-external-locking
skip-name-resolve
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
#
# * Fine Tuning
#
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover          = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log            = /var/log/mysql/mysql.log
#
# Error logging goes to syslog. This is a Debian improvement :)
#
# Here you can see queries with especially long duration
#log_slow_queries       = /master/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
server-id               = 2
log_bin                 = /master/mysql-bin.log
expire_logs_days        = 30
max_binlog_size         = 100M
lower_case_table_names  = 1
#master-host            = 23.128.19.24
#master-user            = repl
#master-password                = repl
#master-port            = 3307
lower_case_table_names  = 1
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#master-host             = 23.128.19.24
#master-user             = repl
#master-password         = repl
#master-port             = 3307
lower_case_table_names  = 1
relay-log               = /master/relay-log
relay-log-index         = /master/relay-ind
relay-log-info-file     = /master/relay.info
master-info-file        = /master/master.info
slave-skip-errors       = 1063,1051,1396
log-slave-updates
#
# * BerkeleyDB
#
# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.
#skip-bdb
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.
skip-innodb
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

#
# * NDB Cluster
#
# See /usr/share/doc/mysql-server-*/README.Debian for more information.
#
# The following configuration is read by the NDB Data Nodes (ndbd processes)
# not from the NDB Management Nodes (ndb_mgmd processes).
#
# [MYSQL_CLUSTER]
# ndb-connectstring=127.0.0.1


#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

and

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port            = 3306
#socket         = /var/run/mysqld/mysqld.sock
host     = localhost
user     = debian-sys-maint
password = smZyoLE1xeV1faiw
socket          = /slave/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket          = /slave/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /slave/mysqld.pid
socket          = /slave/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /slave/data
tmpdir          = /tmp
#language       = /usr/share/mysql/english
language        = /usr/share/mysql
#skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
#
# * Fine Tuning
#
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack=128
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover          = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log            = /var/log/mysql/mysql.log
#
# Error logging goes to syslog. This is a Debian improvement :)
#
# Here you can see queries with especially long duration
#log_slow_queries       = /slave/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
server-id               = 4
#log_bin                        = /slave/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#master-host            = 23.128.20.24
#master-user            = repl
#master-password                = repl
#master-port            = 3307
lower_case_table_names  = 1
relay-log               = /slave/relay-log
relay-log-index         = /slave/relay-ind
relay-log-info-file     = /slave/relay.info
master-info-file        = /slave/master.info
slave-skip-errors       = 1063,1051,1396
#
# * BerkeleyDB
#
# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.
#skip-bdb
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.
skip-innodb
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



#Don't resolve hostnames. All hostnames are IP's or 'localhost'.
skip-name-resolve




[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

#
# * NDB Cluster
#
# See /usr/share/doc/mysql-server-*/README.Debian for more information.
#
# The following configuration is read by the NDB Data Nodes (ndbd processes)
# not from the NDB Management Nodes (ndb_mgmd processes).
#
# [MYSQL_CLUSTER]
# ndb-connectstring=127.0.0.1


#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

Both /master/data and /slave/data exist and show what looks to me like the data/tables that I'm expecting to see.

This server was working until a recent and unexpected power failure, so I'm expecting configuration to be good but struggling on where to go next.

UPDATE: 15 Jan

running ps aux | grep -i 'sql' shows;

root     10876  0.0  0.0   4336  1576 ?        S    10:20   0:00 /bin/sh /usr/bin/mysqld_safe
mysql    10938  0.0  1.0 525512 43956 ?        Sl   10:20   0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var                            /lib/mysql/dlm05u.err --pid-file=/var/lib/mysql/dlm05u.pid
root     11210  0.0  0.0  12728  2268 pts/0    S+   10:40   0:00 grep -i sql

Any and all thoughts and suggestions on how to continue investigating this would be greatly appreciated.

Thanks in advance...

  • This is a dba question – Strawberry Jan 14 at 12:04
  • Each instance should have log files; is there anything interesting in them? If valid user does not have sufficient privileges (see GRANT), he won't be able to see all the DATABASEs. Does ps show that both instances are running? – Rick James Jan 14 at 17:07
  • @RickJames updated question to include ps output. – plisken Jan 15 at 10:41
  • @plisken - Looks like only one instance running, and it is running with default Port, etc? I would expect to see a second instance with diff pid_file, cnf, port, and maybe other parameters. I'm worried that both you Primary and Replicat as using the same pid_file. – Rick James Jan 15 at 17:04
  • Check log files on /var/log/mysqld*** – Jesus Uzcanga Jan 17 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.