Skip to content

MariaDB

MariaDB

MariaDB is a community-developed, commercially supported fork of the MySQL relational database management system, intended to remain free and open-source software under the GNU General Public License. MariaDB External Info

(WE G)ot MariaDB

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

Security by default

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

MariaDB usage tips

How to connect to MariaDB

You can connect to MariaDB 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=mariadb.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=mariadb.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.