MYSQL Server VS SQL lite

 

Feature	SQLite	MySQL Server
Architecture	Embedded (no server)	Client-server
Setup	No installation needed	Requires installation & configuration
Storage	Single .db file	Managed database directories
Concurrency	Limited (best for low write load)	High concurrency support
Scalability	Small to medium apps	Medium to very large systems
User Management	None (file-based access)	Full user & role system
Performance	Very fast for local/single-user	Optimized for multi-user workloads
Backup	Copy the file	Dump tools / replication / snapshots

What is SQLite?

SQLite is a lightweight, embedded relational database engine.

Key characteristics:

• 📦 Serverless — no separate database server process

• 📁 File-based — entire database is stored in a single file

• ⚡ Zero configuration

• 🔌 Runs inside your application (linked as a library)

Commonly used in:

• Mobile apps (Android, iOS)

• Desktop apps

• Browsers (e.g., local storage engines)

• Small tools and embedded systems

---

What is MySQL Server?

MySQL is a full client-server relational database management system (RDBMS).

Key characteristics:

• 🖥 Runs as a separate database server process

• 🌐 Supports multiple concurrent users

• 🔐 Advanced security & user management

• 📊 Designed for web apps and production systems

Commonly used in:

• Web applications

• Enterprise systems

• APIs & backend services

• Cloud-hosted platforms

• A mobile Angular + Capacitor app → SQLite

• A Node.js backend serving 10,000 users → MySQL Server

SQLite has a built-in .dump command.

Using CLI:

sqlite3 mydatabase.db .dump > dump.sql

This generates a file containing:

• CREATE TABLE statements

• INSERT statements

• Indexes

• Triggers

Restore from dump:

sqlite3 newdatabase.db < dump.sql

---

🔹 2️⃣ Simple Backup (Copy the File)

Because SQLite is just a single file:

cp mydatabase.db backup.db

⚠️ Important:
If the database is being written to, use the .backup command instead:

sqlite3 mydatabase.db ".backup backup.db"




🔹 2️⃣ Read Performance
SQLite is very fast for reads.
It can handle:


Thousands of SELECT queries per second


Many concurrent readers


Great performance for cached data


Reads do NOT block other reads.

🔹 3️⃣ Write Concurrency (Important Limitation)
SQLite allows:

🚨 Only ONE writer at a time.

That means:


Multiple users can read simultaneously


But writes are serialized (queued)


If many users try to write at the same time:


You’ll see database is locked errors


Performance drops under heavy write load

Rough Practical Numbers
These are approximate real-world observations:
Workload Type
What SQLite Can Handle
Reads
10,000+ per second (depending on hardware)
Writes
100–1,000 small writes/sec (serialized)
Concurrent Users
Dozens fine, hundreds depends on workload
Database Size
Multi-GB very common




MySQL support concurrent writes?
Yes — especially when using the default engine InnoDB (the default in modern MySQL).
It supports:


Multiple concurrent writers


Row-level locking


Transactions (ACID compliant)


MVCC (Multi-Version Concurrency Control)


This is very different from SQLite, which allows only one writer at a time.

MYSQL , restrict user from hosts, change restriction, restrict users from docker container hosts

 MYSQL Docker container created user root is root@%, so it can connect from any host

During MYSQL init set up , in init.sql, you might have

CREATE USER 'test'@'%' IDENTIFIED BY 'gitea';

GRANT ALL PRIVILEGES ON `gitea`.* TO 'test'@'%';

thats allowing user test to connect from any host, to enforce restriction for the user from a docker container service from any docker container within a docker subnet, its more safer to do :

CREATE USER 'test'@'169.255.255.%' IDENTIFIED BY 'gitea';

GRANT ALL PRIVILEGES ON `gitea`.* TO 'test'@'169.255.255.%';

Or if your init.sql already ran, you can do :

RENAME USER 'test'@'%' TO 'test'@'169.255.255.%';

You can do this with root user as well, 

If root@'localhost' already exists, drop the wide one:

sql

Copy code

DROP USER 'root'@'%';

you can use SQL statement to check:

SELECT user, host, plugin FROM mysql.user WHERE user='root';

Docker Compose MYSQL init, MYSQL port 3306

 docker mysql service has to use port 3306 to start multiple :

version: '3.8'
services:
mysql_db1:
image: mysql:8
environment:
MYSQL_ROOT_PASSWORD: password1
MYSQL_DATABASE: db1
ports:
- "3306:3306" # Maps host port 3306 to container port 3306
volumes:
- db_data1:/var/lib/mysql

mysql_db2:
image: mysql:8
environment:
MYSQL_ROOT_PASSWORD: password2
MYSQL_DATABASE: db2
ports:
- "3307:3306" # Maps host port 3307 to container port 3306
volumes:
- db_data2:/var/lib/mysql

volumes:
db_data1:
db_data2:

-----------------------------------------------------------------------------------------

To add an init.sql script to your MySQL Docker Compose setup to create the gitea and keycloak databases, follow these steps: Create the init.sql file.

Create a file named init.sql (or any other name ending in .sql, .sh, or .sql.gz) in a directory accessible to your docker-compose.yml file. For example, create a subdirectory named mysql-init and place init.sql inside it.

Code

    -- mysql-init/init.sql
    CREATE DATABASE IF NOT EXISTS `gitea`;
    CREATE DATABASE IF NOT EXISTS `keycloak`;

    -- You might also want to create dedicated users and grant privileges here
    -- For example:
    -- CREATE USER 'gitea_user'@'%' IDENTIFIED BY 'gitea_password';
    -- GRANT ALL PRIVILEGES ON `gitea`.* TO 'gitea_user'@'%';
    -- CREATE USER 'keycloak_user'@'%' IDENTIFIED BY 'keycloak_password';
    -- GRANT ALL PRIVILEGES ON `keycloak`.* TO 'keycloak_user'@'%';
    -- FLUSH PRIVILEGES;

Modify your docker-compose.yml.

In your docker-compose.yml file, add a volumes entry to your MySQL service to mount your mysql-init directory into the /docker-entrypoint-initdb.d directory within the MySQL container.

Code

    version: '3.8'

    services:
      mysql:
        image: mysql:8.0 # Or your desired MySQL version
        environment:
          MYSQL_ROOT_PASSWORD: your_root_password
          # Optional: Define default database, user, and password for initial setup
          # MYSQL_DATABASE: default_database
          # MYSQL_USER: default_user
          # MYSQL_PASSWORD: default_password
        volumes:
          - ./mysql-init:/docker-entrypoint-initdb.d
          - mysql_data:/var/lib/mysql # For persistent data
        ports:
          - "3306:3306"
        networks:
          - my_network

      gitea:
        image: gitea/gitea:latest
        environment:
          DB_TYPE: mysql
          DB_HOST: mysql:3306
          DB_NAME: gitea
          DB_USER: gitea_user # If you created a dedicated user
          DB_PASSWD: gitea_password # If you created a dedicated user
          # ... other Gitea configurations
        depends_on:
          - mysql
        networks:
          - my_network

      keycloak:
        image: quay.io/keycloak/keycloak:latest
        environment:
          KC_DB: mysql
          KC_DB_URL: jdbc:mysql://mysql:3306/keycloak
          KC_DB_USERNAME: keycloak_user # If you created a dedicated user
          KC_DB_PASSWORD: keycloak_password # If you created a dedicated user
          # ... other Keycloak configurations
        depends_on:
          - mysql
        networks:
          - my_network

    volumes:
      mysql_data:

    networks:
      my_network:

Run Docker Compose.

Navigate to the directory containing your docker-compose.yml file and run:

Code

    docker-compose up -d

The init.sql script will execute automatically when the MySQL container starts for the first time (when the /var/lib/mysql data directory is empty), creating the gitea and keycloak databases. Subsequent restarts will not re-run the init.sql script unless the mysql_data volume is cleared.

docker expose, docker-compose expose

 

1. Dockerfile → EXPOSE

• Purely documentation/metadata.

• Doesn’t actually open or bind anything.

• Other containers on the same network can still reach your app via container_name:port even if you don’t EXPOSE it.

• Example: you can still curl http://web:8080 inside another container, even if the Dockerfile doesn’t have EXPOSE 8080.

---

2. docker-compose.yml → expose:

• Same as Dockerfile’s EXPOSE, but defined in Compose instead.

• Makes the port visible to other containers in the same Compose network.

• Does not publish it to your host machine.

---

3. docker-compose.yml → ports:

• This is the one that really matters if you want to access the container from outside Docker (e.g., browser, Postman, curl from host).

• Maps container port → host port.
  Example:

  ports:
    - "8080:80"   # host:container

Nginx http server, terminate TLS, proxy http, Nginx TLS proxy

 docker-compose

networks:

  backend:

    ipam:

      driver: default

      config:

        - subnet: 169.254.6.0/28

services:

 go-service:

  ..............

    networks:

      backend:

        ipv4_address: 169.254.6.2

.................

  nginx:

    image: nginx:1.27-alpine

    restart: unless-stopped

    networks:

      backend:

        ipv4_address: 169.254.6.4

    ports:

      - "443:443"

      - "9586:9586"

    volumes:

      - ./nginx/nginx.conf:/etc/nginx/nginx.conf:ro

      - ./nginx/certs/myserver:/etc/nginx/certs:ro

      - ./nginx/logs:/var/log/nginx

    extra_hosts:

      - test.com:169.254.6.2

!!!!!!!!!!!!!!! then whenever u use test.com in nginx container it will go to 169.254.6.2 the docker service

nginx.conf

# /etc/nginx/nginx.conf

worker_processes auto;

events { worker_connections 1024; }

http {

    include       /etc/nginx/mime.types;

    default_type  application/octet-stream;

    server_tokens off;

    sendfile      on;

    access_log /var/log/nginx/access.log;

    error_log  /var/log/nginx/error.log;

    server {

        listen 443 ssl;

        http2 on;

        server_name site_a.com;

        ssl_certificate     /etc/nginx/certs/site_a.crt;

        ssl_certificate_key /etc/nginx/certs/site_a.key;

        root /usr/share/nginx/html;

        location = /docs {

            return 301 /docs/;

        }

        access_log /var/log/nginx/site_a-ssl.access.log;

        error_log  /var/log/nginx/site_a-ssl.error.log;

    }

#teminates TLS at nginx, reverse proxy http to go service 

    server {

        listen                  443 ssl;

        server_name             siteb.com;

        #charset koi8-r;

        access_log /var/log/nginx/siteb-ssl.access.log;

        error_log  /var/log/nginx/siteb-ssl.error.log;

        ssl_certificate     /etc/nginx/certs/siteb.crt;

        ssl_certificate_key /etc/nginx/certs/siteb.key;

        proxy_set_header    X-Real-IP        $remote_addr;

        proxy_set_header    X-Forwarded-For  $proxy_add_x_forwarded_for;

#specified in docker

        location / {

            proxy_pass https:// test.com;

        }

        #error_page  404              /404.html;

        # redirect server error pages to the static page /50x.html

        #

        error_page   500 502 503 504  /50x.html;

        location = /50x.html {

            root   /usr/share/nginx/html;

        }

    }

}

// TCP reverse proxy

stream {

    server {

        listen 9888 reuseport;

        proxy_connect_timeout 10s;

        proxy_timeout 600s;

        proxy_pass test.com:9888;

        #log/inspect SNI if needed

        ssl_preread on;

    }

}

MYSQL important remark, how to listen to server IP, how to enforce TLS

 Create user xxx@IP

means create a user and only allows to connect if user is from this IP

it is different than MYSQL connection string -H (host IP) 

this host IP means where the MYSQL server is 

----------------------------------

GRANT ALL PRIVILEGES ON `db1`.* TO `xxxx`@`IP`;

 

FLUSH PRIVILEGES;

means grant all privlege of db 1 to user

--------------------------------------------------------------------

sudo netstat -tulnp | grep 3306

tcp        0      0 127.0.0.1:33060         0.0.0.0:*               LISTEN      2985660/mysqld

tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      2985660/mysqld

This means MYSQL only listen to localhost, to make it listen to server IP

do 

/etc/mysql/mysql.conf.d

Bind-address 0.0.0.0 so it listens on serverip or it will only listen to local host

---------------------------

Enforce SSL

MYSQL TLS cert location and specificaiton

check where config file is

cat /etc/mysql/my.cnf

go to /etc/mysql/conf.d/ if u found the config is above

check custom-mysqld-ssl.cnf (make your own custom file for TLS)

need chmod 644 this file

 

[mysqld]

# Use server.chain for "requires SSL" authentication

ssl_ca=myca.chain

# Use combined.chain for "requires x509" authentication

#ssl_ca=combined.chain

ssl_cert=myserver.cert

ssl_key=myserver.key

#require_secure_transport=ON

 !! this will make mysql to look cert at /var/lib/mysql/

·         Need 644 on all certs, need mysql:mysql on certs

Check:

SHOW VARIABLES LIKE 'ssl%';      

SHOW VARIABLES LIKE 'tls_version'; 

ALTER USER 'user'@'%' REQUIRE SSL

------------------------------------------------------------------------------------------------------------------------

sudo systemctl restart mysql

sudo systemctl status mysql

MYSQL show user permission and user@host

 MYSQL cmds to show user privileges

SELECT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') FROM mysql.user;

this produce a list of :

SHOW GRANTS FOR 'john'@'localhost';

then execute individual command to get permission of user

To create a new user in MySQL, execute the following SQL command within your MySQL client (e.g., MySQL Shell, phpMyAdmin, or a command-line interface):

Code

CREATE USER 'your_username'@'localhost' IDENTIFIED BY 'your_password';

• Replace 'your_username' with the desired username.
• Replace 'localhost' with the hostname or IP address from which the user will connect. Use '%' for any host.
• Replace 'your_password' with a strong, secure password for the new user.

This host measn the source IP of the user that can use to connect to 

if its test@123.456.789.123

if test is connecting from 155.155.155.231 test will be rejected

MY SQL grant permission 

GRANT ALL PRIVILEGES ON `<mydatabase>`.* TO `test`@`10.121.150.150`;

FLUSH PRIVILEGES;

• 
  
• 
  
• 
  
• Host Restriction:

  host specifies the allowed source(s) from which the user can connect. This can be:

  • localhost: Allows connections only from the same machine where the MySQL server is running (often using a Unix socket, or TCP/IP on 127.0.0.1).
  • A specific IP address (e.g., 192.168.1.100): Limits connections to that exact IP address.
  • A hostname (e.g., myclient.example.com): Restricts connections to that specific hostname.
  • A wildcard character (%): Allows connections from any host. This is often used for broad access but can pose security risks if not managed carefully.
  • An IP address range with wildcards (e.g., 10.0.%): Allows connections from any IP address within that specific range.

  ------------------------------------------------------------------------------------

  --------------------------------------------------------------------------------------

  What if mysql is running on server 172.168.11.110, the docker services running bridge network also in that machine, can docker user test@172.168.11.110 to conenct assume such user is created :
• 
  

Yes. MySQL decides which account to use based on the source IP it sees, and Docker’s NAT usually makes your container’s traffic look like it’s coming from the host’s IP (10.0.0.205) when it connects to a service on the host. So the server accepts test@10.0.0.205.

Why this happens

• Your container lives on a bridge subnet (e.g., 172.17.0.0/16).

• When the container connects to the host’s MySQL at 10.0.0.205:3306, Docker’s iptables rules do MASQUERADE/SNAT for packets leaving the bridge.

• Result: MySQL receives a connection from 10.0.0.205 → 10.0.0.205:3306 (hairpin NAT), which matches the MySQL user test@10.0.0.205.

How to confirm exactly what MySQL sees
Run this in your session after connecting from the container:

SELECT USER() AS client_says,
       CURRENT_USER() AS mysql_matched_account,
       SUBSTRING_INDEX(HOST, ':', 1) AS source_ip
FROM information_schema.processlist
WHERE ID = CONNECTION_ID();

• source_ip shows what IP MySQL thinks you’re coming from.

• CURRENT_USER() shows the precise user@host entry MySQL matched.