Schedule Meeting

a

Getting Started with MindsDB and MySQL

by | Jan 10, 2024 | MySQL

If you have not already heard, Vettabase is now a partner of MindsDB, working on improving MySQL compatibility.

In this post we shall take a look at getting started with MindsDB by connecting to MySQL and some of the improvements to date.

We have a ready to go example environment which will run MindsDB and MySQL in two separate Docker containers. We will be creating a new database user for MindsDB to connect, creating a database connection, and running a couple of queries to test the connection.

Example Environment

I have prepared the example environment, using Docker Compose:

You can either clone the example repo with git, or download and extract a zip file of the repository from Github.

From the root of the directory, we can start the containers:

docker compose up -d

NOTE: The MindsDB image is quite large, around 8GB at the time of writing. The image itself has the tag, lightwood, and AutoML framework.

Run docker ps to see the running containers

$ docker ps
CONTAINER ID   IMAGE             COMMAND                  CREATED          STATUS          PORTS                                             NAMES
54cbac9642d7   mysql             "docker-entrypoint.s…"   11 minutes ago   Up 11 minutes   3306/tcp, 33060/tcp                               vettabase-mindsdb-intro-mysql-1
6cfe06520b07   mindsdb/mindsdb   "sh -c 'python -m mi…"   11 minutes ago   Up 11 minutes   0.0.0.0:47334-47335->47334-47335/tcp, 47336/tcp   vettabase-mindsdb-intro-mindsdb-1

The MySQL container will automatically load the SQL in data/sample.sql to create the schema named sample and the test_table table.

Let’s also perform a quick check to see if all the data was loaded into MySQL by counting the rows and performing a sum on the two columns:

$ docker exec -it vettabase-mindsdb-intro-mysql-1 mysql sample -uroot -pSuperPass123 -e "select count(*) as c,sum(total) as t,sum(value) as v from sample.test_table;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+------+
| c  | t    | v    |
+----+------+------+
| 10 | 6146 |   94 |
+----+------+------+

MindsDB User Interface

MindsDB provides a very handy web User Interface so we can start exploring our data right away.

As per the docker-compose.yml and in docker ps we can see port 47334, is exposed. From your host machine, point a web browser to 127.0.0.7:47334 or the IP address of the machine where the containers are running.

You should now see fresh MindsDB instance:

Connecting MindsDB to MySQL

Before we can connect MindsDB to MySQL, we need to create a user in MySQL.

First let us open a MySQL command shell:

$ docker exec -it vettabase-mindsdb-intro-mysql-1 mysql -uroot -pSuperPass123

Then create a MySQL database user for the sample schema:

mysql> CREATE USER IF NOT EXISTS 'mindsdb'@'%' IDENTIFIED BY 'sampleData_12345';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT SELECT ON sample.* TO 'mindsdb'@'%';
Query OK, 0 rows affected (0.01 sec)

We can now create a connection in the MindsDB web interface using our new user:

CREATE DATABASE sample
WITH ENGINE = "mysql",
PARAMETERS = {
  "user": "mindsdb",
  "password": "sampleData_12345",
  "host": "mysql",
  "port": "3306",
  "database": "sample"
};

Alternatively, you can use the new URL parameter, which was also commited by Vettabase:

CREATE DATABASE sample
WITH ENGINE = "mysql",
PARAMETERS = {
    "url": "mysql://mindsdb:sampleData_12345@mysql/sample"
};

You may notice I have excluded the port number as we are using the default port of 3306 for MySQL, it is not necessary to explicitly set it.

You may already be familiar with adding database specific parameters to your connection details. One enhancement commited by Vettabase was having autocommit on, this means no transactions are left open.

Once the connection has been completed, we can use the sidebar to see what objects have been created and preview the data.

Using the same query as earlier, we can also query our MySQL data directly within the MindsDB interface:

select count(*) as c,
  sum(total) as t,
  sum(value) as v
  from sample.test_table;

The user we created only has SELECT privileges. If your user also has write privileges (INSERT/UPDATE/DELETE/...) then you can also use the MindsDB interface to edit your database! This is another useful secenario for when autocommit is enabled by default.

Using the methods above you can start by connecting MindsDB to your own data and start building machine learning models.

If you haven’t already, be sure to register for our webinar, Unlocking Real-Time Insights: AI-Powered Forecasting with MySQL and MindsDB!

Richard Bensley

All content in this blog is distributed under the CreativeCommons Attribution-ShareAlike 4.0 International license. You can use it for your needs and even modify it, but please refer to Vettabase and the author of the original post. Read more about the terms and conditions: https://creativecommons.org/licenses/by-sa/4.0/

About Richard Bensley
Richard Bensley is a Database Consultant at Vettabase Ltd. Prior to his current role, he worked at a number of companies as a DBA, Database Architect, Software Engineer and Developer with long running history of scaling and monitoring databases. Richard's past service record also includes 5+ years at MariaDB Corporation.

Recent Posts

MariaDB/MySQL: working with storage engines

MariaDB/MySQL: working with storage engines

MariaDB and MySQL support several storage engines. See MariaDB and MySQL storage engines: an overview for a discussion about existing MariaDB and MySQL storage engines. Here we'll see how to work with them. We'll see how to obtain information about storage engines,...

MariaDB and MySQL storage engines: an overview

MariaDB and MySQL storage engines: an overview

MySQL was the first DBMS to introduce the concept of storage engines in the early 2000s. This was one of its main features. Later, MariaDB extended the storage engine API and included some storage engine maintained by third parties as part of its official...

MySQL 8.0.33 : Quick Overview

MySQL 8.0.33 : Quick Overview

The MySQL Team has released MySQL 8.0.33 very recently on April 18, 2023. This release contains a number of bug fixes along with some interesting improvements. In this blog post, I'll cover the most important changes in MySQL 8.0.33. Variables can be set while...

Services

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *