MySQL

Voir cette page sous forme de diaporama.

MySQL est un système de gestion de bases de données relationnelles (SGBDR) libre, gratuit, performant, très populaire, multi-threadé, multi-utilisateurs…

Ressources MySQL

Ma page php

quelques chouettes programmes pour MySQL

mysql_soft xls2mysql, open-office ou excel -> MySQL


Tutoriels Tecfa

Tips & Tricks

Pour remplacer des chaînes de caractères dans des bases de données MySQL

http://sql.sh/fonctions/replace

  REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
        -> 'WwWwWw.mysql.com'

UPDATE table
SET nom_colonne = REPLACE(nom_colonne, 'ancien texte', 'texte de remplacement')

ex:
UPDATE dc_post 
SET post_excerpt = replace(post_excerpt,"cms/dotclear","blog");

un bug ubuntu bien embêtant mais facilement réglé, mysql plante si on augmente pas le nombre de fichiers que mysql peut ouvrir ainsi que la mémoire

sudo su
saveback /lib/systemd/system/mysql.service
vi  /lib/systemd/system/mysql.service
#Add the following lines to the end of it:
LimitNOFILE=infinity
LimitMEMLOCK=infinity
sudo systemctl daemon-reload
/etc/init.d/mysql restart
#check
mysql
SHOW VARIABLES LIKE 'open%';

Change your collation to utf_general_ci. It ignores accent when searching and ordering but still stores them correctly.

source: https://www.bggofurther.com/fr/2016/08/calculate-databasetable-size-in-mysql/

La taille de toutes les bases

SELECT table_schema "Database Name", SUM(data_length+index_length)/1024/1024
"Database Size (MB)"  FROM information_schema.TABLES GROUP BY table_schema;

La taille des tables d’une base spécifique

SELECT table_name "Table Name", table_rows "Rows Count", round(((data_length + index_length)/1024/1024),2) "Table Size (MB)" FROM information_schema.TABLES WHERE table_schema = "db_4";

vous avez une terrible erreur du genre

ERROR 1016 (HY000): Can't open file: 'mempo_users_sessions.MYI' (errno: 145)

et vous n'arrivez même pas à accéder à la table, p.ex.

REPAIR TABLE Syntax

REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE
    tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]

REPAIR TABLE repairs a possibly corrupted table. 
By default, it has the same effect as myisamchk --recover tbl_name.

8-o ATTENTION: à manier avec précaution, lire avant http://dev.mysql.com/doc/refman/5.0/en/repair-table.html

Il est toujours possible de faire cette délicate opération après-coup de la manière suivante:

ALTER TABLE `kulturtrok` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE `batiments` 
ADD `rem01` TEXT NOT NULL ,
ADD `rem02` TEXT NOT NULL ,
ADD `rem03` TEXT NOT NULL ,
ADD `rem04` TEXT NOT NULL ,
ADD `rem05` TEXT NOT NULL ,
ADD `rem06` TEXT NOT NULL ,
ADD `rem07` TEXT NOT NULL ,
ADD `rem08` TEXT NOT NULL ,
ADD `rem09` TEXT NOT NULL ,
ADD `rem10` TEXT NOT NULL ;

netstat -n | grep mys netstat -n | grep 3306

access2MySQL.sh convert Microsoft access db (maybe also other formats) to standard MySQL

concaténation plusieurs bases MySQL: précautions à prendre et solutions, ou pourquoi mysql est parfois benêt!

http://dev.mysql.com/doc/refman/5.0/fr/adding-users.html Ajouter de nouveaux utilisateurs à MySQL

créer un nouvel utilisateur et lui donner tous les droits sur la base db:

GRANT ALL PRIVILEGES ON db.* TO 'monty'@'localhost' WITH GRANT OPTION;

SET PASSWORD FOR monty@host = password( 'new_password' ); 

Exemples

shell> mysql --user=root mysql

Vous pouvez ajouter de nouveaux utilisateurs en utilisant des commandes GRANT :

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     IDENTIFIED BY 'un_mot_de_passe' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
    ->     IDENTIFIED BY 'un_mot_de_passe' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';

Changer son mot de passe MySQL

en deux coups de cuillère à pot, créer une base et un user:

mysql
create database lenom
GRANT ALL PRIVILEGES ON lenom.* TO 'monty'@'localhost' WITH GRANT OPTION;
SET PASSWORD FOR monty@host = password( 'new_password' );

