Use the graphical tool MySQL WorkBench to remotely manage MySQL in Linux

Use the graphical tool MySQL WorkBench to remotely manage MySQL in Linux

If you run a MySQL server on a remote VPS, how do you manage your remote database host? Web-based database management tools such as phpMyAdmin or Adminer may be the first thing you think. These web-based management tools require a functioning backend web service and PHP engine. However, if your VPS is only used for database services (for example, the distributed structure where the database and other services are stored independently ), providing a complete set of LAMP for occasional database management is a waste of VPS resources. Worse, the HTTP port opened by LAMP may become a security vulnerability in your VPS resources.

As an option, you can run a local MySQL client on a client. Of course, if there is no other option, a pure MySQL command line client will be your default choice. However, the function of the command line client is limited because it does not have production-level database management functions, such as Visual SQL development, performance tuning, and mode verification. If you are looking for a mature MySQL management tool, a MySQL graphical management tool will better meet your needs.

 

What is MySQL Workbench?

As an integrated database management tool developed by Oracle, MySQL Workbench is not just a simple MySQL client. In short, Workbench is a graphical tool for database design, development, and management across platforms (such as Linux, MacOX, and Windows. The MySQL Workbench Community version follows the GPL protocol. As a Database Manager, you can use Workbench to configure MySQL services, manage MySQL users, back up and restore databases, and monitor database health, all are processed in a user-friendly graphical environment.

In this manual, let's demonstrate how to install and use MySQL Workbench in Linux.

 

Install MySQL Workbench on Linux

You can run MySQL Workbench on any Desktop linux machine to set up your database management environment. Although some Linux distributions (such as Debian/Ubuntu) already have MySQL Workbench in their software sources, installing it from official sources is a good method, because they provide the latest version. This section describes how to set up an official Workbench Software source and install it from it.

 

Debian-based desktop (Debia, Ubuntu, Mint ):

Go to the official site, select a DEB file that matches your environment, and download and install it:

For example, for Ubuntu 14.10:

1. $ wget http://dev.mysql.com/get/mysql-apt-config_0.3.4-2ubuntu14.10_all.deb
2. $ sudo dpkg -i mysql-apt-config_0.3.4-2ubuntu14.10_all.deb

For Debian 7:

1. $ wget http://dev.mysql.com/get/mysql-apt-config_0.3.3-1debian7_all.deb
2. $ sudo dpkg -i mysql-apt-config_0.3.3-1debian7_all.deb

When you install the DEB file, you will see the following configuration menu and select the MySQL product to be configured.

Select Utilities ". After the configuration is complete, select "Apply" to save the configuration. Then, update the package index and install Workbench.

1. $ sudo apt-get update
2. $ sudo apt-get install mysql-workbench

 

Red Hat-based desktop (CentOS, Fedora, RHEL ):

Go to the official website to download and install the RPM source package suitable for your Linux environment.

For example, for CentOS 7:

1. $ wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
2. $ sudo yum localinstall mysql-community-release-el7-5.noarch.rpm

For Fedora 21:

1. $ wget http://dev.mysql.com/get/mysql-community-release-fc21-6.noarch.rpm
2. $ sudo yum localinstall mysql-community-release-fc21-6.noarch.rpm

Verify that the "MySQL Tools Community" source is installed:

1. $ yum repolis enabled

Install Workbench

1. $ sudo yum install mysql-workbench-community

 

Set secure connections to remote databases

Next, set a remote connection for the VPS of your MySQL service. Of course, you can directly connect to your remote MySQL server through the graphical Workbench (after the remote connection is enabled for the database ). However, this poses a high security risk because some people may easily eavesdrop on your database to transmit information, and a public MySQL port (3306 by default) it will be another attack entry.

A better way is to disable the remote access to the database service (only access to 127.0.0.1 on VPS is allowed ). Then, set an SSH tunnel between the local client and the remote VPS. In this way, the data between the local client and MySQL can be replayed securely through its local loopback interface. Compared with setting an SSL encrypted connection, configuring an SSH tunnel requires very few operations, because it only requires the SSH service and has been deployed on most VPS.

Let's take a look at how to set up an SSH tunnel for a MySQL Workbench.

In this setting, you do not need to enable remote access to the MySQL service.

On a local client running Workbench, type the following command to replace 'user' (remote VPS username) and 'remote _ vps '(remote VPS address) your own information:

1. $ ssh user@remote_vps -L 3306:127.0.0.1:3306-N

You will be asked to enter your SSH password for VPS. After you successfully log on to VPS, an SSH tunnel will be established between the local port 3306 and the remote port 3306 of VPS. You will not see any information on the front-end.

Or you can choose to run the SSH tunnel in the background, press CTRL + Z to stop the current command, then ENTER bg and ENTER

In this way, the SSH tunnel will run in the background.

 

Use MySQL Workbench to remotely manage MySQL services

After creating an SSH tunnel, you can remotely connect to the MySQL service through MySQL Workbench.

Run the following command to start Workbench:

1. $ mysql-workbench

Click the "plus sign" icon on the Workbench page to create a new database connection. The following connection information is displayed.

• Connection Name: any description (for example, My remote VPS database)
• Hostname: 127.0.0.1
• Port: 3306
• Username: MySQL user name (for example, root)

Note: Because the tunnel is set to 127.0.0.1: 3306, the Host Name field must be 127.0.0.1, rather than the IP address or Host Name of the remote VPS.

After you set up a new database connection, you will see a new box in the Workbench window. Click that box to actually connect to the remote MySQL service.

After you log on to the MySQL server, you can view various management tasks in the left-side pane. Let's look at some common management tasks.

 

MySQL Server Status

This Menu displays the real-time monitoring panel that displays the resource usage of the database server. (For example, traffic, Link, read/write)

 

Client Connections

The number of client connections is an extremely important resource to monitor. This Menu displays the details of each connection.

 

Users and permissions

This menu allows you to manage MySQL users, including their resource restrictions and permissions.

 

MySQL Server Administration

You can start or stop the MySQL service and check its service logs.

 

Database Schema Management

You can visually view, change, and check the database structure, select any database or table under the "Schemas" title, and right-click

 

Database Query

You can execute any statement (as long as your permissions permit) and check its results.

In addition, performance statistics and reports are only used for Versions later than MySQL5.6. For versions 5.5 and earlier, the performance section is displayed in gray.

 

Conclusion

The brief and intuitive tab interface, rich features, and open source make MySQL Workbench a good visual database design and management tool. The performance is reduced. I have noticed that on a busy server, Workbench sometimes becomes abnormal and slow. Despite its poor performance, I still think that MySQL Workbench is one of the necessary tools for MySQL database administrators and designers.

Have you used Workbench in your production environment? Or do you have other GUI tools to recommend? Share your experience.

Install MySQL Workbench in Ubuntu

MySQL Workbench Chinese tutorial

MySQL Workbench "Error Code: 1175" SOLUTION

Major improvements to the GUI of MySQL Workbench 6

Install MySQL WorkBench in CentOS 7

MySQL Workbench details: click here
MySQL Workbench: click here

Via: http://xmodulo.com/remote-mysql-databases-gui-tool.html

Author: Dan Nanni Translator: tyzy313481929 Proofreader: wxy

This article was originally translated by LCTT and launched with the Linux honor in China

This article permanently updates the link address: