Setting up MYSQL for remote access

This setup of MYSQL would allow you to putty into a linux box where your MYSQL is installed and run SQL queries remotely. My development rig is within my firewall, so I basically connect to the same network wifi/network band.

Lets get started, first is you need to install openssh on your ubuntu linux box:

    sudo apt-get install openssh-server

This will take you through a wizard that will allow you to setup your openssh.

Now from your remote PC/laptop, test if you can putty into your linux box:

    putty <ip> with root/password

If ssh is denied, you need to setup root password and settings again on the sshd_config file:

    1. Test if you can ssh login with root credentials on localhost/linux box:

        ssh -v localhost (you should get an error that permission is denied because by default
                                   root login is disabled)

    2. Edit the sshd_config file:

        nano /etc/ssh/sshd_config

    3. Navigate to Authentication section, and edit PermitRootLogin

        PermitRootLogin yes

    4. Restart the ssh service

        sudo service ssh restart

    5. Test if a new session will be created when:

        ssh -v localhost (this should now succeed without errors and session is created)

    6. Try to remote-in to the linux box, this time via putty, and you should be able to login via ssh.

Next you need to configure your MYSQL conf file with the IP address of your linux box:

    nano /etc/mysql/mysql.conf.d
    edit the bind-address, as: bind-address  =  192.xx.xx.xx

Now you need to restart your mysql service:

    sudo service mysql restart

Next step is you need to configure root to have localhost and remote access to your mysql. Start mysql again using root:

    mysql -u root -p

Next is we need to create a root user for the following hosts:

    - localhost
    - %
    - 192.xx.xx.xx

Create root user for the hosts listed above:

    create user 'root'@'localhost' identified by password 'pword';
    create user 'root'@'192.xx.xx.xx' identified by password 'pword';
    create user 'root'@'%' identified by password 'pword';

Grant all privileges on all databases to the hosts above:

    grant all privileges on *.* to 'root'@'localhost';
    grant all privileges on *.* to 'root'@'192.xx.xx.xx';
    grant all privileges on *.* to 'root'@'%';

Now, we need to flush all privileges you just created for root:

    flush privileges;

To test the mysql remote, putty into your linux box from a remote PC via putty. Once logged in, connect to mysql via:

    mysq -u root -p

This should bring up a screen similar to below:

You have to note though that both your MYSQL instance installed on your ubuntu linux box and your PC/laptop should be connected to the same wifi network.

In the next article, we will connect IntelliJ with your MYSQL installation from this post.