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) no longer supports MySQL.
- Users are encouraged to view our documentation for MariaDB instead
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.