Mysql will no longer start after DB import and password change

Question

I'm running Mysql5.6 on RHEL 7. I had managed to get the mysql service up and running, and imported a database into it with no issues.

However, after attempting to reset a user's password, the database crashed. I tried to bring the service back up, but the service would only ever timeout.

This morning I once again tried to restart the service, and instead of timing out, received this error:

Job for rh-mysql56-mysqld.service failed because the control process exited with error code. See "systemctl status rh-mysql56-mysqld.service" and "journalctl -xe" for details.

I ran systemctl status rh-mysql56-mysqld.service and got:

rh-mysql56-mysqld.service - MySQL 5.6 database server

Loaded: loaded (/usr/lib/systemd/system/rh-mysql56-mysqld.service; enabled; vendor preset: disabled)

Active: failed (Result: exit-code) since Thu 2016-06-30 15:59:54 EDT; 14s ago

Process: 14677 ExecStopPost=/usr/bin/scl enable $RH_MYSQL56_SCLS_ENABLED -- /opt/rh/rh-mysql56/root/usr/libexec/mysql-wait-stop (code=exited, status=0/SUCCESS)

Process: 14477 ExecStartPost=/usr/bin/scl enable $RH_MYSQL56_SCLS_ENABLED -- /opt/rh/rh-mysql56/root/usr/libexec/mysql-wait-ready $MAINPID (code=exited, status=1/FAILURE)

Process: 14476 ExecStart=/opt/rh/rh-mysql56/root/usr/libexec/mysqld_safe-scl-helper enable $RH_MYSQL56_SCLS_ENABLED -- /opt/rh/rh-mysql56/root/usr/bin/mysqld_safe --basedir=/opt/rh/rh-mysql56/root/usr (code=exited, status=0/SUCCESS)

Process: 14437 ExecStartPre=/usr/bin/scl enable $RH_MYSQL56_SCLS_ENABLED -- /opt/rh/rh-mysql56/root/usr/libexec/mysql-prepare-db-dir %n (code=exited, status=0/SUCCESS)

Process: 14409 ExecStartPre=/usr/bin/scl enable $RH_MYSQL56_SCLS_ENABLED -- /opt/rh/rh-mysql56/root/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)

Process: 14402 ExecStartPre=/usr/bin/scl enable $RH_MYSQL56_SCLS_ENABLED -- /usr/bin/scl_enabled rh-mysql56 (code=exited, status=0/SUCCESS)

Main PID: 14476 (code=exited, status=0/SUCCESS)

Jun 30 15:59:53 sia-cmsweb-new.si.edu systemd[1]: Starting MySQL 5.6 database server...

Jun 30 15:59:53 sia-cmsweb-new.si.edu mysqld_safe-scl-helper[14476]: 160630 15:59:53 mysqld_safe Logging to '/var/opt/rh/rh-mysql56/log/mysql/mysqld.log'.

Jun 30 15:59:53 sia-cmsweb-new.si.edu mysqld_safe-scl-helper[14476]: 160630 15:59:53 mysqld_safe Starting mysqld daemon with databases from /var/opt/rh/rh-mysql56/lib/mysql

Jun 30 15:59:54 sia-cmsweb-new.si.edu mysqld_safe-scl-helper[14476]: 160630 15:59:54 mysqld_safe mysqld from pid file /var/run/rh-mysql56-mysqld/mysqld.pid ended

Jun 30 15:59:54 sia-cmsweb-new.si.edu systemd[1]: rh-mysql56-mysqld.service: control process exited, code=exited status=1

Jun 30 15:59:54 sia-cmsweb-new.si.edu systemd[1]: Failed to start MySQL 5.6 database server.

Jun 30 15:59:54 sia-cmsweb-new.si.edu systemd[1]: Unit rh-mysql56-mysqld.service entered failed state.

Jun 30 15:59:54 sia-cmsweb-new.si.edu systemd[1]: rh-mysql56-mysqld.service failed.

I also checked the error logs after attempting to start mysql. There are a few errors, which I suspect have something to do with the issue:

160630 14:14:48 mysqld_safe Starting mysqld daemon with databases from /var/opt/rh/rh-mysql56/lib/mysql

2016-06-30 14:14:48 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2016-06-30 14:14:48 0 [Note] /opt/rh/rh-mysql56/root/usr/libexec/mysqld (mysqld 5.6.30) starting as process 9138 ...

2016-06-30 14:14:48 9138 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2016-06-30 14:14:48 9138 [Warning] Buffered warning: Changed limits: table_open_cache: 431 (requested 2000)

2016-06-30 14:14:48 9138 [Note] Plugin 'FEDERATED' is disabled.

/opt/rh/rh-mysql56/root/usr/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13 - Permission denied)

2016-06-30 14:14:48 9138 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

2016-06-30 14:14:48 9138 [Note] InnoDB: Using atomics to ref count buffer pool pages

2016-06-30 14:14:48 9138 [Note] InnoDB: The InnoDB memory heap is disabled

2016-06-30 14:14:48 9138 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2016-06-30 14:14:48 9138 [Note] InnoDB: Memory barrier is not used

2016-06-30 14:14:48 9138 [Note] InnoDB: Compressed tables use zlib 1.2.7

2016-06-30 14:14:48 9138 [Note] InnoDB: Using Linux native AIO

2016-06-30 14:14:48 9138 [Note] InnoDB: Using CPU crc32 instructions

2016-06-30 14:14:48 9138 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2016-06-30 14:14:48 9138 [Note] InnoDB: Completed initialization of buffer pool

