Mastodon

Database Management

dBeaver

dBeaver

dBeaver and CloudBeaver: Two Excellent Open Source Database Management Tools

My previous video ona a database amnagement tool was on MySQL Workbench, and got a pretty good response.  I did, however, have quite a few poeple ask about using it with Postgres SQL, which can be done, but is not super easy, and really not what the tool is for.

If you're interested in the MySQL Workbench software, check out my install and basic usage video here: https://www.youtube.com/watch?v=2pA6pl3q91c

In my search through the internet for all things great and open source, I came upon a suggestion for dBeaver https://dbeaver.io/, and after checking it out, found it to be an excellent open source database management tool.  Additionally, the dBeaver folks had made a new product called CloudBeaver which is a self-hosted, online / web browser based database management tool, so this week you get a two-fer (two for one).

Installing dBeaver

dBeaver is a desktop application that will run on Linux (my preferred platform), MacOS (my second choice personally), or Windows.   It has multiple installation options, but the two most promising for most Linux users is through the snap package, or via a flatpak installation depending on your specific OS distribution and preference.

You can find the dBeaver installation / download options on their page at https://dbeaver.io/download/

For those using snap (like me), the installation is quite simple:

sudo snap install dbeaver-ce

This will download and install dBeaver Community Edition on your Linux desktop / laptop / tablet / device, and you'll be up and running in minutes.

For those using / prefering flatpak, you can use the following command:

flatpak install flathub io.dbeaver.DBeaverCommunity

NOTE: I have not installed the flatpak, but this command is directly from the dBeaver install page as of Community Edition 7.2.4.

Once installed, start it up, and start connecting to your databases using the many, many drivers they have available.

If, however, you are a dev shop, IT shop, or are just looking for a tool with support and a few more drivers / features such as NoSQL support (e.g. MongoDB, etc), then you might want to check into the dBeaver Enterprise Edition https://dbeaver.com/ .

They have different licensing options, including a free Academic License for students and teachers, that could very well be worth your time and money.

Installing CloudBeaver

Next up, we have CloudBeaver.  This is a very cool offering from the dBeaver folks that provides a Web User Interface that is open source and self hostable.  The interface front-end is built on modern technology, and has a clean look and feel, and is laid out in a very similar fashion to dBeaver.

Installing CloudBeaver is quite simple, especially using Docker, and a simple script to help setup the container networking to allow you to connect to a locally hosted database (on the same machine you are running the CloudBeaver container).

Installation of Docker-CE and Docker-Compose via a Simple Script

You can easily install Docker-CE, Docker-Compose, Portainer-CE, and NGinX Proxy manager by using this quick install script I created and maintain on Github.  Just use the command:

wget https://gitlab.com/bmcgonag/docker_installs/-/raw/main/install_docker_nproxyman.sh

To download the script to your desired host.

Change the permissions to make the script executable:

chmod +x ./install_docker_nproxyman.sh

and then run the script with the command:

./install_docker_nproxyman.sh

When run, the script will prompt you to select your host operating system, then will ask you which bits of software you want to install.

Simply enter 'y' for each thing you want to install.

At some point, you may be asked for your super user (sudo) password as well.

Allow the script to complete installation.

At this point, you might want to log out and back in, as this will allow you to use the docker and docker-compose commands without the need of sudo in front of them.

Now We Install CloudBeaver

Next, we need to create a folder in our server.  I just made one called "cloudbeaver".

mkdir cloudbeaver

Move into that folder, and create a new file called "install-cloudbeaver.sh"

cd cloudbeaver

nano install-cloudbeaver

Paste the following into that file.  You can copy with CTRL+C (or CMD+C for MacOS), then paste in the terminal with Shift+CTRL+V (or CMD+V for MacOS).

# Detect host machine IP Address (we need this when run in docker container)
export CB_LOCAL_HOST_ADDR=$(ifconfig | grep -E "([0-9]{1,3}\.){3}[0-9]{1,3}" | grep -v 127.0.0.1 | awk '{ print $2 }' | cut -f2 -d: | head -n1)

