In this article, We are going to Perform How to install MySQL 5.7 on Ubuntu 20.04 LTS using command line, creating new user and enabled remote access.
MySQL is the world’s most popular open source relational database management system. It is widely used with web server like apache2,Nginx,IIS,etc.
MySQL has client/Server architecture , supports InnoDB storage engine,can be installed on various operating system like Ubuntu,Debian,CentOS,Windows.,etc.
Step 1: Add MySQL APT Repository in Ubuntu
Ubuntu comes with default package repositories. So,if we want to add/install latest repositories then we have to add/install package repositories.
Update the System Packages
sudo apt update
Install wget on ubuntu if not installed
sudo apt install wget -y
Below are commands to add/download latest MySQL APT repository using command line,
sudo wget https://dev.mysql.com/get/mysql-apt-config_0.8.16-1_all.deb
then, below command is to install above downloaded apt repository,
sudo dpkg -i mysql-apt-config_0.8.16-1_all.deb
Select Ubuntu Bionic option and click on Ok.
By default it shows MySQL 8.0, Click on First option .
Select MySQL 5.7 server and click on OK.
Confirm that showing MySQL 5.7 on First option and Click on OK.
Step 2: Update MySQL Repository
Update apt repository
sudo apt-get update
Search MySQL 5.7 package using MySQL apt cache and select 5.7.30-1ubuntu18.04 to install
sudo apt-cache policy mysql-server
mysql-server: Installed: (none) Candidate: 8.0.22-0ubuntu0.20.04.3 Version table: 8.0.22-0ubuntu0.20.04.3 500 500 http://il.archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages 500 http://il.archive.ubuntu.com/ubuntu focal-security/main amd64 Packages 8.0.19-0ubuntu5 500 500 http://il.archive.ubuntu.com/ubuntu focal/main amd64 Packages 5.7.33-1ubuntu18.04 500 500 http://repo.mysql.com/apt/ubuntu bionic/mysql-5.7 amd64 Packages
Now install MySQL client 5.7.33 as output shown above
sudo apt install -f mysql-community-client=5.7.33-1ubuntu18.04
Step 3: How to Install MySQL 5.7 on Ubuntu 20.04 LTS
The following packages have unmet dependencies:
mysql-community-server : Depends: mysql-client (= 5.7.33-1ubuntu18.04) but it is not going to be installed
E: Unable to correct problems, you have held broken packages.
sudo apt install -f mysql-client=5.7.33-1ubuntu18.04
Install MySQL 5.7 on Ubuntu 20.04 LTS using below command,
sudo apt install -f mysql-community-server=5.7.33-1ubuntu18.04
Installation process will prompt default password for root user and again same password.
Install mysql-server=5.7.33 package also
sudo apt install -f mysql-server=5.7.33-1ubuntu18.04
Step 4: Secure MySQL Installation
MySQL Server comes with a script mysql_secure_installation this can do multiple security related operations,
Run the below script on command prompt, asks below options.
$ mysql_secure_installation Securing the MySQL server deployment. Enter password for user root: VALIDATE PASSWORD PLUGIN 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 plugin? 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: 1 Using existing password for root. Estimated strength of the password: 50 Change the password for root ? ((Press y|Y for Yes, any other key for No) : No ... skipping. 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!
Step 5: Login to MySQL
Now, Login to MySQL 5.7 Server using below command and use password to login entered during installation.
mysql -u root -p
mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.30 MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 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.
Now, We have successfully covered How to Install MySQL 5.7 on Ubuntu 20.04 LTS.
Step 6: Create MySQL Remote User
First, Login to MySQL Server with root user using command line, Below is command is to create user , here i am creating user “fosstechnix“.
mysql> CREATE USER 'fosstechnix'@'%' IDENTIFIED BY '[email protected]';
Next, assign the privileges to database with below command , here i am assigning all databases privileges to user fosstechnix,
If you want to assign privileges to specific database replace ” .” with database name.
mysql> GRANT ALL PRIVILEGES ON * . * TO 'fosstechnix'@'%';
if you want to allow “fosstechnix” user to give privileges to other user.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'fosstechnix'@'%' WITH GRANT OPTION;
To take effect reload the privileges using below command,
mysql> FLUSH PRIVILEGES;
Exit from MySQL prompt
Step 7: How to Enable MySQL Remote Access in Ubuntu 20.04
By default, In MySQL database server remote access is disabled for security reason.
To enable remote connections of MySQL Server, we have to change bind-address in MySQL configuration file.
Open the /etc/mysql/mysql.conf.d/mysqld.cnf file
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Below the [mysqld] section find the Line,
[mysqld] bind-address = 127.0.0.1
And replace it to
bind-address = 0.0.0.0
[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log # By default we only accept connections from localhost bind-address = 0.0.0.0 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0
Restart the MySQL Server to take effect.
sudo systemctl restart mysql
Step 8: Start/Restart/Stop MySQL Server Using Command Line
Below are commands to start/restart/stop MySQL 5.7 on Ubuntu 20.04 LTS
sudo systemctl start mysql sudo systemctl restart mysql sudo systemctl stop mysql
To check the status of mysql service on ubuntu
sudo systemctl status mysql
In this article, We have performed ,How to Install MySQL 5.7 on Ubuntu 20.04 LTS System, creating new user and enabled remote access.