The easiest way to connect to and query BIAD is to use one of many third-party software programs to provide an easy Graphical User Interface (GUI). There are minor differences in functionality between these programs, and of course you are welcome to install and try several, all using exactly the same connection credentials.
For Windows users we suggest the free database GUI HeidiSQL.
For Mac users Sequel Ace is a nice tidy free GUI.
Alternatively, DBeaver is available for free for both Windows and Mac.
For available database software, compare the Archlinux Wiki or Wikipedia: Comparison of database tools. The free and open-source Java program DBeaver (Community Edition) works fine for BIAD. DBeaver is packaged only for some Linux distributions, but on the website’s download section you will find a Debian installer (for Ubuntu and derivatives) and an RPM installer (for Red Hat Linux, Fedora, openSUSE), which you can download and execute.
You need to create a (once only) connection to BIAD in your preferred GUI. There are minor differences between programs, but essentially you ened to open the connection manager, add your credentials (provided by the database manager) and remember to save them. Most of the credential fields are obvious, the only slightly tricky bit is choosing the connection type. This must be a direct (TCP/IP) MySQL connection, so avoid any choices that mention SSH. For example:
| Settings | |
|---|---|
| Network type: | MariaDB or MySQL (TCP/IP) |
| Hostname / IP: | biad.xxxx |
| User: | xxxxxxx |
| Password: | xxxxxxx |
| Port: | 1234 |
| Databases: | BIAD |
Some connection managers also require a choice of Library, which doesnt really matter. Choose libmysql.dll if available. Otherwise libmariadb.dll also works.
The command-line client mycli is a convenient way to interact with the database in a terminal. You can run SQL statements interactively, but you can also conveniently run batch queries. For example, this command will download the sites table into the local file sites.csv, which you can open with any spreadsheet software. (It assumes an active SSH tunnel as described above.)
mycli -u <user> -p <password> -D biad --csv -e 'SELECT * FROM Sites' > ~/sites.csv
DBeaver may throw this error, as literally in the settings this is not allowed.
Edt your database connection -> Driver Properties tab, change the default 'allowPublicKeyRetrieval from 'false' to 'true'.
Some universities and institutions provide laptops with restricted user permissions that effectively prevents users from installing software of their choice, and even restricting access to their own C: Program Files. Either ask your IT team to install it, or better still, get admin rights on your own machine, and transform your laptop from an expensive paperweight into a useful tool!
Sometimes a third-party GUI program is blocked by your firewall, even though no explicit mention of the firewall is returned in the error.
In that case there are several steps, which can help fix the situation.
In the same way that we use environmental variables to connect via R , you can also use them directly in the terminal too.
You will need to defined them in your .bashrc file, instead of in .Renviron, but then they will be available from bash, or python, etc... Then the command will become:
ssh -f -N -L $BIAD_DB_PORt:$BIAD_DB_HOST:3306 $BIAD_SSH_USER@$BIAD_SSH_HOST -i $BIAD_SSH_PEM
Connecting to mysql using terminal client will thus be:
mysql -u $BIAD_DB_USER -h $BIAD_DB_HOST -P $BIAD_DB_PORT -p