Softpanorama (slightly skeptical) Open Source Software Educational Society

May the source be with you, but remember the KISS principle ;-)

MySql

Critics find MySQL's popularity surprising in the light of the existence of other open source database projects with comparable performance and in closer compliance to the SQL standards. MySQL advocates reply that the program serves its purpose for its users, who are willing to accept the program's limitations (which decrease with every major revision) in exchange for speed, simplicity and rapid development. Another, perhaps simpler, explanation for MySQL's popularity is that it is often included as a default component in low-end commercial web hosting plans, so that for application developers (mostly using PHP and Perl) MySQL is the only DBMS choice unless they want to operate their own web hosts.

MySQL installation on Solaris 9 or earlier can be done from precompiled packages or by compiling packages with gcc or Forte 5.0. Forte 5.0 is a better solution: there is at least 4% speed increase on UltraSparc when using Forte 5.0 in 32-bit mode, as compared to using gcc 3.2 with the -mcpu flag.

The Solaris 10 OS has MySQL on Software Supplement disk.  Precompiled packages were compiled with Sun Studio.

The "native" MySQL  Solaris package provides a maintenance script for starting, stopping and restarting. This script is located in /etc/sfw/mysql and is called mysql.server. The default Solaris 10 installation doesn't reference this script during the traditional start-up process. The system administrator may have copied (or linked) this script into the traditional start-up directories.

It's better to configure MySQL using SMF as recommended in the following tip by William Pool Configuring MySQL to Use With Service Management Facility (SMF).

Old News ;-)

[Jul 28, 2008] phpMyAdmin 2.11.8 by phpMyAdmin Devel Team

About: phpMyAdmin is a tool intended to handle the administration of MySQL over the Web. It can create, rename, and drop databases, create/drop/alter tables, delete/edit/add fields, execute any SQL statement, manage keys on fields, create dumps of tables and databases, export/import CSV data, and administrate one single database and multiple MySQL servers.

[Jun 26, 2008 ] MySQL Savior 1.0.4 by tek

About: MySQL Savior is a bash script that, after simple configuration, will create local backups of your databases. If configured to do so it can also email your backups to a remote email address or scp them to a remote server. Or it can do all 3.

Changes: This release adds compression of the resulting backup file. It fixes two error messages only seen when run from the command line that were purely cosmetic. It adds a bit more documentation in the README.TXT file, specifically covering how to set up SSH keys for those who want/need/use that functionality.

[Apr 24, 2008] Entrance 1.2.70  by Tod Landis

About: Entrance is a program for browsing MySQL databases. It includes a "match box" search facility that is simlar to the Mac's Spotlight. Entrance can create charts based on query results, and these charts can be zoomed and panned. Entrance also supports an innovative feature called "data painting" that allows users to select and paint data points interactively.

Changes: Entrance now supports "EarthCharts" which plot (lat, long) values stored in MySQL tables on an image of the Earth. An example at http://todlandis.com/ shows how EarthCharts can be used in conjunction with GeoIP data to plot the approximate locations of Web site visitors.

[Mar 18, 2008] QOT 0.0.1 by vkolesnikov

About: QOT (Query analysis and Optimization Tool) is an intelligent assistant for MySQL DBAs and developers.

Changes: This release has query analysis, index generation, and simple rewrites.

[Mar 06, 2008] Open Source Weblog MySQL CEO it's tough for private open source firms

Yesterday I caught up with Marten Mickos, the former CEO of open source database firm MySQL, which was recently acquired by Sun for $1bn. We had a wide-ranging chat about life, the universe and everything open source.

One of the interesting points he made was that it’s often tough for smaller, private open source firms like MySQL to be taken seriously by enterprise customers or larger organizations. So much so, that being bought by Sun has already started to boost the firm’s revenues.

"As soon as the deal closed we immediately secured a big deal with a major European national police agency," said Mickos, now SVP database products at Sun. "Key to them choosing MySQL was that we are now part of a much larger public corporation. The deal wouldn't have happened when we were private."

Downloads of the firm's free, open source database have accelerated too, from around 50,000 a day before the deal was announced to around 60,000 per day now - 67,000 copies were downloaded on Monday.

But perhaps the bigger question is how many of those downloads get beyond people just ‘kicking the tires’, and turn into active installations.

"My own estimate is that of those 60,000, around 6,000 are new, active installations," said Mickos. "Even that is a staggering volume."

Seeding the market with a free, open source database is clearly no longer a challenge for the originally Swedish firm. But how many of the 6,000 eventually buy into MySQL Enterprise, with the monitoring tools, support, and subscription that actually lines Sun's pockets?

"I would say the ratio is between one in one hundred and one in one thousand," said Mickos. "If you look at averages you get useless information, because we might get 10 million downloads in China and we know almost none of them will pay anything in the near future. In the web 2.0 space, most will pay. In countries with a high GDP, many will pay, and in those with a low economy absolutely nobody will pay today."

But even for those who use the software but do not pay anything, Mickos argues that the firm still benefits. "We get something before the user starts paying," said Mickos. "We get bug fixes, articles, community input. We have more engineering resource than IBM puts into DB2, even though they have 20 times more resources in-house."

Sun, of course, is hoping that as many customers as possible get their wallets out and pay for the Enterprise edition and its support package. Compared to MySQL's 200 field sales people, Sun has 17,000 at its disposal to sell the open source database.

But as Sun CEO Jonathan Schwartz explained on his blog, there is also the belief that many MySQL punters will also be looking for hardware and services, and he hopes they can be encouraged to choose Sun when that time comes.

But do people downloading MySQL really also procure their hardware in tandem? "When customers come to us for the first time there is no denying they come to us because we're free," said Mickos. "And at that stage they will run it on the cheapest, crappiest hardware they can find because they have no money. There's no business for any hardware firm there; not even Dell would make any money there. But although they start frugal, for many of them they then scale to the next level and that's when they start buying stuff."

Mickos said Sun should be able to sell hardware and services to non-paying customers as they start to scale up their use of the database.

"It's a marathon, not a sprint," he said. "If a 15-year-old downloads MySQL now, when do we get our money? In about 15 years' time when he is head of IT at a company and he loves MySQL. But in many cases it will happen sooner than that."

"In open source we say fail fast, scale fast. Many web 2.0 ideas will fail, but when Google or Facebook [two of MySQL's biggest customers] get it right they suddenly need to scale like crazy," Mickos said. "Open source is the only model where they can scale fast on exactly the same code base; it's the same product. All of the [commercial] database players have free versions, but when you need to scale you need a slightly different version [of the database]."

If waiting up to 15 years to see tangible revenue from these huge numbers of free downloads -- the firm has seen over 10 million downloads of its software so far -- sounds like a message that would have been hard to sell to shareholders had MySQL continued with its planned IPO, instead of being acquired, Mickos is unbowed.

"I would have no problem explaining it to shareholders," he said. "Number one is employees, number two customers, and number three shareholders."

While some observers baulked at the price tag for MySQL of $1bn when its current revenue is a mystery, I believe this was a particularly shrewd move by Sun CEO Schwartz and his team.

In the open source LAMP stack (Linux, Apache, MySQL, PHP/Perl/Python), MySQL is the only element owned by a specific company and for which there are no other realistic open source competitors.

With Sun now pinning its flag to the open source movement (note the open sourcing of Java and Solaris), it is probably the biggest contributor to open source of any commercial vendor, and it has the most to gain from widespread open source adoption as a result.

So while Sun's shareholders may question the logic of spending $1bn on an open source technology, being involved in so many IT projects right from their inception could open doors to Sun that it would not formerly have known existed.

In many cases, as Mickos candidly conceded, it may be up to 15 years before many of the MySQL faithful turn into paying subscribers, or buy into Sun hardware, services or other software. But even if it's a marathon rather than a sprint, the history books are likely to recall that Sun's MySQL purchase was key to its continued relevance in enterprise IT.

I’ll be doing some more blogs about my interview with Mickos in coming days so check back from time to time or add my open source blog to your RSS reader if that’s something likely to float your boat, so to speak.

Posted by Jason Stamper on March 6, 2008 10:18 AM

[Feb 5, 2008] Entrance a program for browsing MySQL databases

Community edition is free.

