Mini Servers: MySQL 4.1.22 Service
|
Mini Servers: Introduction | Support | Server 1 - Portable | Server 2 - Service | Server 3 - Portable Authentication | Server 4 - Portable Authen. SSL | Server 5 - SSL Standalone | Browsers dislike self-signed certificates | Server 6 - PHP 5.2.6 Portable | Server 7 - PHP 5.2.6 Service | Server 8 - MySQL Support | Guest Book | Server 9 - Perl 5.2.6 Portable | Server 10 - Perl 5.2.6 Service | Server 11 - MySQL 5.0.67 Portable | Server 12 - MySQL 5.0.67 Service | Server 13 - MySQL 4.1.22 Portable | Server 14 - MySQL 4.1.22 Service | phpMyAdmin - Mini support | MySQL - General problems |
| Mini Servers: Compact but fully functional. |
Mini Server 12 MySQL 4.1.22-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
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.
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.
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.
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.
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:
| my.cnf located in folder: C:\mini_server_14\bin |
|---|
# 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_14\bin |
|---|
# 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/" |
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.
| install.bat located in folder: C:\mini_server_14 |
|---|
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 |
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.
| uninstall.bat located in folder: C:\mini_server_14 |
|---|
net stop mini_server_14 bin\mysqld-opt.exe --remove mini_server_14 |
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_14 |
|---|
cd bin start cmd.exe /k type quick_start_guide.txt |
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.
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_14.exe to drive C.
Note: Check out the mini server's support and download page detailing how to obtain full binaries for MySQL.
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.
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 --port=3314 --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_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>
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
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:
# 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
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
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
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.
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)
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_14\bin>mysqladmin --port=3314 --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_14\bin>mysqladmin --port=3314 --user=root --password=root password NEWPASSWORD
For example to change the root password to fred123
C:\mini_server_14\bin>mysqladmin --port=3314 --user=root --password=root password fred123
To re-log in use the new password for example:
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
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_14\bin>mysqladmin --port=3314 --user=mike --password=fred123 password power26
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 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.
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>
Quick Example:
The following shows a complete session:
Commands used:
mysql --port=3314 --user=root --password=root
SELECT VERSION(), CURRENT_DATE;
SHOW DATABASES;
quit
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>
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.
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_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
For example change the root user password to fred123
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>
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_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
Restrict root to localhost
In addition, the root account can be restricted to login from localhost with the following commands:
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
Note: Like Uniform Server 3.5-Apollo the mini server already restricts access to localhost only.
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_14\bin>mysql --port=3314 --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
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.
 |
Ric |