Install and Configure MySQL on linux, macOS and windows machines
MySQL is one of the most popular open-source, cross-platform and performant relational database management system (RDMS). It is used by a large numbers of websites all over the world to store and manage data. It's original creator is Michael Widenius, but currently it's backed by Oracle. It is based on Structure Query Language (SQL). SQL is a domain-specific language used in programming and managing of data stored in a RDMS. It organizes data into one or many tables in which the data may or may not be related to each other.
Please refer to the following link to see all the available download options for a wide range of operating systems.
https://dev.mysql.com/downloads
Installation of MySQL in Ubuntu
Let's update all the packages to their latest available versions and also remove all the obsolete packages:
sudo apt-get update -y && sudo apt-get upgrade -y
We need to add MySQL APT repository to our system's software repository list. To get the latest version of MySQL APT repository, navigate to the following url:
https://dev.mysql.com/downloads/repo/apt/
wget https://dev.mysql.com/get/mysql-apt-config_0.8.23-1_all.deb
It's a good practice to verify the integrity of the downloaded package using following command:
md5sum mysql-apt-config_0.8.23-1_all.deb
Output for above check will be as follows:
c2b410031867dc7c966ca5b1aa0c72aa mysql-apt-config_0.8.23-1_all.deb
If the above hash matches with the hash from the downloads page for the APT repository, then the integrity is verified.
Let's install the above downloaded release package using following command:
sudo dpkg -i mysql-apt-config_0.8.23-1_all.deb
dpkg command is used to interact with .deb software packages.
Since we want to install the latest version of MySQL server, we can just select fourth option Ok. Click on Down Arrow key till the fourth option OK is selected. Press tab key and then press enter key:
After this, we need to update all the packages to their latest available versions in APT package repository using following command:
sudo apt-get update -y
It's now time to install MySQL server on the machine:
sudo apt-get install mysql-server -y
It will prompt for the root password, enter password and then confirm it. To create a strong password, it should have following characteristics:
- At least 1 uppercase letter
- At least 1 lowercase letter
- At least 1 number
- At least 1 special character
- At least 8 characters long
- Do not use any well know words, make the password as unique as possible so that dictionary attacks could be prevented
Press tab key to select Ok button and then press enter key.
For security purpose, select Use Strong Password Encryption option and press tab key to select Ok and then press enter key.
Now that the MySQL server is installed, check the version of it:
mysql --version
For successful installation, version information will look like below:
mysql Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)
To start MySQL server, issue following command:
sudo systemctl start mysql
To restart MySQL server, issue following command:
sudo systemctl restart mysql
To stop MySQL server, issue following command:
sudo systemctl stop mysql
To check the status of MySQL server, issue following command:
sudo systemctl status mysql
To make sure that MySQL server starts after each system reboot, issue following command:
sudo systemctl enable mysql
MySQL server comes up with some default configurations which we need to modify to make our database server even more secured. To do that, we can issue following script command:
mysql_secure_installation
It will ask for the root password. Type the password and press enter key. After that, it will ask us a series of questions as following:
1> 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?
Type y and press enter key
2> 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:
Type 2 and press enter key
3> Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) :
Since the password strength is already 100, i will type n
If the password strength for you is much lesser than 100, type y and enter new strong password.
4> 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):
Type y and press enter key
5> 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):
Type y and press enter key
6> 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) :
Type y and press enter key
7> 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) :
Type y and press enter key
Finally, MySQL server is installed and fully configured as well. Now, we can start using this database server from our backend applications. If you are using Ubuntu machine, skip installation for windows and macOS and navigate to the Connecting to MySQL server section.
Installation of MySQL in Windows Machine
Click on the following link to download the latest version of MySQL installer and double click on it to start the installation process.
https://dev.mysql.com/downloads/installer/
You can also click on the following link:
https://dev.mysql.com/get/Downloads/MySQLInstaller/mysql-installer-community-8.0.30.0.msi
Installation of MySQL in macOS machine
In macOS, we can install mysql server using either brew or the installer package.
Run the following command to upgrade all the installed packages to their latest available versions:
brew update && brew upgrade
To install using installer package, click on the following link:
https://dev.mysql.com/downloads/mysql/
Select macOS as the operating system and also the associated OS version.
Download the appropriate .dmg file and double click on it.
Once installed, go to the System Preferences screen
At the bottom of the below image, you will see MySQL icon. Click on that icon.
You can start MySQL server by clicking on the Start MySQL Server button.
To install using brew, issue the following command:
brew install mysql
https://formulae.brew.sh/formula/mysql
Connecting to MySQL server
To connect with MySQL server, we can use following command:
mysql -h SERVER_HOST -P SERVER_PORT -u USER_NAME -p
Here:
- SERVER_HOST can either be a localhost if we are interacting from the local machines or the IP address if we are interacting from different machines.
- SERVER_PORT is by default 3306. If you have started MySQL server with different port, specify that port.
- USER_NAME is the username of the user which has access to interact with the MySQL server.
It looks like below if we are interacting from the local machine:
mysql -h localhost -P 3306 -u root -p
It will prompt for the password of the root user that we set earlier, Type the password and press enter key.
To see the list of databases, issue following command:
SHOW DATABASES;
To select a database:
USE mysql;
To list the tables in a mysql database:
SHOW TABLES;
MySQL creates a root user by default to perform administrative tasks. For security purpose, it's best to use the root user credentials as few times as possible. Let's create a new user and grant it all the privileges. Once that new user is created, lock the root user credentials in some secure place.
Following is the syntax for creating a new user:
CREATE USER 'USER_NAME'@'HOST_NAME' IDENTIFIED BY 'PASSWORD';
Here:
- Replace USER_NAME with a meaningful username
- Replace PASSWORD with a very strong password adhering to above discussed password policies.
- HOST_NAME is the name of the host from which, the user will connect to the MySQL server
If you want a user to access MySQL server only from the same machine, use localhost as HOST_NAME.
If you want a user to access MySQL server from some particular machines, use IP address or IP CIDR notation as HOST_NAME.
If you want a user to access MySQL server from anywhere, specify % as HOST_NAME.
To create a user having all the privileges to interact with all the databases in this MySQL server, issue following command:
CREATE USER 'administrator'@'localhost' IDENTIFIED BY 'Am#202Ad2NpK';
If this user is compromised, we can easily delete this user and create a new one using root user credentials. Grant all the privileges to all the databases to above user using WITH GRANT OPTION keyword. Take extreme precaution when using this keyword, as it gives superuser privileges access.
GRANT ALL PRIVILEGES ON *.* TO 'administrator'@'localhost' WITH GRANT OPTION;
It's better to reload all the privileges from the grants table in the mysql system schema after creating a new user.
FLUSH PRIVILEGES;
Now, use the above credentials to connect to the MySQL server and perform all other tasks:
mysql -h localhost -P 3306 -u administrator -p
Create a new database for our travel application and name it as travel_app.
CREATE DATABASE travel_app;
To interact with travel_app db, let's create some new users. First, create a new user called writer, which will have all the privileges to perform CRUD operations in a database.
Note:
We should always follow the Principle of Least Privileges. It states "a user should only have the minimum access privileges/permissions necessary to perform his/her tasks. Nothing more.
CREATE USER 'writer'@'localhost' IDENTIFIED BY 'Tr#2022NpK';
If you want to access travel_app db from any other specific machines, you need to replace the localhost with the ip address of that machine.
CREATE USER 'writer'@'172.26.5.60' IDENTIFIED BY 'Tr#2022NpK';
If you want to access travel_app db from any machines, you need to replace the localhost with the % sign.
CREATE USER 'writer'@'%' IDENTIFIED BY 'Tr#2022NpK';
Also, create another user called reader which will have read only access to the database.
CREATE USER 'reader'@'localhost' IDENTIFIED BY 'R#22N20pK';
Now we need to grant privileges to both the users. While granting privileges, we should always follow the Principle of Least Privileges.
Syntax:
GRANT PRIVILEGES ON DB_NAME.TABLE_NAME TO 'USER_NAME'@'HOST';
Here:
- PRIVILEGES can be any of the following
-> ALL PRIVILEGES for all the privileges (SELECT, INSERT, UPDATE, DELETE and many others)
-> SELECT for reading data
-> INSERT for creating data
-> UPDATE for updating data
-> DELETE for removing data
- DB_NAME - replace it with name of the database. If we want to grant privileges to all the databases - we need to replace it with *
- TABLE_NAME - replace it with name of the table. If we want to grant privileges to all the tables - we need to replace it with *
- USER_NAME - replace it with the username
- HOST - replace it with the host name used while creating a user
GRANT SELECT, INSERT, UPDATE, DELETE ON travel_app.* TO 'writer'@'localhost';
GRANT SELECT ON travel_app.* TO 'reader'@'localhost';
If we need to grant the privileges to a specific table of a particular database:
GRANT SELECT, INSERT, UPDATE, DELETE ON travel_app.TABLE_NAME TO 'writer'@'localhost';
If we want to grant all the privileges to a certain user to a specific database:
GRANT ALL PRIVILEGES ON travel_app.* TO 'writer'@'localhost';
To list all the users in MySQL:
SELECT user, host FROM mysql.user;
If we want to see the list of privileges granted to a user:
SHOW GRANTS FOR 'writer'@'localhost';
SHOW GRANTS FOR 'reader'@'localhost';
We need to reload all the privileges from the grants table in the mysql system schema so that all the recent changes are reflected immediately.
FLUSH PRIVILEGES;
If we want to revoke certain privileges from a user:
REVOKE DELETE ON travel_app.* FROM 'writer'@'localhost';
Now, it's time to connect with the travel_app database using those new users. Let's connect with writer user. This user will only have access to travel_app database.
mysql -h localhost -P 3306 -u writer -p
If you connect with the travel_app database using reader user and try to perform operations other than SELECT query, it will throw permission error:
mysql -h localhost -P 3306 -u reader -p
To check for the current user:
SELECT CURRENT_USER();
To drop user:
DROP USER USER_NAME@HOST_NAME
Here:
- USER_NAME is the username of the existing user
- HOST_NAME is the name of the host used while creating user.
To drop a user, we need to connect to MySQL server with above superuser credentials (administrator).
DROP USER reader@localhost;
In our next chapter, we will discuss about the database schema for our travel application.