Categories
Cloud Entrepreneurship General Topics and tips Sotfware & Developers & DevOps Storage Tools & How-Tos

Picking the SQL Database For You

A Comparison of SQL Database Flavors: How To Pick The Right SQL Database Flavor For You?

SQL, or Structured Query Language, is a database that has widespread uses with many flavors– which we will explore. As such, in our data-driven world, SQL is fast becoming an essential skill. Therefore, in this article, we will look at the most popular flavors of SQL databases individually and then compare the database flavors together. In addition, we’ll help you figure out the right choice for you; in case you want to learn more about some key basic fundamental query building structure– CRUD Operations.

sql database code
SQL code

Two Types: SQL vs NoSQL

There are two main types of databases: SQL and NoSQL. Before we begin, it’s important to know the differences between the two.

While SQL is a relational database that employs a table-based structure, NoSQL is a non-relational database that employs a variety of data models for accessing, retrieving and modifying database. Moreover, SQL has a predefined database schema, whereas NoSQL has a dynamically defined schema.

Additionally, the most popular flavors are mainly based on SQL rather than NoSQL— only MongoDB makes the cut in the top 5 databases in 2021—with other examples like Elastisearch, DynamoDB not used as often by developers. MongoDB is so popular because of the flexibility in offers in database design and the high performance it delivers.

The Most Popular SQL Database Flavors in 2021:

  • MySQL
  • PostgreSQL
  • Microsoft SQL Server
  • SQLite
  • MongoDB

Moreover, the Stack Overflow Developer Survey 2020 has placed MySQL as the most popular database:

sql database flavors popularity rankings 2021
Stack Overflow
FeaturesMySQLPostgreSQLMS SQLSQLite
SELECT QuerySELECT col1, col2…SELECT col1, col2…SELECT [col1], [col2]…SELECT col1,col2…
Current Date FunctionSELECT CURRENT_DATE();SELECT CURRENT_DATE;SELECT GETDATE()’;SELECT date(‘now’);
Case Sensitive DataNo; WHERE city = ‘London’ and WHERE city = ‘london’ are the sameYes. WHERE city = ‘London’ and WHERE city = ‘london are differentYes. WHERE city = ‘London’ and WHERE city = ‘london are differentYes. WHERE city = ‘London’ and WHERE city = ‘london are different
Column AliasSELECT col1 AS COLSELECT col1 AS COLSELECT col1= COLSELECT col1 AS COL
Natural JoinNoYesNoYes
ORDER ByAllows ORDER BY; NULLs are considered lower than non-NULL valuesAllows ORDER BY; NULLs are considered higher than non-NULL valuesAllows ORDER BY; NULLs are considered lower than non-NULL valuesAllows ORDER BY; NULLs are considered lower than non-NULL values
Inserting Multiple RowsYesYesYesYes
BOOLEAN Data TypeNo; uses TinyInt(1)YesNo; uses BIT(1)No; uses Int
Timestamp FunctionYesYesNoNo
Concatenationconcat(String 1, String 2)String 1 || String 2String 1 + String 2String 1 || String 2
List of DatabasesSHOW DATABASESSELECT datnameFROM pg_catalog.pg_databaseEXEC SP_HELPDB.databases
Dummy TablesNoNoNoNo
Window FunctionsYesYesYesNo; subqueries used.
Auto IncrementAUTO_INCREMENTSERIALIDENTITY()AUTOINCREMENT 
Key features and syntax comparison of SQL databases

MySQL

MySQL was created in 1995 to build on the original SQL, as an open source relational database management system (RDBMS). In addition, its queries are run on the MySQL Command Line Interface (CLI) or on MySQL Workbench.

Key Features & Advantages of MySQL:

In addition, MySQL has gained immense popularity for its efficacy, ease of use, performance and powerful execution and database performance:

  • Extremely fast for read-heavy workload
  • Capable of replication
  • Cloud-scalable database

Subsequently, let’s look at some advantages below:

  • Easy to use
  • Low memory leakage; memory efficient
  • Runs on all OS: Windows, Mac OS, Linux, etc.
  • High performance

Disadvantages of MySQL:

MySQL is one of the most used databases around the world. Still, it has some disadvantages you want to keep in mind. Let’s look at some below:

  • It does not handle transaction statements effectively
  • Lack of debugger tools

PostgreSQL

postgresql release years
Photo by Alibaba-cloud

