Archive

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

How to Optimize MySQL Performance Using MySQLTuner

05/11/2017 Comments off

Running MySQL at optimal settings for specific resources helps handle larger server loads and prevents server slowdown. Generally, after tuning Apache to handle larger loads, it is beneficial to tune MySQL to additional connections.

MySQL tuning title graphic

Database tuning is an expansive topic, and this guide covers only the basics of editing your MySQL configuration. Large MySQL databases can require a considerable amount of memory. For this reason, we recommend using a high memory Linode for such setups.

The steps in this guide require root privileges. Be sure to run the steps below as root or with the sudo prefix. For more information on privileges see our Users and Groups guide.

Tools That Can Help Optimize MySQL

In order to determine if your MySQL database needs to be reconfigured, it is best to look at how your resources are performing now. This can be done with the top command or with the Linode Longview service. At the very least, you should familiarize yourself with the RAM and CPU usage of your server, which can be discovered with these commands:

1
2
echo [PID]  [MEM]  [PATH] &&  ps aux | awk '{print $2, $4, $11}' | sort -k2rn | head -n 20
ps -eo pcpu,pid,user,args | sort -k 1 -r | head -20

MySQLTuner

The MySQLTuner script assesses your MySQL installation, and then outputs suggestions for increasing your server’s performance and stability.

  1. Download and run MySQLTuner:

    1
    curl -L http://mysqltuner.pl/ | perl
    
  2. It outputs your results:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
     >>  MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    Please enter your MySQL administrative login: root
    Please enter your MySQL administrative password:
    [OK] Currently running supported MySQL version 5.5.41-0+wheezy1
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in InnoDB tables: 1M (Tables: 11)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 11
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 47s (113 q [2.404 qps], 42 conn, TX: 19K, RX: 7K)
    [--] Reads / Writes: 100% / 0%
    [--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
    [OK] Maximum possible memory usage: 597.8M (60% of installed RAM)
    [OK] Slow queries: 0% (0/113)
    [OK] Highest usage of available connections: 0% (1/151)
    [OK] Key buffer size / total MyISAM indexes: 16.0M/99.0K
    [!!] Query cache efficiency: 0.0% (0 cached / 71 selects)
    [OK] Query cache prunes per day: 0
    [OK] Temporary tables created on disk: 25% (54 on disk / 213 total)
    [OK] Thread cache hit rate: 97% (1 created / 42 connections)
    [OK] Table cache hit rate: 24% (52 open / 215 opened)
    [OK] Open file limit used: 4% (48/1K)
    [OK] Table locks acquired immediately: 100% (62 immediate / 62 locks)
    [OK] InnoDB buffer pool / data size: 128.0M/1.2M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
    Variables to adjust:
        query_cache_limit (> 1M, or use smaller result sets)
    

    MySQLTuner offers suggestions regarding how to better the database’s performance. If you are wary about updating your database on your own, following MySQLTuner’s suggestions is one of the safer ways to improve your database performance.

Tuning MySQL

When altering the MySQL configuration, be alert to the changes and how they affect your database. Even when following the instructions of programs such as MySQLTuner, it is best to have some understanding of the process.

The file you are changing is located at /etc/mysql/my.cnf.

Prior to updating the MySQL configuration, create a backup of the my.cnf file:

1
cp /etc/mysql/my.cnf ~/my.cnf.backup

Best practice suggests that you make small changes, one at a time, and then monitor the server after each change. You should restart MySQL after each change:

  • For systems without systemd:

    1
    systemctl restart mysqld
    
  • For distributions which don’t use systemd:

    1
    service mysql restart
    

When changing values in the my.cnf file, be sure that the line you are changing hasn’t been commented out with the pound (#) prefix.

key_buffer

Changing the key_buffer allocates more memory to MySQL, which can substantially speed up your databases, assuming you have the memory free. The key_buffer size should generally take up no more than 25 percent of the system memory when using the MyISAM table engine, and up to 70 percent for InnoDB. If the value is set too high, resources are wasted.

According to MySQL’s documentation, for servers with 256MB (or more) of RAM with many tables, a setting of 64M is recommended. Servers with 128MB of RAM and fewer tables can be set to 16M, the default value. Websites with even fewer resources and tables can have this value set lower.

max_allowed_packet

This parameter lets you set the maximum size of a sendable packet. A packet is a single SQL state, a single row being sent to a client, or a log being sent from a master to a slave. If you know that your MySQL server is going to be processing large packets, it is best to increase this to the size of your largest packet. Should this value be set too small, you would receive an error in your error log.

thread_stack

This value contains the stack size for each thread. MySQL considers the default value of the thread_stack variable sufficient for normal use; however, should an error relating to the thread_stack be logged, this can be increased.

thread_cache_size

If thread_cache_size is “turned off” (set to 0), then any new connection being made needs a new thread created for it. When the connections disengage the thread is destroyed. Otherwise, this value sets the number of unused threads to store in a cache until they need to be used for a connection. Generally this setting has little affect on performance, unless you are receiving hundreds of connections per minute, at which time this value should be increased so the majority of connections can be made on cached threads.

max_connections

This parameter sets the maximum amount of concurrent connections. It is best to consider the maximum amount of connections you have had in the past before setting this number, so you’ll have a buffer between that upper number and the max_connections value. Note, this does not indicate the maximum amount of users on your website at one time; rather it shows the maximum amount of users making requests concurrently.

table_cache

This value should be kept higher than your open_tables value. To determine this value use:

1
SHOW STATUS LIKE 'open%';

 

Print Friendly, PDF & Email

10 essential performance tips for MySQL

05/11/2017 Comments off

As with all relational databases, MySQL can prove to be a complicated beast, one that can crawl to a halt at a moment’s notice, leaving your applications in the lurch and your business on the line.

The truth is, common mistakes underlie most MySQL performance problems. To ensure your MySQL server hums along at top speed, providing stable and consistent performance, it is important to eliminate these mistakes, which are often obscured by some subtlety in your workload or a configuration trap.

Luckily, many MySQL performance issues turn out to have similar solutions, making troubleshooting and tuning MySQL a manageable task.

Here are 10 tips for getting great performance out of MySQL.

MySQL performance tip No. 1: Profile your workload

The best way to understand how your server spends its time is to profile the server’s workload. By profiling your workload, you can expose the most expensive queries for further tuning. Here, time is the most important metric because when you issue a query against the server, you care very little about anything except how quickly it completes.

The best way to profile your workload is with a tool such as MySQL Enterprise Monitor’s query analyzer or the pt-query-digest from the Percona Toolkit. These tools capture queries the server executes and return a table of tasks sorted by decreasing order of response time, instantly bubbling up the most expensive and time-consuming tasks to the top so that you can see where to focus your efforts.

Workload-profiling tools group similar queries together, allowing you to see the queries that are slow, as well as the queries that are fast but executed many times.

Lire la suite…

Print Friendly, PDF & Email

How To Install and Secure phpMyAdmin on Ubuntu 16.04

05/11/2017 Comments off

Introduction

While many users need the functionality of a database management system like MySQL, they may not feel comfortable interacting with the system solely from the MySQL prompt.

phpMyAdmin was created so that users can interact with MySQL through a web interface. In this guide, we’ll discuss how to install and secure phpMyAdmin so that you can safely use it to manage your databases from an Ubuntu 16.04 system. 

Prerequisites

Before you get started with this guide, you need to have some basic steps completed.

First, we’ll assume that you are using a non-root user with sudo privileges, as described in steps 1-4 in the initial server setup of Ubuntu 16.04.

We’re also going to assume that you’ve completed a LAMP (Linux, Apache, MySQL, and PHP) installation on your Ubuntu 16.04 server. If this is not completed yet, you can follow this guide on installing a LAMP stack on Ubuntu 16.04.

Finally, there are important security considerations when using software like phpMyAdmin, since it:

  • Communicates directly with your MySQL installation
  • Handles authentication using MySQL credentials
  • Executes and returns results for arbitrary SQL queries

For these reasons, and because it is a widely-deployed PHP application which is frequently targeted for attack, you should never run phpMyAdmin on remote systems over a plain HTTP connection. If you do not have an existing domain configured with an SSL/TLS certificate, you can follow this guide on securing Apache with Let’s Encrypt on Ubuntu 16.04.

Once you are finished with these steps, you’re ready to get started with this guide.

Lire la suite…

Print Friendly, PDF & Email

How To Measure MySQL Query Performance with mysqlslap

05/11/2017 Comments off

Introduction

MySQL comes with a handy little diagnostic tool called mysqlslap that’s been around since version 5.1.4. It’s a benchmarking tool that can help DBAs and developers load test their database servers.

mysqlslap can emulate a large number of client connections hitting the database server at the same time. The load testing parameters are fully configurable and the results from different test runs can be used to fine-tune database design or hardware resources.

In this tutorial we will learn how to use mysqlslap to load test a MySQL database with some basic queries and see how benchmarking can help us fine-tune those queries. After some basic demonstrations, we will run through a fairly realistic test scenario where we create a copy of an existing database for testing, glean queries from a log, and run the test from a script.

The commands, packages, and files shown in this tutorial were tested on CentOS 7. The concepts remain the same for other distributions. Lire la suite…

Print Friendly, PDF & Email

How do I… Stress test MySQL with mysqlslap?

04/11/2017 Comments off

One of the interesting new tools in MySQL 5.1.4 is mysqlslap, a load emulator that lets you see how well a particular query set or table engine performs under high-load conditions.

A query that consumes too many database resources may be the result of designing tables incorrectly, choosing the wrong table type, or creating an inefficient query. When a query eats up a lot of database resources, it can negatively affect other application components. By using mysqlslap to stress test a server in a non-public environment, you will discover these errors sooner, allowing you to you avoid a database meltdown once your application goes live.

This tutorial shows how you can use mysqlslap to run stress tests involving multiple clients, custom queries, different table engines, and much more. Lire la suite…

Print Friendly, PDF & Email

Réplication MySql Croisée

25/03/2017 Comments off

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.

Print Friendly, PDF & Email

Iptables Allow MYSQL server incoming request on port 3306

24/03/2017 Comments off

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…

Print Friendly, PDF & Email

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

26/10/2016 Comments off

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

Print Friendly, PDF & Email

Les différents moteurs de stockage de MySQL: MyISAM

01/07/2016 Comments off

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…

Print Friendly, PDF & Email

Les différents moteurs de stockage de MySQL: InnoDB

01/07/2016 Comments off

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…

Print Friendly, PDF & Email