In this tutorial, we are going to install MySQL version 5.7 using the Debian package(.deb)
Step 1. Add the MySQL Apt Repository
First download the Debian(.deb) package from MySQL Apt Repo. Here we are using the wget to download as below:
wget https://dev.mysql.com/get/mysql-apt-config_0.8.12-1_all.deb
Step 2. Configure the .deb file
sudo dpkg -i mysql-apt-config_0.8.12-1_all.deb
You can see the below prompt screen
If somehow we are unable to see the prompt screen then either dpkg is interrupted or broken. Use the below command to clean the config after that use the previous dpkg command, now we can see the screen
apt-get purge mysql-apt-config
Select the ubuntu bionic option and the second screen will pop up as below. Now select the first option to select the MySQL version
Now select the desired version of MySQL, Here we are selecting the MySQL 5.7
Once selected the desired version of MySQL then select ok to configure.
Now the configure suppose to get successful and we can see the output as below:
Selecting previously unselected package mysql-apt-config.
(Reading database ... 36242 files and directories currently installed.)
Preparing to unpack mysql-apt-config_0.8.12-1_all.deb ...
Unpacking mysql-apt-config (0.8.12-1) ...
Setting up mysql-apt-config (0.8.12-1) ...
Warning: apt-key should not be used in scripts (called from postinst maintainerscript of the package mysql-apt-config)
OK
Step 3. Install the MySQL Server
Now update the MySQL repository
sudo apt-get update
Install the MySQL using the following command:
sudo apt install -f mysql-client=5.7* mysql-community-server=5.7* mysql-server=5.7*
If you chose the latest version of MySQL then no need to specify the package.
Next, the screen will pop up to enter the root password. Enter the password and press ok. After that MySQL is installed and running successfully. Test the MySQL version and MySQL status by using the below command:
mysql --version
sudo systemctl status mysql
Step 4. Secure MySQL Installation
Use the following command to secure MySQL installation:
sudo mysql_secure_installation
Enter your MySQL root password and answer all of the security questions.
Now, log in using the root credential
mysql -u root -p
Step 5. Create a New Mysql User
Before creating new users we need to first log in with the root user as mentioned above. Now, create the user using the following command:
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
Here, use your own username and password and localhost as we are working on the same machine.
For example:
CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'testpassword';
If we want to create a user that can be connected from any machine then use the following command
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
Now, let's grant the permission to the newly created user.
GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost';
Here, user have full access to the database with ALL PRIVILEGES and for all the databases as we are using *.*
However, we can grant privileges for specific databases as
GRANT ALL PRIVILEGES ON database_name.* TO 'testuser'@'localhost';
After grant privileges to user, we need to flush the privileges
FLUSH PRIVILEGES;
We can revoke privileges using the following command:
GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost';
Remove User:
DROP USER 'testuser'@'localhost';
Step 6: Some commands for MySQL server:
Stop the MySQL server:
sudo systemctl stop mysql
Start the MySQL server:
sudo systemctl start mysql
Check the status of MySQL server:
sudo systemctl status mysql
Restart MySQL server
sudo systemctl restart mysql
Step 7: Completly remove/uninstall MySQL server
sudo apt-get remove --purge mysql*
sudo apt-get autoremove
sudo apt-get autoclean