MySQL

Run MySQL Server as a Windows Service with FireDaemon Pro


MySQL is a relational database management system. The server can be run as a Windows service using FireDaemon Pro, which allows you to have the server start automatically at boot prior to login, start multiple instances of the server and more. You can also use FireDaemon Fusion to manage FireDaemon and other Windows services via a web browser.

NOTE: There are 3 versions of MySQL server but this HOWTO will only be focusing on the MySQL Community Server because it is free.


MySQL Server Setup Under FireDaemon Pro

Download the latest stable MySQL Server from here as a ZIP file. Some x86 applications have problems connecting to x64 MySQL servers so if you have problems making a database connection, then you will need to reinstall the MySQL server with the x86 binaries. Install the application. By default it installs to C:\Program Files\MySQL\SQL Server X.X. Throughout this HOWTO the folder name is referred to as "SQL Server X.X". This is because MySQL is updated often and rather than having this HOWTO refer to an out of date version, we are using a generic one. So replace X.X with whatever version you're installing.

If you want full functionality, then on step 3 of the installation (Setup Type), choose Full.


If you just want a basic MySQL server, then on step 3 of the installation (Setup Type), choose Custom. On the "Feature Selection" window just leave "MySQL Server X.X" and "Documentation" checked. Uncheck everything else.


On the "Configuration" window, you have 3 options. You only have to pay attention to 1 of them: "Server Configuration Type". In most cases you'll be running the MySQL server off of the same computer as other servers (e.g. web server, game server, etc.) so you should select "Server Machine" as your Config type. If the MySQL server is the only server on the computer, then you should select "Dedicated Machine".


On the "Configuration" window, select a root password. Make it as secure as you possibly can (12 characters recommended with numbers, letters and symbols). In order to safely shut down your MySQL server you will also need to create a new user that just has shutdown as a global privilege. Take note of the password as you will need it later in this HOWTO.

On the "Configuration" window, uncheck "Start the MySQL Server on System Startup", as FireDaemon will take care of this.


Install FireDaemon Pro into the directory of your choice, typically C:\Program Files\FireDaemon. FireDaemon Pro can be downloaded from here.

MySQL Server installs its own Windows Service so you will need to disable it before you setup your FireDaemon Service. Click the filter button on the FireDaemon Pro window and scroll down to MySQLxx. Double click the MySQL service, click the Stop button and change the startup type to "Disabled". Click OK and click the Filter button to switch back to FireDaemon services only.


Next start the FireDaemon GUI from the desktop shortcut. Click on the "Create a new service definition" button in the toolbar (or type Ctrl+N) and enter the information into the fields as you see below. Adjust the paths to suit your installation. Pay special attention to the Parameters list.


The most important field on the tab is the Parameters. The Parameters define the initial setup of your MySQL server.

Here’s the full parameter list you should have:
--defaults-file="C:\ProgramData\MySQL\MySQL Server X.X\my.ini"

  • "--defaults-file" Loads the my.ini config from a directory. The directory above is the default one, if you changed it during installation, you will have to change it here too.
Now click on the Settings tab. If you DON'T want to see MySQL Server running, uncheck the Interact with Desktop check box & select "Hidden" from the "Show Window" dropdown. Be sure to select "Global Job" from the Job Type field so the Windows service will shut down properly. You can optionally run the MySQL server as the user you installed it as. In the Logon Account field type your username (e.g. Administrator) and then enter the user's password twice in the Password and Confirm fields. You can change the Process Priority to allocate more CPU time to the MySQL server or specify which CPU or core the MySQL server will run on, in the case of multi-processor, hyperthreaded or multi-core CPUs.


Now click on the Lifecycle tab. Set "Maximum Shutdown Delay" to a high number. Here we have it set to 5 minutes (300000 ms). Your database might need a longer period to shut down. See the bottom of this HOWTO for more information.
mysql-as-a-service-lifecycle.png

Now click on the Pre/Post-Service tab and enter the following into the Post Service section:

Executable: C:\Program Files\MySQL\MySQL Server X.X\bin\mysqladmin.exe
Working Directory: C:\Program Files\MySQL\MySQL Server X.X\bin
Parameters: -u shutdown -pPASSWORD -P 3306 shutdown
Execution Time: 300000
Run Program: Before Event
Run Detached: [Checked]


Make sure to replace "PASSWORD" with the password you set for your shutdown account. If you haven't made one yet, then please refer to step 5. Also make sure that you change the port number if you changed it during installation.




Now click on the Install button to install and start MySQL Server!

Figuring out the best value for Maximum Shutdown Delay

If your database is large, then 5 minutes may not be enough time to shut it down safely. The best way to figure out exactly how much time you need is to time it.


Open Windows Task Manager and scroll down to mysqld.exe

Open a console window (cmd.exe) and move the console window so you can see mysqld.exe in Windows Task Manager. Enter the following, making sure to replace PASSWORD with the password you set for your shutdown user. If you changed the MySQL port, make sure to change it here too:
"C:\Program Files\MySQL\MySQL Server X.X\bin\mysqladmin.exe" -u shutdown -pPASSWORD -P 3306 shutdown

Do not press Enter yet. Make sure you can see mysqld.exe in task manager behind the command window. Now grab a stopwatch. Right when you press enter, press start on your stopwatch. Now pay attention to mysqld.exe in Task Manager. Right when it exits, press stop on your stop watch.



Take the time you recorded, add 10 to 25% more time to it and then use that as your Maximum Shutdown Delay. For instance, let's say it took 60 seconds for your database to shut down. 10% of 60 is 6 seconds. But you can't enter 66 into FireDaemon because milliseconds is the only unit accepted. To get milliseconds, multiply seconds by 1000 (or click here for a converter). You might need to do this process again if the time you chose is no longer enough.

Database Management

There are three ways you can use to create and manage your databases.

  1. Command line: This can be fast for repetitive things but it does have a learning curve and isn't the easiest thing to do.
  2. PhpMyAdmin: This is a web based manager but it requires you to set up a web server with PHP. It's also a security risk because hackers can brute force hack their way into your database (since the database can be managed remotely through a web browser).
  3. sqlYOG: This is the recommended one. It's a desktop application that gives you complete control over your databases and since its not accessible from the internet, you're protected against brute force password hacks! You will need remote desktop or physical access to the computer to use it though. You can download the free version here.