0
0
This is a short guide on how to install and provide a basic configuration for MySQL with an initial user and database.
I’m providing the steps for a Ubuntu 20.04 but should not be too much different from any other distribution.
From the terminal run
apt search mysql-serverThe result should be something similar to this
$ apt search mysql-server
Sorting... Done
Full Text Search... Done
default-mysql-server/focal 1.0.5ubuntu2 all
MySQL database server binaries and system database setup (metapackage)
default-mysql-server-core/focal 1.0.5ubuntu2 all
MySQL database server binaries (metapackage)
mysql-server/focal-updates,focal-security 8.0.25-0ubuntu0.20.04.1 all
MySQL database server (metapackage depending on the latest version)
mysql-server-8.0/focal-updates,focal-security 8.0.25-0ubuntu0.20.04.1 amd64
MySQL database server binaries and system database setup
mysql-server-core-8.0/focal-updates,focal-security 8.0.25-0ubuntu0.20.04.1 amd64
MySQL database server binariesWe need to install the mysql-server-8.0
sudo apt install -y mysql-server-8.0Job done, all we need to do at this stage is to verify that is actually installed and run the initial configuration that will consists in:
- run the mysql_secure_installation
- create a database
- create a user and grant privileges
1 – run the mysql_secure_installation
from the command line type
sudo mysql_secure_installationYou will be asked a series of question and in particular
- Would you like to setup VALIDATE PASSWORD component? This is up to you, for my guide here I just type no
- New Password: here we are deciding which will be the MySQL root password (please don’t chose “password”, also if is a test)
- Remove anonymous users? (Press y|Y for Yes, any other key for No) – It’s up to you but usually is a Y
- Disallow root login remotely? (Press y|Y for Yes, any other key for No) – It depends on your configuration, usually you don’t want to allow root to authenticate remotely to your MySQL server, but if like in this case is a test and learning and a virtual machine in VirtualBox I would say why not
- Remove test database and access to it? (Press y|Y for Yes, any other key for No): pretty obvious to say Y
- Reload privilege tables now? (Press y|Y for Yes, any other key for No) : I would say Y
All done! Now we have our initial basic configuration and what is needed at this stage is the creation of an initial database and a user with read/write permission on that table(because we don’t want to give root permission to everyone).
2 – Create the MySQL user, no wait…let’s fix it first
Let’s create the user then, from the command line execute this command:
mysql -uroot -p<the password done at step "New Password above">ops we got an error like this
ERROR 1698 (28000): Access denied for user 'root'@'localhost'The reason is that is a new installation so we need to adjust few minor things before actually login using the command line. The root is set by default to authenticate using the auth_socket so we have 2 options
1 – is to change the authentication method
2 – bind the system user (the one you use for login to the shell of Ubuntu to be clear) to a db user
Option 1 – change the MySQL root authentication method:
sudo mysql -uroot Once logged in (please note that at this step we have to set the root password again)
USE mysql;
UPDATE user SET plugin='mysql_native_password' WHERE User='root';
ALTER user 'root'@'localhost' IDENTIFIED by '<your_password>';
FLUSH PRIVILEGES;
exit;Option 2 – bind the system user to a MySQL database user:
sudo mysql -uroot USE mysql;
CREATE USER 'your_system_user'@'localhost' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'your_system_user'@'localhost';
UPDATE user SET plugin='auth_socket' WHERE User='your_system_user';
FLUSH PRIVILEGES;
exit;The option you want to use is up to you, generally the MySQL server is not running on the machine you have your application or web app but is always (or in most cases) remotely accessible by a user and host defined. Something like the image below where User 1 and User 2 will be granted access to different databases tables with different permissions within the MySQL instance.
3 – Create the MySQL user
For creating the MySQL user just login to the mysql shell
mysql -uroot -p<your_password>and run
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;
exit;The commands above will create the “newuser” that actually has all the permission on all the database and tables (some sort of admin) that for now is enough for our testing.
Let’s login with the new created user
mysql -unewuser -pand create a database using the “create database” command
create database playground;
Query OK, 1 row affected (0.01 sec)with the “show database” command we can verify that playground is there
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| playground |
| sys |
+--------------------+Now we can create a simple test table and insert a record
use playground;
create table test_table(
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(100) NOT NULL,
creation_date DATETIME,
PRIMARY KEY ( id )
);Then we can add few records
insert into test_table(title, creation_date) values ("title_1", now()), ("title_2", now()), ("title_3", now());and we can select
mysql> select * from test_table;
+----+---------+---------------------+
| id | title | creation_date |
+----+---------+---------------------+
| 1 | title_1 | 2021-07-07 08:48:34 |
| 2 | title_2 | 2021-07-07 08:48:34 |
| 3 | title_3 | 2021-07-07 08:48:34 |
+----+---------+---------------------+
3 rows in set (0.00 sec)And this is the end of this small guide.
Share this content: