Remote Management
MySQL Workbench first introduced remote access via SSH (secure shell), a widely used and well known approach for secure remote access, especially in the Linux world. Microsoft Windows does not come with an in-built SSH server, hence an additional installation is due. For Windows users is SSH quite an unkown land and very often security rules, policies, company restrictions etc. do not allow to add extra software or open access via SSH. Also setting up SSH is non trivial and can be quite a challenge forless technical users. For these reason we have added support for native Windows management, which comes at no extra cost, since it is built into Windows already.
Native Windows Management
The management we will discuss here is not about how to add users or databases to a MySQL server and things like that.? It’s rather about? administering the MySQL server Instance that we’ll discuss here – for instance, DBA tasks such as manipulating the MySQL configuration file or controlling the server processes. Windows comes with a universal management layer called WMI (Windows Management Instrumentation). WMI is a very powerful means to query all kind of data from a Windows system (drivers, BIOS, motherboard, performance data etc.) and to manipulate the state of certain components (services, subsystems etc.). If you are going to manage a MySQL installation on a Windows server from a Windows machine (Workbench supports Windows 7) then WMI is the way to go. Due to security restrictions (e.g. UAC) WMI access works best in a domain setup as it already has all the necessary pieces to make the interplay work seamlessly. At the end of the article I have included additional information to help when setting up native Windows management for non-domain environments. However beware as that involves? disabling some important UACs, which is not advisable in most instances.
In a Windows domain you will need is a user login that has local administrator rights on the target machine MySQL server runs on. By default firewalls and access rules are typicallu set so that Workbench can connect without extra effort. WMI is used to query a server’s status and start or stop it as well as to get system information like CPU load and memory usage. Manipulating the MySQL configuration file is done by using Window’s normal file system functions, which means the target box must provide access to the file via a shared folder. By default Windows systems have a number of default shares that are always available (so-called administrative shares that give access to the entire hard disk, provided you know the administrator’s credentials for login). On the client side you can then use different possibilities to access the server by using:

A mapped drive, e.g. Z:\<path to file>\my.ini
An administrative share, e.g. \\<server>\C$\<path to file>\my.ini
An explicit share which gives access only to the file or its parent folder, e.g. \\<server>\<share>\my.ini.

This should be an old hat for most Windows users, though I wanted to point out the possibilities.
Setting up a Server Instance
Let’s get to the actual setup of a new server instance in MySQL Workbench. What you need is:

The target server’s name or IP address.
Name and password of a user which is a local administrator -? a member of the administors group -? on the target machine.
The name of the MySQL service to manage.
The path of the configuration file.

Workbench will try to help you with the last two points by giving you a list of MySQL servers it finds on the remote computer and their configuration file location. So it should mostly be point-and-click once you are logged in there.
Connect to the Target Machine
Open the New Server Instance Wizard from Workbench’s home screen. It comes up with the initial screen that allows you to specify the host machine of the MySQL server you want to manage thereby determining if it is a local or a remote connection. For the sake of simplicity let’s reuse a MySQL connection that has been created already in the SQL Development section. A server instance always needs such a connection. So either you reuse an already defined one or create a new one that is then added to the list of connections.

In this example I have used a Windows 2008 R2 server, which hosts the MySQL server. After selecting the connection proceed to the next page by clicking on the “Next” button. The wizard will do some initial tests and display the outcome. For a few more details (especially if something goes wrong) I recommend to open the log window using the “Show Logs” button on the wizard form.

After proceeding to the next page you will be asked to select which type of remote management you want to use. Here is where you will see the Windows Remote option.

Since we want native Windows management the option the choice is obvious. Proceeding to the next page causes the wizard to open a WMI connection to the target machine and retrieve a list of installed MySQL servers (whether running or not). Note that only the MySQL servers installed as Windows Service are recognized. In order to connect we need the credentials for a user on the target machine. As mentioned earlier, this user must be a local administrator.

