MySql 5.7 is an open-source free RDBMS for small and midsize data. This supports connectivity with many different programming languages and platforms via drivers. Om Linux system is part of the LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack.
In this post, I am writing methods to install MySQL 5.7 on Ubuntu 18 via the apt repository.
Simply you can install MySql using these three commands
$ sudo apt-get update $ sudo apt-get install mysql-server $ sudo mysql_secure_installation
Installing MySql
To install MySQL on Ubuntu 18, just update your apt repository first using
$ sudo apt-get update
After this On Ubuntu 18, only the latest version of MySQL is included in the APT package repository by default. At the time of writing, that’s MySQL 5.7. Now another version is not maintained by communities. You can try MariaDB instead of MySQL.
$ sudo apt-get install mysql-server
The above command will ask you sudo user password and some inputs and install MySQL 5.7 on your Ubuntu system.
Configuring MySql
On a fresh installation of MySQL, you need to initialize data directories and security settings of MySQL. But now installation comes with a script that automatically does all configurations for you.
$ sudo mysql_secure_installation
Using the above command it will ask to set up a root user password and other basic security settings. It will ask several questions and you need to select Y or other options as given below.
$ sheel@linux-18:~$ sudo mysql_secure_installation Securing the MySQL server deployment. Connecting to MySQL using a blank password. 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: 0 Please set the password for root here. New password: Re-enter new password: Estimated strength of the password: 100 Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y 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) : ... skipping. 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) : ... skipping. 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) : ... skipping. 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!
To initialize the data directory on Ubuntu you can use a scriptmysqld --initialize to initialize data directories.
Verify and Test MySql Installation
Verify MqSql installation on Ubuntu you can check MySQL service status.
$ sudo service mysql status
If MySQL service is running correctly then it should display output like below
● mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en Active: active (running) since Sun 2019-02-24 01:50:52 UTC; 29min ago Main PID: 16921 (mysqld) Tasks: 28 (limit: 1111) CGroup: /system.slice/mysql.service └─16921 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
After this, you can test MySQL connectivity
sudo mysql -u root -p
It will ask you root password and open you sql terminal. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.7.25-0ubuntu0.18.04.2 (Ubuntu) Copyright (c) 2000, 2019, 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. mysql>
Enable MySql Remote Connection on Ubuntu 18
For MySQL version 5.7 and above
uncomment in /etc/mysql/mysql.conf.d/mysqld.cnf and assigned to your computers IP address and not loopback
#Replace xxx with your IP Address bind-address = xxx.xxx.xxx.xxx
Or add a bind-address = 0.0.0.0 if you don’t want to specify the IP
Then stop and restart MySQL with the new entrymysqld.cnf.
$ sudo service mysql restart
Once running, go to the terminal and enter the following command.
$ netstat -nlt | grep 3306
sample result will like below :
tcp 0 0 xxx.xxx.xxx.xxx:3306 0.0.0.0:* LISTEN
Now create a user in MySQL with privileges to connect from a remote client
$ sudo mysql -u root -p
mysql> CREATE USER 'myuser'@'%' IDENTIFIED BY 'Password$4'; mysql> GRANT ALL ON *.* TO 'myuser'@'%' IDENTIFIED BY 'Password$4' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES; mysql> exit
Open MySql port 3306 on Ubuntu 18
Enable ufw and open ports on ufw
$ sudo enable ufw $ sudo ufw allow 22 $ sudo ufw allow 3306
Now test the connection from another remote client.
$ sudo mysql -u myuser -h <remote host ip> -p