# Database Management
# 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](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/](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/](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/](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](https://patreon.com/awesomeopensource "Support me on Patreon Link")
# 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/](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.
- MySQL Database
- MySQL Storage Engines (InnoDB, MyISAM, etc.)
- MySQL Connectors (JDBC, ODBC, .Net, etc.)
- MySQL Replication
- MySQL Partitioning
- MySQL Router
- MySQL Shell
- MySQL Workbench
- 24x7 Technical Support
- MySQL Enterprise Backup
- MySQL Enterprise Monitor
- MySQL Enterprise HA
- MySQL Enterprise Transparent Data Encryption (TDE)
- MySQL Enterprise Masking and De-identification
- MySQL Enterprise Firewall
- MySQL Enterprise Encryption
- MySQL Enterprise Audit
### 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](https://patreon.com/awesomeopensource "Support me on Patreon Link")