View source for Mini Servers: MySQL 4.1.22 Service

{{Uc nav mini servers}}
[[Image:Uc_mini_14.gif|MySQL 4.1.22|right]]'''Mini Server 12 MySQL 4.1.22-community-nt Core'''

This page covers running our mini server (describe on the [[Mini Servers: MySQL 4.1.22 Portable|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 files | support section for download]] details

'''''[[#top | 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 3314 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 | 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 | 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 | 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_14''', 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 | 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_14''' 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:''' 
{|border="0" cellpadding="4" cellspacing="1" style="background:#222222"
|-style="background:#e8e8e8"
!my.cnf located in folder: C:\mini_server_14\bin
|-style="background:#f5f5f5"
|
<pre style="border:none;background:#f5f5f5;margin:0;padding:6">
# Uncomment the following rows if you move the MySQL distribution to another
# location
basedir = "/"
datadir = "/data/"
tmpdir = "/tmp"
</pre>
|}
'''Change to:'''
{|border="0" cellpadding="4" cellspacing="1" style="background:#222222"
|-style="background:#e8e8e8"
!my.cnf located in folder: C:\mini_server_14\bin
|-style="background:#f5f5f5"
|
<pre style="border:none;background:#f5f5f5;margin:0;padding:6">
# Uncomment the following rows if you move the MySQL distribution to another
# location
basedir = "c:/mini_server_14/"
datadir = "c:/mini_server_14/data/"
tmpdir = "c:/mini_server_14/tmp/"
</pre>
|}

'''''[[#top | 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_14'''. What is important --defaults-file=C:/mini_server_14/bin/my.cnf this forces MySQL to pick-up the correct configuration file. 

The second line starts the service notice it uses the unique name defined in the first line. 

{|border="0" cellpadding="4" cellspacing="1" style="background:#222222"
|-style="background:#e8e8e8"
!install.bat located in folder: C:\mini_server_14
|-style="background:#f5f5f5"
|
<pre style="border:none;padding:0;margin:0">
C:\mini_server_14\bin\mysqld-opt.exe --install mini_server_14 --defaults-file=C:/mini_server_14/bin/my.cnf
net start mini_server_14
</pre>
|}

'''''[[#top | 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_14'''.

{|border="0" cellpadding="4" cellspacing="1" style="background:#222222"
|-style="background:#e8e8e8"
!uninstall.bat located in folder: C:\mini_server_14
|-style="background:#f5f5f5"
|
<pre style="border:none;padding:0;margin:0">
net stop mini_server_14
bin\mysqld-opt.exe --remove mini_server_14
</pre>
|}

'''''[[#top | 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.

{|border="0" cellpadding="4" cellspacing="1" style="background:#222222"
|-style="background:#e8e8e8"
!console.bat located in folder: C:\mini_server_14
|-style="background:#f5f5f5"
|
<pre style="border:none;padding:0;margin:0">
cd bin
start cmd.exe /k type quick_start_guide.txt
</pre>
|}

'''''[[#top | 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_14'''
* '''net start mini_server_14'''

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

'''''[[#top | Top]]'''''

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

=== Download ===
Download this server from SourceForge [http://sourceforge.net/projects/miniserver/files/MiniServer/MiniServer_%20MySQL%204.1.22%20Service/ Project Page] save the file '''mini_server_14.exe''' to drive '''C'''.

'''''Note'':''' Check out the mini server's [[Mini Servers: Support and download|support and download page]] detailing how to obtain full binaries for MySQL.

'''''[[#top | Top]]'''''

=== Extract files ===
Double click on mini_server_14.exe, starts the extraction process. No need to change the folder destination, click extract, this creates a new folder '''mini_server_14''' 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 | 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:<br>'''mysqladmin --port=3314 --user=root --password=root status'''<br>If challenged by your firewall allow access for mysqladmin.exe

Result similar to this confirms server is running:
<pre>
C:\mini_server_14\bin>mysqladmin --port=3314 --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_14\bin>
</pre>

'''''[[#top | Top]]'''''

== Change drive letter and or folder ==
The server requires a fixed location currently: c:\mini_server_14

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

To do this you need to edit three files:
* '''install.bat'''
*''' uninstall.bat'''
* '''my.cnf'''

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

'''''[[#top | Top]]'''''

== Change server default port ==
Mini MySQL uses a non-standard port 3314 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:
<pre>
# This will be passed to all mysql clients
[client]
#password=my_password
port=3314
#socket=MySQL

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

# The MySQL server
[mysqld]

port=3314
#socket=MySQL
</pre>
and change as shown:

<pre>
# 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
</pre>

'''''[[#top | Top]]'''''

== Change Service Name ==
To change the service name two files require editing:

Edit instal.bat locate the name mini_server_14 and change to something unique for example mini_server_14xx

Edit uninstal.bat locate the name mini_server_14 and change to something unique for example mini_server_14xx

'''''[[#top | 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_14 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 | 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 --port=3314 --user=root --password=root status'''

''Command line details:''

*'''mysqladmin''' Runs the program with the following parameters
*'''--port=3314''' Ensures the correct server is picked-up
* '''--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 | 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: 

<pre>
C:\mini_server_14\bin>mysqladmin --port=3314 --user=root password NEWPASSWORD
</pre>

'''''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:
 
<pre>
C:\mini_server_14\bin>mysqladmin --port=3314 --user=root --password=root password NEWPASSWORD
</pre>

For example to change the root password to fred123

<pre>
C:\mini_server_14\bin>mysqladmin --port=3314 --user=root --password=root password fred123
</pre>
To re-log in use the new password for example:
<pre>
C:\mini_server_14\bin>mysqladmin --port=3314 --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
</pre>

==== 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:
<pre>
C:\mini_server_14\bin>mysqladmin --port=3314 --user=mike --password=fred123 password power26
</pre>

'''''[[#top | 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|DOS prompt section]]''' for details.

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

'''Log into Mini Server 14'''

To log into a mini server type the following: '''mysql --port=3314 --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.   

<pre>
C:\mini_server_14\bin>mysql --port=3314 --user=root --password=root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.22-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> quit
Bye
C:\mini_server_14\bin>
</pre>

'''Quick Example:'''

The following shows a complete session:

'''''Commands used'':'''

mysql --port=3314 --user=root --password=root<br>
SELECT VERSION(), CURRENT_DATE;<br>
SHOW DATABASES;<br>
quit

<pre>
C:\mini_server_14\bin>mysql --port=3314 --user=root --password=root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.22-community-nt

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

mysql> SELECT VERSION(), CURRENT_DATE;
+---------------------+--------------+
| VERSION()           | CURRENT_DATE |
+---------------------+--------------+
| 4.1.22-community-nt | 2008-08-13   |
+---------------------+--------------+
1 row in set (0.01 sec)

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
+----------+
1 row in set (0.00 sec)

mysql> quit
Bye

C:\mini_server_14\bin>
</pre>

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 | 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
<pre>
C:\mini_server_14\bin>mysql --port=3314 --user=root --password=root
mysql> USE mysql;
mysql> update user set password=PASSWORD("NEWPASSWORD") where User='root';
mysql> FLUSH PRIVILEGES;
mysql> quit
</pre>

For example change the root user password to fred123 
<pre>
C:\mini_server_14\bin>mysql --port=3314 --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_14\bin>mysql --port=3314 --user=root --password=fred123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 4.1.22-community-nt MySQL Community Edition (GPL)

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

mysql> quit
Bye
C:\mini_server_14\bin>
</pre>

'''''[[#top | 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:
<pre>
C:\mini_server_14\bin>mysql --port=3314 --user=root --password=root
mysql> USE mysql;
mysql> UPDATE user SET user='mike' WHERE user='root';
mysql> FLUSH PRIVILEGES;
mysql> quit
</pre>

'''''[[#top | Top]]'''''

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

<pre>
C:\mini_server_14\bin>mysql --port=3314 --user=root --password=root
mysql> use mysql;
mysql> DELETE FROM user WHERE user = 'root' AND host = '%';
mysql> FLUSH PRIVILEGES;
mysql> quit
</pre>

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

'''''[[#top | 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.

<pre>
C:\mini_server_14\bin>mysql --port=3314 --user=root --password=root
mysql> use mysql;
mysql> DELETE FROM user WHERE user = '';
mysql> FLUSH PRIVILEGES;
mysql> quit
</pre>

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

== Summary ==
That concludes this series of mini servers, although they use only core elements from Apache, MySQL, PHP and Perl they are none the less very powerful. Using Uniform Server's architecture you can pop them on a USB memory stick.

Although Uniform Server 3.5-Apollo is a complete package sometimes its useful to run one of the mini servers alongside it to explore various architectures.

'''''[[#top | Top]]'''''

----

{|
| [[Image:uc_small_logo.gif]] || [[User:Ric|Ric]]
|}


[[Category: UniCenter]]
[[Category: Mini Servers]]
[[Category: MySQL]]
[[Category: Oily Rag]]
[[Category: Self Install]]