PostgreSQL, or commonly known as Postgres, is also an open source object-based relational database management system (RDBMS) initially released in 1996. Moreover, its queries are natively run on pgAdmin, its client. In addition, PostgreSQL is a great database language for beginners because of its broad applicability.

Key Features & Advantages of PostgreSQL:

PostgreSQL focuses on SQL compliance; in other words, it is growing into one of the most popular flavor of SQL in recent years:

  • Client-server model architecture
  • Offers geo-location queries
  • Data integrity
  • Support for JSON data types

After that, let’s take a look at some advantages now:

  • Larger data storage for databases
  • Multi-version concurrency control (MVCC) without read locks
  • Creates views that are updatable
  • Creation of user-defined functions, triggers, data types, etc.
  • Runs on all operating systems: Windows, Mac, Linux, etc.
  • Robust and powerful
  • Huge community support
  • Compatible with many programming languages like C, Python, etc.

Disadvantages of PostgreSQL:

Despite all these advantages and features, Postgres, like any other language, also has its own disadvantages. Therefore, let’s take a look at some below:

  • PostgreSQL struggles in performance factors
  • It is responsible for more memory utilization of computer
  • Data replication is hard

Microsoft SQL Server

Microsoft SQL Server, or MS SQL, is a relational database management system developed by Microsoft in May 1989. Similarly, its queries are natively run on SQL Service Management System (SSMS). In addition, SQL Server is an important part of the Microsoft Data and Business Intelligence platforms.

Key Features & Advantages of MS SQL:

MS SQL is a language that’s growing in userbase with its widespread use. MS SQL is used by companies like Dell, Yahoo, etc. Therefore, let’s look at some key features:

  • Data compression
  • MS SQL is highly secure
  • Its installation process is streamlined
  • It offers different versions to suit personalized needs

Consequently, MS SQL is one of the most popular database languages today, so it makes sense to see some of its key advantages:

  • Free for developers
  • Useful online documentation
  • Debugging tools on SMSS
  • Great community support
  • Highly secure
  • Easy installation with automatic upgrades
  • Easy maintenance

Disadvantages of MS SQL:

In the end, there is no database language that is perfect. Therefore, let’s explore some disadvantages of MS SQL. In other words, like any other database, MS SQL also has negative factors that may affect your choice:

  • Performance tuning is hard
  • There can be a bit of learning curve
  • May be overwhelmed with the huge number of features offered alongside

SQLite

SQLite is a RDBMS built in C library with its primary use being in mobile applications. Unlike other databases. it is not based on client-server architecture, but rather makes use of the embedded architecture.

Key Features & Advantages of SQLite:

SQLite is gaining popularity for its features, customer service and quality. Let’s see its key features in the list below:

  • Configuration doesn’t exist– no installation required
  • Has an easy to use API

SQLite has a lot of advantages that we will look at now. Let’s see what these benefits are below:

  • Runs on multiple OS: Windows, Linux, Mac OS etc.
  • Installation not required
  • Server-less database
  • Flexible and fast database
  • Uses less amount of memory on runtime

Disadvantages of SQLite:

In the end, SQLite has the following disadvantages that we will look at now:

  • Performance compromised because of file system configuration
  • Write operations are recorded
  • Database file size is smaller (15MB)

MongoDB

MongoDB is a document-based database, unlike all the others on this list. As a result, it uses JSON-documents for storing data instead of tables. In addition, you can start writing query from its official editor available at this link.

Key Features & Advantages of MongoDB:

MongoDB is the most popular non-relational database because of these features:

  • Data stored in JSON-documents
  • Indexing used for traversal
  • Horizontally scalable database

In addition, the following are its advantages:

  • Flexible database schema, dynamically defined
  • High speed of access time
  • Easy to setup environment

Disadvantages of MongoDB:

After that, let’s look at some disadvantages:

  • JOINS not allowed
  • Data size is limited in MongoDB

The Right SQL Database Flavor For You Is…

sql database graph question ranknig
A graph from Datacamp

In fairness, it’s difficult to choose one database generically, since each database serves a goal or purpose.

SELECT e.emp_name, AVG(s. salary) AS average_salary
FROM employees AS e
LEFT JOIN salary AS s
ON e.emp_id = s.empid
WHERE e.city = 'USA'
GROUP BY e.emp_name
ORDER BY s.salary;

For instance, this code example above shows that the syntax is actually quite similar. However, it can be seen because the query output is same for MySQL, PostgreSQL and SQLite, there is not much syntactical differences.

However, Microsoft SQL Server is the one that consists of the most differences between all of them.

In other words, the right database for me may not necessarily fit or be the right one for you. As a result, each database is optimized for a specific purpose or need. Therefore, how do you know which database fits for you?

Above all, which database can you learn or base your backend in?
Then, let’s explore some key factors in helping you determine the right SQL database flavor for your needs.

Speed Of Databases:

  • MySQL is fast for concurrent activities
  • PostgreSQL is quite fast
  • Microsoft SQL Server is slower
  • SQLite is fast but not the fastest as compared to others
  • MongoDB is extremely fast for data retrieval since it uses indices to traverse

Performance Of Databases:

  • MySQL has limited performance optimization
  • PostgreSQL is responsible for high performance database
  • Microsoft SQL Server offers high performance and data compression
  • SQLite runs smoothly
  • MongoDB has a great performance because it relies on jsons

Database Sizes Of Databases:

  • MySQL supports limited size database in some scenarios.
  • PostgreSQL supports large database
  • Microsoft SQL Server supports large databases
  • SQLite supports big databases, but with limited file size
  • MongoDB supports large databases

Eventually, you need to ask yourself what factors matter most in your needs before you can aptly choose.

Which Database Should You Choose?

If you’re looking for a fast, high performance database that will be easy to use, MySQL may be the right choice. But, if you’re working on a much larger database or complex queries— PostgreSQL may be the right choice for you.

On the other hand, if you are working with highly sensitive data, then the security, integrity and sturdiness of Microsoft SQL Server may be the perfect choice for your needs. Similarly, if you want fast, portable, and reliable database that runs on multiple operating systems, SQLite may be great.

Furthermore, if your business and data requirements are evolving over time and you need to scale accordingly, the document-based database, MongoDB could be a wonderful selection.

Conclusion to Comparison of SQL Flavors:

SQL consists of multiple flavors of databases used around the world with varying popularity. In addition, these flavors of databases carry plenty of similar traits and syntactically are akin.

Similarly, we proceeded to look at the top 5 databases in the world today individually and then compared the results together; it’s hard to pick one generically since each database serves a goal or purpose.

In conclusion, I hope you will find this post useful. Please share your thoughts in the comments below. Also, like the post if you learned something new here. Subscribe to our blog. Stay updated on all related content. Lastly, let us know your thoughts about this post in the comments. Thanks for reading. Stay safe and stay healthy.

Hits: 24

Categories
Cloud Management and Projects Sotfware & Developers & DevOps Tools & How-Tos

MySQL master-slave replication with docker

a computer that is running mysql database for replication
MySQL Master Slave DB Replication with Docker and Docker-Compose

This post “MySQL master-slave replication with docker” was updated by: Syed Umar Bukhari on August 26, 2021

MySQL replication: a process to enable automatic copying of database(s) from one instance of MySQL to the other. In this case, we will look at master-slave replication– the most popular way to replicate SQL databases, specifically MySQL. We can create multiple slave servers for replication with a single master server. In this post, we use docker compose to create the replication on MySQL. Additionally, the code part of the post uses docker and docker-compose for MySQL master-slave replication The host OS runs the Ubuntu 18.04 OS with docker and docker compose for this experiment. You can use Windows, CentOS, Mac OS, etc. for your experiment as docker abstracts you from most of the host OS. Before beginning the process, it is assumed you have already installed docker and docker compose on your machine. If you haven’t, please do so before proceeding.

On a side note, let’s see a few reasons why MySQL is extremely popular as a database system. It is because it’s open source, has a lot of support from the community and the big tech companies. Similarly, by using SQL commands, MySQL can create, run, and query the database as well as build websites, application, it is quite efficient to use for the database designers. In addition, one of its most common uses is to build a wordpress (WP) websites.

By following this post you should be able to make your own database (DB) system much more resilient. Finally, if you want to take something out of why MySQL is important then know this— it lowers the learning curve, it’s free and it can scale to huge systems with billions of rows.

Create docker compose file

The docker compose file makes it easy for us to set some variables in the container, making the environment able to connect with the container. In addition, we use docker compose to run a multi-container environment based on the definitions in a YML file. After that, let’s create a docker compose file for replication as shown below:

version: '3'
services:
  mysql-master:
    image: percona:ps-8.0
    container_name: mysql-master
    restart: unless-stopped
    env_file: ./master/.env.master
    cap_add:
      - all
    volumes:
      - ./master/data:/var/lib/mysql
      - ./master/my.cnf:/etc/my.cnf
    environment:
      - TZ:${TZ}
      - MYSQL_USER:${MYSQL_USER}
      - MYSQL_PASSWORD:${MYSQL_PASSWORD}
      - MYSQL_ROOT_PASSWORD:${MYSQL_PASSWORD}
    networks:
      default:
        aliases:
          - mysql

  mysql-slave:
    image: percona:ps-8.0
    container_name: mysql-slave
    restart: unless-stopped
    env_file: ./slave/.env.slave
    cap_add:
      - all
    volumes:
      - ./slave/data:/var/lib/mysql
      - ./slave/my.cnf:/etc/my.cnf
    environment:
      - TZ:${TZ}
      - MYSQL_USER:${MYSQL_USER}
      - MYSQL_PASSWORD:${MYSQL_PASSWORD}
      - MYSQL_ROOT_PASSWORD:${MYSQL_ROOT_PASSWORD}
    networks:
      default:
        aliases:
          - mysql

In the docker compose file above, we use a different environment; in the MySQL master container we use an ENV file with the name .env.master. To differentiate, let’s create two folders: a master and slave folder– to separate the configuration for each container. Create folders using the command “mkdir”.

mkdir master && mkdir slave
crate master and slave directory

We have created a new folder to separate the master and slave files. We will create 2 new files next: .env.master. and .env.slave to use later.

touch master/.env.master && touch slave/.env.slave
two files env master and env slave created

Configuring the ENV file for MySQL master-slave replication with docker

This env file contains variables crucial for the container’s creation in docker compose. Additionally, the file makes it easy for us to store some information. We will create two env files for master and slave respectively. We edit the env file using “vi” command; you can use any text editor on Linux or Windows– such as Visual Studio Code or Atom.

vi master/.env.master
### WORKSPACE #############################################
TZ=UTC

#MYSQL_DATABASE=master
MYSQL_USER=master
[email protected]
MYSQL_PORT=3306
MYSQL_ROOT_PASSWORD=Mastermaster123

Create env dot slave file for slave server.

vi slave/.env.slave
### WORKSPACE #############################################
TZ=UTC

#MYSQL_DATABASE=slave
MYSQL_USER=slave
[email protected]
MYSQL_PORT=3306
MYSQL_ROOT_PASSWORD=slaveslave123

Below is an explanation for some of the variables contained in the env file to help us understand what role they perform.

Let’s look at the variables and their uses:


TZ is the time zone that will apply to the container.
MYSQL_DATABASE is the name of the database that will be created by itself.
MYSQL_USER is how the use enters into the database we create
MYSQL_PASSWORD is the password of the user that has been created. Creating a strong password is safe.
MYSQL_PORT is the port that runs the MySQL server.
MYSQL_ROOT_PASSWORD is the root user info to access all MySQL databases; create a password made of letters and symbols to be safe.

Create my.cnf file for master database.

[mysqladmin]
user=master
[mysqld]
skip_name_resolve
explicit_defaults_for_timestamp
basedir=/opt/bitnami/mysql
port=3306
tmpdir=/opt/bitnami/mysql/tmp
socket=/opt/bitnami/mysql/tmp/mysql.sock
pid_file=/opt/bitnami/mysql/tmp/mysqld.pid
max_allowed_packet=16M
bind_address=0.0.0.0
log_error=/opt/bitnami/mysql/logs/mysqld.log
character_set_server=utf8
collation_server=utf8_general_ci
plugin_dir=/opt/bitnami/mysql/lib/plugin
server-id=1
binlog_format=ROW
log-bin

[client]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
default_character_set=UTF8
plugin_dir=/opt/bitnami/mysql/lib/plugin

[manager]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
pid_file=/opt/bitnami/mysql/tmp/mysqld.pid
!include /opt/bitnami/mysql/conf/bitnami/my_custom.cnf

Make a my.cnf file for slave server as well.

[mysqladmin]
user=master

[mysqld]
skip_name_resolve
explicit_defaults_for_timestamp
basedir=/opt/bitnami/mysql
port=3306
tmpdir=/opt/bitnami/mysql/tmp
socket=/opt/bitnami/mysql/tmp/mysql.sock
pid_file=/opt/bitnami/mysql/tmp/mysqld.pid
max_allowed_packet=16M
bind_address=0.0.0.0
log_error=/opt/bitnami/mysql/logs/mysqld.log
character_set_server=utf8
collation_server=utf8_general_ci
plugin_dir=/opt/bitnami/mysql/lib/plugin
server-id=2
binlog_format=ROW
log-bin