docker run -d \
    --name cloudbeaver \
    --restart unless-stopped \
    -p 8978:8978 \
    --add-host=host.docker.internal:${CB_LOCAL_HOST_ADDR} \
    -v /var/cloudbeaver/workspace:/opt/cloudbeaver/workspace \
    dbeaver/cloudbeaver:latest

Sve the file with CTRL+O, then press Enter / Return, and exit nano with CTRL+X.

NOTE: You'll need ifconfig installed if you don't have it already.  

You can check this by typing the command

ifconfig

into your terminal.  If you get an error, you can install it on Ubuntu by doing

sudo apt install net-tools -y

You may want to change the host portion of the port in the script if you want / need a differennt port.  Remember, the host portion is on the left of the colon : .  So in the 8978:8978 part, you would change the left side.  I made min 8121, so it now looks like this:

-p 8121:8978

After you have made adjustments, saved, and exited nano, you can run the script to get CloudBeaver installed.

. ./install-cloudbeaver.sh

Just relax while it installs.  When it's done, give it about a minute, just to be sure the app is up and running, then log into your server at the server IP address and the port you set.  My server IP is 192.168.7.125 and the port I set was 8121, so I navigated in my browser to:

http://192.168.7.125:8121

Next, you'll start the process to create your admin account, and setup your first database.  Check out the video at the top for the navigation and setup of CloudBeaver.

Support my Channel and Content

Support my Channel and ongoing efforts through Patreon:
https://patreon.com/awesomeopensource

MySQL Workbench

MySQL Workbench

MySQL Workbench - A Powerful MySQL / MariaDB IDE

For years I've worked with MySQL / MariaDB.  It's an incredible open source database offering that allows those of us with no extra money to spend on ridiculously difficult and silly licenses for such monstrous things as Microsoft SQL Server.   My use has been very much on the hobbyist level, running my own Wordpress sites, or other small installations of web applications.   Don't misunderstand though, MySQL / MariaDB is an extremely capable relational database system, and is used on all levels of systems and applications around teh world.

When I first started, my only relational database experience was what I had learned working with Microsoft Access.  I was very accustomed to a designer, and all kinds of GUI tools.  My reaction to attempting to move to open source software was to find something with similar features.  Initially I found PHPMyAdmin.  It was good, but definitely not what I was used too.  I had to adjust...that's life.  

Fast forward about 12 years, and I get involved with a project that my Linux User Group started.  We decided initially on MySQL as our database engine.  I, again, initially started wtih PHPMyAdmin, but it wasn't giving me everything I needed.  The hunt for something began again, and I stumbled upon MySQL Workbench.  What a great tool.  I was writing scripts to setup the database, and create "starter" data, and check data integrity...it was just so much more than I had ever done, and I really needed a tool that could help me do this with confidence.

Image of MySQL Workbench Dashboard

Get It

You can get MySQL Workbench Community from https://dev.mysql.com/downloads/workbench/.

It's worth noting that there is a MySQL Workbench Enterprise version that is available for purchase as well.  If you're curious about what features you get with Enterprise, I'm listing them here.  Note, that Enterprise is also generally a newer version than the Community edition.  These types of paid offerings are what allow the Open Source / Free versions to continue to be developed and released.

Install The Community Edition

Once you've downloaded the Community Edition for Ubuntu / Debian versions of Linux, you'll need to run the .deb installer.  I do this through the terminal.

sudo dpkg -i mysql-workbench-version.deb <– don't copy this directly...make sure to use your downloaded version filename.

Alternatively, you can use

sudo apt install ./mysql-workbench-version.deb <-- again, don't copy this directly, use the proper filename.

In running the dpkg command on Ubuntu 18.04, I got an install error due to a missing dependency.  To resolve this you need to use the command:

sudo apt install -f

Affirm your intention to continue the installation of the dependencies needed by entering y.

Now, re-run your dpkg command, and MySQL Workbench will be installed and ready to use.

Check out the video on youtube for connecting to 1, or many MySQL / MariaDB systems.

Support my Channel and Content

Support my Channel and ongoing efforts through Patreon:
https://patreon.com/awesomeopensource