3,250
edits
Changes
From SME Server
Jump to navigationJump to searchupdated to SME10
{{Languages}}
{{Languages}}
SME Server up to and including version 9.x runs MySQL as a database server: versions 10 on use MariaDB to provide this function.
SME Server up to and including version 9.x runs MySQL as a database server.
SME Server 10 uses MariaDB to provide this function.
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.
==General==
==General==
{{warning box|Koozali SME Server Version 10: MySQL is provided by MariaDB. You can check the version in the usual way, e.g. at the time of writing version 15.1
{{warning box|Koozali SME Server Version 10: MySQL is provided by MariaDB. You can check the version in the usual way, e.g. at the time of writing version 5.5
}}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
The SME Server is based on CentOS, the development team will take their stock RPM's from the CentOS releases. The current version of MariaDB installed on SME Server is version 5.5.68.You can upgrade MariaDB, using their rpms, 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 MariaDB. Upgrading to version 10.x has potential to break stuff like webmail. If you insist on upgrading MariaDB 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.
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.
Alternatively you can rely on contribs and Red-Hat Software collection to add [[Mysql57|MySQL 5.7]] and MariaDB [[Mariadb101|10.1]] [[Mariadb102|10.2]] [[Mariadb103|10.3]] or [[Mariadb105|10.5]] as secondary SQL service to satisfy your needs.
MariaDB 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).
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==
{{Note box|All MariaDB services provided by core or contribs shares the same /etc/my.cnf file as configuration file. Please respect the sections inside the file when adding some new template-custom depending if you want this be seen by all running version or a specific version. You can refer to MariaDB manual for more information.
While MySQL supports this kind of configuration, for backward compatibility of the contrib MySQL57 we kept a separate config file.}}
[mysqld]
[mysqld_safe]
[mysql-5.7]
[mariadb-10.1]
[mariadb-10.2]
[mariadb-10.3]
[mariadb-10.5]
== Access to MariaDB/ MySQL from my application ==
As stated above on SME Server you have to use socket, this is more secure than using port. By default the service only listen on the server using socket, so trying to connect with any port will result in a failure.
Most application will have to define a string to access the socket, as below pointing to localhost (not 127.0.0.1, nor the LAN ip) and the full path to the socket. In some situation you will have to define the socket path and the host (localhost again and not 127.0.0.1) in variables.
define( 'DB_HOST', 'localhost:/var/lib/mysql/mysql.sock' );
==MariaDB/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.
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.
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.
For SME Server 7.3 and more recent versions do the following:
For SME 10 (note for SCLO version see respective contrib page):
systemctl stop mariadb
expand-template /root/.my.cnf
expand-template /var/lib/mysql/set.password
/usr/libexec/mysqld --socket=/var/lib/mysql/mysql.sock --bootstrap --user=mysql --skip-grant-tables < /var/lib/mysql/set.password
exit
systemctl start mariadb
For SME Server 7.3 and up to SME 9.2:
cd /var/service/mysqld
cd /var/service/mysqld
sv d .
sv d .
runsvctrl u .
runsvctrl u .
===Restoring accidently deleted MySQL root user===
===Restoring accidentally deleted MySQL root user===
Note: The following is only applicable on SME 7.3 and MySQL 4.1
Note: The following is only applicable on SME 7.3 and MySQL 4.1
cd /var/service/mysqld
cd /var/service/mysqld
sv u .
sv u .
==Access MySQL from the local network==
==Access MariaDB/MySQL using port from the localhost and local network==
MySQL on SME Server is run on a socket instead of on a port.
MariaDB/MySQL on SME Server runs 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.
MariaDB/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 (note access private is not needed as this is the default, and TCPPort 3306 neither as all ports are open to the LAN by default):
{{Warning box|Keep in mind that by default MariaDB/MySQL is not using any kind of encryption unless you did work on that yourself, so any access to the port from the LAN will be as clear text and anyone on the LAN will be able to access to the password and all the data transferred between your server and the client. Refers to the manual of your database version.}}
For SME 10:
config setprop mariadb LocalNetworkingOnly no
expand-template /etc/my.cnf
sv t /service/mysqld
For SME 9 and below:
config setprop mysqld LocalNetworkingOnly no
config setprop mysqld LocalNetworkingOnly no
expand-template /etc/my.cnf
expand-template /etc/my.cnf
==Access MySQL from a remote network==
==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:
If you wish to enable access to MariaDB/MySQL databases from remote networks, then in addition to the LocalNetworkingOnly db setting mentioned above, you will need to execute the following commands:
For SME 10
config set mariadb service access public status enabled TCPPort 3306
signal-event remoteaccess-update
signal-event reboot
For SME9 and below
config set mysqld service access public status enabled TCPPort 3306
config set mysqld service access public status enabled TCPPort 3306
signal-event remoteaccess-update
signal-event remoteaccess-update
signal-event reboot
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.
Keep in mind this enables access to your MariaDB/MySQL database for ANYONE, so make sure you have strong passwords on ALL your MariaDB/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.
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.
{{Warning box|Keep in mind that by default MariaDB/MySQL is not using any kind of encryption unless you did work on that yourself, so any access to the port from the WAN will be as clear text and anyone on the Internet will be able to access to the password and all the data transferred between your server and the client. Refers to the manual of your database version.}}
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).
==Create MariaDB/MySQL user(s) with access from other computers==
SME Server's default MariaDB/MySQL database users, and most of the database examples in the wiki, allow login only from ''localhost''.
If you want to access a MariaDB/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===
===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]).
Assuming your local network is 192.168.1.0, you can create a user with MariaDB/MySQL access from any LAN workstation (or VPN client) using the command shown below (courtesy 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.}}
{{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:
You probably want to change:
*the user name ('''MyUser''')
*the user name ('''MyUser''')
*the password ('''MyPW''') and
*the password ('''MyPW''') and
*the allowed computers ('''192.168.xx.%''')
*the allowed computers ('''192.168.1.%''')
## 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.1.%' \
IDENTIFIED BY 'MyPW'; \
FLUSH PRIVILEGES;"
===Security Implications of allowing remote mysql login===
===Security Implications of allowing remote MariaDB/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.
It is technically possible to combine the above techniques to allow remote MariaDB/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.
If you have remote users who need access to your MariaDB/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. On top of that, you are encouraged to enforce encrypted connection at the level of you MariaDB/MySQL service to avoid any clear text exchange on the LAN or worse on the Internet.
==Enable InnoDB engine==
==Enable InnoDB engine==
===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 MariaDB/MySQL Manual found at the MariaDB/MySQL site. Get access to the SME Server shell and issue the following commands to login to the MySQL server:
mysql
mysql
GRANT SELECT ON test.* TO 'user'@'host' IDENTIFIED BY 'password';
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.
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 MariaDB/MySQL server (don't forget the single quotes). More information can be found in the MariaDB/MySQL Server Manual at the MariaDB/MySQL website linked here.
===Listing available databases===
===Listing available databases===
To view a list of available databases on the system you can issue the following command while logged in in MySQL:
To view a list of available databases on the system you can issue the following command while logged in in MariaDB/MySQL:
show databases;
show databases;
===Remove a database===
===Remove a database===
Get access to the SME Server shell and MySQL and issue the following command:
Get access to the SME Server shell and MariaDB/MySQL and issue the following command:
drop database databasename;
drop database databasename;
===Remove a user===
===Remove a user===
Get access to the SME Server shell and MySQL and issue the following command:
Get access to the SME Server shell and MariaDB/MySQL and issue the following command:
USE mysql;
USE mysql;
{{Tip box|mysql_setpermission is a command line menu driven utility that can assist in MySQL administration.}}
{{Tip box|mysql_setpermission is a command line menu driven utility that can assist in MySQL administration.}}
==Optimizing MYSQL default settings==
== Optimizing MariaDB/MySQL default settings for SME 10 ==
Here are the available settings from the configuration database to tweak you MariaDB service. If no default value indicated, please refers the the manual of your database version for its own default value:
{| class="wikitable"
!key
!default
!Role
|-
|innodb_file_format
|barracuda
|
|-
|innodb_file_per_table
|1
|
|-
|LocalNetworkingOnly
|no
|
|-
|OpenFilesLimit
|0
|
|-
|MaxConnections
|
|
|-
|WaitTimeout
|
|
|-
|QueryCacheLimit
|
|
|-
|QueryCacheSize
|1M
|
|-
|QueryCacheType
|1
|
|-
|SortBufferSize
|
|
|-
|ReadRndBufferSize
|
|
|-
|TableOpenCache
|
|
|-
|TableOpenCacheInstances
|
|
|-
|TmpTableSize
|
|
|-
|MaxHeapTableSize
|
|
|-
|ThreadCacheSize
|256
|
|-
|KeyBufferSize
|
|key_buffer_size
|-
|MyisamSortBufferSize
|
|myisam_sort_buffer_size
|-
|JoinBufferSize
|262144
|
|-
|ReadBufferSize
|
|
|-
|MaxConnectErrors
|
|
|-
|ConnectTimeout
|100
|
|-
|MaxAllowedPacket
|16M
|
|-
|SlowQueries
|
|
|}
to alter a value, just do
config set mariadb KeyBufferSize 18M MyisamSortBufferSize 8M
expand-template /etc/my.cnf
systemctl restart mariadb
if your needed option is not available then create a dedicated template custom. '''Be careful to use a name starting with a number between 016 and 039'''.
mkdir -p /etc/e-smith/templates-custom/etc/my.cnf/
vim /etc/e-smith/templates-custom/etc/my.cnf/017myvalues
expand-template /etc/my.cnf
systemctl restart mariadb
==Optimizing MariaDB/MySQL default settings for up to SME9==
SME Server uses MYSQL for the webmail package, and the default configuration is optimized for that.
SME Server uses MariaDB/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.
If you are using the SME server to provide MariaDB/MySQL databases for functions running on workstations, you may need to adjust some of the default MariaDB/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 MariaDB/MySQL, you will also need to consider the system resources & general specification of the server that MariaDB/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
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
Create a custom template fragment & edit it to include your required parameters
Create a custom template fragment & edit it to include your required parameters
mkdir -p /etc/e-smith/templates-custom/etc/my.cnf/
mkdir -p /etc/e-smith/templates-custom/etc/my.cnf/
vim /etc/e-smith/templates-custom/etc/my.cnf/016mysetup
Save & Exit
Save & Exit
Ctrl o
Ctrl o
