Mini Servers: MySQL 5.0.67 Service

Mini Servers:
Compact but fully functional.

Mini Server 12 MySQL 5.0.67-community-nt Core

This page covers running our mini server (describe on the previous page) as a service, its big brother Uniform Server 3.5-Apollo automates this process. We are not going to take this easy option! Well you would never learn anything by doing that, the manual alternative although slightly more difficult provides an insight into MySQL and Uniform Servers flexibility.

Note: Check support section for download details

Top

Specification

The server has the following specification:

Server shall run as a service and include a service name
MySQL server shall run on port 3312 with the option of changing this to a different port.
Server clients mysqladmin.exe and mysql.exe shall be included.

The only difference in this specification server is no longer portable but run as a service. This means every time you restart your PC the MySQL server will automatically start. The advantage of this, when running a server permanently connected to the Internet should a power interruption occur the server will automatically restart when power is restored. If other elements of the system have been set accordingly then your Internet presence will also be restored.

Top

Security Issues

Security issues remain as explained on the previous page. I would like to stress this is about as best as it gets. The server is restricted to localhost access with a single user (root) requiring a password.

Top

Changes

Interestingly if you run the portable version of this sever on a dedicated disk the configuration file would remain unchanged. Use the two new batch files and you are ready to go.

The server is being run from a folder and the relative paths within my.cnf require converting to absolute paths.

Top

Relative

In theory MySQL is quite happy to use relative paths for example “/” refers to the top level of the current disk. Uniform Server and our portable mini server use this to great advantage by defining a dedicated virtual disk “w”. However when changing across disk space Windows gets uptight about relative paths unless you can explicitly define a disk.

Absolute

Windows likes pegs hammered into the ground so to speak, hence when running as a service specifying absolute paths is essential to keep both parties happy for example c:\mini_server_12, the drive must be specified and then any folders etc.

No need to ramble on, just remember absolute paths are the order of the day so lets roll up ours sleeves and look at some code hacking.

Top

Configuring MySQL

As mentioned above absolute paths are required in the configuration file. I have chosen to run the server on C drive from folder mini_server_12 you can use a different drive and or folder the choice is yours, just substitute accordingly. I have shown only the section that needs changing.

Original:

my.cnf located in folder: C:\mini_server_12
# Uncomment the following rows if you move the MySQL distribution to another # location basedir = "/" datadir = "/data/" tmpdir = "/tmp"

Change to:

my.cnf located in folder: C:\mini_server_12
# Uncomment the following rows if you move the MySQL distribution to another # location basedir = "c:/mini_server_12/" datadir = "c:/mini_server_12/data/" tmpdir = "c:/mini_server_12/tmp/"

Top

install.bat

Batch files to install and uninstall MySQL as a service are not very complex; the trick is to supply the right parameters as shown.

The first line installs MySQL as a service if you run more than one copy of MySQL it requires a unique name for this server I am using mini_server_12. The second line starts the service notice it uses the unique name defined in the first line.

install.bat located in folder: C:\mini_server_12
C:\mini_server_12\bin\mysqld-opt.exe --install mini_server_12 net start mini_server_12

Top

uninstall.bat

The uninstall batch file first stops the “MySQL” service and then proceeds to uninstall it. Note the use of the service name mini_server_12.

uninstall.bat located in folder: C:\mini_server_12
net stop mini_server_12 bin\mysqld-opt.exe --remove mini_server_12

Top

console.bat

This batch file provides a convenient way to start a command window at the right location to run MySQL clients. It displays a quick start guide for reference.

console.bat located in folder: C:\mini_server_12
cd bin start cmd.exe /k type quick_start_guide.txt

Top

Command Line

After installing the MySQL service with the above batch file you can start and stop the "MySQL" service at anytime using a command line this saves uninstalling the service every time you want to stop the server:

net stop mini_server_12
net start mini_server_12

Note: You can use console.bat to open a command window.

Top

Support files

Each mini server is complete and zipped into a single self-extracting archive file.

Download

Download this server from SourceForge Project Page save the file mini_server_12.exe to drive C.

Note: Check out the mini server's support and download page detailing how to obtain full binaries for MySQL.

Top

Extract files

Double click on mini_server_12.exe, starts the extraction process. No need to change the folder destination, click extract, this creates a new folder mini_server_12 containing five files and five folders.

Files:

install.bat - Double click to install and start server as a service.
uninstall.bat - Double click to stop and uninstall server service.
console.bat - Double click to open a command window displays a quick quide.
my.cnf - MySQL server configuration file.
readme.txt - Provides a summary how to change aspects of the server.

Folders:

bin - Contains the MySQL program and MySQL clients.
data - Contains MySQL database and is the destination for user databases.
doc - Contains Uniform Server license.
share - Contains character sets and language files.
tmp - Folder used for temporary files.

Top

Test

Testing is straight forward.

Install the server by double clicking on install.bat
Run console.bat this opens a command window.
In the command window type the following:
mysqladmin --user=root --password=root status
If challenged by your firewall allow access for mysqladmin.exe

Result similar to this confirms server is running:

C:\mini_server_12\bin>mysqladmin --user=root --password=root status
Uptime: 72  Threads: 1  Questions: 1  Slow queries: 0  Opens: 12
Flush tables: 1  Open tables: 3  Queries per second avg: 0.014

C:\mini_server_12\bin>

Top

Change drive letter and or folder

The server requires a fixed location currently: c:\mini_server_12

You can move the server to any drive and folder for example e:\my_server\mini_server_12

To do this you need to edit three files:

install.bat
uninstall.bat
my.cnf

Find all occurrences of c:\mini_server_12 and replace with e:\my_server\mini_server_12

Top

Change server default port

Mini MySQL uses a non-standard port 3312 this avoids clashing with Uniform Server which uses the standard port 3306 for it's MySQL server. If you are not running another server on 3306 you can use this port.

You can run any number of MySQL servers so long as each server's port is unique and it's service name. To change a server's port edit my.cnf. The following example shows how to change the port to 3307:

At the top of the file you will find there are two port lines these need changing locate this section:

# This will be passed to all mysql clients
[client]
#password=my_password
port=3312
#socket=MySQL

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# The MySQL server
[mysqld]

port=3312
#socket=MySQL

and change as shown:

# This will be passed to all mysql clients
[client]
#password=my_password
port=3307
#socket=MySQL

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# The MySQL server
[mysqld]

port=3307
#socket=MySQL

Top

Change Service Name

To change the service name two files require editing:

Edit instal.bat locate the name mini_server_12 and change to something unique for example mini_server_12xx

Edit uninstal.bat locate the name mini_server_12 and change to something unique for example mini_server_12xx

Top

Multi Servers

If you wish to run several mini servers at the same time create a new folder for each server and copy contents of folder mini_server_12 into each of these.

Change the paths, server port and service name as explained above, each server must be unique. The servers may be started in any order.

Top

Additional information

The following assumes you have a command window open if not run console.bat. This section is intended to provide a few general examples and how to run them on server 12.

MySQL - Admin

The mysqladmin utility is a interactive client program allowing you to connect to a MySQL server and perform configuration tasks.

View all commands supported by mysqladmin type the following at a command prompt: mysqladmin --help

One such command is status this allows you to perform a quick check to see if the server is running.

Type: mysqladmin --user=root --password=root status

Command line details:

mysqladmin Runs the program with the following parameters
--user=root The user will normally be the server administrator, in this example name is root.
--password=root User password in this example root. Note if you type only --password you will be prompted to enter a password
status Follow password with a command to be executed on the server, in this example server status is being requested.

Note: I am using what is referred to as long format parameters --something=something you can use short versions (not covered)

Top

Change Root password

Server upgrade: If you upgrade the MySQL server it's default is to have no password set, in this situation use the following to set a password:

C:\mini_server_12\bin>mysqladmin --user=root password NEWPASSWORD

Note: password before NEWPASSWORD is a command.

Mini Server downloaded: Mini Server 4a has both user name and password set to root. To change an existing password use the following:

C:\mini_server_12\bin>mysqladmin --user=root --password=root password NEWPASSWORD

For example to change the root password to fred123

C:\mini_server_12\bin>mysqladmin --user=root --password=root password fred123

To re-log in use the new password for example:

