How to install MySQL server on Debian 10 Linux

How can I install MySQL server 8.0 on Debian 10 LTS Linux server? How do I add a new MySQL user and database on the newly set up Debian server running on AWS EC2 server?

Oracle MySQL server version 8.0 is an open-source and free database used by most web apps and sites on the Internet. Typically MySQL is part of the LAMP (Linux, Apache/Nginx, MySQL, Perl/Python/PHP) stack. Popular opensource software such as WordPress, MediaWiki, and others heavily used by MySQL as a database storage engine. Let us see how to install MySQL server version 8.x on Debian 10 LTS Linux server for your web apps or forum or custom system.

How to install MySQL server on a Debian 10 LTS server

Open the terminal window and log in to your server using the ssh command and then update Debian system to apply security updates and fixes on Debian Linux system:
$ sudo apt update
$ sudo apt upgrade
Make sure you reboot Linux system when a brand new Linux kernel installed:
$ sudo shutdown -r now 'Going down for Debian kernel update'

Step 1 – Installing MySQL server

We are going to use MySQL Community Edition (note down file and md5 hash). It is the freely downloadable version of the world’s most popular open source database. First, we need to add the MySQL APT repository for Debian 10. Run:
$ wget https://dev.mysql.com/get/mysql-apt-config_0.8.16-1_all.deb
Verifying the MD5 checksum is super easy:
$ echo 'f6a7c41f04cc4fea7ade285092eea77a mysql-apt-config_0.8.16-1_all.deb' > file
$ md5sum -c file
However, signature checking using GnuPG is more secure. Next install the file named mysql-apt-config_0.8.16-1_all.deb using the apt command or dpkg command:
$ sudo apt install ./mysql-apt-config_0.8.16-1_all.deb
During the installation of the package, you will be asked to choose the versions of the MySQL server and other components as follows:

Which server version do you wish to use? I am going to stick with version 8.x:

Finally choose Ok. Next we update package information from the MySQL APT repository and then install MySQL version 8.0:
$ sudo apt update
$ sudo apt install mysql-server
During the installation, you are asked to supply a password for the root user for your MySQL system:

Step 2 – Securing MySQL server

We need to run program that enables us to improve the security of our MySQL server in the following ways:

1. Set a password for root accounts.
2. Remove root accounts that are accessible from outside the local host.
3. Delete anonymous-user accounts and the test database (which by default can be accessed by all users, even anonymous users), and privileges that permit anyone to access databases with names that start with test_.

Execute:
$ mysql_secure_installation

Step 3 – Controlling MySQL server at boot time

Enable and start the server when Debian 10 LTS restart:
$ sudo systemctl is-enabled mysql.service
If not enabled, type the following command to enable the server:
$ sudo systemctl enable mysql.service
Verify the server status by typing the following systemctl command:
$ sudo systemctl status mysql.service
We will see:

● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Sat 2020-11-07 15:44:00 UTC; 25min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 1860 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 1895 (mysqld)
   Status: "Server is operational"
    Tasks: 38 (limit: 4680)
   Memory: 329.3M
   CGroup: /system.slice/mysql.service
           └─1895 /usr/sbin/mysqld

Nov 07 15:44:00 debian-aws-mysql systemd[1]: Starting MySQL Community Server...
Nov 07 15:44:00 debian-aws-mysql systemd[1]: Started MySQL Community Server.

Step 4 – Starting and stopping the MySQL server

We can control the MySQL server on Debian Linux using the CLI: Let us start the server if not running:
$ sudo systemctl start mysql.service
Stop the MySQL server, enter:
$ sudo systemctl stop mysql.service
Restart the MySQL server as follows:
$ sudo systemctl restart mysql.service
We can view the MySQL service log as follows using the journalctl command:
$ sudo journalctl -u mysql.service -xe
$ sudo tail -f /var/log/mysql/error.log
Session:

