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.
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 similar to the below:
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';
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