How to install MySQL 8.0 in Ubuntu 20.04

MySQL is a fast, efficient, secure, stable, easy-to-use, cross-platform (GNU / Linux, * BSD, Windows, …), multi-threaded, multi-user and well-documented relational database server. It is currently developed under the guidance of Oracle, however the community version is available under the GPL (a free software license).

MySQL 8.0 supports roles, which are named collections of privileges. Roles can be created and dropped also add the concept of user account categories and now maintains information about password history, enabling restrictions on reuse of previous passwords, for example a DB administrator can configure user accounts such that too many consecutive login failures due to incorrect passwords cause temporary account locking. Several improvements to the InnoDB engine and the JSON data type.

You can check: What Is New in MySQL 8.0 for more information

This post is part of: Install LAMPP Stack in Ubuntu 20.04

Install server and client

The mysql-server package contains all the necessary tools to run the MySQL database server, the mysql-client package is a CLI client that allows you to connect to the MySQL server and execute database queries and data base administration tasks.

$ sudo apt install mysql-server mysql-client
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following additional packages will be installed:
  libcgi-fast-perl libcgi-pm-perl libencode-locale-perl libevent-core-2.1-7 libfcgi-perl libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl
  liblwp-mediatypes-perl libmecab2 libtimedate-perl liburi-perl mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client-8.0 mysql-client-core-8.0 mysql-common mysql-server-8.0 mysql-server-core-8.0
Suggested packages:
  libdata-dump-perl libipc-sharedcache-perl libwww-perl mailx tinyca
The following NEW packages will be installed:
  libcgi-fast-perl libcgi-pm-perl libencode-locale-perl libevent-core-2.1-7 libfcgi-perl libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl
  liblwp-mediatypes-perl libmecab2 libtimedate-perl liburi-perl mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client mysql-client-8.0 mysql-client-core-8.0 mysql-common mysql-server mysql-server-8.0
  mysql-server-core-8.0
0 upgraded, 25 newly installed, 0 to remove and 0 not upgraded.
Need to get 30.6 MB of archives.
After this operation, 248 MB of additional disk space will be used.
Do you want to continue? [Y/n]

Manage the server process

In this part we will learn how to start, stop or restart the mysql service and we will use the systemd initialization system (systemd is a replacement for the SysV initialization system also a configuration and service management suite for the GNU/Linux operating system)

Check the status

Check if your DB server has started sucessfully by executing the following command

$ sudo systemctl status mysql
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2020-08-23 13:25:01 UTC; 47s ago
   Main PID: 14005 (mysqld)
     Status: "Server is operational"
      Tasks: 39 (limit: 1075)
     Memory: 324.2M
     CGroup: /system.slice/mysql.service
             └─14005 /usr/sbin/mysqld

Aug 23 13:25:00 ubuntu systemd[1]: Starting MySQL Community Server...
Aug 23 13:25:01 ubuntu systemd[1]: Started MySQL Community Server.

Start the server

If the previous command output the following information (note Active: inactive):

● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: inactive (dead) since Sun 2020-08-23 22:11:02 UTC; 3s ago
    Process: 15233 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
    Process: 15251 ExecStart=/usr/sbin/mysqld (code=exited, status=0/SUCCESS)
   Main PID: 15251 (code=exited, status=0/SUCCESS)
     Status: "Server shutdown complete"

Aug 23 21:57:21 ubuntu systemd[1]: Starting MySQL Community Server...
Aug 23 21:57:23 ubuntu systemd[1]: Started MySQL Community Server.
Aug 23 22:11:01 ubuntu systemd[1]: Stopping MySQL Community Server...
Aug 23 22:11:02 ubuntu systemd[1]: mysql.service: Succeeded.
Aug 23 22:11:02 ubuntu systemd[1]: Stopped MySQL Community Server.

then we can start the server with:

$ sudo systemctl start mysql

Restart the server

To apply any configuration file modification, you must restart the server with the following command:

$ sudo systemctl restart mysql

In Ubuntu we have the following directory structure for the configuration files.

/etc/mysql/
├── conf.d
│   ├── mysql.cnf
│   └── mysqldump.cnf
├── debian.cnf
├── debian-start
├── my.cnf -> /etc/alternatives/my.cnf
├── my.cnf.fallback
├── mysql.cnf
└── mysql.conf.d
    ├── mysql.cnf
    └── mysqld.cnf

Stop the server

To stop the DB server execute

$ sudo systemctl stop mysql

Start with the OS

You can verify that MySQL will start after a system reboot by running the following command:

$ sudo systemctl is-enabled mysql
enabled

The output of the above command is enabled or disabled, if it is disable run:

$ sudo systemctl enable mysql
Synchronizing state of mysql.service with SysV service script with /lib/systemd/systemd-sysv-install.
Executing: /lib/systemd/systemd-sysv-install enable mysql
Created symlink /etc/systemd/system/multi-user.target.wants/mysql.service → /lib/systemd/system/mysql.service.

If you want it not to start with the operating system run:

sudo systemctl disable mysql
Synchronizing state of mysql.service with SysV service script with /lib/systemd/systemd-sysv-install.
Executing: /lib/systemd/systemd-sysv-install disable mysql
Removed /etc/systemd/system/multi-user.target.wants/mysql.service.

Connect to the server

msyql is a a CLI client for MySQL administration, mysql allows::

Connect to the server
Create DB
Admin users
Execute queries and DB administration task.

Connect as root

The root user has all privileges over the MySQL server, to connecto to the server, execute:

$ sudo mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu)

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.

mysql>

If you don’t use sudo execute from root:

# mysql -u root

The # char means: authenticated as root user

Or from a standard user:

$ su -c 'mysql -u root'

Spanish Video

Administering MySQL from the command line, 2 (15)

Dennis

March 27, 2021 at 2:41 pm

Hi, thanks for the article.

I am setting up mysql. It is installed. I tried to use the bulk data load command and got a security error. After some research, I found the problem is that the data can only be loaded from
+——————+———————–+
| Variable_name | Value |
+——————+———————–+
| secure_file_priv | /var/lib/mysql-files/ |
+——————+———————–+

So the solution to my problem is to move the data file to a directory under value.

My question, under /var, where is a good place to locate bulk files so I don’t have to modify the secure variable?

Ubuntu terminal is not my native language, so I need help as far as the commands. So far my use of mysql is limited to one database and maybe three or four tables.

Thanks, Dennis

sedlav

March 28, 2021 at 4:07 pm

Hi /var/lib/mysql-files/ DIR was created by MYSQL installation process for that purpuse: load data from there so this a good places to put your files.