Don’t get confused by the term “Service” in the login dialog. This is not the Windows service but a key that describes the given credentials. Together with the user name it defines a tupel to allow storage and retrieval of the password. Once the connection is established you can choose the server you want to manage. Selecting a server entry will automatically fill the configuration file path with a default value constructed from the server name, the administrative share C$ and the path of the file found in the service entry. Usually this is a good guess and should work most of the time.

Sometimes though things are a bit different and you can get an error when you switch to the next page.
In that case just go back to the previous page and open the file selector by clicking on the “…” button. A File Open Dialog will pop up that allows to select the file you need. This also works for remote machines, just type the machine name in the input box or use the network branch for selection. Sometimes directly accessing a share might pose problems so you may alternatively want to map a network drive in Explorer before you go on with the selection of the configuration file. For our demo the letter Z has been mapped to \\workbench.testing\C$.

After you have picked the correct file, continue to the next page which will again check if the file is accessible from MySQL Workbench. Note: only the last of the 3 tests will be performed as we already have a connection and hence don’t need to test that again. And as we are on Windows no test for any system command is required. They must be available anyway (otherwise the installation is probably broken).

Continuing, the wizard will bring up a small window asking you for a decision regarding if you just want to proceed or review what settings have been collected so far. Usually you can go on and finish the wizard after you gave your new server instance profile a proper name.

If you however want to review the details, e.g. because there is still an error at this point then click on the button “I’d like to review the settings again”. This will bring up a page that would otherwise just be skipped, which lists the collected details and allows you to opt to change them manually. This is btw. the only way to adjust the section in the configuration file that is managed by MySQL Workbench in this wizard. However, the Server Instance Manager allows to set this value as well. The default is “mysqld”.

If you checked the option “Change Parameters” you will get to a page that allows you to select the configuration file, and also let’s you test again if the file is accessible and if the given section exists in it.

Once you are satisfied with the result click “Next” to continue with the last wizard page, where you can specify a name for the new instance and finish the wizard.

Note: In the case something goes wrong you will see that the wizard does not force you to provide 100% correct details most of the time. In fact you can still continue and create the server instance even if currently no connection is possible or you do not have all relevant details. Of course without correct user credentials the wizard is not able to give you a list of installed services, so this is the minimum information, which must be correct.
What about Windows Workgroups?
In smaller and/or home networks there is typically no domain controller active, so the relationship between Windows boxes is a bit different and it is sometimes much more complicated to adjust all the necessary settings to allow this kind of access from one machine to another. Note: non-domain environments are not supported by MySQL Workbench due to various problems there.
However there are a number of web pages that discuss the use of WMI in such scenarios, which might help you to get it to work. A good overview what needs to be set can be found on the “Connecting to WMI Remotely Starting with Windows Vista” page. Most of the time you will find that these settings are already in place (except for the firewall exception), though you still get either of the most common errors:

“RPC Server not available”, which indicates the target WMI service cannot be reached at all, usually because of firewalls or wrong server name.
“Access denied” which is? the result of access-token-filtering even if an administrator is logged in.

Denied-access errors are usually caused by the UAC. Read here what Microsoft has to say about “User Account Control and WMI”. Also consider the following tips:

Add user with Administrator rights or set the proper rights for another user in “Computer Management/Services and Applications/WMI-Control -> context menu-> Security”
Firewall (“netsh firewall set service RemoteAdmin enable”)
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\ForceGuest must be 0.
A user connecting from a remote computer must have the SC_MANAGER_CONNECT privilege enabled to be able to enumerate services (admins usually have).

INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?);
INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?);
INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?);

While this works, there are two scalability limitations. First is the network overhead of the back and forth of each SQL statement, the second is the synchronous nature, that is your code can not continue until your INSERT is successfully completed.
The first improvement is to use MySQL’s multi values capability with INSERT. That is you can insert multiple rows with a single INSERT statement. For example:

INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?), VALUES (?, ?, ?), (?, ?, ?);

Depending on how you collect the information to be inserted, you may be able to easily implement this. The benefit, as previously mentioned in The value of multi insert values shows an 88% improvement in performance.
One system variable to be aware of is max_allowed_packet. You may have to adjust this for larger INSERT statements.
Next is the ability to INSERT data based on information already in another table. You can also leverage for example another storage engine like MEMORY to batch up data to be inserted via this approach.

INSERT INTO table (col1, col2, col3) SELECT col1, col2, col3 FROM anothertable

The third option is to batch load your data from a flat file. Depending on how you source the information you are inserting, that may also be a significant improvement in throughput in bulk inserting data.

LOAD DATA [CONCURRENT] INFILE ‘file’
INTO TABLE (col1, col2, col3)

Open source has gone a long way toward putting power back in the hands of developers, who can download, install and deploy software without having to go through any kind of?convoluted sales or budget approval process. ?You want?MySQL? ?You can download and install?in?15 minutes, and you don?t have to talk to anyone to do it.
Software as a service (SaaS) takes this to an even broader audience, enabling employees to get the kind of lightweight, consumer, self-serve capabilities in their job without even having to run their own servers. ?Platforms like?Amazon AWS, Heroku, Makara, RightScale?and others put this same kind of SaaS power in the hands of developers…
My view: ease of use trumps a long feature list any day of the week. There are both techological reasons as well as sociological and economic reasons for why organizations are seeking greater simplicity. ?Part of this stems from the fact that complex enterprise applications grew beyond the ability of most organizations to successfully adopt. ?

Bookmark It

Hide Sites

MASTER=()
CURRENT=()
FIRST=1
for SERVER in ${SERVERS}; do
# collect all builds from server and populate CURRENT list
COMMAND=”${LS} -1fd ${WEBROOT}/${SITE}.*”
BUILDS=`${SSH} ${SSHOPTS} root@${SERVER} “${COMMAND}”`
for BUILD in ${BUILDS}; do
CURRENT=( ${CURRENT[@]-} ${BUILD} )
done

# if this is our first time around, copy CURRENT to MASTER
if [ ${FIRST} -eq 1 ]; then
MASTER=( ${CURRENT[@]} )
FIRST=0
fi

# now we do a compare between MASTER and CURRENT to see what builds
# are common
INTERSECT=()
for ENTRY in ${CURRENT[@]}; do
in_array “${ENTRY}” “${MASTER[@]}”
RET=$?
if [ "${RET}" -eq 0 ]; then
INTERSECT=( ${INTERSECT[@]-} ${ENTRY} )
fi
done
MASTER=( ${INTERSECT[@]} )

# clear the CURRENT array
CURRENT=()
done
Let me take a moment to explain the code above:

In order to check for array intersection, you need an in_array()
function

The first argument as the “needle” and the second is the
“haystack”
We verify that both parameters were passed
We simply loop through the haystack checking for the needle
If we find it, return success. Otherwise, eventually return
false

We need to loop through each server eventually, but we’ll start with
the first one

Run an SSH command to get a listing of builds
Populate an array ($CURRENT) with the builds that were found

Since the first server has no previous server to compare with, so we
just copy it to $MASTER

We then loop to the 2nd server, and put the result of getting builds
into $CURRENT

Now that we have the first server’s builds in $MASTER, we perform an
intersect with $CURRENT
We realize the need for an $INTERSECT array to hold the intersections
found above
$INTERSECT becomes $MASTER since it only contains similar builds from
the 1st and 2nd server

Looping to the 3rd server, we get the builds and put them in $CURRENT

Since $MASTER contains only the similar builds thus far, we again
compare it with $CURRENT
The intersect can now be used to compare against builds on the 4th
server, and so on

Once you finish looping through all servers, your $MASTER should
contain only similar builds