Entrance is a program for browsing MySQL databases. It includes a "match box" search facility that is similar to the Mac's Spotlight. Entrance can create charts based on query results, and these charts can be zoomed and panned. Entrance also supports an innovative feature called "data painting" that allows users to select and paint data points interactively.

[Feb 5, 2008] Secrets of the MySQL Client Command Line By W. Jason Gilmore

February 5, 2008 | developer.com

... ... ...

Changing the Client Prompt

If I had a dollar for every time I ran show tables just as a reminder of which database was currently selected, I'd be writing this article from an exotic beach locale. The fact is, when regularly jumping among an IDE, shell prompt, and MySQL client prompt it can be very easy to forget your spot. Eliminate this problem in its entirety by changing MySQL's prompt by using the prompt command:

mysql>prompt mysql (\d)>

Once executed, the currently selected database will appear in the prompt, like so:

mysql (corporate)>

It's also easy to lose track of which database server you're logged in to, not to mention which account you're using. To fix this, modify your prompt using the \u and \h options:

mysql>prompt mysql (\u@\h)>

This produces a prompt similar to the following:

mysql (root@www.wjgilmore.com)>

To render the change permanent, add the command to your .my.cnf file like so:

[mysql] prompt=mysql \\d>

The database, user, and host options are just a few of many available to you. See the MySQL documentation for more information.

... ... ...

Rendering Query Results in Vertical Format

Easily reviewing SELECT query results in a shell window can be particularly difficult when a table consists of numerous columns. Remedying this inconvenience is easily done by appending the \G switch to the query:

mysql>SELECT * from users WHERE id=1\G

Executing this query with the \G switch attached formats the output like so:

mysql test>select * from users where id=1\G
*************************** 1. row *********
       id: 1
     name: Jason
    email: jason@example.com
telephone: (614)999-9999
     city: Columbus
1 row in set (0.00 sec)

Create a CSV File From Query Results

If you're using a database professionally, chances are a colleague has asked you to dump data from the database into an Excel file to perform further analysis. Did you know you can modify a SELECT query to do all of the CSV formatting for you, and place the data in a textfile? All you need to do is identify how the fields and lines should be terminated. For example, to dump a table named users to a CSV file named users.csv, execute this command:

mysql>SELECT * FROM users INTO OUTFILE '/home/jason/users.csv'
   FIELDS TERMINATED BY '\t\' ->LINES TERMINATED BY '\n';

[Jan 24, 2008] Zmanda Recovery Manager for MySQL

A flexible and robust backup and recovery solution for MySQL server.

Zmanda Recovery Manager (ZRM) for MySQL simplifies life of a database administrator who needs an easy to use yet flexible and robust backup and recovery solution for MySQL server. With ZRM for MySQL, you can schedule full and incremental logical or raw backups of your local or remote MySQL database, perform a backup that is the best match for your storage engine and your MySQL configuration, get encrypted and compressed backups, get email notification about the status of your backups (including RSS feeds), monitor and get backup reports, and recover a database easily to any point in time or to any particular transaction.

[Jan 24, 2008] freshmeat.net Project details for MySQL Backup for lazy sysadmins

mysqlblasy is a Perl script for automating MySQL database backups. It uses "mysqldump" for dumping mysql databases to the files sytem. It was written with automated usage in mind. For example, it is silent during operation, and only produces noise on errors/problems. It rotates backups automatically to prevent the backup disk from getting full when the administrator is on vacation (or is lazy).

[Jan 24, 2008] MySQL Backup

"A Perl script to safely backup MySQL databases and tables."

MySQL Backup is a Perl script that uses mysqlshow to grab the database names and "show tables" to grab the table names for a user's account, and then uses mysqldump to save the data in a subdirectory named in the script. It then tars and gzips the files, using the date and time for the file name. It can be run from cron on a daily basis. It removes old files and has an option to email the gzip file to an admin, and/or FTP the file to a remote server. It also has options to use "select data into outfile" or a regular "select" for users who can't use mysqldump. It supports LARGE sets of databases and tables.

[Jan 8,  2008] External Language Stored Procedures for MySQL 1.2761  by Antony T Curtis

About: External Language Stored Procedures for MySQL is a patch for MySQL 6.0 to provide support for stored procedures written in an external languages. Stored procedures are declared in SQL using SQL standards compliant syntax. Additional stored procedure languages are implemented as plug-ins to be installed at run time into the server. Currently, plugins for Java and Perl are implemented, as well as support for declaring XML-RPC requests as stored procedures.

Changes: The build of the psm_perl plugin has been modified so that it will compile with Perl 5.8.8, as shipped with MacOS 10.5 Leopard, which is already built with thread support. The code base has been refreshed with the current MySQL 6.0 repository.

[ Apr 15, 2007] phpMyAdmin 2.10.1-rc1 (Development)

About: phpMyAdmin is a tool written in PHP intended to handle the administration of MySQL over the Web. It can create, rename, and drop databases, create/drop/alter tables, delete/edit/add fields, execute any SQL statement, manage keys on fields, create dumps of tables and databases, export/import CSV data and administrate one single database and multiple MySQL servers. 

Linux.com Create a Web-based interface for MySQL databases in a flash with phpMyEdit By: Dmitri Popov

If you've ever worked with MySQL databases, you are probably familiar with phpMyAdmin, a PHP-based tool that allows you to create and manage MySQL databases via a browser. It is an indispensable tool for anyone building a PHP/MySQL-based Web application. But while phpMyAdmin eases the task of creating and managing the back end of your Web application, it is of no help when it comes to designing a Web-based interface. To simplify creating PHP-based front ends, try phpMyEdit, an ingenious piece of software that can generate a functional Web interface in a matter of minutes -- no PHP programming skills required. Although phpMyEdit hides the complexity of generating a PHP-based interface, it still offers an easy-to-use yet powerful mechanism to customize virtually any aspect of the created front end.

MySQL Basic Configuration and Startup for Solaris 10 - Articles and FAQs

Linux-BSD Gangsters - Content - Linux - Guide-how-to - OpenOffice Base And MySQL

Do you have an existing MySQL database with valuable data in it? Would you like to be able to access it with OpenOffice.org 2.0's new Base database application? Well, it's not that hard to do, and Base is a great program. With it you can

(1) create new tables for your data and/or change them as your needs change;

(2) maintain indexes on your database tables to make data access faster and easier;

(3) view the tables in an editing grid and add, change, and delete records;

(4) use the Report Wizard to produce impressive reports from your data; and

(5) use the Form Wizard to create 'instant' database applications.

In addition to all that, you can also perform simple (single column) or complex (multi-column) sorts; (1) view subsets of your data with simple (one-click) or complex (logical query) filters; (2) create powerful queries to show your data in new ways, including summaries and multi-table views; (3) produce reports in a wide variety of formats using the Report AutoPilot. You can read more here.

So, you already have your MySQL database running. How do you get Base to connect to it so you can do all these wonderful things? Well - I will assume you have OpenOffice.org 2.0 installed on your system. If not, do so first using whatever package management system/method you prefer for your linux distro. Once you have OpenOffice.org 2.0 installed and running, you will need to install an ODBC connector so that Base can use it to connect to you MySQL database. You will want to install 'myodbc' on your system.

Once you have 'myodbc' installed, make sure your MySQL server is running on your system. There are two (2) files you must edit to first test the system, and then to get things working in Base. First, edit /etc/odbc.ini. The file is initially empty. Edit it to look like this:


[MySQL-test]
Description = MySQL database test
Driver = MySQL
Server = localhost
Database = test
Port = 3306

and save the file. Next, edit /etc/odbcinst.ini. The file is initially empty. Edit it to look like this:

[MySQL]
Description = ODBS for MySQL
Driver = /usr/lib/libmyodbc3.so
FileUsage = 1

and save the file.

[Nov 7, 2005] BigAdmin Feature Article Configuring Sun Java System Identity Manager as a Service for the Solaris 10 OS

The integrated MySQL (RDBMS) Solaris package provides a maintenance script for starting, stopping and restarting. This script is located in /etc/sfw/mysql and is called mysql.server. The default Solaris 10 installation doesn't reference this script during the traditional start-up process. The system administrator may have copied (or linked) this script into the traditional start-up directories. You will need to remove any MySQL start-up scripts in /etc/rc*.d and /etc/init.d.

