Dbeaver Mysql Local Client



  • Setting Up
  • Connecting to a Third Party Client

DBeaver Community Edition. DBeaver CE is a popular open-source desktop application for working with SQL-databases. It supports all popular databases: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server and others. Look at DBeaver CE. The IP address of the device on which DBeaver is installed is added to the whitelist of the AnalyticDB for MySQL cluster. For more information, see Configure a whitelist. A public endpoint is applied for if you need to use a public endpoint to connect to AnalyticDB for MySQL clusters. The docs imply DBeaver will just find it if it exists in the normal places or if I have MySQL Workbench installed, which I do (version 6 and 8), but it doesn't. I've been using MySQL Workbench for normal database stuff, then Sequel Pro for importing (because the progress bar actually works), and I had DBeaver recommended to me yesterday, but.

Setting Up

Local

Installation

This part describes the basic installation steps of setting up MySQL 5.7 server on Ubuntu Linux using docker.

Dbeaver

  • Install docker on your Linux machine. See the instruction here.

  • Install docker compose via following the instructions here.

  • Create another folder on your project folder and make a docker-compose.yml file. Run the following instructions one by one:

  • Open the docker-compose.yml file and copy the following lines into it.

Run MySQL Server

Run the docker-compose command. This will build and run the server in detached mode.

Connect Shell to Server

Check the name of the running container with docker ps command. In this case, the running container is called mysql_dumps_mysql-dev_1. Then run the following command to connect your shell to the running server.

Alter Root Password

If you want to change the root password, enter the following command in the MySQL shell. Replace MyNewPass with your new root password:

You should see something like this in the command prompt:

To make the change take effect, type the following command:

Mysql

View Users

MySQL stores the user information in its own database. The name of the database is mysql. If you want to see what users are set up in the MySQL user table, run the following command:

You should see something like this:

Create a Database

According to the docker-compose.yml file, you already have created a database named test_db. You can create anotehr database named test_db_2 via the following command:

List your databases via the following command:

You should see something like this:

To ensure the changes:

Creating Dummy Table in the Database

Show Tables

Delete a Database

To delete a database test_db run the following command:

Add a Database User

To create a new user (here, we created a new user named redowan with the password password), run the following command in the MySQL shell:

Ensure that the changes has been saved via running FLUSH PRIVILEGES;. Verify that a user has been successfully created via running the previous command:

You should see something like below. Notice that a new user named redowan has been created:

Local

Delete a Database User

To delete a database user (here, I’m deleting the user-redowan) run:

Grant Database User Permissions

Dbeaver Local Client Mysql Mac

Give the user full permissions for your new database by running the following command (Here, I provided full permission of test_db to the user redowan:

If you want to give permission to all the databases, type:

Loading Sample Database to Your Own MySQL Server

To load mysqlsampledatabase.sql to your own server (In this case the user is redowan. Provide database password in the prompt), first fireup the server and type the following commands:

Now run:

You should see something like this:

Stop the Server

The following command stops the server.

Notice that a new database named classicmodels has been added to the list.

Connecting to a Third Party Client

We will be using DBeaver as a third party client. While you can use the mysql shell to work on your data, a third partly client that make the experience much better with auto formatting, earsier import features, syntax highlighting etc.

Dbeaver Mysql Local Client Login

Installing DBeaver

You can install DBeaver installer from here. Installation is pretty straight forward.

Connecting MySQL Database to DBeaver

Fire up DBeaver and you should be presented with this screen. Select MySQL 8+ and go next.

The dialogue box will ask for credentials to connect to a database. In this case, I will log into previously created local database test_db with the username redowan, corresponding password password and press test connection tab. A dialogue box might pop up, prompting you download necessary drivers.

If everything is okay, you should see a success message. You can select the SQL Editor and start writing your MySQL scripts right away.

Connecting to MySQL Server via Python

PyMySQL and DBUtils can be used to connect to MySQL Server.