MySQL is an open-source Database management system used for cloud native applications. It is used for different kind of applications including ecommerce, data warehouse and other different data management apps. It is open-source and can be easily used for storing data for applications. There are different ways to setup a mysql instance and connect and use it with applications. In this tutorial, we will look into docker to use mysql database.
First you need to install docker which we will use for creating instance for MySQL. Docker has an official image for MySQL database which can be used for development and production.
Now first we run a docker container using MySQL image, it will automatically pull image from docker hub if it is already not available on your system.
Create MySQL Container
Open cmd/shell on your system and run following command to create a simple MySQL container. It will create and run a docker container named
mysql1 with root password in detached mode.
# create a docker container instance docker run --name mysql1 -e MYSQL_ROOT_PASSWORD=Admin-123456 -d mysql:latest
Once docker container is created, we need to perform some more steps to use it in our application.
We need to perform some more steps in ourder to use it. First we connect to container instance and login to mysql using root user.
# Enter password on prompt docker exec -it mysql1 mysql -uroot -p
Now we first create a database named testdb, you can also create in later steps.
CREATE DATABASE testdb;
Currently, it has only root user with priviliges, so we will create another user which we will use to access mysql server and perform operations.
# create a user named 'faizan' CREATE USER 'faizan'@'%%' IDENTIFIED BY 'MyPass_12345678'; # Grant Privileges GRANT ALL PRIVILEGES ON *.* TO 'bilal'@'%%' WITH GRANT OPTION; # Reload Grant Tables FLUSH PRIVILEGES;
Connect and Use
Now, we can connect to MySQL server using any supported programming language and query data. We will be using python to connect and perform some operations on database. First install required libraries using pip.
pip install mysql-connector-python
Now we connect to database using credentials.
import mysql.connector # Make Connection to MySQL Server db = mysql.connector.connect( host="localhost", # host user="faizan", # user password="MyPass_12345678", #password database='testdb' # database you want to connect ) # create cursor cursor = db.cursor(buffered=True)
We will create a table with two columns and insert and query data.
# create a table cursor.execute("CREATE TABLE IF NOT EXISTS users (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(30), email VARCHAR(50))")
Insert some data to table using python.
cursor.execute("INSERT INTO users (name, email) VALUES ('Faizan', 'firstname.lastname@example.org')") cursor.execute("INSERT INTO users (name, email) VALUES ('Ali', 'email@example.com')") # commit changes db.commit()
Now we can query data using select statements. For further details related to query data view SQL documentation.
cursor.execute("SELECT * FROM users") for row in cursor.fetchall(): # iterate on all rows based on query print(row) # will return all columns for a row
For more details related to MySQL, view details on official docker image details.