Learning how SQL databases work is essential for any backend web developer. In this post you will learn how to install a MySQL server and how to create databases, tables, users, etc.

Table of Contents

Install mysql-server

  • You can install mysql-server (from Oracle) or mariadb-server (an open-source fork). From my experience, MariaDB is less resource-intensive. 
    # Ubuntu
sudo apt install mysql-server -y
  • Secure your MySQL server by typing the next command and following the instructions: ``` sudo mysql_secure_installation

if using mariadb

sudo mariadb-secure-installation


## Create a new user and a database

- Create a user and his password.

sudo mysql -p -u root

CREATE USER ‘user’ identified by ‘password’;

- Create a database and grant permissions to 'user'.

CREATE DATABASE test_db CHARACTER SET ‘utf8’; GRANT ALL PRIVILEGES ON test_db.* TO ‘user’;


## Access from a remote computer

- If you need to access to your database remotely (without using SSH), you need to change the mysql server config file (`/etc/mysql/mysql.conf.d/mysqld.cnf` or `/etc/mysql/mariadb.conf.d/50-server.conf` or something similar, check in `/etc/mysql` for server's `.cnf` files) and look for this line:

Instead of skip-networking the default is now to listen only on

localhost which is more compatible and is not less secure.

bind-address = 127.0.0.1

- Change `127.0.0.1` to `0.0.0.0`, save changes and restart mysql service:

sudo systemctl restart mysql

- Now, (if port 3306 is open) you can access your database from a remote computer using any MySQL client:

mysql -h -u -p

- If you want to connect through SSL (with a CA certificate):

mysql -h -u --ssl-ca=


<div class="ad">
<!-- page-middle -->
<ins class="adsbygoogle"
     style="display:block"
     data-ad-client="ca-pub-4608737552961313"
     data-ad-slot="3492618020"
     data-ad-format="auto"
     data-full-width-responsive="true"></ins>
<script>
     (adsbygoogle = window.adsbygoogle || []).push({});
</script>
</div>


## Create a table

- This will create a table with two columns: "id" (integer values that auto increment for every row and must be unique and not null) and "name" (with text values).

USE test_db; CREATE TABLE test_table (id INT AUTO_INCREMENT PRIMARY KEY, name TEXT);


## Add a record

- This will add a new entry in the table (you don't need to specify an "id" value because of `AUTO_INCREMENT` option).

INSERT INTO test_table (name) VALUES (‘Peter Jackson’);


## View records

- This will show all columns from all records on "test_table".

SELECT * FROM test_table;

+—-+—————+ | id | name | +—-+—————+ | 1 | Peter Jackson | +—-+—————+ 1 row in set (0,00 sec)


## Update a record

- This will update a record based on the "id" key.

UPDATE test_table SET name=’Joe Jackson’ WHERE id=1;


## Delete a record

- This command deletes a record based on the "id" key.

DELETE FROM test_table WHERE id=1;


## Exit from mysql client

- To close the connection, just type:

exit; ```

Extra: using a container (podman/docker)

You can run a MySQL server inside a container using docker or podman. I’ll use podman but if you don’t use it, you only need to replace podman with docker in the following commands.

  1. I recommend you to create a new container network, so your containers can communicate with each other. 
     podman network create my_network
  2. Then, create and run the MySQL container inside the new network. 
     podman run -d --name my_mysql_server --network my_network -e MYSQL_ROOT_PASSWORD=my_password docker.io/library/mysql
    • If using docker, replace docker.io/library/mysql with mysql.
    • If using MariaDB, use this image: docker.io/library/mariadb.
    • Replace my_network and my_password with the network name and a root password for the MySQL server.
    • You can export the MySQL default port (instead of creating a network) with -p 3306:3306.
    • Replace my_mysql_server with a name for the MySQL server container.
    • There are optional “environment variables” to create a database or a user:
      • MYSQL_DATABASE: -e MYSQL_DATABASE=my_database.
      • MYSQL_USER and MYSQL_PASSWORD: -e MYSQL_USER=my_user -e MYSQL_PASSWORD=my_user_password. This user will be granted superuser permissions for the database specified by the MYSQL_DATABASE variable.
      • You can use the same environment variables for a MariaDB container.
      • For more info, check https://hub.docker.com/_/mysql.
  3. To run SQL commands inside the container, execute this command. 
     podman exec -it my_mysql_server mysql -p -u root
    • Replace my_mysql_server with the MySQL server container name.
  4. Now you can run MySQL commands: 
     mysql> show databases;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | sys                |
 | test_db            |
 +--------------------+
 5 rows in set (0.00 sec)
  5. To go out from the container, just type exit; and press Enter.

If you have any suggestion, feel free to contact me via social media or email.