Installing MySQL on Ubuntu running on Elastic Compute Service instance

This article will cover how to install a MySQL server on an Alibaba Cloud ECS instance, create a database and remote into the database. We will be using tools including MySQL Workbench and Putty.exe to achieve this.

Installing MySQL on ECS instance

source – how to install MySQL on a Ubuntu instance

Configuring MySQL for remote access

The following allows you to expose MySQL to remote connections. Start by updating MySQL configuration to enable remote connections. Using Putty.exe remote into the ECS instance, find and edit the following configuration files:

/etc/mysql/my.cnf #(MySQL v5.6)
/etc/mysql/mysql.conf.d/mysqld.cnf #(MySQL v5.7)

Add the following line into the configuration file and comment out “skip-networking” and change the bind-address to either 0.0.0.0 or the IP of the machine you intend to remote into MySQL from. Save the changes and restart MySQL server using:

service mysqld restart

Note that by default when installing MySQL, remote access to MySQL server is turned off by “skip-networking” in the configuration file. (source)

Check the ECS instance setup by running the following command and you should see something like this:

lsof -i -P | grep :3306
mysqld  1046  mysql  10u  IPv4  5203  0t0  TCP  xxx.xxx.xxx.xxx:3306 (LISTEN)

netstat -an | grep :3306
TCP 0.0.0.0:3306 0.0.0.0:0 LISTENING

Once the ECS setup has been verified, open the MySQL command prompt to create a new remote user and grant permissions to the user:

mysql -u root -p (name of database if any)
GRANT ALL ON wordpress.* TO root@'client_public_ip' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
EXIT;

SELECT User, Host FROM mysql.user;
DROP USER 'testuser'@'localhost';

source – MySQL configuration for remote access
source – remote access to MySQL on Ubuntu

Lastly, we need to open 3066 port on Ubuntu ECS instance to allow traffic through to to MySQL server by modifying the iptables. The following command can be used to add firewall rules to allow 3066 traffic:

iptables --list
iptables -A INPUT -p tcp --dport 3306 -s xxx.xxx.xxx.xxx -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -j DROP

To test the connection from the remote server, you can either use MySQL Workbench or use the following command:

mysql -u webadmin -h xxx.xxx.xxx.xxx -p


Leave a Reply

Your email address will not be published. Required fields are marked *