2016-06-30 14:14:48 9138 [Note] InnoDB: Highest supported file format is Barracuda.

2016-06-30 14:14:48 7f9e223b6840 InnoDB: Operating system error number 13 in a file operation.

InnoDB: The error means mysqld does not have the access rights to

InnoDB: the directory.

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Could not find a valid tablespace file for 'mysql/innodb_index_stats'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Tablespace open failed for '"mysql"."innodb_index_stats"', ignored.

2016-06-30 14:14:48 7f9e223b6840 InnoDB: Operating system error number 13 in a file operation.

InnoDB: The error means mysqld does not have the access rights to

InnoDB: the directory.

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Could not find a valid tablespace file for 'mysql/innodb_table_stats'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Tablespace open failed for '"mysql"."innodb_table_stats"', ignored.

2016-06-30 14:14:48 7f9e223b6840 InnoDB: Operating system error number 13 in a file operation.

InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory.

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Could not find a valid tablespace file for 'mysql/slave_master_info'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Tablespace open failed for '"mysql"."slave_master_info"', ignored.

2016-06-30 14:14:48 7f9e223b6840 InnoDB: Operating system error number 13 in a file operation.

InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory.

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Could not find a valid tablespace file for 'mysql/slave_relay_log_info'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Tablespace open failed for '"mysql"."slave_relay_log_info"', ignored.

2016-06-30 14:14:48 7f9e223b6840 InnoDB: Operating system error number 13 in a file operation.

InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory.

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Could not find a valid tablespace file for 'mysql/slave_worker_info'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Tablespace open failed for '"mysql"."slave_worker_info"', ignored.

2016-06-30 14:14:48 9138 [Note] InnoDB: 128 rollback segment(s) are active.

2016-06-30 14:14:48 9138 [Note] InnoDB: Waiting for purge to start

2016-06-30 14:14:48 9138 [Note] InnoDB: 5.6.30 started; log sequence number 1625987

2016-06-30 14:14:48 9138 [Note] RSA private key file not found: /var/opt/rh/rh-mysql56/lib/mysql//private_key.pem. Some authentication plugins will not work.

2016-06-30 14:14:48 9138 [Note] RSA public key file not found: /var/opt/rh/rh-mysql56/lib/mysql//public_key.pem. Some authentication plugins will not work.

2016-06-30 14:14:48 9138 [Note] Server hostname (bind-address): '*'; port: 3306

2016-06-30 14:14:48 9138 [Note] IPv6 is available.

2016-06-30 14:14:48 9138 [Note] - '::' resolves to '::';

2016-06-30 14:14:48 9138 [Note] Server socket created on IP: '::'.

2016-06-30 14:14:48 9138 [ERROR] /opt/rh/rh-mysql56/root/usr/libexec/mysqld: Can't find file: './mysql/user.frm' (errno: 13 - Permission denied)

2016-06-30 14:14:48 9138 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/user.frm' (errno: 13 - Permission denied)

160630 14:14:48 mysqld_safe mysqld from pid file /var/run/rh-mysql56-mysqld/mysqld.pid ended

I have tried setting the ownership of the rh-mysql56 to mysql using chown myql:mysql ./path/to/rh-mysql56, but that didn't help (also tried switching it to root, also didn't help).

I've tried running mysql_upgrade, which I have seen working for others, but I get the error:

Looking for 'mysql' as: ./mysql Looking for 'mysqlcheck' as: ./mysqlcheck Error: Failed while fetching Server version! Could be due to unauthorized access. FATAL ERROR: Upgrade failed

I've also tried doing a yum reinstall, which didn't help issues at all. At this point I'm kinda stuck, and wondering if there is something else I should try, or if there's something in the error messages I'm missing.

I should also say that mysql is installed to a abnormal location (it's at /opt/rh/rh-mysql56).

Answer 1

I keep seeing permission denied errors in the log.

You didn't run the chown command recursively

Please do this

chown -R mysql:mysql ./path/to/rh-mysql56

I also see errors like

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Tablespace open failed for '"mysql"."innodb_index_stats"', ignored.

When you installed mysql before, it created 5 InnoDB system tables (See my answers Cannot open table mysql/innodb_index_stats and InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6).

You need to recreate them.

First you need to delete the orphaned .frm files

cd ./path/to/rh-mysql56/mysql
rm -f innodb_index_stats.frm
rm -f innodb_table_stats.frm
rm -f slave_master_info.frm
rm -f slave_relay_log_info.frm
rm -f slave_worker_info.frm
rm -f innodb_index_stats.ibd
rm -f innodb_table_stats.ibd
rm -f slave_master_info.ibd
rm -f slave_relay_log_info.ibd
rm -f slave_worker_info.ibd

Then, you can recreate them with

USE mysql
CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
CREATE TABLE `slave_master_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
  `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
  `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
  `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
  `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
  `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
  `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
  `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
  `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
  `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
  `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
  `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
  `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
  `Heartbeat` float NOT NULL,
  `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
  `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
  `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
  `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
  `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
  `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
  `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
  PRIMARY KEY (`Host`,`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';
CREATE TABLE `slave_relay_log_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
  `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
  `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
  `Number_of_workers` int(10) unsigned NOT NULL,
  `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';
CREATE TABLE `slave_worker_info` (
  `Id` int(10) unsigned NOT NULL,
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Relay_log_pos` bigint(20) unsigned NOT NULL,
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_seqno` int(10) unsigned NOT NULL,
  `Checkpoint_group_size` int(10) unsigned NOT NULL,
  `Checkpoint_group_bitmap` blob NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';