MySQL-Python – 2. Database and user

This is continuation from MySQL-Python – 1 post where we installed MySQL server. 


In this page:


Accessing database from MySQL Workbench

We will access Sakila sample database with Python. 

First to remeber, when we installed MySQL we choose to install and configure "Samples and Examples". This option installed Sakila database.


We can see it fast, that it is on our server, either using in Windows 11: All Apps > MySQL > MySQL Workbench 8.0 CE

Click on Local instance


MySQL Workench


If password requested for root, type it, then MySQL Workbench will open and display Administration in Navigator panel. 

Clik Schemas and we will see sakila db.


MySQL Workench


And clicking further on Tables we see those. 

MySQL Workench



Accessing database using MySQL Command Line Client


Similar we can see sakila database using MySQL Command Line Client

In Windows 11: All Apps > MySQL > MySQL 8.0 Command Line Client

MySQL Command Line 1

When prompt to enter password, enter root password.

 

Now we can use sakila database, for example type USE sakila;

then we can execute a sql command for example select how many lines are in actor table. 

MySQL Command Line 2


Observation: if sakila database was not installed initial when MySQL was installed 

it can be added later, for example follow procedure like here.


In both cases from above user was root. In real life when access databases in applications we use a normal user (a user with fewer privileges, from security reason).  Below is the procedure to create the user.


Creating user from MySQL Command Line Client

Lets suppose we need to create user u2024, after you login to  MySQL Command Line tool like root run "SELECT user FROM mysql.user;" to check if user already exist. 

MySQL Command Line select user 3


u2024 is not in the list. 

To create it run: CREATE USER 'u2024' IDENTIFIED BY 'password1234';

MySQL Command Line create user

After create user we use again "SELECT user FROM mysql.user;" which confirm that user is created. 


Next step is to grant privileges to u2024 in sakila database for this we run:

GRANT SELECT,CREATE,INSERT,DELETE,UPDATE,EXECUTE,ALTER,DROP,LOCK TABLES,CREATE TEMPORARY TABLES ON sakila.* TO 'u2024';

MySQL Command Line grant 4


To test user, open a command prompt , change directory to folder where mysql is:

cd "C:\Program Files\MySQL1\MySQL Server 8.0\bin"

then connect with that user: mysql -u u2024 -p 

MySQL Command Line connect with user


Further we verify if we run commands, for example select. 

MySQL Command Line select

Run  quit; to exit from MySQL Command Line


Creating user from MySQL Workbench CE

Lets suppose we need to create user u2023.

After open MySQL Workbench CE and use root to connect we have this screen.

MySQL Workbench CE Users and Privileges


Click on "Users and Privileges" there is:

MySQL Workbench CE Users and Privileges 1

click "Add Account


MySQL Workbench CE Add User

Write details as in underlined with red edit boxes, then clik Account Limits


MySQL Workbench CE Account Limits

Here we can set limits for user connections, updates and queries. 

Click Administrative Roles.


MySQL Workbench CE Administrative Roles

here we can add administrative roles used for administer server. We not check something here because u2023 is just a user with limited privileges. 

Click Schema Privileges.


MySQL Workbench CE Schema Privileges

To add privileges for sakila schema click "Add Entry..."


MySQL Workbench CE Schema Privileges 2

Here select schema sakila then click Ok.


MySQL Workbench CE Schema Privileges

Here select rights, similar with what we granted previous for u2024

Click Apply.


User u2023 is created. 

MySQL Workbench CE Schema Privileges 4

MySQL-Python – 1. Installing mySQL

In this page: 


Downloading MySQL

Currently MySQL have 3 flavors: 

  • MySQL Enterprise Edition
  • MySQL NDB Cluster CGE
  • MySQL Community (GPL)

In current article we will discuss how to install MySQL Community (GPL) edition on Windows 10 or 11. 

To download installer first search for "MySQL download" or just load https://www.mysql.com/downloads/

MySQL download

Click on link "MySQL Community (GPL) Downloads"


mySQLDownload-2

Click on link "MySQL Installer for Windows"


MySQL Download 3

Select mysql-installer-community-8.0.35.0.msi to download installer local on pc (or select mysql-installer-web-community-8.0.35.0.msi to start installation directly from web)


MySQL Download 4

Specify location or path where to save file then click Save


MySQL install

After file mysql-installer-community-8.0.35.0.msi download finish, double click on it.


mySQL custom install-5

There are four setup type: 

  • Server only
  • Client only
  • Full
  • Custom

We choose Custom setup because it permit to select components that we desire to install.

After we choose setup type we select the products to install.


MySQL select products

Regarding to selected products it is desirable to mention that:

  • MySQL Workbench is a graphical tool which can be used for working with SQL commands.
  • MySQL Shell is a command prompt tool for the same, i.e. working with SQL commands, in plus in MySQL Shell we can use JavaScript and Python scripts. 


Click next there is:

MySQL installation


Then click Execute:

MySQL installation

the install process start. 


MySQL installation

After install complete click Next


MySQL configuration process

It will follow configuration process.

MySQL configuration


Click Next

mySQL configuration-11

In this screen we can choose what king of server (computer) we will use, depending by the system resources (in special by RAM memory). As we see there are three choices: 

  • Development Computer
  • Server Computer
  • Dedicated Computer

We will choose Server Computer.


In the next step we configure server connectivity.

MySQL configuration

MySQL server as most database servers support three kind of 

connectivity: 

  • TCP/IP
  • Named Pipe, this is a bidirectional IPC (interprocess communication) usual on Windows.
  • Shared Memory, this kind of connection use a shared memory like a communication chanel by which client and server can communicate.

We choos TCP/IP like connectivity, this is reliable. 3306 is the usual TCP/IP port which is used most of the cases. 

There is also X protocol port 33060. A session object use this protocol to permit to run operation on the connected server. 


Click Next permit to choose authentication method.

MySQL configuration

We choose strong password encryption. 

Click Next.


mySQL configuration-root account password

In this screen we set root account password (remember it or note it). root user is the user with full privileges on server. In this screen using "Add User" account we can create new accounts. 

Click Next. 

MySQL configure windows service


MySQL configuring windows service

Here we set MySQL to run as a Windows Service (option valid as we install on windows). Running as a services is useful due easy procedure to start/stop service. 

We choose "Standard System Account" for run MySQL Service under a system account, which is more easy and safe. 

Click Next.


mySQL configuration-server files permission-16

In "Server File Permissions" we choose "Yes, grant full access...", usual this is for easy configuration, but sometime is more suitable second option where we can review and decide which privileges to grant.

Click Next.


mySQL configuration-apply-17

In "Apply Configuration" screen are listed all configuration steps 

that will be applied. Click Execute to apply those.

Click Execute.


MySQL configuration Finish.

After configuration is successful click Finish.


MySQL configuring sample.

Click Next to start to configure "Samples and Examples".


MySQL configuring samples

In this screen enter root password to connect to MySQL Server for configuring examples, then click Next.


MySQL configuring samples, connection to server successful.

In this screen is displayed that connection to MySQL server is successful.

Click Next.


MySQL Configuring samples.

This screen show configuration steps for Samples and Examples.

Click Next.


MySQL configuring samples.

Configuration for Samples and Examples is applied succesfully. Click Finish.


MySQL configuration.

Click Next.


MySQL configuration.

Instalation is complete. In this screen we can choose to start MySQL Workbench and/or MySQL Shell.

Click Finish.


MySQL Shell

This is how MySQL Shell looks.


MySQL Workbench.

This is MySQL Workbench.