Use the following commands to check for any existing mysql start-up scripts:

# find /etc/rc* /etc/init.d | grep -i mysql
/etc/rc0.d/K01mysql
/etc/rc1.d/K01mysql
/etc/rc2.d/K01mysql
/etc/rc3.d/S99mysql
/etc/init.d/mysql

If the above command finds start-up scripts (as shown above), they need to be removed or relocated. Remove the scripts from the /etc/rc* directories:

# rm /etc/rc0.d/K01mysql
# rm /etc/rc1.d/K01mysql
# rm /etc/rc2.d/K01mysql
# rm /etc/rc3.d/S99mysql

Relocate the script in /etc/init.d to an alternate location:

# mv /etc/init.d/mysql /var/tmp

Create SMF mysql Manifest

SMF in the Solaris 10 OS has established a directory structure for storing SMF service manifests. The base directory for SMF manifests is /var/svc/manifest. For organizational purposes, as root, create a new directory called database under the application directory. Create a text file called mysql.xml and insert the XML manifest that defines the MySQL service (see below). The mysql manifest XML file needs to be copied into this directory.

# mkdir /var/svc/manifest/application/database
# vi mysql.xml (insert the manifest below)
# cp  mysql.xml /var/svc/manifest/application/database

The complete manifest for the mysql service is listed in the following example.

<?xml version="1.0"?>
<!DOCTYPE service_bundle SYSTEM "/usr/share/lib/xml/dtd/service_bundle.dtd.1">
<!--
    Copyright 2005 Sun Microsystems, Inc.  All rights reserved.
    Use is subject to license terms.
    MySQL.xml : MySQL manifest, Scott Fehrman, Systems Engineer
    updated: 2005-09-16
-->

<service_bundle type='manifest' name='MySQL'>
<service name='application/database/mysql' type='service' version='1'>

   <single_instance />

   <dependency
      name='filesystem'
      grouping='require_all'
      restart_on='none'
      type='service'>
      <service_fmri value='svc:/system/filesystem/local' />
   </dependency>

   <exec_method 
      type='method' 
      name='start'
      exec='/etc/sfw/mysql/mysql.server start'
      timeout_seconds='120' />

   <exec_method 
      type='method' 
      name='stop'
      exec='/etc/sfw/mysql/mysql.server stop'
      timeout_seconds='120' />

   <instance name='default' enabled='false' />

   <stability value='Unstable' />

   <template>
      <common_name>
         <loctext xml:lang='C'>MySQL RDBMS 4.0.15</loctext>
      </common_name>
      <documentation>
         <manpage title='mysql' section='1' manpath='/usr/sfw/share/man' />
      </documentation>
   </template>

</service>
</service_bundle>

Import the Manifest into the Repository

Placing the XML file into the SMF directory does not mean the mysql service is ready for use. The manifest needs to be validated and imported into the Solaris 10 SMF Repository. Validate and import the XML file into the Repository with the svccfg (Service Configuration) command:

# svccfg validate /var/svc/manifest/application/database/mysql.xml
# svccfg import /var/svc/manifest/application/database/mysql.xml

Check the Service

The service will automatically start when the Solaris 10 OS boots if the service is enabled. The XML file contains the line <instance name='default' enabled='false' />, which means the service will not be enabled when it's imported into the Repository. Check the status of the service with the svcs command:

Status of the service:

# svcs mysql
STATE         STIME    FMRI
disabled      12:45:34 svc:/application/database/mysql:default

Converting Apache/Tomcat into the idmgr Service

Notice: The Solaris 10 OS contains two versions of Apache; "apache" and "apache2". The "apache" Solaris package uses legacy start-up and shut-down scripts. This example will convert the "apache" package into a SMF service. The "apache2" Solaris package is already implemented as a SMF service. This example will not involve the "apache2" service.

Remove Legacy Start-Up Process

The integrated Solaris package containing Apache/Tomcat (web server/JSP/Servlet container) has scripts for starting, stopping, and restarting itself. You will need to remove and/or relocate the Apache/Tomcat start-up scripts in /etc/rc*.d and /etc/init.d.

Use the following commands to check for any existing mysql start-up scripts:

# find /etc/rc* /etc/init.d | grep -i apache
/etc/rc0.d/K16apache 
/etc/rc1.d/K16apache 
/etc/rc2.d/K16apache
/etc/rc3.d/S50apache 
/etc/rcS.d/K16apache 
/etc/init.d/apache 

The scripts in the /etc/rc* directories need to be deleted:

# rm /etc/rc0.d/K16apache
# rm /etc/rc1.d/K16apache
# rm /etc/rc2.d/K16apache
# rm /etc/rc3.d/S50apache
# rm /etc/rcS.d/K16apache

The idmgr service will use the script in the /etc/init.d directory for starting and stopping the service. Use the following command to move and rename the script:

# mv /etc/init.d/apache /etc/apache/apache.sh

Create the SMF idmgr Manifest

SMF in the Solaris 10 OS has established a directory structure for storing SMF service manifests. The base directory for SMF manifests is /var/svc/manifest. For organizational purposes, as root, create a new directory called web under the application directory. Create a text file called idmgr.xml and insert the XML manifest that defines the Identity Manager service (see below). The idmgr manifest XML file needs to be copied into this directory.

# mkdir /var/svc/manifest/application/web
# vi idmgr.xml (insert the manifest below)
# cp idmgr.xml /var/svc/manifest/application/web 

The complete manifest for the idmgr service is listed in the following example. 

<?xml version="1.0"?>
<!DOCTYPE service_bundle SYSTEM "/usr/share/lib/xml/dtd/service_bundle.dtd.1">
<!--
    Copyright 2005 Sun Microsystems, Inc.  All rights reserved.
    Use is subject to license terms.
    IdMgrApache.xml : Identity Mgr manifest, Scott Fehrman, Systems Engineer
    updated: 2005-09-16
-->

<service_bundle type='manifest' name='Identity Manager'>
<service name='application/web/idmgr' type='service' version='1'>

   <single_instance />

   <dependency
      name='mysql'
      grouping='require_all'
      restart_on='restart'
      type='service'>
      <service_fmri value='svc:/application/database/mysql' />
   </dependency>

   <exec_method 
      type='method' 
      name='start'
      exec='/etc/apache/apache.sh start'
      timeout_seconds='120' />

   <exec_method 
      type='method' 
      name='stop'
      exec='/etc/apache/apache.sh stop'
      timeout_seconds='120' />

   <instance name='default' enabled='false' />

   <stability value='Unstable' />

   <template>
      <common_name>
         <loctext xml:lang='C'>SJS Identity Manager 5.0-sp3</loctext>
      </common_name>
      <documentation>
         <manpage title='apache' section='1' manpath='/usr/man' />
      </documentation>
   </template>

</service>
</service_bundle>

Import the Manifest into the Repository

Placing the XML file into the directory does not mean the idmgr service is ready for use. The manifest needs to be validated and imported into the Solaris 10 SMF Repository. Validate and import the XML file into the Repository with the svccfg (Service Configuration) command:

# svccfg validate /var/svc/manifest/application/web/idmgr.xml
# svccfg import /var/svc/manifest/application/web/idmgr.xml

Check the Service

The service will automatically start when the Solaris OS boots if the service is enabled. The XML file contains the line <instance name='default' enabled='false' /> , which means the service will not be enabled when it's imported into the Repository. Check the status of the service with the svcs command:

Status of the service:

# svcs idmgr 
STATE          STIME    FMRI
disabled       14:26:42 svc:/application/web/idmgr:default

List the services that idmgr depends upon:

# svcs -d idmgr
STATE          STIME    FMRI
disabled       12:45:34 svc:/application/database/mysql:default

[Oct 26, 2005] BigAdmin - Submitted Tech Tip Configuring MySQL to Use With Service Management Facility (SMF) by William Pool,

The Solaris 10 OS uses the Service Management Facility (SMF) to handle services. Traditional means like /etc/rc?.d scripts still work, but as a legacy means. (For more information on SMF, see the References section.)

To take advantage of the SMF in the Solaris 10 OS using MySQL, follow these steps.

Note: Read the scripts and "change" the path of MySQL or MySQL's data-directory accordingly!

mysqlsolaris

II. Download and install MySQL
There are several ways to install MySQL, including building from source or installing a binary package.  These instructions detail installing from binary packages, which we believe is simplest.  Adapt these instructions accordingly if you are using a different download method.

1. MySQL server:  from www.mysql.com/downloads/mysql-4.0.html, skip down to the Solaris section, and click on "Pick a mirror" for the appropriate "standard" distribution for your Solaris box.  Download the MySQL server.  You should get a file with a name like "mysql-standard-4.0.14-sun-solaris2.8-sparc.tar.gz".
2. ODBC driver:  from www.mysql.com/downloads/api-myodbc-3.51.html, skip down to the Solaris section, and click on "Pick a mirror" for the appropriate distribution for your Solaris box.  Download the ODBC driver.  You should get a file with a name like "MyODBC-3.51.06-sun-solaris2.8-sparc.tar.gz".
3. Install the server as root.  Gunzip and untar the server kit.  Follow the instructions in the file INSTALL-BINARY.
4. Install the ODBC driver as root.  Gunzip and untar the ODBC driver kit.  Follow the instructions in the file INSTALL-BINARY.
5. Start the server daemon.  Make sure it gets started when your box boots.  Most MySQL distributions contain a boot-time file, such as /etc/init.d/mysql.  Otherwise you can adapt your own to run the MySQL "bin/mysqld_safe" script.

III. Secure MySQL
There is a great deal of literature about how to properly secure MySQL depending on your intended use and circumstances.  The MySQL Reference Manual is an excellent place to start. 

In these instructions, we assume that MySQL should only be accessible from the local host (not by other clients over the network), and the absolute minimum of 'root' level access.

1. Always start mysql for local access only.  As root, edit the mysql startup file (/etc/init.d/mysql or other), and find the line that actually runs mysqld_safe.  Add the "--skip-network" option.  The result should look something like:

    $bindir/mysqld_safe --datadir=$datadir --pid-file=$pid_file --skip-networking &
   

   Stop and restart mysql:

      /etc/init.d/mysql stop
      /etc/init.d/mysql start

    

2. Limit root and non-essential access.  Change the default (empty) root password.  Delete any non-localhost 'root' users, and any 'blank' users.

   mysqladmin -u root password new_password
   
      mysql -u root --password=new_password
      mysql> use mysql;
      mysql> delete from user where host = '%';
      mysql> delete from user where user = '';
      mysql> delete from db where user = '';
      mysql> select user, host from user;
   

   If you see any 'root' users other than root@localhost, delete them -- e.g.

      mysql> delete from user where user = 'root' and host='myhost.com'
   

   Flush the privileges, and you're done.

    mysql> flush privileges;
    mysql> quit

IV. Install ODBC Driver Manager
We recommend the "platform independent ODBC" package from www.iodbc.org.  Again, we recommend a specific series of instructions, but adapt as appropriate for your server.

1. Click on "iODBC Driver Manager" and download the full source archive (e.g. libiodbc-3.0.6.tar.gz).
    
2. Compile and install it:

   gunzip  libiodbc-3.0.6.tar.gz
   tar xvf libiodbc-3.0.6.tar
   cd      libiodbc-3.0.6
   /configure
   make
   make install

3. Set up the odbc.ini file.  Copy the MySQL odbc.ini file from the MyODBC driver kit to /usr/local/etc.  (If you already have a /usr/local/etc/odbc.ini file, add the MySQL file contents to your file.)
    
4. Define the environment variables for the ODBC driver manager (either system-wide, or just for the unix 'root' and 'caucus' userids):

      export ODBCINI=/usr/local/etc/odbc.ini
      export ODBCSYSINI=/usr/local/etc

5. Test it.  You should be able to run /usr/local/bin/odbctest, and then respond to the prompt with

     DSN=myodbc3;USER=root;PASSWORD=new_password

   and perform simple SQL queries.  If you run into problems, consult the text files in the directory where you got the MySQL odbc.ini file for further information.

[Oct 24, 2005] MySQL 5.0 Reference Manual 2.12.3 Solaris Notes

On Solaris, you may run into trouble even before you get the MySQL distribution unpacked, as the Solaris tar cannot handle long filenames. This means that you may see errors when you try to unpack MySQL.

If this occurs, you must use GNU tar (gtar) to unpack the distribution. You can find a precompiled copy for Solaris at http://dev.mysql.com/downloads/os-solaris.html.

Sun native threads work only on Solaris 2.5 and higher. For Version 2.4 and earlier, MySQL automatically uses MIT-pthreads. See Section 2.8.5, “MIT-pthreads Notes”.

If you get the following error from configure, it means that you have something wrong with your compiler installation:

checking for restartable system calls... configure: error can not
run test programs while cross compiling

In this case, you should upgrade your compiler to a newer version. You may also be able to solve this problem by inserting the following row into the config.cache file:

ac_cv_sys_restartable_syscalls=${ac_cv_sys_restartable_syscalls='no'}

If you are using Solaris on a SPARC, the recommended compiler is gcc 2.95.2 or 3.2. You can find this at http://gcc.gnu.org/. Note that egcs 1.1.1 and gcc 2.8.1 do not work reliably on SPARC.

The recommended configure line when using gcc 2.95.2 is:

CC=gcc CFLAGS="-O3" \
CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti" \
./configure --prefix=/usr/local/mysql --with-low-memory \
    --enable-assembler

If you have an UltraSPARC system, you can get 4% better performance by adding -mcpu=v8 -Wa,-xarch=v8plusa to the CFLAGS and CXXFLAGS environment variables.

If you have Sun's Forte 5.0 (or newer) compiler, you can run configure like this:

CC=cc CFLAGS="-Xa -fast -native -xstrconst -mt" \
CXX=CC CXXFLAGS="-noex -mt" \
./configure --prefix=/usr/local/mysql --enable-assembler

To create a 64-bit binary with Sun's Forte compiler, use the following configuration options:

CC=cc CFLAGS="-Xa -fast -native -xstrconst -mt -xarch=v9" \
CXX=CC CXXFLAGS="-noex -mt -xarch=v9" ASFLAGS="-xarch=v9" \
./configure --prefix=/usr/local/mysql --enable-assembler

To create a 64-bit Solaris binary using gcc, add -m64 to CFLAGS and CXXFLAGS and remove --enable-assembler from the configure line.

In the MySQL benchmarks, we obtained a 4% speed incrase on UltraSPARC when using Forte 5.0 in 32-bit mode, as compared to using gcc 3.2 with the -mcpu flag.

If you create a 64-bit mysqld binary, it is 4% slower than the 32-bit binary, but can handle more threads and memory.

When using Solaris 10 for x86_64, you should mount any filesystems on which you intend to store InnoDB files with the forcedirectio option. (By default mounting is done without this option.) Failing to do so will cause a significant drop in performance when using the InnoDB storage engine on this platform.

If you get a problem with fdatasync or sched_yield, you can fix this by adding LIBS=-lrt to the configure line

For compilers older than WorkShop 5.3, you might have to edit the configure script. Change this line:

#if !defined(__STDC__) || __STDC__ != 1

To this:

#if !defined(__STDC__)

If you turn on __STDC__ with the -Xc option, the Sun compiler can't compile with the Solaris pthread.h header file. This is a Sun bug (broken compiler or broken include file).

If mysqld issues the following error message when you run it, you have tried to compile MySQL with the Sun compiler without enabling the -mt multi-thread option:

libc internal error: _rmutex_unlock: rmutex not held

Add -mt to CFLAGS and CXXFLAGS and recompile.

If you are using the SFW version of gcc (which comes with Solaris 8), you must add /opt/sfw/lib to the environment variable LD_LIBRARY_PATH before running configure.

If you are using the gcc available from sunfreeware.com, you may have many problems. To avoid this, you should recompile gcc and GNU binutils on the machine where you are running them.

If you get the following error when compiling MySQL with gcc, it means that your gcc is not configured for your version of Solaris:

shell> gcc -O3 -g -O2 -DDBUG_OFF  -o thr_alarm ...
./thr_alarm.c: In function `signal_hand':
./thr_alarm.c:556: too many arguments to function `sigwait'

The proper thing to do in this case is to get the newest version of gcc and compile it with your current gcc compiler. At least for Solaris 2.5, almost all binary versions of gcc have old, unusable include files that break all programs that use threads, and possibly other programs as well.

Solaris does not provide static versions of all system libraries (libpthreads and libdl), so you cannot compile MySQL with --static. If you try to do so, you get one of the following errors:

ld: fatal: library -ldl: not found
undefined reference to `dlopen'
cannot find -lrt

If you link your own MySQL client programs, you may see the following error at runtime:

ld.so.1: fatal: libmysqlclient.so.#:
open failed: No such file or directory

This problem can be avoided by one of the following methods:

• Link clients with the -Wl,r/full/path/to/libmysqlclient.so flag rather than with -Lpath).
• Copy libmysqclient.so to /usr/lib.
• Add the pathname of the directory where libmysqlclient.so is located to the LD_RUN_PATH environment variable before running your client.

If you have problems with configure trying to link with -lz when you don't have zlib installed, you have two options:

• If you want to be able to use the compressed communication protocol, you need to get and install zlib from ftp.gnu.org.
• Run configure with the --with-named-z-libs=no option when building MySQL.

If you are using gcc and have problems with loading user-defined functions (UDFs) into MySQL, try adding -lgcc to the link line for the UDF.

If you would like MySQL to start automatically, you can copy support-files/mysql.server to /etc/init.d and create a symbolic link to it named /etc/rc3.d/S99mysql.server.

If too many processes try to connect very rapidly to mysqld, you should see this error in the MySQL log:

Error in accept: Protocol error

You might try starting the server with the --back_log=50 option as a workaround for this. (Use -O back_log=50 before MySQL 4.)

Solaris doesn't support core files for setuid() applications, so you can't get a core file from mysqld if you are using the --user option.

[PDF] SNORT-ACID install on Solaris9 ACID installation includes MySQL and Apache installation

freshmeat.net Project details for MySQL

by ctools - Aug 5th 2005 03:11:50

> Can not compile on Solaris. Has anybody succeded ?


I have found the useful mysql manual with Solaris installation notes. Please read the 2.6.3 Solaris Notes item. I hope this manual will help you

MySQL 5.0 Reference Manual 2.12.3.1 Solaris 2.7-2.8 Notes

Using Solaris 9, I installed the gcc compiler available on the Software Companion CD (installed under /opt/sfw). I also set LD_LIBRARY_PATH to /opt/swf/lib, per the installation notes for Solaris.

The compilation phase did not work, producing some error (which I've now lost, but it had to do with processing within the readline subdirectory).

I then downloaded the gcc compiler from www.sunfreeware.com, installed it, and set LD_LIBRARY_PATH to /usr/local/lib.

Configured as follows:
CC=gcc CFLAGS="-O3" \
CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti" \
./configure --prefix=/usr/local/mysql --with-low-memory \
--enable-assembler

Much better.

Notice that I did not include the -m64 flags in CFLAGS or CXXFLAGS. They may have worked, but I didn't try them.

BTW, I wouldn't need to do this compilation at all if MySQL binary distributions for Solaris came with lib/libmysqlclient.so
 

Posted by Charles Richmond on November 1 2004 9:29pm

[Delete] [Edit]

Solaris 8 does have the libmysqlclient .so and .a libs in the binary mysql provided.

# ls lib/mysql/libmysqlclient*
lib/mysql/libmysqlclient.a lib/mysql/libmysqlclient.so.10
lib/mysql/libmysqlclient.la lib/mysql/libmysqlclient.so.10.0.0
lib/mysql/libmysqlclient.so
#
# uname -a
SunOS ck 5.8 Generic_117350-12 sun4u sparc SUNW,UltraAX-i2
#
 

Posted by Darryl Friesen on November 18 2004 2:29pm

[Delete] [Edit]

Not true. The binary package I just downloaded did NOT include the .so files:

# pwd
/usr/local/mysql-standard-4.1.7-sun-solaris2.8-sparc

# ls lib/libmysqlclient*
lib/libmysqlclient.a lib/libmysqlclient_r.a
 

dotvoid.com - MySQL 4, Apache 1.3 and PHP 4.3 on Solaris x86

2004-09-02 03:49 by dotvoid
Updated 2005-08-19 07:23 .
Copyright © http://www.dotvoid.com

Yesterday I decided it was time to get a proper PHP 4 environment configured on my new machine running the x86 version of Solaris 9. I know going outside Windows or Linux with PHP sometimes can be tricky. I haven't used PHP on Solaris since my time at AOL in Dublin three years ago, so I decided to write down exactly how, what and why I did things. I expected it to be a bit simpler then last time as I planned to use Apache rather than IPlanet (or whatever the name is today, it seems to change at least once a week). So my plan was to install a pretty standard LAMP environment. That is without the L ofcourse.

Preparations

The first thing I had to do was to download the different GNU packages needed to compile MySQL, PHP and many other open source projects on Solaris. These packages can all be found on Sunfreeware.com. The packages I installed, by default under /usr/local,  were:

• gcc

• gmake

• flex

• bison

• m4

• autoconf

• automake

• gzip

• tar

• GNU sed

• libiconv

All packages are easy to install using pkgadd. Care needs to be taken to set the system paths correctly after the installation. I used the command crle -u -l /usr/local/lib to add the lib directory to the system defaults. Then I added /usr/local/bin first in my PATH to make sure GNU versions take precedence if there are any conflicts. For example the package m4 could be found on the system already. I also appended /usr/ccs/bin last to the PATH as there are programs there needed for compilation and linking.

MySQL

Having the environment setup as I wanted it I set my eyes on installing MySQL. I couldn't find an official binary package for Solaris 9 x86 so I had to build it myself. I downloaded the source from mysql.com, quickly unpacked it and failed miserably at compiling it.

A quick research using Google gave me the answer. The command mkheaders refused due to paths not being found. So additionally I had to create a softlink as the paths for some reason differed. After creating the symlink I could generate the gcc header files. The exact commands issued are as below.
 

# ln -s /usr/local/lib/gcc /usr/local/lib/gcc-lib
# cd /usr/local/libexec/gcc/i386-pc-solaris2.9/\
  3.4.1/install-tools
# ./mkheaders

Then I tried compiling MySQL again. This time, everything could be done by the book. For sake of convenience - here's a near copy from the MySQL manual.

# ./configure --prefix=/usr/local/mysql
# make && make install
# cp support-files/my-medium.cnf /etc/my.cnf

# cd /usr/local/mysql
# groupadd mysql
# useradd -g mysql mysql
# chown -R root .
# chown -R mysql var
# chgrp -R mysql .

The only thing left then is to start the MySQL daemon and set the root password.

# bin/mysqld_safe --user=mysql
# bin/mysqladmin -u root password XXXXXX
# bin/mysqladmin -u root -h myhost password XXXXXX

Installing gd

I need gd for, among other things, thumbnail generation. As I wanted to download and compile as little as possible a quick check revealed that freetype, libjpeg, libpng and libiconv already existed in my default Solaris 9 installation. The libraries can be found under the /usr/sfw directory.

I wanted the newest version of gd as GIF support is now added again after being removed for several years due to Unisys suddenly starting do demand licensing fees for their LZW patent a few years ago. So I could not use the slightly older gd available as a package at sunfreeware.

I downloaded version 2.0.28 and tried to compile it. However, I could not get it to compile using the libiconv already installed. Instead I had to download libiconv from sunfreeware and install it under /usr/local. The following configure command was what I ended up with to get gd to compile.

# ./configure \
  --prefix=/usr/local \
  --with-freetype=/usr/sfw \
  --with-png=/usr/sfw \
  --with-jpeg=/usr/sfw \
  --with-libiconv-prefix=/usr/local

After that compiling the package was a breeze.

# make && make install

On to apache

Apache must be the easiest application around to build on different platforms. I have never experienced any problems building a stable apache release on any unix platform. I did once have problems getting it to run on NT 4 when the windows version still was marked unstable. I guess that could be expected though. So creating a standard build of apache is very straight forward.

# ./configure --prefix=/usr/local/apache-1.3.31 \
  --enable-module=so
# make && make install

PHP - finally

Compiling PHP became, as it turned out, a bit tricky becaused of my decision to enable gd support. The reason was that the PHP configure script didn't list libiconv when checking that all the gd functions were correct. The configure script just said "no" to all the gd functions and refused to create the makefiles. After awhile I looked through the generated config.log. This revealed that what really failed was not actually gd. When loading gd to check what functions were available it failed to find symbols from libiconv. This must either be a bug in the configure script or more probable a problem with my Solaris 9 environment and the library paths.

So prior to running the configure script I had to manually amend the configure script and add -liconv in all places where -lgd could be found. That did the trick and the script happily trotted along as it should.

# ./configure \
  --with-apxs=/usr/local/apache-1.3.31/bin/apxs \
  --with-mysql=/usr/local/mysql/ \
  --with-gd=/usr/local \
  --with-freetype-dir=/usr/sfw \
  --enable-gd-native-ttf \
  --with-png-dir=/usr/sfw \
  --with-zlib-dir=/usr/sfw \
  --with-jpeg-dir=/usr/sfw

# make && make install

After that I added the directives needed in httpd.conf to enable PHP4. Actually I only had to add the second line below as the LoadModule directive was already present.

[Oct 20, 2004] Compiling MySQL on Solaris Machines

MORE RECENT NOTE: This document is no longer relevant when compiling modern versions of MySQL 3.23. Also, the advent of MySQL 4 has done well to take care of almost all compiling concerns on Solaris. Kudos to the MySQL team for a great product and support.

Preface: I have used this technique to compile MySQL on Sparc Solaris machines running 2.4, 2.5.1, 2.6, and 7, as well as Intel Solaris machines running Solaris 8. Make sure you're using at least gcc 2.95.2 (2.95.3 will work, also.)

NOTE: As of a few versions back, none of the cache file editing described within is unnecessary. As of 3.23.something, (including the latest version,) you can just skip to the ./configure section below, and not worry about anything else. Simply put, you only have to worry about the parts below in GREEN

[Oct 20, 2004] Installing MySQL 3.23 - Solaris

The following article provides the steps necessary to successfully install the binary version of MySQL, release 3.23, on Solaris 2.8 and higher. These procedures should work as well on versions of Solaris as early as version 2.6.

I typically install MySQL in /usr/local/mysql as the UNIX root account. I then create a UNIX mysql account that will have access to manage databases.

Once you've installed MySQL, you will need to initialize the grant tables, start the server, and make sure that the server works okay. You may also elect to arrange for the server to be started and stopped automatically when your system starts up and shuts down.

Installing the grant tables is accomplished by running the mysql_install_db script. This creates the mysql database which will hold all database privileges, the test database which you can use to test MySQL, and also privilege entries for the user that run mysql_install_db and a root user (without any passwords).

[12/23/2003]  ONLamp.com MySQL Crash Course by John Coggeshall

Welcome to another issue of PHP Foundations. Last time, I wrapped up a discussion of an ongoing topic of security and good practices I've dubbed "PHP Paranoia." Today I will be changing gears yet again and discuss a cornerstone of any sizable web application — a database back end. Specifically, I will be spending the next few columns discussing the MySQL database, starting with today's article, which will serve as a crash course in SQL. For those of you who are familiar with relational databases such as MySQL, today's column may not be necessary for you; it contains no PHP code. Instead, I will introduce the concepts of relational databases and the language used to manipulate the data within them. Those of who haven't been exposed to relational databases, however, read on!   

freshmeat.net Project details for FutureSQL Web Database Administration Tool

FutureSQL is a Rapid Application Development web database administration tool written in Perl. FutureSQL allows one to easily setup config files to view, edit, delete and otherwise process records from a MySQL database. It uses a data dictionary, configuration files and html templates, and allows "pre-processing" and "post-processing" on both fields, records and operations. It allows multiple views and operations on a data set, including the use of joined tables for queries and reports. A demo application with most of the features is included.

Tutorials and Documentation

• Web Tools Review home
• Philip and Alex's Guide to Web Publishing -- a 17-chapter book
• Adding Collaboration to Your Web Site (without installing an RDBMS or hiring a dbadmin)
• Site Development (a structured approach to coordinating multiple people working on a mostly-static site)
• ArsDigita Server Architecture, a way to keep that beautiful site up and running reliably
• MySQL Documentation
• Turbolift
• Accessing MySQL from PHP by George Yohng
• [PDF] Searching with PHP and MySQL
• Build Your Own Database Driven Website Using PHP & MySQL Pt. 1
• Introduction to PHP

Recommended Links

MySQL Basic Configuration and Startup for Solaris 10 - Articles and FAQs

Freeware for Solaris

• Solaris 10
  • mysql-4.0.21 -- precompiled binaries

Installing MySQL 3.23 - Solaris

AMPS - Apache MySQL PHP Solaris 8 Installation

MySQL by T.c.X. DataKonsultAB  -- main site(the vendor)

• MySQL AB Sun Solaris Downloads far from being uptodate ;-)
  • MD5SUMS (424 bytes, modified: 2002/08/28)
  • gcc-2.7.2.3-solaris-2.6-include.tar.gz (196.3K, modified: 1997/12/09)
  • gmake-sparc.gz (128.0K, modified: 1997/12/20)
  • gtar-i386.gz (192.0K, modified: 1998/01/28)
  • gtar-solaris-4.1.4-sparc.gz (156.6K, modified: 1999/01/15)
  • gtar-sparc.gz (150.4K, modified: 1997/12/20)
  • mysql-3.22.25-latin2-solaris7-x86.pkg.tgz (3.0M, modified: 1999/09/04)
  • mysql-3.22.25-solaris7-x86.pkg.tgz (3.0M, modified: 1999/09/04)

    Faster ??? mirror: MySQL by T.c.X. DataKonsultAB

freshmeat.net Project details for MySQL

About: MySQL is a widely used and fast SQL database server. It is a client/server implementation that consists of a server daemon (mysqld) and many different client programs/libraries.

Changes: This is the first production level release of the MySQL 5.0 tree. Among the various major new features are Views (both read-only and updatable views), Stored Procedures and Stored Functions (using the SQL:2003 syntax), Triggers (row-level) as well as Server-side cursors (read-only, non-scrolling).

• Links  -- by the vendor

• mySQL Archives

PHP Integration

• PHP3 Manual MySQL Functions

• PHP_links

License

Licensing or When do I have-want to pay for MySQL

• The easiest way to pay for MySQL is to use the license form at TcX's secure server at https://www.tcx.se/license.htmy.
• ...you only have to pay if you are selling MySQL directly, selling a product which includes the MySQL server or installing and maintaining a MySQL server at some client site. You may not include MySQL in a distribution if you charge for some part of the distribution. For internal use, you do not have to pay us if you do not want to.
   
• There is no restriction in the number of clients that connects to a MySQL server or the number of MySQL servers that runs on one machine!
   
• You do not need a license to include client code in commercial programs. The client access part of MySQL is in the public domain. The command line client (mysql) includes parts that are under the GNU Public License (readline).
   
• We may add some additional functionality in the commercial version. The likely test candidate for this is the ability to create fast compressed read-only databases. The current server includes support to read such databases but not the packing tool used to create them. If we get enough revenue from support, we will probably release this under the same license as the other stuff.
   
• But if you like MySQL and want to encourage further development you are welcome to purchase a license or support.
   
• The MySQL server license for Linux

Papers

[Aug 19, 2004] Linux.com Securing MySQL By: Mike Peters

Thanks to its speed and stability, MySQL has earned a place on millions of servers worldwide. MySQL has a simple and effective security mechanism, but administrators must perform a few additional tasks to make a default installation truly secure. The measures we'll talk about below will enable you to better secure your database, but be sure to secure the underlying operating system too.

Installation

One key to better security is to run MySQL as its own user.

Create such a user and group with the commands:

# groupadd mysql
# useradd -c "MySQL Server" -d /dev/null -g mysql -s /bin/false mysql

Install MySQL in /usr/local/mysql: ./configure --prefix=/usr/local/mysql --with-mysqld-user=mysql --with-unix-socket-path=/tmp/mysql.sock --with-mysqld-ldflags=-all-static
make
su
make install
strip /usr/local/mysql/libexec/mysqld
scripts/mysql_install_db
chown -R root /usr/local/mysql
chown -R mysql /usr/local/mysql/var
chgrp -R mysql /usr/local/mysql

The configure option --with-mysqld-user=mysql enables MySQL to run as the mysql user. The --with-mysqld-ldflags=-all-static option makes it easier to chroot MySQL.

Copy the example configuration file from the MySQL source, support-files/my-medium.cnf, to /etc/my.cnf and set the appropriate permissions: chmod 644 /etc/my.cnf.

Once you have MySQL installed, test the installation. Start MySQL with /usr/local/mysql/bin/mysqld_safe & and log on as the root user, mysql -u root. If you see the MySQL prompt you know the database is running you can proceed to chroot it. If the installation is not working, examine the log files to find out what the problem is. Otherwise, shutdown the server: usr/local/mysql/bin/mysqladmin -u root shutdown

Chrooting MySQL

First, create the necessary directory structure for the database:

mkdir -p /chroot/mysql/dev /chroot/mysql/etc /chroot/mysql/tmp /chroot/mysql/var/tmp /chroot/mysql/usr/local/mysql/libexec /chroot/mysql/usr/local/mysql/share/mysql/english

Now set the correct directory permissions:

chown -R root:sys /chroot/mysql
chmod -R 755 /chroot/mysql
chmod 1777 /chroot/mysql/tmp

Once the directories are set up, copy the server's files:

cp /usr/local/mysql/libexec/mysqld /chroot/mysql/usr/local/mysql/libexec/
cp /usr/local/mysql/share/mysql/english/errmsg.sys /chroot/mysql/usr/local/mysql/share/mysql/english/
cp -r /usr/local/mysql/share/mysql/charsets /chroot/mysql/usr/local/mysql/share/mysql/
cp /etc/hosts /chroot/mysql/etc/
cp /etc/host.conf /chroot/mysql/etc/
cp /etc/resolv.conf /chroot/mysql/etc/
cp /etc/group /chroot/mysql/etc/
cp /etc/master.passwd /chroot/mysql/etc/passwords
cp /etc/my.cnf /chroot/mysql/etc/

Finally, copy the mysql databases that contain the grant tables storing the MySQL access privileges:

cp -R /usr/local/mysql/var/ /chroot/mysql/usr/local/mysql/var
chown -R mysql:mysql /chroot/mysql/usr/local/mysql/var

Now create null device:

mknod /chroot/mysql/dev/null c 2 2
chown root:sys /chroot/mysql/dev/null
chmod 666 /chroot/mysql/dev/null

Edit the password and groups files to remove any entries except for the mysql user and group:

/etc/passwd:
mysql:x:12347:12348:MySQL Server:/dev/null:/bin/false

/etc/group:
mysql:x:12347:

In order for PHP to be able to access MySQL you need to create a link to mysql.sock: ln /chroot/mysql/tmp/mysql.sock /chroot/httpd/tmp/. /chroot/mysql/tmp/mysql.sock and /chroot/httpd/tmp/ need to be on same filesystem. This needs to be done every time you start up the MySQL server. An example startup script below will handle this.

To run MySQL in a chrooted environment as a user other than root, you need to install the chrootuid program. Once you've installed chrootuid, test the server: chrootuid /chroot/mysql mysql /usr/local/mysql/libexec/mysqld &. This will run the server as the mysql user.

The MySQL root user and default accounts

The MySQL root user should not be confused with the system root user. By default, the MySQL root user has no password. You can check this with mysql -u root; if you get a mysql prompt, no root password is set. The first thing you should do is set a strong password for this user. Never give the system root password to the MySQL root user.

To set the initial root password, open a mysql prompt -- mysql -u root mysql -- and enter the following:

mysql> UPDATE user SET Password=PASSWORD('new_password')
    ->             WHERE user='root';
mysql> FLUSH PRIVILEGES;

Don't forget to FLUSH PRIVILEGES; to make the privileges effective.

As well as setting the root password, you should remove anonymous accounts:

mysql> DELETE FROM user WHERE User = '';
mysql> FLUSH PRIVILEGES;

Alternatively, set a password for the anonymous accounts:

mysql> UPDATE user SET Password = PASSWORD('new_password')
    ->     WHERE User = '';
mysql> FLUSH PRIVILEGES;

MySQL privilege system and MySQL users

The MySQL privilege system allows for authentication of users connecting from specific hosts. Authenticated users can be assigned privileges such as SELECT, INSERT, UPDATE, and DELETE on a per database, table, column, or host basis. When a user connects, MySQL first checks if that user is authorized to connect, based on the host and supplied password. If the user is allowed to connect, MySQL then checks each statement to see if the user is allowed to perform the requested action.

When creating new MySQL users, always give the users a strong password, and never store passwords as plain text. Only allow the minimum amount of privileges for a user to accomplish a task, and set those privileges on a per database basis. Some extra time spent planning what privileges to assign to users goes a long way in ensuring the security of your data.

You can create a new user with specific privileges using the GRANT statement. For example:

GRANT USAGE ON myapp.* TO 'someuser'@'localhost' IDENTIFIED BY 'some_pass';
FLUSH PRIVILEGES;

 

This statement creates a MySQL user named someuser who has access to all tables in the myapp database. The USAGE option sets all of the user's privileges to No, meaning you must enable specific privileges later. You may replace USAGE with a list of specific privileges. IDENTIFIED BY 'some_pass' sets the accounts password to 'some_pass'; GRANT automatically encrypts the password for you. Finally, this user can only connect from localhost. FLUSH PRIVILEGES; makes privilege changes effective.

MySQL access privileges are stored in the grant tables of the mysql database. You should never grant normal users privileges to edit entries in the mysql database. That right should be reserved for the root user. There are several tables in the mysql database that allow for a fine-grained level of control over user privileges.

The user table is the most important of the MySQL grant tables. It contains the usernames and passwords for all users, as well as the hosts from which users can connect. There are are also many fields specifying a wide range of privileges, such as SELECT, INSERT, DELETE, FILE, and PROCESS. You should examine this table and the MySQL manual yourself to become familiar with all the options available. Setting a value of 'N' for a field disables the privilege and 'Y' enables it.

You can change privileges using an SQL UPDATE command or the GRANT statement. If you are using SQL statements such as UPDATE or INSERT to update or set user passwords, be sure to use the PASSWORD() function to encrypt the password in the database. Finally, remember to FLUSH PRIVILEGES; for any changes you make so that they become effective:

UPDATE user SET Host='localhost', Password=PASSWORD('new_pass'), Reload_priv='Y', Process_priv='Y' WHERE
User='admin';
FLUSH PRIVILEGES;

Of the different privileges, most are self-explanatory, but some bear special consideration. PROCESS and SUPER should never be given to untrusted users. A user with these privileges may run mysqladmin processlist, which shows a list of currently executing queries. This list could potentially reveal sensitive data such as passwords.

FILE should also not be granted lightly. This privilege allows users to read and write files anywhere on the filesystem to which the mysqld process has access.

Privileges with system administrative rights or database administrative rights, such as FILE, GRANT, ALTER, SHOW DATABASE, RELOAD, SHUTDOWN, PROCESS, and SUPER, should not generally be given to accounts used by specific applications, especially Web-based applications. Furthermore, accounts for specific applications should have access only to the databases related to that specific application.

The other tables in the mysql database give an even finer-grained level of control over privileges:

db -- controls the access of users to specific databases.

tables_priv -- controls the access of users to specific tables.

columns_priv -- controls the access of users to specific columns of a table.

hosts -- specify the actions which can be performed from a particular host.

One final point is that, if you don't completely trust your DNS, use IP numbers in grant tables in place of host names. This makes it more difficult to spoof hosts.

Local security

In addition to MySQL privileges, there are a number of measures you need to take to improve security on the local machine. Most importantly, never run mysqld as root, as, among other risks, any user with the FILE privilege would then be capable of creating files as the root user.

Make sure that only the mysql user has read and write access to the database directory. Data in the database files can be viewed with any text editor, so any user with read or write access to the files could read or alter data, bypassing MySQL's privileges.

The mysql command history is stored in $HOME/.mysql_history. This file may show sensitive information such as passwords. You should clear the file with echo > $HOME/.mysql_history. To prevent the file being written to in the future, link the .mysql_history files of administrative users to /dev/null: ln -s /dev/null .mysql_history.

If you are using MySQL only on the local machine -- for example for PHP Web-based applications -- then in /chroot/mysql/etc/my.cnf add the line skip-networking to the [mysqld] section. This disables all TCP networking features of the MySQL daemon.

You can also disable the use of the LOAD DATA LOCAL INFILE command, which allows reading of local files and is potentially dangerous. Add the line set-variable=local-infile=0 to the [mysqld] section of /chroot/mysql/etc/my.cnf.

Finally, add the line socket = /chroot/mysql/tmp/mysql.sock to the [client] section of /etc/my.cnf. Notice that we are adding this line to /etc/my.cnf, not /chroot/mysql/etc/my.cnf. This is because, while the MySQL server daemon will use /chroot/mysql/etc/my.cnf, MySQL administrative programs such as mysqladmin are not in the chroot environment and will therefore read configuration from /etc/my.cnf.

Securing remote access

The most important step in securing remote access to your MySQL server is in having a firewall. Your firewall should allow only trusted hosts access to MySQL's port, 3306. Better still is to firewall off your MySQL server altogether and allow access onlythrough a Secure Shell (SSH) tunnel, as described below.

Always use passwords for user accounts, even for trusted client programs. The password in a mysql connection is sent encrypted, but in versions prior to 4.1.1, encryption was not particularly strong. In version 4.1.1 the encryption algorithm was much improved.

Even though the password is sent encrypted, data is sent as plain text. If you are connecting across an untrusted network, you should use an SSH encrypted tunnel. SSH tunneling allows you to connect to a MySQL server from behind a firewall, even when the MySQL port is blocked.

To set up tunnel, use the command ssh ssh_server -L 5001:mysql_server:3306 sleep 99999. You need not have direct access to mysql_server, provided ssh_server does. Now you can connect to port 5001 on the local machine with your favorite database client and the connection will be forwarded silently to the remote machine in an encrypted SSH tunnel.

Backup

Be sure to make regular backups of your databases. MySQL includes two utilities which make this easy, mysqlhotcopy and mysqldump.

To use mysqlhotcopy, a user needs access to the files for the tables that he is backing up, the SELECT privilege for those tables, and the RELOAD privilege, in order to execute FLUSH TABLES. You can backup a database using mysqlhotcopy db_name [/path/to/backup_db_dir].

mysqldump supports more options and is especially useful for copying databases between servers, backing up multiple databases at once, or making backups of the database structure only. Databases can be backed up using one of the following commands:

mysqldump [options] db_name [tables]
mysqldump [options] --databases DB1 [DB2 DB3...]
mysqldump [options] --all-databases

For example, you can back-up all your databases and compress them in one go with the command:
date=`date -I`; mysqldump --opt --all-databases -u user --password="your_pass" | bzip2 -c > databasebackup-$date.sql.bz2

The --opt option is shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. This should create a backup which is quick and easy to restore. In fact this option is enabled by default in versions 4.1 and later; you can disable it with --skip-opt.

To restore a database from a file created by mysqldump you just need to run mysql -u user -p db_name < backup-file.sql. The -p option tells mysql to prompt for a password.

Server startup

The following script can be used to start your MySQL server:

#!/bin/sh

CHROOT_MYSQL=/chroot/mysql
CHROOT_PHP=/chroot/httpd
SOCKET=/tmp/mysql.sock
MYSQLD=/usr/local/mysql/libexec/mysqld
PIDFILE=/usr/local/mysql/var/`hostname`.pid
CHROOTUID=/usr/local/sbin/chrootuid

echo -n " mysql"

case "$1" in
start)
        rm -rf ${CHROOT_PHP}/${SOCKET}
        nohup ${CHROOTUID} ${CHROOT_MYSQL} mysql ${MYSQLD} >/dev/null 2>&1 &
        sleep 5 && ln ${CHROOT_MYSQL}/${SOCKET} ${CHROOT_PHP}/${SOCKET}
        ;;
stop)
        kill `cat ${CHROOT_MYSQL}/${PIDFILE}`
        rm -rf ${CHROOT_MYSQL}/${SOCKET}
        ;;
*)
        echo ""
        echo "Usage: `basename $0` {start|stop}" >&2
        exit 64
        ;;
esac

exit 0

Summary

Using these procedures will reduce the risk of a potential breakin to your database server. MySQL's extensive privilege system allows you to protect the data stored within the database. As always you should remain vigilant, and be sure to apply patches and upgrades to your server as they become available.

Mike Peters is a freelance consultant and programmer and long-time Linux user.

CNET Product Awards - Best affordable database MySQL (free for non-profit use)

Developed by the T.c.X in SwedenMySQL users have created Scalable enough applications (there are existing databases with of more than a million rows). Rapidly becoming a must-have on Web hosts everywhere. MySQL can be quickly picked up by anyone with a bit of database experience. Database queries written in SQL (Structured Query Language) can be easily ported to and from other databases. ODBC (Open Database Connectivity) support lets you connect to the database with a variety of database front-end programs, such as Microsoft Access.

If you're looking to connect the database to your Web server, MySQL's slick integration with PHP--a free scripting language--makes writing online database applications almost trivial. Most Web builders will need to use only the half-dozen functions it takes to execute queries, but if you need more access, you'll find advanced functions as well. If you have compiled PHP into the Apache Web server, the system overhead of connecting to MySQL's daemon process is minimal. With this type of support--along with Perl's DBI interface and the multiple language APIs (C/C++, Java, Python, TCL) that MySQL supports--you can integrate this sweet little database into just about any product you might be developing.

For the vast majority of applications and Unix platforms, MySQL is distributed free of charge. An active mailing list provides support for most developers. Commercial support is available starting at $200 per year, which also grants the user access to the Windows 95/NT version of the database.

Linux influx: Turning freeware into bloatware

MS Access to mySql conversion

• http://www.buoy.com/mysql/Contrib/exportsql.txt -- " A script that is similar to the "access_to_mysql.txt". Only this one is fully configurable, has better type conversion (including detection of TIMESTAMP fields), gives out warnings and suggestions while converting, quotes *all* special characters on text and binary data, and so on. And it will also convert to mSQL v1 and v2, and is free of charge for anyone.

• http://www.cynergi.net/non-secure/exportsql/ -- another one.

Comparison with other databases

• Functionality & limitation information for many database servers (crash-me).

• Showing the speed difference between MySQL and some other databases using the
  generic DBIDBD perl interface.

• JDBC benchmarks.

• An old overview about database benchmarks (Not MySQL releated).
   

• MySQL Feedback -- an interesting selection of users opinions. Among them:

Name: Mike Albanese MyProduct: other MySQL binary
OS: Win 32 - Win 98 Hardware: Pentium II / Dell
Works: Excellent Email: jmalbanese@mediaone.net
I suffered with getting Personal Oracle client/server running under Win 98 - my pain was real. Got MySQL and MYODBC up in 1 day!! If the db works as good as it installs it'll be great.
Thanks, Mike Albanese

Name: Sergey Kosyakov MyProduct: MySQL source
OS: FreeBSD-3.0 Hardware: AMD-K6-233MMX
Works: Good Email: ks@itp.ac.ru
MySQL is much faster then PostgreSQL. I hope, MySQL will allow SQL functions creation and nested selects. BTW, it is a very good SQL engine!

Name: Jamison Stepan MyProduct: MySQL Linux
OS: RedHat5/also AIX 4.2 Hardware: Intel/RS6000
Works: Excellent Email: jstepan@uswest.net
Using MySQL to run keyword SQL queries off of a Bibliographic database and then serving to the web for a major University. Works great. The database is over 34000 records with some being text abstracts. The SQL queries get to be two to three printed pages long when emulating a keyword search but MySQL handles it wonderfully.

Name: Joseph Sloan MyProduct: MySQL source
OS: Red Hat Linux 5.0/2.0.34 Hardware: Pentium Pro 200
Works: Good Email: jjs@c-me.com
This is a great web db - very fast, and full of features. It's not Oracle, but I don't need Oracle. BTW 3.21.29 came out significantly faster then 3.21.26 on my benchmarks, congrats for the good coding.

Last modified: November 09, 2008