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: Starting MySQL Community Server... Aug 23 13:25:01 ubuntu systemd: 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: Starting MySQL Community Server... Aug 23 21:57:23 ubuntu systemd: Started MySQL Community Server. Aug 23 22:11:01 ubuntu systemd: Stopping MySQL Community Server... Aug 23 22:11:02 ubuntu systemd: mysql.service: Succeeded. Aug 23 22:11:02 ubuntu systemd: 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'
Serie(s): Administering MySQL from the command line, 2 (14)
- How to install MySQL 5.7 on CentOS 7
- How to install MySQL 8.0 in Ubuntu 20.04
- MySQL 8.0, change root password
- How to install MySQL 8.0 in Debian
- How to install MariaDB on Alpine Linux
- MySQL server administration - Basic
- Create / modify / delete tables in MySQL
- MySQL user administration
- MySQL - Execute SQL script
- Disable innodb engine in MySQL
- Show MySQL storage engines
- Character set available in MySQL
- Connect to a MySQL server from the LAN
- MySQL can not write in the /tmp dir