Running a MySQL server on Sol

Owned by William Dizon
Last updated: Jul 01, 2024
4 min read

Running a MySQL server generally requires the purchase and setup of a privately-owned VM to ensure consistent networking accessibility. The process outlined in this page will help set up a long-running process contained within a job allocation so that other jobs can access it from Sol compute nodes itself.

Initializing an Empty Database

From a login node, run the following command: shdb

Change the randomly-generated password

Take note of the third line of output which is the autogenerated MySQL password signified by:
“A temporary password is generated for root@localhost:"

$ shdb 2024-07-01T16:40:07.756793Z 0 [System] [MY-013169] [Server] /packages/apps/spack/18/opt/spack/aocc-3.1.0/mysql-8.0.19-3n4/bin/mysqld (mysqld 8.0.19) initializing of server in progress as process 1687891 2024-07-01T16:40:07.757203Z 0 [ERROR] [MY-010338] [Server] Can't find error-message file '/home/wdizon/.local/var/mysqldb/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive. 2024-07-01T16:40:09.906332Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 5zq_P%sJKzao Securing the MySQL server deployment. Enter password for user root:

Use the password provided on this line as the initial mysql root password.

It will immediately inform you that the randomly-generated password needs to be changed. It will prompt you for the following:

  • Would you like to setup VALIDATE PASSWORD component?: No.

  • Change the password for root ?: No

The existing password for the user account root has expired. Please set a new password. New password: <enter in the password you will remember> Re-enter new password: <enter in the password you will remember> VALIDATE PASSWORD COMPONENT can be used to test passwords and improve security. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD component? Press y|Y for Yes, any other key for No: no Using existing password for root. Change the password for root ? ((Press y|Y for Yes, any other key for No) : No

Default MySQL settings

  • Remove anonymous users? : Yes

  • Disallow root login remotely? : No

  • Remove test database and access to it?: Yes

  • Reload privilege tables now?: Yes

By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? (Press y|Y for Yes, any other key for No) : yes Success. Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? (Press y|Y for Yes, any other key for No) : no ... skipping. By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? (Press y|Y for Yes, any other key for No) : yes - Dropping test database... Success. - Removing privileges on test database... Success. Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? (Press y|Y for Yes, any other key for No) : yes Success. All done!

Confirmation of Successful Creation

The MySQL server files are created, but no background service daemon is running to connect to. All this so far has been able to happen on a login node. Going forward, we want to ensure that the mysql process is run on a compute node, with appropriate compute resources and a workable duration.

Starting the MySQL daemon

After you run shdb again, the script will identify the created mysql files and attempt to start the service with 1 (one) core and a 24 hour walltime in the lightwork partition, which is appropriate for mysql workloads.

The job starting will be announced in your active terminal and signifies the service is operational.

Connect to the MySQL daemon

Congrats! You’re connected!

Arizona State University