Linux Home Server HOWTO

Home

Chapter 17 - MySQL Server

Versions:

- mysql-server 5.0.21
- mysql-administrator 1.1.6
- phpMyAdmin-2.8.1

Initial Configuration
Setting Root Password
phpMyAdmin Web Administrator

Databases are a great way to store information, they can store your personal contact list, your financial records, your household inventory, or even a listing of your favourite websites. Databases are able to store large amounts of useful information in a logical structure that allows for quick retrieval in any user defined format. Community libraries are renowned for their use of large database systems to store the mass amounts of usable information that they collect and share.

Databases also play a major role in todays web applications by storing complete inventories of products and services and making these accessible through a programmed web frontend. A database has the ability to provide dynamic content to a web shop by providing the content to web pages as an online shopper browses through the catalogue; a simple adjustment in the database can provide a 10% discount across the whole product line, instantly. Many Linux distributions implement PHP, MySQL, and Apache as a perfect combination for full featured dynamic web content.

This chapter will provide guidance to establish your MySQL (http://www.mysql.com) database server and also the steps necessary to configure a web based administration tool (phpMyAdmin) for remote management. This chapter will not be an introduction to the Structured Query Language (SQL), there are already many tutorials available on the Internet.

Initial Configuration

Configuring the server is not too difficult but one point to remember is there are three levels of configuration files, each having precedence over the following file. The main configuration file (/etc/my.cnf) contains all of the global options for the server, what ever is defined here will dominate the subordinate files. Likewise the server-specific configuration will dominate any user specified options, if the files exist.

The following table details the configuration files.

Order:	File Location:	Description:
1.	/etc/my.cnf	For setting global options
2.	/var/lib/mysql/my.cnf	For setting server-specific options
3.	~/my.cnf	For setting user-specific options (if applicable)

Depending on your Linux distribution your MySQL server may already be configured with a minimum configuration file and is ready to run, otherwise you may need to create a configuration file before the server can be activated. This is not a big problem as some sample configuration files have been provided that are already tuned to a particular purpose depending on the role of the server.

Sample File:	Used For:
my-huge.cnf	Large site, fully dedicated mysql server, 1-2GB RAM
my-large.cnf	Large site, mostly dedicated mysql server, ~512MB RAM
my-medium.cnf	Medium site, shared server running mysql, > 64MB RAM
my-small.cnf	Small site, shared server running mysql, < 64MB RAM
File Locations: /usr/share/doc/mysql-server-?.??.??

You need to check if you have the global options file (/etc/my.cnf) installed, if you don't have the file you should copy one of the sample files that best suits your requirements into that position, if you do have the global file then you should copy one of the sample files into the server-specific options location. Remember that we should backup the main configuration first if it exists.

[bash]# cp /etc/my.cnf /etc/my.cnf.original
[bash]# cp /usr/share/doc/mysql-server-?.??.??/my-small.cnf /var/lib/mysql/my.cnf

Copy the sample file that best suits your system and requirements. A configuration that is over tuned for your system may waste resources unnecessarily.

Now that the configuration files are in place, the runlevels should be configured and checked. You should ensure that the runlevels match those of Apache if you are using the MySQL server as a backend to a dynamic website.

[bash]# chkconfig --level 345 mysqld on
[bash]# chkconfig --list mysqld

The service can now be started and checked for initialisation errors.

[bash]# /etc/init.d/mysqld restart
[bash]# tail /var/log/mysqld.log

If this is the first time that you have started your server, it will automatically create some standard databases in the /var/lib/mysql directory. If these database directories do not exist or you received an error, then they can be manually created by typing "mysql_install_db" at the command prompt.

[bash]# ls -l /var/lib/mysql

drwx------   2 mysql mysql 4096 Dec 22 04:41 mysql
srwxrwxrwx   1 mysql mysql    0 Dec 22 04:41 mysql.sock
drwx------   2 mysql mysql 4096 Dec 22 04:41 test

You can check the integrity of your new databases with the following command.

[bash]# mysqlcheck -A

mysql.columns_priv                                 OK
mysql.db                                           OK
.
.    *** Output Trimmed ***
.
mysql.time_zone_transition_type                    OK
mysql.user                                         OK

Setting Root Password

Your sql server should now be configured and running, however the initial setup creates a root user with a blank password which should be changed to protect your databases.

The system log also confirms that the server is running, and warns the user to change their root password.

[bash]# /etc/init.d/mysqld restart

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
galaxy mysql_install_db: This is done with:
galaxy mysql_install_db: /usr/bin/mysqladmin -u root password 'new-password'
galaxy mysql_install_db: /usr/bin/mysqladmin -u root -h galaxy.example.com password 'new-password'
galaxy mysql_install_db: See the manual for more instructions.

The sql root account should not be confused with the Linux system superuser account, it is only for access to the sql server so the passwords can be different if you need them to be. You should now select a suitable password and use the following commands to secure the root account. Remember to substitute "NEW_ROOT_PASSWORD" for your chosen password.

[bash]# mysql -u root

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NEW_ROOT_PASSWORD'); <-- change MySQL root password
mysql> SET PASSWORD FOR 'root'@'galaxy.example.com' = PASSWORD('NEW_ROOT_PASSWORD'); <-- change MySQL root password
mysql> quit

Now that the passwords have been configured for the root account, you will need to add the following command options to all your commands when using the mysql client, the client will then ask you for your password before granting access. See "man mysql" for more details.

Now Need: "-u root -p"

[bash]# mysqladmin -u root -p variables
Enter password:

To view a list of all the user accounts within the "mysql" database table, use the following command, note that the passwords are stored as a message digest hash (secure algorithm) to prevent someone from simply "dumping" them out with this command.

[bash]# echo "select USER,HOST,PASSWORD from user" | mysql -u root -p -D mysql
Enter password:

USER    HOST    PASSWORD
root    localhost       31218f0c48d3e60f
root    galaxy.example.com      31218f0c48d3e60f
        galaxy.example.com
        localhost
pma     localhost       3cf4e95402cfe1cd <-- added duing phpMyAdmin config

Graphical Administrator

The extras yum respository has a "mysql-administrator" RPM that can be installed to assist in administering your new MySQL server through a graphical user interface. Type the following command at the prompt to install the administrator (this requires X Windows and a desktop manager to be installed).

[bash]# yum install mysql-admin* mysql-gui*

[bash]# mysql-administrator & <-- To execute GUI administrator

phpMyAdmin Web Administrator

One of the easiest ways to interface and administer your SQL database is through a web application thats running on the local SQL server. phpMyAdmin is an opensource PHP based web application designed specifically to allow remote management of MySQL using nothing more that a standard web browser. The phpMyAdmin application provides an easy to use graphic interface for users that are not too familiar with SQL commands by providing easy to follow instructions. The package can be downloaded from the phpMyAdmin site (http://www.phpmyadmin.net) and quickly installed on your local web/SQL server.

Firstly we need to download the latest archive of phpMyAdmin and save it somewhere on the local server, the following example uses the gzip format. Use the following commands to extract the archive into the "/var/www" directory, remember to replace ?.?.? with the version number you have downloaded.

[bash]# tar -xzvf phpMyAdmin-?.?.?.tar.gz -C /var/www/
[bash]# chown -R root.root /var/www/phpMyAdmin-?.?.?

Replace phpMyAdmin-?.?.? with the version number that you are installing.

The application has now been extracted and needs to be configured with the settings for the local MySQL server. phpMyAdmin will also interface into one of its own databases using an account called "pma" which we will create a little further on, however you will need to place a password into the configuration file for the pma account; remember it for later use.

When you configured your Apache web server you created an SSL certificate and used the rewrite module to force SSL connections. It is recommended that you also force SSL on your phpMyAdmin application so that any logon details and database queries are executed confidentially. Ensure the 'PmaAbsoluteUri' directive uses the HTTPS protocol if you intend using SSL, otherwise substitute it for the standard URL path.

[bash]# vi /var/www/phpMyAdmin-?.?.?/config.inc.php

<?php

$cfg['PmaAbsoluteUri'] = 'https://www.example.com/mysql';
$cfg['PmaAbsoluteUri_DisableWarning'] = FALSE;
$cfg['PmaNoRelation_DisableWarning'] = FALSE;
$cfg['blowfish_secret'] = '';

/* Start of servers configuration */
$i = 0;

/* Server localhost (config:root) [1] */
$i++;
$cfg['Servers'][$i]['host']    = 'localhost';
$cfg['Servers'][$i]['port']          = '3306';
$cfg['Servers'][$i]['socket'] = '/var/lib/mysql/mysql.sock';
$cfg['Servers'][$i]['connect_type'] = 'socket';
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password']    = 'NEW_ROOT_PASSWORD';   <-- change MySQL root password
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = 'PMA_PASSWORD';    <-- change PMA password
$cfg['Servers'][$i]['pmadb']         = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma_bookmark';
$cfg['Servers'][$i]['relation']      = 'pma_relation';
$cfg['Servers'][$i]['table_info']    = 'pma_table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma_table_coords';
$cfg['Servers'][$i]['pdf_pages']     = 'pma_pdf_pages';
$cfg['Servers'][$i]['column_info']   = 'pma_column_info';
$cfg['Servers'][$i]['history']       = 'pma_history';

/* End of servers configuration */

?>

Replace "PMA_PASSWORD" and "NEW_ROOT_PASSWORD" with the appropriate passwords for those accounts.

The local MySQL server now needs to be configured with the tables and access rights so phpMyAdmin can connect to it. These steps can vary depending upon the version of MySQL you are running, and may result in error if the wrong commands are issued. Use the following mysqladmin command below to confirm the version of MySQL you are using before continuing to configure the server.

[bash]# mysqladmin -u root -p version

      Server version       5.0.21

You can now create the tables needed for phpMyAdmin depending on the server version you are running. You will need to enter your 'root' password when prompted (substituting ?.?.? where needed).

      WARNING>>>>
ONLY FOR SQL VERSIONS ABOVE
4.1.2

[bash]# mysql -u root -p < /var/www/phpMyAdmin-?.?.?/scripts/create_tables_mysql_4_1_2+.sql

If the above command was successful you will now see the new database structure located in the servers data directory.

[bash]# ls -l /var/lib/mysql/

      drwx------  2 mysql mysql 4096 Dec 22 02:50 mysql

srwxrwxrwx  1 mysql mysql    0 Dec 22 06:02
mysql.sock

      drwx------  2 mysql mysql
4096 Dec 22 06:17 phpmyadmin

drwx------  2 mysql mysql 4096 Dec 22 02:50 test

We now need to use the mysql command line client to configure the correct access controls for the pma control account and database. The following commands can be cut and pasted into the mysql client when the "mysql>" prompt is available. Remember to replace "PMA_PASSWORD" with the correct password.

      WARNING>>>>
ONLY FOR SQL VERSIONS ABOVE
4.1.2

      [bash]# mysql
-u root -p

mysql> _

GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY 'PMA_PASSWORD'; <-- change PMA password
GRANT SELECT (
     Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
     Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv,
     File_priv, Grant_priv, References_priv, Index_priv, Alter_priv,
     Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,
     Execute_priv, Repl_slave_priv, Repl_client_priv
     ) ON mysql.user TO 'pma'@'localhost';
GRANT SELECT ON mysql.db TO 'pma'@'localhost';
GRANT SELECT ON mysql.host TO 'pma'@'localhost';
GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)
     ON mysql.tables_priv TO 'pma'@'localhost';
quit

Replace "PMA_PASSWORD" with the appropriate password for the pma control account.

The tarball archive for the phpMyAdmin application was originally extracted into the "/var/www/phpMyAdmin-?.?.?", while the Apache web server has its "DocumentRoot" directive set to "/var/www/html" which means the phpMyAdmin application is located outside of the "DocumentRoot" and the contents are not accessible directly by the web server.

We can create a configuration file for the phpMyAdmin application so Apache can access the resources that are required. The configuration below is using the AuthType directive ensuring that the access is restricted to only those users that have a valid username and password.

[bash]# vi /etc/httpd/conf.d/phpMyAdmin.conf

Alias /mysql "/var/www/phpMyAdmin-?.?.?"

<Location "/mysql">
    AuthType Basic
    AuthName "Private Area - MySQL Administrator"
    AuthUserFile /etc/httpd/conf/authusers
    AuthGroupFile /etc/httpd/conf/authgroups
    Require group sqlusers
#    Require valid-user
</Location>

If you have created SSL certificates for your Apache web server, then you should force the phpMyAdmin application into SSL mode to keep it secure. This configuration uses the rewrite module configuration we created in Chapter 13.

[bash]# vi /etc/httpd/conf.d/mod-rewrite.conf

RewriteRule ^/mysql/(.*) https://%{SERVER_NAME}/mysql/$1 [R,L]

The Apache web server needs to be restarted before the settings will be used.

[bash]# /etc/init.d/httpd restart

If everything has gone well you should now be able to access the phpMyAdmin application on the local server at: https://localhost/mysql.

Home