Archive

Archives pour la catégorie ‘Bases de données’

Réplication MySql Croisée

25/03/2017 Comments off
Print Friendly

I► Nous allons voir dans cet article comment mettre en place une réplication croisée MySql en optimisant donc la répartition des charges et la sécurisation des données.

Pour des raisons de simplicité, nous testerons cette config sur le même serveur en utilisant mysqld_multi ( référez vous aux précédents articles )

Ordi2

 

Voici le fichier de conf de MySql

[root@/etc/mysql] cat multi_my.cnf.replicant.double
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = root
password = debian

#——————– Serveur A ——————

[mysqld1]
socket = /tmp/mysql.sock1
port = 3307
pid-file = /var/lib/mysql1/hostname.pid1
datadir = /var/lib/mysql1
language = /usr/share/mysql/french
user = mysql

server-id=1
log-bin = /var/log/mysql/mysql_master_bin.log
binlog_do_db=centrale
replicate-do-db=centrale

master-host = 127.0.0.1
master-port = 3308
master-user = replicant
master-password = replicator

relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

auto_increment_increment = 10
auto_increment_offset = 1

#————–Serveur B ———————-

[mysqld2]
socket = /tmp/mysql.sock2
port = 3308
pid-file = /var/lib/mysql2/hostname.pid2
datadir = /var/lib/mysql2
language = /usr/share/mysql/french
user = mysql
server-id=2
log-bin = /var/log/mysql/mysql_master_bin.log
binlog_do_db=centrale
replicate-do-db=centrale
master-host = 127.0.0.1
master-port = 3307
master-user = replicant
master-password = replicator
master-connect-retry=60
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

auto_increment_increment = 10
auto_increment_offset = 2

Important :

Les fichiers de logs doivent être les mêmes et l’utilisateur de réplication doit bien sûr être créé sur les 2 serveurs maîtres.

Iptables Allow MYSQL server incoming request on port 3306

24/03/2017 Comments off
Print Friendly

MySQL database is a popular for web applications and acts as the database component of the LAMP, MAMP, and WAMP platforms. Its popularity as a web application is closely tied to the popularity of PHP, which is often combined with MySQL. MySQL is open source database server and by default it listen on TCP port 3306. In this tutorial you will learn how to open TCP port # 3306 using iptables command line tool on Linux operating system.

Task: Open port 3306

In most cases following simple rule opens TCP port 3306:

iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT

The following iptable rules allows incoming client request (open port 3306) for server IP address 202.54.1.20. Add rules to your iptables shell script:

iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d 202.54.1.20 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s 202.54.1.20 --sport 3306 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

However in real life you do not wish give access to everyone. For example in a web hosting company, you need to gives access to MySQL database server from web server only. Following example allows MySQL database server access (202.54.1.20) from Apache web server (202.54.1.50) only:

iptables -A INPUT -p tcp -s 202.54.1.50 --sport 1024:65535 -d 202.54.1.20 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s 202.54.1.20 --sport 3306 -d 202.54.1.50 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

Please note if you follow above setup, then you need tell all your hosting customer to use 202.54.1.50 as MySQL host in PHP/Perl code. A better approach is to create following entry in /etc/hosts file or use fully qualified domain name (create dns entry) mysql.hostingservicecompany.com which points to 202.54.1.50 ip:
202.54.1.50 mysql

In shot MySQL database connection code from PHP hosted on our separate webserver would look like as follows:

// ** MySQL settings ** //
define('DB_NAME', 'YOUR-DATABASE-NAME');     // The name of the database
define('DB_USER', 'YOUR-USER-NAME');     // Your MySQL username
define('DB_PASSWORD', 'YOUR-PASSWORD''); // ...and password
define('DB_HOST', 'mysql');       // mysql i.e. 202.54.1.50
// ** rest of PHP code ** //

Lire la suite…

Faille de sécurité : MySQL peut donner les privilèges root à des hackers

26/10/2016 Comments off
Print Friendly

Et il n’y a toujours pas de correctif

mysql hackMySQL fait partie des systèmes de gestion de bases de données les plus utilisés du monde, que ça soit par le grand public ou par les professionnels. De nombreuses entreprises comme Google, Facebook, Yahoo, YouTube, Adobe, l’utilisent encore pour gagner du temps et faire tourner leurs larges sites web, malgré l’émergence et la montée en puissance de nouvelles solutions, notamment les systèmes de gestion de bases de données NoSQL. MySQL est également plébiscité par les petites entreprises en raison de son prix d’implantation nettement inférieur, qui fait de ce système une solution simple et peu onéreuse à mettre en œuvre pour des applications non critiques.

Le chercheur de sécurité polonais Dawid Golunsku a dévoilé deux vulnérabilités dans MySQL, compromettant la sécurité des serveurs. Le chercheur a détaillé l’une des failles de sécurité et a décrit sa méthode d’exploitation. Oracle n’a toujours pas corrigé les deux vulnérabilités, malgré le fait qu’elles ont été signalées il y a plus de quarante jours.

La première vulnérabilité affecte « tous les serveurs MySQL en configuration par défaut dans toutes les versions de MySQL (5.7, 5.6 et 5.5), dont les dernières versions ». Les variantes liées à MySQL, MariaDB et PerconaDB, n’ont pas été épargnées par cette vulnérabilité, néanmoins, des correctifs leur ont été appliqués.

« Une exploitation réussie [de la vulnérabilité CVE-2016-6662] permettrait à un attaquant d’exécuter du code arbitraire avec les privilèges root, ce qui lui permettrait de compromettre entièrement le serveur », explique le chercheur. La faille CVE-2016-6662 peut être exploitée si un hacker a accès à une connexion authentifiée à une base de données MySQL (à travers une connexion réseau ou une interface web comme phpMyAdmin) ou une injection SQL, même avec les modules SELinux et AppArmor installés. Les attaquants peuvent injecter des réglages malicieux dans les fichiers de configuration MySQL, my.cnf, le but étant d’acquérir l’accès root et d’exécuter un code malicieux additionnel. Cette vulnérabilité fait surface 13 ans après qu’un correctif avait été déployé pour remédier à un problème similaire.

Le chercheur a révélé également l’existence d’une seconde faille, néanmoins il n’est pas entré en détail sur la méthode de son exploitation. « Il est à noter que des attaquants peuvent utiliser l’une des autres failles découvertes par l’auteur de ce bulletin, auquel a été assigné l’identifiant CVE CVE-2016-6663 et est en attente de publication. Cette faille facilite la création d’un fichier /var/lib/mysql/my.cnf au contenu arbitraire, sans besoin du privilège FILE ».

Oracle n’a toujours pas adressé officiellement ces vulnérabilités, même si un correctif de sécurité a été publié il y a quelques jours, afin de limiter le risque. Il parait qu’Oracle a secrètement corrigé quelques bogues révélés par Golunski, en limitant les emplacements valides pour charger une bibliothèque au démarrage du service incriminé et en empêchant la génération des fichiers de configuration .ini ou .cnf par la base de données. Même avec ce correctif (MySQL 5.6.33, 5.7.15 et 5.5.52 ?) , le risque reste élevé, surtout avec la persistance d’une deuxième faille non encore détaillée. Si Golunski a révélé l’existence de la vulnérabilité après un mois et demi, avec un prototype limité, c’est pour mettre en garde les utilisateurs afin qu’ils puissent se protéger.

Il faut rappeler que des forks de MySQL, comme par exemple MariaDB et PerconaDB, ont été aussi notifiés de l’existence de la vulnérabilité, et ont déjà pu déployer des correctifs pour corriger les deux failles.

Source : Legalhackers

Les différents moteurs de stockage de MySQL: MyISAM

01/07/2016 Comments off
Print Friendly

II. Le moteur MyISAM

  • Licence : GPL
  • Version de MySQL : Depuis la version 3.23, MyISAM a remplacé le moteur ISAM
  • Type : Moteur Non transactionnel.
  • Domaines d’application :
    – Recherche FULL-TEXT (texte intégrale).
    – Tables en lecture seule.
    – Tables de Log.
  • Information : Documentation officielle de MyISAM

II-A. Description

Depuis sa création, il est devenu le moteur par défaut de MySQL. Il a remplacé Isam en y ajoutant des extensions. En raison de sa souplesse, simplicité et rapidité, MyISAM gagne en popularité dans les applications du web.

Il est très utilisé pour le web car, comme il ne gère ni les clés étrangères, ni les transactions, il n’a pas à vérifier la validité des enregistrements. Cela permet donc un précieux gain de temps sur des tables très fréquemment ouvertes en écriture/lecture.

En effet, lorsque vous faites des suppressions sur des champs de type VARCHAR, CHAR, BLOB ou TEXT, le moteur supprime le contenu mais la place précédemment supprimée est conservée et peut être réutilisée ultérieurement.
OPTMIZE va défragmenter la table afin de gagner de la place et ainsi faciliter l’accès aux données sur cette table.

 

Sélectionnez
OPTIMIZE maTable;

Rien ne sert d’exécuter cette commande sur toutes les tables en permanence. Vous risquez de faire baisser les performances si beaucoup de connexions s’effectuent en même temps.
Cette commande doit être utilisée seulement sur des tables dont la taille évolue rapidement.

D’après ses détracteurs, MySQL ne serait pas capable de gérer les transactions.
En réalité, les personnes ayant utilisé MySQL ne savaient pas qu’il était possible de changer de moteur. Ils sont tombés sur le moteur par défaut, MyISAM, qui ne gère pas les transactions.

Une table MyISAM utilise trois fichiers :
  • maTable.FRM : Fichier de définition de la table
  • maTable.MYD : Fichier contenant les données de la table
  • maTable.MYI : Fichier d’index

Lire la suite…

Les différents moteurs de stockage de MySQL: InnoDB

01/07/2016 Comments off
Print Friendly

X. Le moteur InnoDB

  • Licence : GPL niveau 2. Société InnoBase, filiale depuis 2005 de la société Oracle.
  • Version de MySQL : Par défaut depuis la version 4.0 de MySQL mais il y est possible de l’installer sur une version 3.23 de MySQL.
  • Type : Transactionnel
  • Domaines d’application : Application nécessitant une fiabilité de l’information avec une gestion des transactions

X-A. Description

InnoDB, est le moteur transactionnel le plus utilisé à l’heure actuelle dans les secteurs dit sensibles, c’est-à-dire nécessitant une cohérence et une grande intégrité des données.
Jusqu’à la version 5.1 incluse, c’est le seul moteur supportant les contraintes de clés étrangères (intégrité référentielle).

Il n’est pas concevable d’avoir des informations faisant référence à quelque chose d’inexistant. Peut-on imaginer un numéro de sécurité sociale qui ne soit pas associé à une personne ou un code postal associé à aucune ville ?
Il y a des domaines d’application où les données doivent être fiables à 100%.

Au-delà de l’intégrité référentielle, InnoDB propose des mécanismes transactionnelles présentant une grande compatibilité aux critères ACID.

X-B. Organisation interne

Avec une base de données composée de tables utilisant le moteur InnoDB, il est important de ne pas utiliser les mêmes méthodes qu’avec une base contenant uniquement des tables MyISAM.
Avec les tables utilisant le moteur MyISAM, il est facile de copier, supprimer une base de données : il suffit de copier le répertoire se trouvant dans le répertoire /Data/ portant le même nom que la base de données.
De là, il est possible de le déplacer vers un autre serveur, de réaliser une autre base de donnés à partir de celle-ci, d’effectuer des sauvegardes.
Par contre, si la base de données comporte des tables utilisant le moteur InnoDB, il faudra faire plus attention.
En effet, toutes les données de toutes les tables de toutes les bases sont stockées dans un espace de tables commun. De ce fait, la base devient un peu plus rigide.

Lire la suite…

Sécurisez votre serveur MySQL sous Unix

17/05/2016 Comments off
Print Friendly

securiser mysqlI. Introduction

Les bases de données (notamment MySQL) sont des éléments primordiaux dans un système d’informations. Elles sont incontournables dans de nombreuses entreprises et les données qu’elles contiennent sont souvent d’une importance cruciale. Ignorer la sécurité du système de gestion de base de données serait une erreur grave tant les conséquences d’une détérioration de la base pourraient s’avérer désastreuses.

Ce document explique quelques moyens simples d’améliorer et maintenir la sécurité de MySQL sous système Unix (Linux, BSD…). Il est bien sûr impensable de croire que les conseils de cet article suffiront à obtenir une sécurité optimale.

À l’instar de la sécurisation du SGBD, il faudra veiller à sécuriser le système d’exploitation de la machine hôte, les autres services actifs (serveurs HTTP, FTP…), le réseau, l’accès physique à cette machine et bien sûr vos applications (éviter les failles d’injection SQL).

II. Exécution du serveur : utilisateur système non privilégié

Après installation du serveur MySQL, beaucoup de personnes ont la fâcheuse habitude de lancer le daemon avec les privilèges de l’utilisateur système root. Ce choix peut s’expliquer par une envie d’utiliser la méthode la plus rapide : le serveur MySQL doit lire et écrire dans certains fichiers auxquels l’utilisateur régulier n’a pas accès ; en le lançant avec les privilèges root, on a l’assurance que MySQL aura les droits nécessaires.

Cette méthode d’exécution parait anodine. Cependant, elle peut conduire à des dégâts énormes. Le processus du daemon MySQL tournant avec les privilèges super-utilisateur, il a tous les droits sur le système. Imaginons maintenant qu’un pirate puisse prendre contrôle de ce processus (généralement par exploitation d’une faille de type buffer overflow dans MySQL), il deviendra dès lors le maître absolu de la machine hôte du serveur. Il pourra créer des fichiers, installer des programmes, lire des données confidentielles, ou supprimer des données.

Bien sûr, si une faille est présente dans MySQL, cela n’est pas la faute de l’administrateur. Mais ce n’est pas une raison pour ne pas s’en protéger. Il est impossible d’empêcher un pirate d’exploiter une telle faille. Les seuls à pouvoir le faire sont les développeurs de MySQL AB, qui, dans une telle situation, proposeront une nouvelle version du serveur.

De son côté, l’administrateur du serveur peut limiter les dégâts afin que le pirate ayant pris le contrôle du processus MySQL ne puisse pas détruire autre chose que la base de données (ce qui n’est déjà pas mal !). Le principe est assez simple : créer un utilisateur spécial dont la seule fonction sera de lancer MySQL. Cet utilisateur doit posséder le moins de droits système possible. La solution optimale serait que cet utilisateur n’ait accès qu’aux fichiers propres à MySQL.

Les administrateurs qui ont suivi le fichier INSTALL ou la documentation officielle de MySQL auront remarqué que la création d’un utilisateur « mysql » est préconisée.

Pour les autres, nous allons voir comment y remédier. Les commandes suivantes permettent de créer un groupe système mysql ainsi qu’un utilisateur mysql appartenant au groupe précédemment créé.

# groupadd mysql
# useradd -g mysql mysql

Pour des raisons de sécurité, il est préférable de s’assurer que cet utilisateur ne puisse pas se logger et ne possède pas de home. La commande suivante permet de spécifier ces options sous OpenBSD. Les paramètres de la commande useradd n’étant pas les mêmes d’un système à l’autre, référez-vous au manuel (man useradd) pour obtenir une commande équivalente chez vous.

# useradd -d /nonexistent -s /sbin/nologin -g mysql mysql

Sous Linux, vous pouvez spécifier /bin/false en tant que shell de l’utilisateur pour qu’il ne puisse pas se logger.

Maintenant que notre utilisateur est créé, nous allons lui donner accès en lecture/écriture aux répertoires de données (tables, index, enregistrements…) de MySQL. Pour cela, nous allons le désigner comme propriétaire de ces fichiers. Par la même occasion, nous nous assurons que l’utilisateur système root est propriétaire des autres fichiers de MySQL :

# cd REPERTOIRE_MYSQL
# chown -R root .
# chown -R mysql data
# chgrp -R mysql .

La commande précédente suppose que le dossier de données de MySQL se trouve dans le même répertoire que les autres fichiers de MySQL. Dans le cas contraire, adaptez la commande selon votre installation.

Assurons-nous ensuite que seuls l’utilisateur et le groupe mysql puissent avoir accès aux fichiers de données :

# chmod -R 660 data
# chmod 700 data/mysql

Une fois les droits attribués, nous pouvons lancer le serveur MySQL en spécifiant que l’utilisateur mysql que nous venons de créer sera le propriétaire du processus. Pour cela, deux possibilités s’offrent à nous.

La première est de rajouter une option à la commande permettant de lancer le serveur MySQL :

# cd REPERTOIRE_MYSQL
# bin/mysqld_safe --user=mysql &

La seconde consiste à modifier le fichier de configuration de MySQL (ex : /etc/my.cnf) afin de spécifier que l’utilisateur par défaut pour l’exécution du serveur est mysql. Il ne sera dès lors plus nécessaire de le répéter lors du lancement du serveur.

[mysqld]
user= mysql

Vérifions enfin que notre serveur MySQL tourne avec des droits limités. Pour cela, nous allons utiliser la commande ps après lancement de MySQL.

ps -aux | grep 'mysql'

La première colonne du résultat de la commande indique le propriétaire du processus. Si votre configuration est correcte, vous devriez donc voir mysql.

Lire la suite…

MySQL: Améliorer les performances grâce au partitionnement

17/05/2016 Comments off
Print Friendly

mysql partitionnementI. Introduction

I-A. Définition

Pour tout bon informaticien, le terme « partitionnement » n’est pas inconnu. Cependant, bien que le partitionnement d’un disque dur soit devenu une banalité, l’utilisation du partitionnement en base de données reste encore rare.

Faisons tout d’abord un rappel sur le partitionnement d’un disque dur. L’intérêt de créer différentes partitions est de pouvoir organiser les données : des partitions sont réservées aux fichiers des systèmes d’exploitation installés et d’autres pour les données personnelles (photos, téléchargements…).

Dans cet article, nous allons nous intéresser au partitionnement dans le domaine des bases de données, et plus exactement au partitionnement de tables. Le but est, comme pour les disques durs, d’organiser les données. Néanmoins, nous n’allons pas utiliser cette organisation pour simplifier nos requêtes, mais bien pour en améliorer les performances !

I-B. Les types de partitionnement

Deux grands types de partitionnement sont utilisés pour partitionner une table :

  • partitionnement horizontal : les enregistrements (= lignes) d’une table sont répartis dans plusieurs partitions. Il est nécessaire de définir une condition de partitionnement, qui servira de règle pour déterminer dans quelle partition ira chaque enregistrement.

Exemple : nous disposons d’une table Amis et nous choisissons de la partitionner en

deux partitions :

  1. Les amis dont la première lettre du prénom est comprise entre A et M,
  2. Les amis dont la première lettre du prénom est comprise entre N et Z.
Image non disponible

Pour récupérer la liste complète de nos amis, il sera nécessaire de regrouper le contenu de nos deux partitions. Pour ce faire, une simple opération d’union d’ensembles suffit ;

  • partitionnement vertical : les colonnes d’une table sont réparties dans plusieurs partitions. Cela peut être pratique pour écarter des données fréquemment utilisées d’autres auxquelles l’accès est plus rare.

Exemple : nous disposons d’une table Amis contenant les prénom et photo de chacun de nos amis. Les photos prenant de la place et étant rarement accédées, nous décidons de les écarter des autres données.

Image non disponible

Comme vous pouvez le remarquer, les deux partitions contiennent l’identifiant des amis. Cela est nécessaire afin de garder le lien entre les données de chaque enregistrement. Ainsi, pour récupérer toutes les informations des amis, il suffit de faire une jointure entre les deux partitions.

Nous connaissons maintenant les deux types de partitionnement. Il est bien sûr possible d’utiliser un partitionnement vertical et un partitionnement horizontal sur une même table, ainsi que de partitionner sur plusieurs niveaux, c’est-à-dire définir des partitions de partitions.

I-C. Les avantages du partitionnement

Le partitionnement apporte plusieurs avantages à un administrateur de base de données. Voici les principaux intérêts du partitionnement :

  • pouvoir créer des tables plus grandes que la taille permise par un disque dur ou par une partition du système de fichiers : il est tout à fait possible de stocker des partitions à des endroits (partitions, disques, serveurs…) différents ;
  • pouvoir supprimer très rapidement des données qui ne sont plus utiles et utilisées : si ces données sont placées sur une partition séparée, il suffit de détruire la partition pour supprimer toutes les données ;
  • optimiser grandement certaines requêtes : les données étant organisées dans différentes partitions, le SGBD n’accède qu’aux données nécessaires lors des requêtes. Sans partitionnement, tous les enregistrements sont pris en compte.

Enfin, notons que le partitionnement de tables est généralement utilisé avec des bases de données réparties où les données sont placées sur des sites géographiques (éloignés ou proches) différents. Cet article se limite au partitionnement sur site local, mais les principes évoqués sont valides pour le partitionnement distant.

I-D. Notes à propos de MySQL

Dans MySQL, le partitionnement n’est géré de manière automatique qu’à partir de la version 5.1. Lors de la rédaction de cet article, MySQL 5.1 est encore en version bêta et par conséquent, il est possible que des bogues subsistent.

De plus, il est important de noter que seules les fonctionnalités de base du partitionnement seront disponibles dans la version finale de MySQL 5.1. Mais il est évident que les versions futures du SGBD apporteront leur lot d’améliorations.

Lire la suite…

Ten MySQL performance tuning settings after installation

04/05/2016 Comments off
Print Friendly

mysql performance tuningIn this blog we’re going to discuss the top ten MySQL performance tuning settings that you can implement after an installation.

When we are hired for a MySQL performance audit, we are expected to review the MySQL configuration and to suggest improvements. Many people are surprised because in most cases, we only suggest changing a few MySQL performance tuning settings after installation – even though hundreds of options are available. The goal of this post is to give you a list of some of the most critical settings.

We already made such suggestions in the past here on this blog a few years ago, but things have changed a lot in the MySQL world since then!

Before we start…

Even experienced people can make mistakes that can cause a lot of trouble. So before blindly applying the recommendations of this post, please keep in mind the following items:

  • Change one setting at a time! This is the only way to estimate if a change is beneficial.
  • Most settings can be changed at runtime with SET GLOBAL. It is very handy and it allows you to quickly revert the change if it creates any problem. But in the end, you want the setting to be adjusted permanently in the configuration file.
  • A change in the configuration is not visible even after a MySQL restart? Did you use the correct configuration file? Did you put the setting in the right section? (all settings in this post belong to the [mysqld] section)
  • The server refuses to start after a change: did you use the correct unit? For instance, innodb_buffer_pool_size should be set in bytes while max_connection is dimensionless.
  • Do not allow duplicate settings in the configuration file. If you want to keep track of the changes, use version control.
  • Don’t do naive math, like “my new server has 2x RAM, I’ll just make all the values 2x the previous ones”.

Basic settings fro MySQL performance

Here are 3 MySQL performance tuning settings that you should always look at. If you do not, you are very likely to run into problems very quickly.

innodb_buffer_pool_size: this is the #1 setting to look at for any installation using InnoDB. The buffer pool is where data and indexes are cached: having it as large as possible will ensure you use memory and not disks for most read operations. Typical values are 5-6GB (8GB RAM), 20-25GB (32GB RAM), 100-120GB (128GB RAM).

innodb_log_file_size: this is the size of the redo logs. The redo logs are used to make sure writes are fast and durable and also during crash recovery. Up to MySQL 5.1, it was hard to adjust, as you wanted both large redo logs for good performance and small redo logs for fast crash recovery. Fortunately crash recovery performance has improved a lot since MySQL 5.5 so you can now have good write performance and fast crash recovery. Until MySQL 5.5 the total redo log size was limited to 4GB (the default is to have 2 log files). This has been lifted in MySQL 5.6.

Starting with innodb_log_file_size = 512M (giving 1GB of redo logs) should give you plenty of room for writes. If you know your application is write-intensive and you are using MySQL 5.6, you can start with innodb_log_file_size = 4G.

max_connections: if you are often facing the ‘Too many connections’ error, max_connections is too low. It is very frequent that because the application does not close connections to the database correctly, you need much more than the default 151 connections. The main drawback of high values for max_connections (like 1000 or more) is that the server will become unresponsive if for any reason it has to run 1000 or more active transactions. Using a connection pool at the application level or a thread pool at the MySQL level can help here.

Lire la suite…

How to Optimize MySQL Tables and Defragment to Recover Space

04/05/2016 Comments off
Print Friendly

MySQL OPTIMIZE TABLE command

If your application is performing lot of deletes and updates on MySQL database, then there is a high possibility that your MySQL data files are fragmented.

This will result in lot of unused space, and also might affect performance.

So, it is highly recommended that you defrag your MySQL tables on an ongoing basis.

This tutorial explains how to optimize MySQL to defrag tables and reclaim unused space.

1. Identify Tables for Optimization

The first step is to identify whether you have fragmentation on your MySQL database.

Connect to your MySQL database, and execute the following query, which will display how much unused space are available in every table.

mysql> use thegeekstuff;

mysql> select table_name,
round(data_length/1024/1024) as data_length_mb, 
round(data_free/1024/1024) as data_free_mb 
 from information_schema.tables 
 where round(data_free/1024/1024) > 500 
 order by data_free_mb;

+------------+----------------+--------------+
| table_name | data_length_mb | data_free_mb |
+------------+----------------+--------------+
| BENEFITS   |           7743 |         4775 |
| DEPARTMENT |          14295 |        13315 |
| EMPLOYEE   |          21633 |        19834 |
+------------+----------------+--------------+

In the above output:

  • This will display list of all tables that has minimum of 500MB of unused space. As we see above, in this example, there are 3 tables that has more than 500MB of unused space.
  • data_length_mb column displays the total table size in MB. For example, EMPLOYEE table size is around 21GB.
  • data_free_mb column displays the total unused space in that particular table. For example, EMPLOYEE table has around 19GB of unused space in it.
  • All these three tables (EMPLOYEE, DEPARTMENT AND BENEFITS) are heavily fragmented and it needs to be optimized to reclaim the unused space.

From the filesystem level, you can see the size of the individual table files as shown below.

The file size will be the same as what you see under “data_length_mb” column in the above output.

# ls -lh /var/lib/mysql/thegeekstuff/
..
-rw-rw----. 1 mysql mysql  7.6G Apr 23 10:55 BENEFITS.MYD
-rw-rw----. 1 mysql mysql   14G Apr 23 12:53 DEPARTMENT.MYD
-rw-rw----. 1 mysql mysql   22G Apr 23 12:03 EMPLOYEE.MYD
..

In this example, the EMPLOYEE.MYD file is taking up around 22GB at the filesystem level, but it has lot of unused space in it. If we optimize this table, the size of this file should go down dramatically.

Lire la suite…

How to enable SSL for MySQL server and client with ssh

28/04/2016 Comments off
Print Friendly

MySQL secure SSH

When users want to have a secure connection to their MySQL server, they often rely on VPN or SSH tunnels. Yet another option for securing MySQL connections is to enable SSL wrapper on an MySQL server. Each of these approaches has its own pros and cons. For example, in highly dynamic environments where a lot of short-lived MySQL connections occur, VPN or SSH tunnels may be a better choice than SSL as the latter involves expensive per-connection SSL handshake computation. On the other hand, for those applications with relatively few long-running MySQL connections, SSL based encryption can be reasonable. Since MySQL server already comes with built-in SSL support, you do not need to implement a separate security layer like VPN or SSH tunnel, which has their own maintenance overhead.

The implementation of SSL in an MySQL server encrypts all data going back and forth between a server and a client, thereby preventing potential eavesdropping or data sniffing in wide area networks or within data centers. In addition, SSL also provides identify verification by means of SSL certificates, which can protect users against possible phishing attacks.

In this article, we will show you how to enable SSL on MySQL server. Note that the same procedure is also applicable to MariaDB server.

Creating Server SSL Certificate and Private Key

We have to create an SSL certificate and private key for an MySQL server, which will be used when connecting to the server over SSL.

First, create a temporary working directory where we will keep the key and certificate files.

$ sudo mkdir ~/cert
$ cd ~/cert

Make sure that OpenSSL is installed on your system where an MySQL server is running. Normally all Linux distributions have OpenSSL installed by default. To check if OpenSSL is installed, use the following command.

$ openssl version
OpenSSL 1.0.1f 6 Jan 2014

Now go ahead and create the CA private key and certificate. The following commands will create ca-key.pem and ca-cert.pem.

$ openssl genrsa 2048 > ca-key.pem
$ openssl req -sha1 -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem

The second command will ask you several questions. It does not matter what you put in these field. Just fill out those fields.

The next step is to create a private key for the server.

$ openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout server-key.pem > server-req.pem

This command will ask several questions again, and you can put the same answers which you have provided in the previous step.

Next, export the server’s private key to RSA-type key with this command below.

$ openssl rsa -in server-key.pem -out server-key.pem

Finally, generate a server certificate using the CA certificate.

$ openssl x509 -sha1 -req -in server-req.pem -days 730 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

Lire la suite…