Difference between revisions of "MySQL"

From SME Server
Jump to navigationJump to search
m
(36 intermediate revisions by 13 users not shown)
Line 1: Line 1:
===MySQL Database===
+
{{Languages}}
 
SME Server runs MySQL as a database server.
 
SME Server runs MySQL as a database server.
There are a lot of applications require a MySQL database, among them is the Horde webmail interface which is supplied by SME Server by default.  
+
A lot of applications require a MySQL database, among them is the Horde webmail interface which is supplied by SME Server by default.  
  
Usefull links
 
 
* MySQL website: http://www.mysql.com <br />
 
* MySQL website: http://www.mysql.com <br />
 
* MySQL 4.1 manual: http://dev.mysql.com/doc/refman/4.1/en/
 
* MySQL 4.1 manual: http://dev.mysql.com/doc/refman/4.1/en/
  
====General====
+
==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.
 
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.
  
Line 15: Line 13:
  
 
MySQL on SME Server runs on a socket instead of on a port which you might be accustomed to.
 
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 self needs to have access to the MySQL server. However you can configure MySQL to be accessible from the local network
+
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 ====
+
==MySQL root password==
There seems 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.
+
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.
 
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 =====
+
 
 +
===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.
 
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 =====
+
===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:
+
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.
  
  cd /var/service/mysqld
+
For SME Server 7.3 and more recent versions do the following:
  runsvctrl d .
+
cd /var/service/mysqld
  expand-template /root/.my.cnf
+
sv d .
  expand-template /var/service/mysqld/set.password
+
expand-template /root/.my.cnf
  /usr/libexec/mysqld --bootstrap --user=mysql --skip-grant-tables < ./set.password
+
expand-template /var/service/mysqld/set.password
  runsvctrl u .
+
/usr/libexec/mysqld --bootstrap --user=mysql --skip-grant-tables < ./set.password
 +
sv u .
  
==== Access MySQL from the local network ====
+
For SME Server 7.2 and earlier releases do the following (they use the runsvctrl command instead of the sv command):
To access MySQL from my local network
+
 
Because MySQL on SME Sverer is ran on a socket instead of a on a port
+
cd /var/service/mysqld
MySQL on SME Server is by default configured to allow only local connections to improve security, this means that it is only accessible from the server it self and not from the local network nor from the internet.
+
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:
 
If you wish to enable local network access, execute the following commands on a SME Server shell as root:
  
  config setprop mysqld LocalNetworkingOnly no
+
config setprop mysqld LocalNetworkingOnly no
  expand-template /etc/my.cnf
+
expand-template /etc/my.cnf
  /etc/rc.d/init.d/mysqld restart
+
sv t /service/mysqld
  
====Administration====
+
==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:
  
===== Create a new database =====
+
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.
 +
 
 +
==Create MySQL user(s) with access from other computers==
 +
SME Server's default mysql database users, and most of the database examples in the wiki, allow login only from ''localhost''. 
 +
 
 +
If you want to access a mysql database on your SME server from other computers, you must not only make the configuration changes described above, you must also create a user who is allowed to login from those systems (see [http://dev.mysql.com/doc/refman/4.1/en/connection-access.html 5.5.4. Access Control, Stage 1: Connection Verification] for more detail).
 +
 
 +
===Allow mysql login from any LAN workstation===
 +
Assuming your local network is 192.168.xx.0, you can create a user with mysql access from any LAN workstation (or VPN client) using the command shown below (couresy of [http://forums.contribs.org/index.php/topic,32270.msg136322.html#msg136322 DarkMirage]).
 +
{{Tip box|The suggestion here is to assign privileges based on IP number (using a wild card if desired), the same can also be done for hostnames. In some cases, like dynamicaly assgned IP addresses, this might be a more suitable and robust solution.}}
 +
You probably want to change:
 +
* the database name ('''MyDB''')
 +
* the user name ('''MyUser''')
 +
* the password ('''MyPW''') and
 +
* the allowed computers ('''192.168.xx.%''')
 +
<nowiki>## In the command below, \ escapes a linebreak.
 +
## Either include them, or place the entire command on one line
 +
mysql -e "\
 +
create database MyDB; \
 +
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,ALTER \
 +
ON *.* \
 +
TO 'MyUser'@'192.168.xx.%' \
 +
IDENTIFIED BY 'MyPW'; \
 +
FLUSH PRIVILEGES;"</nowiki>
 +
 
 +
===Security Implications of allowing remote mysql login===
 +
It is technically possible to combine the above techniques to allow remote mysql login from any host on the Internet (allow network login, open the firewall, then set the network address to '%').  This would be a bad idea.
 +
 
 +
If you have remote users who need access to your mysql database(s), encourage them to use a VPN connection, or an SSH tunnel, or (at a minimum), restrict the allowed login hosts to their internet IP address.
 +
 
 +
==Enable InnoDB engine==
 +
To enable the InnoDB engine, run the following commands:
 +
 
 +
db configuration setprop mysqld InnoDB enabled
 +
expand-template /etc/my.cnf
 +
sv t /service/mysqld
 +
 
 +
To disable the InnoDB engine, run the following commands:
 +
 
 +
db configuration setprop mysqld InnoDB disabled
 +
expand-template /etc/my.cnf
 +
sv t /service/mysqld
 +
 
 +
==Administration==
 +
Information about user managament can be found in the [http://dev.mysql.com/doc/refman/4.1/en/user-account-management.html MySQL User Account Management section] of the [http://dev.mysql.com/doc/refman/4.1/en/ 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
 
* See the developers guide if you wish to automate the creation of a database within an rpm
 
or
 
or
 
* Get access to the SME Server shell and issue the following commands:
 
* Get access to the SME Server shell and issue the following commands:
  
  mysqladmin createdatabase 'name'
+
mysqladmin create 'dbname' --default-character-set=utf8
  
This will create an empty database called name.
+
This will create an empty database called dbname.
  
{{DrawBoxWarning|content=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.||
+
{{Warning box|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) =====
+
===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:
 
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
+
mysql
  
 
Suppose we want to create a user which has read-only access on all tables in the database called 'test':
 
Suppose we want to create a user which has read-only access on all tables in the database called 'test':
Line 69: Line 142:
 
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.
 
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.
  
<noinclude>[[Category:Howto|MySQL]]</noinclude>
+
===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 box|mysql_setpermission is a command line menu driven utility that can assist in MySQL administration.}}
 +
 
 +
==Optimizing MYSQL default settings==
 +
 
 +
SME Server uses MYSQL for the webmail package, and the default configuration is optimized for that.
 +
 
 +
If you are using the SME server to provide MYSQL databases for functions running on workstations, you may need to adjust some of the default MySQL parameters. Keep in mind it is difficult to optimize MYSQL for a number of different applications, as default values that are suitable for one application may not be suitable for another. In determining appropriate settings for MYSQL, you will also need to consider the system resources & general specification of the server that MYSQL is running on.
 +
 
 +
Pointers for tuning and optimizing the databases can be found at http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ and http://lists.mysql.com/mysql/214398 and specifically re key_buffer_size at http://lists.mysql.com/mysql/214398
 +
 
 +
The following example comes from this forum thread http://forums.contribs.org/index.php/topic,46694.0.html and refers to this bug report http://bugs.contribs.org/show_bug.cgi?id=6287
 +
 
 +
To change the following parameters
 +
key_buffer_size=18M
 +
myisam_sort_buffer_size=8M
 +
 
 +
Create a custom template fragment & edit it to include your required parameters
 +
mkdir -p /etc/e-smith/templates-custom/etc/my.cnf/
 +
nano -w 011mysetup
 +
Save & Exit
 +
Ctrl o
 +
Ctrl x
 +
Expand the changes & restart mysql
 +
expand-template /etc/my.cnf
 +
sv t /service/mysqld
 +
Check /etc/my.cnf to see that the changes are reflected.
 +
 
 +
<noinclude>
 +
----
 +
[[Category:Howto]]
 +
[[Category:Administration]]</noinclude>

Revision as of 12:54, 27 February 2013


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.

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.

Create MySQL user(s) with access from other computers

SME Server's default mysql database users, and most of the database examples in the wiki, allow login only from localhost.

If you want to access a mysql database on your SME server from other computers, you must not only make the configuration changes described above, you must also create a user who is allowed to login from those systems (see 5.5.4. Access Control, Stage 1: Connection Verification for more detail).

Allow mysql login from any LAN workstation

Assuming your local network is 192.168.xx.0, you can create a user with mysql access from any LAN workstation (or VPN client) using the command shown below (couresy of DarkMirage).

Information.png Tip:
The suggestion here is to assign privileges based on IP number (using a wild card if desired), the same can also be done for hostnames. In some cases, like dynamicaly assgned IP addresses, this might be a more suitable and robust solution.


You probably want to change:

  • the database name (MyDB)
  • the user name (MyUser)
  • the password (MyPW) and
  • the allowed computers (192.168.xx.%)
## In the command below, \ escapes a linebreak.
## Either include them, or place the entire command on one line
mysql -e "\
create database MyDB; \
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,ALTER \
ON *.* \
TO 'MyUser'@'192.168.xx.%' \
IDENTIFIED BY 'MyPW'; \
FLUSH PRIVILEGES;"

Security Implications of allowing remote mysql login

It is technically possible to combine the above techniques to allow remote mysql login from any host on the Internet (allow network login, open the firewall, then set the network address to '%'). This would be a bad idea.

If you have remote users who need access to your mysql database(s), encourage them to use a VPN connection, or an SSH tunnel, or (at a minimum), restrict the allowed login hosts to their internet IP address.

Enable InnoDB engine

To enable the InnoDB engine, run the following commands:

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

To disable the InnoDB engine, run the following commands:

db configuration setprop mysqld InnoDB disabled
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 dbname.


Warning.png 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.


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


Optimizing MYSQL default settings

SME Server uses MYSQL for the webmail package, and the default configuration is optimized for that.

If you are using the SME server to provide MYSQL databases for functions running on workstations, you may need to adjust some of the default MySQL parameters. Keep in mind it is difficult to optimize MYSQL for a number of different applications, as default values that are suitable for one application may not be suitable for another. In determining appropriate settings for MYSQL, you will also need to consider the system resources & general specification of the server that MYSQL is running on.

Pointers for tuning and optimizing the databases can be found at http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ and http://lists.mysql.com/mysql/214398 and specifically re key_buffer_size at http://lists.mysql.com/mysql/214398

The following example comes from this forum thread http://forums.contribs.org/index.php/topic,46694.0.html and refers to this bug report http://bugs.contribs.org/show_bug.cgi?id=6287

To change the following parameters

key_buffer_size=18M
myisam_sort_buffer_size=8M

Create a custom template fragment & edit it to include your required parameters

mkdir -p /etc/e-smith/templates-custom/etc/my.cnf/
nano -w 011mysetup

Save & Exit

Ctrl o
Ctrl x

Expand the changes & restart mysql

expand-template /etc/my.cnf
sv t /service/mysqld

Check /etc/my.cnf to see that the changes are reflected.