MySQL: Installing and using a database server
Table of Contents
- Install mysql-server
- Create a new user and a database
- Access from a remote computer
- Create a table
- Add a record
- View records
- Update a record
- Delete a record
- Exit from mysql client
- Extra: using a container (podman/docker)
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) ormariadb-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
to0.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.
- I recommend you to create a new container network, so your containers can communicate with each other.
podman network create my_network
- 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)
- 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.
Latest tutorials and articles:
Featured content: