https://ravieres.radeff.red/dokuwiki/ 2026-07-16T22:04:06+00:00 https://ravieres.radeff.red/dokuwiki/ https://ravieres.radeff.red/dokuwiki/_media/wiki:dokuwiki-128.png text/html 2023-03-20T13:16:15+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:access2mysql?rev=1679318175 access2MySQL access2mysql.sh convert microsoft_access db (maybe also other formats) to standard MySQL mysql text/html 2023-03-20T13:17:28+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:add_10_fields?rev=1679318248 Ajouter 10 champs "remarques" 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 ; text/html 2023-03-20T13:15:48+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:add_increment_id?rev=1679318148 Ajouter une clé d'index auto-incrémentale 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; mysql text/html 2023-03-20T13:14:31+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:calc_size?rev=1679318071 Calculer la taille d’une base/table en MySQL 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 text/html 2024-02-29T07:39:00+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:changer_son_mot_de_passe_mysql?rev=1709192340 Changer son mot de passe MySQL On peut changer facilement son mot de passe depuis phpmyadmin: en ligne de commande: SET PASSWORD FOR monty@host = password( 'new_password' ); change password for user lambda mysql -u root -p use mysql; update user set password=PASSWORD("NEWPASSWORD") where User='lambda'; text/html 2023-03-20T13:19:49+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:charset?rev=1679318389 Changer le charset 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 mysql text/html 2023-03-20T13:20:26+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:classer?rev=1679318426 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) text/html 2023-03-20T13:21:56+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:command_line_create_user_db?rev=1679318516 Create DB + User command line - Ligne de commande 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' ); text/html 2023-03-20T13:21:13+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:concatenation_plusieurs_bases_mysql?rev=1679318473 concaténation plusieurs bases MySQL précautions à prendre et solutions, ou pourquoi mysql est parfois benêt! lorsqu'on concatène plusieurs bases MySQL ayant une structure similaire mais dans un ordre différent, ne surtout pas croire que la concaténation est correcte! elle ne fonctionne que si la structure ET l'ordre sont identiques, sinon il prend dans l'ordre seulement text/html 2023-03-20T13:22:56+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:connect.inc.php?rev=1679318576 connect.inc.php global file to connect to MySQL with php <?php $login= ""; // user name for you database $pass= ""; // pass word to the database if you dont have a password //set up leave it as "" $database_name=""; //name of the database /* Don't change anything below this line */ function connect_… text/html 2023-03-20T13:40:52+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:csv_import?rev=1679319652 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 ";"; text/html 2023-03-20T13:31:52+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:dates?rev=1679319112 Dates (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 ) text/html 2026-03-05T08:26:50+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:doublonsmysql?rev=1772699210 doublonsMySQL <? include('php/connect.php'); $result=mysql_query("SELECT *, COUNT(term) as term_count FROM glossary_terms_fr GROUP BY term"); while ($ligne = mysql_fetch_object($result)) { if($ligne->term_count >= 2) { echo "$ligne->term$ligne->term_count<br>"; } } ?> text/html 2023-04-04T06:17:45+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:drop_db?rev=1680589065 DROP (Détruire un utilisateur et une base de données) DROP DATABASE database_name; DROP USER 'database_name'@'localhost'; mysql text/html 2023-03-20T13:11:13+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:drop_table?rev=1679317873 DROP TABLE (Si on veut juste détruire les tables) echo "SET FOREIGN_KEY_CHECKS = 0;" > ./temp.sql mysqldump --add-drop-table --no-data -u root -p db_name | grep 'DROP TABLE' >> ./temp.sql mysql text/html 2023-03-20T13:23:40+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:fichier_mysql2csvusingphp?rev=1679318620 csv Mysql2csvUsingPhp <?php header('Content-type: text/x-csv'); header('Content-Disposition: attachment; filename="tpg.csv"'); include_once("/register_global.inc.php"); #produit un fichier csv include("connect.php"); $laTable="nomDeVotreTableMySQL"; $db=connect_db(); $db_name=db_name(); mysql_select_db($db_name,$db); $result = mysql_query("select * from " .$laTable); $dbResult = mysql_list_fields($database_name, $laTable, $db); #nom des champs //loop over each field $x="\""; f… text/html 2023-03-20T13:12:06+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:increase_file_open?rev=1679317926 increase file open 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%'; text/html 2023-09-03T04:36:07+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:join?rev=1693715767 JOIN (Jointure) LEFT JOIN ON <https://fr.wikipedia.org/wiki/Jointure_(informatique)> mysql text/html 2023-03-20T13:24:48+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:last_inserted_id?rev=1679318688 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()); ?> text/html 2023-03-20T13:13:27+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:mariadb?rev=1679318007 mariadb migration pas si simple... notamment le démon qui plante, Job for mariadb.service failed because a timeout was exceeded. si c'est le cas je m'en suis sorti avec echo "/usr/sbin/mysqld { }" > /etc/apparmor.d/usr.sbin.mysqld apparmor_parser -v -R /etc/apparmor.d/usr.sbin.mysqld systemctl restart mariadb text/html 2023-03-20T13:27:15+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:my.cnf?rev=1679318835 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 text/html 2023-03-20T13:37:19+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:mysql_join_left?rev=1679319439 MySQL SELECT LEFT JOIN etc. Doc * <http://dev.mysql.com/doc/refman/5.0/fr/left-join-optimization.html>|7.2.9. Comment MySQL optimise les clauses LEFT JOIN et RIGHT JOIN * <http://www.trucsweb.com/asp/trucs.asp?no=104&type=7>|Jointures et relations INNER, LEFT, RIGHT, UNION... Exemple Créer deux tables: CREATE TABLE IF NOT EXISTS `a` ( `id` varchar(3) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Contenu de la table `a` -- INSERT INTO `a` (`id`) VALUE… text/html 2023-03-20T13:52:43+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:mysql2xml?rev=1679320363 mysql2xml <?php /* SCRIPT PERMETTANT DE CONVERTIR UNE TABLE MYSQL EN FICHIER XML SANS PRECISER LES CHAMPS */ // INFOS DE CONNEXION A LA BDD $Sbddhost = 'localhost'; $Sbdduser = 'xxx'; $Sbddpass = 'xxx'; $Sbddbase = 'xxx'; $Sbdderreur = 'La base est inaccessible.'; #$Sbddtable = 'chronologie'; // TABLE A CONVERTIR $Sbddtable = 'chronologie'; // TABLE A CONVERTIR $Sdatalimit = 0; // LIMIT DE DONNEES A CONVERTIR ( 0 = toutes ) // DEFINIR L'ORDRE DE LECTURE (NECCESSITE DE CONNAITRE LE CHAMPS … text/html 2023-03-20T13:46:52+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:mysqlbackupcertainesbasesradeff?rev=1679320012 Récupérer une base parmi toutes les bases dans un backup v1 shell script su@silene1:/w3/backups/ more backup.sh foreach i (users pre_booking textpv LocalNumbers MainLicences companies) /usr/local/mysql/bin/mysqldump -h servername -u userMySQL -pPasswdMySQL dbName $i | gzip &gt; $i_`date +\%d_\%m_\%Y_\%H:\%M`.sql.gz end text/html 2023-03-20T13:48:33+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:mysqlbinlog?rev=1679320113 mysqlbinlog pour extraire les log binaires d'une base: mysqlbinlog --database=nameOfYourDB mysql_bin.* | grep -v '^# at' > result Très utile p. ex. en cas de crash, si on veut récupérer juste une partie des backups... si on a accès aux binaires! text/html 2023-03-21T22:20:43+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:mysqldump?rev=1679437243 mysqldump (backups & Export MySQL) mysqldump basics se mettre dans le path du bin mysql puis ./mysqldump -u[login] -p[passwordCOLLEau-P] [db_name] > /tmp/[some-file-for-saving-the-datas] [enter password at the prompt] à mettre dans un crontab si on veut bien faire les choses pour récupérer des données UTF8 en latin1 (p.ex. si on fait du 4.1 text/html 2026-03-05T08:52:31+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:mysqlfetchobject?rev=1772700751 <? # ########## mysql_fetch_object ############################ # permet d'extraire rapidement un array provenant de mysql # # il suffit de creer l'array et d'appeller ses champs: # # $array -> nomChamps # ############################################################ $konnect=mysql_connect("localhost","nobody",""); $konnect=mysql_select_db("radeff"); $result = mysql_query("select # from signets order by name"); if($result) { while($links=mysql_fetch_… text/html 2023-03-20T13:54:01+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:mysqlphp_find_string_all_databases_all_tables_all_fields?rev=1679320441 Script php cgi pour trouver un string dans toutes les bases, toutes les tables, tous les champs d'un hôte #! /usr/bin/php <?php error_reporting(63); /*************************************** ** Title........: Search and replace utility in all MySQL db ** Filename.....: search.and.replace.php ** Author.......: Fred Radeff ** Version......: 0.0 ** Notes........: ** Last changed.: 09/24/2008 ** Last change..: ** Usage: mysqlcherchepartout.php [enter] [yourString] [enter] **************************… text/html 2023-03-20T13:41:51+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:mysqlphp_list_all_databases_all_tables_all_fields?rev=1679319711 Script php cgi pour liste toutes les bases, toutes les tables, tous les champs d'un hôte #! /usr/bin/php <?php error_reporting(63); /*************************************** ** Title........: list all databases, all tables, all fields from a mysql server ** Filename.....: mysqlphp_list_all_databases_all_tables_all_fields.php ** Author.......: Fred Radeff ** Version......: 0.0 ** Notes........: ** Last changed.: 09/24/2008 ** Last change..: --- //[[fradeff@akademia.ch|Fred Radeff]] 2008/09/24 15… text/html 2023-03-20T13:52:29+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:phpcgimysql2xml?rev=1679320349 phpCgiMysql2xml utiliser un cgi php appellé depuis php+apache, marche nickel - sauf vitesse! chercher autre solution... test.php: le fichier à appeller par http / apache <?php $dest = './backup'; // Path to directory class MySQLDump { /** * The backup command to execute * @private * @var string */ var $cmd; /** * MySQLDump constructor * @param string dest (Full destination directory path for backup file) * @access public */ … text/html 2023-03-20T13:38:09+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:regexp?rev=1679319489 Expressions régulières $sql5b="SELECT * FROM voc WHERE libelle REGEXP '^$ereg$q5b$ereg$' ORDER BY libelle"; mysql text/html 2026-03-21T06:44:52+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:replace?rev=1774075492 REPLACE / UPDATE: Rechercher / Remplacer un string de caractères descriptif du problème dans le champ titre de la table recettes, j'aimerais remplacer toutes les occurences de “calmar” par “calamar”, exemple: -> solution UPDATE recettes SET titre = REPLACE(titre, 'calmar', 'calamar') WHERE titre LIKE '%calmar%'; text/html 2023-03-20T13:15:06+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:restore_corrupted_db?rev=1679318106 Restaurer une base corrompue 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. text/html 2023-03-20T13:40:14+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:select_in_not?rev=1679319614 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') text/html 2023-03-20T13:31:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:select_without?rev=1679319078 SELECT... NOT IN (Faire une sous-requete et exclure des enregistrements) SELECT * FROM cours WHERE id_cours NOT IN (SELECT id_cours FROM cours_dates) mysql text/html 2023-03-20T13:39:05+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:select?rev=1679319545 Syntaxe de SELECT 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_i… text/html 2023-03-20T13:19:11+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:sockets?rev=1679318351 Trouver les sockets netstat -n | grep mys netstat -n | grep 3306 mysql text/html 2024-06-07T05:10:04+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:soft?rev=1717737004 MySQL Softs (logiciels mysql) Concevoir la DB MySQL Workbench <http://wb.mysql.com/> top: essayé et approuvé le --- Fred Radeff 2010/03/17 17:55 permet de faire facilement du reverse engineering et donc de dessiner un joli modèle à partir d'une base pré-existante remplace DBDesigner text/html 2025-01-27T07:16:36+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:start?rev=1737962196 MySQL MySQL est un système de gestion de bases de données relationnelles (SGBDR) libre, gratuit, performant, très populaire, multi-threadé, multi-utilisateurs… Cruddiy is a free no-code PHP Bootstrap CRUD generator with foreign key support. PDO homepage utiliser couche d'abstraction PDO Tips & Tricks access2MySQL Ajouter 10 champs "remarques" Ajouter une clé d'index auto-incrémentale Calculer la taille d’une base/table en MySQL Changer le charset Changer son mot de passe MySQL Classer… text/html 2023-03-20T13:26:43+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:table_optimisation?rev=1679318803 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()); } } ?> text/html 2023-03-20T13:55:34+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:ubuntu?rev=1679320534 ubuntu / linux mysql 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 mysql text/html 2026-01-21T08:35:41+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:users?rev=1768984541 Droits / Privileges / Users <http://dev.mysql.com/doc/refman/5.0/fr/adding-users.html> Ajouter de nouveaux utilisateurs à MySQL CREATE 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' ); text/html 2023-03-20T13:09:44+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:view?rev=1679317784 CREATE VIEW pour faire une vue CREATE VIEW database1.view_name AS (SELECT * FROM database2.other_view_name WHERE... ORDER BY) <https://dev.mysql.com/doc/refman/5.7/en/create-view.html> mysql text/html 2023-03-20T13:42:45+00:00 Anonymous (anonymous@undisclosed.example.com) https://ravieres.radeff.red/dokuwiki/info:mysql:xls2mysql?rev=1679319765 xls2mysql bien pratique pour générer rapidement une table MySQL à partir d'un fichier excel astuce: convertir un fichier csv ou openoffice en excel avant puis kw: oo2mysql, csv2mysql : open-office ou excel -> MySQL <http://scripts.ringsworld.com/database-tools/xls2mysql/> [local copy]