doc.progysm.com

Problème avec SET NAMES UTF8

si on fait un WHERE CONCAT(field1, ' ', field2) parfois la condition échoue car ' ' n'est pas en UTF-8. Il faut écrire: WHERE CONCAT(field1, CONVERT(' ' USING utf8), field2)

Problème avec les dates 0000-00-00 00:00

anciennement: unedate datetime NOT NULL default '0000-00-00 00:00'

En 5.7 Strict, on ne peut insérer une date 0000-00-00 à moins d'utiliser INSERT IGNORE, UPDATE IGNORE.

1. Désactiver le mode strict
SET sql_mode = '';

2. Pour corriger le champ:
ALTER TABLE t CHANGE unedate unedate datetime NULL default NULL;
-- s'il y a plusieurs champs:
ALTER TABLE t CHANGE unedate unedate datetime NULL default NULL, CHANGE deuxiemedate deuxiemedate datetime NULL default NULL;

3. Mettre à jour les valeurs, 
UPDATE IGNORE t SET unedate = NULL WHERE unedate = '0000-00-00 00:00:00';
-- plusieurs champs
UPDATE IGNORE t SET unedate = NULL WHERE unedate = '0000-00-00 00:00:00';
UPDATE IGNORE t SET deuxiemedate = NULL WHERE deuxiemedate = '0000-00-00 00:00:00';

4. En PHP, utiliser null et prepare/bindValue/execute,
ou si on construit la requête manuellement, ajouter une fonction pour utiliser PDO::quote() si non vide, null, substr($date, 0, 10) === '0000-00-00', sinon utiliser 'NULL'.

Mot de passe caché dans un fichier

exemple pour mysqldump:

1. créer un fichier
/root/.my.password.cnf

2. dans le fichier
[mysqldump]
user="test"
password=test

3. à la ligne de commande
mysqldump --defaults-file="/root/.my.password.cnf" database > database.sql

Note: il est très important d'écrire --defaults-file comme premier argument. La commande ne fonctionne pas après les paramètres -h ou -u. Cette option n'est pas dans le manuel (man) de mysql ou mysqldump.

Pour la commande mysql, la section dans le fichier est [client].

mysqldump

Depuis la version 5.1, il faut ajouter --events --ignore-table=mysql.event pour enlever l'avertissement suivant:

-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

Source: MySQL and warnings - Yet another compatibility break

Problème suivant:

$ mysql -u root -p mysql
mysql> show events;
ERROR 1577 (HY000): Cannot proceed because system tables used by Event Scheduler were found damaged at server start

MySQL Bugs: #70975 - http://bugs.mysql.com/bug.php?id=70975

Solution: arrêter et redémarrer le serveur (fonctionne)

# service mysql stop
# service mysql start

# mysql -u root -p mysql
mysql> show events;
Empty set (0.01 sec)

Problème avec table events

Commande utilisée: mysqldump --events --ignore-table=mysql.event --all-databases -u root

Problème:

mysqldump: Couldn't execute 'show events': Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577)

Solution sur serverfault:

server:~# mysqlcheck -u root -p --check-upgrade --auto-repair mysql
server:~# mysql_upgrade -u root -p
server:~# mysql -u root -p mysql
MariaDB [mysql]> show events;
Empty set (0.01 sec)

Création d'une base de données

mysql -p -e "CREATE DATABASE database CHARACTER SET utf8 COLLATE utf8_general_ci" # note je n'ai pas trouvé comment créé une base de données en UTF-8 à partir de mysqladmin # --default-character-set=utf8 ne fonctionne pas mysqladmin create databasename

Réplication

Instruction

1. Sur le serveur Maître, créer un usager de réplication:

   # mysql -u root
   mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY '<password>';

2. Arrêter les transactions sur le maître et flusher les verrouillages:

   mysql> FLUSH TABLES WITH READ LOCK;
   exit

3. Créer une copie de MYSQL:

   # tar -C /var/lib --exclude mysql/mysql --exclude mysql/*.info -czf /tmp/mysql-snapshot.tar.gz mysql

4. Copier le fichier sur le serveur esclave (slave):

   # scp /tmp/mysql-snapshot.tar.gz slave:/tmp/

5. Sur le serveur Maître, réactiver le serveur avec:

   # mysql> UNLOCK TABLES;
   exit

6. Dans le fichier /etc/mysql/my.cnf, section [mysqld], ajouter les lignes log-bin et server-id=1, ou encore:

   # sed -i "/^\[mysqld\\]$/a\\log-bin\nserver-id=1" /etc/mysql/my.cnf

   . Il faut aussi modifier le bind-address du serveur mysqld pour écouter sur l'adresse externe du serveur. Par exemple: 0.0.0.0 pour écouter sur toutes les interfaces réseaux, 192.168.0.100 pour écouter sur une interface.
7. Redémarrer le serveur mysql maître:

   # /etc/init.d/mysql restart

8. Noter le fichier et la position du log binaire avec la commande

   mysql> SHOW MASTER STATUS;
   exit

9. Sur le serveur Esclave, arrêter le serveur mysql:

   # /etc/init.d/mysql stop

10. Dans le fichier /etc/mysql/my.cnf, section [mysqld], ajouter les lignes server-id=2, ou encore:

    # sed -i "/^\[mysqld\\]$/a\\server-id=2" /etc/mysql/my.cnf

11. Copier les fichiers de la base de données du maître dans les fichiers du serveur mysql

    # tar -C /var/lib -xvf /tmp/mysql-snapshot.tar.gz mysql/*/

12. Démarrer le serveur mysql esclave:

    # /etc/init.d/mysql start

13. Spécifier le serveur maître avec la commande:

    mysql> CHANGE MASTER TO MASTER_HOST='<host>', MASTER_USER='<replication username>', MASTER_PASSWORD='<replication password>', MASTER_LOG_FILE='<recorded logfilname>', MASTER_LOG_POS=<recorded log offset>;

    Dans mon cas, il s'agit de CHANGE MASTER TO MASTER_HOST='ymorin-france', MASTER_USER='repl', MASTER_PASSWORD='repl_password', MASTER_LOG_FILE='mysqld-bin.000001', MASTER_LOG_POS=98;
14. Démarrer le fil de processus (thread) de l'esclave:

    mysql> START SLAVE;
    exit

15. Puisque la base de données "mysql" n'a pas été modifiée, il faut recréer les utilisateurs:

    master> GRANT select,insert,update,delete,create,drop,index,lock tables ON maBD.* TO 'monUsager'@'localhost' IDENTIFIED BY 'monMotdepasse';

Commande

LOAD TABLE FROM MASTER  [ droit SUPER, RELOAD et SELECT sur l'usager "repl" du serveur maître ]
LOAD DATA FROM MASTER   [ droit SUPER, RELOAD et SELECT sur l'usager "repl" du serveur maître ] 
START SLAVE
SHOW SLAVE STATUS

Voir Replication Howto.

ASP

Connexion avec ASP: Dim ConnectionString As String = "Data Source=localhost;Database=DBNAME;User ID=USERNAME;Password=PASSWORD" ou pour ODBC "Driver={MySQL ODBC 3.51 Driver};Server=mysql.mydns.com:3306;Database=myDB;uid=myID;pwd=myPassword;option=3" (source http://forums.asp.net/t/36920.aspx)

  cn_cap = Server.CreateObject("ADODB.connection");
  var connectionString = ""; 
  connectionString += "Provider=SQLOLEDB; Data Source=" + dbserver;
  connectionString += "; Initial Catalog=" + dbname;
  connectionString += "; User ID="+ dbuser;
  connectionString += "; Password=" + dbpassword;
  cn_cap.Open(connectionString);

  cn_cap.close();
  delete cn_cap;