Install and set up MySQL
The first thing we do is install MySQL Server:
__$ sudo apt install -y mysql-server
MySQL provides us with a procedure to set some security settings right after installation. This can be started with the command mysql_secure_installation:
__$ sudo mysql_secure_installation
The following settings are queried:
- VALIDATE PASSWORD COMPONENT: no (force strong passwords?)
- Password for root: ***
- Password repeat: ***
- Remove anonymous users?: y
- Disallow root login remotely?: y
- Remove test database and access to it?: y
- Reload privilege tables now?: y
The strict password regulation demands very complex passwords. I make it easy for myself at this point and choose no. The most important setting is probably remote access, which I disable. In the terminal it looks like this:
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: n
Please set the password for root here.
New password:
Re-enter new password:
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!
We can retrieve the version with --version:
__$ mysql --version
Create MySQL user and database
The MySQL user root should be used for administrative purposes only.
We create a new database user tom and a database MyDatabase to which tom should have access.
First we start the MySQL console with sudo mysql and immediately specify the MySQL root user with the parameter -u:
__$ sudo mysql -u root
MySQL console:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 37
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>
Create a new database user tom that can only act locally and not remotely. The password should be tom123, which is set with IDENTIFIED BY:
__gt CREATE USER 'tom'@'localhost' IDENTIFIED BY 'tom123';
Create a new database MyDatabase:
__gt CREATE DATABASE MyDatabase;
Grant the user tom access rights to the previously created database:
__gt GRANT ALL PRIVILEGES ON tom.* TO 'tom'@'localhost';
To make sure that changes are applied, let's re-read the new privileges with FLUSH PRIVILEGES:
__gt FLUSH PRIVILEGES;
We can leave the MySQL console again with exit:
__gt exit
Uninstall MySQL
How to uninstall MySQL is described here only for the sake of completeness.
Stop MySQL:
__$ sudo systemctl stop mysql
Uninstall MySQL:
__$ sudo apt purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*
__$ sudo apt autoremove
__$ sudo apt autoclean
Delete MySQL data:
__$ sudo rm -rf /etc/mysql /var/lib/mysql /var/log/mysql
Common MySQL commands
Start MySQL:
__$ sudo systemctl start mysql
Stop MySQL:
__$ sudo systemctl stop mysql
Restart MySQL:
__$ sudo systemctl restart mysql
MySQL status:
__$ sudo systemctl status mysql
MySQL console via command line
Start MySQL console:
__$ sudo mysql
The MySQL Shell:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
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>
Show all users:
__gt SELECT user from mysql.user;
Show all databases:
__gt SHOW DATABASES;
Create a new database myDatabase.:
__gt CREATE DATABASE myDatabase;
Switch to the myDatabase database:
__gt USE myDatabase;
Create a new table Users.:
__gt CREATE TABLE Users (
id INT AUTO_INCREMENT PRIMARY KEY,
create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
username VARCHAR(50) NOT NULL,
email TINYTEXT NOT NULL,
age SMALLINT,
bio TEXT
);
List tables:
__gt SHOW TABLES;
Insert a record:
__gt INSERT INTO Users (username, email, age) VALUES ('tom', 'tom@linuxservrsetup.com', 80);
List the entire contents of the Users table.:
__gt SELECT * FROM Users;
Delete record with ID 1:
__gt DELETE FROM Users WHERE id = 1;
Delete Users table:
__gt DROP TABLE Users;
Delete myDatabase database:
__gt DROP DATABASE myDatabase;
Delete user userA:
__gt DROP USER 'userA'@'localhost';
Exit the MySQL console:
__gt exit;
Single line command: Create MySQL user jerry with access to database of the same name (password: jerry123):
__$ echo "CREATE USER 'jerry'@'localhost' IDENTIFIED BY 'jerry123';GRANT ALL PRIVILEGES ON jerry.* TO 'jerry'@'localhost'; FLUSH PRIVILEGES; CREATE DATABASE jerry;" | sudo mysql