Customize Consent Preferences

We use cookies to help you navigate efficiently and perform certain functions. You will find detailed information about all cookies under each consent category below.

The cookies that are categorized as "Necessary" are stored on your browser as they are essential for enabling the basic functionalities of the site. ... 

Always Active

Necessary cookies are required to enable the basic features of this site, such as providing secure log-in or adjusting your consent preferences. These cookies do not store any personally identifiable data.

No cookies to display.

Functional cookies help perform certain functionalities like sharing the content of the website on social media platforms, collecting feedback, and other third-party features.

No cookies to display.

Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics such as the number of visitors, bounce rate, traffic source, etc.

No cookies to display.

Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.

No cookies to display.

Advertisement cookies are used to provide visitors with customized advertisements based on the pages you visited previously and to analyze the effectiveness of the ad campaigns.

No cookies to display.

Schedule Meeting

a

Getting Started with MindsDB and MySQL

by | Jan 10, 2024 | MySQL

Need Help?  Click Here for Expert Support

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

The Mystery of ProxySQL Galera Writer Switchover!

The Mystery of ProxySQL Galera Writer Switchover!

Writer switchover issues with ProxySQL and Galera can feel like an unsolved puzzle. Recently, I encountered two strange behaviors that revealed underlying issues in how ProxySQL interacts with Galera clusters. In this post, I’ll walk through the unexpected behaviors I...

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,...

Services

Need Help?  Click Here for Expert Support

0 Comments

Submit a Comment

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