voir http://radeff.red/info/php/mysql_db_create.php

lister les champs dans rem puis

cat rem | while read i
do
echo "ALTER TABLE  '$i'  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;"
done

ALTER DATABASE

Classer

On peut bien sur faire des arbres sommaires avec un classement basé sur le parent_id, mais on a vite des problèmes de profondeur (p. ex. 2 requêtes si on cherche les petits-enfants ou les grand-parents, et ça grandit avec la profondeur)

D'ou l'affreux Classement intervallaire ou toutes les astuces du lft + rght

vidéo http://www.grafikart.fr/tutoriels/mysql/intervallaire-159

tuto http://openclassrooms.com/courses/la-representation-intervallaire

comportement intervallaire dans CakePHP avec "Tree Behaviour"

Connection php+Mysql

connect.inc.php global file to connect to MySQL with php

Automatic MySQL FORM generation

Pour générer automatiquement des fichiers php pour afficher et administrer une base de données MySQL

http://radeff.red/info/mysql/mysql-php-robot.zip

ajout id auto-incrémental

pour avoir un id numérique auto-incrémental

ALTER TABLE latable ADD id MEDIUMINT NOT NULL AUTO_INCREMENT KEY 

Last inserted id

keywords: getlastid

suite à un INSERT, on veut récupérer le nouvel id pour continuer p.ex. ds un formulaire:

<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
die('Impossible de se connecter : ' . mysql_error());
}
mysql_select_db('mydb');

mysql_query("INSERT INTO mytable (product) values ('kossu')");
printf("Le dernier ID inséré dans est le id %d\n", mysql_insert_id());
?>

et dans cakephp:

  echo $this->Form->getLastInsertId(); //changer Form pour le nom du modèle

FICHIER CSV MODELE

fichier MySQL2csvUsingPhp

Films

exemples du bouquin O'Reilly base de données film http://radeff.red/info/mysql/film/

Links MySQL fred

http://radeff.red/links/disciplin.php?disc=mysql

mysql basics fred

http://radeff.red/info/mysql/MySQLmemo.php

optimiser table

http://www.totallyphp.co.uk/scripts/optimize-all-tables-in-a-mysql-database.htm

<?php

dbConnect()

$alltables = mysql_query("SHOW TABLES");

while ($table = mysql_fetch_assoc($alltables))
{

   foreach ($table as $db => $tablename)
   {
       mysql_query("OPTIMIZE TABLE '".$tablename."'")
           or die(mysql_error());
   }
   
}

?>

my.cnf

pratique pour ouvrir mysql sans login+password,

mettre dans son $HOME un fichier .my.cnf contenant:

[client]
user = userName
password = userPassword
[mysql]
database = userDB

puis chmod 700 .my.cnf

ensuite quand on fait mysql c'est comme

mysql -u userName -puserPassword userDB  

(attention à bien coller le mot de passe au -p)

password

mysql -u root -p
use mysql;
update user set password=PASSWORD("NEWPASSWORD") where User='lambda';

vous avez perdu le password root et vous ne voulez pas réinstaller mysql en faisant un backup des tables? rien de plus simple

source: http://www.cyberciti.biz/tips/recover-mysql-root-password.html

Step # 1 : Stop mysql service

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

# mysqld_safe --skip-grant-tables &
Output:

