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.
Tutorial requirements | |
---|---|
Operating system/app | Debian Linux 10 LTS |
Root privileges required | Yes |
Difficulty | Easy (rss) |
Estimated completion time | 15m |
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
We will see something as follows:
mysql-apt-config_0.8.16-1_all.deb: OK
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:
- Set a password for root accounts.
- Remove root accounts that are accessible from outside the local host.
- 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:
$ sudo mysql_secure_installation
Improving MySQL server security
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 'STRONG_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.
🐧 6 comments so far... add one ↓
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 |
I don’t see any admin user added to the article. Am I missing something?
Run the following to create an admin user who can only login from 192.168.1.5 IP address with SSL:
You can replace 192.168.1.5 with sub/net such as 192.168.1.0/255.255.255.0. Make sure you create and use SSL certs.
Thank you. Can you tell me how to set MySQL 8.0 cluster on 3 VMs running on EC2 or any cloud service provider?
I will cover it sometimes later.
Can I manage the database through cPanel? How can I do this?
Thanks
Yes, you can. cPanel has option to manage and configure MySQL DBs. See https://docs.cpanel.net/whm/sql-services/