Launch MySQL from the Command Line

In my last blog entry, I discussed a “silent” install method for deploying MySQL on Windows.  However, the best way to distribute MySQL may be to not install it.  Instead, design the application to launch mysql from the command line.  With this method, you can deploy with two files, avoid registry entries, and hide database files from end-users and their IT staff.

[Note:  these instructions may also be useful if you can't get the MySQL installer to run on a particular Windows machine.  I find it easier to copy the system tables from an existing install of MySQL that was deployed with the Windows MSI installer.  Nevertheless, you could download the "without installer" MySQL zip file and use the mysql_system_tables.sql and mysql_system_table_data.sql to create your system tables.  I had to remove the "WHERE @had_user_table=0" from the mysql_system_table_data.sql to get it to work.  The SC.exe tool can be used to add it to the services]

The following is a minimum install example.  I prefer to run the full install using the Windows Essentials or the Windows MSI Installer download from http://dev.mysql.com/downloads/mysql/5.1.html#win32 .  Stop the service and use the files that were generated from the install to set up MySQL on your target machine.

Step 1.  Create an empty directory on the target server.  I created c:\min.  From an existing MySQL install, copy the following files into c:\min:

mysqld.exe

errmsg.sys

In my unmodified 5.1 version, mysqld.exe is 8.2 MB and errmsg.sys is 36K.  The following command will launch the full version of the server with only the aforementioned files:

Step 2.  c:\min>mysqld.exe --language=. --datadir=. --skip-grant-tables --bind-address=localhost

That’s it.  MySQL is running and you can now connect and create tables.

I used the following server options:

--language=. This is an essential parameter.  Your install needs to find errmsg.sys to start.  I retrieved this file from the “share” folder in an existing installation.

--datadir=. This determines the location of the data directory.  In my simple example, I put the data files in the application directory.  You may prefer to specify a data directory in the recommended Windows application directory.

--skip-grant-tables This variable allows MySQL to load without the privileges table.  Anyone can connect without authenticating.   I’ve specified --bind-address for only local connections. If you’re deploying to a server in a secure location on a private network and MySQL is only running when your application is running, this may be acceptable.

--bind-address=localhost  This option determines the IP address for which to bind, it will only listen on localhost.  As an alternative, it’s possible to run MySQL on windows with named pipes or shared memory.

To force authentication, you will need to include the privilege tables.  In this case, create a data directory.  In my case I used c:\min\data .  Then, copy the mysql folder from an existing install into the data directory.  Then, start MySQL and specify the new data directory:

c:\min>mysqld.exe --language=. --datadir=./data

Since these are MyISAM files, MySQL will recognize the additional database and files.  Also, omit the --skip-grant-tables parameter.  Now, you’ll be prompted for a password.  It will be the same password that you specified when you ran the full install.

MySQL is running.  What’s next?  If you’re deploying an application, you’ll need to deploy your initial schema and data.  The simplest way would be to dump the schema and tables from your development machine and then load in the sql script on your target server.  Documentation for dumping a loading and database can be found here: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

There are many startup options and system variables which can be specified on the command line or the configuration file (--defaults-file).

Here are the appropriate links:

http://dev.mysql.com/doc/refman/5.1/en/server-options.html

http://dev.mysql.com/doc/refman/5.1/en/using-system-variables.html

If only using MyISAM, you might want to start MySQL with --skip-innodb.  You can use these options to make your installation more secure. You may want to change the default TCP/IP port.  If using InnoDB, you can set innodb-data-file-path to a different location, a more obscure filename and a different size.  This would prevent someone from copying the data to another computer and trying to access the data with a different application.

There are other ways to deploy MySQL with your application, including using the “silent” install method or using the deeply embedded library (libmysqld).  If you’d prefer that your customer has full visibility and access to MySQL, a “silent” install adds MySQL to the program files, service control panel and the Add/Remove program options.  If you’re developing a stand-alone application in C/C++, the deeply embedded library (libmysqld) provides excellent performance with a small footprint. Otherwise, your best option may be to  launch MySQL from the command line.  This method provides a simple and lightweight deployment with all the MySQL server capabilities.

About these ads