C:\mini_server_12\bin>mysqladmin --user=root --password=fred123 status
Uptime: 571  Threads: 1  Questions: 6  Slow queries: 0  Opens: 13  Flush tables: 1  Open tables: 4  Queries per second a
vg: 0.011

Change password for other users

To change a normal user's password you follow a similar process for example user mike, old password fred123 and new password power26:

C:\mini_server_12\bin>mysqladmin --user=mike --password=fred123 password power26

Top

MySQL - Client program

The mysql client program (terminal monitor, monitor or mysql prompt) is an interactive program allowing you to connect to a MySQL server, run queries, and view the results.

It may also be used in batch mode, you place your queries in a file beforehand, then tell mysql to execute the contents of the file.

The program is run from a command prompt you need to navigate to the bin folder in order to run it see DOS prompt section for details.

To view all commands supported by mysql type the following: mysql --help

Log into Mini Server 12

To log into a mini server type the following: mysql --user=root --password=root on success a mysql prompt mysql> is displayed this is where you type in sql commands.

The following example logs into and then out of the server. At the mysql prompt I have typed quit this terminate the program logs out of the server.

C:\mini_server_12\bin>mysql --user=root --password=root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.51b-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> quit
Bye
C:\mini_server_12\bin>

Quick Example:

The following shows a complete session:

Commands used:

mysql --user=root --password=root
SELECT VERSION(), CURRENT_DATE;
SHOW DATABASES;
quit

C:\mini_server_12\bin>mysql --user=root --password=root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.51b-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT VERSION(), CURRENT_DATE;
+----------------------+--------------+
| VERSION()            | CURRENT_DATE |
+----------------------+--------------+
| 5.0.51b-community-nt | 2008-07-28   |
+----------------------+--------------+
1 row in set (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)

mysql> quit
Bye

C:\mini_server_12\bin>

After running the above you will have an idea what to expect. The next examples show only the commands used, I have included the prompts Z:\bin> (DOS prompt) and mysql> (mysql prompt) for reference do not type these in.

Top

Change Root password

MySQL stores user names and passwords in the MySQL database in table user, you can directly update both name and password.

Use the following method to update or change password for user root

C:\mini_server_12\bin>mysql --user=root --password=root
mysql> USE mysql;
mysql> update user set password=PASSWORD("NEWPASSWORD") where User='root';
mysql> FLUSH PRIVILEGES;
mysql> quit

For example change the root user password to fred123

C:\mini_server_12\bin>mysql --user=root --password=root

mysql> USE mysql;
Database changed
mysql> update user set password=PASSWORD("fred123") where User='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (1.03 sec)

mysql> quit
Bye

--- Test new password ----------

C:\mini_server_12\bin>mysql --user=root --password=fred123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.51b-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> quit
Bye
C:\mini_server_12\bin>

Top

Change Root User name

The root user does not need to be named 'root'. Most attackers will obviously try to compromise the 'root' user account and will be stopped if there is no 'root' user. To change the name of the root user account, use the following commands:

C:\mini_server_12\bin>mysql --user=root --password=root
mysql> USE mysql;
mysql> UPDATE user SET user='mike' WHERE user='root';
mysql> FLUSH PRIVILEGES;
mysql> quit

Top

Restrict root to localhost

In addition, the root account can be restricted to login from localhost with the following commands:

C:\mini_server_12\bin>mysql --user=root --password=root
mysql> use mysql;
mysql> DELETE FROM user WHERE user = 'root' AND host = '%';
mysql> FLUSH PRIVILEGES;
mysql> quit

Note: Like Uniform Server 3.5-Apollo the mini server already restricts access to localhost only.

Top

Remove the anonymous account

Most new MySQL installations contain an anonymous account for testing. To secure this remove the account using the following commands:

Note: Shown as an example it does not exist on the mini server.

C:\mini_server_12\bin>mysql --user=root --password=root
mysql> use mysql;
mysql> DELETE FROM user WHERE user = '';
mysql> FLUSH PRIVILEGES;
mysql> quit

Note: The mini server does not include an anonymous account.

Summary

A large number of service providers still use the MySQL series 4 on the next two pages a portable and service version of MySQL 4.1.22 are included completing the range of mini servers.

These allowing code to be tested on both series 5 and 4 reducing incompatibility problems when transferring to a service provider.

Top

Ric