MySQL is an open-source relational database management system. Its name is a combination of "My", the name of co-founder Michael Widenius's daughter, and "SQL", the abbreviation for Structured Query Language. [MySQL](https://www.mysql.com)

(WE G)ot MySQL🔗

• Workflow Enablement Group (WEG) is proud to offer MySQL hosted as a Docker container in our new LC Kubernetes/Openshift cluster.
• We currently support version 8.

Security by default🔗

• LC WEG routinely performs static security scans powered by Clair in order to find any container vulnerabilities.
• TLS/SSL connection is enforced and required.

MySQL usage tips🔗

How to connect to MySQL🔗

You can connect to MySQL by specifying parameters in a configuration file in the format

[client]
ssl
port=<port>
database=<databasename>
user=<username>
password=<password>

and then running

mysql --defaults-file=<PATH_TO_my.cnf> -h <XXXXXXX.apps.czapps.llnl.gov>

from the command line.

You can also move parameters from the configuration file to the command line, making the above equivalent to

mysql -P <port> -h <XXXXXXX.apps.czapps.llnl.gov> -u <username> -p<password> --ssl <DBname>

However, for security reasons, we recommend that you put your password in a configuration file and not place it on the command line.

How to back up a database to a .sql file🔗

We will use mysqldump to back up a database to a .sql file. Using a configuration file, you can issue a command of the form

mysqldump --defaults-file=<PATH_TO_my.cnf> -h <XXXXXXX.apps.czapps.llnl.gov> <DBname> > <outputfile.sql>

For example, given an appropriate configuration file, mysqldump.cnf, running

mysqldump --defaults-file=mysqldump.cnf  -h janehost.apps.czapps.llnl.gov backuptest > backuptest_dump.sql

creates the file backuptest_dump.sql.

Note that because the name of the database to be dumped is specified on the command line, the database no longer needs to be listed inside the configuration file:

[client]
ssl
port=<port>
user=<username>
password=<password>

How to back up a table to a .sql file🔗

We can use mysqldump to dump or back up a table similar to how we might back up an entire database. Simply specify the name of the table after the name of the database and before the > and output .sql filename:

mysqldump --defaults-file=<PATH_TO_my.cnf> -h <XXXXXXX.apps.czapps.llnl.gov> <DBname> <table> > <outputfile.sql>

Tweaking the example from the last section, and assuming the database backuptest contains a table called mytable, running

mysqldump --defaults-file=mysqldump.cnf  -h janehost.apps.czapps.llnl.gov backuptest mytable > mytable_dump.sql

creates an output file mytable_dump.sql with data about/from mytable only, not the rest of the database.

How to restore a database🔗

To restore a database from a backup in a .sql file using, you’ll run something like

mysql --defaults-file=<PATH_TO_my.cnf> -h <XXXXXXX.apps.czapps.llnl.gov> < <inputfile.sql>

as in

mysql --defaults-file=mysql.cnf -h janehost.apps.czapps.llnl.gov < backuptest_dump.sql

where the configuration file includes specification of the database:

[client]
ssl
port=<port>
database=<databasename>
user=<username>
password=<password>

Alternatively you can omit the database from the config file and include it on the command line, giving a command like

mysql --defaults-file=mysql.cnf -h janehost.apps.czapps.llnl.gov backuptest < backuptest_dump.sql

Note that the direction of the < in this command — “<DBname> < <inputfile.sql>” — is the opposite of that used when dumping a database.

Be careful about overwriting newer content when you import a backup, especially if you have multiple tables dumped into the .sql file.