Mini Servers: MySQL 5.0.67 Portable
|
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 11 uses MySQL 5.0.67-community-nt Core
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.
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.
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.
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.
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.
- mysql_start.bat - Double click to start the server
- mysql_stop.bat - Double click to stop server
- udrive - Folder containing server and clients.
Test
Testing is straight forward.
- Start the server by double clicking on mysql_start.bat (Automatically detects free drive letter creates new virtual drive and runs the server.)
- 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>
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.
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
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
Change Virtual Drive
The server automatically detects the first free drive letter and uses that. You can override this in one of two ways:
- Start the server using a drive parameter for example: mysql_start.bat z this forces the server to use drive z
- 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
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:
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.
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
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.
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)
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
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.
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
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
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.
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.
 |
Ric |