Workbench starting/stopping multiple instance set-ups with myenv

Table of Content

• Introduction
• Prerequisites
• What is MyEnv?
• MySQL Workbench configuration.
  • Add MySQL connections to MySQL Workbench.
  • Start/Stop instance configurations.

Introduction

MySQL Workbench is a very good and free GUI tool provided by Oracle to manage MySQL administration and development tasks. Opening many MySQL connections (same or different instances, remote or local MySQL servers) at the same time is one of its main features. While it's working fine to perform SQL statements on the different connections opened for multiple instances, but some people are asking if it is available as well to start and stop multiple MySQL instances using MySQL Workbench? if yes, how to configure it to perform such task? and also does that make any conflict with MyEnv tool - if it's installed - or not?
Yes, MySQL Workbench could be configured to start and stop multiple MySQL instances (local or remote) and it does not make any conflict with MyEnv tool.

In this article, I will describe how to configure MySQL Workbench to start and stop multiple MySQL instances and getting benefits from MyEnv scripts in this purpose.

Prerequisites

System information and installed packages:

• Operating System: Ubuntu 12.04 (64 bit) .
• MySQL Server: Any mysql version (I used MySQL 5.5 tarballs).
• Number of MySQL Instances: Two instances are installed (mysql1 & mysql2).
• MySQL Workbench: Version 6.0 .
• MyEnv: Version 1.0.1.

What is MyEnv?

MyEnv is a set of scripts to run comfortably multiple MySQL, Percona Server or MariaDB database instances on the same server. You can even run multiple database instances with different binary versions. If you have MySQL multiple instance setups, you really should try out MyEnv.

I will not talk more about MyEnv features and its benefits rather, I'd like to mention that if you're using MyEnv and want to use MySQL Workbench at the same time, you will not face any conflict between them both and you can manage your MySQL instances by either MyEnv or MySQL Workbench. More over, you can use MyEnv scripts to configure MySQL Workbench starting/stopping multiple instances in an easy way!

For more information about MyEnv tool , you can check it out on our website myenv.

MySQL Workbench configuration

Add MySQL connections to MySQL Workbench

• Choose a connection name for the 1st instance "mysql1" and specify the connection string:
  
  

  

  If you didn't adjust the "Configure Server Management" in this step - at the left bottom of the previous screen - you can open MySQL connections and perform SQL queries normally to this instance but you can neither edit the instance configuration parameters nor start/stop it.
  BTW, you can adjust it at anytime later and that what I did already in this example.

• Add another connection for the 2nd instance "mysql2" the same like "mysql1".

Start/Stop instance configurations

To configure MySQL Workbench to start/stop instance, we need to have relevant start and stop commands because it just execute them as they would be execute in the system shell. In this case, we may get benefit of MyEnv scripts for that purpose using the following command:

$MYENV_BASE/bin/database.php $MYENV_DATABASE start|stop

Now, we can use the following window to modify System type,Configuration file path, start , stop and status commands to match each instance configurations:

Now MySQL Workbench should be able to start and stop the configured MySQL instances.

Notes:

• MyEnv doesn't allow any user to start a mysql instance except mysql user (even if it's the root user), so that mysql OS user should be used to execute those commands and you might need to assign it SUDO permissions.
• Start/Stop Server button in MySQL Workbench depends on the output of the status command used ("cat /opt/mysql1/data/*.pid 2>/dev/null" for checking mysql1 instance status), and the button label will be changed to execute the appropriate command accordingly (for ex. if the instance is not running, the label should be changed to "Start Server" and the start command will be executed if the button clicked and vise verse), so you should make sure that this command is returning the expected results, otherwise, Workbench wont be able to manage the instance.
• It doesn't matter how MySQL was installed (RPM, tarballs or from source), it's the same concept, you just need to provide MyEnv start and stop commands along with the status command and then MySQL Workbench will work.
• If MyEnv is not installed, you can also configure MySQL Workbench to start/stop multiple instances by providing normal start|stop instance commands in the "Manage Server Connections" window but those need to be prepared first.