[client]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
default_character_set=UTF8
plugin_dir=/opt/bitnami/mysql/lib/plugin

[manager]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
pid_file=/opt/bitnami/mysql/tmp/mysqld.pid
!include /opt/bitnami/mysql/conf/bitnami/my_custom.cnf
tree docker compose to understand the usage

From the picture above, let’s understand the usage of the master and slave folders and their files.


Data folder stores all the data files inside the container in the host.
My.cnf file makes configurations easier on MySQL.

Building a container for MySQL master-slave replication with Docker

Let’s build the MySQL master and slave containers with the docker compose config settings. Make sure you have everything ready to build container using the command “docker-compose up -d”.

docker-compose up -d
docker compose up building container for replication

Wait for the process of building the container to be done successfully. After that, check the process with the command “docker-compose ps”.

docker-compose ps
docker compose process check status mysql

Replication of MySQL master-slave with Docker

Now that the container runs properly, let’s begin the replication process.

Enter the container with “docker-compose exec container bash”. This will configure the replication with the MySQL command.

docker-compose exec mysql-master bash
docker compose exec command master slave bash replication start

Let’s login using the root user that we made above on MySQL now.

mysql -u root -p
login to the master slave replication server mysql

Create a user on MySQL for more replications.

mysql> CREATE USER 'replication'@'%' IDENTIFIED WITH mysql_native_password BY 'Slaverepl123';
creating a new user mysql

Grant user replication access to allow creation for MySQL replications.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
granting user replication mysql

Now, we examine if replication executed successfully or not.

mysql> show grants for [email protected]'%';
see the log status of users mysql

We will know the log status on master from this.

After that, we see the binary log of MySQL master with the following command:

mysql> SHOW MASTER STATUS\G
see the status of master server mysql

After that, the configuration on the master is complete and we continue to make the configuration on the slave. Login to container using “docker-compose exec” command.

docker-compose exec mysql-slave bash
logging in tto the containter mysql docker

After that, enter the MySQL slave server to run the following MySQL command.

mysql -u root -p
MySQL login

Execute this SQL command to push the MySQl slave part to join the master.

CHANGE MASTER TO
MASTER_HOST='mysql-master',
MASTER_USER='replication',
MASTER_PASSWORD='Slaverepl123',
MASTER_LOG_FILE='87e8982d00d1-bin.000004',
MASTER_LOG_POS=349;

The command to join to master from slave has successfully executed.

mysql change master

Let’s start the slave on mysql.

START SLAVE;
start slave mysql

After completion of all steps, recheck your work to ensure nothing was missed. Subsequently, check the status of replication on the slave server.

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: mysql-master
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 87e8982d00d1-bin.000005
          Read_Master_Log_Pos: 156
               Relay_Log_File: ba7af6f52d85-relay-bin.000002
                Relay_Log_Pos: 331
        Relay_Master_Log_File: 87e8982d00d1-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 156
              Relay_Log_Space: 547
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 5166800b-f068-11eb-abf5-0242ac150002
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.01 sec)

Make sure all databases are running smoothly, check on MySQL master.

show database master ensure mysql database db is working

If the master database is working fine, check on the slave.

mysql slave show database

After that, we will test to create a database on the master.

Therefore, let’s create a database to test if replication is working properly.

mysql> create database replicate_db;
create database replicate test

Let’s check on the slave whether the database created on the master can be created automatically on the slave.

mysql> show databases;
check slave database show

MySQL master slave replication on the docker machine is now successfully set up.

Conclusion

In this post, we have finished configuring MySQL master slave. Master slave on MySQL can be used in production and building applications. However, we must be careful when setting up replication in production. In the case you need to add slave servers, you need to manually configure it. This allows you to horizontally scale the readers. Moreover, you can backup MySQL into file storage; Sesamedisk provides storage for business and personal needs— data encryption and point in time recovery.

Additionally, you can find the docker config on github.

If you like this article, please add our blog to your bookmarks. We have lots of tech articles for you to study and understand. Lastly, I would encourage you to read this post about webrtc jitsi.

Update by: Syed Umar Bukhari on August 26, 2021

Hits: 457