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?:YesDisallow root login remotely?:NoRemove test database and access to it?:YesReload 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
================================================================ Mysql Standalone DB Creation Completed Successfully! ================================================================ The database files are present in /home/$HOME/.local/var/mysqldb To start the mysqld daemon, run /usr/local/bin/shdb again. This will submit a 24hr job on an appropriate compute node. You can connect to the database with `mysql -p`, MySQL workbench or your own application. The connection information can be found at /home/$HOME/.my.cnf This file will be updated on each MySQL run with the actual hostname and port the instance started on. ================================================================
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.
$ shdb Self-hosted database: submitting job 'mysqld' to scheduler... Submitted batch job 16638506 Message from wdizon@login02.sol.rc.asu.edu on <no tty> at 09:45 ... ================================================================ Your mysqld job has started on a compute node and is ready to access. ================================================================ Here are the details on the mysql job submitted: 16638506 93711002 lightwork/public mysqld RUNNING 0:03 1-00:00:00 1/1/NA c002 You can find the updated connection details in ~/.my.cnf EOF
Connect to the MySQL daemon
[wdizon@login02:~]$ mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.19 Source distribution Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> quit Bye
Congrats! You’re connected!