====== MySQL ======

  * [[/informatique/sgbd/mariadb]]
  * [[/informatique/sql]]

===== Books =====

[[http://www.amazon.com/gp/product/0596101716|High Performance MySQL]] Optimization, Backups, Replication, and More (Paperback)

===== Tools =====

==== Myterm ====

[[https://launchpad.net/myterm|Myterm]]: Extensible mysql command line client with pipe chaining

"[[http://www.jetprofiler.com/blog/10/tail--f-table-with-myterm/|Tail -f table with myterm]]" Ever found yourself running the same query over and over again to see if a table has got new content, just waiting for that magic row to appear? The tail command will watch for new lines in a table, just like you might tail a log file in Linux.

==== Mysql Workbench ====

https://dev.mysql.com/downloads/workbench/

Plugins & Scripts :
  * A list: http://forums.mysql.com/read.php?155,225437,225437
  * [[https://github.com/johmue/mysql-workbench-schema-exporter|mysql-workbench-schema-exporter]]
  * [[http://trac.symfony-project.org/wiki/SymfonyYamlMyqlWorkbenchPlugin|SymfonyYamlMyqlWorkbenchPlugin]]
===== Tips =====

==== How to select random rows in MySQL ====

The easiest way to generate random rows in MySQL is to use the ORDER BY RAND() clause. This can work fine for small tables. However, for big tables, it will have a serious performance problem, as in order to generate the list of random rows, MySQL needs to assign random number to each row and then sort them.
  * [[http://www.rndblog.com/how-to-select-random-rows-in-mysql/|How to select random rows in MySQL]]

==== Create DB & User ====

  mysql> create database db_name ;
  mysql> CREATE USER 'user'@'%' IDENTIFIED BY 'secret';
  mysql> grant all privileges on db_name.* to user@'%' ;

A user for backup only :

<code sql>
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'password';
REVOKE ALL  PRIVILEGES  ON  *.* FROM  'backup'@'localhost';
REVOKE GRANT  OPTION  ON  *.*  FROM  'backup'@'localhost';
GRANT SELECT, LOCK TABLES ON *.*
    TO 'backup'@'localhost' 
    WITH
        MAX_QUERIES_PER_HOUR 0
        MAX_CONNECTIONS_PER_HOUR 0
        MAX_UPDATES_PER_HOUR 0
        MAX_USER_CONNECTIONS 0 ;
FLUSH PRIVILEGES;
</code>

==== Reset the Root Password ====

  $ service mysql stop
  $ mysqld_safe --skip-grant-tables
dans un autre terminal:
  $ mysql -u root
   UPDATE mysql.user SET Password=PASSWORD('TheNewPassword') WHERE User='root';
   exit
tuer le "mysqld_safe" avec un Ctrl^C
  $ service mysql start

===== Procedures and Functions =====

http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html

==== Delimiter ====

Dans le corps d'un Trigger ou d'une Stored Procedure il peut y avoir des ';', alors il faut un nouveau séparateur pour délimiter l'ensemble. C'est DELIMITER :
  DELIMITER //

  CREATE TRIGGER t_bu BEFORE UPDATE ON t
     FOR EACH ROW
     BEGIN
       DECLARE CONTINUE HANDLER FOR 1264 SET new.column1 = -1;
       SET new.column1 = new.column1 * 2;
     END;//

==== Triggers ====

[[http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html|http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html]]

Quelques exemples :

<code sql>
  CREATE TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_stmt
</code>

wa_Guide, un nouveau tuple doit être insérer dans wa_GuideVote (table gérant les votes des visiteurs au niveau de chaque guide) avec comme idGuide l’id du guide qui a été ajouté. Avec un trigger cela donne ceci :

<code sql>
CREATE TRIGGER voteGuideInsert AFTER INSERT ON wa_Guide
FOR EACH ROW
INSERT INTO wa_GuideVote (idGuide) VALUES (NEW.idGuide);
</code>

Vous allez me dire que si on supprime un guide il va rester un tuple inutile dans wa_GuideVote. Et bien non ! :

<code sql>
CREATE TRIGGER voteGuideDelete AFTER DELETE ON wa_Guide
FOR EACH ROW
DELETE FROM wa_GuideVote WHERE idGuide = OLD.idGuide;
</code>

Un astuce pour gérer un cache de données: quand une table est mise à jour elle met à jour un timestamp dans une autre table. ceci permet de tenir des valeurs de last modification par groupe de données.

<code sql>
drop trigger if exists TABLE_A1_TRIGGER ;

create trigger TABLE_A1_TRIGGER AFTER INSERT ON TABLE_A1
for each row
  REPLACE INTO LAST_MOD (lm_module) values ('MyModule')
</code>

Et encore d'autres :

<code sql>
delimiter //
CREATE TRIGGER survey_check_empty BEFORE INSERT ON surv_surveys
FOR EACH ROW
BEGIN
IF LENGTH(NEW.survey_name) <1 THEN
RETURN FALSE;
END IF;
END; //
delimiter ;
</code>

<code sql>
delimiter //
DROP TRIGGER demande_before_insert//
CREATE TRIGGER demande_before_insert BEFORE INSERT ON demande
FOR EACH ROW
BEGIN
  DECLARE i_decalage NUMERIC;
  DECLARE c_file_demandes CURSOR FOR
  SELECT decalage_sec FROM
  file_demandes,
  themes
  WHERE file_demandes.id_theme = themes.id
  AND file_demandes.id = NEW.id_file_demandes;

  # valorise la date cible si elle est vide
  IF(NEW.date_objectif='0000-00-00 00:00:00' OR NEW.date_objectif IS NULL)THEN
    OPEN c_file_demandes;
    FETCH c_file_demandes INTO i_decalage;
    CLOSE c_file_demandes;
    SET NEW.date_objectif = DATE_ADD(NEW.timestamp, INTERVAL i_decalage SECOND);
  END IF;
END;//
delimiter ;
</code>

==== Regular expression ====

<code sql>
update a_table
set content = REGEXP_REPLACE(content, '##', '$$')
where content like '%##%'
</code>

Attention, il faut 2 anti-slash pour le ''?''. Exemple pour supprimer le spam WordPress ''weatherplllatform'':
<code sql>
update wpci_posts
set post_content = REGEXP_REPLACE(
	post_content ,
	"<script src='https://new\.weatherplllatform\.com/pick\.js\\?v=11\.87\.33' type='text/javascript'></script>", ''
	)
where post_content like "%weatherplllatform%"

</code>

===== XA Transaction =====

  * [[http://dev.mysql.com/doc/refman/5.1/en/xa.html|MySQL 5.1 Reference Manual - 12.3.7. XA Transactions]]
  * [[http://dev.mysql.com/doc/refman/5.1/en/xa-restrictions.html|MySQL 5.1 Reference Manual - E.5. Restrictions on XA Transactions]]
  * [[http://dev.mysql.com/doc/refman/5.5/en/xa.html|MySQL 5.5 Reference Manual - 12.3.7. XA Transactions]]

  * [[http://www.developpez.net/forums/d563872/dotnet/general-dotnet/sgbd/net2-mysql-transaction-distribuee/|[.Net2][Mysql] Transaction distribuée]] par Cyrille37
  * [[http://blog.on-x.com/2010/11/xatransactions-distribuees-avec-atomikos/|(Xa)Transactions distribuées avec Atomikos]] et Oracle/Mysql (en français, avec code exemple).
  * [[/glossaire/xa|/Glossaire/AX]]


===== Replication =====

[[/informatique/sgbd/mysql/replication|mysql/replication]]

Articles:
  * [[https://www.it-connect.fr/replication-en-temps-reel-masterslave-mysql%EF%BB%BF/#VII_Simulation_de_panne_recuperation_et_remise_en_ordre_du_master|Réplication en temps réel Master/Slave MySQL]] (2013, Mysql 5.5)
    * Et avec une méthode toute simple pour "Simulation de panne, récupération et remise en ordre du master"
  * [[https://opensolitude.com/2016/09/12/painless-high-availability-failover-mariadb.html|Painless HA failover with MariaDB]] (2016
    * avec Master<->Master
    * ''auto_increment_increment'' and ''auto_increment_offset'' variables such that one only uses odd numbers for auto-incrementing integer primary keys and the other use even bumbers
    * [[https://fr.wikibooks.org/wiki/MySQL/R%C3%A9plication|MySQL Réplication]]
  * [[https://www.christophe-casalegno.com/reparation-dune-replication-mysql-mariadb/|Réparation d’une réplication MySQL ou MariaDB]]
  * [[http://missingmanuals.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html?page=1|Advanced MySQL Replication Techniques]] (2006-04-20)
  * [[http://www.ovaistariq.net/528/statement-based-vs-row-based-replication/|Statement-based vs Row-based Replication]] (2011-01)

==== Reprise après échec/panne ====

Sans arrêter le Master avec l'option "--master-data" de mysqldump.
  * http://dev.mysql.com/doc/refman/5.1/en/replication-howto-mysqldump.html

==== Superviser la réplication ====


  * replication check ?
  * slave-skip-errors ?
  * [[https://mariadb.com/kb/en/show-replica-status/|show slave status]] (ou SHOW REPLICA]
  * SHOW PROCESSLIST; SHOW SLAVE HOSTS;

Dans ''/var/log/syslog'' des lignes comme ''...mysqld[123]: 101015 21:11:19 [ERROR] Slave: Error...''


===== Performance =====

Optimisation.

==== Articles ====


  * [[http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics|Innodb Performance Optimization Basics]] sur mysqlperformanceblog.com
  * [[https://fr.wikibooks.org/wiki/MySQL/Optimisation|MySQL/Optimisation]] sur wikibooks
  * [[http://hackmysql.com/selectandsort|MySQL Select and Sort Status Variables]]
  * [[http://phpinfo.net/articles/article_optimisation-mysql.html|Optimisation MySQL]] Par Laurent DECORPS sur phpinfo.net
  * Official doc:
    * [[http://dev.mysql.com/doc/refman/5.0/fr/mysql-optimization.html|MySql optimization]]
    * [[http://dev.mysql.com/doc/refman/5.0/fr/tips.html|MySql tips]]

==== Tips ====

  * Always check whether all your **queries really use the indexes** you have created in the tables. In MySQL, you can do this with the EXPLAIN statement. Check status variable "**Select_scan**", "**Select_full_join**".
  * With MyISAM tables that have no deleted rows, you can insert rows at the end at the same time that another query is reading from the table. If this is important for you, you should consider using the table in ways that avoid deleting rows. Another possibility is to run **OPTIMIZE TABLE after you have deleted a lot of rows**.