Install MySQL and Setup Remote Connection
Install MySQl in Ubuntu 14.04
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:
sudo mysql_install_db
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:
sudo mysql_secure_installation
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 UFW and Netstat
Check if your mysql server is listening on a socket with netstat:
netstat -tulpen
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
ufw status
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 Remote Access privileges
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%'IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Then restart mysql
/etc/init.d/mysql restart
Test it
Now test your remote connection from another client
mysql -h [ip] -u [username] -p