Install MySQL and Setup Remote Connection
apt-get install mysql-server
During the installation, your server will ask you to select and confirm a password for the MySQL “root” user. This is an administrative account in MySQL that has increased privileges. Think of it as being similar to the root account for the server itself (the one you are configuring now is a MySQL-specific account however).
When the installation is complete, we need to run some additional commands to get our MySQL environment set up securely.
First, we need to tell MySQL to create its database directory structure where it will store its information. You can do this by typing:
Afterwards, we want to run a simple security script that will remove some dangerous defaults and lock down access to our database system a little bit. Start the interactive script by running:
You will be asked to enter the password you set for the MySQL root account. Next, it will ask you if you want to change that password. If you are happy with your current password, type “n” for “no” at the prompt.
For the rest of the questions, you should simply hit the “ENTER” key through each prompt to accept the default values. This will remove some sample users and databases, disable remote root logins, and load these new rules so that MySQL immediately respects the changes we have made.
At this point, your database system is now set up and we can move on.
Check if your mysql server is listening on a socket with netstat:
and search for port number 3306. If not or if only on localhost, check
/etc/mysql/my.cnf and search the bind-address line and change it to:
bind-address = 0.0.0.0
Check your UFW configuration
If your don’t have the port 3306, for example like this:
Status: active To Action From -- ------ ---- 22 ALLOW Anywhere 80/tcp ALLOW Anywhere 3000/tcp ALLOW Anywhere 22 (v6) ALLOW Anywhere (v6) 80/tcp (v6) ALLOW Anywhere (v6) 3000/tcp (v6) ALLOW Anywhere (v6)
Add 3306 to the list by typing
ufw allow 3306/tcp
Now check ufw again, you will find 3306 in that list.
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%'IDENTIFIED BY 'mypassword' WITH GRANT OPTION; FLUSH PRIVILEGES;
Then restart mysql
Now test your remote connection from another client
mysql -h [ip] -u [username] -p