Table of Contents

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.

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 <server-ip> -u <user> -p
  • If you want to connect through SSL (with a CA certificate):
mysql -h <server-ip> -u <user> --ssl-ca=<CA certificate file>

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
  1. 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)
  1. 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.