I written article for my personal purpose. I am using some mysql query on daily. I created the list of my Important mysql queries.
Important mysql queries
Taking backup of all mysql databases
>mysqldump -uroot -p --all-databases >> all_database_dump.sql;for single database
mysqldump -uroot -p public_wordpressmu wp_1_posts > one.sql
database import from sql file
mysql –verbose -uroot -p public_wordpressmu wp_1_posts < one.sql
Creating Trigger
create trigger
DROP TRIGGER wordpress.after_wp_1_post_update;
delimiter //
CREATE TRIGGER wordpressmu.after_wp_1_post_update
AFTER UPDATE ON wordpressmu.wp_1_posts
FOR EACH ROW
IF new.post_status = ‘publish’ THEN
INSERT INTO .article_posts (id,ref_id, title, status,
comments_on, created_at, updated_at) values (new.id,new.id,
new.post_title, new.post_status, 1, new.post_date, new.post_date);
END IF;
//
delimiter ;
Insert from different table (migration)
INSERT INTO database_anme.TableName(id, ref_id, title, status, comments_on, created_at, updated_at) select id, id, post_title, post_status, 1, post_date,post_date from wordpress.wp_1_posts
Update multiple rows in through Sql query
Update user set status=”ok” where id in (2 ,3 ,4 ,5,6)
changing all post path and image path
UPDATE wp_1_posts SET post_content = REPLACE(post_content, ‘test.siwan.com’, ‘www.siwan.com’);
WordPress -fetching category through sql queryfrom post
SELECT wp_1_term_taxonomy.taxonomy, wp_1_term_relationships.object_id, wp_1_terms.name, wp_1_posts.post_title
FROM wp_1_posts INNER JOIN ((wp_1_terms INNER JOIN wp_1_term_taxonomy ON wp_1_terms.term_id = wp_1_term_taxonomy.term_id) INNER JOIN wp_1_term_relationships ON wp_1_term_taxonomy.term_taxonomy_id = wp_1_term_relationships.term_taxonomy_id) ON wp_1_posts.ID = wp_1_term_relationships.object_id
WHERE wp_1_term_taxonomy.taxonomy=’category’ and wp_1_term_relationships.object_id=’217′
ORDER BY wp_1_term_relationships.object_id ;
CREATE DATABASE DB-NAME;
CREATE USER ‘DB-NAME’@’localhost’ IDENTIFIED BY ‘DB-NAME@123’;
grant all on USER-NAME.* to ‘DB-NAME’@’%’ identified by ‘DB-NAME@123’;
grant all on USER-NAME.* to ‘DB-NAME’@’localhost’ identified by ‘DB-NAME@123’;
FLUSH PRIVILEGES;
