In this tutorial, we are going to show you how to install MySQL on your Ubuntu 20.04 and how to use it with the basic MySQL commands. MySQL is an open-source relational database management system and with its popularity is used widely on different systems for storing data.
In this post you will learn more about logging to MySQL with or without root user, database creation, user creation, granting privileges, external access to your databases, importing a database, making a dump of a database and etc. Let’s get started!
Table of Contents
1. Update the system
We assume that you have a fresh installation of Ubuntu 20.04 as OS and that is why we are going to update the system before we start with the installation.
sudo apt update -y && sudo apt upgrade -y
2. Install MySQL server
Install the MySQL database server with the following command:
sudo apt install mysql-server -y
After this command is executed the installation process of MySQL 8.0 will start. The MySQL 8.0 is included by default in the Ubuntu 20.04 repository. Once the installation is completed you can check if the service is up and running:
sudo systemctl status mysql
You should get the following output:
root@vps:~# systemctl status mysql
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Thu 2021-11-04 17:07:12 UTC; 39s ago
Main PID: 98088 (mysqld)
Status: "Server is operational"
Tasks: 38 (limit: 4617)
Memory: 355.4M
CGroup: /system.slice/mysql.service
└─98088 /usr/sbin/mysqld
3. Secure the MySQL server
Before we start using MySQL, we need to secure it with multiple steps with the following command:
sudo mysql_secure_installation
This command will take us through multiple steps which will help us to configure the security of our MySQL, set a strong root password and, etc. You should use the options with bold letters from the output below:
root@vps:~# sudo mysql_secure_installation Securing the MySQL server deployment. Connecting to MySQL using a blank password. VALIDATE PASSWORD COMPONENT can be used to test passwords and improve security. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD component? Press y|Y for Yes, any other key for No: Y There are three levels of password validation policy: LOW Length >= 8 MEDIUM Length >= 8, numeric, mixed case, and special characters STRONG Length >= 8, numeric, mixed case, special characters and dictionary file Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2 Please set the password for root here. New password: YourStrongRootPassword Re-enter new password:YourStrongRootPassword Estimated strength of the password: 100 Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y Success. Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y Success. By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y - Dropping test database... Success. - Removing privileges on test database... Success. Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y Success. All done!
The MySQL installation will be secured now and we can proceed with some useful commands in the next steps
4. Login to MySQL server with and without root password
After fresh installation of MySQL, you can log in to MySQL prompt by only typing the “mysql” command on the servers command line without MySQL to ask the root password that you set up in the previous step.
After typing the “mysql” you will be logged in the MySQL prompt.
root@vps:~# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu) Copyright (c) 2000, 2021, Oracle and/or its affiliates. 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>
The same will be if you execute the following command and hit enter without typing your password:
mysql -u root -p
To configure the MySQL to ask for root password execute the following command in the MySQL prompt:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourStrongRootPassword';
You should see the following input after executing
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourStrongRootPassword'; Query OK, 1 rows affected (0.01 sec) mysql
Now, if you try to login with “mysql” command you will get the message below:
root@vps:~# mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
You need to use the command below, with your previously set root password:
mysql -u root -p
It is your choice to decide if you want to use a password for root login or not. If you want to access without the password simply ignore this step and move on.
5. Database, User creation and Granting Privileges
If you list the databases in the MySQL prompt with command “show databases” you will get the default databases from MySQL:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec)
To create a database, execute the following command:
Create database 'admin';
Once, it is created you can list again the databases:
mysql> show databases; +--------------------+ | Database | +--------------------+ | admin | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql>
Let’s create a user named “adminuser” and grant privileges to the “admin” database with a password.
CREATE USER 'adminuser'@'localhost' IDENTIFIED by 'YourStrongPassword'; GRANT ALL PRIVILEGES ON admin* TO 'adminuser'@'localhost'; FLUSH PRIVILEGES; EXIT;
Once, the database user is created with privileges to the “admin” database you can easily check if everything is set up correctly:
mysql -u adminuser -p
You should see only the “admin” and the default “information_schema” databases:
mysql> show databases; +--------------------+ | Database | +--------------------+ | admin | | information_schema | +--------------------+ 2 rows in set (0.01 sec) mysql>
6. External access to User
To grant remote access to the user “adminuser” and our existing “admin” database execute the following command:
GRANT ALL ON admin.* TO adminuser@'remote_ip_address' IDENTIFIED BY 'YourStrongPassword';
If you want the user to connect from any IP address use the %:
GRANT ALL ON admin.* TO adminuser@'%' IDENTIFIED BY 'YourStrongPassword';
7. Make a dump of database
Making a dump of the database does not need login to MySQL prompt. It can be done through the server command line terminal using the name of the user, database, and password. We will make a dump of our “admin database with the root user:
mysqldump -u root -p admin > dump.sql
Once the dump is completed you can list to check if everything is ok
root@vps:/# mysqldump -u root -p admin > dump.sql Enter password: root@vps:/# ls -al total 12 drwxr-xr-x 2 root root 4096 Nov 4 21:38 . drwxr-xr-x 19 root root 4096 Oct 18 14:09 .. -rw-r--r-- 1 root root 1266 Nov 4 21:40 dump.sql
8. Import database dump
To import the dump of the database into the “admin” database with the root user you need to use the following command:
mysql -u root -p admin < dump.sql
This is the syntax for dumping and importing the database:
mysqldump -u "database user" -p "database name" > "name of dump file".sql mysql -u "database user" -p "database name" < "name of dumped database".sql
Please note, the database user must have privileges to the database that need to be dumped. The MySQL root user has privileges to dump and import all databases.
Congratulations, you managed to install and use the most important commands in the MySQL server
If you want to learn more MySQL commands feel free to visit their official documentation. Of course, if you find it difficult you can contact our 24/7 technical support, and our admins will install and configure the MySQL server on Ubuntu 20.04 for you.
If you liked this post on how to install and use MySQL on Ubuntu 20.04, please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks.