|
Softpanorama
(slightly skeptical)
Open Source Software Educational Society |
May the
source be with you,
but remember the KISS principle ;-)
|
MySql
MySql was written by Michael (monty) Widenius and now is owned by Sun. See
the CREDITS file in the distribution for more credits for mysql and related things.
MySql runs on most Unix based systems including Linux and has support for clients
running under both Unix and windows. For a good introduction see
MySQL - Wikipedia,
the free encyclopedia
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).
Notes:
- Those pages are written by people for whom English is not a
native language. Some amount of grammar and spelling errors
should be expected.
- This is a Spartan WHYFF (We Help You For Free) site. It
cannot replace the best teachers and
the
best books.
- The site contain some obsolete pages as it develops like a
living tree... Some links on older pages
are broken. Please
try to use Google, Open directory, etc. to find a replacement link
(see
HOWTO search the WEB for details).
We would appreciate if you can
mail us a correct link.
|
|
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.
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.
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
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.
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';
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.
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).
"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.
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.
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.
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.
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.
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
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!
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.
- 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".
- 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".
- Install the server
as root. Gunzip and untar the server kit. Follow the instructions
in the file INSTALL-BINARY.
- Install the ODBC driver
as root. Gunzip and untar the ODBC driver kit. Follow the instructions
in the file INSTALL-BINARY.
- 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.
- 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
- 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.
- Click on "iODBC Driver Manager" and download
the full source archive (e.g. libiodbc-3.0.6.tar.gz).
- 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
- 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.)
- 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
- 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.
-
2.12.3.2. Solaris x86 Notes
the recommended compiler is
gcc 2.95.2 or 3.2.
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.
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
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
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.
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
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.
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
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).
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!
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.
MySQL Basic Configuration and Startup for Solaris 10 - Articles
and FAQs
Freeware
for Solaris
Installing MySQL 3.23 - Solaris
AMPS - Apache
MySQL PHP Solaris 8 Installation
MySQL by T.c.X. DataKonsultAB
-- main site(the vendor)
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).
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
[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
-
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.
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.
Copyright © 1996-2008 by Dr. Nikolai Bezroukov.
www.softpanorama.org was
created as a service to the UN Sustainable Development Networking Programme (SDNP)
in the author free time.
Submit
comments This document is an industrial compilation designed and created
exclusively for educational use and is placed under the copyright of the
Open Content License(OPL).
Original materials copyright belong to respective owners. Quotes are made
for educational purposes only in compliance with the fair use doctrine.
Standard disclaimer: The statements, views and opinions presented on
this web page are those of the author and are not endorsed by, nor do they necessarily
reflect, the opinions of the author present and former employers, SDNP or any other
organization the author may be associated with. We do not warrant the correctness
of the information provided or its fitness for any purpose.
Last modified:
June 27, 2008
