Getting Started with SQL (part 2)

By: L33tdawg

Okay we're back once again... last month I took you guys through the
installation steps needed to get MySQL up and running on a *NIX boxen, and
as promised, this time around, I shall continue through the other little
bits like server configuration, security issues and a little guide to
starting a database so you can start playing with stuff. Without further
ado, let's get to it.

Server configuration.

Like any other daemon, wheh mysqld starts up, it looks for a configuration
file. It will look for the file /etc/my.cnf by default. Thus to make your
life slightly easier, you'll need to build you own copy of
/etc/my.cnf. The distribution ships with four sample scripts from you to
choose from, which are located in the support-files directory. I would
reccomend selecting one of those as the basis for your own script thus
saving you time and effort in getting a config file ready. The question on
which file to select is really dependant on your needs as well as whether
MySQL is the primary service running on the system, or if it must share
resources with other services (like Apache or sendmail). For the record,
the four included files are:

my-huge.cnf -- For systems with 1GB or more of RAM where MySQL is the
primary service running on the machine.

my-large.cnf -- For any systems with 512MB of RAM and above where MySQL is
once again the primary service running on the machine.

my-medium.cnf -- For machines with 32 - 128MB of RAM, and where MySQL is
sharing resources with other services.

my-small.cnf -- For systems with less than 64MB of memory where MySQL is
sharing resources with other services and will not be utilized much.

For the sake of keeping things simple. I shall only highlight the sections
in the config file that you're most likely going to need - if you need to
know the other nifty bits, then I suggest reading the documentation in
detail or get yourself a good SQL book (then again, just get yourself a
good SQL book anyway - it always comes in handy.)

Configuration Options.

Open up your my.cnf file (whichever you copied from the distribution-files
over to /etc/my.cnf and pay close attention to the mysqld section of the
config script. The other sections as I mentioned above are certainly
beyond the scope of this article so you're going to have to look elsewhere
if you need help with them.

key_buffer=16M -- The key_buffer tells MySQL basically how much memory it
is allowed to use for caching index data. Increasing this value can often
yeild dramatic improvements in your SQL performance. However, if this
value is set too higeh, it can adversely affect system performance
(basically there won't be enough ram for the rest of the system). On HITB
our box, we've got 128MB of RAM, so we changed this value to 32MB.

table_cache=128 -- The table_cache is the number of simultaneously open
tables that MySQL can have. MySQL requires two file descriptors for each
unique open table plus one for each concurrent client using a table. For
larger installations it would probrably be wise to change this value to
more than 64 -- perhaps 128 as shown above.

sort_buffer=512K -- If you plan to constantly use the ORDER BY and GROUP
BY within your queries, you may benefit from increasing the size of the
sort_buffer.

myisam_sort_buffer_size=8MB -- This section determines the size of the
buffer used when reparing a table or creating an index. In most cases, the
default value should do just fine.

One important value missing from the my.cnf file is the variable that
tells MySQL which user it should run as. Because we created a mysql user
earlier, it would be wise to add the folloowing line to the [mysqld]
seciton of the config file:

user = mysql

Now that you've got MySQL configured to start at boot time, and the
configuration values have been tweaked for the utmost performance, its

time to move on to security issues and the sort.

Security.

Like with most things in the networked world, MySQL is only as secure as
the machine it is installed on. While keeping your system up to date with
the latest security patches and bug fixes, it is also important that you
understand the basiscs of the MySQL security system so that you can keep
your data sae. By default, MySQL provides extensive, host, database, table
and user-level security which can all be applied to make the data and the
server more secure.

Network Security

If the host that runs MySQL is not behind a firewall one effective
security measure is to simply change the default port that MySQL runs on
(3306). Basically, just ednt the /etc/my.cnf file and change the value of
port= from 3306 to an unused port on your system. If youre MySQL is behind
a firewall it would be best to just block access to port 3306 for
connections coming in from beyond the firewall. A simple ipchains rule
that could be used to accomplish this would be:

/sbin/ipchains -A input -p tcp -d 0/0 3306 -s ! 192.168.1.1/255.255.255.0 -i DE$

where 192.168.1.1 is the IP address of the firewall.

Database-Level Security

Upon receiving a connection request, MySQL checeks to see whether or not
the user is allowed to connect to the database they've requested. This
check involves not only the user name and password that the client
provides, but also the host from which they are attempting to connect
to. If the combination of the three above is not allowed, the daemon
simple closes the connecttion.

Table-Level Security

When a user attemptes to execute an SQL query, MySQL checks the user's
priveleges to see if the user is allowed to perform the requested
operation. For instance, some users may only be allowed to read data,
while others might be given full read and write privs. As with most
security things, just give users the access privs they need and nothing
more. You'll find that in most cases, the users will only need access to
SELECT, INSERT, UPDATE, and DELETE.

Adding an Administrative User.

Its always good to add an Administrative User to the database who will be
allowed to add databases and other users. Log into your SQL server:

/usr/local/bin/mysql -u root -p

mysql> GRANT ALL PRIVILEGES ON *.* TO admin@localhost IDENTIFIED BY
'password' WITH GRANT OPTION;

Exit and then log in as the new database administrator just to make sure
it works.

/usr/local/bin/mysql -u admin -p

Adding a Normal User

To add a normal user, just follow the steps as above (to log into your
MySQL admin account and issue the following):

mysql> GRANT SELECT, INSERT, DELETE, UPDATE ON users.* TO dbuser@localhost
IDENTIFIED BY 'password';

Keep in mind that MySQL allows you to assign permissions on objects and
databases before they exsist, thus allowing you to plan out your security
guidelines before the databases are built.

Well that's about it. You're pretty much all done. I know I promised to
perhaps take a brief look at building a simple database, but time's
running out so I'll leave that to the next issue -- I know most of you
will probrably not need it, but for the benefit of those who do; stay
tuned.

Peace.
L33tdawg.

1.) Script Kiddies: How to be one and be loathed by your peers. - Grifter
2.) Napster, MPAA, AOL and how stupid people in power will kill the first amendment.
- Dan
3.) Getting Started with SQL (part 2) - L33tdawg
4.) Freedom and The Net - Archfiend
5.) The Guide to Annoying People on ICQ - Archfiend
6.) Quantum Mechanics - Part 3 - Josette
7.) The Art of Programming - Dinesh Nair