Titre

Formation MySQL 101

Dates

Synopsis

familiariser les administrateur à la gestion d'un serveur MySQL: installation, configuration, sauvegardes, optimisations de base

Niveau

intermédiaire

Nombre de participants

Formateur

TheAnarcat

Public

externe

Résumé

MySQL est un des serveurs de base de données les plus populaires au monde, certainement le serveur libre le plus populaire.

C'est un engin devenu assez complexe, mais aussi très performant et assez complet pour couvrir une grande variété de cas d'utilisation.

Nous verrons ici des conseils de base sur l'installation d'un serveur MySQL et de son compagnon desktop (MySQL Workbench), des techniques de sauvegarde et de restauration de base, des techniques de maintenance élémentaires ainsi que plusieurs paramètres de configuration importants.

Plan de cours

Installation

Dans Windows:

• serveur "GA": Installer MSI

• outil desktop: MySQL workbench

Conseils:

• partition séparée
• RAID-10+
• assez de ram pour stocker toute la DB

Création d'une database

Gestion des utilisateurs

GRANT et REVOKE, CREATE USER, DROP USER.

• usernames basés sur user@host

• http://dev.mysql.com/doc/refman/5.6/en/grant.html#grant-privileges

Charger un schema

Sauvegardes

Techniques:

• "mysqldump"
• "hot copy"

Configuration

De base:

• bind-address: *

Conseils:

• utilisez InnoDB
  • mieux pour la plupart des workloads, sauf pour certains cas particuliers
  • par défaut depuis 5.6
  • ACID (Atomicité, Consistence, Isolation, et Durabilité), commit, rollback, row-level locking, foreign keys, etc

  • détail des engins de stockage, default_storage_engine: https://dev.mysql.com/doc/refman/5.6/en/storage-engines.html

• un fichier par table, innodb_file_per_table: https://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html

  • meilleure gestion de l'espace
  • restore spécifique
  • plus de fsync()
  • plus de file handles
  • MySQL 5.1 et plus

• innodb_buffer_pool_size: https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size

  • 80% de la RAM, minimum
  • idéalement couvre tout le dataset

  • note: pour dépasser 4GB sous Windows, il faut recompiler et changer le réglage innodb_buffer_pool_awe_mem_mb

• innodb_log_file_size: https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_log_file_size

  • plus grand signifie moins de writes sur le disque...
  • ... mais plus de risque de perte, et restores plus lents

• query cache: http://dev.mysql.com/doc/refman/5.6/en/query-cache.html

  • près de 3x plus rapide

  • query_cache_size: http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_query_cache_size

  • query_cache_type=1

  • SHOW VARIABLES LIKE 'have_query_cache'; - vérification

  • SHOW STATUS LIKE 'Qcache%'; - optimisation

  • RESET QUERY CACHE - flush

  • gros résultats? augmenter query_cache_limit

  • augmenter query_cache_size jusqu'à satisfaction (ratio = Qcache_hits / Queries)

• slow query log: http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html

  • long_query_time

  • slow_query_log=1

  • slow_query_log_file=file_name

• table_open_cache & thread_cache_size

• skip-name-resolve ou examiner: http://dev.mysql.com/doc/refman/5.6/en/host-cache.html

Autre maintenance

• OPTIMIZE TABLES; régulier (1/semaine)
• CHECK TABLE; REPAIR TABLE;

• http://mysqltuner.com/ = http://mysqltuner.pl

Problèmes courants

• Out of resources when opening file: open-files-limit

• Too many connexions: max_connections - mais attention! c'est votre pare-feu!

Replication

Documentation principale:

• http://dev.mysql.com/doc/refman/5.6/en/replication.html

• http://wiki.koumbit.net/MysqlReplication

Notes:

• basic setup:

  1. GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'PASS';

  2. server-id=N where N increments at each new slave

  3. log-bin=mysql-bin on the master

  4. skip-slave-start on master (generally)

• general principle:

  1. FLUSH TABLES WITH READ LOCK;

  2. snapshot filesystem

  3. SHOW MASTER STATUS; UNLOCK TABLES;

  4. copy data to slave
  5. start slave at the master status showed above:

     • CHANGE MASTER TO
                MASTER_HOST='master.example.com',
                MASTER_USER='repl',
                MASTER_PASSWORD='PASS',
                MASTER_LOG_FILE='recorded_log_file_name',
                MASTER_LOG_POS=recorded_log_position;

• maintenance tips:

  • STOP SLAVE;, START SLAVE;

  • SHOW SLAVE STATUS\G

  • skip silly errors once: SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

  • ignore those errors: slave-skip-errors=1062,1053, see http://dev.mysql.com/doc/refman/5.6/en/replication-options.html http://dev.mysql.com/doc/refman/5.6/en/error-messages-server.html

  • monitor SLAVE-SKIP-COUNTER variable - http://dev.mysql.com/doc/refman/5.6/en/set-global-sql-slave-skip-counter.html

• binary log management:

  • SHOW BINARY LOGS;

  • discard unused space, on the slave: FLUSH LOGS;; on the master: RESET MASTER;

  • note that this may desynchronise replication!

  • make sure logs are rotated: max_binlog_size & expire_logs_days http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_max_binlog_size http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_expire_logs_days

• common diagnostics:

  • Last_SQL_Error: Error 'Incorrect key file for table' : USE <DBname>; REPAIR TABLE <tablename>; START SLAVE;

  • may be due to missing space in tmp, change location: tmpdir

Monitoring

• disk space
• memory usage
• a few munin plugins

Mises à jour, sécurité et Oracle

• assurez-vous de faire des mises à jour régulièrement
• Oracle ont adopté une politique de mise à jour "obscure" où les problèmes de sécurité ne sont pas dévoilés clairement, ou du moins les patches ne sont pas isolées
• alternatives plus libres:

  • https://mariadb.org/ - "fork" de l'auteur original de MySQL, Michael "Monty" Widenius, compatible avec MySQL

  • http://www.percona.com/software/percona-server - version haute-performance de MySQL

Autres sujets

• Views: http://dev.mysql.com/doc/refman/5.1/en/views.html

• Triggers: http://dev.mysql.com/doc/refman/5.1/en/triggers.html

• Stored procedures: http://dev.mysql.com/doc/refman/5.1/en/stored-routines.html

• Optimisation: http://dev.mysql.com/doc/refman/5.6/en/optimization.html

• Le dialecte MySQL: http://dev.mysql.com/doc/refman/5.6/en/sql-syntax.html http://dev.mysql.com/doc/refman/5.6/en/language-structure.html http://dev.mysql.com/doc/refman/5.6/en/functions.html

• Partitionnement: http://dev.mysql.com/doc/refman/5.6/en/partitioning.html

CategoryFormation CategoryFormationPublique