After a lot of searching and headache with that, I decide to create this little tutorial to help you setup and host a MySQL database on ubuntu machine without stress.
Install MySQL
Install the MySQL server by using the Ubuntu operating system package manager:
sudo apt-get update
sudo apt-get install mysql-server
The installer installs MySQL and all dependencies.
If the secure installation utility does not launch automatically after the installation completes, enter the following command:
sudo mysql_secure_installation utility
This utility prompts you to define the MySQL root password and other security-related options, including removing remote access to the root user and setting the root password.
Allow remote access
If you have iptables enabled and want to connect to the MySQL database from another machine, you must open a port in your server’s firewall (the default port is 3306). You don’t need to do this if the application that uses MySQL is running on the same server.
Run the following command to enable remote access to the MySQL server:
sudo ufw enable
and this command to allow remote access
sudo ufw allow mysql
Start the MySQL service
After the installation is complete, you can start the database service by running the following command. If the service is already started, a message informs you that the service is already running:
sudo systemctl start mysql
Launch at reboot
To ensure that the database server launches after a reboot, run the following command:
sudo systemctl enable mysql
Configure interfaces
MySQL, by default is no longer bound to ( listening on ) any remotely accessible interfaces. Edit the “bind-address” using this command
nano /etc/mysql/mysql.conf.d/mysqld.cnf
comment these lines on the file:
#bind-address = 0.0.0.0`
`#mysqlx-bind-address = 0.0.0.0
Restart the MySQL service.
sudo systemctl restart mysql
Start the MySQL shell and create your user to login remotely
/usr/bin/mysql -u root -p
Now you can run the script bellow to create your user with remote access enabled:
CREATE USER 'demouser'@'%' IDENTIFIED BY 'Demopassword2021#';
ALTER USER 'demouser'@'%' IDENTIFIED WITH mysql_native_password BY 'Demopassword2021#';
GRANT ALL on *.* TO 'demouser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Ps: The "%" means that user can connect from any IP. If you would like safer you can specify the you public IP.
for you exit from the MySQL terminal just type the command bellow and press enter:
exit
Restart the MySQL server
sudo systemctl start mysql
Now your server is done!
I recommend Workbench to connect on user machine.
If you have any question, text me on LinkedIn or enter in our community group by this link
Good Hacking!
Top comments (0)