================================================================================================ MySQL vanuit BASH shell Gebruikt in IMPORTDESCRIPTION om hardcoding van wachtwoorden te verbergen. Door de punt aan het begin van het bestand te zetten wordt deze niet getoond. Zet permissies van .mysql_access naar 400 (alleen te lezen door eigenaar) Zet eigenaar van .mysql_access naar root:root Plaats volgende variables in .mysql_access bestand: --------------------------------------------------- MYSQL_PWD_R="" MYSQL_PASSWORD="" SQL in bash script sql-pwg: --------------------------- . /usr/local/bin/.mysql_access mysql -upiwigo -p$MYSQL_PASSWORD -Dpiwigo SQL in bash script sql-wp: --------------------------- . /usr/local/bin/.mysql_access mysql -uwordpress -p$MYSQL_PASSWORD -Dwordpress ================================================================================================== MYSQL commando's ================================================================================================== Nuttige commando's mysql> show databases; Laat alle MYSQL databases zien mysql> select database (); Laat zien welke database actief is mysql> use piwigo; Zet piwigo als actieve database mysql> show tables; Laat alle tables van actieve db zien mysql> describe pi_images; Geeft structuur van table pi_images mysql> show columns from pi_images; --als boven-- mysql> select now(); Geeft systeemtijd mysql> select user(); Geeft huidige gebruiker mysql> select now(), user(); Combinatie van bovenstaande ================================================================================================= ************* * WORDPRESS * ************* ================================================================================================= Maak Wordpress database aan --------------------------- # mysqladmin -uroot -p'' CREATE wordpress; Geef user 'wordpress' beheer over Wordpress database ---------------------------------------------------- # mysql wordpress -uroot -p'' -e "GRANT ALL PRIVILEGES ON wordpress.* TO wordpress@localhost IDENTIFIED BY ''"; Login als root op alle mysql databases -------------------------------------- # mysql -uroot -p'' Gebruik Wordpress database ------------------------- mysql> use wordpress; Login als root op wordpress database ----------------------------------- # mysql -uroot -p'' -Dwordpress Login als admin van de wordpress database ---------------------------------------- # mysql wordpress -uwordpress -p'' Maak backup wp-dump.sql van database wordpress ----------------------------------------------- # mysqldump -uwordpress -p'adminruurlo' wordpress > /vault/wordpress/wp-dump.sql Verwijder complete wordpress database (root access nodig) -------------------------------------------------------- # mysqladmin -uroot -p'' drop wordpress Terugzetten van database door backup wp-dump.sql (root access nodig) --------------------------------------------------------------------- # mysql -uroot -p'' < /vault/wordpress/wp-dump.sql Creeer nieuwe table 'xls_descriptions' in database wordpress ----------------------------------------------------------- mysql> create table xls_descriptions (title varchar(128), description text); Importeer data uit een tabdelimited tekstbestand ------------------------------------------------ mysql> load data local infile '/var/www/html/wp_desc.txt' into table xls_descriptions fields terminated by '\t' lines terminated by '\r\n' (title, description); Maak table 'xls_descriptions' leeg ---------------------------------- mysql> DELETE FROM xls_descriptions; Verwijder table 'xles_descriptions uit wordpress database --------------------------------------------------------- mysql> drop table xls_descriptions; Update 'wp_posts.post_excerpt' met 'xls_descriptions.description' data ---------------------------------------------------------------------- mysql> update wp_posts, xls_descriptions set wp_posts.post_excerpt=xls_descriptions.description where (wp_posts.post_title=xls_descriptions.title); Check voor images zonder beschrijving in wp_posts.post_excerpt -------------------------------------------------------------- mysql> select wp_posts.post_title, xls_descriptions.title, wp_posts.post_excerpt from wp_posts join xls_descriptions on wp_posts.post_name = xls_descriptions.title where wp_posts.post_excerpt is null or wp_posts.post_excerpt=''; Wordpress blog migreren van domein www.makkink.eu naar lokaal IP 192.168.1.5 ============================================================================ Zet alle voorkomens van www.makkink.eu in tabel 'wp_posts' naar testserver of 192.168.1.5 ========================================================================================= Check in kolom post_content voor records met www.makkink.eu ----------------------------------------------------------- SELECT * FROM `wp_posts` WHERE post_content LIKE '%www.makkink.eu%' 372 records Update records naar 192.168.1.5: -------------------------------- UPDATE `wp_posts` SET post_content=REPLACE(post_content,'www.makkink.eu','192.168.1.5') WHERE post_content LIKE '%www.makkink.eu%' Check in kolom guid voor records met www.makkink.eu --------------------------------------------------- SELECT * FROM `wp_posts` WHERE guid LIKE '%www.makkink.eu%' 1277 records Update records naar 192.168.1.5: -------------------------------- UPDATE `wp_posts` SET guid=REPLACE(guid,'www.makkink.eu','192.168.1.5') WHERE guid LIKE '%www.makkink.eu%' ================================================================================================= ************* * PIWIGO * ************* ================================================================================================= Maak Piwigo database aan --------------------------- # mysqladmin -uroot -p'' CREATE piwigo; Geef user 'piwigo' beheer over Piwigo database ---------------------------------------------------- # mysql piwigo -uroot -p'' -e "GRANT ALL PRIVILEGES ON piwigo.* TO piwigo@localhost IDENTIFIED BY ''"; Login als root op alle mysql databases -------------------------------------- # mysql -uroot -p''; Gebruik Piwigo database ------------------------- mysql> use piwigo; Login als root op Piwigo database ----------------------------------- # mysql -uroot -p'' -Dpiwigo; Login als admin van de Piwigo database ---------------------------------------- # mysql piwigo -upiwigo -p''; Maak backup piwigo-dump.sql van database wordpress ----------------------------------------------- # mysqldump -upiwigo -p'' piwigo > /vault/piwigo/dbase_dump/piwigo-dump.sql; Verwijder complete Piwigo database (root access nodig) -------------------------------------------------------- # mysqladmin -uroot -p'' drop piwigo Terugzetten van database door backup piwigo-dump.sql (root access nodig) --------------------------------------------------------------------- # mysql -uroot -p'' < /vault/piwigo/dbase_dump/piwigo-dump.sql; Tel hoeveel foto's in PIWIGO zijn die (nog) geen beschrijving hebben -------------------------------------------------------------------- mysql> SELECT COUNT(file)FROM pi_images WHERE (pi_images.comment IS NULL OR pi_images.comment='') AND (pi_images.file LIKE '%.jpg' OR pi_images.file LIKE '%.JPG') AND pi_images.path NOT LIKE '%thumbs%'; Maak lijst van foto's in Piwigo die nog geen beschrijvingen hebben ----------------------------------------------------------------------- mysql> SELECT pi_images.file FROM pi_images WHERE (pi_images.comment is NULL OR pi_images.comment='') AND (pi_images.file LIKE '%.jpg' OR pi_images.file LIKE '%.JPG') AND pi_images.path NOT LIKE '%thumbs%'; Exporteer lijst van foto's in Piwigo die nog geen beschrijvingen hebben naar tijdelijk bestand ---------------------------------------------------------------------------------------------- mysql> SELECT pi_images.file,pi_images.filesize INTO OUTFILE '/var/lib/mysql/nwe_pwg_fotos.txt' FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' \ FROM pi_images WHERE (pi_images.comment is NULL OR pi_images.comment='') AND (pi_images.file LIKE '%.jpg' OR pi_images.file LIKE '%.JPG') AND pi_images.path NOT LIKE '%thumbs%' ORDER BY pi_images.file; Tel hoeveel foto's in PIWIGO zijn die (nog) geen beschrijving hebben, alleen map 'erica' ---------------------------------------------------------------------------------------- mysql> SELECT COUNT(pi_images.file) FROM pi_images WHERE (pi_images.comment is NULL OR pi_images.comment='') AND (pi_images.file LIKE '%.jpg' OR pi_images.file LIKE '%.JPG') AND pi_images.path LIKE '%/erica/%'; Maak lijst van foto's in Piwigo die nog geen beschrijvingen hebben, alleen map 'erica' -------------------------------------------------------------------------------------- mysql> SELECT pi_images.file FROM pi_images WHERE (pi_images.comment is NULL OR pi_images.comment='') AND (pi_images.file LIKE '%.jpg' OR pi_images.file LIKE '%.JPG') AND pi_images.path LIKE '%/erica/%'; Exporteer lijst van foto's in Piwigo map 'erica'die nog geen beschrijvingen hebben naar tijdelijk bestand --------------------------------------------------------------------------------------------------------- mysql> SELECT pi_images.file,pi_images.filesize INTO OUTFILE '/var/lib/mysql/nwe_pwg_fotos.txt' FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' \ FROM pi_images WHERE (pi_images.comment is NULL OR pi_images.comment='') AND (pi_images.file LIKE '%.jpg' OR pi_images.file LIKE '%.JPG') AND pi_images.path LIKE '%/erica/%' ORDER BY pi_images.file; Creeer nieuwe table 'xls_descriptions' in database piwigo ----------------------------------------------------------- mysql> create table xls_descriptions (file varchar(128), comment text); Importeer data uit een tabdelimited tekstbestand ------------------------------------------------ mysql> LOAD DATA LOCAL INFILE '/var/www/html/descriptions.txt' INTO TABLE xls_descriptions CHARACTER SET latin1 FIELDS TERMINATED BY '\t' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n'; Maak table 'xls_descriptions' leeg ---------------------------------- mysql> DELETE FROM xls_descriptions; Verwijder table 'xls_descriptions uit wordpress database --------------------------------------------------------- mysql> drop table xls_descriptions; Maak lijst van foto's met beschrijvingen in xls_descriptions die niet in Piwigo voorkomen ----------------------------------------------------------------------------------------- mysql> SELECT xls_descriptions.file FROM xls_descriptions LEFT JOIN pi_images ON xls_descriptions.file = pi_images.file WHERE pi_images.file is null"; Tel hoeveel foto's zonder beschrijving in Piwigo een beschrijvings-update gaan krijgen -------------------------------------------------------------------------------------- mysql> SELECT COUNT(pi_images.file) FROM pi_images,xls_descriptions WHERE (pi_images.file=xls_descriptions.file) AND (pi_images.comment IS NULL OR pi_images.comment=''); Update de Piwigo foto's zonder beschrijving met de beschrijvingen in de tabel xls_descriptions ---------------------------------------------------------------------------------------------- mysql> UPDATE pi_images, xls_descriptions SET pi_images.comment=xls_descriptions.comment WHERE pi_images.file=xls_descriptions.file AND (pi_images.comment IS NULL or pi_images.comment=''); Zet de beschrijvingen van een specifieke map terug naar NULL (bijv. van 2016/familie)) -------------------------------------------------------------------------------------- mysql> UPDATE pi_images SET pi_images.comment=NULL WHERE (pi_images.file LIKE '%.jpg' OR pi_images.file LIKE '%.JPG') AND pi_images.path LIKE '%/2016/familie/%'; ================================================================================================= ************** * phpMyAdmin * ************** ================================================================================================= [root@testserver ~]# mysql phpmyadmin -uroot -p'kn1kk@M!48' MariaDB [phpmyadmin]> SET old_passwords=0; MariaDB [phpmyadmin]> SELECT PASSWORD('adminruurlo'); +-------------------------------------------+ | PASSWORD('adminruurlo') | +-------------------------------------------+ | *D46A109C9908091F3596C9285B07C36C8E753024 | +-------------------------------------------+ MariaDB [phpmyadmin]> CREATE USER phpmyadmin@localhost IDENTIFIED VIA mysql_native_password USING '*D46A109C9908091F3596C9285B07C36C8E753024'; MariaDB [phpmyadmin]> GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO phpmyadmin@localhost;