[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client:

# mysql -u root
Output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Step # 4: Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

pour faire une sous-requete et exclure des enregistrements

SELECT * FROM cours WHERE id_cours NOT IN (SELECT id_cours FROM cours_dates)

MySQL and palmPilot

Opérations sur les dates

  • prend les dates d'aujourd'hui - 30 jours
SELECT *
FROM testit
WHERE TO_DAYS( NOW( ) ) - TO_DAYS( timestamp ) <=30
LIMIT 0 , 100;
  • ajouter à une date (ici 611 jours)
UPDATE `DJTDemiJour` SET DJTDate =ADDDATE(DJTDate, INTERVAL 611 DAY)

 UPDATE `jos_reservations` SET date = ADDDATE( date, INTERVAL 70 DAY )  

et soustraire:

mysql> SELECT SUBDATE('2008-01-02', INTERVAL 31 DAY);

Pour modifier des datas

update sr_data set email='@bcu.unil.ch' where email='FR'
# update signets 
set category='Geneve_et le_monde' 
where category='Genève_et le_monde' 

ou syntaxe replace: 

$replace = mysql_query("
replace into 
recet (id, prov, titre, temps, ingr, pers, typ, prep, date) 
VALUES 
('$id', '$prov', '$titre', '$temps', '$ingr', '$pers', '$typ', '$prep', '$date')");

Pour changer un string précis dans 1/n champs

UPDATE xoops_bb_posts_text
SET post_text=(
REPLACE 
(post_text,'morphix.sourceforge.net','www.morphix.org')
); 

marche pas (infra) 

UPDATE recet SET ingr = 
REPLACE ( ingr, 'calmar', 'calamar' ) 
WHERE ingr LIKE '%calmar%' 

CA CA MARCHE: 

update `tableName` 
set `Field`=replace('string','old','new')

Sélections imbriquées / SELECT ... IN (SELECT...) / SELECT ... NOT IN (SELECT...)

Example 1

SELECT name FROM cia WHERE population > (SELECT population FROM cia WHERE name='United States')

Example 2

SELECT name FROM cia WHERE region = (SELECT region FROM cia WHERE name = 'Brazil')

“SELECT t1.cod_uni, t1.TEL_IMP_UNI, t1.comm, t1.dateu FROM testunite t1 WHERE t1.cod_uni NOT IN (SELECT t2.cod_uni FROM testunite2 t2) ORDER BY t1cod_uni”;

Syntaxe de SELECT

[+/-]

13.1.7.1. Syntaxe de JOIN 13.1.7.2. Syntaxe de UNION

SELECT [STRAIGHT_JOIN]

     [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
     [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
     [DISTINCT | DISTINCTROW | ALL]
  select_expression,...
  [INTO {OUTFILE | DUMPFILE} 'nom_fichier' export_options]
  [FROM table_references
    [WHERE where_definition]
    [GROUP BY {unsigned_integer | nom_de_colonne | formula} [ASC | DESC], ...
    [HAVING where_definition]
    [ORDER BY {unsigned_integer | nom_de_colonne | formula} [ASC | DESC] ,...]
    [LIMIT [offset,] lignes]
    [PROCEDURE procedure_name(argument_list)]
    [FOR UPDATE | LOCK IN SHARE MODE]]

mysql join left

Expressions régulières

$sql5b="SELECT * FROM voc WHERE libelle REGEXP '^$ereg$q5b$ereg$' ORDER BY libelle";

importer des données csv

load data local infile '/home/radeff/public_html/emails/emails.txt' into table fred_email; 
load data local infile '/home/radeff/public_html/secret/dereda/file.txt' into table frdereda fields terminated by ";";   
# load data local infile '/export/data/htdocs/radeff/signets.txt' into table signets fields terminated by ";";   

Sauvegardes mysql (Export/Import)

http://video.google.com/videoplay?docid=2742157386318372444 Tutoriel vidéo pour faire un backup avec phpMyAdmin

Export MySQL / mysqldump

mysqldump

Récupérer une base parmi toutes les bases dans un backup

Récupérer une base parmi toutes les bases dans un backup

Script php cgi pour liste toutes les bases, toutes les tables, tous les champs d'un hôte

mysqlphp_list_all_databases_all_tables_all_fields

Script php cgi pour trouver un string dans toutes les bases, toutes les tables, tous les champs d'un hôte

mysqlphp_find_string_all_databases_all_tables_all_fields

Sécurité MySQL

Sécurité MySQL

mysqlbinlog

pour extraire les log binaires d'une base:

mysqlbinlog –database=nameOfYourDB mysql_bin.* | grep -v '^# at' > result

mysql2xml

mysql2xml script php pour convertir en xml une base mysql kw: mysqltoxml mysql xml php mysql php xml mysql

mysqldump2xml utiliser mysqldump pour générer du xml à partir d'une base MySQL; défaut: c'est pas du xml standard

phpCgiMysql2xml utiliser un cgi php appellé depuis php+apache, marche nickel

whereis mysql mysql: /usr/bin/mysql /etc/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz

Where are the Mysql data files located? They're all in /var/lib/mysql

The log files: /var/log/mysql

  • info/mysql.txt
  • Dernière modification: 2018/07/18 09:46
  • par radeff