2020-11-07T15:43:58.019666Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/tmp' in the path is accessible to all OS users. Consider choosing a different directory.
2020-11-07T15:43:58.049176Z 7 [System] [MY-013172] [Server] Received SHUTDOWN from user boot. Shutting down mysqld (Version: 8.0.22).
2020-11-07T15:43:59.625343Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.22)  MySQL Community Server - GPL.
2020-11-07T15:44:00.530816Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.22) starting as process 1895
2020-11-07T15:44:00.541857Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-11-07T15:44:00.795781Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-11-07T15:44:00.905407Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2020-11-07T15:44:00.976732Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-11-07T15:44:00.976916Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2020-11-07T15:44:00.999492Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.22'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.

Step 5 – Login into the MySQL server for testing purpose

So far, so good. We discovered how to install, set up, secure, and start/stop the Oracle MySQL server community edition version 8.x on Debian 10 Linux system. It is time to log in as a mysql admin (root) user. The syntax pretty simple too:
$ mysql -u root -p
$ mysql -u USER -h host -p
$ mysql -u USER -h HOST_NAME_IP_HERE -p DB_NAME

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

The STATUS command displays the version and other info at mysql> prompt:
STATUS;
And we see:

--------------
mysql  Ver 8.0.22 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:		12
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.0.22 MySQL Community Server - GPL
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/var/run/mysqld/mysqld.sock
Binary data as:		Hexadecimal
Uptime:			32 min 47 sec

Threads: 2  Questions: 17  Slow queries: 0  Opens: 128  Flush tables: 3  Open tables: 49  Queries per second avg: 0.008
--------------

We can see MySQL version as follows:
SHOW VARIABLES LIKE "%version%";
Again we get details:

+--------------------------+-------------------------------+
| Variable_name            | Value                         |
+--------------------------+-------------------------------+
| admin_tls_version        | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| immediate_server_version | 999999                        |
| innodb_version           | 8.0.22                        |
| original_server_version  | 999999                        |
| protocol_version         | 10                            |
| slave_type_conversions   |                               |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| version                  | 8.0.22                        |
| version_comment          | MySQL Community Server - GPL  |
| version_compile_machine  | x86_64                        |
| version_compile_os       | Linux                         |
| version_compile_zlib     | 1.2.11                        |
+--------------------------+-------------------------------+
12 rows in set (0.01 sec)

Step 6 – Creating a new MySQL database and user/password

Let create a new database called nixcraft, type the following SQL commands after you gain root mysql shell:
CREATE DATABASE nixcraft;
Next, we are going to create a new user named ‘vivekdada’ for our database called ‘nixcraft’ as follows:
# CREATE USER 'vivekdada'@'%' IDENTIFIED BY 'PASSWORD_HERE';
CREATE USER 'vivekdada'@'%' IDENTIFIED BY 'e3b0c44298fc1C_D8b7852b85';
Finally, give permissions:
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON nixcraft.* TO 'vivekdada'@'%';
Of course, I can grant ALL database level PRIVILEGES too:
GRANT ALL ON nixcraft.* TO 'vivekdada'@'%';
We can see MySQL user grants as follows:
SELECT user,host FROM mysql.user;
SHOW GRANTS for vivekdada;
Test it as follows:
$ mysql -u vivekdada -p nixcraft
$ mysql -u vivekdada -h localhost -p nixcraft

Where,

• -u vivekdada : User for login name
• -h localhost : Connect to host called ‘localhost’
• -p : Prompt for password
• nixcraft : Connect to database called ‘nixcraft’

Conclusion

Now we have Oracle MySQL community server version 8.x set up and running correctly on Debian Linux 10 LTS server. Further, you learned how to add a new database, user, and password for your project. Finally, we added an admin user for day-to-day MySQL server management tasks.

Category	List of Unix and Linux commands
File Management	cat
Firewall	Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04
Network Utilities	dig • host • ip • nmap
OpenVPN	CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04
Package Manager	apk • apt
Processes Management	bg • chroot • cron • disown • fg • jobs • killall • kill • pidof • pstree • pwdx • time
Searching	grep • whereis • which
User Information	groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w
WireGuard VPN	Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04