Accessing MySQL NDB Cluster Database From MySQL Connector/Python

In this post, we will see how to access database and its objects in MySQL NDB Cluster from Connector/python program. I assume that the reader has some basic understanding of python language and MySQL NDB Cluster.

Let’s create a MySQL NDB Cluster with the following environment:

• MySQL NDB Cluster version (Latest GA version)
• 1 Management node
• 4 Data nodes
• 1 Mysqld server
• Configuration slots for up to 4 additional API nodes
• Connector/Python version (Latest GA version)

Note: Python software must be installed on the same host where we are planning to install MySQL Connector/Python.

MySQL NDB Cluster Architecture:

Let’s look at the MySQL NDB Cluster architecture.

MySQL Connector/Python is one of the connector provided by MySQL to access data in the database. Connector/Python is a library written in python that can talk to the MySQL server. It is a self-contained driver i.e. Connector/Python does not require the MySQL client library or any Python modules outside the standard library.

From NDB prospective, MySQL server (mysqld) is one of the API node which allows SQL access to data stored in the cluster. API nodes are the point of access to the system for user transactions. User transactions are defined and sent to data nodes which process them and send results back. So MySQL Connector/Python send the user transactions to the MySQL server which in turns send it to data nodes where it is processed and return back to user again thru MySQL server.

Step 1: Get the MySQL Connector/Python package

Users can get the MySQL Connector/Python packages from MySQL download site (link). Select your required Connector/Python version for download.

For demonstration purpose, I am using Connector/Python RPM package (Linux). Before start installing the packages, it’s better to check if any old versions packages is already installed on it. If yes, then please remove them first.

To check the older packages installed on your host:

    HOST> rpm -qa | grep -i mysql

In my host, I can see the older versions already installed.

From the above image, we can see that there are many old versions co-exist with the latest versions of packages. Let’s remove those first. To remove the packages, use the below command:

    HOST> rpm -e old_packages_name

Once you delete all the packages then check once again if any leftover packages are still there or not.

Now as there are no more packages exist on the host, lets installed the Connector/Python packages.

HOST> sudo rpm -i mysql-connector-python3-8.0.23-1.el7.x86_64.rpm

Note:
On this host (cluster-test09), we have not installed or running either MySQL NDB cluster nodes or MySQL server. Only Connector/Python is installed. From this host we will try to connect to MySQL NDB Cluster.

Once the Connector/Python is installed, we can verify it through below command:

cluster-test09> rpm -qa | grep i mysql
cluster-test09> python

From the above image we can see that connector/python package is installed successfully.

Step 2: Start the MySQL NDB Cluster

Let’s start the cluster with above configuration.

Step 3: Create a user with whom connection will be made

Connection:

MySQL Server running on host:
        cluster-test01 (IP: 100.103.21.66)

Connector/Python has been installed on host:
        cluster-test09 (IP: 100.103.21.70)

We will create a user called ‘user1’ with password ‘user123#’ and will grant permission so that when any connection attempt is made from other host, then the connection should be allowed.

Let’s check who can connect to the MySQL Server.

Cluster-test01> select host from mysql.user where user="root";

At present only user ‘root’ have access to connect.

Let’s try to connect to the MySQL server (cluster-test01) from the connector host (cluster-test09) and see if we are allowed or not.

Cluster-test09> bin/mysql -u root -P"13010" -h "100.103.21.66"

From the above image, we can see that we are not allowed to connect to the MySQL Server from the connector host.

Now let’s create a user say ‘user1’ and assigned a password ‘user123#’. Then grant the permission to the new user created.

From MySQL Server (Cluster-test01):

mysql> create user 'user1'@'100.103.21.70' identified by 'user123#';
mysql> grant all on *.* to 'user1'@'100.103.21.70';
mysql> flush privileges;
mysql> select host from mysql.user where user="user1";

As the user is already created, let’s try to connect to the MySQL Server from the connector host. This is through MySQL connection not from Connector/Python. The idea is to verify if we are allowed to connect or not.

Connector host (cluster-test09):

mysql> bin/mysql -u user1 -p"user23#" -P"13010" -h "100.103.21.66"

From the above image, we can see that user ‘user1’ is allowed to connect. This connection is through mysql client from connector host.

Let’s check that we have not created any databases before as we will create a database say ‘test1’, table ‘t1’ and few insert/update/delete on the table rows from the Connector/Python program.

Now let’s write a python program where we will initiate a connection to the MySQL server and then create a database and table.

Please look below for the python code snippet:

import mysql.connector

# pass all the configurations that is required to connect the MySQL server.
#

config = {
                        'host': '100.103.21.66',
                        'user': 'user1',
               'password': 'user123#',
                        'port': '13010',
'raise_on_warnings': True
}

# Connect to the MySQL server
cnx = mysql.connector.connect(**config)
cur1 = cnx.cursor()

# Create a database 'test1'
cur1.execute("create database test1")
cur1.execute("use test1")

# Create a table 't1'
cur1.execute("create table t1(col1 int primary key, col2 int unsigned not null, col3 char(255)) engine=ndb")

# Close the connection
cur1.close()
cnx.close()

Once we run the above program, let’s check whether database and table are created or not from the MySQL client.

From the above image, we can see that database ‘test1’ and table ‘t1’ got created. Let’s insert few rows into the table ‘t1’ and run the program again.

import mysql.connector

# pass all the configurations that is required to connect the MySQL server.
#

config = {
                        'host': '100.103.21.66',
                        'user': 'user1',
                'password': 'user123#',
                        'port': '13010',
'raise_on_warnings': True
}

# Connect to the MySQL server
cnx = mysql.connector.connect(**config)
cur1 = cnx.cursor()

# Create a database 'test1'
cur1.execute("create database test1")
cur1.execute("use test1")

# Create a table 't1'
cur1.execute("create table t1(col1 int primary key, col2 int unsigned not null, col3 char(255)) engine=ndb")

# Insert few rows into the table 't1'
sql="insert into t1 (col1, col2, col3) values (%s, %s, %s)"
val=[(1,1,'aaaa'), (2,1,'bbbb'), (3,1,'cccc'), (4,1,'dddd'), (5,1,'eeee'), (6,1,'ffff')]
cur1.executemany(sql,val)
cnx.commit()

# Close the connection
cur1.close()
cnx.close()

Let’s verify the rows inserted from the MySQL client.

From the above image, we saw that ‘6’ rows has been inserted into table ‘t1’. Let’s do some more ops on the table rows like delete and update.

Let’s change the python program accordingly and then run it.

import mysql.connector

# pass all the configurations that is required to connect the MySQL server.
#

config = {
                        'host': '100.103.21.66',
                        'user': 'user1',
                'password': 'user123#',
                        'port': '13010',
'raise_on_warnings': True
}

# Connect to the MySQL server
cnx = mysql.connector.connect(**config)
cur1 = cnx.cursor()

# Create a database 'test1'
cur1.execute("create database test1")
cur1.execute("use test1")

# Create a table 't1'
cur1.execute("create table t1(col1 int primary key, col2 int unsigned not null, col3 char(255)) engine=ndb")

# Insert few rows into the table 't1'
sql="insert into t1 (col1, col2, col3) values (%s, %s, %s)"
val=[(1,1,'aaaa'), (2,1,'bbbb'), (3,1,'cccc'), (4,1,'dddd'), (5,1,'eeee'), (6,1,'ffff')]
cur1.executemany(sql,val)
cnx.commit()

# Delete a single row from the table 't1'
sql="delete from t1 where col1=3"
cur1.execute(sql)
cnx.commit()

# Update a single row
sql="update t1 set col3='kkkk' where col1=5"
cur1.execute(sql)
cnx.commit()

# Close the connection
cur1.close()
cnx.close()

Now let’s verify both the ops from the MySQL client.

From the above image, we can see that row#3 is already deleted and row#5 where col3 is also updated with new value ‘kkkk’. So we are able to connect and do some operations on the NDB table from Connector/Python program.

This concludes our discussion on accessing NDB Cluster database from MySQL Connector/Python.

*******************************************************************************

This blog is dedicated to my manager Mr. Bernd Ocklin on his last working day (23/04/2021) at Oracle/MySQL. I wish him all the very best for his new venture!

                             

*******************************************************************************