sudo apt update
MySql 8 installation on Ubuntu 20
Upasana | November 19, 2020 | | 24 views
Please ensure tha you have sudo privileges in your ubuntu OS before proceeding with the rest of the article.
Update your system repositories
Now install MySQL 8 using the below command:
sudo apt install mysql-server
This command should be enough to install MySQL on your system.
To check the status of mysql service, run the below command:
sudo systemctl status mysql
Output
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Sat 2020-11-07 08:22:34 IST; 41min ago
Process: 144105 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 144133 (mysqld)
Status: "Server is operational"
Tasks: 38 (limit: 14007)
Memory: 336.4M
CGroup: /system.slice/mysql.service
└─144133 /usr/sbin/mysqld
Nov 07 08:22:34 e2e-72-183 systemd[1]: Starting MySQL Community Server...
Nov 07 08:22:34 e2e-72-183 systemd[1]: Started MySQL Community Server.Stop the server
sudo systemctl stop mysql
Start the server
sudo systemctl start mysql
Restart the server
sudo systemctl restart mysql
Secure server installation
sudo mysql_secure_installation
Adding user to database
Though we can use root user to connect the database, but it’s recommended to create a separate user (with least-privilege principle) for database access from application.
We can easily add a new user in MySQL by launching mysql shell and run the following commands:
sudo mysql
or, if you have enabled password access for root:
mysql -u root -p
Create a new user and grant access
USE mysql;
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'user'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;You can change the password for any existing user too:
ALTER USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
To change user’s host, run the below command:
RENAME USER 'user'@'localhost' TO 'user'@'remote_server_ip';
Alternatively, just create a new user account that will only connect from the remote host:
CREATE USER 'foo'@'remote_server_ip' IDENTIFIED BY 'password';
Normally we should give least-privilege to the user based on need, you can restrict privileges based on host, database and tables, as show below:
Grant permissions to all tables in database_foo:
GRANT ALL ON database_foo.* TO 'user'@'localhost';
Grant permissions to allow remote login from other hosts:
GRANT ALL ON database_foo.* TO 'user'@'%';
Grant permissions to a specific table only:
GRANT ALL ON database_foo.table_foo TO 'user'@'localhost';
Grant user permissions to all tables and databases from all hosts (except the grant permission):
GRANT ALL ON *.* TO 'user'@'%';
Creating a Super User with Grant option:
GRANT ALL ON *.* TO 'user'@'localhost' WITH GRANT OPTION;
To show grants for a given user, run the below command:
SHOW GRANTS FOR 'user'@'localhost';
Allow remote access
By default, MySQL installation does not allow connections from host other than localhost or 127.0.0.1, you need to make the modifications to the below configuration file to allow remote connections to MySQL:
/etc/mysql/mysql.conf.d/mysqld.cnf
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0Now just create a user that have grant options for remote access using the below command:
Create a new user and grant access for remote access
USE mysql;
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'remote_user'@'%' WITH GRANT OPTION; (1)
FLUSH PRIVILEGES;| 1 | Instead of specifying wildcard '%', you can provide a specific IP address which should be allowed for remote logins using this account. |
To create a super user with grant option, use the below command instead:
GRANT ALL ON *.* TO 'remote_user'@'%' WITH GRANT OPTION;Configure firewall
If Ubuntu firewall is turned on, it won’t allow you to make connections to the database without adding rule to its configuration.
It is recommended not to open port 3306 for everyone on internet, instead you should allow mysql connections for a given ip address:
sudo ufw allow from <remote-ip> to any port 3306
Check firewall status
sudo ufw status
Enable firewall
sudo ufw enable
Top articles in this category:
- Upgrade MySQL from 5.7 to 8 on Ubuntu 18.04
- Install RabbitMQ and Erlang 23 on Ubuntu 20
- Install Cassandra 4 on Ubuntu 20.04
- Install & configure Redis on Ubuntu
- Install ElasticSearch 7 on Ubuntu 20.04
- Install OpenJDK 11 on Ubuntu 18.04 LTS
- Upgrade Jenkins on Ubuntu 18.04 LTS
Recommended books for interview preparation:
Book you may be interested in..
Book you may be interested in..
