/usr/bin/mysql_secure_installation and mysql_install_db do not work and cannot install mysql successfully on VPS

Question

PROBLEM SUMMARY

Cannot successfully run three scripts that are supposed to install

mysql.

DETAILS about MY goal

I am setting up a virtual private server that requires mysql

as one of it's components. Without this part of the installation

I cannot complete the server setup.

DESCRIPTION OF EXPECTED RESULTS

The expected results are to have no errors in executing the

mysql installation scripts.

DESCRIPTION OF ACTUAL RESULTS

The actual results were failed installation scripts and no

successful mysql installation.

ERROR MESSAGES (3)

1 - mysql_install_db //attempted command line execution

ERROR OUTPUT UPON EXECUTION

2020-08-23 15:04:20 [WARNING] mysql_install_db is deprecated.

Please consider switching to mysqld --initialize

2020-08-23 15:04:20 [ERROR] The data directory needs to be specified

2 - mysqld --initialize //attempted command line execution

ERROR OUTPUT UPON EXECUTION

2020-08-23T15:08:05.159534Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2020-08-23T15:08:05.161539Z 0 [ERROR] Can't change data directory owner to mysql

2020-08-23T15:08:05.161731Z 0 [ERROR] Aborting

3 - mysql_secure_installation //attempted command line execution

ERROR OUTPUT UPON EXECUTION

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

The 'validate_password' plugin is installed on the server.

The subsequent steps will run with the existing configuration

of the plugin.

Please set the password for root here.

New password:

Re-enter new password:

Estimated strength of the password: 50

Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) :

Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y

... Failed! Error: File './mysql/user.MYD' not found (Errcode: 2 - No such file or directory)

DESCRIPTION OF WHAT I HAVE TRIED

Internet search for the error messages.

One post I tried was to address this error message from

mysql_secure_installation command

Failed! Error: File './mysql/user.MYD' not found (Errcode: 2 - No such file or directory)

I found a post on Stack Overflow

Trying to update root password on mysql or create a new user is giving me /mysql/user.MYD error

that suggested the following:

Initializing the database.

"Your error means, that the table in which users are stored doesn't exist."

"Have a look in your /etc/my.cnf file. There should be an entry datadir. Make sure this directory is empty. Then do"

mysql_install_db --defaults-file=/etc/my.cnf

This creates the schema mysql and all necessary tables. After that you can do

mysql_secure_installation

What I did; I went in to look for the my.cnf file however it did not

contain the datadir entry. So I was unable to solve the problem this way.

I also tried uninstalling and reinstalling mysql, mysql_server and mysqldb

but this did not solve the prolem of installing these three packages.

I also went to look at the error log.

here is the output of /var/log/mysql/error.log

Skipping generation of SSL certificates as certificate files are present in data directory.

2020-08-23T06:36:08.632239Z 0 [Warning] CA certificate ca.pem is self signed.

2020-08-23T06:36:08.632279Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.

2020-08-23T06:36:08.632376Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306

2020-08-23T06:36:08.632394Z 0 [Note] - '127.0.0.1' resolves to '127.0.0.1';

2020-08-23T06:36:08.632431Z 0 [Note] Server socket created on IP: '127.0.0.1'.

2020-08-23T06:36:08.641055Z 0 [Note] Event Scheduler: Loaded 0 events

2020-08-23T06:36:08.641268Z 0 [Note] /usr/sbin/mysqld: ready for connections.

Version: '5.7.31-0ubuntu0.18.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)

2020-08-23T07:06:36.289591Z 3 [Note] Aborted connection 3 to db: 'unconnected' user: 'root' host: 'localhost' (Got an error reading communication packets)

2020-08-23T07:24:05.775887Z 5 [Note] Aborted connection 5 to db: 'unconnected' user: 'root' host: 'localhost' (Got an error reading communication packets)

2020-08-23T07:25:39.186516Z 7 [Note] Aborted connection 7 to db: 'unconnected' user: 'root' host: 'localhost' (Got an error reading communication packets)

2020-08-23T07:33:24.873983Z 9 [Note] Aborted connection 9 to db: 'unconnected' user: 'root' host: 'localhost' (Got an error reading communication packets)

2020-08-23T07:37:04.250153Z 12 [Note] Aborted connection 12 to db: 'unconnected' user: 'root' host: 'localhost' (Got an error reading communication packets)

2020-08-23T07:39:48.755869Z 14 [Note] Aborted connection 14 to db: 'unconnected' user: 'root' host: 'localhost' (Got an error reading communic

and

Starting shutdown...

2020-08-23T15:15:51.553016Z 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool

2020-08-23T15:15:51.553114Z 0 [ERROR] InnoDB: Cannot open '/var/lib/mysql/ib_buffer_pool.incomplete' for writing: Permission denied

2020-08-23T15:15:52.892146Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2720753

2020-08-23T15:15:52.892240Z 0 [Note] Shutting down plugin 'MEMORY'

2020-08-23T15:15:52.892253Z 0 [Note] Shutting down plugin 'CSV'

2020-08-23T15:15:52.892261Z 0 [Note] Shutting down plugin 'sha256_password'

2020-08-23T15:15:52.892267Z 0 [Note] Shutting down plugin 'mysql_native_password'

2020-08-23T15:15:52.892567Z 0 [Note] Shutting down plugin 'binlog'

2020-08-23T15:15:52.893075Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

Answer 1

SOLVED

By doing the following 7 steps

that wiped clean the mysql files

I was able to succesfully run the

secure_mysql_installation

Here they are for anyone with the same experience.

Uninstall or Completely remove mysql from ubuntu 16-04 sudo apt-get remove --purge mysql* sudo apt-get purge mysql* sudo apt-get autoremove. sudo apt-get autoclean. sudo apt-get remove dbconfig-mysql. sudo apt-get dist-upgrade. sudo apt-get install mysql-server.