MySQL

MySQL Database

SME Server runs MySQL as a database server. A lot of applications require a MySQL database, among them is the Horde webmail interface which is supplied by SME Server by default.

• MySQL website: http://www.mysql.com
  
• MySQL 4.1 manual: http://dev.mysql.com/doc/refman/4.1/en/

General

The SME Server is based on CentOS, the development team will take their stock RPM's from the CentOS releases. The current version of MySQL installed on SME Server is version 4.1.20.

You can upgrade MySQL to a higher version but you are advised not to do so, as this might break your SME Server configuration. The Horde webmail interface relies on MySQL. Upgrading to version 5.x is known to break stuff like webmail. If you insist on upgrading MySQL you may be able to find instructions in the forum, but be advised that no support can be expected from the developers and all bugs reported in the bugtracker will not be taken into account.

MySQL on SME Server runs on a socket instead of on a port which you might be accustomed to. This is done to improve security as in the view of the development team only the server itself (localhost) needs to have access to the MySQL server. However you can configure MySQL to be accessible from the local network (see below).

MySQL root password

There appears to be no password set for the MySQL root password, but this is not true. If you are logged in to the SME Server shell a special mechanism is in place to log you in with MySQL root privileges without prompting you for the password.

The MySQL root password for SME Server is a 72 character random string generated during installation of SME Server. You should never change the MySQL root password as this will break your SME Server configuration. How to login as MySQL root user? describes how to access MySQL with root privileges on SME Server.

Login as MySQL root user

To login as MySQL root user, simply type 'mysql' at the SME Server shell, this will log you in with root privileges.

Resetting the MySQL root password

To reset the password for the MySQL root account. The MySQL root user on SME Server has a random generated password which is generated during installation. You do not need to know this password to login to MySQL with root privileges on SME Server. If you might have changed the MySQL root password you can reset it like this after getting command line access as root user.

For SME Server 7.3 and more recent versions do the following:

cd /var/service/mysqld
sv d .
expand-template /root/.my.cnf
expand-template /var/service/mysqld/set.password
/usr/libexec/mysqld --bootstrap --user=mysql --skip-grant-tables < ./set.password
sv u .

For SME Server 7.2 and earlier releases do the following (they use the runsvctrl command instead of the sv command):

cd /var/service/mysqld
runsvctrl d .
expand-template /root/.my.cnf
expand-template /var/service/mysqld/set.password
/usr/libexec/mysqld --bootstrap --user=mysql --skip-grant-tables < ./set.password
runsvctrl u .

Restoring accidently deleted MySQL root user

Note: The following is only applicable on SME 7.3 and MySQL 4.1

cd /var/service/mysqld
sv d .
echo 'use mysql;'>set.rootuser
echo -n 'INSERT INTO user VALUES("localhost","root","",'>>set.rootuser
echo '"Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","","","","",0,0,0);'>>set.rootuser
expand-template /root/.my.cnf
expand-template /var/service/mysqld/set.password
/usr/libexec/mysqld --bootstrap --user=mysql --skip-grant-tables < set.rootuser
/usr/libexec/mysqld --bootstrap --user=mysql --skip-grant-tables < set.password
sv u .

Access MySQL from the local network

MySQL on SME Server is run on a socket instead of on a port. MySQL on SME Server is by default configured to allow only localhost connections to improve security, this means that it is only accessible from the server itself and not from the local network nor from the internet. If you wish to enable local network access, execute the following commands on a SME Server shell as root:

config setprop mysqld LocalNetworkingOnly no
expand-template /etc/my.cnf
sv t /service/mysqld

Access MySQL from a remote network

If you wish to enable access to MySQL databases from remote networks, then in addition to the LocalNetworkingOnly db setting mentioned above, you will need to execute the following commands:

config set mysqld service access public status enabled TCPPort 3306 
signal-event remoteaccess-update 
signal-event reboot

Keep in mind this enables access to your MySQL database for ANYONE, so make sure you have strong passwords on ALL your MySQL databases. Alternatively it would be a more secure approach to require external (remote) users to establish a VPN connection and effectively become part of the local network. In that case do not change the mysql access to public status using the above command.

Enable InnoDB engine

For enable InnoDB engine, run the following commands:

db configuration setprop mysqld InnoDB enabled
expand-template /etc/my.cnf
sv t /service/mysqld

Administration

Information about user managament can be found in the MySQL User Account Management section of the MySQL manual, which holds a lot of useful information, a small section is listed here for convenience.

Create a new database

• See the developers guide if you wish to automate the creation of a database within an rpm

or

• Get access to the SME Server shell and issue the following commands:

mysqladmin create 'dbname' --default-character-set=utf8

This will create an empty database called name.

	Warning:
	The 'root' user should not be permitted to access the database except from localhost. Each application should have its own database and its own user to access that database.

Creating MySQL user(s)

Decide which permissions you will have to give to the user on what database. Details about this can be found in the MySQL Manual found at the MySQL site. Get access to the SME Server shell and issue the following commands to login to the MySQL server:

mysql

Suppose we want to create a user which has read-only access on all tables in the database called 'test':

GRANT SELECT ON test.* TO 'user'@'host' IDENTIFIED BY 'password';

In the above line you will have to fill in the user and the host and/or domain from which you will allow the user access to the SME Server MySQL server (don't forget the single quotes). More information can be found in the MySQL Server Manual at the MySQL website linked here.

Listing available databases

To view a list of available databases on the system you can issue the following command while logged in in MySQL:

show databases;

Remove a database

Get access to the SME Server shell and MySQL and issue the following command:

drop database databasename;

Replace databasename with the name of the database.

Remove a user

Get access to the SME Server shell and MySQL and issue the following command:

USE mysql;
DELETE FROM user WHERE user = 'username';
FLUSH PRIVILEGES;

Replace username with the username you wish to delete.

	Tip:
	mysql_setpermission is a command line menu driven utility that can assist in MySQL administration.