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.

Installing and Re-installing MYSQL on UBUNTU Linux

Recently I've started to setup a Development environment at home that would have some technologies for my next project - Spring MAVEN with JPA and running on JBOSS connected to MYSQL and using IntelliJ IDE for development.

During this setup process, I had to go through install and reinstall process of MYSQL and a lot of online googling to find answers on how. Unfortunately, there weren't a lot of guides out on the internet helped me (although the instructions from digital ocean were pretty good) in a comprehensive/complete way.

A lot of the setup install/re-install steps were scattered over several articles, stack exchange, stack overflow, askubuntu resources. So, I started to document this process myself and this is what I came up with.

To start, I am just setting up MYSQL for root user, which would be the case since this Development rig is only for my home setup and is not meant for any production environment. The elevated access of root also makes it easier for setting up other stuff that I need for my project.

First step is to sudo as root together with the root user's environment:
      sudo -i

Once logged in as root, you need to install mysql:

      sudo apt-get install mysql-server mysql-client mysql-common dbconfig-mysql

If you have a previous mysql installation, you need to remove all instances of mysql:

      1. Backup any my.cnf fiels you have and copy to your personal folder:

             cp my.cnf /home/users/someacct/Documents/my.cnf.bak

      2. Remove mysql folder:

             rm -rf /etc/mysql

      2. Remove mysql:
           
             sudo apt-get remove --purge mysql-common mysql-client mysql-server dbconfig-mysql

      3. Remove packages and dependencies of mysql:

             sudo apt-get autoremove
             sudo apt-get autoclean

After installing mysql, install mysql-workbench (mysql GUI tool):

      sudo apt-get install mysql-workbench

Next run the mysql_secure_installation script to address security concerns in a default mysql installtion:

     sudo mysql_secure_installation

The previous script will go through a wizard that would ask about permissions related to root, allowing you to change the root password and if mysql can be accessed outside localhost.

Now, test your mysql installation with root:

    mysql -u root -p

This would take you to the mysql prompt where you can begin to to use mysql, you can test it by running the command:

    show databases;

This should show all the default databases (including mysql) that comes with your installation.

In my next post, I will go through the steps that I went through to setup mysql for remote access.