MySQL/fr
Le serveur SME, jusqu'à la version 9.2 incluse, exécute MySQL en tant que gestionnairre de base de données.
Le serveur Koozali SME 10 utilise MariaDB pour réaliser cette fonction. De nombreuses applications nécessitent une base de données MySQL, parmi lesquelles l'interface webmail Horde qui est fournie par défaut par le serveur SME.
- Site web de MySQL : http://www.mysql.com
- Manuel de MySQL 4.1 : http://dev.mysql.com/doc/refman/4.1/en/
- Documentation de MariaDB : https://mariadb.org/documentation/
Généralités
Le serveur SME est fondé sur CentOS, l'équipe de développement prendra leurs paquets en stock à partir des versions de CentOS. La version actuelle de MariaDB installée sur le serveur SME est la version 5.5.68. L'interface de messagerie Web Horde s'appuie sur MariaDB. La mise à niveau vers la version 10.x peut potentiellement casser des choses comme le webmail. Si vous insistez pour mettre à niveau MariaDB, vous pourrez peut-être trouver des instructions sur le forum, mais sachez qu'aucun support ne peut être attendu des développeurs et que tous les bogues signalés dans Bugzilla ne seront pas pris en compte.
Vous pouvez également vous fier aux contributions et à la collection logicielle Red-Hat pour ajouter MySQL 5.7 et MariaDB 10.1 10.2 10.3 ou 10.5 comme service SQL secondaire pour répondre à vos besoins.
MariaDB s'exécute sur le serveur SME sur un socket (une interface de connexion) au lieu d'un port - auquel vous êtes peut-être habitué. Ceci est fait pour améliorer la sécurité car, de l'avis de l'équipe de développement, seul le serveur lui-même (localhost) doit avoir accès au serveur MySQL. Cependant, vous pouvez configurer MySQL pour qu'il soit accessible depuis le réseau local (voir ci-dessous).
[mysqld] [mysqld_safe] [mysql-5.7] [mariadb-10.1] [mariadb-10.2] [mariadb-10.3] [mariadb-10.5]
Accès à MariaDB/MySQL depuis mon application
Comme indiqué ci-dessus, sur SME Server, vous devez utiliser un socket (une interface de connexion), c'est plus sûr que d'utiliser un port. Par défaut, le service n'écoute que sur le serveur à l'aide d'un socket, donc essayer de se connecter avec n'importe quel port entraînera un échec.
La plupart des applications devront définir une chaîne pour accéder au socket, comme ci-dessous pointant vers localhost (pas 127.0.0.1, ni l'ip LAN) et le chemin complet vers le socket. Dans certaines situations, vous devrez définir le chemin du socket et l'hôte (localhost à nouveau et non 127.0.0.1) dans des variables.
define( 'DB_HOST', 'localhost:/var/lib/mysql/mysql.sock' );
Mot de passe root MariaDB/MySQL
Il semble qu'il n'y ait pas de mot de passe défini comme mot de passe root MySQL, mais ce n'est pas vrai. Si vous êtes connecté au shell du serveur SME, un mécanisme spécial est en place pour vous connecter avec les privilèges root de MySQL sans vous demander le mot de passe.
Le mot de passe root MySQL pour le serveur SME est une chaîne aléatoire de 72 caractères générée lors de l'installation du serveur SME. Vous ne devez jamais modifier le mot de passe root MySQL car cela casserait la configuration de votre serveur SME. Comment se connecter en tant qu'utilisateur root MySQL ? décrit comment accéder à MySQL avec les privilèges root sur le serveur SME.
Se connecter en tant qu'utilisateur root de MySQL
Pour se connecter en tant qu'utilisateur root de MySQL, saisir simplement 'mysql' sur le shell du serveur SME, ceci vous connectera avec les privilèges de root.
Réinitialiser le mot de passe root de MySQL
Pour réinitialiser le mot de passe du compte root MySQL : l'utilisateur root de MySQL sur le serveur SME a un mot de passe généré aléatoirement qui est créé lors de l'installation. Vous n'avez pas besoin de connaître ce mot de passe pour vous connecter à MySQL avec les privilèges root sur le serveur SME. Si vous avez peut-être changé le mot de passe root MySQL, vous pouvez le réinitialiser comme ci-dessous après avoir obtenu l'accès à la ligne de commande en tant qu'utilisateur root.
Pour SME 10 (noter que pour les versions SCLO (???, ndt), voir la page de la contribution correspondante) :
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
Pour le serveur SME 7.3 et jusqu'au serveur SME 9.2 :
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 .
Pour le serveur SME 7.2 et les versions précédantes, exécuter ce qui suit (on utilise la commande runsvctrl à la place de la commande sv ) :
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 .
Restaurer un effacement accidentel du mot de passe de l'utilisateur root de MySQL
Note : ce qui suit n'est applicable que sur le serveur SME 7.3 et à 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 .
Accès à MariaDB/MySQL using port from the localhost and local network
MariaDB/MySQL on SME Server runs on a socket instead of on a port. 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 (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):
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 expand-template /etc/my.cnf sv t /service/mysqld
Access MySQL from a remote network
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 signal-event remoteaccess-update signal-event reboot
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.
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 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.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 DarkMirage).
You probably want to change:
- the database name (MyDB)
- the user name (MyUser)
- the password (MyPW) and
- 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 MariaDB/MySQL login
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 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
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.
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 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
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 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
To view a list of available databases on the system you can issue the following command while logged in in MariaDB/MySQL:
show databases;
Remove a database
Get access to the SME Server shell and MariaDB/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 MariaDB/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.
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:
| 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 MariaDB/MySQL for the webmail package, and the default configuration is optimized for that.
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
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/ vim /etc/e-smith/templates-custom/etc/my.cnf/016mysetup
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.