How to create a MySQL 8 database user with remote access to all databases

At some point, you’re going to need to be able to connect to a MySQL 8 database remotely to manage your databases (either from the command line or a GUI). Jack Wallen shows you how to make this possible.

Image: Getty Images/iStockphoto/sdecoret

Recently I was tasked to add a MySQL database GUI for a client and came up against an issue where the database server wasn’t properly configured to accept remote connections. This can be a bit tricky to pull off, but it’s not impossible.

SEE: Hiring kit: Back-end Developer (TechRepublic Premium)

I’m going to walk you through the process of configuring MySQL 8 such that not only can you connect to it remotely, but you can also connect with a user that has full access to all databases. Understand, this can be considered a security issue for some instances, so you want to make absolutely certain that not only is your LAN secure but you’re using very strong passwords for the MySQL users (which you should be doing anyway).

With that said, let’s get this configuration up and running.

What you’ll need

To make this connection, you’ll need a running instance of MySQL and either a Linux machine to test the connection or any number of MySQL clients that allow for remote connection setup. You’ll also need a user on the MySQL server with sudo privileges.

That’s it. Let’s make some database magic.

How to configure MySQL for remote connections

The first thing we must do is configure MySQL for remote connections. To do this, log into your MySQL database server and open the configuration file with the command:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

In that file, look for the line:

bind-address = 127.0.0.1

Change that line to:

bind-address = 0.0.0.0

Save and close the file. Restart the MySQL service with:

sudo systemctl restart mysql

At this point, MySQL is open for remote connections, but you’ve yet to create a user for access.

How to create a user for remote access

Next, we must create a new MySQL user. We’re going to call this user root. Yes, there is already a root user, but that user is bound to localhost connections only. First, we access the MySQL console (from the server hosting MySQL) with the command:

sudo mysql -u root -p

Once at the MySQL console, create the new user and add the GRANT OPTION (which gives the user the ability to grant privileges to other users) with the command:

CREATE USER 'root'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Where PASSWORD is a very strong/unique password.

Next, we’ll grant the new user access to all the databases:

GRANT ALL PRIVILEGES ON *.* to 'root'@'%';

Flush the privileges and exit the console with:

FLUSH PRIVILEGES;
exit

How to test the connection

We can now test our connection. If you have access to another Linux machine with MySQL installed, you can test the connection by running the command (on the second machine):

mysql -u root -h SERVER -p

Where SERVER is the IP address or domain of the MySQL hosting server. When prompted for the password, type the strong password you created for the new root user. You should be granted access to the MySQL console, where you can manage any of the databases on the system.

Once the system tests out fine, you can then connect to that database server with a GUI tool like Beekeeper Studio. With Beekeeper studio, create a new connection, select MySQL as the connection type, and fill out the following details (Figure A):

  • Host–the IP address or domain of your MySQL server.
  • Port–3306
  • User–root
  • Password–the password you set for the newly created root user.
  • Default Database–type the name of one of your databases found on the MySQL server (which can be listed from the MySQL console with the query SHOW DATABASES;)

Figure A

The Beekeeper Studio database connection window.
The Beekeeper Studio database connection window.

Once you’ve filled out the details, click Test to make sure the connection works (it should). After getting the OK, give the connection a name and click Save. Finally, click Connect and the GUI should successfully connect to your remote database, where you can get to work (Figure B).

Figure B

Beekeeper Studio has successfully connected to a remote database.
Beekeeper Studio has successfully connected to a remote database.

Congratulations, not only have you configured MySQL 8 for remote connections, you’ve created a user with access to all databases, and connected to the remote server with both the command line and a GUI. Time to don your DB admin hat and get to work.

Subscribe to TechRepublic’s How To Make Tech Work on YouTube for all the latest tech advice for business pros from Jack Wallen.

For all the latest Technology News Click Here 

 For the latest news and updates, follow us on Google News

Read original article here

Denial of responsibility! TechNewsBoy.com is an automatic aggregator around the global media. All the content are available free on Internet. We have just arranged it in one platform for educational purpose only. In each content, the hyperlink to the primary source is specified. All trademarks belong to their rightful owners, all materials to their authors. If you are the owner of the content and do not want us to publish your materials on our website, please contact us by email – [email protected]. The content will be deleted within 24 hours.