Using JPA 2.1 With Wildfly To Access a MySQL Database On Ubuntu Server

  Java, JBoss, JPA, Tutorial, Wildfly

Installing MySQL And Create a Workbench Connection

Contents

Using MySQL on Ubuntu requires some installation and configuration. In order to comfortably administer your schemas and database objects, it is recommended to install the MySQL Workbench on your local (Windows) workstation. With the locally installed workbench program, you can connect to your MySQL instance on your Ubuntu server.

Download the MySQL Workbench from here.

On your Ubuntu server, you need to enter following commands:

$ sudo apt-get update $ sudo apt-get install mysql-server mysql-client

During installation, you’ll be asked for a password for the ‘root’ user. If you want to change the password that you’ve entered at a later time, enter:

$ mysqladmin -u root -p -h localhost password 'secret'

To check whether the server is up and running, enter the following command:

$ mysqladmin -u root -p -h localhost ping -p
mysqld is alive

To gain access to the Ubuntu instance of MySQL from your local Windows workstation, it is required that you enable remote access to it. To do that, edit the MySQL configuration file /etc/mysql/mysql.conf.d/mysql.cnf . Make sure the bind-address is set to 0.0.0.0:

bind-address=0.0.0.0

Next, you need to include your workstation in the user table of the mysql system database. To do so, we’ll create a new user who will be granted access from any workstation he tries to log in. First, launch the mysql command line interface by entering:

$ mysql -u root -p

Now, follow this sequence of commands on the mysql CLI:

mysql> USE mysql;
mysql> SELECT Host, User FROM user;
mysql> CREATE USER 'newuser'@'%' IDENTIFIED BY 'my_safe_password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

Since we modified the MySQL configuration file, we need to restart the MySQL service for the changes to become effective:

$ sudo service mysql restart

Remote access to the MySQL instance on the Ubuntu server should now be possible for user ‘newuser’. Just open the MySQL Workbench on your local Windows workstation and create a new connection to the Ubuntu server on port 3306.

LEAVE A COMMENT