Mini Servers: MySQL 5.0.67 Portable

Mini Servers: Compact but fully functional.

Mini Server 11 uses MySQL 5.0.67-community-nt Core

MySQL 5.0.67

MySQL portable complements Uniform Server's range of mini servers. This server uses the latest 5.0.67 stable community release which weights in at 173 MB however the mini's size is just over 10MB.

The full download contains several database variants, tools and languages used by professional users. Most of these are not required to run a fully functioning server hence the mini contains only two clients for maintaining the server, single language support and the main community-nt core. This results in a very compact server ideal for learning SQL and exploring database management.

(See support files for download)

This write-up shows how to install, configure MySQL and to minimise the risk of unauthorized access.

Top

Specification

The server has the following specification:

• Server shall be portable
• MySQL server shall run on port 3311 with the option of changing this to a different port.
• The server shall automatically detect and run on next available free drive. Provision to hard code this drive letter.
• Server clients mysqladmin.exe and mysql.exe shall be included.

Top

my-small.cnf changes

This mini server uses the configuration file from Uniform Server 3.5-Apollo I have added a single line:

pid-file=mysql_mini.pid

Without this line the pid file is named based on the host machines name. In the batch files I specifically target this file hence a defined name is required.

Renamed

The file my-small.cnf has been renamed to my.cnf this is the default name MySQL searches for during start-up. Renaming avoids the need to specifically target the configuration file by name.

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_11.exe to any folder of your choice.

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_11.exe, starts the extraction process. No need to change the folder destination, click extract, this creates a new folder mini_server_11 containing two files and one folder.

1. mysql_start.bat - Double click to start the server
2. mysql_stop.bat - Double click to stop server
3. udrive - Folder containing server and clients.

Top

Test

Testing is straight forward.

1. Start the server by double clicking on mysql_start.bat (Automatically detects free drive letter creates new virtual drive and runs the server.)
2. In the command window that opens 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:

Z:\bin>mysqladmin --user=root --password=root status

Uptime: 960  Threads: 1  Questions: 1  Slow queries: 0  Opens: 12
Flush tables: 1  Open tables: 3  Queries per second a vg: 0.001

Z:\bin>

Top

Security

After installation the first thing you must do is set a new password for user root. The default password is root you can change this using either admin or mysql utilities.

Make sure to use a strong password, containing letters, numbers, and symbols. The password should have a minimum of six characters and not contain any words found in a dictionary, and letters should be in mixed case.

Quickest way to change the password is to use mysqladmin the general format is:

Z:\bin>mysqladmin --user=root --password=root password NEWPASSWORD

Example change password to fred123 use the following:

Z:\bin>mysqladmin --user=root --password=root password fred123

After changing the password the server is ready to use.

Note: The mini server currently has a single user configured named "root" access to the server is restricted to "localhost" and "127.0.0.1" hence changing the password enhances security. If you change access to "%" (Internet access) you must change the password otherwise kiss your MySQL server good bye! Hence why I always recommend changing the password as the first thing to do.

Top

DOS (CMD) Prompt

To use either of the utilities admin or mysql you need to open a command window and navigate to folder bin. The Z:\bin> is referred to as a DOS prompt, in the section extra information I have assumed you are starting at the DOS prompt. If you close the initial window that opens you will need to manual start it and navigate to the bin folder:

Open a command window:

Open a command prompt click start > click run > type cmd > click Ok.
Alternatively click on Start > all programs > accessories > click on Command Prompt

Navigate to folder bin: Type the following into the command window assuming the drive automatically created was z:

z:
cd bin

Top

PHP

The mini server will quite happily run alongside Uniform Server 3.5-Apollo. This little PHP test script creates a new database on the mini MySQL server.

Copy the following script to Uniform Server 3.5-Apollo folder www and save it as test.php

<?
// Set database info
$dbhost='localhost:3311'; // Port mini server is on
$dbusername='root';       // User name
$dbuserpass='root';       // Use the password you set
$dbname='mpgtest';        // name of database to be created

// Connect to mysql server.
$server_link_id = mysql_connect ($dbhost, $dbusername, $dbuserpass);
echo "Success connected to MySQL server. <br>";

// Create the database.
if (!mysql_query("CREATE DATABASE $dbname")) die(mysql_error());
echo "Success database created.";
?>

Start both Apollo and mini mysql server.

Run the script by typing: http://localhost/test.php

Displayed result:

Success connected to MySQL server.
Success database created.

Check folder \mini_server_11\udrive\data this will now contain a new database folder named mpgtest

Top

Change Virtual Drive

The server automatically detects the first free drive letter and uses that. You can override this in one of two ways:

1. Start the server using a drive parameter for example: mysql_start.bat z this forces the server to use drive z
2. Edit mysql_start.bat, locate the following line:
     rem set Disk=w
   Remove the rem and replace w with the letter you want to use for example:
     set Disk=X  Forces the server to use drive letter X

Top

Change server default port

Mini MySQL uses a non-standard port 3311 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 port is unique. To change a server's port two files server_stop.bat and my.cnf require changing. The following example shows how to change the port to 3307:

Top

server_stop.bat

This script shuts-down the server gracefully search for the line:

rem ## Kill server
udrive\bin\mysqladmin.exe --port=3311 --user=root --password=root shutdown

change the port number:

rem ## Kill server
udrive\bin\mysqladmin.exe --port=3307 --user=root --password=root shutdown

Note: If you change either the user name or password substitute the new values in the above line otherwise you will be unable to shut down the server.

Top

my.cnf

This is the MySQL server configuration file.

• Open file: my.cnf (Located in folder: \mini_server_11\udrive) Note: The file extension .cnf is not displayed.

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

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

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

# The MySQL server
[mysqld]

port=3311
#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

Multi Servers

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

If using auto drive detection no need to change the virtual drive letter. However the server port for each server has to be unique hence need changing. The servers may be started in any order.

Note: You can run the mini servers alongside Uniform Server 3.5-Apollo make sure the mini's do not use port 3306 Uniform Server's MySQL uses this port.

Top

Additional information

The following assumes you have a command window open and are at a DOS prompt as explained above. This section is intended to provide a few general examples and how to run them on server 11.

MySQL - Admin

The mysqladmin utility is a interactive client program allowing you to connect to a MySQL server and perform configuration tasks. 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.

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:

Z:\bin>mysqladmin --user=root password NEWPASSWORD

Note: password before NEWPASSWORD is a command.

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

Z:\bin>mysqladmin --user=root --password=root password NEWPASSWORD

For example to change the root password to fred123

Z:\bin>mysqladmin --user=root --password=root password fred123

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

Z:\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

Note: After changing the password and before stopping the server edit mysql_stop.bat and change the password in this line to match.

rem ## Kill server
udrive\bin\mysqladmin.exe --port=3311 --user=root --password=root shutdown

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:

Z:\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 11

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.

Z:\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
Z:\bin>

Quick Example:

The following shows a complete session:

Commands used:

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

Z:\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

Z:\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

Z:\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

Z:\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 ----------

Z:\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
Z:\bin>

Note: After changing the password and before stopping the server edit mysql_stop.bat and change the password in this line to match.

rem ## Kill server
udrive\bin\mysqladmin.exe --port=3311 --user=root --password=root shutdown

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:

Z:\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:

Z:\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.

Z:\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

These mini servers to reduce size are stripped down versions of the full product however they use real cores, MySQL mini server is no exception. It packs all the power of the full product only full language support and some tools are lacking. After downloading the full product select only the components you need this will keep size to a minimum.

MySQL is very versatile with a few minor changes can easily run as a service covered on the next page.

Top

---

Ric